实体类
//实体类①
@Getter
@Setter
public class AssetExportInfo{
@ApiModelProperty(value = "店铺名称")
private String shopName;
@ApiModelProperty(value = "账单编号")
private String shopNumber;
@ApiModelProperty(value = "账单开始时间")
private String startTime;
@ApiModelProperty(value = "账单结束时间")
private String endTime;
@ApiModelProperty(value = "收入合计")
private String incomeTotal;
@ApiModelProperty(value = "支出合计")
private String expendTotal;
@ApiModelProperty(value = "账单总计")
private String billTotal;
@ApiModelProperty(value = "其他-记账金额")
private String tallyMoney;
@ApiModelProperty(value = "其他-优惠金额")
private String discountsMoney;
@ApiModelProperty(value = "其他-失效金额")
private String loseEfficacyMoney;
@ApiModelProperty(value = "其他-过账金额")
private String postMoney;
@ApiModelProperty("对账单店铺xxx命名")
private String name;
@ApiModelProperty("对账单数据")
List<StatementOfAccountList> statementOfAccountList;
}
//实体类②
@Getter
@Setter
public class StatementOfAccountList{
@ApiModelProperty("对账单-收支类型名称")
private String name;
List<StatementOfAccountChildrenList> statementOfAccountChildrenLists;
}
//实体类③
@Getter
@Setter
public class StatementOfAccountChildrenList{
@ApiModelProperty("资金类型")
private String capitalType;
@ApiModelProperty("笔数")
private String number;
@ApiModelProperty("金额(元)")
private String money;
}
实现方法 我用的json接收
public void assetExportAsync (
req.setDataInfo("{\n" +
" \"statementOfAccountList\":[\n" +
" \n" +
"\t\t{\n" +
"\t\t \"statementOfAccountChildrenLists\":[\n" +
" {\n" +
" \"capital_type\":\"POS微信支付\",\n" +
" \"money\":\"+6100\",\n" +
" \"number\":\"199\"\n" +
" },\n" +
"\t\t\t\t{\n" +
" \"capital_type\":\"POS支付宝支付\",\n" +
" \"money\":\"-1155\",\n" +
" \"number\":\"218\"\n" +
" }\n" +
" ],\n" +
" \"name\":\"收入\"\n" +
"\t\t},\n" +
"\t\t\t{\n" +
"\t\t \"statementOfAccountChildrenLists\":[\n" +
" {\n" +
" \"capital_type\":\"手机优惠券\",\n" +
" \"money\":\"-100\",\n" +
" \"number\":\"1\"\n" +
" },\n" +
"\t\t\t\t{\n" +
" \"capital_type\":\"通用积分\",\n" +
" \"money\":\"-53\",\n" +
" \"number\":\"1\"\n" +
" }\n" +
" ],\n" +
" \"name\":\"失效\"\n" +
"\t\t}\n" +
" ],\n" +
" \"shopName\":\"epn\",\n" +
"\t\"name\":\"epn\",\n" +
"\t\"type\":\"3\",\n" +
"\t\"shopNumber\":\"77777777775446546545\",\n" +
"\t\"startTime\":\"2017-07-07\",\n" +
"\t\"endTime\":\"2017-07-09\",\n" +
"\t\"incomeTotal\":\"收入合计:共2笔,+150元\",\n" +
"\t\"expendTotal\":\"支出合计:共1笔,-100元\",\n" +
"\t\"billTotal\":\"账单总计:+50元\",\n" +
"\t\"tallyMoney\":\"记账金额:共0笔,0.00元\",\n" +
"\t\"discountsMoney\":\"优惠金额:共0笔,0.00元\",\n" +
"\t\"loseEfficacyMoney\":\"失效金额:共0笔,0.00元\"\n" +
"}");
AssetExportInfo assetExportInfo = JSON.parseObject(JSON.parse(req.getDataInfo()).toString(), AssetExportInfo.class);
int rowNum = 0;
// 穿件一个excel文件
HSSFWorkbook workBook = new HSSFWorkbook();
// 创建一个sheet表
HSSFSheet sheet = workBook.createSheet("sheet1");
sheet.setDefaultColumnWidth(12);
sheet.autoSizeColumn(1);
sheet.setDefaultRowHeight((short) 390);
// 创建行
HSSFRow titleRow = sheet.createRow((short) rowNum++);
// 字体设置
HSSFFont font = workBook.createFont();
font.setFontName("宋体");
font.setColor(HSSFFont.COLOR_NORMAL);
// 样式
HSSFCellStyle cellStyle = workBook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
//设置第①行
HSSFCell rowOne = titleRow.createCell(0);
rowOne.setCellStyle(cellStyle);
rowOne.setCellValue(assetExportInfo.getShopName()+" "+assetExportInfo.getName());
rowNum = firstEstablish(rowNum, "账单编号:" + assetExportInfo.getShopNumber(), sheet, cellStyle, 0, workBook);
// 设置第③行
HSSFRow rowThree = sheet.createRow(rowNum++);
HSSFCell rowThreeLeftCell = rowThree.createCell(0);
rowThreeLeftCell.setCellStyle(cellStyle);
rowThreeLeftCell.setCellValue("账单开始日期:" + assetExportInfo.getStartTime());
HSSFCell rowThreeRightCell = rowThree.createCell(4);
rowThreeRightCell.setCellStyle(cellStyle);
rowThreeRightCell.setCellValue("账单结束日期:" + assetExportInfo.getEndTime());
//设置第四行
rowNum = firstEstablish(rowNum, "-------------资金入账----------", sheet, cellStyle, 0, workBook);
// 设置第一行 合并以及设置边框
// 设置第一行
setBorderStyle(0, 0, 0, 7, sheet);
// 设置第二行
setBorderStyle(1, 1, 0, 7, sheet);
// 设置第三行
setBorderStyle(2, 2, 0, 3, sheet);
// 设置第三行
setBorderStyle(2, 2, 4, 7, sheet);
//设置第四行
setBorderStyle(3, 3, 0, 7, sheet);
statementOfAccountExport(assetExportInfo,rowNum,sheet,cellStyle,workBook,time,logId);
}
其他需要调用的方法
public void statementOfAccountExport(AssetExportInfo assetExportInfo,int rowNum,HSSFSheet sheet,HSSFCellStyle cellStyle,HSSFWorkbook workBook,Integer time, String logId){
String[] secondRowTitle = {"收支类型", "资金类型", "笔数", "金额(元)"};
List<StatementOfAccountList> statementList = assetExportInfo.getStatementOfAccountList();
if (!CollectionUtils.isEmpty(statementList)) {
List<StatementOfAccountList> judgeDemo = statementList.stream().filter(s -> s.getName().equals("收入")).collect(Collectors.toList());
List<StatementOfAccountList> judgeTwoDemo = statementList.stream().filter(s -> s.getName().equals("支出")).collect(Collectors.toList());
List<StatementOfAccountList> judgeThreeDemo = statementList.stream().filter(s -> s.getName().equals("记账")).collect(Collectors.toList());
List<StatementOfAccountList> judgeFourDemo = statementList.stream().filter(s -> s.getName().equals("优惠")).collect(Collectors.toList());
List<StatementOfAccountList> judgeFivDemo = statementList.stream().filter(s -> s.getName().equals("失效")).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(judgeDemo) || !CollectionUtils.isEmpty(judgeTwoDemo)) {
int titleNum = rowNum;
//设置第⑤行
rowNum = getTitleRowNum(rowNum, sheet, secondRowTitle, cellStyle);
setBorderStyle(titleNum, titleNum, 0, 1, sheet);
setBorderStyle(titleNum, titleNum, 2, 3, sheet);
setBorderStyle(titleNum, titleNum, 4, 5, sheet);
setBorderStyle(titleNum, titleNum, 6, 7, sheet);
int nowNum = rowNum;
if (!CollectionUtils.isEmpty(judgeDemo)) {
rowNum = getRowNum(rowNum, workBook, sheet, cellStyle, judgeDemo, nowNum);
}
int nowNewNum = rowNum;
if (!CollectionUtils.isEmpty(judgeTwoDemo)) {
rowNum = getRowNum(rowNum, workBook, sheet, cellStyle, judgeTwoDemo, nowNewNum);
}
} else {
int nowNum = rowNum;
rowNum = firstEstablish(rowNum, "暂无收支账单信息", sheet, cellStyle, 0, workBook);
setBorderStyle(nowNum, nowNum, 0, 7, sheet);
}
if (!CollectionUtils.isEmpty(judgeThreeDemo) || !CollectionUtils.isEmpty(judgeFourDemo) || !CollectionUtils.isEmpty(judgeFivDemo)) {
int num = rowNum;
rowNum = firstEstablish(rowNum, "-------------其他资金列表----------", sheet, cellStyle, 0, workBook);
setBorderStyle(num, num, 0, 7, sheet);
int titleNum=rowNum;
rowNum = getTitleRowNum(rowNum, sheet, secondRowTitle, cellStyle);
setBorderStyle(titleNum, titleNum, 0, 1, sheet);
setBorderStyle(titleNum, titleNum, 2, 3, sheet);
setBorderStyle(titleNum, titleNum, 4, 5, sheet);
setBorderStyle(titleNum, titleNum, 6, 7, sheet);
int nowNum = rowNum;
if (!CollectionUtils.isEmpty(judgeThreeDemo)) {
rowNum = getRowNum(rowNum, workBook, sheet, cellStyle, judgeThreeDemo, nowNum);
}
int nowNewNum = rowNum;
if (!CollectionUtils.isEmpty(judgeFourDemo)) {
rowNum = getRowNum(rowNum, workBook, sheet, cellStyle, judgeFourDemo, nowNewNum);
}
int nowNewsNum = rowNum;
if (!CollectionUtils.isEmpty(judgeFivDemo)) {
rowNum = getRowNum(rowNum, workBook, sheet, cellStyle, judgeFivDemo, nowNewsNum);
}
}
int num = rowNum;
rowNum = firstEstablish(rowNum, "", sheet, cellStyle, 0, workBook);
setBorderStyle(num, num, 0, 7, sheet);
if (Strings.isNotBlank(assetExportInfo.getIncomeTotal())) {
int nowNum = rowNum;
rowNum = firstEstablish(rowNum, assetExportInfo.getIncomeTotal(), sheet, cellStyle, 1, workBook);
setBorderStyle(nowNum, nowNum, 0, 7, sheet);
}
if (Strings.isNotBlank(assetExportInfo.getExpendTotal())) {
int nowNewNum = rowNum;
rowNum = firstEstablish(rowNum, assetExportInfo.getExpendTotal(), sheet, cellStyle, 1, workBook);
setBorderStyle(nowNewNum, nowNewNum, 0, 7, sheet);
}
if (Strings.isNotBlank(assetExportInfo.getBillTotal())) {
int nowNewsNum = rowNum;
rowNum = firstEstablish(rowNum, assetExportInfo.getBillTotal(), sheet, cellStyle, 1, workBook);
setBorderStyle(nowNewsNum, nowNewsNum, 0, 7, sheet);
}
if (Strings.isNotBlank(assetExportInfo.getTallyMoney()) || Strings.isNotBlank(assetExportInfo.getDiscountsMoney()) || Strings.isNotBlank(assetExportInfo.getLoseEfficacyMoney())) {
int nowNewsNum = rowNum;
rowNum = normalEstablish(rowNum, "其他", assetExportInfo.getTallyMoney(), assetExportInfo.getDiscountsMoney(), assetExportInfo.getLoseEfficacyMoney(), sheet, cellStyle, 1, workBook);
setBorderStyle(nowNewsNum, nowNewsNum, 0, 1, sheet);
setBorderStyle(nowNewsNum, nowNewsNum, 2, 3, sheet);
setBorderStyle(nowNewsNum, nowNewsNum, 4, 5, sheet);
setBorderStyle(nowNewsNum, nowNewsNum, 6, 7, sheet);
}
int nowNums = rowNum;
rowNum = firstEstablish(rowNum, "导出时间:" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()), sheet, cellStyle, 1, workBook);
setBorderStyle(nowNums, nowNums, 0, 7, sheet);
}else{
int nowNum = rowNum;
rowNum = firstEstablish(rowNum, "暂无收支账单信息", sheet, cellStyle, 0, workBook);
setBorderStyle(nowNum, nowNum, 0, 7, sheet);
int nowTwoNum = rowNum;
rowNum = firstEstablish(rowNum, "", sheet, cellStyle, 0, workBook);
setBorderStyle(nowTwoNum, nowTwoNum, 0, 7, sheet);
int nowThreeNum = rowNum;
rowNum = firstEstablish(rowNum, "账单总计:0.00元", sheet, cellStyle, 1, workBook);
setBorderStyle(nowThreeNum, nowThreeNum, 0, 7, sheet);
int nowNewsNum = rowNum;
rowNum = normalEstablish(rowNum, "其他", "记账金额:共0笔,0.00元", "优惠金额:共0笔,0.00元", "失效金额:共0笔,0.00元", sheet, cellStyle, 1, workBook);
setBorderStyle(nowNewsNum, nowNewsNum, 0, 1, sheet);
setBorderStyle(nowNewsNum, nowNewsNum, 2, 3, sheet);
setBorderStyle(nowNewsNum, nowNewsNum, 4, 5, sheet);
setBorderStyle(nowNewsNum, nowNewsNum, 6, 7, sheet);
int nowFourNum = rowNum;
rowNum = firstEstablish(rowNum, "导出时间:" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()), sheet, cellStyle, 1, workBook);
setBorderStyle(nowFourNum, nowFourNum, 0, 7, sheet);
}
try {
OutputStream outputStream = new FileOutputStream("D:\\AAA.xls");
workBook.write(outputStream);
outputStream.close();
File file = new File(savePath);
resultImageUrl=fileUploadUtils.uploadFileGetURL(file);
} catch (Exception e) {
e.printStackTrace();
}
}
//设置边框-合并单元格
private void setBorderStyle(int firstRow, int lastRow, int firstCol, int lastCol, HSSFSheet sheet) {
CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); // 起始行, 终止行, 起始列, 终止列
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
sheet.addMergedRegion(cra);
}
private int getTitleRowNum(int rowNum, HSSFSheet sheet, String[] secondRowTitle, HSSFCellStyle cellStyle) {
HSSFRow rowNine = sheet.createRow(rowNum++);
for (int i = 0; i < secondRowTitle.length; i++) {
HSSFCell rowNineCell = rowNine.createCell(i*2);
rowNineCell.setCellStyle(cellStyle);
rowNineCell.setCellValue(secondRowTitle[i]);
}
return rowNum;
}
private int getRowNum(int rowNum, HSSFWorkbook workBook, HSSFSheet sheet, HSSFCellStyle cellStyle, List<StatementOfAccountList> accountList, int nowNum) {
int size = 0;
for (int i = 0; i < accountList.size(); i++) {
StatementOfAccountList statementOfAccountList = accountList.get(i);
List<StatementOfAccountChildrenList> childrenList = statementOfAccountList.getStatementOfAccountChildrenLists();
size = childrenList.size();
for (int j = 0; j < childrenList.size(); j++) {
StatementOfAccountChildrenList demo = childrenList.get(j);
int otherNum=rowNum;
rowNum = normalEstablish(rowNum, statementOfAccountList.getName(), demo.getCapitalType(), demo.getNumber(), demo.getMoney(), sheet, cellStyle, 0, workBook);
setBorderStyle(otherNum, otherNum, 2, 3, sheet);
setBorderStyle(otherNum, otherNum, 4, 5, sheet);
setBorderStyle(otherNum, otherNum, 6, 7, sheet);
}
}
if (size > 1) {
rowNum = rowNum - 1;
setBorderStyle(nowNum, rowNum, 0, 1, sheet);
rowNum = rowNum + 1;
}
return rowNum;
}
private int firstEstablish(int rowNum, String OneValue, HSSFSheet sheet, HSSFCellStyle cellStyle, int type, HSSFWorkbook workBook) {
HSSFCellStyle OtherCellStyle = getHssfCellStyle(cellStyle, type, workBook);
HSSFRow row = sheet.createRow(rowNum++);
HSSFCell rowCell = row.createCell(0);
rowCell.setCellStyle(OtherCellStyle);
rowCell.setCellValue(OneValue);
return rowNum;
}
private HSSFCellStyle getHssfCellStyle(HSSFCellStyle cellStyle, int type, HSSFWorkbook workBook) {
HSSFCellStyle OtherCellStyle = workBook.createCellStyle();
if (type != 0) {
OtherCellStyle.setBorderLeft(BorderStyle.THIN);
OtherCellStyle.setBorderRight(BorderStyle.THIN);
OtherCellStyle.setBorderTop(BorderStyle.THIN);
OtherCellStyle.setBorderBottom(BorderStyle.THIN);
OtherCellStyle.setAlignment(HorizontalAlignment.LEFT); // 靠左
OtherCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
} else {
OtherCellStyle = cellStyle;
}
return OtherCellStyle;
}
最后预览效果
总结 可以根据自己的需求去控制表格的生成