package com.aistarfish.damo.web.lion.rest.utils;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
/**
* Excel导出
* @author 17040365
*/
public class ExcelExporter1 {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExporter1.class);
public static final int PAGE_SIZE = 1000;
public static final int EXPORT_TOTAL_SIZE = 20000;
/**
* 导出excel
*
* @param excelData
* @return
*/
public static <T> void writeDetailExcel(XSSFSheet sheet,XSSFWorkbook workbook, List<ExcelData> excelData) {
//开始遍历源数据进行表格数据组装
for (int m = 0; m < excelData.size(); m++) {
// String sheetName = excelData.get(m).getSheetName()
List<String> titleData = excelData.get(m).getTitleData();
//2)在workbook中获取第一个Sheet
// XSSFSheet sheet = workbook.createSheet()
//3)创建标题,在sheet中添加表头第0行
XSSFRow row = sheet.createRow(0);
//首行冻结
sheet.createFreezePane(0,1);
CellStyle cellStyle = workbook.createCellStyle();
//边框样式
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
//水平居中
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//垂直居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置单元格颜色
cellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
//设置填充样式(实心填充),不设置填充样式不会有颜色
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//设置字体
Font font = workbook.createFont();
font.setFontName("宋体");
// 设置字体大小
font.setFontHeightInPoints((short) 12);
//字体颜色
font.setColor(IndexedColors.WHITE.getIndex());
// 加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
//行高
short height = 500;
row.setHeight(height);
for (int i = 0; i < titleData.size(); i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(titleData.get(i));
//设置自动列宽
// sheet.autoSizeColumn(i);
//设置固定列宽
sheet.setColumnWidth(i, 5000);
}
CellStyle detailCellStyle = workbook.createCellStyle();
detailCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
detailCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
detailCellStyle.setBorderRight(CellStyle.BORDER_THIN);
detailCellStyle.setBorderTop(CellStyle.BORDER_THIN);
//创建内容
List<List<Map<String, String>>> cellValue = excelData.get(m).getCellValue();
for (List<Map<String, String>> rowList : cellValue) {
//默认第一行为标题
XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < rowList.size(); i++) {
//将内容按顺序赋给对应的列对象
Map<String, String> rowMap = rowList.get(i);
Entry<String, String> rowEntry = rowMap.entrySet().iterator().next();
XSSFCell cell = dataRow.createCell(i);
cell.setCellStyle(detailCellStyle);
cell.setCellValue(rowEntry.getValue());
}
}
}
}
public static <T> void writeExcel(Workbook workbook,String fileName,HttpServletResponse response) {
OutputStream outputStream = null;
try {
// 设置response头信息
response.reset();
// 改成输出excel文件
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" +
URLEncoder.encode(fileName, "utf-8")+".xlsx");
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (IOException e) {
LOGGER.error("导出excel失败:", e);
} finally {
if (null != outputStream) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
LOGGER.error("导出excel失败:", e);
}
}
}
}
/**
* 订单导出
* @param exportFields 导出标题和对象字段
* @param mapList 导出数据
* @param <T>
*/
public static <T> void export(XSSFSheet sheet,XSSFWorkbook workbook,List<Map<String,String>> exportFields,List<Map<String,String>> mapList){
if(CollectionUtils.isEmpty(exportFields)){
return;
}
List<String> titleData = new ArrayList<>();
List<String> titleEnData = new ArrayList<>();
for (Map<String, String> exportField : exportFields) {
for (Entry<String, String> entry : exportField.entrySet()) {
titleEnData.add(entry.getKey());
titleData.add(entry.getValue());
}
}
List<ExcelData> excelData = new ArrayList<>();
ExcelData data = new ExcelData();
data.setSheetName("支付订单");
data.setTitleData(titleData);
List<List<Map<String,String>>> cellValue = new ArrayList<>();
for (Map<String, String> map : mapList) {
List<Map<String,String>> maps = new ArrayList<>();
for (String key : titleEnData) {
for (String s : map.keySet()) {
if(s.equals(key)){
Map<String,String> map1 = new HashMap<>();
map1.put(key,map.get(key));
maps.add(map1);
break;
}
}
}
cellValue.add(maps);
}
data.setCellValue(cellValue);
excelData.add(data);
writeDetailExcel(sheet,workbook,excelData);
}
}
=======================================================================
package com.aistarfish.damo.web.lion.rest.utils;
import java.util.List;
import java.util.Map;
/**
* excel实体
*/
public class ExcelData {
/**
* 表头
*/
private List<String> titleData;
/**
* sheet名称
*/
private String sheetName;
/**
* 行
*/
private List<List<Map<String,String>>> cellValue;
public List<String> getTitleData() {
return titleData;
}
public void setTitleData(List<String> titleData) {
this.titleData = titleData;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<List<Map<String, String>>> getCellValue() {
return cellValue;
}
public void setCellValue(List<List<Map<String, String>>> cellValue) {
this.cellValue = cellValue;
}
}
======================================================================
@PostMapping("/export") public void export(@RequestBody UserPageInfoV2 userPageInfo, HttpServletResponse response) { AssertUtils.notNull(userPageInfo); try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); userPageInfo.setCurrent(1); userPageInfo.setSize(ExcelExporter.PAGE_SIZE); UserPageInfoV2 pageInfoV2 = patientPayService.exportPayOrder(userPageInfo); int total = pageInfoV2.getTotal(); if(total > ExcelExporter.EXPORT_TOTAL_SIZE){ throw new DamoException(JsonCodeEnum.EXPORT_EXCEL_FAIL, "最多只可导出20000条数据,请筛选后再试。"); } ExcelExporter.export(sheet,workbook,userPageInfo.getExportFields(),pageInfoV2.getRecords()); if(total > ExcelExporter.PAGE_SIZE){ int y = total % ExcelExporter.PAGE_SIZE > 0 ? 1 : 0; int t = total / ExcelExporter.PAGE_SIZE + y; for(int i = 1; i < t ;i++){ userPageInfo.setCurrent(i+1); UserPageInfoV2 pageInfo = patientPayService.exportPayOrder(userPageInfo); if(null != pageInfo){ ExcelExporter.export(sheet,workbook,userPageInfo.getExportFields(),pageInfo.getRecords()); } } } String exportFileName = "exportOrder"+ DateUtils.getCurrentTime(); ExcelExporter.writeExcel(workbook,exportFileName,response); } catch (Throwable e) { throw new DamoException(JsonCodeEnum.EXPORT_EXCEL_FAIL, e); } }
======================================================================
public class UserPageInfoV2 extends Paginate { private String productId; private String payNo; private String orderStatus; private String startTime; private String endTime; private List<String> userIds; private String userKeyWord; List<Map<String, String>> exportFields;
。。。
}
======================================================================
// // Source code recreated from a .class file by IntelliJ IDEA // (powered by FernFlower decompiler) // package com.aistarfish.zeus.common.facade.model; import java.util.List; public class Paginate<T> { public static final int DEFAULT_PAGE_SIZE = 10; private int current = 1; private int pages; private int size = 10; private int total; private List<T> records; public Paginate() { } public Paginate(Paginate paginate) { this.current = paginate.getCurrent(); this.size = paginate.getSize(); this.total = paginate.getTotal(); } public Paginate(int current, int size) { this.size = size; this.current = current; } public int getCurrent() { return this.current; } public void setCurrent(int current) { if (current <= 0) { this.current = 1; } else { this.current = current; } } public int getPages() { if (this.size == 0) { this.pages = 0; } else { this.pages = this.total / this.size; if (this.total % this.size != 0) { ++this.pages; } } return this.pages; } public int getSize() { return this.size; } public void setSize(int size) { if (size <= 0) { this.size = 10; } else { this.size = size; } } public int getTotal() { return this.total; } public void setTotal(int total) { if (total < 0) { this.total = 0; } else { this.total = total; } } public List<T> getRecords() { return this.records; } public void setRecords(List<T> records) { this.records = records; } }
====================================================================
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>test</title> <script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"> </script> <script> $(document).ready(function(){ $("button").click(function(){ let url = "http://localhost:9091/export"; fetch(url, { body: JSON.stringify({ "exportFields":[{"payNo":"订单号"},{"orderStatusName":"订单状态"},{"createTime":"下单时间"},{"productName":"服务项目"},{"providerName":"服务提供者"},{"userName":"患者姓名"},{"userPhoneNum":"患者手机号"},{"discountsAmount":"优惠金额"},{"productPrice":"应付金额"},{"payNo":"支付流水号"},{"payChannel":"支付方式"},{"payAmount":"实付金额"},{"refundTime":"退款时间"},{"refundAmount":"已退款金额"},{"refundReason":"退款原因"},{"refundRemark":"退款备注"},{"gmtModified":"取消时间"}] ,"payNo":"" }), //请求报文 cache: 'no-cache', //是否缓存页面,取值有 *default, no-cache, reload, force-cache, only-if-cached credentials: 'same-origin', // 是否带有包含凭证的请求,取值有include, same-origin, *omit headers: { 'content-type': 'application/json' }, method: 'POST', // 请求方式自定义,支持*GET, POST, PUT, DELETE等 mode: 'cors', // 是否启用cors策略,取值有no-cors, cors }).then((response) => response.blob()) .then((blob) => { console.log(blob.size) let blobUrl = window.URL.createObjectURL(blob); let a = document.createElement('a'); a.download = 'test.xls'; a.href= blobUrl; a.click(); }) }); }); </script> </head> <body> <#--<form enctype='application/json' action="http://dev.aistarfish.net:9091/api/lion/patient/pay/order/export" method="post">--> <#-- <input name='exportFields[0][payNo]' value='订单号'><br/>--> <#-- <input name='exportFields[1][orderStatusName]' value='订单状态'><br/>--> <#-- <input name='exportFields[2][createTime]' value='下单时间'><br/>--> <#-- <input name='exportFields[3][productName]' value='服务项目'><br/>--> <#-- <input name='exportFields[4][providerName]' value='服务提供者'><br/>--> <#-- <input name='exportFields[5][userName]' value='患者姓名'><br/>--> <#-- <input name='exportFields[6][userPhoneNum]' value='患者手机号'><br/>--> <#-- <input name='exportFields[7][discountsAmount]' value='优惠金额'><br/>--> <#-- <input name='exportFields[8][productPrice]' value='应付金额'><br/>--> <#-- <input name='exportFields[9][payNo]' value='支付流水号'><br/>--> <#-- <input name='exportFields[10][payChannel]' value='支付方式'><br/>--> <#-- <input name='exportFields[11][payAmount]' value='实付金额'><br/>--> <#-- <input name='exportFields[12][refundTime]' value='退款时间'><br/>--> <#-- <input name='exportFields[13][refundAmount]' value='已退款金额'><br/>--> <#-- <input name='exportFields[14][refundReason]' value='退款原因'><br/>--> <#-- <input name='exportFields[15][refundRemark]' value='退款备注'><br/>--> <#-- <input name='exportFields[16][gmtModified]' value='取消时间'><br/>--> <#-- <input id="form" type="submit" value="测试导出">--> <#--</form>--> <button>ajax方式导出</button> </body> </html>