2011-04-01

apache POI + 中文过滤 导出MSSQL Server的数据

package jay;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import static java.lang.System.out;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class CheckData {

  static {
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

  static void run(String db, boolean only_chinese) throws Exception {
    Map<String, List<String>> map = new HashMap<String, List<String>>();

    BufferedReader reader = new BufferedReader(new FileReader(db + ".txt"));
    String line;

    /**
     * table1=field1,field2,field3,field4,field5
     * table2=field1,field2,field3,field4,field5
     */
    while ((line = reader.readLine()) != null) {
      List<String> list = new ArrayList<String>();
      String[] parts = line.split("=");
      String table = parts[0];
      String fields = parts[1];
      for (String filed : fields.split(",")) {
        list.add(filed.trim());
      }
      map.put(table, list);
    }

    for (String key : map.keySet()) {
      out.println("table: " + key + ", " + map.get(key));
    }
    reader.close();

    genData(db, map, only_chinese);
  }

  static void genData(String db, Map<String, List<String>> map,
      boolean only_chinese) {
    Connection conn = null;

    try {
      conn = DriverManager
          .getConnection("jdbc:sqlserver://DbServer:1433;integratedSecurity=true;DatabaseName="
              + "Public_" + db);
    } catch (SQLException e) {
      e.printStackTrace();
    }

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFont f = wb.createFont();
    f.setFontHeightInPoints((short) 12);
    // f.setColor(HSSFColor.RED.index);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    HSSFCellStyle cs = wb.createCellStyle();
    cs.setFont(f);

    int tables = 0;
    for (String table : map.keySet()) {
      listField(table, map.get(table), wb, cs, conn, only_chinese);
      tables++;
    }
    out.println("共有" + tables + "个表的数据输出。");

    try {
      String fileName = db + ".xls";
      if (only_chinese) {
        fileName = db + "-only-chinese.xls";
      }

      FileOutputStream out = new FileOutputStream(fileName);
      wb.write(out);
      out.close();
    } catch (Exception e) {
      e.printStackTrace();
    }

    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  static void listField(String table, List<String> options, HSSFWorkbook wb,
      HSSFCellStyle cs, Connection conn, boolean only_chinese) {

    HSSFSheet sheeet = wb.createSheet(table);
    int row_index = 0;
    HSSFRow row = sheeet.createRow(row_index++);
    for (int i = 0; i < options.size(); i++) {
      HSSFCell cell = row.createCell(i);
      cell.setCellValue(options.get(i));
      cell.setCellStyle(cs);
    }

    String selectOption = "";
    for (int i = 0; i < options.size() - 1; i++) {
      selectOption += options.get(i) + ",";
    }
    selectOption += options.get(options.size() - 1);

    Statement stmt;
    // 检查是否有中文
    final String regexp = "[\u4e00-\u9fa5]+";
    final Pattern pattern = Pattern.compile(regexp);

    try {
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select " + selectOption
          + " from " + table);
      int limit = 65534;
      boolean needCreate = false;
      int sheetIndex = 2;
      while (rs.next()) {
        if (row_index > limit && row_index <= limit * 2) {
          needCreate = true;
          if (sheetIndex >= 5) {
            out.println("table: " + table + " 超出最大数量" + sheetIndex
                + "，不再计算");
            break;
          }
        }

        if (needCreate) {
          out.println("table: " + table + "(" + sheetIndex + ")"
              + " 生成");
          sheeet = wb.createSheet(table + "(" + sheetIndex + ")");
          sheetIndex++;
          row_index = 1;
          needCreate = false;
        }

        if (only_chinese) {
          boolean hasChinese = false;

          for (int i = 0; i < options.size(); i++) {
            String value = rs.getString(i + 1);
            if (value != null)
              if (pattern.matcher(value).find()) {
                hasChinese = true;
                break;
              }
          }
          if (hasChinese) {
            row = sheeet.createRow(row_index++);
            for (int i = 0; i < options.size(); i++) {
              String value = rs.getString(i + 1);
              HSSFCell cell = row.createCell(i);
              cell.setCellValue(value);
            }
          }
        } else {
          row = sheeet.createRow(row_index++);
          for (int i = 0; i < options.size(); i++) {
            String value = rs.getString(i + 1);
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(value);
          }
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  static void xlsTest() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet s = wb.createSheet("sheet1");

    HSSFFont f = wb.createFont();
    f.setFontHeightInPoints((short) 12);
    f.setColor(HSSFColor.RED.index);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    HSSFCellStyle cs = wb.createCellStyle();
    cs.setFont(f);

    // Define a few rows
    for (short rownum = (short) 0; rownum < 30; rownum++) {
      HSSFRow row = s.createRow(rownum);
      for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) {
        HSSFCell cell2 = row.createCell(cellnum + 1);

        cell2.setCellValue(new HSSFRichTextString("Hello! " + cellnum));
        cell2.setCellStyle(cs);
      }
    }

    // Save
    FileOutputStream out = new FileOutputStream("workbook.xls");
    wb.write(out);
    out.close();
  }

  static void getMessageTest() throws Exception {
    Connection conn = null;

    final String regexp = "[\u4e00-\u9fa5]+";
    final Pattern pattern = Pattern.compile(regexp);

    try {
      conn = DriverManager
          .getConnection("jdbc:sqlserver://DbServer:1433;integratedSecurity=true;DatabaseName="
              + "Public_X");

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt
          .executeQuery("select dbo.big5ISOToUnicode(dbo.UnicodeToGbkISO(message)) from X where id = 4775");
      while (rs.next()) {
        String value = rs.getString(1);
        out.println("<< " + value);
        if (pattern.matcher(value).find()) {
          out.println("find it...");
        }

      }

      rs.close();
      stmt.close();
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) throws Exception {
    run("XDataBase", false); // show all
    run("XDataBase", true);  // only chinese character
    // getMessageTest();
  }
}

build.xml

<project default="run">
  <target name="run">
    <java classname="jay.CheckData" fork="yes">
      <classpath>
        <fileset dir="poi-3.7">
          <include name="poi-3.7-20101029.jar" />
          <include name="poi-scratchpad-3.7-20101029.jar" />
        </fileset>
        <path location="sqljdbc4.jar" />
        <path location="bin" />
      </classpath>
      <jvmarg value="-Xms1024m" /> <!-- 数据较多，需要设置大内存，用Event model可能会更好 -->
      <jvmarg value="-Xmx1024m" />
      <jvmarg value="-Djava.library.path=E:\Software\JAVA\SQL" />
    </java>
  </target>
</project>

工作日志