java poi-ooxml带格式生成excel数据

package com.mytest.Learning;

import com.mytest.Dto.User;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * ClassName: UserServiceTest
 * Description:
 *
 * @author Weiwan
 * @date 2022/11/21 18:00
 */
public class UserServiceTest {
    @Test
    public void testLogin() throws IOException {
        int size = 2000;
        List<User> users = new ArrayList<>(size);
        User user;
        for (int i = 0; i < size; i++) {
            user = new User();
            user.setId((long)i);
            user.setAge(i + 10);
            user.setName("user" + i);
            user.setRemark(System.currentTimeMillis() + "");
            user.setSex("男");
            users.add(user);
        }

        String[] columnName = {"用户id", "姓名", "年龄", "性别", "备注"};
        Object[][] data = new Object[size][5];
        int index = 0;
        for (User u : users) {
            data[index][0] = u.getId();
            data[index][1] = u.getName();
            data[index][2] = u.getAge();
            data[index][3] = u.getSex();
            data[index][4] = u.getRemark();
            index++;
        }
        XSSFWorkbook xssfWorkbook = generateExcel("测试", "用户", columnName, data);

        String fileName = "E:/IdeaProject/jemterTest/excels/user.csv";
        createNewFile( fileName );
        try(FileOutputStream fos = new FileOutputStream( fileName )){
            xssfWorkbook.write( fos );
        } finally {
            xssfWorkbook.close();
        };




//        new Thread(() -> {
//        String[] columnName = {"用户id", "姓名", "年龄", "性别", "备注"};
//        Object[][] data = new Object[size][5];
//        int index = 0;
//        for (User u : users) {
//            data[index][0] = u.getId();
//            data[index][1] = u.getName();
//            data[index][2] = u.getAge();
//            data[index][3] = u.getSex();
//            data[index][4] = u.getRemark();
//            index++;
//        }
//        XSSFWorkbook xssfWorkbook = generateExcel("test", "test", columnName, data);
//        }
//        ).start();
//
//
//        try {
//            Thread.currentThread().join();//等待子线程结束
//        } catch (InterruptedException e) {
//            e.printStackTrace();
//        }
    }

    public static void createNewFile(String fileName) throws IOException {
        File file = new File( fileName );
        File fileParent = file.getParentFile();
//        System.out.println(fileParent.getAbsolutePath());
        if (!fileParent.exists()){
            fileParent.mkdirs();
        }
        file.createNewFile();
    }

    /**
     *
     * @param xssfWorkbook 对象
     * @param isCenter	是否居中
     * @param fontHeight	字体大小
     * @return
     */
    public static XSSFCellStyle style(XSSFWorkbook xssfWorkbook, boolean isCenter, int fontHeight){
        XSSFCellStyle style = xssfWorkbook.createCellStyle();
        if (isCenter){
            style.setAlignment( HorizontalAlignment.CENTER); //居中
        }
        style.setBorderBottom( BorderStyle.THICK); //下边框
        style.setBorderLeft(BorderStyle.THICK);//左边框
        style.setBorderTop(BorderStyle.THICK);//上边框
        style.setBorderRight(BorderStyle.THICK);//右边框
        style.setWrapText(true); //自动换行
//        java.awt.Color green = new java.awt.Color( 64, 230, 18 );
//        style.setFillForegroundColor(new XSSFColor(green, new DefaultIndexedColorMap()));
//        style.setFillPattern( FillPatternType.SOLID_FOREGROUND);

        XSSFFont font = xssfWorkbook.createFont();
//        font.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeight(fontHeight);
        font.setBold( true );
        style.setFont(font);
        return style;
    }

    /**
     * 宋体  9号 居中 全边框
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getStyle2(XSSFWorkbook workbook, boolean isCenter, int fontHeight) {
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeight(fontHeight);
        XSSFCellStyle style = workbook.createCellStyle();
        if(isCenter){
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        style.setVerticalAlignment( VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);// 自动换行
        style.setFont(font);
        return style;
    }

    private static XSSFWorkbook generateExcel(String sheetName, String title, String[] columnName, Object[][] data) {

        XSSFWorkbook workBook = new XSSFWorkbook();

        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        // 如果没有给定sheet名，则默认使用Sheet1
        XSSFSheet sheet;
        if (StringUtils.isNotBlank(sheetName)) {
            sheet = workBook.createSheet(sheetName);
        } else {
            sheet = workBook.createSheet();
        }
        sheet.setColumnWidth(1, 256*12+184);//12为excel列宽，width未转化为poi的列宽
        sheet.setColumnWidth(4, 256*21+184);
//        sheet.autoSizeColumn( 4,true );

        // 构建大标题，可以没有
        XSSFRow headRow = sheet.createRow(0);
        XSSFCell cell = null;

        cell = headRow.createCell(0);
        cell.setCellValue(title);

        //设置样式
        assert cell != null;
        cell.setCellStyle(getStyle2(workBook,true,20));

        //大标题行的偏移
        int offset = 0;
        if (StringUtils.isNotBlank(title)) {
            offset = 1;
        }

        // 构建列标题，不能为空
        headRow = sheet.createRow(offset);
        for (int i = 0; i < columnName.length; i++) {
            cell = headRow.createCell(i);
            cell.setCellStyle(getStyle2(workBook,true,18));
            cell.setCellValue(columnName[i]);
        }

        //设置跨行,前两个参数是需要合并的开始行数和结束行数,后两个是开始列数和结束列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length-1));
        //冻结行数,冻结前两行
        sheet.createFreezePane( 0, 2, 0, 2 );

        // 构建表体数据（二维数组），不能为空
        for (int i = 0; i < data.length; i++) {
            headRow = sheet.createRow(++offset);
            for (int j = 0; j < data[0].length; j++) {
                cell = headRow.createCell(j);
                cell.setCellStyle(getStyle2(workBook,true,15));
                if (data[i][j] instanceof BigDecimal)
                    cell.setCellValue(((BigDecimal) data[i][j]).doubleValue());
                else if (data[i][j] instanceof Double)
                    cell.setCellValue((Double) data[i][j]);
                else if (data[i][j] instanceof Long)
                    cell.setCellValue((Long) data[i][j]);
                else if (data[i][j] instanceof Integer)
                    cell.setCellValue((Integer) data[i][j]);
                else if (data[i][j] instanceof Boolean)
                    cell.setCellValue((Boolean) data[i][j]);
                else if (data[i][j] instanceof Date)
                    cell.setCellValue((Date) data[i][j]);
                else
                    cell.setCellValue((String) data[i][j]);
            }
        }
        return workBook;
    }
}