Java中常见的用来操作 Excel 的方式有2种：JXL和POI。JXL只能对 Excel进行操作，且只支持到 Excel 95-2000的版本。而POI是Apache 的开源项目，由Java编写的跨平台 Java API，可操作 Microsoft Office。借助POI，可以方便的生成数据报表，数据批量上传，数据备份等工作。
一.简单使用
1.创建Maven工程导入POI坐标
<!-- poi 相关 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.0.1</version></dependency>
2.使用API
HSSF : 读写 Microsoft Excel XLS 格式文档
XSSF : 读写 Microsoft Excel OOXML XLSX 格式文档
SXSSF : 读写 Microsoft Excel OOXML XLSX 格式文档
HWPF : 读写 Microsoft Word DOC 格式文档
HSLF : 读写 Microsoft PowerPoint 格式文档
HDGF : 读 Microsoft Visio 格式文档
HPBF : 读 Microsoft Publisher 格式文档
HSMF : 读 Microsoft Outlook 格式文档
3.操作示例
//1.创建Excel对象XSSFWorkbook wb = new XSSFWorkbook();//2.创建Sheet对象Sheet sheet = wb.createSheet();//3.创建行对象(索引从0开始)Row nRow = sheet.createRow(0);//4.设置行高和列宽nRow.setHeightInPoints(26.25f);sheet.setColumnWidth(1,26*256); //(列的索引,列宽*256(理解为固定写法))//5.创建单元格对象(索引从0开始)Cell nCell = nRow.createCell(0);//6.设置单元格内容nCell.setCellValue("dinTalk");//==============================//7.创建单元格样式对象CellStyle style = wb.createCellStyle();//8.创建字体对象Font font = wb.createFont();//9.设置字体和其大小及效果font.setFontName("黑体");font.setFontHeightInPoints((short)12);font.setBold(true); //加粗//10.设置样式style.setFont(font);style.setAlignment(HorizontalAlignment.CENTER); //横向居中style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中style.setBorderTop(BorderStyle.THIN); //上细线style.setBorderBottom(BorderStyle.THIN); //下细线style.setBorderLeft(BorderStyle.THIN); //左细线style.setBorderRight(BorderStyle.THIN); //右细线//11.为单元格应用样式nCell.setCellStyle(style);
二.使用模板
1.模板打印(下载)
我们通过自定义生成 Excel 报表文件很是麻烦，特别是字体、样式比较复杂的时候。这时候我们可以考虑使用准备好的 Excel 模板，这样我们只需关注模板中的数据即可。
制作并加载Excel 模板，填充数据响应到浏览器（下载）
/*** 下载用户新增表* @param inputDate 格式为:2019-01*/@RequestMapping(value = "/printExcel",name = "下载用户新增表")public void printExcel(String inputDate) throws Exception {//1.用servletContext对象获取excel模板的真实路径String templatePath = session.getServletContext().getRealPath("/dintalk/xlsprint/dtUSER.xlsx");//2.读取excel模板,创建excel对象XSSFWorkbook wb = new XSSFWorkbook(templatePath);//3.读取sheet对象Sheet sheet = wb.getSheetAt(0);//4.定义一些可复用的对象int rowIndex = 0; //行的索引int cellIndex = 1; //单元格的索引Row nRow = null;Cell nCell = null;//5.读取大标题行nRow = sheet.getRow(rowIndex++); // 使用后 +1//6.读取大标题的单元格nCell = nRow.getCell(cellIndex);//7.设置大标题的内容String bigTitle = inputDate.replace("-0","-").replace("-","年")+"月份新增用户表";nCell.setCellValue(bigTitle);//8.跳过第二行(模板的小标题,我们要用)rowIndex++;//9.读取第三行,获取它的样式nRow = sheet.getRow(rowIndex);//读取行高float lineHeight = nRow.getHeightInPoints();//10.获取第三行的5个单元格中的样式CellStyle cs1 = nRow.getCell(cellIndex++).getCellStyle();CellStyle cs2 = nRow.getCell(cellIndex++).getCellStyle();CellStyle cs3 = nRow.getCell(cellIndex++).getCellStyle();CellStyle cs4 = nRow.getCell(cellIndex++).getCellStyle();CellStyle cs5 = nRow.getCell(cellIndex++).getCellStyle();//11.通过月份查询新增用户列表List<User> newUserList =UserService.findByAddTime(inputDate);//12.遍历数据for(User user : newUserList){//13.创建数据行nRow = sheet.createRow(rowIndex++);//16.设置数据行高nRow.setHeightInPoints(lineHeight);//17.重置cellIndex,从第一列开始写数据cellIndex = 1;//18.创建数据单元格，设置单元格内容和样式//用户名nCell = nRow.createCell(cellIndex++);nCell.setCellStyle(cs1);nCell.setCellValue(user.getUserName());//性别nCell = nRow.createCell(cellIndex++);nCell.setCellStyle(cs2);nCell.setCellValue(user.getSex());//年龄nCell = nRow.createCell(cellIndex++);nCell.setCellStyle(cs3);nCell.setCellValue(user.getAge());//手机号nCell = nRow.createCell(cellIndex++);nCell.setCellStyle(cs4);nCell.setCellValue(user.getPhone());//邮箱nCell = nRow.createCell(cellIndex++);nCell.setCellStyle(cs5);nCell.setCellValue(user.getEmail());}//最后，下载新增用户表，字节数组的输出流，它可存可取，带缓冲区ByteArrayOutputStream bos = new ByteArrayOutputStream();wb.write(bos); //将工作簿写到输出流中new DownloadUtil().download(bos,response,bigTitle+".xlsx");bos.close();wb.close();}
2.批量导入(上传)
在添加数据时，通过批量导入可大大减少人力。但是批量导入需要代码解析固定格式的模板，因此我们最好给用户提供模板下载功能。我们同样以导入用户表为例：
统一 excel 模板格式
/*** 批量导入用户* @param companyId* @param file* @return*/@RequestMapping("/import")public String ImportExcel(String companyId, MultipartFile file) throws IOException {//定义一个list 集合保存从excel解析的用户List<User> list = new ArrayList<>();//1.读取上传的文件InputStream inputStream = file.getInputStream();XSSFWorkbook wb = new XSSFWorkbook(inputStream);//2.获取工作表对象XSSFSheet sheet = wb.getSheetAt(0);//3.得到行的迭代器Iterator<Row> iterator = sheet.iterator();int rowNum = 0;while (iterator.hasNext()) {Row row = iterator.next();//跳过标题行if (rowNum == 0) {rowNum++;continue;}//4.遍历,把每一行数据存到Object数组中Object[] obj = new Object[6];for (int i = 1; i < 6; i++) {obj[i] = getValue(row.getCell(i));//获取到单元格内的数据，方法见下}//5.创建用户对象(用户实体类的有参构造方法)User user = new User(obj, companyId);//6.将用户对象保存到集合中list.add(user);}//7.读取完数据后,调用service层方法进行批量保存UserService.saveAll(list);//8.重定向到企业列表return "redirect:/dintalk/company/list.do";}/*** 获取单元格内的数据,并进行格式转换* @param cell* @return*/private Object getValue(Cell cell) {switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case BOOLEAN:return cell.getBooleanCellValue();case NUMERIC:// 数值和日期均是此类型,需进一步判断if (DateUtil.isCellDateFormatted(cell)) {//是日期类型return cell.getDateCellValue();} else {//是数值类型return cell.getNumericCellValue();}default:return null;}}
三.百万数据报表的导出导入
当我们碰到数据量比较大的时候（百万级），我们该如何通过使用 POI 对百万级数据报表进行导入和导出的操作呢？我们知道，Excel可以分为早期的 Excel2003版本（使用POI的HSSF对象操作）和 Excel2007版本（使用POI的 XSSF操作），两者对百万数据的支持如下：
HSSFWorkbook 最大行数和列数限制 最大支持65536行
XSSFWorkbook 最大支持1048576行
XSSFWorkbook 单个 sheet 表就支持近百万条数据。但实际运行时还可能存在问题，原因是执行 POI 报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就有导致 OOM 的风险。我们可以使用JDK提供的性能工具 Jvisualvm 来监视程序的运行情况，包括 CUP,垃圾回收，内存的分配和使用情况（Jvisualvm位于JAVA_HOME/bin目录下，双击打开即可）。
1.百万数据报表导出
基于 XSSFWork 导出 Excel 报表，是通过将所有单元格对象保存到内存中，当所有的 Excel 单元格全部创建完成之后一次性写入到 Excel 并导出。当百万数据级别的Excel 导出时，随着表格的不断创建，内存中对象越来越多，直至内存溢出。Apache Poi 提供了 SXSSFWork 对象，专门用于处理大数据量 Excel 报表导出。 在实例化 SXSSFWork 这个对象时，可以指定在内存中所产生的 POI 导出相关对象的数量（默认 100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML 的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至 Excel 导出完成。SXSSFWorkbook它支持百万级数据的POI,但是不支持模板打印也不支持太多的样式。因此我们需要通过自定义的方式来进行导出。
/** 下载用户新增表* @param inputDate 格式为:2019-01*/@RequestMapping("/printExcel")public void printExcel(String inputDate)throws Exception{//1.创建Excel对象XSSFWorkbook wb = new XSSFWorkbook();SXSSFWorkbook wb = new SXSSFWorkbook(1000);//默认值是100//2.创建Sheet对象Sheet sheet = wb.createSheet();//3.定义一些可复用的对象int rowIndex = 0;//行的索引int cellIndex = 1;//单元格的索引Row nRow = null;Cell nCell = null;//4.设置列的宽度（列索引，列宽*256 理解为固定写法）sheet.setColumnWidth(1,26*256);sheet.setColumnWidth(2,12*256);sheet.setColumnWidth(3,29*256);sheet.setColumnWidth(4,12*256);sheet.setColumnWidth(5,15*256)；//5.创建大标题行 大标题：2019年5月份新增用户表nRow = sheet.createRow(rowIndex++);//使用的是0,使用完了+1//设置大标题行的高度nRow.setHeightInPoints(36);//6.创建大标题的单元格nCell = nRow.createCell(cellIndex);//7.合并单元格sheet.addMergedRegion(new CellRangeAddress(0,0,1,5));//8.设置大标题内容String bigTitle = inputDate.replaceAll("-0","-").replaceAll("-","年") + "月份新增用户表";//inputDate 2015-01 2015年1月份出货表nCell.setCellValue(bigTitle);//9.创建小标题内容String[] titles = new String[]{"用户名","性别","年龄","手机号","邮箱"};//10.创建小标题行nRow = sheet.createRow(rowIndex++);//使用的是1，使用完了再加1//设置小标题行高nRow.setHeightInPoints(26.25f);//12.创建小标题的单元格for(String title : titles){nCell = nRow.createCell(cellIndex++);//设置小标题内容nCell.setCellValue(title);}//13.获取要生成的数据（数据库内的用户数据）List<User> list = UserService.findUserByAddTime(inputDate);//14.遍历数据for(User user : list){for(int i=0;i<5000;i++) {//15.创建数据行nRow = sheet.createRow(rowIndex++);//16.设置数据行高nRow.setHeightInPoints(24);//17.重置cellIndexcellIndex = 1;//18.创建数据单元格，设置单元格内容//用户名nCell = nRow.createCell(cellIndex++);nCell.setCellValue(user.getUserName());//性别nCell = nRow.createCell(cellIndex++);nCell.setCellValue(user.getSex());//年龄nCell = nRow.createCell(cellIndex++);nCell.setCellValue(user.getAge());//手机号nCell = nRow.createCell(cellIndex++);nCell.setCellValue(user.getPhone());//邮箱nCell = nRow.createCell(cellIndex++);nCell.setCellValue(user.getEmail());}}//最后，下载新增用户表文件，字节数组的输出流，它可存可取，带缓冲区ByteArrayOutputStream bos = new ByteArrayOutputStream();wb.write(bos);new DownloadUtil().download(bos,response,bigTitle+".xlsx");bos.close();wb.close();}
Tips: DownUtils的download方法：
/*** By Mr.Song 2019-05-17* @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream* @param response HttpServletResponse 写入response* @param returnName 返回的文件名*/public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{response.setContentType("application/octet-stream;charset=utf-8");returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码response.addHeader("Content-Disposition", "attachment;filename=" + returnName);response.setContentLength(byteArrayOutputStream.size());ServletOutputStream outputstream = response.getOutputStream(); //取得输出流byteArrayOutputStream.writeTo(outputstream); //写到输出流byteArrayOutputStream.close(); //关闭outputstream.flush(); //刷数据}
2.百万数据报表导入
导入，其实就是读取，读取excel的两种思路：
第一种：全部读取
优势：对excel的增删改查都方便
弊端：由于要加载完整合excel文件，如果文件过大时，对内存消耗严重
第二种：按事件触发
触发到什么事件，就读什么内容。
事件分为：
读到行的开始
读到行的结束
读到一行的内容
优势：执行解析效率高，因为它是按照事件触发的。一次只读一行数据
弊端：不利于保存，更新和删除。因为它没有读完整个excel，所以对整个excel的结构不清楚。
它适用于数据量级比较大的情况
第一步：导入POI坐标后创建处理器
/**这个类谁用谁写（读取excel内容要做的事，实现接口，重写方法）* @author Mr.song* @date 2019/05/19 20:11*/public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {/*** 每一行创建一个user对象,当此行解析结束之后，打印user对象*/private User user;//开始解析某一行，int ：行号@Overridepublic void startRow(int i) {if (i >= 2) { //跳过标题行，开始创建user对象user = new User();}}//此行解析结束@Overridepublic void endRow(int i) {System.out.println(user);//这里简单打印，可以存入列表。当列表内user对象大于1000时，存入数据库}/*** 获取当前行的每一个单元格数据* @param cellName ： 当前单元格名称 ： B32 C23 DXX* @param cellValue ： 当前单元格数据* @param xssfComment ： 单元格注释* 用户名 性别 年龄 手机号 邮箱*/@Overridepublic void cell(String cellName, String cellValue, XSSFComment xssfComment) {String name = cellName.substring(0, 1);//B2--->B F2---->Fif (user != null) {switch (name) {case "B": {user.setUserName(cellValue);break;}case "C": {user.setSex(cellValue);break;}case "D": {user.setAge(Integer.parseInt(cellValue));break;}case "E": {user.setPhone(cellValue);break;}case "F": {user.setEmail(cellValue);break;}default: {break;}}}}}
第二步：创建解析器
/**解析器，固定写法* @author Mr.song* @date 2019/05/19 20:08*/public class ExcelParse {public void parse (String path) throws Exception {//解析器SheetHandler hl = new SheetHandler();//1.根据 Excel 获取 OPCPackage 对象OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);try {//2.创建 XSSFReader 对象XSSFReader reader = new XSSFReader(pkg);//3.获取 SharedStringsTable 对象SharedStringsTable sst = reader.getSharedStringsTable();//4.获取 StylesTable 对象StylesTable styles = reader.getStylesTable();XMLReader parser = XMLReaderFactory.createXMLReader();// 处理公共属性parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, hl,false));XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)reader.getSheetsData();//逐行读取逐行解析while (sheets.hasNext()) {InputStream sheetstream = sheets.next();InputSource sheetSource = new InputSource(sheetstream);try {parser.parse(sheetSource);} finally {sheetstream.close();}}} finally {pkg.close();}}//测试解析桌面百万级excel文件public static void main(String[] args)throws Exception {new ExcelParse().parse("C:\\Users\\sh\\Desktop\\User.xlsx");}}
测试时，可以通过 Jvisualvm 来监视程序的运行情况，包括 CUP,垃圾回收，内存的分配和使用情况（Jvisualvm位于JAVA_HOME/bin目录下，双击打开即可）。
文章转载自顶哥说，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
