使用POI操作Excel和Word

前言:今天在项目中看到有小模块是上传Excel解释后保存到数据库的操作,好奇之下去了解了如何使用Apache POI操纵Excel和Word,以下为小分享


什么是POI?
      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;}@Beforepublic void setUp() throws InvalidFormatException, IOException {// 加载excel文件,自动判断是HSSF还是XSSFworkbook = WorkbookFactory.create(new File("E:/aaa.xls"));}/* * 读取一个已存在的Excel */@Testpublic 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 */@Testpublic 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 */@Testpublic 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);// 设置单元格valuecell.setCellValue("test");// 此处为设置Excel的样式,设置单元格内容居中,// 但这样设置方式并不常用,请留意下面的方法CellStyle cs = workbook.createCellStyle();cs.setAlignment(CellStyle.ALIGN_CENTER);cell.setCellStyle(cs);}}// 将Excel写出到文件流workbook.write(fos);}/* * 通过使用模板生成Excel文件,模板当中包含样式,  * 这样我们只为模板填充数据就可以有相应的样式 */@Testpublic 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()来获取到该单元格对象的样式, * 获取到样式只要将此样式放入新创建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;// 创建Word输出流private FileOutputStream fos;@Beforepublic 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"));}@Testpublic void testReadWord2003() {// 直接通过getText()获取文本String text = word2003.getText();// 获取总页数doc2003.getSummaryInformation().getPageCount();// 获取总字数doc2003.getSummaryInformation().getWordCount();Assert.assertNotNull(text);}@Testpublic void testReadWord2007() {// 直接通过getText()获取文本String text = word2007.getText();// 获取总页数doc2007.getProperties().getExtendedProperties().getUnderlyingProperties().getPages();// 获取去除空格的总页数doc2007.getProperties().getExtendedProperties().getUnderlyingProperties().getCharacters();// 获取带空格的总页数doc2007.getProperties().getExtendedProperties().getUnderlyingProperties().getCharactersWithSpaces();Assert.assertNotNull(text);}/* * 演示如何创建Word文档 */@Testpublic void testWriteWord2007() throws IOException {XWPFDocument doc = new XWPFDocument();// 创建段落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);// 创建1段文字,通过段落创建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);}@Afterpublic void tearDown() throws IOException {if (fis2003 != null) {fis2003.close();}if (fis2007 != null) {fis2007.close();}if (fos != null) {fos.close();}}}

总结:
      使用POI读取写出Excel文件非常方便,但写出的时候需要注意,是不包含样式的,但若然要结合样式来写出Excel或Word就会变得非常复杂,当有业务需求的时候建议上Apache POI的官网查看相关的API和Demo