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

最新推荐文章于 2024-05-31 11:26:07 发布

traceofsun

最新推荐文章于 2024-05-31 11:26:07 发布

阅读量4k

点赞数 2

分类专栏： JAVA WEB应用开发相关文章标签： excel java string list null path

本文链接：https://blog.csdn.net/traceofsun/article/details/5842414

版权

这是一个关于如何使用JAVA处理超过60000条数据并写入EXCEL的示例代码，主要通过Apache POI库实现。程序会根据Excel的最大行数限制动态分页写入数据，确保不会因行数过多导致写入失败。同时，提供了组织数据和导出到Excel的函数。

摘要由CSDN通过智能技术生成

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) {