首先增加Maven的依賴
<!-- POI核心依賴 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<!-- 為POI支持Office Open XML -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
<!-- 支持Word文檔的操作 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.8</version>
</dependency>
以下為操作Excel的測試類
package com.accentrix.ray;
import Java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Before;
import org.junit.Test;
public class TestExcel {
private Workbook workbook;
/*
* 由于Excel當中的單元格Cell存在類型,若獲取類型錯誤 就會產(chǎn)生錯誤,
* 所以通過此方法將Cell內(nèi)容全部轉(zhuǎn)換為String類型
*/
private String getCellValue(Cell cell) {
String str = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
str = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
str = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
str = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
str = String.valueOf(cell.getStringCellValue());
break;
default:
str = null;
break;
}
return str;
}
@Before
public void setUp() throws InvalidFormatException, IOException {
// 加載excel文件,自動判斷是HSSF還是XSSF
workbook = WorkbookFactory.create(new File("E:/aaa.xls"));
}
/*
* 讀取一個已存在的Excel
*/
@Test
public void testReadExcel() throws InvalidFormatException, IOException {
// 獲取第一個工作目錄,下標從0開始
Sheet sheet = workbook.getSheetAt(0);
// 獲取該工作目錄最后一行的行數(shù)
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
// 獲取下標為i的行
Row row = sheet.getRow(i);
// 獲取該行單元格個數(shù)
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
// 獲取下標為j的單元格
Cell cell = row.getCell(j);
// 調(diào)用獲取方法
String cellValue = this.getCellValue(cell);
}
}
}
/*
* 使用Foreach方式讀取Excel
*/
@Test
public void testForeachReadExcel() {
// 根據(jù)sheet的名字獲取
Sheet sheet = workbook.getSheet("test");
// 處了上面testReadExcel的方式讀取以外,還支持foreach的方式讀取
for (Row row : sheet) {
for (Cell cell : row) {
String cellValue = this.getCellValue(cell);
System.out.println(cellValue);
}
}
}
/*
* 創(chuàng)建簡單的Excel
*/
@Test
public void testWriteExcel() throws IOException {
// 創(chuàng)建一個XSSF的Excel文件
workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream("E:/test.xlsx");
// 創(chuàng)建名稱為test的工作目錄
Sheet sheet = workbook.createSheet("test");
/*
* 創(chuàng)建1個10行x10列的工作目錄
*/
for (int i = 0; i < 10; i++) {
// 創(chuàng)建一行
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
// 創(chuàng)建一個單元格
Cell cell = row.createCell(j);
// 設置單元格value
cell.setCellValue("test");
// 此處為設置Excel的樣式,設置單元格內(nèi)容居中,
// 但這樣設置方式并不常用,請留意下面的方法
CellStyle cs = workbook.createCellStyle();
cs.setAlignment(CellStyle.ALIGN_CENTER);
cell.setCellStyle(cs);
}
}
// 將Excel寫出到文件流
workbook.write(fos);
}
/*
* 通過使用模板生成Excel文件,模板當中包含樣式,
* 這樣我們只為模板填充數(shù)據(jù)就可以有相應的樣式
*/
@Test
public void testWriteExcelByTemplate() throws InvalidFormatException,
IOException {
String fileName = "test.xlsx";
// 通過類加載器獲取模板
workbook = WorkbookFactory.create(this.getClass().getClassLoader()
.getResourceAsStream(fileName));
FileOutputStream fos = new FileOutputStream("E:/test.xlsx");
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
/*
* 此時可以通過getCellStyle()來獲取到該單元格對象的樣式,
* 獲取到樣式只要將此樣式放入新創(chuàng)建Excel單元格中,
* 就可以完成樣式的替換 獲取可以直接填充此模板再進行輸出,
* 注意插入新一行時,要使用sheet.shiftRows(0, 7, 1, true, true);
* 這里代表從第0行到第7向下移動1行,保持寬度和高度
*/
CellStyle cellStyle = cell.getCellStyle();
workbook.write(fos);
}
}
以下為操作Word的測試類
package com.accentrix.ray;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import junit.framework.Assert;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.poi.xwpf.usermodel.Borders;
import org.apache.poi.xwpf.usermodel.LineSpacingRule;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.TextAlignment;
import org.apache.poi.xwpf.usermodel.UnderlinePatterns;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class TestWord {
// 生成Word2007版本
private FileInputStream fis2007;
private XWPFDocument doc2007;
private XWPFWordExtractor word2007;
// 生成Word2003版本
private FileInputStream fis2003;
private HWPFDocument doc2003;
private WordExtractor word2003;
// 創(chuàng)建Word輸出流
private FileOutputStream fos;
@Before
public void setUp() throws Exception {
// 初始化2007版本
fis2007 = new FileInputStream(new File("D:/test.docx"));
doc2007 = new XWPFDocument(fis2007);
word2007 = new XWPFWordExtractor(doc2007);
// 初始化2003版本
fis2003 = new FileInputStream(new File("D:/test2.doc"));
doc2003 = new HWPFDocument(fis2003);
word2003 = new WordExtractor(doc2003);
// 初始化輸出流
fos = new FileOutputStream(new File("D:/testCreateWord.docx"));
}
@Test
public void testReadWord2003() {
// 直接通過getText()獲取文本
String text = word2003.getText();
// 獲取總頁數(shù)
doc2003.getSummaryInformation().getPageCount();
// 獲取總字數(shù)
doc2003.getSummaryInformation().getWordCount();
Assert.assertNotNull(text);
}
@Test
public void testReadWord2007() {
// 直接通過getText()獲取文本
String text = word2007.getText();
// 獲取總頁數(shù)
doc2007.getProperties().getExtendedProperties()
.getUnderlyingProperties().getPages();
// 獲取去除空格的總頁數(shù)
doc2007.getProperties().getExtendedProperties()
.getUnderlyingProperties().getCharacters();
// 獲取帶空格的總頁數(shù)
doc2007.getProperties().getExtendedProperties()
.getUnderlyingProperties().getCharactersWithSpaces();
Assert.assertNotNull(text);
}
/*
* 演示如何創(chuàng)建Word文檔
*/
@Test
public void testWriteWord2007() throws IOException {
XWPFDocument doc = new XWPFDocument();
// 創(chuàng)建段落
XWPFParagraph p1 = doc.createParagraph();
// 設置樣式,此時樣式為一個正方形包圍文字
p1.setAlignment(ParagraphAlignment.CENTER);
p1.setBorderBottom(Borders.DOUBLE);
p1.setBorderTop(Borders.DOUBLE);
p1.setBorderRight(Borders.DOUBLE);
p1.setBorderLeft(Borders.DOUBLE);
p1.setBorderBetween(Borders.SINGLE);
p1.setVerticalAlignment(TextAlignment.TOP);
// 創(chuàng)建1段文字,通過段落創(chuàng)建
XWPFRun r1 = p1.createRun();
// 設置是否粗體
r1.setBold(true);
r1.setText("The quick brown fox");
r1.setBold(true);
r1.setFontFamily("Courier");
r1.setUnderline(UnderlinePatterns.DOT_DOT_DASH);
r1.setTextPosition(100);
XWPFParagraph p2 = doc.createParagraph();
p2.setAlignment(ParagraphAlignment.RIGHT);
p2.setBorderBottom(Borders.DOUBLE);
p2.setBorderTop(Borders.DOUBLE);
p2.setBorderRight(Borders.DOUBLE);
p2.setBorderLeft(Borders.DOUBLE);
p2.setBorderBetween(Borders.SINGLE);
XWPFRun r2 = p2.createRun();
r2.setText("Hello Hello Hello Hello Hello Hello Hello");
r2.setStrike(true);
r2.setFontSize(20);
XWPFRun r3 = p2.createRun();
r3.setText("World World World World World World World");
r3.setStrike(true);
r3.setFontSize(20);
XWPFParagraph p3 = doc.createParagraph();
p3.setWordWrap(true);
// 設置該段落填充滿本頁,下面在顯示新文本將在下一頁顯示
p3.setPageBreak(true);
p3.setAlignment(ParagraphAlignment.DISTRIBUTE);
p3.setAlignment(ParagraphAlignment.BOTH);
p3.setSpacingLineRule(LineSpacingRule.EXACT);
p3.setIndentationFirstLine(600);
doc.write(fos);
}
@After
public void tearDown() throws IOException {
if (fis2003 != null) {
fis2003.close();
}
if (fis2007 != null) {
fis2007.close();
}
if (fos != null) {
fos.close();
}
}
}
總結(jié):
使用POI讀取寫出Excel文件非常方便,但寫出的時候需要注意,是不包含樣式的,但若然要結(jié)合樣式來寫出Excel或Word就會變得非常復雜,當有業(yè)務需求的時候建議上Apache POI的官網(wǎng)查看相關(guān)的API和Demo
