JAVA后台导出文件
此功能针对Java后端,前端调用接口,访问控制层。针对没有模板的情况。
1.获取数据
List<T> lists = Service.getLists(param);
2.将数据转成Map类型
List<Map<String, Object>> mapList = new ArrayList<>();
mapList = ExcelUtils.objectsToMaps(lists);
//调用 objectsToMaps 方法
/**
* 将list<JavaBean>转化为List<Map<String, Object>>
*
* @param objList
* @param <T>
* @return
*/
public static <T> List<Map<String, Object>> objectsToMaps(List<T> objList) {
List<Map<String, Object>> list = Lists.newArrayList();
if (objList != null && objList.size() > 0) {
Map<String, Object> map = null;
T bean = null;
for (int i = 0, size = objList.size(); i < size; i++) {
bean = objList.get(i);
map = beanToMap(bean);
list.add(map);
}
}
return list;
}
3.设置导出的表头
String exportFields = “rowIndex|序号,字段|名称,字段|名称”;
4.调用导出方法
ExcelUtils.export(response,request, fileName, mapList, exportFields);
public static void export(HttpServletResponse response,HttpServletRequest request,String realName,List<Map<String, Object>> data,String fields) {
String fileName = realName + ".xlsx";
writeFileToResponse(construct(fields, data, fileName), response, request);
}
下载通过的方法
/**
* 将文件写入响应流
*
* @param file 文件
* @param response 响应
* @throws UnsupportedEncodingException
*/
public static void writeFileToResponse(MultipartFile file,
HttpServletResponse response,HttpServletRequest request) {
// 设置文件ContentType类型,这样设置,会自动判断下载文件类型
response.setContentType("multipart/form-data");
InputStream inputStream = null;
try {
String fileName = ((CommonsMultipartFile) file).getFileItem().getName();
String userAgent = request.getHeader("user-agent").toLowerCase();
if (userAgent != null && userAgent.indexOf("firefox") >= 0 || userAgent.indexOf("chrome") >= 0
|| userAgent.indexOf("safari") >= 0) {
fileName= new String((fileName).getBytes(), "ISO8859-1");
} else {
fileName=URLEncoder.encode(fileName,"UTF8"); //其他浏览器
}
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setDateHeader("Expires", (System.currentTimeMillis() + 1000));
inputStream = file.getInputStream();
OutputStream out = response.getOutputStream();
byte[] buffer = new byte[1024];
int count = 0;
while ((count = inputStream.read(buffer)) != -1) {
out.write(buffer, 0, count);
}
out.close();
out.flush();
} catch (IOException e) {
logger.error("文件下载异常:文件名【%s】", file.getName(), e);
} finally {
IOUtils.closeQuietly(inputStream);
}
}
/**
* 创建生成 excel 文档
*
* @param fields 导出字段
* @param data 导出数据
* @param fileName 导出文档名称
* @return 生成的 excel 文档
*/
private static MultipartFile construct(String fields, List<Map<String, Object>> data, String fileName) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
CellStyle cellStyle = createCellStyle(workbook);
Map<String, Integer> fieldOrder = getFieldOrder(fields);
// 头部占的行数
int headerRows = calculateHeaderRows(fields);
initHeader(sheet, cellStyle, fields, headerRows, workbook);
fillData(workbook, sheet, cellStyle, data, fieldOrder, headerRows);
DiskFileItem fileItem = (DiskFileItem) (new DiskFileItemFactory()).createItem("file", "text/plain", true, fileName);
try {
OutputStream os = fileItem.getOutputStream();
Throwable var8 = null;
try {
workbook.write(os);
} catch (Throwable var18) {
var8 = var18;
throw var18;
} finally {
if (os != null) {
if (var8 != null) {
try {
os.close();
} catch (Throwable var17) {
var8.addSuppressed(var17);
}
} else {
os.close();
}
}
}
} catch (Exception var20) {
throw new IllegalArgumentException("Invalid file: " + var20, var20);
}
return new CommonsMultipartFile(fileItem);
}
/**
* 初始化表头
*
* @param sheet sheet页
* @param cellStyle 单元格样式
* @param fields 导出字段
* @param headerRows 头部所占的行数
*/
private static void initHeader(Sheet sheet, CellStyle cellStyle, String fields, int headerRows, Workbook workbook) {
// 构造表头
Row row1 = sheet.createRow(0);
Row row2 = null;
if (headerRows > 1) {
row2 = sheet.createRow(1);
}
String[] field = fields.split(",");
// 上一个合并的表头名称
String preMergeName = null;
// 合并的起止索引
int startIdx = 0, endIdx = -1;
for (int i = 0; i < field.length; i++) {
String[] f = field[i].split("\\|");
Cell c1 = row1.createCell(i);
c1.setCellStyle(cellStyle);
c1.setCellValue(f[1]);
if (f.length > 2) {
Cell c2 = row2.createCell(i);
c2.setCellStyle(cellStyle);
c2.setCellValue(f[f.length - 1]);
if (preMergeName == null) {
preMergeName = f[1];
startIdx = i;
endIdx = startIdx;
} else if (preMergeName.equals(f[1])) {
endIdx++;
} else {
mergeRegion(sheet, 0, 0, startIdx, endIdx, workbook);
preMergeName = f[1];
startIdx = i;
endIdx = startIdx;
}
} else {
if (headerRows > 1) {
mergeRegion(sheet, 0, 1, i, i, workbook);
}
if (preMergeName != null) {
mergeRegion(sheet, 0, 0, startIdx, endIdx, workbook);
preMergeName = null;
}
}
// 处理最后一次循环
if (i == field.length - 1) {
if (preMergeName != null) {
mergeRegion(sheet, 0, 0, startIdx, endIdx, workbook);
}
}
}
}
/**
* 计算表头需要占用的行数
*
* @param fields 导出字段
* @return 表头需要占用的行数
*/
private static int calculateHeaderRows(String fields) {
int rowMax = 0;
int rowCount = 0;
char[] chars = fields.toCharArray();
for (char c : chars) {
if (c == '|') {
rowCount++;
} else if (c == ',') {
if (rowCount > rowMax) {
rowMax = rowCount;
}
rowCount = 0;
}
}
return rowMax;
}
/**
* 填充数据
*
* @param workbook workbook文档
* @param sheet sheet页
* @param cellStyle 单元格样式
* @param data 待填充的数据
* @param fieldOrder 字段填充顺序
* @param headerRows 头部占的行数
*/
private static void fillData(Workbook workbook, Sheet sheet, CellStyle cellStyle, List<Map<String, Object>> data, Map<String, Integer> fieldOrder, int headerRows) {
for (int i = 0; i < data.size(); i++) {
Row row = sheet.createRow(headerRows + i);
data.get(i).forEach((key, value) -> {
// 为了保证只导出 fields 中的字段,这里必须加这个判断
if (fieldOrder.get(key) != null) {
Cell cell = row.createCell(fieldOrder.get(key));
cell.setCellStyle(cellStyle);
if (value != null) {
if (value instanceof Image) {
// setCellPicture(workbook, sheet, cell, (Image) value);
} else {
cell.setCellValue(String.valueOf(value));
}
} else {
cell.setCellValue("");
}
}
});
}
}
/**
* 设置单元格图片(图片是悬浮的)
* @param workbook workbook文档
* @param sheet sheet页
* @param cell 单元格
* @param image 图片
*/
private static void setCellPicture(Workbook workbook, Sheet sheet, Cell cell, Image image) {
int rowIndex = cell.getRowIndex();
int columnIndex = cell.getColumnIndex();
// 绘图对象
Drawing<?> patriarch = sheet.createDrawingPatriarch();
// 创建锚点
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex, columnIndex + 1, rowIndex + 1);
patriarch.createPicture(anchor, workbook.addPicture(image.getImageData(), XSSFWorkbook.PICTURE_TYPE_JPEG));
}
/**
* 得到字段的行索引
*
* @param fields 字段
* @return 字段索引map
*/
private static Map<String, Integer> getFieldOrder(String fields) {
Map<String, Integer> fieldOrder = new HashMap<>(16);
String[] field = fields.split(",");
for (int i = 0; i < field.length; ++i) {
String[] f = field[i].split("\\|");
fieldOrder.put(f[0], i);
}
return fieldOrder;
}
/**
* 创建自定义单元格样式
*
* @param workbook 工作簿
*/
private static CellStyle createCellStyle(Workbook workbook) {
// 为单元格设置边框线
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 居中显示
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return cellStyle;
}
/**
* 合并区域
*
* @param sheet sheet页
* @param firstRow 起始行
* @param lastRow 结束行
* @param firstCol 起始列
* @param lastCol 结束列
*/
private static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, Workbook workbook) {
if (firstRow == lastRow && firstCol == lastCol) {
return;
}
CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(cra);
// 为合并后的单元格添加边框线
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cra, sheet, workbook);
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, cra, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cra, sheet, workbook);
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cra, sheet, workbook);
}
/**
* 转换时间格式
*
* @param data 导出数据
* @param formatter 转换格式 formatter
*/
private static void convertDateToStr(List<Map<String, Object>> data, String formatter) {
for (Map<String, Object> datum : data) {
for (Map.Entry<String, Object> stringObjectEntry : datum.entrySet()) {
String key = stringObjectEntry.getKey();
Object value = stringObjectEntry.getValue();
if (value != null) {
if (value instanceof Date) {
datum.put(key, DateFormatUtils.format((Date) value, formatter));
} else if (value instanceof LocalDateTime) {
datum.put(key, ((LocalDateTime) value).format(DateTimeFormatter.ofPattern(formatter)));
}
}
}
}
}
/**
* 将list<JavaBean>转化为List<Map<String, Object>>
*
* @param objList
* @param <T>
* @return
*/
public static <T> List<Map<String, Object>> objectsToMaps(List<T> objList) {
List<Map<String, Object>> list = Lists.newArrayList();
if (objList != null && objList.size() > 0) {
Map<String, Object> map = null;
T bean = null;
for (int i = 0, size = objList.size(); i < size; i++) {
bean = objList.get(i);
map = beanToMap(bean);
list.add(map);
}
}
return list;
}
/**
* 将对象转化为map
*
* @param bean
* @param <T>
* @return
*/
public static <T> Map<String, Object> beanToMap(T bean) {
Map<String, Object> map = Maps.newHashMap();
if (bean != null) {
BeanMap beanMap = BeanMap.create(bean);
for (Object key : beanMap.keySet()) {
map.put(key.toString(), beanMap.get(key));
}
}
return map;
}