使用Apache POI API读写Excel
目录(?)[-][*]读取Excel文件
[*]写入Excel文件
[*]读取Excel公式
[*]写入Excel公式
原文地址:http://www.journaldev.com/2562/java-readwrite-excel-file-using-apache-poi-api.
有时候我们需要从Excel文件中读取数据,或者我们为了商务或者财政的目的生成Excel格式的报表.Java没有对操作Excel文件提供内在的支持,所以我们需要寻找开源的APIs.当我开始寻找操作Excel的APIs时候,大部分人建议使用JExcel或者Apache POI.
在深入研究后,我发现由于以下主要原因Apache POI是正确的选择.还有些关于高级特性的原因,但是我们不深入太多细节.
1)Apache基金的支持.
2)JExcel不支持xlsx格式而POI既支持xls格式又支持xlsx格式.
3)Apache POI是基于流的处理,因此更适合大文件和要求更少的内存.
Apache POI对处理Excel文件提供了强大的支持,并且能处理xls和xlsx格式的电子表格.
关于Apache POI一些重要的地方:
1)Apache POI包含适合Excel97-2007(.xls文件)的HSSF实现.
2)Apache POI XSSF实现用来处理Excel2007文件(.xlsx).
3)Apache POI HSSF和XSSF提供了读/写/修改Excel表格的机制.
4)Apache POI提供了XSSF的一个扩展SXSSF用来处理非常大的Excel工作单元.SXSSF API需要更少的内存,因此当处理非常大的电子表格同时堆内存又有限时,很合适使用.
5)有两种模式可供选择--事件模式和用户模式.事件模式要求更少的内存,因为用tokens来读取Excel并处理.用户模式更加面向对象并且容易使用,因此在我们的示例中使用用户 模式.
6)Apache POI为额外的Excel特性提供了强大支持,例如处理公式,创建单元格样式--颜色,边框,字体,头部,脚部,数据验证,图像,超链接等.
Apache POI的Maven依赖
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]<span style="font-size:14px;"><dependency>
[*] <groupId>org.apache.poi</groupId>
[*] <artifactId>poi</artifactId>
[*] <version>3.10-FINAL</version>
[*]</dependency>
[*]<dependency>
[*] <groupId>org.apache.poi</groupId>
[*] <artifactId>poi-ooxml</artifactId>
[*] <version>3.10-FINAL</version>
[*]</dependency></span>
Apache POI的当前版本是3.10-FINAL.如果你使用单独的java应用,添加jars根据下面的图片.
http://cdn1.journaldev.com/wp-content/uploads/2014/03/Apache-POI-Dependencies.png
读取Excel文件
假设我们有一个叫Sample.xlsx的Excel文件,里面有两个sheet并且下面图片中的数据.我们想要读取这个Excel文件并且创建Countries list.sheet1有些额外的数据,当我们解析时会忽略它.
http://1-ps.googleusercontent.com/x/www.journaldev.com/cdn2.journaldev.com/wp-content/uploads/2014/03/450x366xJava-Read-Excel-File-1-450x366.png.pagespeed.ic.GtvRSYybva.png
http://1-ps.googleusercontent.com/x/www.journaldev.com/cdn1.journaldev.com/wp-content/uploads/2014/03/450x366xJava-Read-Excel-File-2-450x366.png.pagespeed.ic.GVs2b-4eT8.png
我们的国家(Country)java bean如下:
Country.java
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]package com.journaldev.excel.read;
[*]
[*]public class Country {
[*]
[*] private String name;
[*] private String shortCode;
[*]
[*] public Country(String n, String c){
[*] this.name=n;
[*] this.shortCode=c;
[*] }
[*]
[*] public String getName() {
[*] return name;
[*] }
[*] public void setName(String name) {
[*] this.name = name;
[*] }
[*] public String getShortCode() {
[*] return shortCode;
[*] }
[*] public void setShortCode(String shortCode) {
[*] this.shortCode = shortCode;
[*] }
[*]
[*] @Override
[*] public String toString(){
[*] return name + "::" + shortCode;
[*] }
[*]
[*]}
读取Excel文件并创建Countries list代码如下:
ReadExcelFileToList.java
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]package com.journaldev.excel.read;
[*]
[*]import java.io.FileInputStream;
[*]import java.io.IOException;
[*]import java.util.ArrayList;
[*]import java.util.Iterator;
[*]import java.util.List;
[*]
[*]import org.apache.poi.hssf.usermodel.HSSFWorkbook;
[*]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;
[*]
[*]public class ReadExcelFileToList {
[*]
[*] public static List<Country> readExcelData(String fileName) {
[*] List<Country> countriesList = new ArrayList<Country>();
[*]
[*] try {
[*] //Create the input stream from the xlsx/xls file
[*] FileInputStream fis = new FileInputStream(fileName);
[*]
[*] //Create Workbook instance for xlsx/xls file input stream
[*] Workbook workbook = null;
[*] if(fileName.toLowerCase().endsWith("xlsx")){
[*] workbook = new XSSFWorkbook(fis);
[*] }else if(fileName.toLowerCase().endsWith("xls")){
[*] workbook = new HSSFWorkbook(fis);
[*] }
[*]
[*] //Get the number of sheets in the xlsx file
[*] int numberOfSheets = workbook.getNumberOfSheets();
[*]
[*] //loop through each of the sheets
[*] for(int i=0; i < numberOfSheets; i++){
[*]
[*] //Get the nth sheet from the workbook
[*] Sheet sheet = workbook.getSheetAt(i);
[*]
[*] //every sheet has rows, iterate over them
[*] Iterator<Row> rowIterator = sheet.iterator();
[*] while (rowIterator.hasNext())
[*] {
[*] String name = "";
[*] String shortCode = "";
[*]
[*] //Get the row object
[*] Row row = rowIterator.next();
[*]
[*] //Every row has columns, get the column iterator and iterate over them
[*] Iterator<Cell> cellIterator = row.cellIterator();
[*]
[*] while (cellIterator.hasNext())
[*] {
[*] //Get the Cell object
[*] Cell cell = cellIterator.next();
[*]
[*] //check the cell type and process accordingly
[*] switch(cell.getCellType()){
[*] case Cell.CELL_TYPE_STRING:
[*] if(shortCode.equalsIgnoreCase("")){
[*] shortCode = cell.getStringCellValue().trim();
[*] }else if(name.equalsIgnoreCase("")){
[*] //2nd column
[*] name = cell.getStringCellValue().trim();
[*] }else{
[*] //random data, leave it
[*] System.out.println("Random data::"+cell.getStringCellValue());
[*] }
[*] break;
[*] case Cell.CELL_TYPE_NUMERIC:
[*] System.out.println("Random data::"+cell.getNumericCellValue());
[*] }
[*] } //end of cell iterator
[*] Country c = new Country(name, shortCode);
[*] countriesList.add(c);
[*] } //end of rows iterator
[*]
[*]
[*] } //end of sheets for loop
[*]
[*] //close file input stream
[*] fis.close();
[*]
[*] } catch (IOException e) {
[*] e.printStackTrace();
[*] }
[*]
[*] return countriesList;
[*] }
[*]
[*] public static void main(String args[]){
[*] List<Country> list = readExcelData("Sample.xlsx");
[*] System.out.println("Country List\n"+list);
[*] }
[*]
[*]}
这个程序很容易明白,主要步骤如下:
1)根据文件类型(.xls与.xlsx)创建Workbook实例,xlsx用XSSFWorkbook,xls用HSSFWorkbook.我们可以基于文件名字使用工厂模式创建一个包装类来创建Workbook实例.
2)使用Workbook getNumberOfSheets()来获取sheet的数量,然后循环解析每一个sheet.使用getSheetAt(int i)方法获取 Sheet实例.
3)获取Row和Cell迭代器来获取每一个Cell对象.Apache POI在这里使用了迭代器模式.
4)使用switch-case根据Cell的类型来处理它.
现在我们运行上面的程序,在控制台产生如下的输出:
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]Random data::1.0
[*]Random data::2.0
[*]Random data::3.0
[*]Random data::4.0
[*]Country List
[*]
[*]Denmark ::DNK, Dominican Republic ::DOM, Algeria ::DZA, Ecuador ::ECU]
写入Excel文件
除了我们首先创建Workbook,然后设置sheets,rows,cells的值并且使用FileOutputStream把Workbook写入文件外,与读取Excel的操作相似.我们把从上面的方法读取countries list写入到另一个文件作一个例子:
WriteListToExcelFile.java
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]package com.journaldev.excel.read;
[*]
[*]import java.io.FileOutputStream;
[*]import java.util.Iterator;
[*]import java.util.List;
[*]
[*]import org.apache.poi.hssf.usermodel.HSSFWorkbook;
[*]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;
[*]
[*]public class WriteListToExcelFile {
[*]
[*] public static void writeCountryListToFile(String fileName, List<Country> countryList) throws Exception{
[*] Workbook workbook = null;
[*]
[*] if(fileName.endsWith("xlsx")){
[*] workbook = new XSSFWorkbook();
[*] }else if(fileName.endsWith("xls")){
[*] workbook = new HSSFWorkbook();
[*] }else{
[*] throw new Exception("invalid file name, should be xls or xlsx");
[*] }
[*]
[*] Sheet sheet = workbook.createSheet("Countries");
[*]
[*] Iterator<Country> iterator = countryList.iterator();
[*]
[*] int rowIndex = 0;
[*] while(iterator.hasNext()){
[*] Country country = iterator.next();
[*] Row row = sheet.createRow(rowIndex++);
[*] Cell cell0 = row.createCell(0);
[*] cell0.setCellValue(country.getName());
[*] Cell cell1 = row.createCell(1);
[*] cell1.setCellValue(country.getShortCode());
[*] }
[*]
[*] //lets write the excel data to file now
[*] FileOutputStream fos = new FileOutputStream(fileName);
[*] workbook.write(fos);
[*] fos.close();
[*] System.out.println(fileName + " written successfully");
[*] }
[*]
[*] public static void main(String args[]) throws Exception{
[*] List<Country> list = ReadExcelFileToList.readExcelData("Sample.xlsx");
[*] WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
[*] }
[*]}
当我们执行上面的程序后,这个Excel文件被创建了,如下图:
http://cdn.journaldev.com/wp-content/uploads/2014/03/Java-Write-Excel-File-450x397.png
读取Excel公式
有时候我们需要出来带有公司的复杂Excel文件.让我们来看一个关于读取一个有值的cell中的公式的例子:
ReadExcelFormula.java
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]package com.journaldev.excel.read;
[*]
[*]import java.io.FileInputStream;
[*]import java.io.IOException;
[*]import java.util.Iterator;
[*]
[*]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;
[*]
[*]public class ReadExcelFormula {
[*]
[*] public static void readExcelFormula(String fileName) throws IOException{
[*]
[*] FileInputStream fis = new FileInputStream(fileName);
[*]
[*] //assuming xlsx file
[*] Workbook workbook = new XSSFWorkbook(fis);
[*] Sheet sheet = workbook.getSheetAt(0);
[*] Iterator<Row> rowIterator = sheet.iterator();
[*] while (rowIterator.hasNext())
[*] {
[*] Row row = rowIterator.next();
[*] Iterator<Cell> cellIterator = row.cellIterator();
[*]
[*] while (cellIterator.hasNext())
[*] {
[*] Cell cell = cellIterator.next();
[*] switch(cell.getCellType()){
[*] case Cell.CELL_TYPE_NUMERIC:
[*] System.out.println(cell.getNumericCellValue());
[*] break;
[*] case Cell.CELL_TYPE_FORMULA:
[*] System.out.println("Cell Formula="+cell.getCellFormula());
[*] System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
[*] if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
[*] System.out.println("Formula Value="+cell.getNumericCellValue());
[*] }
[*] }
[*] }
[*] }
[*] }
[*]
[*] public static void main(String args[]) throws IOException {
[*] readExcelFormula("FormulaMultiply.xlsx");
[*] }
[*]}
当我们执行上面的程序后,我们得到下面的输出:
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]1.0
[*]2.0
[*]3.0
[*]4.0
[*]Cell Formula=A1*A2*A3*A4
[*]Cell Formula Result Type=0
[*]Formula Value=24.0
写入Excel公式
有时候,我们需要做一些计算然后把值写入cell.我们可以使用Excel公司来做这些计算,这样会更加精确,因为这些值会随着计算的值改变而改变.让我们来看一个关于写入一个带有公式的Excel的例子:
WriteExcelWithFormula.java
viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.pnghttp://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg
[*]package com.journaldev.excel.read;
[*]
[*]import java.io.FileOutputStream;
[*]import java.io.IOException;
[*]
[*]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;
[*]
[*]public class WriteExcelWithFormula {
[*]
[*] public static void writeExcelWithFormula(String fileName) throws IOException{
[*] Workbook workbook = new XSSFWorkbook();
[*] Sheet sheet = workbook.createSheet("Numbers");
[*] Row row = sheet.createRow(0);
[*] row.createCell(0).setCellValue(10);
[*] row.createCell(1).setCellValue(20);
[*] row.createCell(2).setCellValue(30);
[*] //set formula cell
[*] row.createCell(3).setCellFormula("A1*B1*C1");
[*]
[*] //lets write to file
[*] FileOutputStream fos = new FileOutputStream(fileName);
[*] workbook.write(fos);
[*] fos.close();
[*] System.out.println(fileName + " written successfully");
[*] }
[*]
[*] public static void main(String[] args) throws IOException {
[*] writeExcelWithFormula("Formulas.xlsx");
[*] }
[*]}
当我们执行上面的程序后,Excel被创建了,如下图:
http://cdn.journaldev.com/wp-content/uploads/2014/03/java-write-excel-formula-450x343.png
这就是所有Apache POI处理Excel文件的内容.查看POI源代码可以学习它的更多特性.
页:
[1]