Java中如何进行读写Excel的操作

=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=

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/4867.html

2 thoughts on “Java中如何进行读写Excel的操作”

  1. Apache POI 的 设置单元格内容报错 java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32767 characters
    https://stackoverflow.com/questions/31935340/increase-the-maximum-length-of-hssfcell-in-java
    `
    # 简而言之就是——没有办法,32767个字符是Excel这类文件格式的限制,Apache POI仅仅是加强了这个限制的检查,并无法修改这个限制。
    Your only option is to switch file formats. There’s a hard limit in both the .xls and .xlsx file formats of 32,767 characters. Apache POI is simply enforcing the file format + Excel limit.
    `
    https://poi.apache.org/apidocs/dev/org/apache/poi/ss/SpreadsheetVersion.html

    [Apache POI] java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32,767 characters
    https://albert-kuo.blogspot.com/2016/12/apache-poi-javalangillegalargumentexcep.html
    https://blog.csdn.net/jiewenike/article/details/54907037

  2. Apache POI中的 setCellValue 操作出现NPE 异常
    `
    用Apache POI操作时,createCell 创建最多报 IllegalArgumentException 异常,不会有 NPE 异常,那个只会出现在 setCellValue 设置单元格内容时出现,原因就是设置的值类型为 null ,而且简单测可能还不容易发现,比如:

    JSONObject jsonObject = new JSONObject();

    Cell cell = row.createCell(columnCount++);
    System.out.println(jsonObject.getIntValue(“key1”));
    cell.setCellValue(jsonObject.getIntValue(“key1”)); // 正常,因为即便取不到key1的value会默认返回0

    String data = null;
    cell = row.createCell(columnCount++);
    cell.setCellValue(data); // 它说他对null做了容错处理,不会报 NPE 异常

    cell = row.createCell(columnCount++);
    System.out.println(jsonObject.getInteger(“key1”));
    cell.setCellValue(jsonObject.getInteger(“key1”)); // NPE
    `
    http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Row.html#createCell-int-
    http://apache-poi.1045710.n5.nabble.com/Bug-59199-New-setting-null-value-to-date-cell-leads-to-NPE-td5722365.html
    https://www.experts-exchange.com/questions/23180270/How-to-avoid-NullPointerException-in-excel-writing.html
    https://stackoverflow.com/questions/20694915/apachi-poi-cell-setcellvalue-thows-nullpointerexception

发表评论

电子邮件地址不会被公开。 必填项已用*标注