2 sinat 33415638 sinat_33415638 于 2016.01.20 13:56 提问

小白求助:poi生成excel速度过慢问题,该怎么优化

package poiMain;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Font;
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;

public class PoiGH3215 {
private static String fileName="";
public static String getCell(int x,int y) throws IOException{
DecimalFormat df = new DecimalFormat("#");

    InputStream myxls=new FileInputStream(fileName);
    @SuppressWarnings("resource")
    XSSFWorkbook wb =new XSSFWorkbook(myxls);
    XSSFSheet sheet=wb.getSheetAt(0);//第一个工作表
    XSSFRow row =sheet.getRow(x-1);//第x行
    XSSFCell cell =row.getCell((short)y-1);//第x行第y个元素
    if(cell==null){
        return null;
    }
     String cellValue = "";  

        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_STRING:  
            cellValue = cell.getRichStringCellValue().getString().trim();

            break;  
        case HSSFCell.CELL_TYPE_NUMERIC:  

            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
                SimpleDateFormat sdf = null;  
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
                        .getBuiltinFormat("h:mm")) {  
                    sdf = new SimpleDateFormat("HH:mm");  
                } else {// 日期  
                    sdf = new SimpleDateFormat("yyyy/MM/dd");  
                }  
                Date date = cell.getDateCellValue();  
                cellValue = sdf.format(date);  
            } else {
                cellValue = df.format(cell.getNumericCellValue()).toString(); 
            }
            break;  

        default:  
            cellValue = "";
        }
        return cellValue;  
}  
public static void ModelGH3215() throws IOException{

    @SuppressWarnings("resource")
    XSSFWorkbook wb =new XSSFWorkbook();
    Date d = new Date();
    DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    String fileName = df.format(d);
    FileOutputStream fileOut = new FileOutputStream("D:/输出/工行3215/" + fileName + "_main" + ".xls");
    XSSFSheet sheet=wb.createSheet();

    Font font = wb.createFont();
    XSSFCellStyle style = wb.createCellStyle(); //新建单元格样式  
    font.setFontName("宋体");    
    font.setFontHeightInPoints((short) 12);//设置字体大小      
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示   
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //下边框
    style.setFont(font);
    //设置单元格列宽
    sheet.setColumnWidth(0, 100 * 100);
    sheet.setColumnWidth(1, 100 * 30);
    sheet.setColumnWidth(3, 100 * 30);
    sheet.setColumnWidth(4, 100 * 30);
    sheet.setColumnWidth(9, 100 * 130);
    sheet.setColumnWidth(20, 100 * 40);
    XSSFRow row =sheet.createRow((short)0);
    XSSFCell cell=row.createCell((short)0);
    row.setHeight((short) 700);//设置行高

    cell.setCellValue("银行科目");
    row.getCell((short)0).setCellStyle(style);
    row.createCell((short)1).setCellValue("币别");
    row.getCell((short)1).setCellStyle(style);
    row.createCell((short)2).setCellValue("序列号");
    row.getCell((short)2).setCellStyle(style);
    row.createCell((short)3).setCellValue("日期");
    row.getCell((short)3).setCellStyle(style);
    row.createCell((short)4).setCellValue("业务日期");
    row.getCell((short)4).setCellStyle(style);
    row.createCell((short)5).setCellValue("凭证字");
    row.getCell((short)5).setCellStyle(style);
    row.createCell((short)6).setCellValue("凭证号");
    row.getCell((short)6).setCellStyle(style);
    row.createCell((short)7).setCellValue("凭证年");
    row.getCell((short)7).setCellStyle(style);
    row.createCell((short)8).setCellValue("凭证期间");
    row.getCell((short)8).setCellStyle(style);
    row.createCell((short)9).setCellValue("摘要");
    row.getCell((short)9).setCellStyle(style);
    row.createCell((short)10).setCellValue("对方科目");
    row.getCell((short)10).setCellStyle(style);
    row.createCell((short)11).setCellValue("结算方式");
    row.getCell((short)11).setCellStyle(style);
    row.createCell((short)12).setCellValue("结算号");
    row.getCell((short)12).setCellStyle(style);
    row.createCell((short)13).setCellValue("");
    row.getCell((short)13).setCellStyle(style);
    row.createCell((short)14).setCellValue("");
    row.getCell((short)14).setCellStyle(style);
    row.createCell((short)15).setCellValue("汇率");
    row.getCell((short)15).setCellStyle(style);
    row.createCell((short)16).setCellValue("附件数");
    row.getCell((short)16).setCellStyle(style);
    row.createCell((short)17).setCellValue("汇率类型");
    row.getCell((short)17).setCellStyle(style);
    row.createCell((short)18).setCellValue("备注");
    row.getCell((short)18).setCellStyle(style);
    row.createCell((short)19).setCellValue("对应外币");
    row.getCell((short)19).setCellStyle(style);
    row.createCell((short)20).setCellValue("对应外币金额");
    row.getCell((short)20).setCellStyle(style);

    int j = 1;int sum=0;int a=0;int s=getNum();
    ArrayList<String> date1 = new ArrayList();
    ArrayList<Integer> value1 = new ArrayList();
    ArrayList<String> date2 = new ArrayList();
    ArrayList<Integer> value2 = new ArrayList();
    for(int i=5;i<s+2;i++){
        if(PoiGH3215.getCell(i,4).equals("转款")&&(PoiGH3215.getCell(i,8).equals("31001547840050012398"))){
            System.out.println("get");
            XSSFRow row2 =sheet.createRow((short)j++);
            row2.createCell((short)14).setCellValue(getCell(i,5));
            row2.createCell((short)13).setCellValue(getCell(i,7));
            row2.createCell((short)3).setCellValue(getCell(i,2));//日期
            row2.createCell((short)4).setCellValue(getCell(i,2));//日期
            row2.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行(8980)-备汇缴");//第一列
            row2.createCell((short)9).setCellValue("收转款,建行怒江2398转入工行侨香");//摘要
        }else if(PoiGH3215.getCell(i,4).equals("收付")&&(!PoiGH3215.getCell(i,5).equals(PoiGH3215.getCell(i,6)))){
            XSSFRow row3 =sheet.createRow((short)j);
            row3.createCell((short)9).setCellValue("付移动手机充值款");
            row3.createCell((short)13).setCellValue(getCell(i,7));//添加值
            row3.createCell((short)14).setCellValue(getCell(i,5));//添加值
            row3.createCell((short)3).setCellValue(getCell(j,2));
            row3.createCell((short)4).setCellValue(getCell(j,2));
            row3.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行(8980)-备汇缴");
            row3.createCell((short)14).setCellValue(getCell(i,5));
            row3.createCell((short)9).setCellValue("收还款退款");//摘要
        }   else if(PoiGH3215.getCell(i,4).equals("收付")&&(PoiGH3215.getCell(i,5).equals(PoiGH3215.getCell(i,6)))){
            XSSFRow row3 =sheet.createRow((short)j);
            row3.createCell((short)9).setCellValue("付移动手机充值款");
            row3.createCell((short)13).setCellValue(getCell(i,7));
            row3.createCell((short)14).setCellValue(getCell(i,5));//添加值
            row3.createCell((short)3).setCellValue(getCell(j,2));
            row3.createCell((short)4).setCellValue(getCell(j,2));
            row3.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行(8980)-备汇缴");
            row3.createCell((short)14).setCellValue(getCell(i,5));
            row3.createCell((short)9).setCellValue("收收付");//摘要
        }   else if(PoiGH3215.getCell(i,4).equals("拉卡拉代付款")&&(!PoiGH3215.getCell(i,9).equals("拉卡拉支付有限公司客户备付金"))&&(!PoiGH3215.getCell(i,9).equals("拉卡拉支付有限公司(客户备付金)"))&&(!PoiGH3215.getCell(i,9).equals("拉卡拉网络技术有限公司北京分公司的数(合计数)"))){
            //System.out.println(i);
            date1.add(PoiGH3215.getCell(i,2));
            value1.add(Integer.parseInt(PoiGH3215.getCell(i,5)));
        }
    }
    for(int c=0;c<date1.size();c++){
        try {
            if(date1.get(c).equals(date1.get(c+1))){

            a=value1.get(c);
            sum=a+sum;
        }else {
            a= value1.get(c);
            sum=a+sum;
            date2.add(date1.get(c));
            value2.add(sum);
            sum=0;
        }
        } catch (Exception e) {
            a=value1.get(c);
            sum=a+sum;
            date2.add(date1.get(c));
            value2.add(sum);
        }
        }
    for(int k=0;k<date2.size();k++){
         XSSFRow row3 =sheet.createRow((short)j+k);
    row3.createCell((short)14).setCellValue(value2.get(k));
    row3.createCell((short)9).setCellValue("付代付款");
    row3.createCell((short)3).setCellValue(date2.get(k));
    row3.createCell((short)4).setCellValue(date2.get(k));
    row3.createCell((short)0).setCellValue("1021.01.10|工行广州白云路支行(8980)-备汇缴");
    }
    for(int m=1;m<j+date2.size();m++){
        XSSFRow row2 =sheet.getRow((short)m);
        row2.createCell((short)1).setCellValue("RMB|人民币");
        row2.createCell((short)15).setCellValue("1");
        row2.createCell((short)16).setCellValue("0");
        row2.createCell((short)17).setCellValue("01|公司汇率");
        row2.createCell((short)20).setCellValue("0");
    }
    wb.write(fileOut);
    fileOut.close();
    System.out.println("输入完毕");
    }


public static int getNum() throws IOException{
        synchronized (fileName) {
            while("".equals(fileName)){
                System.out.println("请输入您的文件路径:");
                Scanner sc = new Scanner(System.in);
                fileName = sc.nextLine();
                sc.close();
            }
        }
    InputStream myxls=new FileInputStream(fileName);
    @SuppressWarnings("resource")
    XSSFWorkbook wb =new XSSFWorkbook(myxls);
    XSSFSheet sheet=wb.getSheetAt(0);//第一个工作表
    return sheet.getLastRowNum();   
}

}

我要处理的表有6万多行,我这个处理一个200行的都要1分钟

7个回答

fengzinihuai
fengzinihuai   2016.01.20 14:40
    文本生成字段间以tab分割   行间以\r\n分隔~~~~   结果复制粘贴到 excel内~~~搞定
Royal_lr
Royal_lr   Ds   Rxr 2016.01.20 14:41

6万,,慢也很正常,,

rui888
rui888   Ds   Rxr 2016.01.20 14:41

可以考虑用个excel模板,然后往里面写数据,而不是通过代码一个个创建cell

其次行或者单元格用拷贝 的方式看看

sinat_33415638
sinat_33415638   2016.01.20 16:19

就是看看能不能优化一下,让程序跑快一点

angus_Lucky
angus_Lucky   2016.01.20 16:58

应该是你的程序代码,数据源获得慢了吧~也有可能楼上说的,你先弄个模板,然后填充数据

不然你试试jxl.这个我用,觉得很快

dsfyyh
dsfyyh   2016.01.22 11:16
dsfyyh
dsfyyh 回复勤奋的埃菲尔: 最好是先优化sql,再优化程序
接近 2 年之前 回复
u014547540
u014547540 我的和这个网址代码逻辑差不多的,但是数据库获取数据太慢,也试着降低查询次数,但依旧很慢,不知如何优化了~~
接近 2 年之前 回复
wqqqianqian
wqqqianqian   2017.06.16 17:16

用pageoffice生成excel把。PageOffice对Excel的接口是做过深度优化的,填充上万行数据也能瞬间完成。可以在线打开编辑保存。兼容性很好

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!