从数据库中导出excel提示

发布于 2021-11-28 17:55:29 字数 22112 浏览 874 评论 1

package com.system.dao;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.jsp.PageContext;
import javax.sql.rowset.CachedRowSet;

import org.apache.taglibs.standard.tag.el.core.OutTag;

import com.sun.rowset.CachedRowSetImpl;
import com.system.DB.DBManager;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class getExcel {
   private static Connection conn = null;
   private static PreparedStatement pstmt = null;
   private static ResultSet rs = null;
   private static CachedRowSet crs;
   private WritableCell labelCF11;

   @SuppressWarnings("static-access")
   public getExcel(String sql) {
       try {
           conn = DBManager.getDBManager().connection;
           pstmt = conn.prepareStatement(sql);
           rs = pstmt.executeQuery();
           crs = new CachedRowSetImpl();
           crs.populate(rs);
           System.out.println(null == crs);
           return;
       } catch (Exception e) {
           // TODO: handle exception
           return;
       }

   }

   public void getExcelResult(String sql, ServletOutputStream os)
           throws SQLException, IOException, WriteException {
       // 首先获取结果集
       // 这里获取RowSet的方法
       // List crs = this.getResult(sql);
       // 然后将结果集转化为Excel输出
       // 初始化工作
       List<Excel> list = new ArrayList<Excel>();
       for (int i = 0; i < 250000; i++) {
           Excel u = new Excel();
           u.setAcct_month(i + "--1");
           u.setProd_unit_name(i + "--2");
           u.setDevice_number(i + "--3");
           u.setInnet_date(i + "--4");
           u.setNet_type(i + "--5");
           u.setChannel_name(i + "--6");
           u.setTown_name(i + "--7");
           u.setInnet_channel_no(i + "--8");
           u.setDealer_name(i + "--9");
           u.setCity_desc(i + "--10");
           u.setLac_city_desc(i + "--11");
           u.setAll_fee(i + "--12");
           u.setAll_income(i + "--13");
           u.setMax_userlable(i + "--14");
           list.add(u);
       }
       // List list = (List) conn.prepareStatement(sql);
       int length = list.size();
       int sheetSize = 40000;
       int sheetNum = 1;
       if (length % sheetSize > 0) {
           sheetNum = length / sheetSize + 1;
       } else {
           sheetNum = length / sheetSize;
       }
       System.out.println(length);
       // 创建可写工作薄
       // 创建可写工作薄
       jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os);

       for (int kk = 0; kk < sheetNum; kk++) {
           // ///////创建sheet[kk]开始/////////////

           // 创建可写工作表
           jxl.write.WritableSheet ws = wwb.createSheet(("sheet" + kk), kk);
           // ////////////设置标题开始////////////////
           // 设置写入字体
           jxl.write.WritableFont wf = new jxl.write.WritableFont(
                   jxl.write.WritableFont.ARIAL, 11,
                   jxl.write.WritableFont.BOLD, false);
           // 设置CellFormat
           jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(
                   wf);
           // 用于Number的格式
           // jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00");
           // jxl.write.WritableCellFormat priceformat = new
           // jxl.write.WritableCellFormat(nf);
           // 把水平对齐方式指定为左对齐
           wcfF.setAlignment(jxl.format.Alignment.LEFT);
           // 把垂直对齐方式指定为居中对齐
           wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
           // 设置列名
           Label labelCF1 = new Label(0, 0, "账期", wcfF);
           Label labelCF2 = new Label(1, 0, "网格", wcfF);
           Label labelCF3 = new Label(2, 0, "号码", wcfF);
           Label labelCF4 = new Label(3, 0, "入网时间", wcfF);
           Label labelCF5 = new Label(4, 0, "网络类型", wcfF);
           Label labelCF6 = new Label(5, 0, "渠道类型1", wcfF);
           Label labelCF7 = new Label(6, 0, "渠道类型2", wcfF);
           Label labelCF8 = new Label(7, 0, "渠道代码", wcfF);
           Label lableCF9 = new Label(8, 0, "渠道名称", wcfF);
           Label lableCF10 = new Label(9, 0, "城市规划", wcfF);
           Label lableCF11 = new Label(10, 0, "基站计费规划", wcfF);
           Label lableCF12 = new Label(11, 0, "上月应收", wcfF);
           Label lableCF13 = new Label(12, 0, "上月主营", wcfF);
           Label lableCF14 = new Label(13, 0, "最大基站", wcfF);
           // 绑定值
           ws.addCell(labelCF1);
           ws.addCell(labelCF2);
           ws.addCell(labelCF3);
           ws.addCell(labelCF4);
           ws.addCell(labelCF5);
           ws.addCell(labelCF6);
           ws.addCell(labelCF7);
           ws.addCell(labelCF8);
           ws.addCell(lableCF9);
           ws.addCell(lableCF10);
           ws.addCell(lableCF11);
           ws.addCell(lableCF12);
           ws.addCell(lableCF13);
           ws.addCell(lableCF14);
           // ////////////设置标题结束////////////////
           jxl.write.WritableFont wf1 = new jxl.write.WritableFont(
                   jxl.write.WritableFont.ARIAL, 11,
                   jxl.write.WritableFont.NO_BOLD, false);
           // 设置CellFormat
           jxl.write.WritableCellFormat wcfF2 = new jxl.write.WritableCellFormat(
                   wf1);

           // ///////////循环写excel主体开始////////////

           for (int i = kk * sheetSize; i < (kk + 1) * sheetSize; i++) {
               if (i < length) {
                   Excel u = list.get(i);

                   Label data1 = new Label(0, i + 1 - (kk * sheetSize),
                           ("" + u.getAcct_month()), wcfF2);
                   Label data2 = new Label(1, i + 1 - (kk * sheetSize),
                           ("" + u.getProd_unit_name()), wcfF2);
                   Label data3 = new Label(2, i + 1 - (kk * sheetSize),
                           ("" + u.getDevice_number()), wcfF2);
                   Label data4 = new Label(3, i + 1 - (kk * sheetSize),
                           ("" + u.getInnet_date()), wcfF2);
                   Label data5 = new Label(4, i + 1 - (kk * sheetSize),
                           ("" + u.getNet_type()), wcfF2);
                   Label data6 = new Label(5, i + 1 - (kk * sheetSize),
                           ("" + u.getChannel_name()), wcfF2);
                   Label data7 = new Label(6, i + 1 - (kk * sheetSize),
                           ("" + u.getTown_name()), wcfF2);
                   Label data8 = new Label(7, i + 1 - (kk * sheetSize),
                           ("" + u.getInnet_channel_no()), wcfF2);
                   Label data9 = new Label(8, i + 1 - (kk * sheetSize),
                           ("" + u.getDealer_name()), wcfF2);
                   Label data10 = new Label(9, i + 1 - (kk * sheetSize),
                           ("" + u.getCity_desc()), wcfF2);
                   Label data11 = new Label(10, i + 1 - (kk * sheetSize),
                           ("" + u.getLac_city_desc()), wcfF2);
                   Label data12 = new Label(11, i + 1 - (kk * sheetSize),
                           ("" + u.getAll_fee()), wcfF2);
                   Label data13 = new Label(12, i + 1 - (kk * sheetSize),
                           ("" + u.getAll_income()), wcfF2);
                   Label data14 = new Label(13, i + 1 - (kk * sheetSize),
                           ("" + u.getMax_userlable()), wcfF2);

                   ws.addCell(data1);
                   ws.addCell(data2);
                   ws.addCell(data3);
                   ws.addCell(data4);
                   ws.addCell(data5);
                   ws.addCell(data6);
                   ws.addCell(data7);
                   ws.addCell(data8);
                   ws.addCell(data9);
                   ws.addCell(data10);
                   ws.addCell(data11);
                   ws.addCell(data12);
                   ws.addCell(data13);
                   ws.addCell(data14);
               } else {
                   break;
               }

           }
           // ///////////循环写excel主体结束////////////
           // ///////创建sheet[kk]结束/////////////
       }
       // 我猜测数据太多时，可能会导致内存溢出
       wwb.write();
       wwb.close();
       os.flush();
       os.close();
   }

}

报错信息：

false
250000
一月 27, 2015 5:02:44 下午 org.apache.tomcat.util.net.NioEndpoint$SocketProcessor doRun
严重:
java.lang.OutOfMemoryError: Java heap space
   at java.util.HashMap.resize(HashMap.java:703)
   at java.util.HashMap.putVal(HashMap.java:662)
   at java.util.HashMap.put(HashMap.java:611)
   at jxl.write.biff.SharedStrings.getIndex(SharedStrings.java:73)
   at jxl.write.biff.LabelRecord.setCellDetails(LabelRecord.java:220)
   at jxl.write.biff.WritableSheetImpl.addCell(WritableSheetImpl.java:1199)
   at com.system.dao.getExcel.getExcelResult(getExcel.java:188)
   at com.system.servlet.downloadExcelServlet.doPost(downloadExcelServlet.java:66)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:644)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
   at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:537)
   at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1085)
   at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)
   at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
   at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1556)
   at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1513)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
   at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
   at java.lang.Thread.run(Thread.java:745)