poi 列宽自动调整

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.FileOutputStream;
import java.io.IOException;
 
public class AutoSizeColumnExample {
    public static void main(String[] args) throws IOException {
        Workbook workbook = new XSSFWorkbook(); // 创建新的Excel工作簿
        Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个工作表
 
        // 创建一些数据
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("自动调整列宽");
 
        // 自动调整第一列的宽度
        sheet.autoSizeColumn(0);
 
        // 写入到文件
        try (FileOutputStream outputStream = new FileOutputStream("autosize_column.xlsx")) {
            workbook.write(outputStream);
        }
 
        workbook.close();
    }
}
package com.incar.base.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * ExcelUtils
 *
 * @author ct
 * @date 2021/10/13
 */
public class ExcelUtils {

    /**
     * 将值填充到单元格中
     * @param cell
     * @param val
     */
    private static void inputValue(Cell cell, Object val){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        if(val==null){
            cell.setCellValue("");
            return;
        }
        Class clazz= val.getClass();
        if(String.class.isAssignableFrom(clazz)){
            cell.setCellValue((String)val);
        }else if(Double.class.isAssignableFrom(clazz)){
            cell.setCellValue((Double)val);
        }else if(Date.class.isAssignableFrom(clazz)){
            String formatDate = simpleDateFormat.format(((Date) val));
            cell.setCellValue(formatDate);
        }else if(Boolean.class.isAssignableFrom(clazz)){
            cell.setCellValue((Boolean)val);
        }else if(Calendar.class.isAssignableFrom(clazz)){
            String formatDate = simpleDateFormat.format(((Calendar) val).getTime());
            cell.setCellValue(formatDate);
        }else if(RichTextString.class.isAssignableFrom(clazz)){
            cell.setCellValue((RichTextString)val);
        }else if(Float.class.isAssignableFrom(clazz)){
            DecimalFormat format = new DecimalFormat("#0.000") ;
            cell.setCellValue(format.format(val));
        }else if(Byte.class.isAssignableFrom(clazz)){
            cell.setCellValue((Byte)val);
        }else if(Short.class.isAssignableFrom(clazz)){
            cell.setCellValue((Short)val);
        }else if(Integer.class.isAssignableFrom(clazz)){
            cell.setCellValue((Integer)val);
        }else if(Long.class.isAssignableFrom(clazz)){
            cell.setCellValue(val.toString());
        }else if(BigDecimal.class.isAssignableFrom(clazz)){
            cell.setCellValue(val.toString());
        }
    }

    /**
     * 导出excel
     * @param dataList 数据集合
     * @return
     */
    public static Workbook exportExcel(List<List> dataList){
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet();
        Map<String, CellStyle> styles = createStyles(workBook);
        exportExcel(sheet,dataList,styles);
        return workBook;
    }

    /**
     * 导出excel(针对数据量较大的excel)
     * @param dataList 数据集合
     * @return
     */
    public static Workbook exportBigExcel(List<List> dataList){
        SXSSFWorkbook workBook = new SXSSFWorkbook(1000);
        SXSSFSheet sheet = workBook.createSheet();
        int columnLen = 0;
        Map<String, CellStyle> styles = createStyles(workBook);
        Map<Integer, Integer> titleColWidth = new HashMap<>();
        for(int i=0;i<=dataList.size()-1;i++){
            SXSSFRow curRow = sheet.createRow(i);
            List innerDataList= dataList.get(i);
            columnLen = innerDataList.size();
            for(int j=0;j<=innerDataList.size()-1;j++){
                SXSSFCell curCell= curRow.createCell(j);
                inputValue(curCell, innerDataList.get(j));
                if(i == 0) {
                    curCell.setCellStyle(styles.get("header"));
                    //计算标题的列宽(这个计算公式是经验值，可以根据实际情况调整)并缓存
                    titleColWidth.put(j, val.toString().length() * 3 * 17 / 10 * 256);
                } else {
                    curCell.setCellStyle(styles.get("data"));
                }
            }
        }
        //需要加上这句保证宽度自适应
        sheet.trackAllColumnsForAutoSizing();
		for (int i = 0; i < columnLen; i++) {
			sheet.autoSizeColumn(i);
			//取标题的列宽
			int width = titleColWidth.get(i);
			//取标题宽度和实际列宽度较大的，因为SXSSFSheet开启自适应宽度后如果数据行很多是空白的，
			//宽度会变得很小，这样可以保证最终的列宽不小于标题宽度
			int max = Math.max(sheet.getColumnWidth(i) * 17 / 10, width);
			sheet.setColumnWidth(i, Math.min(max, 255 * 256));
		}
        return workBook;
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    public static Map<String, CellStyle> createStyles(Workbook wb)
    {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

    /**
     * 导出excel到对应sheet中
     * @param sheet
     * @param dataList
     */
    public static void exportExcel(XSSFSheet sheet,List<List> dataList,Map<String,CellStyle> cellStyle){
        int columnLen = 0;
        for(int i=0;i<=dataList.size()-1;i++){
            XSSFRow curRow = sheet.createRow(i);
            List innerDataList= dataList.get(i);
            columnLen = innerDataList.size();
            for(int j=0;j<=innerDataList.size()-1;j++){
                Object o = innerDataList.get(j);
                XSSFCell curCell= curRow.createCell(j);
                if (cellStyle != null) {
                    if(i == 0) {
                        curCell.setCellStyle(cellStyle.get("header"));
                    } else {
                        curCell.setCellStyle(cellStyle.get("data"));
                    }
                }
                inputValue(curCell, o);
            }
        }

        for (int i = 0; i < columnLen; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
        }
    }
}