当前位置： 首页 > article >正文

在Java中使用Apache POI导入导出Excel（一）

article 2024/12/1 14:20:28

Excel导入导出应用场景

在日常的管理系统应用场景中，进行Excel表格的导入导出是比较常见的需求，比如电商平台、企业管理系统、库存管理系统、人事管理系统等等，基本都需要使用数据进行分析，需要将已有的数据通过Excel导入到系统中，或者将系统分析的结果导出到Excel文件中，可以说在这些系统中，Excel导入导出是一个重要且普遍使用的功能，本文将介绍在Java开发中，如何快速实现Excel的导入导出。

Apache POI介绍

为了在Java中实现Excel的导入导出，我们一般会用到Apache的一个重要组件Apache POI，Apache POI是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。用它可以使用Java读取和创建，修改MS Excel文件。而且还可以使用Java读取和创建MS Word和MS PowerPoint文件等，本文主要介绍MS Excel的相关操作。

引入Apache POI组件

在Java开发中，我们一般从Spring Boot开始，所以本文主要以Spring Boot3框架下介绍Apache POI的使用，首先，我们需要在Spring Boot中引入Apache POI组件，引入方式如下：

打开pom.xml，在<dependencies>中添加如下依赖，如果大家需想要获取ApachePOI的最新版本，可以从Maven仓中去搜寻。

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.3.0</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.3.0</version>
        </dependency>

使用Apache POI组件操作Excel

MS Excel分老版（2007以前版本）和新版，老版是.xls后缀，新版是.xlsx后缀，同样在处理不同版本的Excel的时候，Apache POI也提供了两个不同的模块，分别是HSSF和XSSF：

HSSF：Horrible SpreadSheet Format，用于处理老版本的Excel文件，即“.xls”格式：

XSSF：XML SpreadSheet Format，用于处理新版本的Excel文件，即“.xlsx”格式。

本文主要介绍XSSF模块的使用。

1、新建工作簿

Workbook wb = new XSSFWorkbook();

try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

2、创建单元格

Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);

// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);

// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
     createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}

3、创建日期单元格

Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();

Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);

// Create a cell and put a date value in it.  The first cell is not styled
// as a date.
Cell cell = row.createCell(0);
cell.setCellValue(new Date());

// we style the second cell as a date (and time).  It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.

CellStyle cellStyle = wb.createCellStyle();

cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));

cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

//you can also set date as java.util.Calendar
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}

4、使用不同类型的单元格

Workbook wb = new XSSFWorkbook();

Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow(2);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(new Date());
row.createCell(2).setCellValue(Calendar.getInstance());
row.createCell(3).setCellValue("a string");
row.createCell(4).setCellValue(true);
row.createCell(5).setCellType(CellType.ERROR);

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}

5、打开工作簿

工作簿可以从 File 或 InputStream 加载。使用 File 对象允许 更低的内存消耗，而 InputStream 需要更多的内存消耗 memory 的 SET 文件，因为它必须缓冲整个文件。

如果使用 WorkbookFactory，操作比较简单：

// Use a file
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

// Use an InputStream, needs more memory
Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

如果直接使用 HSSFWorkbook 或 XSSFWorkbook， 您通常应该通过 POIFSFileSystem 或 OPCPackage 来完全控制生命周期（包括 完成后关闭文件）：

// XSSFWorkbook, File
OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);

// TODO ....

pkg.close();

// XSSFWorkbook, InputStream, needs more memory
OPCPackage pkg = OPCPackage.open(myInputStream);
XSSFWorkbook wb = new XSSFWorkbook(pkg);

// TODO ....

pkg.close();

6、各种对齐选项

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); 

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(2);
    row.setHeightInPoints(30);

    createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
    createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
    createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
    createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
    createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
    createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
    createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);

    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) {
        wb.write(fileOut);
    }
    wb.close();
}

/**
 * Creates a cell and aligns it a certain way.
 *
 * @param wb     the workbook
 * @param row    the row to create the cell in
 * @param column the column number to create the cell in
 * @param halign the horizontal alignment for the cell.
 * @param valign the vertical alignment for the cell.
 */
private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
    Cell cell = row.createCell(column);

    cell.setCellValue("Align It");

    CellStyle cellStyle = wb.createCellStyle();

    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);

    cell.setCellStyle(cellStyle);
}

7、使用边框

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(1);

// Create a cell and put a value in it.
Cell cell = row.createCell(1);
cell.setCellValue(4);

// Style the cell with borders all around.
CellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(BorderStyle.MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style);

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}
wb.close();

8、获取单元格内容

要获取单元格的内容，您首先需要 知道它是什么类型的 Cell （询问 String Cell ，因为它的数字内容将为您提供一个 NumberFormatException 的示例）。所以，你会 想要打开单元格的类型，然后调用 该单元格的适当 getter。

在下面的代码中，我们遍历每个单元格 在一张工作表中，打印出单元格的引用 （例如 A3），然后是单元格的内容。

// import org.apache.poi.ss.usermodel.*;
DataFormatter formatter = new DataFormatter();

Sheet sheet1 = wb.getSheetAt(0);

for (Row row : sheet1) {
    for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(cellRef.formatAsString());

        System.out.print(" - ");

        // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
        String text = formatter.formatCellValue(cell);

        System.out.println(text);

        // Alternatively, get the value and format it yourself
        switch (cell.getCellType()) {
            case CellType.STRING:
                System.out.println(cell.getRichStringCellValue().getString());
                break;
            case CellType.NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.println(cell.getDateCellValue());
                } else {
                    System.out.println(cell.getNumericCellValue());
                }
                break;
            case CellType.BOOLEAN:
                System.out.println(cell.getBooleanCellValue());
                break;
            case CellType.FORMULA:
                System.out.println(cell.getCellFormula());
                break;
            case CellType.BLANK:
                System.out.println();
                break;
            default:
                System.out.println();
        }
    }
}

9、文本提取

try (InputStream inp = new FileInputStream("workbook.xls")) {
    HSSFWorkbook wb = new XSSFWorkbook(new POIFSFileSystem(inp));
    ExcelExtractor extractor = new ExcelExtractor(wb);
    extractor.setFormulasNotResults(true);
    extractor.setIncludeSheetNames(false);
    String text = extractor.getText();
    wb.close();
}

10、填充和颜色

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(1);

// Aqua background
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(FillPatternType.BIG_SPOTS);

Cell cell = row.createCell(1);
cell.setCellValue("X");
cell.setCellStyle(style);

// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

cell = row.createCell(2);
cell.setCellValue("X");
cell.setCellStyle(style);

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}
wb.close();

11、合并单元格

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow(1);

Cell cell = row.createCell(1);
cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new CellRangeAddress(
        1, //first row (0-based)
        1, //last row  (0-based)
        1, //first column (0-based)
        2  //last column  (0-based)
));

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}
wb.close();

12、使用字体

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(1);

// Create a new font and alter it.
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);

// Fonts are set into a style so create a new one to use.
CellStyle style = wb.createCellStyle();
style.setFont(font);

// Create a cell and put a value in it.
Cell cell = row.createCell(1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style);

// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}
wb.close();

13、自定义颜色

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();

XSSFRow row = sheet.createRow(0);

XSSFCell cell = row.createCell( 0);
cell.setCellValue("custom XSSF colors");

XSSFCellStyle style1 = wb.createCellStyle();
style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap()));

style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);


http://www.kler.cn/a/417134.html

相关文章：

  • 10. 函数
  • uniapp运行时，同步资源失败，未得到同步资源的授权，请停止运行后重新运行，并注意手机上的授权提示。
  • 用micropython 操作stm32f4单片机的定时器实现蜂鸣器驱动
  • Axios与FastAPI结合：构建并请求用户增删改查接口
  • 信息安全实验--密码学实验工具：CrypTool
  • MySQL之单行函数
  • TensorBoard中的add_image()和add_scalar()
  • Pandas 操作Excel
  • duxapp 2024-11-29 更新 新增 UI 组件功能
  • iQOO Neo10系列携三大蓝科技亮相，性能与续航全面升级
  • 知行合一：实践中的技术分享与学习
  • PostgreSQL WAL日志膨胀处理
  • 排序算法思维导图
  • Ubuntu源码安装gitlab13.7集群多前端《二》
  • MATLAB期末复习笔记（二）
  • Zero to JupyterHub with Kubernetes上篇 - Kubernetes 离线二进制部署
  • 【Git系列】利用 Bash 脚本获取 Git 最后一次非合并提交的提交人
  • CSS笔记（四）卡片翻转
  • 【优选算法篇】两队接力跑：双指针协作解题的艺术（下篇）
  • elementUI el-image的使用
  • 深度学习基础2
  • Windchill查找某一个id关联的数据库表
  • #JAVA－常用API－爬虫
  • ACM输入输出模板（下）【Java、C++版】
  • 【论文笔记】Towards Online Continuous Sign Language Recognition and Translation
  • 【JAVA进阶篇教学】第二十篇：如何高效处理List集合数据及明细数据