批量导出大量数据Excel

恩，耐心看下，3Q。 // -----导出- OQC的数据-------------------------
public String export() throws Exception {
/********************************csv处理数据导出*****************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(5000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
Boolean isAddHead = false;
String sourcefile=System.getenv("temp")+"\\oqcRdmInsp.csv";
        int currentRow = 0;
        OutputStream out = new FileOutputStream(new File(sourcefile));
        Writer writer = new PrintWriter(out);
        BufferedWriter bw = new BufferedWriter(writer);
        for(int currentPage = 1 ; currentPage <= 1 ; currentPage++){
         pageBean.setCurrentPage(currentPage);
pageBean = oqcInfoService.rsToList(findBean, pageBean);
List listNgSet = pageBean.getList();
String[] headers = (String[])listNgSet.remove(0);//保存查询后的记录数据
for (int i = 0; i < listNgSet.size(); i++){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
Date datetime = sdf1.parse((String)((List)(listNgSet.get(i))).get(2));
String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
((List)(listNgSet.get(i))).set(1,weekdate);
}
if(!isAddHead){
// 标题设置结束
// 在Label对象的构造子中指明单元格位置是第一列第一行(0,0);
StringBuffer sb = new StringBuffer();
sb.append("OQC抽检\n");
for(int i=0;i< headers.length;i++) {
sb.append(headers[i]+",");
}
sb.setLength(sb.length() - 1);
bw.write(sb.toString()+"\n");
isAddHead = true;
currentRow = 2;
}
StringBuffer sb = new StringBuffer();
for(int i = 0;i < listNgSet.size();i++){
for(int k=0;k<((List)(listNgSet.get(i))).size();k++){
if(k==0||k==3||k==5||k==7){
Object o = ((List)(listNgSet.get(i))).get(k);
if(o!=null){
o = o.toString().replaceAll(",", "|");
sb.append("'" + (o==null?"":o));
}
sb.append(",");
}
else{
Object o = ((List)(listNgSet.get(i))).get(k);
if(o!=null){
o = o.toString().replaceAll(",", "|");
}
sb.append((o==null?"":o) + ",");
}
}
sb.setLength(sb.length() - 1);
sb.append("\n");

}
bw.write(sb.toString());
currentRow += listNgSet.size();
        }
        bw.flush();
        bw.close();
     pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.csv");
InputStream is = new FileInputStream(new File(sourcefile));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0) {
request.getOutputStream().write(b, 0, len);
}
is.close();
return null;  //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常！" + e.getMessage());
return null;
}
}
/***********************************csv处理数据导出 END*******************************************/
/***********************************合并多个EXCEL方式*******************************************/
// this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
// pageBean.setPageSize(1000);
// int records = oqcInfoService.getAllRows(findBean);
// int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
// Boolean isAddHead = false;
// String sourceRoot=System.getenv("temp")+"\\oqcRdmInsp";
// List<String> sourcefiles = new ArrayList<String>();
// for(int currentPage = 1 ; currentPage <= pages ; currentPage++){
// String newFileName = sourceRoot + currentPage + ".xls";
// sourcefiles.add(newFileName);
// int currentRow = 0;
// WritableWorkbook book = Workbook.createWorkbook(new File(newFileName));
// WritableSheet sheet = book.createSheet("OQC抽检", 0);
// pageBean.setCurrentPage(currentPage);
// pageBean = oqcInfoService.rsToList(findBean, pageBean);
// List listNgSet = pageBean.getList();
// String[] headers = (String[])listNgSet.remove(0);//保存查询后的记录数据
// for (int i = 0; i < listNgSet.size(); i++){
// SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
// Date datetime = sdf1.parse((String)((List)(listNgSet.get(i))).get(2));
// String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
// ((List)(listNgSet.get(i))).set(1,weekdate);
// }
// if(!isAddHead){
//                WritableCellFormat wcf = new jxl.write.WritableCellFormat();
//                Label wlabel = new Label(3,0,"OQC抽检表",wcf);
//                sheet.addCell(wlabel);
//                // 标题设置结束
//         // 在Label对象的构造子中指明单元格位置是第一列第一行(0,0);
//                for(int i=0;i< headers.length;i++) {
//                 sheet.addCell(new Label(i, 1 ,headers[i]));
//                }
//                isAddHead = true;
//                currentRow = 2;
// }
//
// for(int i = 0;i < listNgSet.size();i++){
//                int j = i + currentRow;
//                for(int k=0;k<((List)(listNgSet.get(i))).size();k++){
//                 String val=(String)((List)(listNgSet.get(i))).get(k);
//                 if(k==14||k==15||k==17||k==18||k==19||k==20||k==22||k==23||k==26||k==27||k==30||
//                 k==31||k==34||k==35||k==38||k==39||k==42||k==43||k==46||k==47||k==66||k>70){
//                 if(val!=null && !val.isEmpty()){
//                 jxl.write.Number num = new jxl.write.Number(k ,j ,StringHandle.formatIsEmptyToZeroI(val));
//                 sheet.addCell(num);
//                 }
//                 }else if(k==2||k==68||k==70){
//                 if(val!=null && !val.isEmpty()){
//                 java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
//                 Date datetime = sdf.parse(val);
//                 Calendar calendar = new GregorianCalendar();
// calendar.setTime(datetime);
// calendar.add(Calendar.DATE,1);//
// datetime = calendar.getTime();
// jxl.write.DateTime checkDate =new jxl.write.DateTime(k,j,datetime);
// sheet.addCell(checkDate);
//                    }
//    }else{
//    sheet.addCell(new Label(k, j ,(String)((List)(listNgSet.get(i))).get(k)));
//    }
//                }
// }
// book.write();
// book.close();
//
// }
// //写入数据并关闭文件
// String destFile = sourceRoot + "oqcRdmInsp0.xls";
// ExcelMergeUtil.merge(sourcefiles, destFile);
//     pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
// try {
// HttpServletResponse request = ServletActionContext.getResponse();
// request.setContentType("application/vnd.ms-excel");
// request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
// InputStream is = new FileInputStream(new File(destFile));
// byte[] b = new byte[4096];
// int len;
// while ((len = is.read(b)) > 0) {
// request.getOutputStream().write(b, 0, len);
// }
// is.close();
// return null;  //必须返回null 否则会报错.
// } catch (Exception e) {
// this.clearMessages();
// this.addActionMessage("导出出现异常！" + e.getMessage());
// return null;
// }
// }

/***********************************合并多个EXCEL方式 END**********************************************/

StringBuffer sb = new StringBuffer();
            for(int i = 0;i < listNgSet.size();i++){
                for(int k=0;k<((List)(listNgSet.get(i))).size();k++){
                    if(k==0||k==3||k==5||k==7){
                        Object o = ((List)(listNgSet.get(i))).get(k);
                        if(o!=null){
                            o = o.toString().replaceAll(",", "|");
                            sb.append("'" + (o==null?"":o));
                        }
                        sb.append(",");
                    }
                    else{
                        Object o = ((List)(listNgSet.get(i))).get(k);
                        if(o!=null){
                            o = o.toString().replaceAll(",", "|");
                        }
                        sb.append((o==null?"":o) + ",");
                    }
                }
                sb.setLength(sb.length() - 1);
                sb.append("\n");

            }
            bw.write(sb.toString());sb这个不要到最后再输出，1行就bw.write下。还有list.get(i)这样遍历，效率很低。
特别还写在了取size的时候。
for(int k=0;k<((List)(listNgSet.get(i))).size();k++){

我以前做过把40万条数据写入excel文件，写了个总结你可以参考一下http://download.csdn.net/detail/yechwng/3773328

我想不能频繁读写文件，影响速度，才到一定程度读写，你那样会好点吗，不过我的问题不是问csv的。基本跟这个关系不大。

时间，空间本身就是矛盾。
现在如果是内存不够，只能牺牲下时间了。其实也慢不了多少。另外，你贴上来的代码，没有被注释掉得部分是读csv的。
如果你现在问题主要不是这个，能不能请整理下想问题部分的代码，再贴一次？谢谢。

事实上感觉不是list的问题，而是excel占的内存问题。下面是代码。发现结果更不理想了。求开导 public String export() throws Exception {
/******************************合并多个EXCEL方式**************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(3000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
String sourceRoot=System.getenv("temp")+"\\oqcRdmInsp";
        Integer row = 1;
        List<String> sourcefiles = new ArrayList<String>();
for(int currentPage = 1 ; currentPage <= 4 ; currentPage++){
String newFileName = sourceRoot + currentPage + ".xls";
sourcefiles.add(newFileName);
WritableWorkbook book = Workbook.createWorkbook(new File(newFileName));
        WritableSheet sheet = book.createSheet("OQC抽检", 0);
        WritableCellFormat wcf = new jxl.write.WritableCellFormat();
        Label wlabel = new Label(3,0,"OQC抽检表",wcf);
        sheet.addCell(wlabel);
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(pageBean,findBean,book,sheet,row);
book.write();
book.close();
}
String destFile = sourceRoot + ".xls";
ExcelMergeUtil.merge(sourcefiles, destFile);
//写入数据并关闭文件
     pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
InputStream is = new FileInputStream(new File(sourceRoot+".xls"));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0){
request.getOutputStream().write(b, 0, len);
}
is.close();
return null;  //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常！" + e.getMessage());
return null;
}
}
/************************合并多个EXCEL方式 END****************************************/
@SuppressWarnings("deprecation")
@Override
public Integer exportToExcel(PageBean pageBean, OqcInfoBean findBean,WritableWorkbook book, WritableSheet sheet,int row) { Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();

if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between  "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
if (rs == null) {
            return null;
        }
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            if(row <=1){
             for(int i = 1; i <= columnCount; i++) {
                 sheet.addCell(new Label(i, 1 ,md.getColumnName(i)));
                }
            }
            while(rs.next()){
             row++;
             for (int i = 1; i <= columnCount; i++){
             Object o = rs.getObject(i);
             if(columnCount == 2 && o!= null){
             SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
         Date datetime = sdf1.parse((String)o);
         String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
         o = weekdate;
             }
             sheet.addCell(new Label(i, row,(String)(o!=null?o.toString():o)));
            }
            }
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return row;
}
}

你的表字段个数是？就是输出的excel有多少列？？
我写了个简单测试jxl性能的小程序。你试试看？
虽然慢，但是没有很花内存吧？
package sh.pl;import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;public class TestExcel {
public static void main(String[] args) {
String newFileName = "d:\\test_performance.xls"; int rows = 60000;
int cols = 12;

Date time = new Date();
        WritableWorkbook book = null;
try {
book = Workbook.createWorkbook(new File(newFileName));
WritableSheet sheet = book.createSheet("OQC抽检", 0);
exportToExcel(book, sheet, rows, cols);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (book != null) {
try {
book.write();
book.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println(new Date().getTime() - time.getTime());
}

    public static void exportToExcel(WritableWorkbook book, WritableSheet sheet, int row, int col) throws RowsExceededException, WriteException {
for (int i = 0; i < row; i++){
for (int j = 0; j < col; j++) {
            String value = i + "_" + j;
            if (j == 2){
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
                value = sdf1.format(new Date());
            }
            sheet.addCell(new Label(j, i, value));
}
        }

    }
}

OK啦。试试看。        WorkbookSettings wbs = new WorkbookSettings();
        wbs.setUseTemporaryFileDuringWrite(true);  //设置使用中间文件，而不是全内存保持输出内容。
        book = Workbook.createWorkbook(new File(newFileName), wbs); // 把WorkbookSettings对象设到Workbook里。
当然应该比直接内容保持所有内容要慢点。但是不会发生OOM错误了。

jxl输出的excel有行数限制的，最大65535。
你要改得话得改jxl的源代码了。
jxl.write.biff.WritableSheetImpl的，  /**
   * The maximum number of rows excel allows in a worksheet
   */
  private final static int numRowsPerSheet = 65536;改大点

        WorkbookSettings wbs = new WorkbookSettings();
        wbs.setUseTemporaryFileDuringWrite(true);  //设置使用中间文件，而不是全内存保持输出内容。
        book = Workbook.createWorkbook(new File(newFileName), wbs); // 把WorkbookSettings对象设到Workbook里。
试试看，谢谢。

恩，改了，如果是 40000行，6列，加上你那句确实变好很多，能加到40000行，10列意思，但是我的70列，6000行还是溢出了。但是也不能太多往上加，40000行，20列，等了很久，最后还是OOM了。

我看了jxl写得不是特别好。
每次缓存的量太大了。
jxl.write.biff.SSTContinueRecord的  /**
   * The maximum amount of bytes available for the SST record
   */
  private static int maxBytes = 8228 - // max length
                                4;  // standard biff record stuff也不让人改。所以你稍微把你的jvm 内存开大一点点就没事了。
-Xms256m -Xmx256m只要撑得住最大换存的量，数据量再大也没事了。

还有，其实我处理Excel。
一直用apache的POI的，要是可以你试试看这个库。用法也差不多。
你的水平，百度下肯定马上能用了。http://poi.apache.org/

谢谢，换了POI，5000条溢出了。代码应该没什么问题，开始换写html table 格式，table转换成excel 数据很慢，但是没有溢出，也没测试大概什么时候会溢出，不过文档生成速度很快，就是解析速度太慢了。还有有什么办法可以把撇好不显示不出来不？/******************************************************POI处理数据导出***************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(4000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
String sourcefile=System.getenv("temp")+"\\oqcRdmInsp.xls";
ExcelWriter excelWriter = new  ExcelWriter(new FileOutputStream(sourcefile));
for(int currentPage = 1 ; currentPage <= 1 ; currentPage++){
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(pageBean,findBean,excelWriter);
}
try{
excelWriter.export();
System.out.println(" 导出Excel文件[成功]");
} catch (IOException ex) {
System.out.println(" 导出Excel文件[失败]");
ex.printStackTrace();
}
//写入数据并关闭文件
     pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
InputStream is = new FileInputStream(new File(sourcefile));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0){
request.getOutputStream().write(b, 0, len);
}
is.close();
return null;  //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常！" + e.getMessage());
return null;
}
}
/*****************************************************POI处理数据导出  END*******************************************************/
@SuppressWarnings("deprecation")
@Override
public Integer exportToExcel(PageBean pageBean, OqcInfoBean findBean,ExcelWriter excelWriter) {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();
if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between  "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
if (rs == null) {
            return null;
        }
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
System.out.println(columnCount);
            int rowNum = 0;
            excelWriter.createRow(rowNum++);
            excelWriter.setCell(3,"OQC抽检");
         excelWriter.createRow(rowNum++);
         for(int i = 1; i <= columnCount; i++) {
         excelWriter.setCell(i-1,md.getColumnName(i).toString());
            }
            while(rs.next()){
             excelWriter.createRow(rowNum++);
             for (int i = 1; i <= columnCount; i++){
             Object o = rs.getObject(i);
             if(columnCount == 2 && o!= null){
             SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
         Date datetime = sdf1.parse((String)o);
         String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
         o = weekdate;
             }
             excelWriter.setCell(i-1,(String)(o==null?"":o.toString()));
            }
            }
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}

这是HTML的./***********************************HTML方式导出数据 ************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(20000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():(records/pageBean.getPageSize()+1);
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
for(int currentPage = 1 ; currentPage <= pages ; currentPage++){
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(response,findBean,pageBean);
}
//写入数据并关闭文件
     pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
return null;
}
/**********************************HTML方式导出数据 END********************************************/
@Override
public void exportToExcel(HttpServletResponse response, OqcInfoBean findBean, PageBean pageBean) throws IOException {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();

if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between  "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
OutputStream os= response.getOutputStream();
BufferedWriter bw = new BufferedWriter(new PrintWriter(new OutputStreamWriter(os)));
bw.write("OQC抽检表 <br>");

if (rs != null) {
ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            bw.write("<table cellspacing=\"0\" cellpadding=\"2\" border=\"1\"><tr>");
            for(int i = 1; i <= columnCount; i++){
             bw.write("<th>"+ md.getColumnName(i)+" </th>");
                }
            bw.write("</tr>");
            while(rs.next()){
             bw.write("<tr>");
             for (int i = 1; i <= columnCount; i++){
             Object o = rs.getObject(i);
             if(columnCount == 0||columnCount == 3||columnCount == 5||columnCount == 7){
             bw.write("<td nowrap>" + (String)(o!=null?("'"+o.toString()):"") +" </td>");
             continue;
             }
             if(columnCount == 2 && o!= null){
             SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
         Date datetime = sdf1.parse((String)o);
         String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
         o = weekdate;
             }
             bw.write("<td nowrap>" + (String)(o!=null?(o.toString()):"") +" </td>");
            }
             bw.write("</tr>");
            }
            bw.write("</table>");
            bw.flush();
            bw.close();
        }
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

POI代码，60000*70测试通过。 LZ参考下。
不过输出是Excel2007格式。没问题吧？
package sh.pl;import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;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.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;public class TestExcelPOI { public static void main(String[] args) {
String newFileName = "d:\\test_performance.xlsx"; int rows = 60000;
int cols = 70; Date time = new Date();
Workbook book = null;
Sheet sheet = null;
BufferedOutputStream out = null;
try {
book = new SXSSFWorkbook(128); //缓存128在内存。
sheet = book.createSheet("OQC抽检");

exportToExcel(book, sheet, rows, cols);

out = new BufferedOutputStream(new FileOutputStream(newFileName));
book.write(out);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println(new Date().getTime() - time.getTime());
}

    public static void exportToExcel(Workbook book, Sheet sheet, int row, int col) throws RowsExceededException, WriteException, IOException {
for (int i = 0; i < row; i++){
Row sheetRow = sheet.createRow(i);
for (int j = 0; j < col; j++) {
Cell cell = sheetRow.createCell(j);
            String value = i + "_" + j;
            if (j == 2){
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
                value = sdf1.format(new Date());
            }
            cell.setCellValue(value);
}
        }

    }
}

我也下班了。代码在公司里。就是用你说的方法，发现能导出10W条。速度也不慢，3分钟左右。非常感谢，以前看网上有人说有这种做法，不过当时没有源代码，也不知道他们是怎么写的。刚才我导包的时候还发现少包了，就重新下了个，把里面的jar包导进去就好了。明天贴代码，并结贴~~

/******************************************************POI处理数据导出***************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(10000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
String sourcefile=System.getenv("temp")+"\\oqcRdmInsp.xls";
ExcelWriter excelWriter = new  ExcelWriter(new FileOutputStream(sourcefile));
for(int currentPage = 1 ; currentPage <= 1 ; currentPage++){
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(pageBean,findBean,excelWriter);
}
try{
excelWriter.export();
System.out.println(" 导出Excel文件[成功]");
} catch (IOException ex) {
System.out.println(" 导出Excel文件[失败]");
ex.printStackTrace();
}
//写入数据并关闭文件
     pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
InputStream is = new FileInputStream(new File(sourcefile));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0){
request.getOutputStream().write(b, 0, len);
}
is.close();
return null;  //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常！" + e.getMessage());
return null;
}
}
/*****************************************************POI处理数据导出  END*******************************************************/
@SuppressWarnings("deprecation")
@Override
public Integer exportToExcel(PageBean pageBean, OqcInfoBean findBean,ExcelWriter excelWriter) {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();
if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between  "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
if (rs == null) {
            return null;
        }
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
System.out.println(columnCount);
            int rowNum = 0;
            excelWriter.createRow(rowNum++);
            excelWriter.setCell(3,"OQC抽检");
         excelWriter.createRow(rowNum++);
         for(int i = 1; i <= columnCount; i++) {
         excelWriter.setCell(i-1,md.getColumnName(i).toString());
            }
            while(rs.next()){
             excelWriter.createRow(rowNum++);
             for (int i = 1; i <= columnCount; i++){
             Object o = rs.getObject(i);
             if(columnCount == 2 && o!= null){
             SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
         Date datetime = sdf1.parse((String)o);
         String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
         o = weekdate;
             }

             excelWriter.setCell(i-1,o==null?"":o.toString());
            }
            }
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
} public class ExcelWriter {
// 定制浮点数格式
private static String NUMBER_FORMAT = "#,##0.00";
// 定制日期格式
private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
private OutputStream out = null;
private Workbook workbook = null;
private Sheet sheet = null;
private Row row = null;
public ExcelWriter() {
}
public ExcelWriter(OutputStream out) {
this.out = out;
this.workbook = new SXSSFWorkbook(128);;
this.sheet = workbook.createSheet();
}
/**
* 导出Excel文件
* @throws IOException
*/
public void export() throws FileNotFoundException, IOException {
try {
workbook.write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
throw new IOException(" 生成导出Excel文件出错! ", e);
} catch (IOException e) {
throw new IOException(" 写入Excel文件出错! ", e);
}
} /**
* 增加一行
* @param index 行号
*/
public void createRow(int index) {
this.row = this.sheet.createRow(index);
} /**
* 获取单元格的值
* @param index 列号
*/
public String getCell(int index){
Cell cell = this.row.getCell((short) index);
String strExcelCell = "";
if (cell != null) { // add this condition
// judge
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
strExcelCell = "FORMULA ";
break;
case Cell.CELL_TYPE_NUMERIC: {
strExcelCell = String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
strExcelCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
strExcelCell = "";
break;
default:
strExcelCell = "";
break;
}
}
return strExcelCell;
} /**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index, int value) {
Cell cell = this.row.createCell((short) index);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
} /**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index, double value) {
Cell cell = this.row.createCell((short) index);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));//设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
} /**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index, String value) {
Cell cell = this.row.createCell((short) index);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value);
} /**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index,Calendar value) {
Cell cell = this.row.createCell((short) index);
cell.setCellValue(value.getTime());
CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
}}

lz你好，我也遇到了excel导出的问题，不过我的是用poi程序自测能成功了，搬到项目组代码中就生成不了了，另外我看见2楼的修改jxl源代码的，请问jxl.write.biff.WritableSheetImpl在哪？谢谢……

调试易

批量导出大量数据Excel

解决方案 »