excel超过java 65535 条数据 poi SXSSFWorkbook

package aaa.utils.scanner.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import next.entity.Option;
/**
 * 
 * 大数据导出Excel工具类
 * @author: wqq
 * @date:2018年9月14日
 */
public class ExcelExport {
    /** 日期格式 yyyy-MM-dd*/
    private static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    /**
     * 单元格映射
     */
    public static class CellMap {
        private String title;// 标题
        private String property;// 属性

        public CellMap(String title, String property) {
            this.title = title;
            this.property = property;
        }

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public String getProperty() {
            return property;
        }

        public void setProperty(String property) {
            this.property = property;
        }

    }
    /**
     * 导出数据兼容以前的
     * @param title
     * @param headMap
     * @param ja
     * @param response
     * @throws Exception
     */
    public static void downloadExcelFile(String title, Map<String, String> headMap,List<?> dataList, HttpServletResponse response) throws Exception{
        parseDownloadExcelFile(title, headMap,dataList,response);
    }
    /**
     * 以 SXSSF 导出2007(增强版)
     * <功能详细描述>
     * @param response
     * @param excelName
     * @param cellMapList
     * @param dataList
     * @param foot
     * @throws Exception
     */
    public static void exportExcel_2007_SXSSF(HttpServletResponse response,String excelName,List<CellMap> cellMapList,List<?> dataList,List<HashMap<String, Object>> foot) throws Exception{
        createExcel(response, excelName, cellMapList,dataList,foot);
    }
    /**
     * 以 SXSSF 导出2007  无页脚
     * @param response
     * @param excelName
     * @param cellMapList
     * @param dataList
     * @throws Exception
     */
    public static void exportExcel_2007_SXSSF(HttpServletResponse response,String excelName,List<CellMap> cellMapList,List<?> dataList) throws Exception{
        createExcel(response, excelName, cellMapList,dataList,null);
    }
    public static void parseDownloadExcelFile(String title, Map<String, String> headMap,List<?> dataList,HttpServletResponse response) throws Exception{
        List<CellMap> cellMapList=new ArrayList<CellMap>();
        for(String key:headMap.keySet()){
            CellMap CellMap=new CellMap(headMap.get(key),key);
            cellMapList.add(CellMap);
        }
        createExcel(response, title, cellMapList,dataList,null);
    }
    /**
     * 导出Excel
     * @param cellMapList 单元格映射列表
     * @param dataList 数据列表
     * @param rowAccessWindowSize 内存中缓存记录数
     * @param out 输出流
     * @throws Exception
     */
    public static void exportSXSSFExcel(String sheetName, List<CellMap> cellMapList, List<?> dataList, int rowAccessWindowSize, OutputStream out) throws Exception {
        @SuppressWarnings("resource")
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
        Sheet sheet = workbook.createSheet(sheetName);
        Row row = null;
        Cell cell = null;
        if (cellMapList == null || cellMapList.size() <= ) {
            throw new Exception("cellMapList不能为空或小于等于0");
        }
        int rowIndex = ;
        // 标题
        Font titleFont = workbook.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleCellStyle.setFont(titleFont);
        row = sheet.createRow(rowIndex++);
        int cellSize = cellMapList.size();
        for (int i = ; i < cellSize; i++) {
            CellMap cellMap = cellMapList.get(i);
            String title = cellMap.getTitle();
            cell = row.createCell(i);
            cell.setCellStyle(titleCellStyle);
            cell.setCellValue(title);
            if (title != null) {
                sheet.setColumnWidth(i, title.getBytes().length *  * );
            }
        }
        // 数据
        CellStyle dataCellStyle = workbook.createCellStyle();
        dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        int rowSize = (dataList == null) ?  : dataList.size();
        for (int i = rowIndex; i < rowSize + rowIndex; i++) {
            Object obj = dataList.get(i - rowIndex);
            row = sheet.createRow(i);
            for (int j = ; j < cellSize; j++) {
                CellMap cellMap = cellMapList.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(dataCellStyle);
                String property = cellMap.getProperty();
                if(property.equals("rowNumber") || StringUtils.isEmpty(property)){
                    cell.setCellValue(i);
                }else{
                    String propertyValue = getPropertyValue(obj, property);
                    cell.setCellValue(propertyValue);
                    if (propertyValue != null) {
                        int columnWidth = sheet.getColumnWidth(j);
                        int propertyValueLength = propertyValue.getBytes().length *  * ;
                        if (columnWidth < propertyValueLength) {
                            sheet.setColumnWidth(j, propertyValueLength);
                        }
                    }
                }

            }
        }
        workbook.write(out);
    }


    /**
     * 导出Excel
     * @param cellMapList 单元格映射列表
     * @param dataList 数据列表
     * @param rowAccessWindowSize 内存中缓存记录数
     * @param out 输出流
     * @throws Exception
     */
    public static void exportSXSSFExcelFromMapData(String sheetName, List<CellMap> cellMapList, List<Map<String,String>> dataList, int rowAccessWindowSize, OutputStream out) throws Exception {
        @SuppressWarnings("resource")
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
        Sheet sheet = workbook.createSheet(sheetName);
        Row row = null;
        Cell cell = null;
        if (cellMapList == null || cellMapList.size() <= ) {
            throw new Exception("cellMapList不能为空或小于等于0");
        }
        int rowIndex = ;
        // 标题
        Font titleFont = workbook.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleCellStyle.setFont(titleFont);
        row = sheet.createRow(rowIndex++);
        int cellSize = cellMapList.size();
        for (int i = ; i < cellSize; i++) {
            CellMap cellMap = cellMapList.get(i);
            String title = cellMap.getTitle();
            cell = row.createCell(i);
            cell.setCellStyle(titleCellStyle);
            cell.setCellValue(title);
            if (title != null) {
                sheet.setColumnWidth(i, title.getBytes().length *  * );
            }
        }
        // 数据
        CellStyle dataCellStyle = workbook.createCellStyle();
        dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        int rowSize = (dataList == null) ?  : dataList.size();
        for (int i = rowIndex; i < rowSize + rowIndex; i++) {
            Map<String,String> obj = dataList.get(i - rowIndex);
            row = sheet.createRow(i);
            for (int j = ; j < cellSize; j++) {
                CellMap cellMap = cellMapList.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(dataCellStyle);
                String property = cellMap.getProperty();
                if(property.equals("rowNumber") || StringUtils.isEmpty(property)){
                    cell.setCellValue(i);
                }else{
//                  String propertyValue = getPropertyValue(obj, property);
                    String propertyValue = obj.get(property);
                    cell.setCellValue(propertyValue);
                    if (propertyValue != null) {
                        int columnWidth = sheet.getColumnWidth(j);
                        int propertyValueLength = propertyValue.getBytes().length *  * ;
                        if (columnWidth < propertyValueLength) {
                            sheet.setColumnWidth(j, propertyValueLength);
                        }
                    }
                }

            }
        }
        workbook.write(out);
    }

    /**
     * 获取属性值
     * @param obj
     * @param property
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    private static String getPropertyValue(Object obj, String property) throws Exception {
        if (obj instanceof Map)
        {
            Object val = ((Map<String,Object>)obj).get(StringUtils.upperCase(property));
            if (val==null)
            {
                return "";
            }
            return val.toString();
        }
        Object result = null;
        String str = "";
        Class<?> clazz = obj.getClass();
        if (property == null || "".equals(property)) {
            return "";
        }
        Method readMethod = clazz.getMethod("get" + property.substring(, ).toUpperCase() + property.substring());
        if (readMethod != null) {
            result = readMethod.invoke(obj);
        }
        if (result != null) {
            if (result.getClass() == Date.class) {
                str = dateFormat.format(result);
            } else {
                str = result.toString();
            }
        } else {
            str = "";
        }
        return str;
    }


    public static void main(String[] args) {
         List<CellMap> cellMapList = new ArrayList<CellMap>();
         cellMapList.add(new CellMap("单元格1", "index"));
//         cellMapList.add(new CellMap("单元格21111111111", "cell2"));
//         cellMapList.add(new CellMap("单元格3", "a"));

         List<Option> list = new ArrayList<Option>();
         Option Option=new Option();
         Option.setIndex("2");
         for(int i=;i<;i++) {
            list.add(Option);
         }
         list.add(Option);

         try {
             ExcelExport.exportSXSSFExcel("昂",cellMapList, list, , new FileOutputStream(new File("C:\\Users\\dap\\Desktop\\新建文件夹\\test.xlsx")));
         } catch (Exception e) {
             e.printStackTrace();
         }
    }

    /**
     * 
     * 填充excel数据
     * <功能详细描述>
     * @param dataCellStyle
     * @param cellSize
     * @param sheet
     * @param rowIndex
     * @param workbook
     * @param response
     * @param excelName
     * @param cellMapList
     * @param dataList
     * @return
     * @throws Exception
     * @see [类、类#方法、类#成员]
     */
    public static int fillExcel_2007_SXSSF(CellStyle dataCellStyle,int cellSize,Sheet sheet,int rowIndex,SXSSFWorkbook workbook,HttpServletResponse response,String excelName, List<CellMap> cellMapList, List<?> dataList) throws Exception{
        Row row = null;
        Cell cell = null;
        int rowSize = (dataList == null) ?  : dataList.size();
        for (int i = rowIndex; i < rowSize + rowIndex; i++) {
            Object obj = dataList.get(i - rowIndex);
            row = sheet.createRow(i);
            for (int j = ; j < cellSize; j++) {
                CellMap cellMap = cellMapList.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(dataCellStyle);
                String property = cellMap.getProperty();
                if(property.equals("rowNumber") || StringUtils.isEmpty(property)){
                    cell.setCellValue(i);
                }else{
                    String propertyValue = getPropertyValue(obj, property);
                    cell.setCellValue(propertyValue);
                    if (propertyValue != null) {
                        int columnWidth = sheet.getColumnWidth(j);
                        int propertyValueLength = propertyValue.getBytes().length *  * ;
                        if (columnWidth < propertyValueLength) {
//                            sheet.setColumnWidth(j, propertyValueLength);
                        }
                    }
                }
            }
        }
        return rowSize + rowIndex;
    }
    /**
     * 创建excel
     * @param response
     * @param excelName
     * @param cellMapList
     * @param dataList
     * @param footers
     * @throws Exception
     */
    public static void createExcel(HttpServletResponse response,String excelName, List<CellMap> cellMapList,List<?> dataList,List<HashMap<String, Object>> footers) throws Exception{
        //参数校验
        if (response==null||CollectionUtils.isEmpty(cellMapList))
        {
            throw new Exception("cellMapList不能为空或小于等于0");
        }
        if (StringUtils.isBlank(excelName))
        {
            excelName = UUID.randomUUID().toString();
        }
        @SuppressWarnings("resource")
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        workbook.setCompressTempFiles(true);
        Sheet sheet = workbook.createSheet();
        Row row = null;
        Cell cell = null;
        int rowIndex = ;
        // header 标题
        Font titleFont = workbook.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleCellStyle.setFont(titleFont);
        row = sheet.createRow(rowIndex++);
        // 数据列数
        int cellSize = cellMapList.size();
        for (int i = ; i < cellSize; i++) {
            CellMap cellMap = cellMapList.get(i);
            String title = cellMap.getTitle();
            cell = row.createCell(i);
            cell.setCellStyle(titleCellStyle);
            cell.setCellValue(title);
            if (title != null) {
                sheet.setColumnWidth(i, title.getBytes().length *  * );
            }
        }
        CellStyle dataCellStyle = workbook.createCellStyle();
        dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        int rowSize = (dataList == null) ?  : dataList.size();
        for (int i = rowIndex; i < rowSize + rowIndex; i++) {
            Object obj = dataList.get(i - rowIndex);
            row = sheet.createRow(i);
            for (int j = ; j < cellSize; j++) {
                CellMap cellMap = cellMapList.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(dataCellStyle);
                String property = cellMap.getProperty();
                if(property.equals("rowNumber") || StringUtils.isEmpty(property)){
                    cell.setCellValue(i);
                }else{
                    String propertyValue = getPropertyValue(obj, property);
                    cell.setCellValue(propertyValue);
                    if (propertyValue != null) {
                        int columnWidth = sheet.getColumnWidth(j);
                        int propertyValueLength = propertyValue.getBytes().length *  * ;
                        if (columnWidth < propertyValueLength) {
                            sheet.setColumnWidth(j, propertyValueLength);
                        }
                    }
                }

            }
        }
      //footer 尾部
//        if (CollectionUtils.isNotEmpty(footers))
//        {
//          //footers数据样式
//            CellStyle dataCellStyle = workbook.createCellStyle();
//            dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            rowIndex=fillExcel_2007_SXSSF(dataCellStyle,cellSize,sheet, rowIndex, workbook, response, excelName, cellMapList, footers);
//        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); 
        response.setHeader("Content-Disposition", "attachment;filename="+ new String((excelName + ".xlsx").getBytes(), "iso-8859-1"));
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
        workbook.dispose();
    }
}
excel超过java 65535 条数据 poi SXSSFWorkbook

继续阅读

关于Gradle配置的小结

Java小案例——随机数猜测随机数猜测

nginx location中斜线的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method
