1500字范文 > Apache POI Excel WORD PowerPoint Visio处理

Apache POI Excel WORD PowerPoint Visio处理

时间：2019-05-07 03:10:27

一：简介

开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中，操作Excel目前有两个框架，一个是apache 的poi，另一个是 Java Excel

Apache POI 简介是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现”。

官方主页：/index.html

API文档：/apidocs/index.html

Java Excel是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。jxl 由于其小巧易用的特点, 逐渐已经取代了 POI-excel的地位, 成为了越来越多的java开发人员生成excel文件的首选。

由于apache poi 在项目中用的比较多，本篇博客只讲解apache poi，不讲jxl

二：Apache POI常用的类

HSSF －提供读写Microsoft Excel XLS格式档案的功能。XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。HWPF －提供读写Microsoft Word DOC97格式档案的功能。XWPF －提供读写Microsoft Word DOC格式档案的功能。HSLF －提供读写Microsoft PowerPoint格式档案的功能。HDGF －提供读Microsoft Visio格式档案的功能。HPBF －提供读Microsoft Publisher格式档案的功能。HSMF －提供读Microsoft Outlook格式档案的功能。

在开发中我们经常使用HSSF用来操作Excel处理表格数据，对于其它的不经常使用。

HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。

常用的类和方法

HSSFWorkbook ：工作簿，代表一个excel的整个文档 HSSFWorkbook(); // 创建一个新的工作簿HSSFWorkbook(InputStream inputStream); // 创建一个关联输入流的工作簿，可以将一个excel文件封装成工作簿HSSFSheet createSheet(String sheetname); 创建一个新的SheetHSSFSheet getSheet(String sheetName); 通过名称获取SheetHSSFSheet getSheetAt(int index); // 通过索引获取Sheet，索引从0开始HSSFCellStyle createCellStyle()；创建单元格样式int getNumberOfSheets(); 获取sheet的个数setActiveSheet(int index); 设置默认选中的工作表write();write(File newFile);write(OutputStream stream);HSSFSheet：工作表 HSSFRow createRow(int rownum); 创建新行，需要指定行号，行号从0开始HSSFRow getRow(int index); 根据索引获取指定的行int addMergedRegion(CellRangeAddress region); 合并单元格

CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格，需要指定要合并的首行、最后一行、首列、最后一列。autoSizeColumn(int column); 自动调整列的宽度来适应内容getLastRowNum(); 获取最后的行的索引，没有行或者只有一行的时候返回0setColumnWidth(int columnIndex, int width); 设置某一列的宽度，width=字符个数 * 256，例如20个字符的宽度就是20 * 256HSSFRow ：行 HSSFCell createCell(int column); 创建新的单元格HSSFCell setCell(shot index);HSSFCell getCell(shot index);setRowStyle(HSSFCellStyle style); 设置行样式short getLastCellNum(); 获取最后的单元格号，如果单元格有第一个开始算，lastCellNum就是列的个数setHeightInPoints(float height); 设置行的高度HSSFCell：单元格 setCellValue(String value); 设置单元格的值setCellType(); 设置单元格类型，如字符串、数字、布尔等setCellStyle(); 设置单元格样式String getStringCellValue(); 获取单元格中的字符串值setCellStyle(HSSFCellStyle style); 设置单元格样式，例如字体、加粗、格式化setCellFormula(String formula); 设置计算公式，计算的结果作为单元格的值，也提供了异常常用的函数，如求和”sum(A1,C1)”、日期函数、字符串相关函数、CountIf和SumIf函数、随机数函数等HSSFCellStyle ：单元格样式 setFont(Font font); 为单元格设置字体样式setAlignment(HorizontalAlignment align); // 设置水平对齐方式setVerticalAlignment(VerticalAlignment align); // 设置垂直对齐方式setFillPattern(FillPatternType fp);setFillForegroundColor(short bg); 设置前景色setFillBackgroundColor(short bg); 设置背景颜色HSSFFont：字体， setColor(short color); // 设置字体颜色setBold(boolean bold); // 设置是否粗体setItalic(boolean italic); 设置倾斜setUnderline(byte underline); 设置下划线HSSFName：名称HSSFDataFormat ：日期格式化HSSFHeader ： Sheet的头部HSSFFooter ：Sheet的尾部HSSFDateUtil ：日期工具HSSFPrintSetup ：打印设置HSSFErrorConstants：错误信息表

Excel中的工作簿、工作表、行、单元格中的关系：

一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成

三：基础示例

首先引入apache poi的依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dependency>

示例一：在桌面上生成一个Excel文件

public static void createExcel() throws IOException{// 获取桌面路径FileSystemView fsv = FileSystemView.getFileSystemView();String desktop = fsv.getHomeDirectory().getPath();String filePath = desktop + "/template.xls";File file = new File(filePath);OutputStream outputStream = new FileOutputStream(file);HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("Sheet1");HSSFRow row = sheet.createRow(0);row.createCell(0).setCellValue("id");row.createCell(1).setCellValue("订单号");row.createCell(2).setCellValue("下单时间");row.createCell(3).setCellValue("个数");row.createCell(4).setCellValue("单价");row.createCell(5).setCellValue("订单金额");row.setHeightInPoints(30); // 设置行的高度HSSFRow row1 = sheet.createRow(1);row1.createCell(0).setCellValue("1");row1.createCell(1).setCellValue("NO00001");// 日期格式化HSSFCellStyle cellStyle2 = workbook.createCellStyle();HSSFCreationHelper creationHelper = workbook.getCreationHelper();cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));sheet.setColumnWidth(2, 20 * 256); // 设置列的宽度HSSFCell cell2 = row1.createCell(2);cell2.setCellStyle(cellStyle2);cell2.setCellValue(new Date());row1.createCell(3).setCellValue(2);// 保留两位小数HSSFCellStyle cellStyle3 = workbook.createCellStyle();cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));HSSFCell cell4 = row1.createCell(4);cell4.setCellStyle(cellStyle3);cell4.setCellValue(29.5);// 货币格式化HSSFCellStyle cellStyle4 = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setFontName("华文行楷");font.setFontHeightInPoints((short)15);font.setColor(HSSFColor.RED.index);cellStyle4.setFont(font);HSSFCell cell5 = row1.createCell(5);cell5.setCellFormula("D2*E2"); // 设置计算公式// 获取计算公式的值HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);cell5 = e.evaluateInCell(cell5);System.out.println(cell5.getNumericCellValue());workbook.setActiveSheet(0);workbook.write(outputStream);outputStream.close();}

示例2：读取Excel，解析数据

public static void readExcel() throws IOException{FileSystemView fsv = FileSystemView.getFileSystemView();String desktop = fsv.getHomeDirectory().getPath();String filePath = desktop + "/template.xls";FileInputStream fileInputStream = new FileInputStream(filePath);BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);HSSFSheet sheet = workbook.getSheet("Sheet1");int lastRowIndex = sheet.getLastRowNum();System.out.println(lastRowIndex);for (int i = 0; i <= lastRowIndex; i++) {HSSFRow row = sheet.getRow(i);if (row == null) { break; }short lastCellNum = row.getLastCellNum();for (int j = 0; j < lastCellNum; j++) {String cellValue = row.getCell(j).getStringCellValue();System.out.println(cellValue);}}bufferedInputStream.close();}

四：Java Web 中导出和导入Excel

1、导出示例

@SuppressWarnings("resource")@RequestMapping("/export") public void exportExcel(HttpServletResponse response, HttpSession session, String name) throws Exception { String[] tableHeaders = {"id", "姓名", "年龄"}; HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("Sheet1");HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);Font font = workbook.createFont(); font.setColor(HSSFColor.RED.index); font.setBold(true);cellStyle.setFont(font);// 将第一行的三个单元格给合并sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));HSSFRow row = sheet.createRow(0);HSSFCell beginCell = row.createCell(0);beginCell.setCellValue("通讯录"); beginCell.setCellStyle(cellStyle);row = sheet.createRow(1);// 创建表头for (int i = 0; i < tableHeaders.length; i++) {HSSFCell cell = row.createCell(i);cell.setCellValue(tableHeaders[i]);cell.setCellStyle(cellStyle); }List<User> users = new ArrayList<>();users.add(new User(1L, "张三", 20));users.add(new User(2L, "李四", 21));users.add(new User(3L, "王五", 22));for (int i = 0; i < users.size(); i++) {row = sheet.createRow(i + 2);User user = users.get(i);row.createCell(0).setCellValue(user.getId()); row.createCell(1).setCellValue(user.getName()); row.createCell(2).setCellValue(user.getAge()); }OutputStream outputStream = response.getOutputStream(); response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=template.xls");workbook.write(outputStream);outputStream.flush(); outputStream.close();}

2、导入示例

1、使用SpringMVC上传文件，需要用到commons-fileupload

<dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3</version></dependency>

2、需要在spring的配置文件中配置一下multipartResolver

<bean name="multipartResolver" class="org.springframework.monsMultipartResolver"> <property name="defaultEncoding" value="UTF-8" /></bean>

3、index.jsp

<a href="/Spring-Mybatis-Druid/user/export">导出</a> <br/><form action="/Spring-Mybatis-Druid/user/import" enctype="multipart/form-data" method="post"><input type="file" name="file"/> <input type="submit" value="导入Excel"></form>

4、解析上传的.xls文件

@SuppressWarnings("resource")@RequestMapping("/import")public void importExcel(@RequestParam("file") MultipartFile file) throws Exception{InputStream inputStream = file.getInputStream();BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);//HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());HSSFSheet sheet = workbook.getSheetAt(0);int lastRowNum = sheet.getLastRowNum();for (int i = 2; i <= lastRowNum; i++) {HSSFRow row = sheet.getRow(i);int id = (int) row.getCell(0).getNumericCellValue();String name = row.getCell(1).getStringCellValue();int age = (int) row.getCell(2).getNumericCellValue();System.out.println(id + "-" + name + "-" + age);}}

导出效果：

导入效果：

项目示例代码下载地址：
/detail/vbirdbest/9861536

// apache poi操作(适用于word )import java.io.FileOutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Map.Entry;import org.apache.poi.POIXMLDocument;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.xwpf.usermodel.XWPFDocument;import org.apache.poi.xwpf.usermodel.XWPFParagraph;import org.apache.poi.xwpf.usermodel.XWPFRun;import org.apache.poi.xwpf.usermodel.XWPFTable;import org.apache.poi.xwpf.usermodel.XWPFTableCell;import org.apache.poi.xwpf.usermodel.XWPFTableRow;/*** 适用于word poi 版本 3.7*/public class WordPoiUtil {/*** 根据指定的参数值、模板，生成 word 文档* * @param param* 需要替换的变量* @param template* 模板*/public static XWPFDocument generateWord(Map<String, Object> param,String template) {XWPFDocument doc = null;try {OPCPackage pack = POIXMLDocument.openPackage(template);doc = new XWPFDocument(pack);if (param != null && param.size() > 0) {// 处理段落List<XWPFParagraph> paragraphList = doc.getParagraphs();processParagraphs(paragraphList, param, doc);// 处理表格Iterator<XWPFTable> it = doc.getTablesIterator();while (it.hasNext()) {XWPFTable table = it.next();List<XWPFTableRow> rows = table.getRows();for (XWPFTableRow row : rows) {List<XWPFTableCell> cells = row.getTableCells();for (XWPFTableCell cell : cells) {List<XWPFParagraph> paragraphListTable = cell.getParagraphs();processParagraphs(paragraphListTable, param, doc);}}}}} catch (Exception e) {e.printStackTrace();}return doc;}/*** 处理段落* * @param paragraphList*/public static void processParagraphs(List<XWPFParagraph> paragraphList,Map<String, Object> param, XWPFDocument doc) {if (paragraphList != null && paragraphList.size() > 0) {for (XWPFParagraph paragraph : paragraphList) {boolean addReplace = false;List<XWPFRun> runs = paragraph.getRuns();//每个需要替换的key的run的位置的集合List<Integer> replaceRuns = new ArrayList<Integer>();//每个段落的所有的key run的集合List<List<Integer>> perReplaceRunList = new ArrayList<List<Integer>>();for (int i = 0; i< runs.size();i++){String text = runs.get(i).getText(0);if(addReplace){replaceRuns.add(i);}if(text != null && text.contains("#")){addReplace = true;replaceRuns.add(i);}if(text != null && text.contains("}")){addReplace = false;perReplaceRunList.add(replaceRuns);replaceRuns = new ArrayList<Integer>();}}for(int i=0;i<perReplaceRunList.size();i++){List<Integer> runsList = perReplaceRunList.get(i);System.out.println("==========================");StringBuffer textSb = new StringBuffer();for(int j = 0;j<runsList.size();j++){System.out.println("============replace_runs"+runs.get(runsList.get(j)).getText(0));textSb.append(runs.get(runsList.get(j)).getText(0));}String replaceStr = textSb.toString();for(int j = 0; j<runsList.size();j++){for (Entry<String, Object> entry : param.entrySet()) {String key = entry.getKey();if (replaceStr.indexOf(key) != -1) {Object value = entry.getValue();if (value instanceof String) {// 文本替换replaceStr = replaceStr.replace(key, value.toString());}}}}System.out.println("==========="+replaceStr);for(int j = 0;j<runsList.size();j++){if(j == 0){runs.get(runsList.get(j)).setText(replaceStr, 0);}else{runs.get(runsList.get(j)).setText("", 0);}}for(int j = 0;j<runsList.size();j++){System.out.println("============转换后"+runs.get(runsList.get(j)).getText(0));}}}}}public static List<String> getReplaceFields(String template){List<String> replaceFields = new ArrayList<String>();XWPFDocument doc = null;try {OPCPackage pack = POIXMLDocument.openPackage(template);doc = new XWPFDocument(pack);// 处理段落List<XWPFParagraph> paragraphList = doc.getParagraphs();replaceFields.addAll(getFields(paragraphList));// 处理表格Iterator<XWPFTable> it = doc.getTablesIterator();while (it.hasNext()) {XWPFTable table = it.next();List<XWPFTableRow> rows = table.getRows();for (XWPFTableRow row : rows) {List<XWPFTableCell> cells = row.getTableCells();for (XWPFTableCell cell : cells) {List<XWPFParagraph> paragraphListTable = cell.getParagraphs();replaceFields.addAll(getFields(paragraphListTable));}}}} catch (Exception e) {e.printStackTrace();}return replaceFields;}/*** 获取段落的需要替换的字段* @param paragraphList* @return*/public static List<String> getFields(List<XWPFParagraph> paragraphList) {List<String> fieldList = new ArrayList<String>();if (paragraphList != null && paragraphList.size() > 0) {for (XWPFParagraph paragraph : paragraphList) {boolean addReplace = false;List<XWPFRun> runs = paragraph.getRuns();//每个需要替换的key的run的位置的集合List<Integer> replaceRuns = new ArrayList<Integer>();//每个段落的所有的key run的集合List<List<Integer>> perReplaceRunList = new ArrayList<List<Integer>>();for (int i = 0; i< runs.size();i++){String text = runs.get(i).getText(0);if(addReplace){replaceRuns.add(i);}if(text != null && text.contains("#")){addReplace = true;replaceRuns.add(i);}if(text != null && text.contains("}")){addReplace = false;perReplaceRunList.add(replaceRuns);replaceRuns = new ArrayList<Integer>();}}for(int i=0;i<perReplaceRunList.size();i++){List<Integer> runsList = perReplaceRunList.get(i);System.out.println("==========================");StringBuffer textSb = new StringBuffer();for(int j = 0;j<runsList.size();j++){System.out.println("============replace_runs"+runs.get(runsList.get(j)).getText(0));textSb.append(runs.get(runsList.get(j)).getText(0));}String replaceStr = textSb.toString().trim();System.out.println("====replaceStr=" + replaceStr.substring(replaceStr.indexOf("#")+2,replaceStr.length()-1));// System.out.println(replaceStr.substring(2,replaceStr.length()-1));fieldList.add(replaceStr.substring(replaceStr.indexOf("#")+2,replaceStr.length()-1));}}}return fieldList;}}