POI自定义excel样式及自适应列宽

Java侠

已于 2023-10-13 11:37:44 修改

阅读量1.6w

点赞数 7

分类专栏：日常开发工具类文章标签： java poi excel excel工具类

于 2021-10-13 15:47:00 首次发布

本文链接：https://blog.csdn.net/ThomasChant/article/details/120745236

版权

工具类同时被 2 个专栏收录

2 篇文章

订阅专栏

日常开发

1 篇文章

订阅专栏

本文介绍了使用Apache POI 3.17版本创建Excel工作簿，如何定制样式（包括数据和标题样式）、实现列宽自适应，特别是针对大数据量时的SXSSFSheet优化。展示了如何设置居中、边框、字体，并演示了如何通过Java编程填充值和调整列宽以适应不同场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

POI版本

org.apache.poi:poi:3.17
org.apache.poi:poi-ooxml:3.17

样式定制


    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    public static Map<String, CellStyle> createStyles(Workbook wb)
    {
        //内容样式
        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;
    }

列宽自适应

针对XSSFSheet

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

针对大数据量的SXSSFSheet

  //宽度自适应
  
	sheet.trackAllColumnsForAutoSizing();
	for (int i = 0; i < columnLen; i++) {
		s.autoSizeColumn(i);
		//取标题的宽度
		int width = titleColWidth.get(i);
		//取标题宽度和实际列宽度较大的，因为开启自适应宽度后如果数据行空白的，
		//可能导致自动计算出来的宽度值很小，这样可以保证最终的列宽不小于标题宽度
		int max = Math.max(sheet.getColumnWidth(i) * 17 / 10, width);
		s.setColumnWidth(i, Math.min(max, 255 * 256));
	}

完整代码

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);
        }
    }
}