使用POI操作Excel和Word
- - ITeye博客前言:今天在项目中看到有小模块是上传Excel解释后保存到数据库的操作,好奇之下去了解了如何使用Apache POI操纵Excel和Word,以下为小分享. POI是Apache下的一个项目,是用Java编写的开源框架,提供API供开发者直接操作Microsoft Office(Excel,Word,PowerPoint...).
<!-- 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>
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); } /* * 通过使用模板生成Excel文件,模板当中包含样式, * 这样我们只为模板填充数据就可以有相应的样式 */ @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()来获取到该单元格对象的样式, * 获取到样式只要将此样式放入新创建Excel单元格中, * 就可以完成样式的替换 获取可以直接填充此模板再进行输出, * 注意插入新一行时,要使用sheet.shiftRows(0, 7, 1, true, true); * 这里代表从第0行到第7向下移动1行,保持宽度和高度 */ CellStyle cellStyle = cell.getCellStyle(); workbook.write(fos); } }
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; @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(); // 获取总页数 doc2003.getSummaryInformation().getPageCount(); // 获取总字数 doc2003.getSummaryInformation().getWordCount(); Assert.assertNotNull(text); } @Test public 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文档 */ @Test public 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); } @After public void tearDown() throws IOException { if (fis2003 != null) { fis2003.close(); } if (fis2007 != null) { fis2007.close(); } if (fos != null) { fos.close(); } } }