JavaWeb项目操作Excel（导出、解析）
对于javaweb开发者来说，对于Excel的操作是必须要了解的知识点，自己原先也做过一些excel的操作，但是没有系统的整理过，所以每回使用的时候都是在重新查找信息，所以做了如下整理：
其实在javaweb开发中经常用到的Excel操作便是导出、解析。下面就从这两个方面来总结。
首先便是导出，在这个方面其实我主要用到的是两种方式：一种是依赖于freemarker，另一种则是POI
一、依赖于freemarker的Excel导出：
首先如果要使用freemarker的导出方式，在框架中必须使用了freemarker这个模板引擎，对于现在主流的mvc框架来说：spring mvc是可以集成freemarker的，spring boot则更近一步，spring mvc推荐使用jsp，而spring boot 推荐使用freemarker。至于Struts2本人没有涉及过不是很清楚，需要自己去探索啊！！不过应该也是没有问题的。然后就是Struts这个东西，我想现在应该没有公司在用Struts了吧！！！
好！！闲话不多直接上操作步骤，我这里的代码是使用的spring boot+freemarker的方式，至于dao层本文档不会涉及其内容：
（一）、首先要做的便是将要导出的Excel的模板准备好，接编写一个Excel表格，如图：
（二）、将Excel另存为xml格式：
然后用文本编辑器打开Excel文件看到内容为：
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Created>2006-09-16T00:00:00Z</Created> <LastSaved>2018-10-22T03:45:04Z</LastSaved> <Version>15.00</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <AllowPNG/> <RemovePersonalInformation/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>8010</WindowHeight> <WindowWidth>14805</WindowWidth> <WindowTopX>240</WindowTopX> <WindowTopY>105</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="宋体" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s16"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> </Style> <Style ss:ID="s17"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> </Style> <Style ss:ID="s18"> <Alignment ss:Vertical="Center" ss:WrapText="1"/> </Style> <Style ss:ID="s19"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <NumberFormat ss:Format="yyyy/m/d\ hh:mm:ss"/> </Style> </Styles> <Worksheet ss:Name="存储池"> <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5"> <Column ss:AutoFitWidth="0" ss:Width="123.75" ss:Span="3"/> <Row> <Cell ss:StyleID="s16"><Data ss:Type="String">存储池名</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="String">总容量（TB）</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="String">已使用容量（TB）</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="String">未使用容量（TB）</Data></Cell> </Row> <Row> <Cell ss:StyleID="s16"><Data ss:Type="String">pool0</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="Number">5.68</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="Number">1.23</Data></Cell> <Cell ss:StyleID="s17"><Data ss:Type="Number">2.33</Data></Cell> </Row> </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <PageSetup> <Header x:Margin="0.3"/> <Footer x:Margin="0.3"/> <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> </PageSetup> <Print> <ValidPrinterInfo/> <PaperSizeIndex>9</PaperSizeIndex> <HorizontalResolution>600</HorizontalResolution> <VerticalResolution>600</VerticalResolution> </Print> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveCol>4</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> <Worksheet ss:Name="报错日志"> <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5"> <Column ss:AutoFitWidth="0" ss:Width="153"/> <Column ss:AutoFitWidth="0" ss:Width="81.75"/> <Column ss:AutoFitWidth="0" ss:Width="605.25"/> <Row ss:AutoFitHeight="0" ss:Height="20.0625"> <Cell ss:StyleID="s16"><Data ss:Type="String">时间</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="String">级别</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="String">内容</Data></Cell> </Row> <Row ss:Height="27"> <Cell ss:StyleID="s19"><Data ss:Type="String" x:Ticked="1">2017/12/12 11:11:11</Data></Cell> <Cell ss:StyleID="s16"><Data ss:Type="String">error</Data></Cell> <Cell ss:StyleID="s18"><Data ss:Type="String">fdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsafdsasdfdsafsddddddddddddddddddddddddddfsaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa</Data></Cell> </Row> </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <PageSetup> <Header x:Margin="0.3"/> <Footer x:Margin="0.3"/> <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> </PageSetup> <Print> <ValidPrinterInfo/> <PaperSizeIndex>9</PaperSizeIndex> <HorizontalResolution>600</HorizontalResolution> <VerticalResolution>600</VerticalResolution> </Print> <Panes> <Pane> <Number>3</Number> <ActiveRow>1</ActiveRow> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook>
（三）、接下来便是将xml代码复制到一个新建的freemarker模板文件中便可以了
（四）、下面便是java代码的编写了，废话不多直接上代码：
1 @RequestMapping("/exportreport") 2 private String exportReport(Model model,HttpServletRequest request,HttpServletResponse response,int did) { 3 4 //获取要导入的信息 5 List<DPool> poolList=reportService.selectPoolByDid(did); 6 List<DAlarm> alarmList=reportService.selectAlarmByDid(did); 7 Device device = deviceDao.selectByPrimaryKey(did); 8 9 10 //将需要到导入的信息存入model中 11 model.addAttribute("poolList", poolList); 12 model.addAttribute("alarmList", alarmList); 13 14 15 //设置下载头部，通过response实现 16 response.reset(); 17 response.setContentType("application/vnd.ms-excel;charset=utf-8"); 18 try { 19 response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(device.getName()+"-report.xls", "UTF8")); 20 } catch (UnsupportedEncodingException e) { 21 e.printStackTrace(); 22 } 23 //带导出数据跳转到freemarker模板 24 return "generate_reports"; 25 }
（五）、最后便是利用freemarker的知识去编写，上面新建的模板了，将数据插入到模板中，其中有些地方需要注意一下：
这个地方的数字代表了这张工作表中有多少行
二、第二种方式便是利用POI了，步骤如下：
（一）、利用poi生成Excel表格
1 package cn.tools; 2 3 import java.text.DecimalFormat; 4 import java.util.List; 5 6 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 7 import org.apache.poi.hssf.usermodel.HSSFFont; 8 import org.apache.poi.hssf.usermodel.HSSFRow; 9 import org.apache.poi.hssf.usermodel.HSSFSheet; 10 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 11 import org.apache.poi.ss.usermodel.HorizontalAlignment; 12 import org.apache.poi.ss.usermodel.VerticalAlignment; 13 14 import cn.entitys.DAlarm; 15 import cn.entitys.DPool; 16 17 public class GenerateReport { 18 public static HSSFWorkbook Generate(List<DPool> poolList, List<DAlarm> alarmList){ 19 HSSFWorkbook workbook = new HSSFWorkbook(); 20 HSSFSheet firstsheet = workbook.createSheet("存储池"); 21 22 HSSFFont font = workbook.createFont(); 23 font.setFontName("宋体"); 24 font.setFontHeightInPoints((short) 11); 25 26 HSSFCellStyle cellStyle1 = workbook.createCellStyle(); 27 cellStyle1.setAlignment(HorizontalAlignment.CENTER); 28 cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER); 29 cellStyle1.setFont(font); 30 31 firstsheet.setColumnWidth(0, 20*265); 32 firstsheet.setColumnWidth(1, 20*265); 33 firstsheet.setColumnWidth(2, 20*265); 34 firstsheet.setColumnWidth(3, 20*265); 35 firstsheet.setDefaultColumnStyle(0, cellStyle1); 36 firstsheet.setDefaultColumnStyle(1, cellStyle1); 37 firstsheet.setDefaultColumnStyle(2, cellStyle1); 38 firstsheet.setDefaultColumnStyle(3, cellStyle1); 39 40 HSSFRow row = firstsheet.createRow(0); 41 row.setHeight((short) (13.5*20)); 42 row.createCell(0).setCellValue("存储池名"); 43 row.createCell(1).setCellValue("总容量（TB）"); 44 row.createCell(2).setCellValue("已使用容量（TB）"); 45 row.createCell(3).setCellValue("未使用容量（TB）"); 46 47 double d = Math.random()*10; 48 DecimalFormat dFormat = new DecimalFormat("#.00"); 49 for (int i=0;i<poolList.size();i++) { 50 DPool pool=poolList.get(i); 51 row = firstsheet.createRow(i+1); 52 row.setHeight((short) (13.5*20)); 53 row.createCell(0).setCellValue(pool.getName()); 54 row.createCell(1).setCellValue(pool.getCapacity()); 55 row.createCell(2).setCellValue(pool.getUsed_capacity()); 56 row.createCell(3).setCellValue(pool.getFree_capacity()); 57 } 58 59 HSSFSheet secondsheet = workbook.createSheet("报错日志"); 60 61 HSSFCellStyle cellStyle2 = workbook.createCellStyle(); 62 cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER); 63 cellStyle2.setAlignment(HorizontalAlignment.LEFT); 64 cellStyle2.setFont(font); 65 cellStyle2.setWrapText(true); 66 67 HSSFCellStyle cellStyle3 = workbook.createCellStyle(); 68 cellStyle3.setAlignment(HorizontalAlignment.CENTER); 69 cellStyle3.setFont(font); 70 71 secondsheet.setColumnWidth(0, 25*265); 72 secondsheet.setColumnWidth(1, 13*265); 73 secondsheet.setColumnWidth(2, 100*265); 74 secondsheet.setDefaultColumnStyle(0, cellStyle1); 75 secondsheet.setDefaultColumnStyle(1, cellStyle1); 76 secondsheet.setDefaultColumnStyle(2, cellStyle2); 77 78 HSSFRow row2 = secondsheet.createRow(0); 79 row2.setHeight((short) (20*20)); 80 row2.createCell(0).setCellValue("时间"); 81 row2.createCell(1).setCellValue("级别"); 82 row2.createCell(2).setCellValue("内容"); 83 row2.getCell(2).setCellStyle(cellStyle3); 84 for(int i=0;i<alarmList.size();i++){ 85 DAlarm alarm=alarmList.get(i); 86 row2 = secondsheet.createRow(i+1); 87 row2.setHeight((short) (27*20)); 88 row2.createCell(0).setCellValue(alarm.getTime()); 89 row2.createCell(1).setCellValue(alarm.getLevel()); 90 row2.createCell(2).setCellValue(alarm.getContext()); 91 } 92 return workbook; 93 } 94 }
（二）、设置control中的下载头部，这里和freemarker的导出是一样的。
（三）、利用输入输出流，输出到浏览器
1 package cn.Controllers; 2 3 import java.io.FileOutputStream; 4 import java.io.IOException; 5 import java.io.UnsupportedEncodingException; 6 import java.net.URLEncoder; 7 import java.text.DecimalFormat; 8 import java.util.List; 9 10 import javax.servlet.ServletOutputStream; 11 import javax.servlet.http.HttpServletRequest; 12 import javax.servlet.http.HttpServletResponse; 13 14 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 15 import org.apache.poi.hssf.usermodel.HSSFFont; 16 import org.apache.poi.hssf.usermodel.HSSFRow; 17 import org.apache.poi.hssf.usermodel.HSSFSheet; 18 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 19 import org.apache.poi.ss.usermodel.HorizontalAlignment; 20 import org.apache.poi.ss.usermodel.VerticalAlignment; 21 import org.springframework.beans.factory.annotation.Autowired; 22 import org.springframework.stereotype.Controller; 23 import org.springframework.ui.Model; 24 import org.springframework.web.bind.annotation.RequestMapping; 25 import org.springframework.web.bind.annotation.ResponseBody; 26 27 import cn.dao.DeviceMapper; 28 import cn.entitys.DAlarm; 29 import cn.entitys.DPool; 30 import cn.entitys.Device; 31 import cn.service.ReportService; 32 import cn.service.ThreadService; 33 import cn.tools.GenerateReport; 34 35 @Controller 36 @RequestMapping("/report") 37 public class ReportController { 38 39 @Autowired 40 ReportService reportService; 41 42 @Autowired 43 ThreadService threadService; 44 45 @Autowired 46 DeviceMapper deviceDao; 47 48 @RequestMapping("/exportreport") 49 private void exportReport(Model model,HttpServletRequest request,HttpServletResponse response,int did) throws IOException { 50 51 //获取要导入的信息 52 List<DPool> poolList=reportService.selectPoolByDid(did); 53 List<DAlarm> alarmList=reportService.selectAlarmByDid(did); 54 Device device = deviceDao.selectByPrimaryKey(did); 55 56 57 //生成Excel表 58 HSSFWorkbook workbook = GenerateReport.Generate(poolList, alarmList); 59 60 61 //设置下载头部，通过response实现 62 response.reset(); 63 response.setContentType("application/vnd.ms-excel;charset=utf-8"); 64 try { 65 response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(device.getName()+"-report.xls", "UTF8")); 66 } catch (UnsupportedEncodingException e) { 67 e.printStackTrace(); 68 } 69 ServletOutputStream fileOut = response.getOutputStream(); 70 workbook.write(fileOut); 71 } 72 98 }
这里没有编写代码，后续会跟上的。
三、Excel的解析：
其实这一部分可以分为两部分来讲解 ，首先涉及到的便是文件的上传，毕竟有文件上传了才能进行解析，然后便是利用POI解析上传后的文件获取数据，
至于POI 的操作会在下一篇博客中整理。
