欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-28 20:26:37
如何使用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....

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

一. 生成Excel截图:

如何使用Java POI生成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);

本文地址:https://blog.csdn.net/weixin_44942995/article/details/112680793