Sybase数据库大量数据导出Excel

    应客户要求，导出数据库中的数据为Excel，只在一个Sheet中，Excel中的内容要与数据库里的内容一致（主要针对日期型数据）。

            由于之前接触过POI，对POI的一些特性还是有一定的了解的，因此顺其自然的用POI去解决这个问题。POI3.8版本之前的版本处理大量数据的导出Excel效果不是很理想，主要在与Excel2003版本单个Sheet的行限制为65536，大量数据的导出得分多个Sheet，针对这一点，客户就不会满意。其次，在实验过程中，大数据量的导出很容易引发内存溢出，调整JVM的内存大小治标不治本。很多人建议保存为.CSV格式的文件。不过，.CSV方式导出也存在问题：首先，如果用excel来打开csv，超过65536行的数据都会看不见，这是Excel程序的问题。其次，如果要导出一个身份证号码，手机号码等纯数字构成的字符串，在excel中打开csv时，这些字段很容易被识别成数字，被误处理。
    POI3.8以后的版本支持Excel2007高版本，单个Sheet的行数可达到百万，针对内存溢出问题，可通过设置内存数据保留数，每当计数到指定的数值时，刷新数据到硬盘,清理内存。

           有工具，有思路，那么剩下来要做的就是coding and testing 。不多说了，上图上代码。

   

 

     上图是工程目录树。以下是数据库连接以及Excel导出代码。此处我用mysql在本地实验，实际运用中需要修改Driver url usernmae password。

package Sybase2Excel.sh.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Utils {private static Connection con;private static String Driver = "com.mysql.jdbc.Driver";private static String url = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8";private static String username = "root";private static String password = "123";public static Connection getConnection() {try {Class.forName(Driver);con = DriverManager.getConnection(url, username, password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return con;}public static void closeDB(ResultSet rs, Statement st, Connection con) {try {if (rs != null) {rs.close();rs = null;}if (st != null) {st.close();st = null;}if (con != null) {con.close();con = null;}} catch (SQLException e) {e.printStackTrace();}}}

 

package Sybase2Excel.sh;import java.io.BufferedReader;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.FileReader;import java.io.IOException;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import Sybase2Excel.sh.db.Utils;public class Sybase2Excel {public Sybase2Excel(String FileName , String exportPath) throws SQLException, IOException{long curr_time=System.currentTimeMillis();Connection con = null;Statement st = null;ResultSet rs = null;String sql = "select * from "+FileName;con = Utils.getConnection();st =con.createStatement();rs =st.executeQuery(sql);int rowInMemory = 1000;FileOutputStream output = new FileOutputStream(new File(exportPath+FileName+".xlsx"));  //写入的文件路径            SXSSFWorkbook wb = new SXSSFWorkbook(rowInMemory);//内存中保留 100 条数据，以免内存溢出    int CountColumnNum = rs.getMetaData().getColumnCount(); // 获取总列数        Sheet sheet = wb.createSheet("第一页");   //wb.createSheet("第一页");        Row titleRow = sheet.createRow(0); //标题行        Cell cell;//单元格for (int j = 0; j < CountColumnNum; j++) {//sheet.setColumnWidth(j, 5500);//生成单元格 ， 并将列标题写入单元格中cell = titleRow.createCell(j);cell.setCellValue(rs.getMetaData().getColumnName(j + 1)); //cell.setCellValue(new HSSFRichTextString(rs.getMetaData().getColumnName(j + 1)));}int i=1;Row row;while (rs.next()) {//每取一行数据，新生成一行，并将每列的数据写入到该行的每一个单元格中row = sheet.createRow(i); //下标为1的行开始for (int j = 0; j < CountColumnNum; j++) {cell = row.createCell(j);cell.setCellValue(rs.getString(j + 1)); //cell.setCellValue(new HSSFRichTextString(rs.getString(j + 1)));}if(i%rowInMemory==0){//每当计数到指定的数值时，刷新数据到硬盘,以清理内存((SXSSFSheet)sheet).flushRows();}i++;}wb.write(output);        Utils.closeDB(rs, st, con);        System.out.println("耗时:"+(System.currentTimeMillis()-curr_time)/1000+"秒");                }public static void main(String[] args) {String path="Resource/table.txt";String exportPath="D://ExportDataToExcel/"; //  Resource/Data/File dir=new File(exportPath);if(!dir.exists()){dir.mkdir();//mkdir dir}File file=new File(path);BufferedReader reader = null;String FileName = null;try {            reader = new BufferedReader(new FileReader(file));            while ((FileName = reader.readLine()) != null) {            try {Sybase2Excel S2E = new Sybase2Excel(FileName,exportPath);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}            }            reader.close();        } catch (IOException e) {            e.printStackTrace();        } finally {            if (reader != null) {                try {                    reader.close();                } catch (IOException e1) {                }            }        }       }}

     入口函数从table.txt文件中读取需要导出的表名。

       导出的Excel文件效果如下。实际导出32w条，30列的数据，耗时在160s左右。

 

 

代码下载地址