import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import java.util.Map;
@Component
public class ExcelComponent {
/**
* 生成Excel
* @param input
* @return
* @throws Exception
*/
public HSSFWorkbook generateExcel(ExcelFormatInput input) throws Exception{
try {
// step1: 参数校验
this.checkParams(input);
// step2: 构建excel表头及大体样式
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFSheet sheet = wb.createSheet(input.getSheetName());
HSSFRow row = sheet.createRow(0);
row.setHeight((short) (26.25 * 15));
for (int i = 0,length = input.getCellNameArray().length; i < length; i++) {
row.createCell(i).setCellValue(input.getCellNameArray()[i]);
sheet.setColumnWidth(i,(short) (26.25 * 200));
}
// step3: 构建数据行
for (int i = 0,size = input.getDataList().size(); i <size; i++) {
Map<String,Object> map = input.getDataList().get(i);
HSSFRow rows = sheet.createRow(i + 1);
for (int j = 0,length = input.getDataCloumnNameArray().length; j < length; j++) {
rows.setHeight((short) (26.25 * 15));
rows.createCell(j).setCellValue(String.valueOf(map.get(input.getDataCloumnNameArray()[j])));
}
}
return wb;
} catch (Exception e) {
e.printStackTrace();
throw new Exception("生成Excel失败"+e.getMessage());
}
}
/**
* 参数校验
* @param input
*/
private void checkParams(ExcelFormatInput input) throws Exception{
if (StringUtils.isEmpty(input.getSheetName()))
throw new Exception("sheetName不能为空");
if (CollectionUtils.isEmpty(input.getDataList()))
throw new Exception("数据列表dataList不能为空");
if (input.getCellNameArray() == null || input.getCellNameArray().length == 0)
throw new Exception("excel列名列表cellNameArray不能为空");
if (input.getDataCloumnNameArray() == null || input.getDataCloumnNameArray().length == 0)
throw new Exception("数据字段列表dataCloumnNameArray不能为空");
if (input.getDataCloumnNameArray().length != input.getCellNameArray().length)
throw new Exception("数据字段列表dataCloumnNameArray与excel列名列表cellNameArray不匹配");
}
}
import java.io.Serializable;
import java.util.List;
import java.util.Map;
public class ExcelFormatInput implements Serializable {
private static final long serialVersionUID = 8249936897348760813L;
private String[] CellNameArray;
private String[] dataCloumnNameArray;
private String sheetName;
private List<Map<String,Object>> dataList;
public String[] getCellNameArray() {
return CellNameArray;
}
public void setCellNameArray(String[] cellNameArray) {
CellNameArray = cellNameArray;
}
public String[] getDataCloumnNameArray() {
return dataCloumnNameArray;
}
public void setDataCloumnNameArray(String[] dataCloumnNameArray) {
this.dataCloumnNameArray = dataCloumnNameArray;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<Map<String, Object>> getDataList() {
return dataList;
}
public void setDataList(List<Map<String, Object>> dataList) {
this.dataList = dataList;
}
public ExcelFormatInput() {
}
public ExcelFormatInput(String[] cellNameArray, String[] dataCloumnNameArray, String sheetName, List<Map<String, Object>> dataList) {
CellNameArray = cellNameArray;
this.dataCloumnNameArray = dataCloumnNameArray;
this.sheetName = sheetName;
this.dataList = dataList;
}
}
主方法:
/**
* 调账记录数据导出
* @param acctAdjustQuery
* @param response
* @throws Exception
*/
@RequestMapping("/acctAdjustProcessLogExport")
public void acctAdjustProcessLogExport(AcctAdjustQuery acctAdjustQuery, HttpServletResponse response, HttpServletRequest request) throws Exception{
OutputStream os = null;
try {
String sheetName = "调账记录";
String[] cellNameArray = {"调账时间", "订单号", "商户号", "商户名称", "实付金额", "FTP支付结算金额", "代付金额", "FTP代付结算金额", "支付结果", "代付结果", "原支付结果", "原代付结果", "操作人"};
String[] dataColumnNameArray = {"createTime", "orderNum", "merId", "shopName", "payAmt", "ftpPayCalcAmt", "paidAmt", "ftpPaidCalcAmt", "paySts", "paidSts", "oldPaySts", "oldPaidSts", "createUser"};
List<Map<String,Object>> dataList = acctAdjustService.queryProcessLogExprotData(acctAdjustQuery);
ExcelFormatInput input = new ExcelFormatInput(
cellNameArray,
dataColumnNameArray,
sheetName,
dataList
);
HSSFWorkbook wb = excelComponent.generateExcel(input);
os = response.getOutputStream();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition",
"attachment;filename=" + DownChineseEncode.setFileDownHeader(request, response, "调账记录.xls"));
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("调账记录导出失败"+e.getMessage());
} finally {
if (os != null) {
try {
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}