=Start=

缘由：

整理总结一下在Java中如何借助Apache POI实现Excel的读、写操作，方便有需要的时候参考和理解。

正文：

参考解答：

写Excel的基本步骤

1. 创建一个 workbook
2. 创建一个 sheet
3. 重复如下步骤直到数据被写入完成：
   • 创建一个 row
   • 在 row 中创建 cells ，如果要设置格式的话可以用 CellStyle
4. 将 workbook 写入 FileOutputStream
5. 关闭 FileOutputStream
6. 关闭 workbook

一个简单的写Excel的代码样例

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class WriteExcel {
    public static void main(String[] args) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Java-Books");

        Object[][] bookData = {
                {"Head First Java", "Kathy Serria", 79},
                {"Effective Java", "Joshua Bloch", 36},
                {"Clean Code", "Robert martin", 42},
                {"Thinking in Java", "Bruce Eckel", 35},
        };

        int rowCount = 0;
        for (Object[] aBook : bookData) {
            Row row = sheet.createRow(rowCount++);

            int columnCount = 0;

            for (Object field : aBook) {
                Cell cell = row.createCell(columnCount++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }

        }

        // 将 workbook 写入 FileOutputStream 存文件
        try (FileOutputStream outputStream = new FileOutputStream("JavaBooks.xlsx")) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        workbook.close();
    }
}

一个读Excel内容及其元信息的代码样例

import org.apache.poi.ooxml.POIXMLProperties;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.officeDocument.x2006.customProperties.CTProperties;

import java.io.*;
import java.util.Iterator;

public class ReadExcel {
    public static void main(String[] args) {
        String excelFilePath = "JavaBooks.xlsx";
        
        try {
            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
            Workbook workbook = new XSSFWorkbook(inputStream);

            int sheetNum = workbook.getNumberOfSheets();
            System.out.println("Number of sheet: " + sheetNum);
            for (int i = 0; i < sheetNum; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                System.out.println(sheet.getSheetName());
                Iterator<Row> iterator = sheet.iterator();

                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();

                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();

                        switch (cell.getCellType()) {
                            case STRING:
                                System.out.print(cell.getStringCellValue());
                                break;
                            case BOOLEAN:
                                System.out.print(cell.getBooleanCellValue());
                                break;
                            case NUMERIC:
                                System.out.print(cell.getNumericCellValue());
                                break;
                        }
                        System.out.print(" - ");
                    }
                    System.out.println();
                }
                System.out.println();
            }
            System.out.println();

            /* Get properties info of excel */
            POIXMLProperties props = ((XSSFWorkbook) workbook).getProperties();

            /* Let us set some core properties now */
            POIXMLProperties.CoreProperties coreProp = props.getCoreProperties();
            System.out.println(coreProp.getCreator());
            System.out.println(coreProp.getCategory());
            coreProp.setCreator("zero"); // set document creator
            coreProp.setDescription("set Metadata via Apache POI in Java");
            coreProp.setCategory("Programming"); // category

            /* We can set some custom Properties now */
            POIXMLProperties.CustomProperties customProp = props.getCustomProperties();
            // java.lang.IllegalArgumentException: A property with this name already exists in the custom properties
            // addProperty() 不能对已有的 name 进行再次添加，否则会抛出异常
            customProp.addProperty("Author", "zero");// String
            customProp.addProperty("Year", 2020); // Number Property
            customProp.addProperty("Published", true); // Yes No Property
            customProp.addProperty("Typist", "zero");
            CTProperties ctProperties = customProp.getUnderlyingProperties();
            for (int i = 0; i < ctProperties.sizeOfPropertyArray(); i++) {
                System.out.print(i + "\t");
                System.out.println(ctProperties.getPropertyArray(i));
            }

            POIXMLProperties.ExtendedProperties extendedProp = props.getExtendedProperties();
            System.out.println(extendedProp.getCompany()); // null
            extendedProp.setCompany("test");
            System.out.println(extendedProp.getCompany()); // test

            /* Save modification to properties */
            FileOutputStream outputStream = new FileOutputStream(new File(excelFilePath));
            workbook.write(outputStream);
            outputStream.close();

            workbook.close();
            inputStream.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

参考链接：

How to Read Excel Files in Java using Apache POI
https://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi

Java Example to Read Password-protected Excel Files Using Apache POI
https://www.codejava.net/coding/java-example-to-read-password-protected-excel-files-using-apache-poi

Java Example to Update Existing Excel Files Using Apache POI
https://www.codejava.net/coding/java-example-to-update-existing-excel-files-using-apache-poi

How to Write Excel Files in Java using Apache POI
https://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi

https://mvnrepository.com/artifact/org.apache.poi/poi
https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
https://mvnrepository.com/artifact/org.apache.commons/commons-compress

Java 使用 POI 操作 Excel
https://juejin.im/post/5c09e559e51d451da152df9c

POI核心类
https://www.yiibai.com/apache_poi/apache_poi_core_classes.html

java excel添加水印
https://www.jianshu.com/p/5ebf2217f0be

https://stackoverflow.com/questions/3454975/writing-to-excel-in-java

Java Code Examples for org.apache.poi.POIXMLProperties
https://www.programcreek.com/java-api-examples/?api=org.apache.poi.POIXMLProperties

Java Examples for org.apache.poi.POIXMLProperties
https://www.javatips.net/api/org.apache.poi.poixmlproperties

how to write metadata into Excel workbooks using Apache POI in Java
https://stackoverflow.com/questions/43796819/how-to-write-metadata-into-excel-workbooks-using-apache-poi-in-java

Read / Write Metadata – Excel POI Example
https://thinktibits.blogspot.com/2014/07/read-write-metadata-excel-poi-example.html

poi4.0.0读取excel文件时报java.lang.NoClassDefFoundError: org/apache/commons/compress/archivers/zip/ZipFile
https://blog.csdn.net/redsoft_mymuch/article/details/84099902

JAVA简单快速的读写Excel之EasyExcel
https://www.lifengdi.com/archives/article/1393
https://alibaba-easyexcel.github.io/index.html
https://github.com/alibaba/easyexcel

java excel添加水印
https://www.jianshu.com/p/5ebf2217f0be
https://juejin.im/post/5d1edde051882542cf1009dc

=END=