项目总结23：POI生成Excel文件并浏览器导出
具体的逻辑可以参考Controller层的注释
代码1-前端html
<button onclick="downLoad()" type="button" class="btn btn-default">导出Excel</button> <script> function downLoad(){ url = basePath + "/download/data/" + 1 window.location.href =url; } </script>
代码3-POM文件应用POI
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
代码3-封装成完整的ExcelUtil
package com.hs.web.controller.saichang.member; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.web.bind.annotation.GetMapping; import javax.servlet.http.HttpServletResponse; import java.io.FileOutputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; public class ExcelObject { private HSSFWorkbook workbook; private HSSFSheet sheet; public ExcelObject() { this.workbook = new HSSFWorkbook(); this. sheet = this.workbook.createSheet("sheet1"); } public ExcelObject(String sheetName) { this.workbook = new HSSFWorkbook(); this. sheet = this.workbook.createSheet(sheetName); sheet.setDefaultRowHeight((short) (2 * 256)); //设置默认行高，表示2个字符的高度 sheet.setDefaultColumnWidth(17); //设置默认列宽，实际上回多出2个字符，不知道为什么 } /** * @description:设置头标题格式 * @param:[workbook, sheet] * @return:org.apache.poi.hssf.usermodel.HSSFCellStyle * @date:2019/5/11 * @author:tangyj * @remark: * */ private HSSFCellStyle getHeadTitleStyle(){ //设置为居中加粗 HSSFCellStyle style = this.workbook.createCellStyle(); HSSFFont font = this.workbook.createFont(); font.setFontHeightInPoints((short)24); font.setBold(true); style.setAlignment( HorizontalAlignment.CENTER); style.setFont(font); style.setWrapText(true);//设置自动换行 return style; } /** * @description:设置行标题格式 * @param:[workbook, sheet] * @return:org.apache.poi.hssf.usermodel.HSSFCellStyle * @date:2019/5/11 * @author:tangyj * @remark: * */ private HSSFCellStyle getRowTitleStyle(){ //设置为居中加粗 HSSFCellStyle style = this.workbook.createCellStyle(); HSSFFont font = this.workbook.createFont(); font.setFontHeightInPoints((short)12); font.setBold(true); style.setAlignment( HorizontalAlignment.CENTER); style.setFont(font); style.setWrapText(true);//设置自动换行 return style; } /** * @description:设置普通单元格格式 * @param:[workbook, sheet] * @return:org.apache.poi.hssf.usermodel.HSSFCellStyle * @date:2019/5/11 * @author:tangyj * @remark: * */ private HSSFCellStyle getNormalCellTitleStyle(){ //设置为居中加粗 HSSFCellStyle style = this.workbook.createCellStyle(); HSSFFont font = this.workbook.createFont(); font.setFontHeightInPoints((short)12); style.setAlignment( HorizontalAlignment.CENTER); style.setFont(font); style.setWrapText(true);//设置自动换行 return style; } /** * @description:1-写入头标题 * @param:[workbook, sheet, column, headTitle] * @return:void * @date:2019/5/11 * @author:tangyj * @remark: * */ public void createHeadTile(int column, String headTitle){ //创建行(默认第一行) HSSFRow row = sheet.createRow(0); //合并单元格 int lastColIndex = column > 0 ? (column-1):0; sheet.addMergedRegion(new CellRangeAddress(0,0,0,lastColIndex)); //单元格居中加粗格式 HSSFCellStyle style = getHeadTitleStyle(); //写入标题 HSSFCell cell = row.createCell(0);//获取当前行的第一列 cell.setCellValue(headTitle);//写入数据 cell.setCellStyle(style);//设置单元格格式 } /** * @description:2-写入行标题 * @param:[workbook, sheet, rowTitleList, firstRowIndex] * @return:void * @date:2019/5/11 * @author:tangyj * @remark: * */ public void createRowTitle(List<String> rowTitleList,int firstRowIndex){ //创建行 HSSFRow row = sheet.createRow(firstRowIndex); //设置为居中加粗 HSSFCellStyle style = getRowTitleStyle(); HSSFCell cell; for(int i=0; i< rowTitleList.size(); i++){ cell = row.createCell(i);//获取当前列 cell.setCellValue(rowTitleList.get(i));//写入数据 cell.setCellStyle(style);//设置单元格格式 } } /** * @description:3-写入具体的单元格数据 * @param:[firstRowIndex, lineDataList] * @return:void * @date:2019/5/11 * @author:tangyj * @remark: * */ public void createDataByRow(int firstRowIndex,List<List<String>> lineDataList){ //i代表行，j代表列 HSSFCellStyle style = getNormalCellTitleStyle(); for(int i=0; i< lineDataList.size(); i++){ List<String> lineDatas = lineDataList.get(i); HSSFRow row = sheet.createRow(firstRowIndex + i);//行 for(int j=0; j<lineDatas.size(); j ++){ HSSFCell cell = row.createCell(j);//列 cell.setCellValue(lineDatas.get(j));//写入数据 cell.setCellStyle(style);//设置单元格格式 } } } /** * @description:4-生成excel文件 * @param:[filename, workbook] * @return:void * @date:2019/5/11 * @author:tangyj * @remark: * */ public void buildExcelFile(String filename) throws Exception{ FileOutputStream fos = new FileOutputStream(filename); workbook.write(fos); fos.flush(); fos.close(); } /** * @description:5-浏览器下载excel * @param:[filename, workbook, response] * @return:void * @date:2019/5/11 * @author:tangyj * @remark: * */ public void buildExcelDocument(String filename,HttpServletResponse response) throws Exception{ response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "utf-8")); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } }
代码4-controller层
package com.hs.web.controller.saichang.member; import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import com.hs.dao.exception.ServerSqlErrorException; import com.hs.web.model.ResponseMessageEnum; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.*; import java.util.*; @Controller @RequestMapping(value="/memberschool") public class MemberSchoolController extends BaseWebController<MemberSchool> { /** * @description:导出数据 * @param:[schoolId, response] * @return:void * @date:2019/5/11 * @author:tangyj * @remark: * */ @RequestMapping(value = "/download/data/{schoolId}") public void download( @PathVariable(value="schoolId",required = true)Long schoolId, HttpServletResponse response) throws Exception{ //临时生成测试数据 String fileName = "导出excel例子.xls"; String headTitle = "这是头标题"; int colunmNum = 10; List<String> headTitleList = new ArrayList<String>(); for(int i=0;i<10; i++){ headTitleList.add("第"+(i+1)+"列标题"); } List<List<String>> dataList = new ArrayList<List<String>>(); for(int i=0;i<5; i++){ List<String> datas = new ArrayList<>(); for(int j=0;j<10; j++){ datas.add("第"+(i+1)+"行第"+(j+1)+"列"); } dataList.add(datas); } //1-创建一个HSSFWorkbook ExcelObject excel = new ExcelObject("实验数据"); //2-写入头标题 excel.createHeadTile(colunmNum,headTitle);//头标默认写在第一行 //3-写入行标题 excel.createRowTitle(headTitleList,1); //4-写入具体数据 excel.createDataByRow(2,dataList);//因为没有行标题，所以从第二行开始 //5-生成excel文件 excel.buildExcelFile(fileName); //6-浏览器下载excel excel.buildExcelDocument(fileName,response); } }
效果展示
