Apache POI 是 Apache 软件基金会的开放源码函式库,POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能。
这里演示了 POI 对新版 Excel (.xlsx) 和 旧版 Excel (.xls) 两种格式文件的读写操作。
POI 下载处 http://poi.apache.org/
需要导入的 jar
xmlbeans-2.6.0.jar (POI 完整包内的 ooxml-lib 目录内)
curvesapi-1.03.jar (POI 完整包内的 ooxml-lib 目录内)
poi-3.14-20160307.jar
poi-excelant-3.14-20160307.jar
poi-ooxml-3.14-20160307.jar
poi-ooxml-schemas-3.14-20160307.jar
poi-scratchpad-3.14-20160307.jar
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadWriteExcelFile
{
public static void readXLSFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls");
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell = (HSSFCell) cells.next();
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue() + " ");
}
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue() + " ");
}
else
{
// U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}
}
public static void writeXLSFile() throws IOException
{
String excelFileName = "C:/Test.xls";// name of excel file
String sheetName = "Sheet1";// name of sheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
// iterating r number of rows
for (int r = 0; r < 5; r++)
{
HSSFRow row = sheet.createRow(r);
// iterating c number of columns
for (int c = 0; c < 5; c++)
{
HSSFCell cell = row.createCell(c);
cell.setCellValue("Cell " + r + " " + c);
}
}
FileOutputStream fileOut = new FileOutputStream(excelFileName);
// write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
public static void readXLSXFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
XSSFWorkbook test = new XSSFWorkbook();
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
row = (XSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell = (XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue() + " ");
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue() + " ");
}
else
{
// U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}
}
public static void writeXLSXFile() throws IOException
{
String excelFileName = "C:/Test.xlsx";// name of excel file
String sheetName = "Sheet1";// name of sheet
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName);
// iterating r number of rows
for (int r = 0; r < 5; r++)
{
XSSFRow row = sheet.createRow(r);
// iterating c number of columns
for (int c = 0; c < 5; c++)
{
XSSFCell cell = row.createCell(c);
cell.setCellValue("Cell " + r + " " + c);
}
}
FileOutputStream fileOut = new FileOutputStream(excelFileName);
// write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
public static void main(String[] args) throws IOException
{
writeXLSFile();
readXLSFile();
writeXLSXFile();
readXLSXFile();
}
}
POI 所支持的文档格式
Component | Application type | Maven artifactId | Notes |
---|---|---|---|
POIFS | OLE2 Filesystem | poi | Required to work with OLE2 / POIFS based files |
HPSF | OLE2 Property Sets | poi | |
HSSF | Excel XLS | poi | For HSSF only, if common SS is needed see below |
HSLF | PowerPoint PPT | poi-scratchpad | |
HWPF | Word DOC | poi-scratchpad | |
HDGF | Visio VSD | poi-scratchpad | |
HPBF | Publisher PUB | poi-scratchpad | |
HSMF | Outlook MSG | poi-scratchpad | |
DDF | Escher common drawings | poi | |
HWMF | WMF drawings | poi-scratchpad | |
OpenXML4J | OOXML | poi-ooxml plus either poi-ooxml-schemas or ooxml-schemas and ooxml-security | See notes below for differences between these options |
XSSF | Excel XLSX | poi-ooxml | |
XSLF | PowerPoint PPTX | poi-ooxml | |
XWPF | Word DOCX | poi-ooxml | |
Common SL | PowerPoint PPT and PPTX | poi-scratchpad and poi-ooxml | SL code is in the core POI jar, but implementations are in poi-scratchpad and poi-ooxml. |
Common SS | Excel XLS and XLSX | poi-ooxml | WorkbookFactory and friends all require poi-ooxml, not just core poi |
原文来自 https://gist.github.com/madan712/3912272
相关参考 https://poi.apache.org/spreadsheet/examples.html