前台
function exportExcel() {
var month = mini.get("month").getValue();
if (month === "") {
mini.get("month").setIsValid(false);
ciic.miniAlert("期间不能为空!", "提示", "", "fail");
return;
}
var filter = { month: month};
var fields = new Array();
var columns = grid.columns;
for (var i = 0; i < columns.length; i++) {
var column = columns[i];
if (column.field == 'ID' || column.name == 'operation' || column.type == "indexcolumn" || column.type == "checkcolumn" || column.field == null || column.visible == false || column.enabled == false) continue;
var field = column.displayField != null && column.displayField != "" ? column.displayField : column.field;
fields.push(field);
}
exportFile(contextRoot + "/mvc/eport/export", {
fileName: "测试测试测试.xlsx",
fieldStr: fields.toString(),
filterStr: JSON.stringify(filter).toString(),
});
}
function exportFile(url, fields) {
var submitfrm = document.createElement("form");
submitfrm.action = url;
submitfrm.style = "display:none";
submitfrm.method = "post";
submitfrm.target = "_self";
document.body.appendChild(submitfrm);
if (fields) {
for (var p in fields) {
var input = mini.append(submitfrm, "<input type='hidden' name='" + p + "'>");
var v = fields[p];
if (typeof v != "string") v = mini.encode(v);
input.value = v;
}
}
ciic.mask({
el: document.body,
cls: 'mini-mask-loading',
html: '下载中...'
});
submitfrm.submit();
setTimeout(function () {
submitfrm.parentNode.removeChild(submitfrm);
ciic.unmask(document.body);
}, 1000);
}
controller
@RequestMapping({"/export"})
public void export(String fileName, String fieldStr, String filterStr, HttpServletResponse res) throws BizException {
try {
URL xmlPath = this.getClass().getClassLoader().getResource("");
String rootPath = xmlPath.getPath();
String filePath = rootPath + "/templates/exportTemplate.xlsx";
Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
ParmMap filterMap = JSONUtils.fromJson(filterStr, ParmMap.class);
workbook = reportService.export(fieldStr, filterMap, workbook);
ExcelExportUtil.writeFile(workbook, fileName, res);
} catch (Exception e) {
throw new BizException(e, "文件下载失败!", new String[0]);
}
}
service
public Workbook export(String fieldStr, ParmMap filterMap, Workbook workbook) {
if (workbook == null) {
workbook = new XSSFWorkbook();
}
XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
short defaultHeight = ((short) (300 * 2));
List<Map> mapList = queryList(filterMap);
int dataStartRowNum = sheet.getLastRowNum() + 1;
String[] fieldStr_arr = fieldStr.split(",");
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setFont(font);
for (int i = 0; i < mapList.size(); i++) {
Map map = mapList.get(i);
sheet.createRow(dataStartRowNum + i);
Row row = sheet.getRow(dataStartRowNum + i);
row.setHeight(defaultHeight);
Cell cell_index = row.createCell(0);
cell_index.setCellValue(i + 1);
cell_index.setCellStyle(cellStyle);
for (int j = 0; j < fieldStr_arr.length; j++) {
Cell cell = row.createCell(j + 1);
String field = fieldStr_arr[j];
String value = map.get(field) == null ? "" : String.valueOf(map.get(field));
cell.setCellValue(value);
cell.setCellStyle(cellStyle);
}
}
CellRangeAddress region2 = new CellRangeAddress(dataStartRowNum + list.size() - 3, dataStartRowNum + list.size() - 3, (short) 1, (short) 3);
CellRangeAddress region3 = new CellRangeAddress(dataStartRowNum + list.size() - 2, dataStartRowNum + list.size() - 2, (short) 1, (short) 3);
CellRangeAddress region4 = new CellRangeAddress(dataStartRowNum + list.size() - 1, dataStartRowNum + list.size() - 1, (short) 1, (short) 3);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
return workbook;
}