项目总结23：POI生成Excel文件并浏览器导出
具体的逻辑可以参考Controller层的注释
代码1-前端html
代码3-POM文件应用POI
org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17
代码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 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 > lineDataList){ //i代表行，j代表列 HSSFCellStyle style = getNormalCellTitleStyle(); for(int i=0; i< lineDataList.size(); i++){ List lineDatas = lineDataList.get(i); HSSFRow row = sheet.createRow(firstRowIndex + i);//行 for(int j=0; j
代码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{ /** * @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 headTitleList = new ArrayList (); for(int i=0;i<10; i++){ headTitleList.add("第"+(i+1)+"列标题"); } List
> dataList = new ArrayList
>(); for(int i=0;i<5; i++){ List 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); }}
效果展示
