百万记录poi导出日记

来源：互联网发布：php做九九乘法表编辑：程序博客网时间：2024/05/16 11:56

机器环境：
hp probook 4326s 4G AMD P340 2.20Hz
mysql 5.0.26
sts spring eclipse
jdk 1.7.09
tomcat 7.0

步骤：
JDBC访问
使用POI中的 XFFX 对象

导入jar包：
核心包：
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
依赖包：
commons-beanutils-1.7.0.jar
commons-collections-3.0.jar
commons-lang-2.0.jar
commons-logging-1.0.4.jar
dom4j-1.6.1.jar
standard-1.0.2.jar
stax-api-1.0.1.jar
xmlbeans-2.3.0.jar
mysql-connector-java-5.1.10-bin.jar
junit-4.8.2.jar
测试程序，从测试表中读取100万数据，轻松写入excel中。

[html] view plain copy
 
package test;  
  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.CellStyle;  
import org.apache.poi.ss.usermodel.Font;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.streaming.SXSSFWorkbook;  
import org.apache.poi.xssf.usermodel.XSSFCell;  
import org.apache.poi.xssf.usermodel.XSSFCellStyle;  
import org.apache.poi.xssf.usermodel.XSSFFont;  
import org.apache.poi.xssf.usermodel.XSSFRow;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  
/**  
 * @Description:  poi实现输出信息到excel文件  
 * @Author: nutony  
 * @Date: 2013-05-15  
 */  
public class TestMore100 {  
        public static void main(String[] args) throws Exception {  
                TestMore100 tm = new TestMore100();  
                tm.jdbcex(true);  
        }  
  
        public void jdbcex(boolean isClose) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException, InterruptedException {  
                String xlsFile = "d:/test100.xlsx";                                        //输出文件  
                Workbook wb = new SXSSFWorkbook(100);                                //创建excel文件，内存只有100条记录【关键语句】  
                Sheet sheet = wb.createSheet("我的第一个工作簿");                //建立新的sheet对象  
  
                Row nRow = null;  
                Cell nCell   = null;  
  
                //使用jdbc链接数据库  
                Class.forName("com.mysql.jdbc.Driver").newInstance();    
                  
                String url = "jdbc:mysql://localhost:3306/jkmore100?characterEncoding=UTF-8";  
                String user = "root";  
                String password = "root";  
                  
                Connection conn = DriverManager.getConnection(url, user,password);     
                Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);     
  
                String sql = "select name,age,des from customer limit 1000000";   //100万测试数据  
                ResultSet rs = stmt.executeQuery(sql);    
                  
                  
                long  startTime = System.currentTimeMillis();        //开始时间  
                System.out.println("strat execute time: " + startTime);  
                //context  
                int rowNo = 0;  
                int colNo = 0;  
                while(rs.next()) {  
                        colNo = 0;  
                        nRow = sheet.createRow(rowNo++);  
  
                        nCell = nRow.createCell(colNo++);  
                        nCell.setCellValue(rs.getString(colNo));  
                          
                        nCell = nRow.createCell(colNo++);  
                        nCell.setCellValue(rs.getString(colNo));  
                          
                        if(rowNo%100==0){  
                                System.out.println("row no: " + rowNo);  
                        }  
                          
                        Thread.sleep(1);                        //休息一下，防止对CPU占用  
                }  
                  
                long finishedTime = System.currentTimeMillis();        //处理完成时间  
                System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");  
                  
                  
                FileOutputStream fOut = new FileOutputStream(xlsFile);  
                wb.write(fOut);  
                fOut.flush();  
                fOut.close();  
                  
                long stopTime = System.currentTimeMillis();                //写文件时间  
                System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");  
                  
                if(isClose){  
                        this.close(rs, stmt, conn);  
                }  
        }  
          
        //close resource  
        private void close(ResultSet rs, Statement stmt, Connection conn ) throws SQLException{  
                rs.close();     
                stmt.close();     
                conn.close();   
        }          
          
}  

0 0