将数据库数据导出到Excel中,并可以在浏览器上下载Excel
附代码:
//设置文件保存路径
public static String url ="F:\\Workspase\\BackController\\src\\main\\webapp\\WEB-INF\\doc\\data.xls";
public static void createExcel(Map<String, List<String>> map, String[] strArray) {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
sheet.setDefaultColumnWidth(20);// 默认列宽
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// 创建一个居中格式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 添加excel title
HSSFCell cell = null;
for (int i = 0; i < strArray.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(strArray[i]);
cell.setCellStyle(style);
}
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = 0;
for (String str : map.keySet()) {
row = sheet.createRow((int) i + 1);
List<String> list = map.get(str);
// 第四步,创建单元格,并设置值
for (int j = 0; j < strArray.length; j++) {
row.createCell((short) j).setCellValue(list.get(j));
}
// 第六步,将文件存到指定位置
try {
FileOutputStream fout = new FileOutputStream(url);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
i++;
}
}
/**
* 下载excel,常规的文件读写操作,只需要在服务器调用时传入文件名(fname)就可以了
* @param
*/
public static void getExcel(String fname){
try {
response.setCharacterEncoding("utf-8");
response.setHeader("Pragma", "No-Cache");
response.setHeader("Cache-Control", "No-Cache");
response.setDateHeader("Expires", 0);
response.setContentType("application/msexcel; charset=UTF-8");
response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fname, "UTF-8"));// 设定输出文件头
ServletOutputStream out = null;
FileInputStream in = new FileInputStream(url); // 读入文件
out = response.getOutputStream();
out.flush();
int aRead = 0;
while ((aRead = in.read()) != -1 & in != null) {
out.write(aRead);
}
out.flush();
in.close();
out.close();
}catch (Exception e){
e.printStackTrace();
}
}
下面是服务器部分:
//生成excel
@RequestMapping("/getExcel")
public String getExcel() {
Map<String, List<String>> memberMap = Download.getMember();
String[] strArray = excelTitle();
Create.createExcel(memberMap, strArray);
return "index";
}
//下载excel(注意@ResponseBody注解)
@ResponseBody
@RequestMapping("download")
public String download() {
Create.getExcel("data.xls");
return "index";
}
完成!!!