如何使用Java POI生成Excel表格?

如何使用Java POI生成Excel表格?

一. 生成Excel截图:

二. 代码如下:

Java POI提供三种Workbook:

HSSFWorkbook: 对应Excel2003及以前的版本(.xls)
XSSFWorkbook:对应Excel2007后的版本(.xlsx)
SXSSFWorkbook: 对应Excel2007后的版本(.xlsx)

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * Description: .
 *
 * @author : ys.
 * @date : 2021-01-15 16:11
 */
public class MarkDown {

    private static final Logger LOGGER = LoggerBuilder.getLogger(MarkDown.class);
    
    private ExcelFormatUtil formatUtil = ExcelFormatUtil.getInstance();


    public Workbook createWorkBook(ExcelVersion version) {
        long startTime = System.currentTimeMillis();
        Workbook workbook;
        if (version.equals(ExcelVersion.SXSSFWORKBOOK)) {
            workbook = new SXSSFWorkbook(new XSSFWorkbook(), -1);
        } else {
            workbook = new HSSFWorkbook();
        }
        //创建table工作薄
        Sheet sheet = workbook.createSheet("sheetname");
        CellStyle title = formatUtil.setCellStyle(workbook);
        CellStyle content = formatUtil.setContentCellStyle(workbook);
        for (int rowIndex = 0; rowIndex < 10; rowIndex++) {
            //创建表格行
            Row row = sheet.createRow(rowIndex);
            for (int colIndex = 0; colIndex < 5; colIndex++) {
                //根据表格行创建单元格
                Cell cell = row.createCell(colIndex);
                //设置单元格格式
                if (rowIndex == 0) {
                    cell.setCellStyle(title);
                    cell.setCellValue("标题");
                } else {
                    cell.setCellStyle(content);
                    //设置单元格内容
                    cell.setCellValue("内容");
                }
            }
        }

        long endTime = System.currentTimeMillis();
        long usedTime = endTime - startTime;
        LOGGER.info("生成Excel耗时： " + usedTime);
        return workbook;
    }

    public void exportExcel(Workbook workbook, String filename) {
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(filename);
            workbook.write(fileOutputStream);
            LOGGER.info("导出成功!");
        } catch (IOException ex) {
            LOGGER.info("导出失败!");
        } finally {
            try {
                if (fileOutputStream != null) {
                    workbook.close();
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                LOGGER.error("关闭流出错!", e);
            }
        }
    }
}

三. 单元格格式设置

* 设置单元格边框,背景颜色,对齐方式,字体风格与大小

    public CellStyle setCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //字体设置
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 13);
        cellStyle.setFont(font);

        return cellStyle;
    }

四. 设置行冻结与列筛选

    /**
     * Description: 设置Excel表格指定行以前的所有行冻结,并给每一列都添加筛选.
     * @author : ys.
     **/
    public void setFreezeAndFilter(Sheet sheet, int rowIndex) {
        //清除冻结
        sheet.createFreezePane(0, 0, 0, 0);
        /**
         * colSplit: 拆分单元格的列   rowSplit: 拆分单元格的行
         * leftmostColumn: 右边区域可见的左边列数   topRow: 下面区域可见的首行
         */
        //设置冻结行
        sheet.createFreezePane(0, rowIndex + 1, 0, rowIndex + 1);
        int count = sheet.getRow(0).getPhysicalNumberOfCells();
        if (count > 0) {
            CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex, rowIndex,
                    0, count - 1);
            //添加筛选
            sheet.setAutoFilter(cellAddresses);
        }
    }

五. 其他格式设置

合并单元格

//合并指定范围单元格
sheet.addMergedRegion(new CellRangeAddress(firstrow, lastrown, firstcolu, lastcolu));

设置行高

//设置首行行高为25磅
sheet.getRow(0).setHeightInPoints(25);

设置列宽

//设置列宽自适应
sheet.autoSizeColumn(columnIndex, true);
//设置给定列宽, width in units of 1/256th of a character width (单位: 字符的1/256)
sheet.setColumnWidth(columnIndex, width);