Excel导出SpringBoot项目中表格数据
最近写业务代码，涉及到这个，前阵子写了篇博客介绍了用JS以Excel形式导出后台返回给前台的数据，这种方法只能是导出一个页面的数据，并不能按分页的形式导出，所以今天就改写一下，实现将接口返回的所有数据进行全部导出。
前台
jsp/html页面代码
<div class="p-sm border no-top-border"> <button type="submit" class="btn btn-warning start export-btn lbox-btn"> <i class="glyphicon glyphicon-export" aria-hidden="true"></i> <span>导出Excel</span> </button> <div class="row"> <div class="col-md-12"> <table class="table table-bordered text-center table_ellipsis" id="_table" style="width: 100%"> </table> </div> </div> </div>
js
js是用来向后请求Controller方法，并传递相关的字段参数。
$(".export-btn").on("click", function () { var url = $("#path").val() + "/rest/qday/exportExcel?orgId=" + orgId + "&startDate=" + startTime.replace(/-/g, "") + "&endDate=" + endTime.replace(/-/g, "") + "&leixing=0"; window.location.href = url; });
、Controller
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET) public void exportExcel(String startDate, String endDate, String orgId, String leixing, HttpServletRequest request, HttpServletResponse response) { try { //当前页数 int pageNum = 1; //每页显示条数 int pageSize = 10000; String orgIdStr = ""; if (orgId.contains("--")) { orgIdStr = orgId.substring(0, 6); } else { orgIdStr = orgId; } //通过接口来取得数据 JSONObject json =XXXService.receiveData(startDate, endDate, orgIdStr, interfaceUrl + JSONObject.parseObject(jqrqlUrl).getString("1"), "&type=" + leixing + "&pageNum=" + pageNum + "&pageSize=" + pageSize); if (json != null && json.size() > 0) { //excel文件名 String[] title = new String[]{"编号", "名称", "单位", "状态"}; String name = null; //根据不同的类型来进行excel文件的命名 switch (leixing) { case "0": name = "第一个"; break; case "100001": name = "第二个"; break; case "100002": name = "第三个"; break; case "100003": name = "第四个"; break; default: break; } String fileName = URLEncoder.encode(name + startDate + "至" + endDate + name + ".xls", "UTF-8"); //sheet名 String sheetName = name; //取json字符穿中data对象，里面有一个list数组和total对象 JSONObject object = (JSONObject) json.get("data"); //取出List数组 JSONArray earlyList = (JSONArray) object.get("list"); //字符串二维数据用来保存表格数据 String[][] content = new String[earlyList.size()][]; //循环遍历 for (int i = 0; i < earlyList.size(); i++) { content[i] = new String[title.length]; JSONObject pojo = (JSONObject) earlyList.get(i); //list数据类型为：[{"name":"张","password":"jian"},{"name":"张","password":"chao"}],所以这里取key值 if (pojo.get("name") != null) { content[i][0] = (String) pojo.get("name"); } if (pojo.get("password") != null) { content[i][1] = (String) pojo.get("password"); } //如果是int类型数据将其转换成String if (pojo.get("XXX") != null) { content[i][2] = pojo.getBigDecimal("XXX").toString(); } if (pojo.get("xxx") != null) { content[i][3] = pojo.getBigDecimal("xxx").toString(); } /* if (pojo.gettWarnStatus() != null) { // 状态 0关闭；1开启； String warnStatus = ""; switch (pojo.gettWarnStatus()) { case 0: warnStatus = "已关闭"; break; case 1: warnStatus = "已开启"; break; } content[i][4] = warnStatus; }*/ } HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null); ExcelUtil.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } } catch (Exception e) { e.printStackTrace(); logger.error(e.getMessage(), e); } }
下面是重头戏啦。
ExcelUtil
import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import javax.servlet.http.HttpServletResponse; import javax.swing.text.Style; import java.beans.IntrospectionException; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.InvocationTargetException; import java.net.URLEncoder; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; public class ExcelUtil { public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) { // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 if (wb == null) { wb = new HSSFWorkbook(); } // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheetName); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 HSSFRow row = sheet.createRow(0); // 第四步，创建单元格，并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式 sheet.setColumnWidth(0, 20 * 256); sheet.setColumnWidth(1, 30 * 256); sheet.setColumnWidth(2, 50 * 256); sheet.setColumnWidth(3, 30 * 256); sheet.setColumnWidth(4, 10 * 256); sheet.setColumnWidth(5, 20 * 256); sheet.setColumnWidth(6, 20 * 256); //声明列对象 HSSFCell cell = null; //创建标题 for (int i = 0; i < title.length; i++) { cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } //创建内容 for (int i = 0; i < values.length; i++) { row = sheet.createRow(i + 1); for (int j = 0; j < values[i].length; j++) { //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values[i][j]); /*if (values[i][j] != null) { sheet.setColumnWidth(i, values[i][j].toString().length() * 512); }*/ } } return wb; } //发送响应流方法 public static void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(), "gbk"); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } response.setContentType("application/octet-stream;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } /** * 生成表格数据 */ public void outputByDataSXSSF(HttpServletResponse response, List<List<String>> matrix, String title, String titles) throws UnsupportedEncodingException { Workbook workbook = null; try { // 生成workBook，导出Excel对象 //ExcelSheetBean excelSheetBean = new ExcelSheetBean(matrix, title); // List<ExcelSheetBean> excelSheetBeans = new ArrayList<>(); // excelSheetBeans.add(excelSheetBean); workbook = generateWorkbooks(matrix, title); downloadExport(workbook, response, titles); } catch (IllegalArgumentException | IllegalAccessException | InvocationTargetException | ClassNotFoundException | IntrospectionException | ParseException e) { e.printStackTrace(); } } private Workbook generateWorkbooks(List<List<String>> matrix, String title) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException, UnsupportedEncodingException { Workbook workBook = new SXSSFWorkbook(); Integer maxColumnIndex = 0; if (matrix.size() > 0) { maxColumnIndex = matrix.get(0).size() - 1; } Sheet sheet = workBook.createSheet(); /*if (title != null) { workBook.setSheetName(i, title); }*/ // ExcelUtil.createFontssf(workBook); // 标题行 Row row0 = sheet.createRow(0); Cell cell = row0.createCell(0); CellRangeAddress range0 = new CellRangeAddress(0, 0, 0, maxColumnIndex); row0.setHeightInPoints(100); sheet.addMergedRegion(range0); cell.setCellValue(title); // 标题样式 CellStyle style = workBook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // 指定单元格居中对齐 style.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格垂直居中 //style.setAlignment(XSSFCellStyle.ALIGN_CENTER); Font font = workBook.createFont(); font.setFontName("黑体"); font.setFontHeight((short) 400); style.setFont(font); style.setWrapText(true);//设置自动换行 cell.setCellStyle(style); // 标题行样式 CellStyle stylehang = workBook.createCellStyle(); Font fonts = workBook.createFont(); fonts.setFontName("宋体"); fonts.setFontHeight((short) 200); stylehang.setFont(fonts); CellStyle Headhang = workBook.createCellStyle(); Font Headfonts = workBook.createFont(); Headfonts.setFontName("宋体"); Headfonts.setFontHeight((short) 200); Headhang.setFont(fonts); Headhang.setAlignment(HorizontalAlignment.CENTER); //内容行 if (matrix != null && matrix.size() > 0) { Row currRows = sheet.createRow(1); List<String> rowDatas = matrix.get(0); if (rowDatas != null && rowDatas.size() > 0) { for (int k = 0; k < rowDatas.size(); k++) { Cell currCell = currRows.createCell(k); currCell.setCellValue(rowDatas.get(k)); currCell.setCellStyle(Headhang); } } for (int j = 1; j < matrix.size(); j++) { Row currRow = sheet.createRow(1 + j); List<String> rowData = matrix.get(j); rowData.removeAll(Collections.singleton(null)); if (rowData != null && rowData.size() > 0) { for (int k = 0; k < rowData.size(); k++) { Cell currCell = currRow.createCell(k); currCell.setCellValue(rowData.get(k)); currCell.setCellStyle(stylehang); } } } } int maxColumn = maxColumnIndex; for (int j = 0; j <= maxColumn; j++) { // sheet.autoSizeColumn(j); sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 19 / 5); } return workBook; } /*// SXSSFWorkbook样式 public static void createFontssf(SXSSFWorkbook workbook) { // 表头 CellStyle fontStyle = workbook.createCellStyle(); Font font1 = workbook.createFont(); font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font1.setFontName("黑体"); font1.setFontHeightInPoints((short) 14);// 设置字体大小 fontStyle.setFont(font1); fontStyle.setAlignment(HorizontalAlignment.CENTER); // 居中 // 表头2 CellStyle fontContentStyle = workbook.createCellStyle(); Font font3 = workbook.createFont(); fontContentStyle.setFont(font3); fontContentStyle.setAlignment(HorizontalAlignment.CENTER); // 居中 fontContentStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 内容 CellStyle fontStyle2 = workbook.createCellStyle(); Font font2 = workbook.createFont(); font2.setFontName("宋体"); font2.setFontHeightInPoints((short) 10);// 设置字体大小 fontStyle2.setFont(font2); fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框 fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框 fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框 fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框 fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中 fontStyle2.setWrapText(true);//设置自动换行 }*/ public void downloadExport(Object obj, HttpServletResponse response, String title) { response.reset(); try { title = URLEncoder.encode(title, "UTF-8"); } catch (Exception e) { e.getMessage(); } // 指定下载的文件名 response.setHeader("Content-Disposition", "attachment;filename=" + title + ".xlsx"); //response.setContentType("multipart/form-data"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Pragma", "no-cache"); response.setHeader("Cache-Control", "no-cache"); response.setDateHeader("Expires", 0); OutputStream output; try { output = response.getOutputStream(); SXSSFWorkbook workbook = (SXSSFWorkbook) obj; workbook.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } } }
我也是进行了一个参考，如果后面遇到了可以更好的拿来用，先记录到这里了，后面还有比较好用的小插件我还会继续进行总结，可以更方便自己的开发，如有不对的地方还请多多指正。
