<![CDATA[Heck's Blog]]>

<![CDATA[Heck's Blog]]> https://www.heckjj.com/index.php zh-cn https://www.heckjj.com/post// <![CDATA[poi导出excel内存溢出问题解决]]> Heck <@hecks.tk> Fri, 24 Sep 2021 03:56:17 +0000 https://www.heckjj.com/post// 写入excel时使用 SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook , 1000);只在内存中留1000行，不会占用过多的内存。下面只贴了部分代码。

public static void createExcelByTrade(List mergeCellConfigList ,  Map cellConfMap , List dataList ,String tempPath , String fileName  ) {
        try {
          int  excelRowNum  = 0;
          short fontSize = 12;
          // 创建新的Excel 工作簿
          XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
          SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook , 1000);

          Sheet sheet = workbook.createSheet(fileName);
          // 设置合并表头
          setMergeHeaderCellByTrade(   workbook,   sheet,    mergeCellConfigList ,  excelRowNum,   fontSize );
          excelRowNum ++ ;
          excelRowNum ++ ;

          //设置表头
          setHeaderCell(  workbook,   sheet,  cellConfMap ,  excelRowNum,   fontSize );
          excelRowNum ++ ;     //查询数据库中所有的数据
          setCellData(   workbook,   sheet, cellConfMap,   dataList,   excelRowNum,   fontSize);
          // 新建一输出文件流
          FileOutputStream fOut = new FileOutputStream(tempPath);
          // 把相应的Excel 工作簿存盘
          workbook.write(fOut);
          //清空缓冲区数据
          fOut.flush();
          // 操作结束，关闭文件
          fOut.close();
          System.out.println("文件生成...");
        } catch (Exception e) {
            e.printStackTrace();
          System.out.println("已运行 xlCreate() : " + e);
        }
      }

导出excel时进行合并单元格及样式设置。package com.heckjj.fiss.otcdownload;

import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.bstek.dorado.common.UserConfig;
import com.sfit.fiss.util.ExcelUtil;
import com.sfit.framework.sp.SpCondition;

public class ExcelExportUtil {


    public static void main(String[] args) throws SQLException {

        String  tempFilePath = "D:/Eclipse/WorkSpaceGanyMede/otcreport/temp/report/";
        List dataList = new  ArrayList();
        //导出数据
        exportPositionInfoData(dataList , tempFilePath) ;

    }
/**
     * 导出数据
     * @param dataList
     */
    static String  exportPositionInfoData( List dataList, String  tempFilePath) {
        List mergeCellConfigList = new ArrayList();
          mergeCellConfigList.add(new String[] {"主体信息" , "2"  , "HeaderStyle_2"  });
          mergeCellConfigList.add(new String[] {"对手方信息" , "2"  , "HeaderStyle_2"  });
          mergeCellConfigList.add(new String[] {"物信息" , "11"  , "HeaderStyle_2"  });
          mergeCellConfigList.add(new String[] {"仓信息" , "8"  , "HeaderStyle_2"  });


        //设置表头
          Map cellConfMap = new LinkedHashMap();
           cellConfMap.put("MAINBODYNAME", new String[] { "ASSS", "HeaderStyle_2", "DataStyle_TEXT_LC", "30" });
           cellConfMap.put("NOCID", new String[] { "ASD", "HeaderStyle_2", "DataStyle_TEXT_LC", "30" });
           cellConfMap.put("ANALOGUENAME", new String[] { "ASDASD", "HeaderStyle_2", "DataStyle_TEXT_LC", "30" });
           cellConfMap.put("ANALOGUENOCID", new String[] { "ASDASDSD", "HeaderStyle_2", "DataStyle_TEXT_LC", "35" });
           cellConfMap.put("TRANSCONFIRNUMBER", new String[] { "ASDASDAS", "HeaderStyle_2", "DataStyle_TEXT_LC", "20" });
           cellConfMap.put("TRANSCONFIRTIME", new String[] { "DFGDFG", "HeaderStyle_2", "DataStyle_TEXT_LC", "15" });

           SpCondition sc = new SpCondition();
           String fileName = "数据";
           String tempPath = tempFilePath +"OTCPositionData.xlsx";
           createExcel(  mergeCellConfigList , cellConfMap ,   dataList , tempPath, fileName);
           return  tempPath;
    }


    @SuppressWarnings("deprecation")
    public static void createExcel(List mergeCellConfigList ,  Map cellConfMap , List dataList ,String tempPath , String fileName  ) {
        try {
          int  excelRowNum  = 0;
          short fontSize = 12;
          // 创建新的Excel 工作簿
          XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
          SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 1000);
          Sheet sheet = workbook.createSheet(fileName);
          // 设置合并表头
          setMergeHeaderCell(   workbook,   sheet,    mergeCellConfigList ,  excelRowNum,   fontSize );
          excelRowNum ++ ;
          //设置表头
          setHeaderCell(  workbook,   sheet,  cellConfMap ,  excelRowNum,   fontSize );
          excelRowNum ++ ;
          //设置cell自动宽度
          //setAutoSizeColumn(sheet, cellConfMap.size());
          //查询数据库中所有的数据
          setCellData(   workbook,   sheet, cellConfMap,   dataList,   excelRowNum,   fontSize);
          // 新建一输出文件流
          FileOutputStream fOut = new FileOutputStream(tempPath);
          // 把相应的Excel 工作簿存盘
          workbook.write(fOut);
          //清空缓冲区数据
          fOut.flush();
          // 操作结束，关闭文件
          fOut.close();
          System.out.println("文件生成...");
        } catch (Exception e) {
            e.printStackTrace();
          System.out.println("已运行 xlCreate() : " + e);
        }
      }

    //向单元格填充数据
    private static void setCellData( SXSSFWorkbook workbook, Sheet sheet, Map cellConfMap,  List list, int excelRowNum, short fontSize) throws SQLException, Exception {
        Row row = null ;
        Cell cell = null ;
        //生成样式
        Map   styleMap = new HashMap();
        if(cellConfMap!=null && !cellConfMap.isEmpty()) {
            for(String keyStr:  cellConfMap.keySet() ) {
                styleMap.put(keyStr, ExcelExportUtil.createCellStyle(workbook, cellConfMap.get(keyStr)[2], fontSize));

            }
        }


        if(list != null &&  !list.isEmpty()) {
            ResultSet rs = (ResultSet) list.get(0);
            if(cellConfMap != null   && !cellConfMap.isEmpty()) {
                while(rs.next()){
                     row =  sheet.createRow(excelRowNum);
                     int  cellNum = 0;
                     for (String  keyStr:   cellConfMap.keySet()) {
                             cell =  row.createCell(cellNum);
                              cell.setCellStyle(styleMap.get(keyStr));
                             cell.setCellValue(rs.getString(keyStr));
                             cellNum++;
                     }
                   excelRowNum ++ ;
               }
            }
        }

    }

    /**
     * 设置普通表头信息

     * @param workbook
     * @param sheet
     * @param cellConfMap
     * @param excelRowNum
     * @param fontSize
     * @throws Exception
     */
    private static void setHeaderCell( SXSSFWorkbook workbook, Sheet sheet, Map cellConfMap ,int excelRowNum, short fontSize ) throws Exception {
         Row row;
         Cell cell;
        XSSFCellStyle style;
        row =  sheet.createRow(excelRowNum);
         if(cellConfMap != null &&  !cellConfMap.isEmpty() ) {
             int  cellNum = 0;
            for(String keyStr: cellConfMap.keySet()) {

                cell = row.createCell(cellNum);
                cell.setCellValue(cellConfMap.get(keyStr)[0]); // 表头列名
                // 设置类表宽度
                sheet.setColumnWidth(cellNum, 256 * Integer.parseInt((cellConfMap.get(keyStr)[3] != null && !"".equals(cellConfMap.get(keyStr)[3])) ? cellConfMap.get(keyStr)[3] : "10"));

                // 更具样式类型设置表头样式HeaderStyle
                String headerStyleTypeStr = cellConfMap.get(keyStr)[1];
                if (headerStyleTypeStr == null && "".equals(headerStyleTypeStr)) {
                    headerStyleTypeStr = "HeaderStyle";
                }

                style =  createCellStyle(workbook, headerStyleTypeStr, fontSize);
                cell.setCellStyle(style);
                cellNum++;
            }
         }
    }

    /**
     * 生成成交的合并表头信息

     * @param workbook
     * @param sheet
     * @param mergeCellConfigList
     * @param excelRowNum
     * @param fontSize
     * @throws Exception
     */
    private static void setMergeHeaderCellByTrade( SXSSFWorkbook workbook, Sheet sheet,  List mergeCellConfigList ,int excelRowNum, short fontSize ) throws Exception {
         Row row = null ;
        int startCellIndex = 0;
        int endCellIndex = 0;
         Cell cell = null;
        XSSFCellStyle style = null;
        if (mergeCellConfigList != null && !mergeCellConfigList.isEmpty()) {
             row =  sheet.createRow(excelRowNum);
                  for (String[] mergeParamItem : mergeCellConfigList) {
                    if (endCellIndex != 0) {
                        startCellIndex = (endCellIndex + 1);
                    }
                     endCellIndex = (startCellIndex + Integer.parseInt(mergeParamItem[1]) - 1);
                    if(mergeParamItem[0] != null &&  "交易信息".equals(mergeParamItem[0])){
                          sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, startCellIndex, endCellIndex));
                     }else  {
                        sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum +1, startCellIndex, endCellIndex));
                     }
                    endCellIndex = (startCellIndex + Integer.parseInt(mergeParamItem[1]) - 1);
                    cell =  row.createCell(startCellIndex  );
                     style =  ExcelExportUtil.createCellStyle(workbook, "HeaderStyle", fontSize);
                     cell.setCellStyle(style);
                     cell.setCellValue(mergeParamItem[0]);
                 }
             }

        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 0, 1));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 2, 3));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 4, 5));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 6, 7));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 8, 9));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 10, 10));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 11, 34));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 35, 36));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 37, 41));

            excelRowNum=excelRowNum+1;
             row =  sheet.createRow(excelRowNum);

             cell = row.createCell(11 );
             style =  ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
             cell.setCellStyle(style);
             cell.setCellValue("交易编码");


             cell = row.createCell(12);
             style =  ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
             cell.setCellStyle(style);
             cell.setCellValue("时间信息");


             cell = row.createCell(17);
             style =  ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
             cell.setCellStyle(style);
             cell.setCellValue("产品信息及标的物信息");


             cell = row.createCell(29);
             style =  ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
             cell.setCellStyle(style);
             cell.setCellValue("价格及价值信息");


            sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, 12, 16));
        sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, 17, 28));
        sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, 29, 34));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 0, 1));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 2, 3));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 4, 5));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 6, 7));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 8, 9));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 10, 10));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 11, 11));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 12, 16));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 17, 28));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 29, 34));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 35, 36));
        ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 37, 41));


    }
    /**
     * 设置表头信息
     * @param workbook
     * @param sheet
     * @param mergeCellConfigList
     * @param excelRowNum
     * @param fontSize
     * @throws Exception
     */
    private static void setMergeHeaderCell( SXSSFWorkbook workbook, Sheet sheet,  List mergeCellConfigList ,int excelRowNum, short fontSize ) throws Exception {
         Row row = null ;
        int startCellIndex = 0;
        int endCellIndex = 0;
         Cell cell = null;
        if (mergeCellConfigList != null && !mergeCellConfigList.isEmpty()) {
                  row =  sheet.createRow(excelRowNum);
                  for (String[] mergeParamItem : mergeCellConfigList) {
                    if (endCellIndex != 0) {
                        startCellIndex = (endCellIndex + 1);
                    }
                    endCellIndex = (startCellIndex + Integer.parseInt(mergeParamItem[1]) - 1);
                    sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, startCellIndex, endCellIndex));
                    cell =   row.createCell(startCellIndex);
                     cell.setCellStyle(ExcelExportUtil.createCellStyle(workbook, mergeParamItem[2],  fontSize));
                   cell.setCellValue(mergeParamItem[0]);



                }
            }
    }




    private static void setAutoSizeColumn(Sheet sheet , int cellNum) {
        //设置列宽
           if(sheet!= null ) {
               for(int   index = 0 ; index<=cellNum ; index++ ) {
                   sheet.autoSizeColumn(index, true);
                   //sheet.setColumnWidth(index,sheet.getColumnWidth(index));
               }

           }
     }




    /* 更具单元格样式类型生成相应的单元格样式 */
    public static XSSFCellStyle createCellStyle(SXSSFWorkbook workbook, String styleType, short fontSize)
            throws Exception {

        // 设置数据类型
        XSSFDataFormat dataFormat = (XSSFDataFormat) workbook.createDataFormat();
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        if (styleType != null && "HeaderStyle".equals(styleType)) { // 表头数据格式 HeaderStyle
            /*
             * // 背景色 style.setFillForegroundColor((short) 11);
             * style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
             * style.setFillBackgroundColor((short) 11);
             */

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            /*style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());*/

        } else if (styleType != null && "HeaderStyle_1".equals(styleType)) { // 表头数据格式 HeaderStyle1

            // 背景色

            /*
             * style.setFillForegroundColor((short) 8);
             * style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
             * style.setFillBackgroundColor((short) 8);
             */
            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        } else if (styleType != null && "HeaderStyle_2".equals(styleType)) { // 表头数据格式 HeaderStyle2
            // 背景色


            /*  style.setFillForegroundColor((short) 9);
              style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
              style.setFillBackgroundColor((short) 9);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
             style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        } else if (styleType != null && "HeaderStyle_3".equals(styleType)) { // 表头数据格式 HeaderStyle3
            // 背景色

            /*
             * style.setFillForegroundColor((short) 10);
             * style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
             * style.setFillBackgroundColor((short) 10);
             */
            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        }  else if (styleType != null && "DataStyle_TEXT".equals(styleType)) { // 数据单元格样式文本
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        } else if (styleType != null && "DataStyle_TEXT_LC".equals(styleType)) { // 数据单元格样式文本水平靠左
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 水平靠左
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        } else if (styleType != null && "DataStyle_TEXT_CC".equals(styleType)) { // 数据单元格样式文本
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        } else if (styleType != null && "DataStyle_TEXT_RC".equals(styleType)) { // 数据单元格样式文本
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平靠右
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        } else if (styleType != null && "DataStyle_TEXT_LC_LINE".equals(styleType)) { // 数据单元格样式文本水平靠左
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 水平靠左
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(false);// 设置不自动换行

            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        } else if (styleType != null && "DataStyle_NUMBER".equals(styleType)) { // 数据单元格样式数字格式整数
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("#,#0"));

        } else if (styleType != null && "DataStyle_NUMBER_F2".equals(styleType)) { // 数据单元格样式数字格式两位小数
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("#,#0.00"));

        } else if (styleType != null && "DataStyle_NUMBER_F3".equals(styleType)) { // 数据单元格样式数字格式三位小数
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("#,#0.000"));

        } else if (styleType != null && "DataStyle_NUMBER_F4".equals(styleType)) { //
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("#,#0.0000"));

        }   else if (styleType != null && "DataStyle_DATE".equals(styleType)) { // 数据单元格样式日期格式
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("yyyy-MM-dd"));

        } else if (styleType != null && "DataStyle_TIME".equals(styleType)) { // 数据单元格样式时间格式
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("hh:mm:ss"));

        } else if (styleType != null && "DataStyle_DATETIME".equals(styleType)) { // 数据单元格样式日期时间格式
            // 背景色

            /*style.setFillForegroundColor((short) 11);
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            style.setFillBackgroundColor((short) 11);*/

            // 居中显示
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
            // 设置字体
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setFontName("宋体");
            font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints(fontSize);
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(true);// 设置自动换行
            /* 设置边框 */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setDataFormat(dataFormat.getFormat("yyyy-MM-dd hh:mm:ss"));

        } else { /* 默认样式 */

        }

        return style;
    }


    public static  Sheet setMergedCellStyle(SXSSFWorkbook wb ,  Sheet sheet ,CellRangeAddress cra  ) {

        RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_THIN, cra, sheet , wb); // 下边框

        RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_THIN, cra, sheet,wb); // 左边框

        RegionUtil.setBorderRight(XSSFCellStyle.BORDER_THIN, cra, sheet,wb); // 有边框

        RegionUtil.setBorderTop(XSSFCellStyle.BORDER_THIN, cra, sheet,wb); // 上边框

        return sheet;

    }

} ]]> https://www.heckjj.com/post//#blogcomment <![CDATA[[评论] poi导出excel内存溢出问题解决]]> <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 https://www.heckjj.com/post//#blogcomment