Java导出excel
控制层Controller
1 /** 2 * 导出状态 3 */ 4 private boolean excelFlag = true; 5 6 /** 7 * 导出excel 8 * 9 * @param response 10 * @param keyword 关键字 11 * @param type 类型 12 * @param state 状态 13 * @param startTime 开始时间 14 * @param endTime 结束时间 15 */ 16 @RequestMapping(value = "/exportExcel") 17 public void exportOrder(HttpServletResponse response, 18 String keyword, String type, String state, 19 String startTime, String endTime) { 20 SearchVO vo = new SearchVO (); 21 // 查询参数 22 vo.setKeyword(keyword); 23 vo.setState(state); 24 vo.setType(type); 25 vo.setStartTime(startTime); 26 vo.setEndTime(endTime); 27 excelFlag = true; 28 String[] header = {"姓名", "地址", "联系电话", "订单金额", "商品名", "购买日期", "订单状态", "订单类型", "创建时间"}; 29 String[] columsNames = {"customer_name", "address","phone", "order_money", "product_name", "buy_date","order_state", "order_type", "create_time"}; 30 List<Map<String, Object>> dataList = service.search(vo); 31 ExcelUtils.exportExcel("订单列表.xlsx", "sheet", header, columsNames, dataList, response); 32 excelFlag = false; 33 } 34 35 /** 36 * 获取导出状态 37 * 38 * @return 39 */ 40 @RequestMapping("/getExcelFlag") 41 @ResponseBody 42 public String getExportFlag() { 43 return String.valueOf(excelFlag); 44 }
excel工具类
/** * excel工具类 */ public class ExcelUtils { private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class); /** * 导出excel * @param fileName 文件名（*.xsl） * @param sheetName sheet名 * @param headers 头部 * @param columsNames 实体属性名 * @param dataList 数据列表 * @param response */ public static void exportExcel(String fileName, String sheetName, String[] headers, String[] columsNames, List<Map<String, Object>> dataList, HttpServletResponse response) { XSSFWorkbook wb = new XSSFWorkbook(); // 创建头部样式 XSSFCellStyle headerStyle = wb.createCellStyle(); // 创建字体样式 XSSFFont headerFont = wb.createFont(); // 字体加粗：0x2bc headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 设置字体类型：宋体 headerFont.setFontName("宋体"); // 设置字体大小：12 headerFont.setFontHeightInPoints((short) 12); // 为头部样式设置字体样式 headerStyle.setFont(headerFont); // 设置头部居中 headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建body样式 XSSFCellStyle bodyStyle = wb.createCellStyle(); // 设置body居左 bodyStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 创建字体样式 XSSFFont bodyFont = wb.createFont(); // 设置字体类型 bodyFont.setFontName("宋体"); // 设置字体大小 bodyFont.setFontHeightInPoints((short) 10); // 为body样式设置字体样式 bodyStyle.setFont(bodyFont); // 在workbook中添加一个sheet,对应Excel文件中的sheet XSSFSheet sheet = wb.createSheet(sheetName); // 在sheet中添加表头第0行 XSSFRow row = sheet.createRow(0); // 声明列对象 XSSFCell cell = null; // 创建标题行 for (int i = 0; i < headers.length; i++) { cell = row.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(headers[i]); } // 创建excel内容 for (int i = 0; i < dataList.size(); i++) { // 行 row = sheet.createRow(i + 1); // 数据填入列 for (int j = 0; j < columsNames.length; j++) { Map<String, Object> map = dataList.get(i); Object obj = map.get(columsNames[j]); String columsValue = StringUtils.EMPTY; columsValue = obj == null ? "" : obj.toString(); // 列 cell = row.createCell(j); cell.setCellStyle(bodyStyle); cell.setCellValue(columsValue); } } // 必须在单元格设值以后进行 // 设置为根据内容自动调整列宽 for (int k = 0; k < dataList.size(); k++) { sheet.autoSizeColumn(k); } // 处理中文不能自动调整列宽的问题 setSizeColumn(sheet, dataList.size()); setResponseHeader(response, fileName); OutputStream out = null; try { out = response.getOutputStream(); wb.write(out); out.close(); out = null; String str = "导出" + fileName + "成功！"; log.info(str); } catch (IOException e) { String str = "导出" + fileName + "失败！"; log.error(str); e.printStackTrace(); } finally { if (out != null) { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } } }
jsp
<button type="button" class="export-excel" id="export" onclick="exportExcelAll();">导出Excel</button>
js
//导出excel function exportExcelAll() { let startTime = $("#start").val(); let endTime = $("#end").val(); $(document).an_dialog({ massage: { type: '提示', content: "导出订单日期：" + startTime + "至" + endTime, }, buttons: [{ text: '确认', cls: "success", handler: function (e) { var myloading = andy.loading('top', '请稍等，正在导出数据...', function () { }); submitForm(); //location.href = contextPath + 'order/export/exportExcel?startTime=' + startTime + '&endTime=' var excelTimer = setInterval(function () { $.get(contextPath + "/order/export/getExcelFlag", function (r) { if (r == 'false') { myloading.close(); clearInterval(excelTimer); } }); }, 1000); e.data.an_dialog("close"); } }, { text: '取消', handler: function (e) { e.data.an_dialog("close"); } }] }); } // form表单提交 function submitForm(){ var orderState = $("#orderState ").val(); var orderType = $("#orderType ").val(); var keySearch = encodeURI(encodeURI(encodeURI($("#keySearch ").val().trim()))); var startTime= $("#start").val(); var endTime= $("#end").val(); // 构建form表单 var form = $("<form>"); form.attr('style', 'display:none'); form.attr('target', ''); form.attr('method', 'post'); form.attr('action', contextPath + '/order/export/exportExcel'); var input1 = $('<input>'); input1.attr('type', 'hidden'); input1.attr('name', 'keyword'); input1.attr('value', keySearch); /* JSON.stringify($.serializeObject($('#searchForm'))) */ var input2 = $('<input>'); input2.attr('type', 'hidden'); input2.attr('name', 'state'); input2.attr('value', orderState); var input3 = $('<input>'); input3.attr('type', 'hidden'); input3.attr('name', 'type'); input3.attr('value', orderType); var input4 = $('<input>'); input4.attr('type', 'hidden'); input4.attr('name', 'startTime'); input4.attr('value', startTime); var input5 = $('<input>'); input5.attr('type', 'hidden'); input5.attr('name', 'endTime'); input5.attr('value', endTime); $('body').append(form); form.append(input1); form.append(input2); form.append(input3); form.append(input4); form.append(input5); form.submit(); form.remove(); }
这里出现一个小问题，location.href这里get请求，报400错误。参数类型和参数名都能对上，去掉几个参数之后也能进请求。单独用url请求时，发现后面的参数被截取掉了。所以这里改用form表单提交方式实现。
