【通用】报表excel导出工具
导包----->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
工具类实现
/**
* 【通用】报表excel导出工具
* @param dataList 查询结果集
* @param head 其中的key:对应[查询结果集]中的key,value:对应字段描述作为excel的表头
* @return
*/
public HSSFWorkbook exportExcelExt(List<Map> dataList, Map<String, String> head) {
// 第一步,创建一个workbook,对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet hssfSheet = workbook.createSheet("sheet1");
// 第三步,在sheet中添加表头第0行
HSSFRow row = hssfSheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
// 居中样式
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 一、设置背景色:
hssfCellStyle.setFillForegroundColor((short) 31);// 设置背景色
hssfCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 二、设置边框:
hssfCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
hssfCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
hssfCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
hssfCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
HSSFFont fontStyle = workbook.createFont();
fontStyle.setFontName("宋体");
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// fontStyle.setFontHeight((short)15);
hssfCellStyle.setFont(fontStyle);
HSSFCellStyle hssfCellStyleContent = workbook.createCellStyle();
// 居中样式
hssfCellStyleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 一、设置背景色:
// hssfCellStyleContent.setFillForegroundColor((short) 31);// 设置背景色
hssfCellStyleContent.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 二、设置边框:
hssfCellStyleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
hssfCellStyleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
hssfCellStyleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
hssfCellStyleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
HSSFCell hssfCell = null;
// 解析列名和表头
int size = head.size();
String[] columns = new String[size];// 列名
String[] headNames = new String[size];// 表头
int x = 0;
for (Entry<String, String> e : head.entrySet()) {
columns[x] = e.getKey();
headNames[x] = e.getValue();
x++;
}
// 设置表头
for (int i = 0; i < headNames.length; i++) {
hssfCell = row.createCell(i);// 列索引从0开始
hssfCell.setCellStyle(hssfCellStyle);
String name = headNames[i];
hssfCell.setCellValue(name);// 列名1
hssfCell.setCellStyle(hssfCellStyle);// 列居中显示
// hssfSheet.autoSizeColumn(i);
hssfSheet.setColumnWidth(i, 3000);//设置默认宽度
}
// 填充数据
for (int i = 0; i < dataList.size(); i++) {
row = hssfSheet.createRow(i + 1);
Map map = dataList.get(i);
for (int y = 0; y < columns.length; x++) {// 此行需要记录对应字段
String key = columns[y];
String value = map.get(key) + "";
HSSFCell cell = row.createCell(y);
cell.setCellStyle(hssfCellStyleContent);
cell.setCellValue(value);
}
}
return workbook;
}
Controller
@RequestMapping("/export")
@Controller
public class exportController{
@Resource
private ExportService service;
/**
* excel报表导出
* @param request
* @param response
*/
@RequestMapping("/exportExcel.xls")
public void exportExcel(HttpServletRequest request,HttpServletResponse response){
Map<String,Object> paramMap = getParameterMap(request);
response.setCharacterEncoding("utf-8");
ServletOutputStream out = null;
try {
out = response.getOutputStream();
//替换参数
List<Map<String,Object>> listData = new ArrayList<Map<String, Object>>();
listData = service.list(paramMap);//需要导出的业务数据
Map<String,String> head = new HashMap<String,String>();
head.put("id","序号");
head.put("name","名字");
head.put("love","爱好");
HSSFWorkbook workbook = ExportExcelUtils.exportExcel(listData,head);
workbook.write(out);
out.flush();
} catch (Exception e) {
logger.error("查询数据异常:" + e.getMessage());
}finally{
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
前端
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
</head>
<body>
名字:<input type="text" id="name">
序号:<input type="text" id="id"></br>
<button onclick="export">导出</button>
</body>
<script>
function export(){
var name= document.getElementById("#name").value;
var id = document.getElementById("#id").value;
var url = "/export/exportExcel.xls?name="+name+"&id="+id;
window.open(url);
}
</script>
</html>