侧边栏壁纸
博主头像
MDZZW博主等级

曾经也是帅哥,如今只是肉多

  • 累计撰写 28 篇文章
  • 累计创建 26 个标签
  • 累计收到 7 条评论

JAVA导出百万行Excel清单_动态列名

MDZZW
2024-03-11 / 0 评论 / 0 点赞 / 159 阅读 / 1,222 字
温馨提示:
本文最后更新于 2024-03-11,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

Renew

SQL 增/减 字段不需要发版本调整清单标题或模板
无视OOM、 CPU爆表

POM

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
</dependency>
<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
</dependency>

Source Code


import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.*;

public class PoiSXSSFWorkbookExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(PoiSXSSFWorkbookExcel.class);


    public static SXSSFWorkbook getSxssfwbManySameSheets(String sheetTitle,  ResultSet rs)  throws Exception{
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date dateBefore = new Date();
        logger.debug("开始导出"+sheetTitle);
        SXSSFWorkbook wb = new SXSSFWorkbook();

        ResultSetMetaData data = rs.getMetaData();

        int sheetNum = 0;// 记录额外创建的sheet数量
        Sheet sheet = wb.createSheet(sheetTitle);
        // wb.setSheetName(sheetNum, sheetTitle+sheetNum);
        int rownum = 0;
        Row row = sheet.createRow(rownum);

        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);

        Cell cell;
        // 创建标题,此时row=0,即第一行
        String[] titles  = new String[data.getColumnCount()];

        for (int j = 0; j < data.getColumnCount(); j++) {
            //获得指定列的列名
            String columnName = data.getColumnName(j+1);
            titles[j] =columnName;
            cell = row.createCell(j);
            cell.setCellValue(columnName);
            cell.setCellStyle(xssfCellStyleHeader);
        }
        int rowIndex =0;

        while (rs.next()){
            if ((rowIndex + 1) % 1048576 == 0) {
                sheetNum++;
                sheet = wb.createSheet(sheetTitle +"_"+ sheetNum);
                row = sheet.createRow(0);
                // 声明列对象,参数为列索引,可以是0~255之间的任何一个
                // 创建标题,此时row=0,即第一行
                for (int j = 0; j < titles.length; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(titles[j]);
                    cell.setCellStyle(xssfCellStyleHeader);
                }
            }
            row = sheet.createRow((rowIndex + 1) - (sheetNum * 1048576)+sheetNum);

            for (int cellIndex = 1; cellIndex <= data.getColumnCount(); cellIndex++) {
                //获得指定列的列名
                String columnName = data.getColumnName(cellIndex);
                //获得指定列的值
                String columnValue = rs.getString(cellIndex);
                //对应数据类型的类
                String columnClassName = data.getColumnClassName(cellIndex);

                Object o = rs.getObject(columnName);
                if("java.sql.Timestamp".equals(columnClassName)){
                    if(StringUtils.isNotBlank(columnValue)){
                        row.createCell((short) cellIndex-1).setCellValue(columnValue);
                    }
                }
                if(o instanceof Byte){
                    row.createCell((short) cellIndex-1).setCellValue((Byte) o);
                }
                if(o instanceof Short){
                    row.createCell((short) cellIndex-1).setCellValue((Short) o);
                }
                if(o instanceof Integer){
                    row.createCell((short) cellIndex-1).setCellValue((Integer) o);
                }
                if(o instanceof Long){
                    row.createCell((short) cellIndex-1).setCellValue((Long) o);
                }
                if(o instanceof Character){
                    row.createCell((short) cellIndex-1).setCellValue((Character) o);
                }
                if(o instanceof Float){
                    row.createCell((short) cellIndex-1).setCellValue((Float) o);
                }
                if(o instanceof Double){
                    row.createCell((short) cellIndex-1).setCellValue((Double) o);
                }
                if(o instanceof Boolean){
                    row.createCell((short) cellIndex-1).setCellValue((Boolean) o);
                }
                if(o instanceof BigDecimal){
                    row.createCell((short) cellIndex-1).setCellValue(((BigDecimal) o).doubleValue());
                }
                if(o instanceof String){
                    row.createCell((short) cellIndex-1).setCellValue((String) o);
                }
                if(o instanceof Date){
                    String dateStr = simpleDateFormat.format((Date)o);
                    row.createCell((short) cellIndex-1).setCellValue(dateStr);
                }
            }
            rowIndex++;
        }
        Date dateAfter = new Date();
        logger.info("共"+rowIndex+"条数据,导出列表共执行"+(dateAfter.getTime()-dateBefore.getTime())+"ms");
        if(rowIndex==0){
            throw new Exception(sheetTitle+"无数据");
        }
        return wb;
    }

    /**
     *
     * @param linkedHashMap 多个游标
     * @return
     * @throws Exception
     */
    public static SXSSFWorkbook getSxssfwbManySameSheets(LinkedHashMap<String,ResultSet> linkedHashMap)  throws Exception{
        Date dateBefore = new Date();
        SXSSFWorkbook wb = new SXSSFWorkbook();
        int ExportRow = 0;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (Map.Entry<String,ResultSet> entry : linkedHashMap.entrySet()) {
            ResultSet rs = entry.getValue();
            String sheetTitle = entry.getKey();
            logger.debug("开始导出"+sheetTitle);
            ResultSetMetaData data = rs.getMetaData();

            int sheetNum = 0;// 记录额外创建的sheet数量
            Sheet sheet = wb.createSheet(sheetTitle);
            int rownum = 0;
            Row row = sheet.createRow(rownum);

            // 设置并获取到需要的样式
            XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);

            Cell cell;
            // 创建标题,此时row=0,即第一行
            String[] titles  = new String[data.getColumnCount()];

            for (int j = 0; j < data.getColumnCount(); j++) {
                //获得指定列的列名
                String columnName = data.getColumnName(j+1);
                titles[j] =columnName;
                cell = row.createCell(j);
                cell.setCellValue(columnName);
                cell.setCellStyle(xssfCellStyleHeader);
            }
            int rowIndex =0;

            while (rs.next()){
                if ((rowIndex + 1) % 1048576 == 0) {
                    sheetNum++;
                    sheet = wb.createSheet(sheetTitle +"_"+ sheetNum);
                    row = sheet.createRow(0);
                    // 声明列对象,参数为列索引,可以是0~255之间的任何一个
                    // 创建标题,此时row=0,即第一行
                    for (int j = 0; j < titles.length; j++) {
                        cell = row.createCell(j);
                        cell.setCellValue(titles[j]);
                        cell.setCellStyle(xssfCellStyleHeader);
                    }
                }
                row = sheet.createRow((rowIndex + 1) - (sheetNum * 1048576)+sheetNum);

                for (int cellIndex = 1; cellIndex <= data.getColumnCount(); cellIndex++) {
                    String columnName = data.getColumnName(cellIndex);
                    String columnValue = rs.getString(cellIndex);
                    String columnClassName = data.getColumnClassName(cellIndex);
                    Object o = rs.getObject(columnName);
                    if("java.sql.Timestamp".equals(columnClassName)){

                        if(StringUtils.isNotBlank(columnValue)){
                            row.createCell((short) cellIndex-1).setCellValue(columnValue);
                        }
                    }
                    if(o instanceof Byte){
                        row.createCell((short) cellIndex-1).setCellValue((Byte) o);
                    }
                    if(o instanceof Short){
                        row.createCell((short) cellIndex-1).setCellValue((Short) o);
                    }
                    if(o instanceof Integer){
                        row.createCell((short) cellIndex-1).setCellValue((Integer) o);
                    }
                    if(o instanceof Long){
                        row.createCell((short) cellIndex-1).setCellValue((Long) o);
                    }
                    if(o instanceof Character){
                        row.createCell((short) cellIndex-1).setCellValue((Character) o);
                    }
                    if(o instanceof Float){
                        row.createCell((short) cellIndex-1).setCellValue((Float) o);
                    }
                    if(o instanceof Double){
                        row.createCell((short) cellIndex-1).setCellValue((Double) o);
                    }
                    if(o instanceof Boolean){
                        row.createCell((short) cellIndex-1).setCellValue((Boolean) o);
                    }
                    if(o instanceof BigDecimal){
                        row.createCell((short) cellIndex-1).setCellValue(((BigDecimal) o).doubleValue());
                    }
                    if(o instanceof String){
                        row.createCell((short) cellIndex-1).setCellValue((String) o);
                    }
                    if(o instanceof Date){
                        String dateStr = simpleDateFormat.format((Date)o);
                        row.createCell((short) cellIndex-1).setCellValue(dateStr);
                    }
                }
                rowIndex++;
                ExportRow++;
            }
        }
        if(ExportRow ==0){
            Date dateAfter = new Date();
            logger.info("共"+ExportRow+"条数据,导出列表共执行"+(dateAfter.getTime()-dateBefore.getTime())+"ms");
            if(ExportRow==0){
                throw new Exception("无数据");
            }
        }
        return wb;
    }



    /**
     * 获取并设置header样式
     */
    private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体粗细
        font.setBold(true);
        font.setFontName("宋体");
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }
}


0

评论区