package com.utils;
|
|
import org.apache.poi.hssf.usermodel.HSSFCell;
|
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.core.io.Resource;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.io.OutputStream;
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.Map;
|
|
|
/**
|
* @Author：haung
|
* @Date：2019-09-21 11:21
|
* @Description：Excel导出工具类，依赖于ClassUtil工具类
|
*/
|
public final class ExcelExport {
|
|
/**
|
* 将传入的数据导出excel表并下载
|
* @param response 返回的HttpServletResponse
|
* @param importlist 要导出的对象的集合
|
* @param attributeNames 含有每个对象属性在excel表中对应的标题字符串的数组（请按对象中属性排序调整字符串在数组中的位置）
|
* @throws Exception
|
*/
|
public static void export(HttpServletResponse response, List<Map<String, Object>> importlist, String[] attributeNames, String[] mapNames, String file_name) throws Exception {
|
//获取数据集
|
List<Map<String, Object>> datalist = importlist;
|
|
//声明一个工作薄
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
//生成一个表格
|
HSSFSheet sheet = workbook.createSheet();
|
//设置表格默认列宽度为15个字节
|
sheet.setDefaultColumnWidth((short) 18);
|
|
//获取字段名数组
|
String[] tableAttributeName = attributeNames;
|
/*//获取对象属性
|
Field[] fields = ClassUtil.getClassAttribute(importlist.get(0));
|
//获取对象get方法
|
List<Method> methodList = ClassUtil.getMethodGet(importlist.get(0));*/
|
|
//循环字段名数组，创建标题行-16
|
Row row = sheet.createRow(0);
|
for (int j = 0; j< tableAttributeName.length; j++){
|
//创建列
|
Cell cell = row.createCell(j);
|
//设置单元类型为String
|
//cell.setCellType(CellType.);
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
cell.setCellValue(transCellType(tableAttributeName[j]));
|
}
|
//创建普通行
|
for (int i = 0;i<datalist.size();i++){
|
//因为第一行已经用于创建标题行，故从第二行开始创建
|
row = sheet.createRow(i+1);
|
//如果是第一行就让其为标题行
|
Object targetObj = datalist.get(i);
|
for (int j = 0;j<mapNames.length;j++){
|
//创建列
|
Cell cell = row.createCell(j);
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
//
|
//try {
|
Object value = datalist.get(i).get(mapNames[j]);//methodList.get(j).invoke(targetObj, new Object[]{});
|
cell.setCellValue(transCellType(value));
|
/*} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}*/
|
}
|
}
|
response.setContentType("application/octet-stream");
|
//默认Excel名称
|
// response.setHeader("Content-Disposition", "attachment;fileName="+file_name);
|
response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(file_name, "UTF-8"));
|
|
try {
|
response.flushBuffer();
|
workbook.write(response.getOutputStream());
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
//一定要在XSSFWorkbook用输入流当成构造函数参数创建新对象后，再使用输入流
|
public static void export(HttpServletResponse response, List<Map<String, Object>> importlist, XSSFWorkbook workbook, String[] mapNames, String filePath, String file_name) throws Exception {
|
//获取数据集
|
List<Map<String, Object>> datalist = importlist;
|
//获取第一页
|
Resource resource = new ClassPathResource(filePath);
|
InputStream in = resource.getInputStream();
|
workbook = new XSSFWorkbook(in);
|
XSSFSheet sheet = workbook.getSheetAt(0);
|
//循环字段名数组，创建标题行-16
|
Row row = sheet.getRow(0);
|
/**lst-2021-01-30 标题行设置**/
|
row = sheet.createRow(0);//第一行为标题行
|
for (int j = 0;j<mapNames.length;j++){
|
Cell cell = row.createCell(j);
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
Object value = mapNames[j];//methodList.get(j).invoke(targetObj, new Object[]{});
|
cell.setCellValue(transCellType(value));
|
}
|
/**lst-2021-01-30**/
|
//创建普通行
|
for (int i = 0;i<datalist.size();i++){
|
//因为前两行已经用于创建标题行，故从第二行开始创建
|
row = sheet.createRow(i+1);
|
//如果是第一行就让其为标题行
|
Object targetObj = datalist.get(i);
|
for (int j = 0;j<mapNames.length;j++){
|
//创建列
|
Cell cell = row.createCell(j);
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
//
|
//try {
|
Object value = datalist.get(i).get(mapNames[j]);//methodList.get(j).invoke(targetObj, new Object[]{});
|
cell.setCellValue(transCellType(value));
|
/*} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}*/
|
}
|
}
|
|
// XSSFSheet lastSheet = workbook.getSheetAt(0);
|
// int lastRowNum = lastSheet.getLastRowNum();
|
// Row lastRow = lastSheet.getRow(lastRowNum);
|
// lastRow.getCell(0).setCellValue("");
|
//
|
response.setContentType("application/octet-stream");
|
//默认Excel名称
|
// response.setHeader("Content-Disposition", "attachment;fileName="+file_name);
|
response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(file_name, "UTF-8"));
|
OutputStream outputStream = response.getOutputStream();
|
try {
|
response.flushBuffer();
|
workbook.write(outputStream);
|
// response.getOutputStream().close();
|
// in.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}finally {
|
if (outputStream != null) {
|
try {
|
outputStream.flush();
|
outputStream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
|
private static String transCellType(Object value){
|
String str = null;
|
if (value instanceof Date){
|
Date date = (Date) value;
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
str = sdf.format(date);
|
}else{
|
str = String.valueOf(value);
|
if (str == "null"){
|
str = "";
|
}
|
}
|
|
return str;
|
}
|
|
}
|
