JAVA写EXCEL多于60000条的处理（POI)

 import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.opensymphony.xwork.Preparable;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
import javax.servlet.http.HttpServletResponse;

import com.opensymphony.webwork.ServletActionContext;
import com.opensymphony.webwork.interceptor.ServletResponseAware;
import com.ytincl.scdp.common.util.FormatUtilJB;
import org.apache.commons.beanutils.LazyDynaBean;

import com.ytincl.drms.common.importfromfile.web.action.Excelutil;
import com.ytincl.drms.common.importfromfile.web.action.judgeStringUtil;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.BeansException;
import org.springframework.beans.MutablePropertyValues;
import org.springframework.beans.PropertyValue;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
import uk.ltd.getahead.dwr.WebContextFactory;
import com.opensymphony.webwork.interceptor.ServletRequestAware;
import javax.servlet.http.HttpServletRequest;
import com.opensymphony.xwork.util.XWorkList;
 
 
 /*
     * 传入一个列表数据，将其写入到EXCEL中 2008.01.09 赵祖龙
     *
     */

    /*
     * 传入一个列表数据，将其写入到EXCEL中 2008.01.09 赵祖龙
     * 加入对于多行超过65535的处理 2008.01.17 赵祖龙
     */

    public static void writeExcel(String filename, List writeValue, HttpServletResponse response) throws IOException {

     String Disposition = "attachment;filename=" + URLEncoder.encode(filename, "utf8");
     System.out.println("Dispositin"+Disposition);
     
     response.setContentType("application/ms-excel; charset=gb2312");
        response.setHeader("Content-Disposition", Disposition);

        HSSFWorkbook wb = new HSSFWorkbook();

        if (writeValue == null) {
            return;
        }

        List listTitle = new ArrayList(); //保存当前的EXCEL题头
        listTitle = (List) writeValue.get(0);//得到第一行的题头

        List listWriteContent = new ArrayList();
        for (int icount = 1; icount < writeValue.size(); icount++) {
            listWriteContent.add((List) writeValue.get(icount));
        }
       // System.out.println("得到的未带题头的行数"+listWriteContent.size()+"带题头的行数"+writeValue.size());

        // 判断当前总行数是否超过EXCEL一页的总行数65536
        int iMaxLines = 65535;
        int iCurrentLines = listWriteContent.size();//要写的内容总行数
        int iPageCount = iCurrentLines / iMaxLines;//总页数
        int ileftValue = iCurrentLines % iMaxLines;//尾页要写的行数
        if (iCurrentLines == 0) {
            return;
        }
        //页数大于1并且尾页行大于0
        if (iPageCount > 1 && ileftValue > 0) {
            iPageCount = iPageCount + 1;
        }
       
        if(iPageCount==0 && ileftValue>0){
            //进行按SHEET页写数据
            for (int p = 0; p <= iPageCount; p++) {
          
                HSSFRow row = null;
                HSSFCell cell = null;
                HSSFSheet sheet = wb.createSheet("sheet" + Integer.toString(p));
                int iStartRow = 0;
                int iEndRow = 0;
                //确定当前页开始写的行首及行尾号
                if (p == 0) {
                    iStartRow = 0;
                    if (iPageCount == 0) {
                        iEndRow = iCurrentLines - 1;
                    } else {
                        iEndRow = (p + 1) * iMaxLines - 1;

                    }
                } else {
                    iStartRow = p * iMaxLines;
                    if (p == iPageCount - 1) {
                        iEndRow = iCurrentLines - 1;
                    } else {
                        iEndRow = (p + 1) * iMaxLines - 1;
                    }

                }
              //  System.out.println("开始行"+iStartRow+"结束行"+iEndRow);
                List listPageValue = new ArrayList();//保存每页的数据
                listPageValue.add(listTitle);
                for (int i = iStartRow; i <= iEndRow; i++) {
                    if (listWriteContent.get(i) == null) {
                        continue;
                    }

                    listPageValue.add((List) listWriteContent.get(i));
                }
         
                // System.out.println("iStartRow" + iStartRow + "iEndRow" + iEndRow + "p" + p);

                int iRow = 0; //定义每页开始写入的行号
                for (int i = 0; i < listPageValue.size(); i++) {
                    if (listPageValue.get(i) == null) {
                        continue;
                    }
                    //得到要写的内容行数据
                    List listValue = (List) listPageValue.get(i);

                    row = sheet.createRow((short) i);
                   
                    wb.getSheet(Integer.toString(p));//得到要写数据当前的页
                    for (int n = 0; n < listValue.size(); n++) {
                       
                        cell = row.createCell((short) n);
                        if ( i ==1) {
                            HSSFCellStyle style = wb.createCellStyle();
                            style.setFillBackgroundColor(HSSFColor.BLUE.index);
                            cell.setCellStyle(style);
                           // System.out.println("设定第一行的背景颜色");
                        }
                       
                        cell.setEncoding(cell.ENCODING_UTF_16);
                        // System.out.println(listValue.get(n));
                        cell.setCellType(cell.CELL_TYPE_STRING);
                        if (listValue.get(n) == null) {
                            listValue.set(n, "");
                        }
                        cell.setCellValue(listValue.get(n).toString());
                       // System.out.println("现在要写的数据"+listValue.get(n).toString());
                       
                    }
                }

 

            }
        }else{
            //进行按SHEET页写数据

            for (int p = 0; p < iPageCount; p++) {
          
                HSSFRow row = null;
                HSSFCell cell = null;
                HSSFSheet sheet = wb.createSheet("sheet" + Integer.toString(p));
                int iStartRow = 0;
                int iEndRow = 0;
                //确定当前页开始写的行首及行尾号
                if (p == 0) {
                    iStartRow = 0;
                    if (iPageCount == 0) {
                        iEndRow = iCurrentLines - 1;
                    } else {
                        iEndRow = (p + 1) * iMaxLines - 1;

                    }
                } else {
                    iStartRow = p * iMaxLines;
                    if (p == iPageCount - 1) {
                        iEndRow = iCurrentLines - 1;
                    } else {
                        iEndRow = (p + 1) * iMaxLines - 1;
                    }

                }
              //  System.out.println("开始行"+iStartRow+"结束行"+iEndRow);
                List listPageValue = new ArrayList();//保存每页的数据
                listPageValue.add(listTitle);
                for (int i = iStartRow; i <= iEndRow; i++) {
                    if (listWriteContent.get(i) == null) {
                        continue;
                    }

                    listPageValue.add((List) listWriteContent.get(i));
                }
         
                // System.out.println("iStartRow" + iStartRow + "iEndRow" + iEndRow + "p" + p);

                int iRow = 0; //定义每页开始写入的行号
                for (int i = 0; i < listPageValue.size(); i++) {
                    if (listPageValue.get(i) == null) {
                        continue;
                    }
                    //得到要写的内容行数据
                    List listValue = (List) listPageValue.get(i);

                    row = sheet.createRow((short) i);
                   
                    wb.getSheet(Integer.toString(p));//得到要写数据当前的页
                    for (int n = 0; n < listValue.size(); n++) {
                       
                        cell = row.createCell((short) n);
                        if ( i ==1) {
                            HSSFCellStyle style = wb.createCellStyle();
                            style.setFillBackgroundColor(HSSFColor.BLUE.index);
                            cell.setCellStyle(style);
                     //       System.out.println("设定第一行的背景颜色");
                        }
                       
                        cell.setEncoding(cell.ENCODING_UTF_16);
                        // System.out.println(listValue.get(n));
                        cell.setCellType(cell.CELL_TYPE_STRING);
                        if (listValue.get(n) == null) {
                            listValue.set(n, "");
                        }
                        cell.setCellValue(listValue.get(n).toString());
                    //    System.out.println("现在要写的数据"+listValue.get(n).toString());
                       
                    }
                }

 

            }
        }

        // System.out.println("总行数iCurrentLines" + iCurrentLines + "总页数iPageCount" + iPageCount);

        ServletOutputStream outstream = response.getOutputStream();
        wb.write(outstream);
        outstream.flush();
    }

 

    /*
     * 组织数据后调用写入EXCEL文件的操作writeExcel 2008.01.09 赵祖龙
     *
     */
    public static void outputExcel(String filename, List printList, HttpServletResponse response) throws IOException {
        List rowValue =  new ArrayList();
        List titleValue = new ArrayList();
        //定义行标题              
        titleValue.add("年度");
        titleValue.add("省局编号");       
        titleValue.add("省局名称");
        titleValue.add("省内分发单元代码");
        titleValue.add("省内分发单元名称");
      
        rowValue.add(titleValue);
        //得到行内容
       
        for (int i = 0; i < printList.size(); i++) {
         DistSjSnffdy fbkResult = (DistSjSnffdy) printList.get(i);
            List listValue = new ArrayList();

            int iYxn = fbkResult.getYxn();
            listValue.add(Integer.toString(iYxn));
           
                String strSjid = fbkResult.getSjid();
                listValue.add(strSjid);
                String strSjmc = fbkResult.getSjmc();
                listValue.add(strSjmc);

                String strSnffdy = fbkResult.getSnffdyid();
                listValue.add(strSnffdy);
                String strSnffdymc = fbkResult.getSnffdymc();
                listValue.add(strSnffdymc);

          
            rowValue.add(listValue);
  
        }
       
        writeExcel( filename, rowValue,  response);
       
    
    }

    public List getExportList() {
        return exportList;
    }

    public void setExportList(List exportList) {
        this.exportList = exportList;
    }

    public String exportToExcell() {
        try {

             if (exportList == null|| exportList.size() == 0) {
                 addScdpActionMessage("当前界面没有导出的记录!");
                
              }else{

                  outputExcel("省内分发单元.xls", this.exportList, this.response);
                 
            }
        //导出操作完成后，界面进行刷新
          listView();

        } catch (IOException e) {
            System.out.println("导出文件未成功" + e.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return SUCCESS;
    }

 

 /*
     * 完成将EXCE导入到数据库的操作 2008.03.21 赵祖龙
     *
     */

    public String importExceltoDb() throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        // 声明各列的头
        // 年度、省内分发单元代码、省内分发单元名称、省局代码、省局名称
        // 新开发时，此处需要修改----------

     this.beanprops = new String[] { "yxn", "sjid", "sjmc", "snffdyid", "snffdymc"};
     UserInfo userInfor = (UserInfo) findUserInfo();
        String userId = userInfor.getUserId();
        // 将当前Excel打开，得到其中数据保存到list中
        List listValue = null;
        try {

            listValue = parseExceltoList();

        } catch (IOException e) {
            System.out.println("读文件出错" + e.getMessage());
            addScdpActionMessage("读取Excel文件进出错!");
            return INPUT;
        }

        // 完成字段映射,得到可以批次保存到后台数据库的list

        List listBd = Bean2Bean(listValue);
       
        // 列表插入数据库
        if(listBd!=null){

            snffdySvc.insertBatch(listBd);         
        }
        System.out.println("batchSn="+batchSn);
        System.out.println("userId="+userId);
        //调后台数据库的后续处理
        snffdySvc.callProcAfterInsert(batchSn, "SNFFDY",userId);

        if(this.listErrMsgInfor==null){
           
        }else{
            String strErrMsg=null;
            for (int i=0;i<listErrMsgInfor.size();i++){
                strErrMsg = strErrMsg+""+listErrMsgInfor.get(i);
            }
            addScdpActionMessage(strErrMsg);
           
            return INPUT;
        }

        System.out.println("importExceltoDb----8--------------");
        addScdpActionMessage("保存成功");
        return INPUT;
    }

    /*
     * 将两个JavaBean进行映射，新模块，此处需要修改,需要加入业务判断 2008.03.21 赵祖龙
     *
     */
    public List Bean2Bean(List listValue) {
        List errList = null;
        java.util.List result = new java.util.ArrayList();
        java.util.List ErrList = new java.util.ArrayList();
        LazyDynaBean dbean;
        UserInfo userInfor = (UserInfo) findUserInfo();
        String userId = userInfor.getUserId();
        judgeStringUtil judgement = new judgeStringUtil();

        System.out.println("Bean2Bean----------1-----------listValue.size()="+listValue.size());
        for (int ii = 0; ii < listValue.size(); ii++) {
            DistSjSnffdy ffgxbd = new DistSjSnffdy();
            String strErrMsg = null;
            dbean = null;
            dbean = (LazyDynaBean) listValue.get(ii);
            ffgxbd.setSnffdyid((String) dbean.get("snffdyid"));
            System.out.println("snffdyid1111111="+dbean.get("snffdyid"));
            // 判断省内分发单元代码是否为数字
            if (!judgement.judgeStrIsNumber((String) dbean.get("snffdyid"))) {
                strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "分发单元代码不是数字!" + "***";
            }
            // 判断分发单元代码是否为8位
            if (!judgement.judgeStrLength((String) dbean.get("snffdyid"), 8)) {
                strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "分发单元代码不是8位" + "***";
            }
            System.out.println("yxn111888811111="+(String) dbean.get("yxn"));
            String strYXN =(String) dbean.get("yxn");
            ffgxbd.setYxn(Integer.valueOf(strYXN.trim()));
            System.out.println("yxn2222222222222="+strYXN);
            // 判断变动通知单号是否为数字
            if (!judgement.judgeStrIsNumber((String) dbean.get("yxn"))) {
                strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "有效年不是数字!" + "***";
            }
            // 判断变动通知单号是否为12位
            if (!judgement.judgeStrLength((String) dbean.get("yxn"), 4)) {
                strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "有效年不是4位" + "***";
            }
           
            ffgxbd.setImpuser(userId);
            ffgxbd.setBatchsn(batchSn);
            if (strErrMsg == null) {
                result.add(ffgxbd);
            } else {
                ErrList.add(strErrMsg);
            }

          
        }
        listErrMsgInfor = ErrList;
        return result;

    }

    /*
     * 将Excel解析成List 2008.03.20 赵祖龙
     *
     */
    public List parseExceltoList() throws IOException {
     System.out.println("parseExceltoList----1----------");
        List listValue = null;
        String path = getFileUploadPath();
        System.out.println("parseExceltoList----2----------");
        // 为了多文件上传
        for (int i = 0; i < doc.length; i++) {
         System.out.println("parseExceltoList----3----------");
            // 如果存在上传的文件
            if (doc[i] != null) {
                // 打开输入流
                FileInputStream input = new FileInputStream(doc[i]);
                // 此入添加将输入流转成list操作
                if (excelutil == null) {
                    excelutil = new Excelutil();
                }
                HSSFWorkbook wb = new HSSFWorkbook(input);

                listValue = excelutil.parseSheettoList(wb.getSheetAt(0), beanprops);
                // 关闭流
                input.close();

            }
        }
        return listValue;

    }
    /**
     * 获得文件上传路径
     *
     * @return
     */
    private String getFileUploadPath() {
     
        String path = request.getSession().getServletContext().getInitParameter("fileUploadPath");
        System.out.println("getFileUploadPath----1----------path="+path);
        if ((path == null) || path.trim().length() == 0) {
            path = this.request.getSession().getServletContext().getRealPath("//WEB-INF//upload//");
        }
        if (!path.endsWith(File.separator)) {
            path = path + File.separator;
        }
        File file = new File(path);
        if (!file.exists()) {
            file.mkdirs();
        }
        return path;
    }
   
    public void setServletRequest(HttpServletRequest argrequest) {
        setRequest(argrequest);

    }

 public HttpServletRequest getRequest() {
  return request;
 }