导出Excel又有了新的需求,之前都是直接导出list<T>,现需要导出List<map>,并且需要动态创建表头。如下:
【工具类】
引用jxl包
public class ListMapExportExcelUtil {
/**
* 写excel.
*
* @param fileName 文件名
* @param sheetName sheet名
* @param mapKeyListString map的key,表头们
* @param dataListMap 数据
*/
public static void writeExcel(HttpServletResponse response,String fileName,
String sheetName,
List<String> mapKeyListString, List<Map<String, Object>> dataListMap) throws EngineException {
WritableWorkbook wwb = null;
OutputStream os = null;
try {
os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
WritableSheet ws = wwb.createSheet(sheetName, 0);
int size = mapKeyListString.size();
insertHead(ws, mapKeyListString);
int rownum = 1;
if ((dataListMap != null) && (dataListMap.size() > 0)) {
for (int i = 0; i < dataListMap.size(); i++) {
LogUtil.getAppLogger().info("当前行数为:{}",i);
Map map = (Map) dataListMap.get(i);
for (int j = 0; j < mapKeyListString.size(); j++) {
if(map.get(mapKeyListString.get(j)) != null ){
insertCell(ws, null, rownum, j, map.get(mapKeyListString.get(j)).toString());
}else{
insertCell(ws, null, rownum, j, " ");
}
}
rownum++;
}
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
response.setContentType("application/msexcel");
response.addHeader("Content-disposition", "attachment;" +
"filename=" + fileName + "_" + sdf.format(new Date()
) + ".xls");
wwb.write();
} catch (Exception e) {
throw new Exception(e);
} finally {
try {
if (wwb != null)
wwb.close();
if (os != null)
os.close();
} catch (Exception e) {
throw new Exception(e);
}
}
}
//单元格
private static void insertCell(WritableSheet writableSheet, WritableCellFormat writableCellFormat, int row, int column, String name) throws RowsExceededException, WriteException {
WritableCell cell = new Label(column, row, name);
if (writableCellFormat != null) {
cell.setCellFormat(writableCellFormat);
}
writableSheet.addCell(cell);
}
//表头
private static void insertHead(WritableSheet writableSheet, List<String> titleColumn) throws RowsExceededException, WriteException, EngineException {
WritableFont writableFont = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
WritableCellFormat writableCellFormat = new WritableCellFormat(writableFont);
try {
writableCellFormat.setAlignment(Alignment.CENTRE);
} catch (WriteException e) {
throw new EngineException(CodeEnum.PARAM_FORMAT_ERROR.getCode(),CodeEnum.PARAM_FORMAT_ERROR.getMessage(),e);
}
for (int i = 0; i < titleColumn.size(); i++) {
WritableCell cell = new Label(i, 0, titleColumn.get(i));
cell.setCellFormat(writableCellFormat);
writableSheet.addCell(cell);
}
}
}