直接上代码导出数据到excel表
API:http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/FillPatternType.html
/**
* @description 数据导出至Excel
* @date 2019-06-16 16:35:22
* @param sheetName sheet名
* @param map 数据集
* @param headers 标题数组
* @param exportExcelName 导出文件名
* */
public static void export2Exc(String sheetName, List<HashMap<String, Object>> map, String[] headers,
String exportExcelName, HttpServletResponse aResponse) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(20);
// 生成表格中非标题栏的样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.WHITE.index);//背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成表格中非标题栏的字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBold(true);
// 把字体应用到当前的样式
style.setFont(font);
// 设置表格标题栏的样式
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置标题栏字体
XSSFFont titleFont = workbook.createFont();
titleFont.setColor(HSSFColor.WHITE.index);
titleFont.setFontHeightInPoints((short) 12);
titleFont.setBold(true);
// 把字体应用到当前的样式
titleStyle.setFont(titleFont);
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(titleStyle);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//填数据
int index = 0;
Iterator<HashMap<String, Object>> it = map.iterator();
while (it.hasNext()){
index++;
row = sheet.createRow(index);
Map<String, Object> data = it.next();
int i = 1;
for(String key : data.keySet()){
XSSFCell cell = row.createCell(0);
cell.setCellValue(index);
cell.setCellStyle(style);
cell = row.createCell(i);//数据添加
cell.setCellStyle(style);
Object s = data.get(key);
String ss = "";
if (ComnUtil.isEmpty(s)){
ss = "";
}else {
ss = s + "";
}
XSSFRichTextString text = new XSSFRichTextString(ss);
cell.setCellValue(text);
i++;
}
}
//写文件/响应
OutputStream out = null;
try {
// setResponseHeader(aResponse,exportExcelName);
// OutputStream tmpOutputStream = aResponse.getOutputStream();
// workbook.write(tmpOutputStream);
// tmpOutputStream.flush();
// tmpOutputStream.close();
String tmpPath = "F:\\" + exportExcelName + ".xlsx";
out = new FileOutputStream(tmpPath);
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}finally{
if(workbook != null){
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(out != null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void setResponseHeader(HttpServletResponse aResponse, String aFileName){
try {
try {
aFileName = new String(aFileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
aResponse.setContentType("application/octet-stream;charset=ISO8859-1");
aResponse.setHeader("Content-Disposition", "attachment;filename=" + aFileName);
aResponse.addHeader("Pargam", "no-cache");
aResponse.addHeader("Cache-Control", "no-cache");
} catch (Exception e) {
e.printStackTrace();
}
}
}
普通的从前端获得参数集合 carRecList
@Override
public void export(Dto reqDto, HttpServletResponse response) {
List<HashMap<String, Object>> carRecList = (List<HashMap<String, Object>>) reqDto.get("carRecList");
if (ComnUtil.isEmpty(carRecList)){//校验空值
throw new ComnException(ComnStatusCode.ERROR_PARAM, "carRecMaps.has.been.disabled");
}
String [] headList = {"序号","车牌号","所属人","联系方式","所属企业","进入时间","闸机号","车牌照片","驶离时间","闸机号","车牌照片","逗留时长"};
ExportUtil.export2Exc("carRecord",carRecList,headList,"carRecord",response);
}
前端传参样式:
结果展示: