建一个Util工具类,工具类的作用是返回一个临时的文件,当然也可以返回一个到本地的文件:
public class ExportExcelUtil {
private FileOutputStream fos;
private static int NUM = 10000;// 一个sheet的记录数
private static String level1 = "1";
private static String level2 = "2";
private static String level3 = "3";
private static String level4 = "4";
/**
* @param title
* 每个Sheet里的顶部大标题
* @param column
* 单个sheet里每行数据的列对应的对象属性名称
* column ="rule_name,cityName,specName,ivrName,contactGroup,specName,RulestCont".split(",");
* @param data
* 数据
* @param fileName
* 文件名
* @throws UnsupportedEncodingException
*/
public File getOutputFile(String[] title, String[] column,List<Map> data, String fileName) throws UnsupportedEncodingException {
if (title == null || title.equals("")) {
System.out.println("Excel表格 标题(表头)为空");
}
if (column == null || column.equals("")) {
System.out.println("没有定义取值字段集合");
}
if (data == null || data.equals("")) {
System.out.println("没有定义导出数据集合");
}
if (fileName == null || fileName.equals("")) {
System.out.println("没有定义输出文件名");
}
HSSFWorkbook workbook = null;
try {
File tempFile = File.createTempFile(fileName, ".xls");
fos = new FileOutputStream(tempFile);
//直接写入本地,改一下流就好了
//fos = new FileOutputStream("E://"+fileName+".xls");
workbook = new HSSFWorkbook();// 创建Excel
HSSFSheet sheet = null; // 工作表
HSSFRow row = null; // 行
HSSFCell cell = null; // 行--列
Iterator it = data.iterator();
int i = 0;
// 字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 父告警标题样式
HSSFCellStyle pStyle = workbook.createCellStyle();
pStyle.setFont(font);
// 子告警标题样式
// HSSFCellStyle sStyle = workbook.createCellStyle();
// sStyle.setFont(font);
// sStyle.setFillBackgroundColor((short) 0x3399CC);
// 告警样式
HSSFCellStyle level1Style = workbook.createCellStyle();
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex((short) 9, (byte) (0xFF), (byte) (0x00),
(byte) (0x00));
palette.setColorAtIndex((short) 10, (byte) (0xFF), (byte) (0xA5),
(byte) (0x00));
palette.setColorAtIndex((short) 11, (byte) (0xFF), (byte) (0xFF),
(byte) (0x00));
palette.setColorAtIndex((short) 12, (byte) (0x41), (byte) (0x69),
(byte) (0xE1));
level1Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level1Style.setFillForegroundColor((short) 9);
HSSFCellStyle level2Style = workbook.createCellStyle();
level2Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level2Style.setFillForegroundColor((short) 10);
HSSFCellStyle level3Style = workbook.createCellStyle();
level3Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level3Style.setFillForegroundColor((short) 11);
HSSFCellStyle level4Style = workbook.createCellStyle();
level4Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level4Style.setFillForegroundColor((short) 12);
sheet = workbook.createSheet("Sheet0"); // 工作簿
row = sheet.createRow(0);
// 在每一页的第一行输入标题
for (int j = 0; j < title.length; j++) {
cell = row.createCell(j);
cell.setCellValue(new HSSFRichTextString(title[j]));
cell.setCellStyle(pStyle);
}
// 逐行添加数据
int k = 0;
while (it.hasNext()) {
if (i / NUM > k) { // 每50000条记录分一页
k = i / NUM;
sheet = workbook.createSheet("Sheet" + k);
row = sheet.createRow(0);
for (int j = 0; j < title.length; j++) {
cell = row.createCell(j);
cell.setCellValue(new HSSFRichTextString(title[i]));
}
}
Map dataMap = (Map) it.next();
row = sheet.createRow(i - NUM * k + 1);
// 输出数据
for (int j = 0; j < column.length; j++) {
cell = row.createCell(j);
// 按字段取值
String columnName = column[j]; //取值的key
cell.setCellValue(new HSSFRichTextString(String.valueOf(dataMap.get(columnName))));
String value = String.valueOf(dataMap.get(columnName));
if (value.equalsIgnoreCase("null") && !value.equals("0")) {
String level = String.valueOf(dataMap.get(columnName)
+ "_level");
if (!level.equalsIgnoreCase("null") && !level.equalsIgnoreCase("")) {
if (level1.equals(level)) {
cell.setCellStyle(level1Style);
} else if (level2.equals(level)) {
cell.setCellStyle(level2Style);
} else if (level3.equals(level)) {
cell.setCellStyle(level3Style);
} else if (level4.equals(level)) {
cell.setCellStyle(level4Style);
}
} else {
cell.setCellStyle(level1Style);
}
}
}
i++;
}
// 写入流
workbook.write(fos);
fos.flush();
fos.close();
workbook.close();
System.out.println("Excel 文件导出完成");
return tempFile;
} catch (Exception e) {
System.out.println("Excel导出错误: ");
e.printStackTrace();
return null;
}
}
}
实现则通过拿取到的临时文件提供下载:
public void ExportToExcel(HttpServletRequest request,
HttpServletResponse response, String[] title, SearchCash searchCash)
throws CommonException, IOException {
ExportExcelUtil eeu = new ExportExcelUtil();
// 用于存储数据
List<Map> list = new ArrayList<Map>();
// 从数据库获取需要的数据
List<Cash> cashs =.....;
// 将数据传入map
for (int i = 0; i < cashs.size(); i++) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("序号", i + 1);
map.put("记录Id", cashs.get(i).getId());
map.put("申请人", cashs.get(i).getRealName());
map.put("金额", cashs.get(i).getMoney());
String key = cashs.get(i).getStatus();
switch (key) {
// 0:新申请,1提现完成,9提现撤销
case "0":
map.put("状态", "新申请");
break;
case "1":
map.put("状态", "提现完成");
break;
case "9":
map.put("状态", "提现撤销");
break;
default:
map.put("状态", "");
break;
}
map.put("创建日期", cashs.get(i).getCreateTime());
list.add(map);
}
String[] column = title;
List<Map> data = list;
String fileName = "提现记录表";
// 从工具类中获取临时的Excel文件
File tempFile = eeu.getOutputFile(title, column, data, fileName);
if (tempFile != null) {
// 处理文件名
String realname = URLEncoder.encode(fileName, "UTF-8");
// 设置响应头,控制浏览器下载该文件
response.reset();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("content-disposition", "attachment;filename="
+ realname + ".xls");
// 读取要下载的文件,保存到文件输入流
FileInputStream in = new FileInputStream(tempFile);
// 创建输出流
OutputStream out = response.getOutputStream();
// 创建缓冲区
byte buffer[] = new byte[1024];
int len = 0;
// 循环将输入流中的内容读取到缓冲区当中
while ((len = in.read(buffer)) > 0) {
// 输出缓冲区的内容到浏览器,实现文件下载
out.write(buffer, 0, len);
}
// 关闭文件输入流
in.close();
// 关闭输出流
out.flush();
out.close();
tempFile.delete();
} else {
throw new CommonException("400",
"操作失败");
}
}
控制层:
@ApiOperation(value="导出记录的Excel")
@RequestMapping(value = "/url", method = RequestMethod.GET)
public void exportToExcel( HttpServletRequest request,
HttpServletResponse response,@ModelAttribute SearchCash searchCash) throws Exception{
String[] title = "序号,记录Id,申请人,金额,状态,创建日期".split(",");
service.ExportToExcel(request,response,title,searchCash);
}