java-poi-excel-导出20万条记录【源代码】

package com.ufida.cutm.export; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; 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.poifs.filesystem.POIFSFileSystem; import com.ufida.cutm.dao.DBConnection; import com.ufida.cutm.util.ConstantsExcel; import com.ufida.cutm.util.ExcelWriter; import com.ufida.cutm.util.StringUtilCommon; import com.ufida.cutm.util.ZipCompressorByAnt; import com.ufida.cutm.vo.T1; /** * 测试导出excel * 最大数据量 * 2011-3-9 22：00 * @author zengq * */ public class ExportExcel { public static final String[] excelHeader=new String[]{"id","ida","idb","name","pwd","aa","bb","cc","dd","ee","ff","gg", "hh","ii","jj","kk","ll","mm","nn","oo","pp","qq","rr","ss","tt"}; public static final String File_Path="E:\\projects\\java\\ws_0\\export\\data"; /** * @author zengq * @param sql 获取记录行数的sql * @param recordStr sql语句中记录数的别名 * @return 总记录数 * 2011-3-10 14:25 * @throws Exception */ public Long getRecordNumber(String sql,String recordStr) throws Exception{ Connection conn=null; PreparedStatement pst=null; ResultSet rs1=null; recordStr=StringUtilCommon.getPropertyValue(recordStr); try { Long recordNum=0l; conn=DBConnection.getConnection(); pst=conn.prepareStatement(sql); rs1=pst.executeQuery(); while(rs1.next()){ if(recordStr.length()==0){ recordNum=rs1.getLong(1); }else{ recordNum=rs1.getLong(recordStr); } } return recordNum; } catch (RuntimeException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ closeResource(rs1,pst,conn); } return new Long(0); } public List getAll(String sql) throws Exception{ Connection conn=null; PreparedStatement pst=null; ResultSet rs1=null; List list=null; //FileOutputStream out=null; FileWriter out=null; try { list=new ArrayList(); if(sql==null){ sql="select *from t1 "; } conn=DBConnection.getConnection(); // Statement st=conn.createStatement(); // ResultSet rs1=st.executeQuery(sql); pst=conn.prepareStatement(sql); rs1=pst.executeQuery(); rs1.last(); System.out.println("rs1.getRow()="+rs1.getRow()); rs1.beforeFirst(); File file=new File("c:/test.doc"); // if(!file.exists()){ // file=new File("c:/test.doc"); // } //out=new FileOutputStream(file,true); // out=new FileWriter(file,true); while(rs1.next()){ T1 t=new T1(); t.setId(rs1.getLong("id")); t.setIda(rs1.getLong("ida")); t.setIdb(rs1.getString("idb")); t.setName(rs1.getString("name")); t.setPwd(rs1.getString("pwd")); t.setAa(rs1.getString("aa")); t.setBb(rs1.getString("bb")); t.setCc(rs1.getString("cc")); t.setDd(rs1.getString("dd")); t.setEe(rs1.getString("ee")); t.setFf(rs1.getString("ff")); t.setGg(rs1.getString("gg")); t.setHh(rs1.getString("hh")); t.setIi(rs1.getString("ii")); t.setJj(rs1.getString("jj")); t.setKk(rs1.getString("kk")); t.setLl(rs1.getString("ll")); t.setMm(rs1.getString("mm")); t.setNn(rs1.getString("nn")); t.setOo(rs1.getString("oo")); t.setPp(rs1.getString("pp")); t.setQq(rs1.getString("qq")); t.setRr(rs1.getString("rr")); t.setSs(rs1.getString("ss")); t.setTt(rs1.getString("tt")); //System.out.println("t1="+t); //out.write(t.toString().getBytes()); // out.write(t.toString()+"\t\r"); list.add(t); } System.out.println("空一行*********************************************"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ closeResource(rs1,pst,conn); if(out!=null){ out.flush(); out.close(); out=null; } } return list; } public void closeResource(ResultSet rs1,Statement pst,Connection conn) throws Exception{ if(rs1!=null){ rs1.close(); rs1=null; }if(pst!=null){ pst.close(); pst=null; } DBConnection.closeConnection(conn); System.gc(); } /** * 获取总页数 * @param recordNums * @param pageSize * @return * @throws Exception */ public static Long getTotalPage(Long recordNums,Long pageSize) throws Exception{ if(recordNums==0||pageSize==0) return 0l; return (long)Math.ceil(recordNums/(double)pageSize); } /** * 为sql语句增加提取数 * @param basicSql * @param begin * @param limitNum * @return * @throws Exception */ public static String getPageSql(String basicSql,Long begin,Long limitNum) throws Exception{ basicSql=StringUtilCommon.getPropertyValue(basicSql); if(basicSql.length()==0) return ""; String sql=" limit "+begin+","+limitNum; return sql; } public Map getPageList(Map argsMap) throws Exception{ String path=(String)argsMap.get("path"); if(path==null){ path=this.File_Path; } File file=new File(path+"/test0.xls"); String sql="select count(*) record from t1"; Long totalNum=getRecordNumber(sql, ""); System.out.println("totalNum="+totalNum); Long pageSize=10000l; Long totalPage=getTotalPage(totalNum, pageSize); System.out.println("totalPage="+totalPage); String sql2="select *from t1 limit "; ExcelWriter writer=new ExcelWriter(); for (int i = 0; i < totalPage; i++) { if(i==0){ sql2+=" 0,"+pageSize; System.out.println("sql2="+sql2); List list=getAll(sql2); WriteToExcel(file,list); }else{ sql2="select *from t1 limit "+pageSize*i+","+pageSize; System.out.println("sql2="+sql2); List list=getAll(sql2); WriteToExcel(new File(path+"/test"+i+".xls"), list); //reWirte(file, list); } } return null; } HSSFWorkbook workbook=null; HSSFSheet sheet=null; HSSFRow row=null; HSSFCell cell=null; public void WriteToExcel(File file,List list) throws Exception{ if(file==null&&!file.exists()){ System.out.println("文件不能为空！"); return; } FileOutputStream out=new FileOutputStream(file,true); BufferedOutputStream buffout=new BufferedOutputStream(out); ExcelWriter writer=new ExcelWriter(); writer=new ExcelWriter(buffout); writer.createRow(0); for (int i = 0; i < excelHeader.length; i++) { writer.setCell(i, excelHeader[i]); } int rownum=0; for (int j=0;j<list.size();j++) { T1 t=(T1)list.get(j); //System.out.println("WriteToExcel="+t); //Thread.sleep(1000); writer.createRow(rownum++); for(int k=0;k<excelHeader.length;k++){ String header=excelHeader[k]; //System.out.println("j="+j+",k="+k+",header="+header); if(header.equals("id")){ writer.setCell(0,t.getId()); }else if(header.equals("ida")){ writer.setCell(1,t.getIda()+"ida"); }else if(header.equals("idb")){ writer.setCell(2,t.getIdb()+"idb"); }else if(header.equals("name")){ writer.setCell(3,t.getName()+"name"); }else if(header.equals("pwd")){ writer.setCell(4,t.getPwd()+"pwd"); } //下一个10行 else if(header.equals("aa")){ writer.setCell(5,t.getAa()+"aa"); }else if(header.equals("bb")){ writer.setCell(6,t.getBb()+"bb"); }else if(header.equals("cc")){ writer.setCell(7,t.getCc()+"cc"); }else if(head