1.pom.xml
<!--导出EXCEL-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
2.公用方法
//设置单元格
private static void setCell(HSSFRow row, int col, String content, CellStyle cellStyle)
{
Cell cell = row.createCell(col);
cell.setCellStyle(cellStyle);
cell.setCellValue(content);
}
//设置合并单元格
private void addCellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol, HSSFSheet sheet, Workbook wb) {
CellRangeAddress address = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(address);//订单明细ID
setRegionBorder(1,address,sheet,wb);
}
//给合并单元格增加边框
private static void setRegionBorder(int border, CellRangeAddress region, HSSFSheet sheet, Workbook wb){
RegionUtil.setBorderBottom(border,region, sheet, wb);
RegionUtil.setBorderLeft(border,region, sheet, wb);
RegionUtil.setBorderRight(border,region, sheet, wb);
RegionUtil.setBorderTop(border,region, sheet, wb);
}
3.service
@Transactional
public boolean downloadUsrCommissions(Cash cash, HttpServletResponse response) {
List<Commission> lstCash = query4UsrCommissions(cash);
Doctor doctor = new Doctor();
doctor.setDocId(cash.getDocId());
Doctor search = doctorDao.queryById(doctor);
if(search != null) {
OutputStream os = null;
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = StringUtils.isEmpty(cash.getLastTime())?"全部":cash.getLastTime();
String fileName = "专家收入明细_ "+search.getCnName()+"_"+ time +".xls";
response.setContentType("application/unknown; charset=GB2312");
response.setHeader("content-disposition", "attachment;filename="
+ new String(fileName.getBytes("GB2312"), "iso-8859-1"));
os = response.getOutputStream();
HSSFWorkbook wb = new HSSFWorkbook();
//给sheet命名
HSSFSheet s = wb.createSheet("sheet");
//设置样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setWrapText(true);//设置自动换行
HSSFRow row = s.createRow(0);
setCell(row, 0, "用户", cellStyle);
setCell(row, 1, "支付金额", cellStyle);
setCell(row, 2, "平台服务费", cellStyle);
setCell(row, 3, "实际收益", cellStyle);
setCell(row, 4, "支付时间", cellStyle);
setCell(row, 5, "用户是否已退款", cellStyle);
if(null != lstCash && lstCash.size() > 0){
int rowIndex = 1;
for(Commission commission : lstCash) {
row = s.createRow(rowIndex);
setCell(row, 0, commission.getUsrName(), cellStyle);
setCell(row, 1, commission.getFee(), cellStyle);
setCell(row, 2, commission.getServiceFee(), cellStyle);
setCell(row, 3, commission.getActualFee(), cellStyle);
setCell(row, 4, sdf.format(commission.getTraDate()), cellStyle);
setCell(row, 5, "2".equals(commission.getStatus())?"支付成功":"已退款", cellStyle);
rowIndex++;
}
String totalActIn = "0.00";
List<Commission> total = lstCash.stream().filter(commission -> "2".equals(commission.getStatus())).collect(Collectors.toList());//未退款的才计算
for(Commission tmp : total){
totalActIn = MathUtil.addBigDecimal(totalActIn, tmp.getActualFee());
}
row = s.createRow(rowIndex);
addCellRangeAddress(rowIndex,rowIndex,0,5,s,wb);
setCell(row, 0, "专家:"+search.getCnName() + ",电话:"+search.getTel(), cellStyle);
rowIndex+=1;
row = s.createRow(rowIndex);
addCellRangeAddress(rowIndex,rowIndex,0,1,s,wb);
setCell(row, 0, "银行:"+search.getBank(), cellStyle);
addCellRangeAddress(rowIndex,rowIndex,2,3,s,wb);
setCell(row, 2, "银行卡号:"+search.getBankCardNo(), cellStyle);
addCellRangeAddress(rowIndex,rowIndex,4,5,s,wb);
setCell(row, 4, "累计收入:"+totalActIn, cellStyle);
}
//写入excel 关闭流
wb.write(os);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
os.flush();
os.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}else {
return false;
}
return true;
}