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的代码样例
一个读Excel内容及其元信息的代码样例
参考链接:

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

《Java中如何进行读写Excel的操作》上有2条评论

  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

发表评论

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