HTML:
<a href="#" class="easyui-linkbutton" onclick="exportExcel()" data-options="iconCls:'icon-excel',plain:true">导出报表</a>
JS:
1 //导出报表 2 function exportExcel() { 3 window.top.$.messager.confirm("提示", "确认导出吗? ", function(r) { 4 if (r) { 5 window.open("/xxexport/entranceToExport"); 6 } 7 }); 8 }
JAVA:
1 package com.missy.controller; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileOutputStream; 6 import java.io.OutputStream; 7 import java.net.URLEncoder; 8 9 import org.apache.commons.io.FileUtils; 10 import org.apache.poi.ss.usermodel.Cell; 11 import org.apache.poi.ss.usermodel.Font; 12 import org.apache.poi.ss.usermodel.HorizontalAlignment; 13 import org.apache.poi.ss.usermodel.Row; 14 import org.apache.poi.ss.usermodel.Sheet; 15 import org.apache.poi.ss.usermodel.VerticalAlignment; 16 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 17 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 18 import org.springframework.beans.factory.annotation.Autowired; 19 import org.springframework.http.HttpHeaders; 20 import org.springframework.http.HttpStatus; 21 import org.springframework.http.MediaType; 22 import org.springframework.http.ResponseEntity; 23 import org.springframework.web.bind.annotation.RequestMapping; 24 import org.springframework.web.bind.annotation.RestController; 25 26 import com.github.pagehelper.Page; 27 import com.github.pagehelper.util.StringUtil; 28 import com.gomai.sinomach.oms.mcagency.data.XxData; 29 import com.gomai.sinomach.oms.mcagency.service.XxService; 30 import com.siims.framework.utils.ActionUtil; 31 import com.siims.sinomach.sys.org.data.SysOrgData; 32 import com.siims.sinomach.sys.org.service.SysOrgService; 33 34 import net.sf.json.JSONObject; 35 36 /** 37 * XXexcel导出 38 * 39 * @author ywy 40 * @date 2020-09-02 41 */ 42 @RestController 43 @RequestMapping("/xxexport") 44 public class XxExportController { 45 46 @Autowired 47 private XxService XxService; 48 49 /** 50 * 导表入口 51 * 52 * @param entity 查询条件 53 * @author ywy 54 * @date 2020-09-02 55 * @return 56 */ 57 @SuppressWarnings("resource") 58 @RequestMapping(value = "/entranceToExport") 59 public ResponseEntity<?> entranceToExport(XxData entity) { 60 JSONObject json = new JSONObject(); 61 json.put("success", false); 62 json.put("msg", "导出失败!"); 63 OutputStream out = null; 64 try { 65 FileInputStream tps = new FileInputStream( 66 new File(new StringBuffer().append(ActionUtil.getRequest().getServletContext().getRealPath("/")) 67 .append("fileTemplate/xx导出模板.xlsx").toString()));// 拿到模板文件 68 File file = new File(new StringBuffer().append(System.getProperty("java.io.tmpdir")).append(File.separator) 69 .append("xx统计表.xlsx").toString());// 提示下载文件 70 out = new FileOutputStream(file); 71 XSSFWorkbook workbook = new XSSFWorkbook();// 新建一个Excel的工作空间 72 workbook = new XSSFWorkbook(tps);// 把模板复制到新建的Excel 73 setSheetOne(workbook, entity);// 填充数据 74 workbook.write(out);// 输出Excel内容,生成Excel文件 75 out.flush(); 76 out.close(); 77 78 HttpHeaders headers = new HttpHeaders(); 79 headers.setContentDispositionFormData("attachment", URLEncoder.encode(file.getName(), "UTF-8")); 80 headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); 81 return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK); 82 } catch (Exception e) { 83 json.put("success", false); 84 json.put("msg", "导出失败!"); 85 e.printStackTrace(); 86 } 87 return null; 88 89 } 90 91 /** 92 * 处理数据 93 * 94 * @param workbook 95 * @param entity 查询条件 96 * @return XSSFWorkbook 97 * @author ywy 98 * @date 2020-09-02 99 */ 100 private XSSFWorkbook setSheetOne(XSSFWorkbook workbook, XxData entity) throws Exception { 101 Sheet sheet = workbook.getSheetAt(0); 102 Page<XxData> list = XxService.getXxList(entity); 103 if (list.size() > 0) { 104 XSSFCellStyle cellStyle = workbook.createCellStyle();// 单元格样式 105 cellStyle.setWrapText(true);// 自动换行 106 cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中 107 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 108 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); 109 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); 110 cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); 111 cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); 112 Font font = workbook.createFont(); 113 font.setFontName("仿宋_GB2312"); 114 font.setFontHeightInPoints((short) 12); 115 cellStyle.setFont(font); 116 int rowNum = 3;// 行索引 117 for (int i = 0; i < list.size(); i++) { 118 if(list.get(i) != null) { 119 handelRowData(sheet.createRow(rowNum++), list.get(i), cellStyle, (i+1)); 120 } 121 } 122 } 123 return workbook; 124 } 125 126 /** 127 * 处理行数据 128 * 129 * @param tempRow 行对象 130 * @param XxData 要处理的信息 131 * @param cellStyle 单元格样式 132 * @param dataNum 数据的序号 133 * @return Row 134 * @throws Exception 135 * @author ywy 136 * @date 2020-09-02 137 */ 138 private Row handelRowData(Row tempRow, XxData XxData, XSSFCellStyle cellStyle, int dataNum) 139 throws Exception { 140 int cellIndex = 0;// 单元格索引 141 final String EMPTY = "无";// 空字符 142 // 给单元格插入值 143 handleCell(tempRow, cellIndex++, cellStyle, dataNum);// 序号 144 handleCell(tempRow, cellIndex++, cellStyle, StringUtil.isEmpty(XxData.getUserName) ? EMPTY : XxData.getUserName());// 姓名 145 handleCell(tempRow, cellIndex++, cellStyle, StringUtil.isEmpty(XxData.getPhoneNum()) ? EMPTY : XxData.getPhoneNum());// 电话 146 return tempRow; 147 } 148 149 150 /** 151 * 给单元格插入值 152 * @param row 行对象 153 * @param cellIndex 单元格索引 154 * @param cellStyle 单元格样式 155 * @param cellValue 单元格的值 Integer类型 156 * @return Cell 157 * @throws Exception 158 * @author ywy 159 * @date 2020-09-02 160 */ 161 private Cell handleCell(Row row, int cellIndex, XSSFCellStyle cellStyle, Integer cellValue) throws Exception { 162 Cell cell = row.createCell(cellIndex); 163 cell.setCellStyle(cellStyle); 164 cell.setCellValue(cellValue); 165 return cell; 166 } 167 168 /** 169 * 给单元格插入值 170 * @param row 行对象 171 * @param cellIndex 单元格索引 172 * @param cellStyle 单元格样式 173 * @param cellValue 单元格的值 String类型 174 * @return Cell 175 * @throws Exception 176 * @author ywy 177 * @date 2020-09-02 178 */ 179 private Cell handleCell(Row row, int cellIndex, XSSFCellStyle cellStyle, String cellValue) throws Exception { 180 Cell cell = row.createCell(cellIndex); 181 cell.setCellStyle(cellStyle); 182 cell.setCellValue(cellValue); 183 return cell; 184 } 185 186 }
导出模板文件存放: