POIæ¯Apacheä¸çä¸ä¸ªé¡¹ç®,æ¯ç¨Javaç¼åçå¼æºæ¡æ¶,æä¾APIä¾å¼åè
ç´æ¥æä½Microsoft Office(Excel,Word,PowerPoint...)
POI为æ们带æ¥äºä»ä¹?
å¨å¾å¤çä¼ä¸å½ä¸,å¨èæ°æ®æ¯ä½¿ç¨Excelææ¡£ç,å 为Excelææ¡£çæ ¼å¼æ¹ä¾¿,ä¹è½å¥ç¨å
¬å¼,èä¼ä¸ç¨åºæ¯åå¨å¨æ°æ®åºå½ä¸,è¿æ ·å°±éè¦ä¸ç§ä¸¤è
ä¹é´äºç¸è½¬æ¢çæ¹æ³,å½ä¼ä¸åå¼å§ä½¿ç¨ä¿¡æ¯åç管çç³»ç»æ¶,ä¹éè¦å°Excelçæ°æ®å½å
¥å°ç¨åºå½ä¸,è¿ç§éæ±æ¯é常æ®éç.
POI使ç¨:
é¦å
å¢å 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åå¨ç±»å,è¥è·åç±»åé误 å°±ä¼äº§çé误,
* æ以éè¿æ¤æ¹æ³å°Cellå
容å
¨é¨è½¬æ¢ä¸º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);
// è·å该工ä½ç®å½æåä¸è¡çè¡æ°
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
// è·åä¸æ 为içè¡
Row row = sheet.getRow(i);
// è·å该è¡åå
æ ¼ä¸ªæ°
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
// è·åä¸æ 为jçåå
æ ¼
Cell cell = row.getCell(j);
// è°ç¨è·åæ¹æ³
String cellValue = this.getCellValue(cell);
}
}
}
/*
* 使ç¨Foreachæ¹å¼è¯»åExcel
*/
@Test
public void testForeachReadExcel() {
// æ ¹æ®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);
}
}
}
/*
* å建ç®åçExcel
*/
@Test
public void testWriteExcel() throws IOException {
// å建ä¸ä¸ªXSSFçExcelæ件
workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream("E:/test.xlsx");
// å建å称为testçå·¥ä½ç®å½
Sheet sheet = workbook.createSheet("test");
/*
* å建1个10è¡x10åçå·¥ä½ç®å½
*/
for (int i = 0; i < 10; i++) {
// å建ä¸è¡
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
// å建ä¸ä¸ªåå
æ ¼
Cell cell = row.createCell(j);
// 设置åå
æ ¼value
cell.setCellValue("test");
// æ¤å¤ä¸ºè®¾ç½®Excelçæ ·å¼,设置åå
æ ¼å
容å±
ä¸,
// ä½è¿æ ·è®¾ç½®æ¹å¼å¹¶ä¸å¸¸ç¨,请çæä¸é¢çæ¹æ³
CellStyle cs = workbook.createCellStyle();
cs.setAlignment(CellStyle.ALIGN_CENTER);
cell.setCellStyle(cs);
}
}
// å°Excelååºå°æ件æµ
workbook.write(fos);
}
温馨提示:答案为网友推荐,仅供参考