hutools写出excel

hutools写出excel

引入依赖

        <!--hutool 工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.22</version>
        </dependency>
        <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>


示例代码

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.List;

public class ExcelDemo {

    public static void main(String[] args) {
        //构建数据
        List<?> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd", DateUtil.date(), 3.22676575765d);
        List<?> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1", DateUtil.date(), 250.7676d);
        List<?> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2", DateUtil.date(), 0.111d);
        List<?> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3", DateUtil.date(), 35d);
        List<?> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4", DateUtil.date(), 28.00d);
        List<List<?>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

        List<String> headRow = CollUtil.newArrayList("列1", "列2", "列3", "列4", "时间", "数字");

        String fileName = "D:\\示例.xlsx";
        BigExcelWriter writer = ExcelUtil.getBigWriter(fileName);

        //设置内容字体
        Font font = writer.createFont();
        font.setBold(true);
        font.setColor(Font.COLOR_RED);
        font.setItalic(true);

        //默认单元格样式
        CellStyle defauleStyle = writer.getCellStyle();
        defauleStyle.setFont(font);

        //头部样式
        CellStyle headStyle = writer.getHeadCellStyle();
        StyleUtil.setColor(headStyle, IndexedColors.BLUE, FillPatternType.SOLID_FOREGROUND);

        //数字样式
        CellStyle numberStyle = writer.getStyleSet().getCellStyleForNumber();
        StyleUtil.setColor(numberStyle, IndexedColors.RED, FillPatternType.SOLID_FOREGROUND);
        StyleUtil.setAlign(numberStyle, HorizontalAlignment.RIGHT, VerticalAlignment.CENTER);

        //日期样式
        CellStyle dateStyle = writer.getStyleSet().getCellStyleForDate();
        StyleUtil.setColor(dateStyle, IndexedColors.DARK_YELLOW, FillPatternType.SOLID_FOREGROUND);

        //写入表头
        writer.writeHeadRow(headRow);

        // 一次性写出内容,使用默认样式
        rows.forEach(k -> {
            writer.writeRow(k);
        });
        // 关闭writer,释放内存
        writer.close();
    }

其他记录

行乘列限制

  • excel 2003版本 单sheet最大 65536*256
  • excel 2007以上版本 单sheet最大 1048576*16384

临时文件占用系统缓存目录

说明:hutool的excel工具包通过封装poi包提供处理excel的功能
使用SXSSFWorkbook因需要缓存大量数据,又不能占内存,需要生成临时文件

  • windows下:AppData\Local\Temp\poifiles文件夹下,生成一个叫poi-sxssf-sheet**************的文件
  • Linux系统下:会在/tmp/poifiles文件下生成该临时文件
posted @   二品  阅读(800)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示