导出Excel的相关内容
设置列index的宽：
SXSSFSheet.setColumnWidth(index, width)
设置样式：
CellStyle style = excelUtil.getWorkbook().createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN) //设置单元格下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN) //设置单元格左边框
style.setAlignment(HSSFCellStyle.ALIGN_CENTER) //设置水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER) //设置垂直居中
style.setWrapText(true) //设置内容自动换行
设置字体：
Font font = excelUtil.getWorkbook().createFont();
font.setFontHeightInPoints((short) 9) //设置字体大小
style.setFont(font) //将字体样式font加入样式style中
创建第index行：
SXSSFRow row1 = sheet.createRow(index)
row1.setHeight(rowHeight) //设置行高
row1.createCell(i).setCellValue(" ") 设置内容
row1.createCell(i).setCellStyle(style) //为行的第i单元格设置样式
合并单元格：
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5))
new CellRangeAddress(startRow, endRow, startColl, endColl)
举例：
private static String excelUtils(LoanWoWorkQueryCondition condition) {
// 创建新的excel
PlExcelUtil excelUtil = PlExcelUtil.newWorkbook();
// 创建标题 Sheet 合并单元格的宽度
SXSSFSheet sheet = excelUtil.addTitle("呆账认定申报表", "呆账认定申报表", 8);
HSSFWorkbook workbook = new HSSFWorkbook();
short rowHeight = 340;
//设置列宽
sheet.setColumnWidth(0, 2500);
sheet.setColumnWidth(1, 2400);
sheet.setColumnWidth(2, 3200);
sheet.setColumnWidth(3, 2900);
sheet.setColumnWidth(4, 3000);
sheet.setColumnWidth(5, 2600);
sheet.setColumnWidth(6, 3000);
sheet.setColumnWidth(7, 3200);
sheet.setDefaultRowHeight((short) 500);
//全局字体
Font font = excelUtil.getWorkbook().createFont();
font.setFontHeightInPoints((short) 9);
//自定义字体
Font font2 = excelUtil.getWorkbook().createFont();
font2.setFontHeightInPoints((short) 9);
font2.setBold(true);
//全局样式
CellStyle style = excelUtil.getWorkbook().createCellStyle();
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
//居中样式
CellStyle centerStyle = excelUtil.getWorkbook().createCellStyle();
centerStyle.setFont(font2);
centerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
centerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
centerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
centerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setWrapText(true);
//左对齐样式
CellStyle leftStyle = excelUtil.getWorkbook().createCellStyle();
leftStyle.setFont(font2);
leftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
leftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
leftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
leftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
leftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
leftStyle.setWrapText(true);
//右对齐样式
CellStyle rightStyle = excelUtil.getWorkbook().createCellStyle();
rightStyle.setFont(font2);
rightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
rightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
rightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
rightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
rightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
rightStyle.setWrapText(true);
//创建行
SXSSFRow row1 = sheet.createRow(1);
SXSSFRow row2 = sheet.createRow(2);
SXSSFRow row3 = sheet.createRow(3);
SXSSFRow row4 = sheet.createRow(4);
SXSSFRow row5 = sheet.createRow(5);
SXSSFRow row6 = sheet.createRow(6);
SXSSFRow row7 = sheet.createRow(7);
SXSSFRow row8 = sheet.createRow(8);
SXSSFRow row9 = sheet.createRow(9);
SXSSFRow row10 = sheet.createRow(10);
SXSSFRow row11 = sheet.createRow(11);
SXSSFRow row12 = sheet.createRow(12);
SXSSFRow row13 = sheet.createRow(13);
SXSSFRow row14 = sheet.createRow(14);
//创建列--防止后面增加样式时报错
for(int i = 0; i < 8; ++i) {
//全局样式
row1.createCell(i).setCellStyle(style);
row2.createCell(i).setCellStyle(style);
row3.createCell(i).setCellStyle(style);
row4.createCell(i).setCellStyle(style);
row5.createCell(i).setCellStyle(style);
row6.createCell(i).setCellStyle(style);
row7.createCell(i).setCellStyle(style);
row8.createCell(i).setCellStyle(style);
row9.createCell(i).setCellStyle(style);
row10.createCell(i).setCellStyle(style);
row11.createCell(i).setCellStyle(style);
row12.createCell(i).setCellStyle(style);
row13.createCell(i).setCellStyle(style);
row14.createCell(i).setCellStyle(style);
}
//第一行
row1.createCell(0).setCellValue("申报机构：" + (loWoWorkInfo == null ? isNull : loWoWorkInfo.getBranch_id()));
row1.createCell(6).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getOver_due_amt().toString());
row1.createCell(7).setCellValue("单位：人民币元");
row1.setHeight(rowHeight);
//第二行
row2.createCell(0).setCellValue("客户名称");
row2.createCell(2).setCellValue("发放金额（除贷记卡）/审批额度（贷记卡）");
row2.createCell(3).setCellValue("风险分类");
row2.createCell(4).setCellValue("起止日期（除贷记卡）/开户日期（贷记卡）");
row2.createCell(6).setCellValue("身份证件号码（贷记卡）");
row2.createCell(7).setCellValue("卡号/借据号");
row2.setHeight((short) 900);
//第三行
row3.createCell(0).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getCust_name());
row3.createCell(2).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getLoan_amt().toString());
row3.createCell(3).setCellValue(riskDesc.equals("") ? isNull : riskDesc);
row3.createCell(4).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getInstdt());
row3.createCell(5).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getMatudt());
row3.createCell(6).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getId_no());
row3.createCell(7).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getDuebill_no());
row3.setHeight((short) 600);
//第四行
row4.createCell(0).setCellValue("业务品种");
row4.createCell(1).setCellValue("担保方式");
row4.createCell(2).setCellValue("申报呆账认定金额");
row4.setHeight(rowHeight);
//第五行
row5.createCell(2).setCellValue("本金");
row5.createCell(4).setCellValue("欠息");
row5.createCell(6).setCellValue("费用及其他（贷记卡）");
row5.setHeight(rowHeight);
//第六行
row6.createCell(0).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getColl_kind().toString());
row6.createCell(1).setCellValue(isNull);
row6.createCell(2).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getOver_due_prin().toString());
row6.createCell(4).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getOver_due_amt().subtract(loWoWorkInfo.getOver_due_prin()).toString());
row6.createCell(6).setCellValue(loWoWorkInfo == null ? isNull : loWoWorkInfo.getOver_due_amt().toString());
row6.setHeight((short) 600);
//第七行
row7.createCell(0).setCellValue("本次呆账认定属于");
row7.createCell(1).setCellValue(condition.getIsPlDeclare().equals("N") ? "☑ 单笔申报" : "□ 单笔申报");
row7.createCell(2).setCellValue(condition.getIsPlDeclare().equals("Y") ? "☑ 批量申报" : "□ 批量申报");
row7.createCell(3).setCellValue("如属于批量申报，上述内容可不进行填写，须另附清单详细罗列（清单格式见“汇总清单”）");
row7.setHeight((short) 600);
//第八行
row8.createCell(0).setCellValue("符合呆账认定的主要条件及适用标准");
row8.setHeight(rowHeight);
//第九行
row9.createCell(0).setCellValue("");
row9.setHeight((short) 2000);
//第十行
row10.createCell(0).setCellValue("经营责任人意见");
row10.createCell(1).setCellValue("");
row10.createCell(3).setCellValue("");
row10.createCell(5).setCellValue("");
row10.createCell(6).setCellValue("");
row10.setHeight((short) 2200);
//第十一行
row11.createCell(1).setCellValue("经营主责任人意见：");
row11.createCell(3).setCellValue("经营岗位责任人意见：");
row11.createCell(5).setCellValue("");
row11.createCell(6).setCellValue("经营机构负责人意见：");
row11.setHeight(rowHeight);
//第十二行
row12.createCell(1).setCellValue("");
row12.createCell(2).setCellValue("");
row12.createCell(3).setCellValue("");
row12.createCell(4).setCellValue("");
row12.createCell(5).setCellValue("");
row12.createCell(6).setCellValue("");
row12.createCell(7).setCellValue("");
row12.setHeight(rowHeight);
//第十三行
row13.createCell(1).setCellValue(" 年 月 日 ");
row13.createCell(3).setCellValue(" 年 月 日 ");
row13.createCell(5).setCellValue("");
row13.createCell(6).setCellValue(" 年 月 日 ");
row13.setHeight(rowHeight);
for(int i = 0; i < 8; ++i) {
//全局样式
row1.getCell(i).setCellStyle(style);
row2.getCell(i).setCellStyle(style);
row3.getCell(i).setCellStyle(style);
row4.getCell(i).setCellStyle(style);
row5.getCell(i).setCellStyle(style);
row6.getCell(i).setCellStyle(style);
row7.getCell(i).setCellStyle(style);
row8.getCell(i).setCellStyle(style);
row9.getCell(i).setCellStyle(style);
row10.getCell(i).setCellStyle(style);
row11.getCell(i).setCellStyle(style);
row12.getCell(i).setCellStyle(style);
row13.getCell(i).setCellStyle(style);
row14.getCell(i).setCellStyle(style);
}
//自定义样式
row1.getCell(0).setCellStyle(leftStyle);
row1.getCell(7).setCellStyle(centerStyle);
row2.getCell(0).setCellStyle(centerStyle);
row2.getCell(2).setCellStyle(centerStyle);
row2.getCell(3).setCellStyle(centerStyle);
row2.getCell(4).setCellStyle(centerStyle);
row2.getCell(6).setCellStyle(centerStyle);
row2.getCell(7).setCellStyle(centerStyle);
row4.getCell(0).setCellStyle(centerStyle);
row4.getCell(1).setCellStyle(centerStyle);
row4.getCell(2).setCellStyle(centerStyle);
row5.getCell(2).setCellStyle(centerStyle);
row5.getCell(4).setCellStyle(centerStyle);
row5.getCell(6).setCellStyle(centerStyle);
row7.getCell(0).setCellStyle(centerStyle);
row7.getCell(1).setCellStyle(centerStyle);
row7.getCell(2).setCellStyle(centerStyle);
row7.getCell(3).setCellStyle(centerStyle);
row8.getCell(0).setCellStyle(centerStyle);
row10.getCell(0).setCellStyle(centerStyle);
row11.getCell(1).setCellStyle(centerStyle);
row11.getCell(3).setCellStyle(centerStyle);
row11.getCell(6).setCellStyle(centerStyle);
row11.getCell(1).setCellStyle(leftStyle);
row11.getCell(3).setCellStyle(leftStyle);
row11.getCell(6).setCellStyle(leftStyle);
row13.getCell(1).setCellStyle(centerStyle);
row13.getCell(3).setCellStyle(centerStyle);
row13.getCell(6).setCellStyle(centerStyle);
row13.getCell(1).setCellStyle(rightStyle);
row13.getCell(3).setCellStyle(rightStyle);
row13.getCell(6).setCellStyle(rightStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
sheet.addMergedRegion(new CellRangeAddress(4, 5, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(4, 5, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 7));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 2, 3));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 4, 5));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 3));
sheet.addMergedRegion(new CellRangeAddress(6, 6, 4, 5));
sheet.addMergedRegion(new CellRangeAddress(6, 6, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 7));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 7));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 0, 7));
sheet.addMergedRegion(new CellRangeAddress(10, 10, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(10, 10, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(11, 11, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(11, 11, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(11, 11, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(13, 13, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(13, 13, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(13, 13, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(10, 13, 0, 0));
String excelName = "ShenBaoXiaXai.xls";
Map<Integer,Map<String,Float>> rowRule = new HashMap<>();
if(condition.getIsPlDeclare().equals("N")) {
//第十四行
row14.createCell(0).setCellValue("注：1、本表适用总行部室申报呆账认定及对申报的呆账认定进行审查。\n" +
"2、原则上按每笔填写。如每笔申请呆账认定的经营责任人相同时，可批量申报及审查。\n" +
"3、如申报贷款的发放币种为其他币种时，请根据原发放币种调整申报的单位。\n" +
"4、该表内容框架可根据国家及行内政策制度、实际执行情况等进行适当调整。");
row14.getCell(0).setCellType(HSSFCellStyle.ALIGN_LEFT);
sheet.addMergedRegion(new CellRangeAddress(14, 14, 0, 7));
row14.setHeight((short) 1200);
row14.getCell(0).setCellStyle(leftStyle);
exportWorkStatisUnnaturalHandle2(excelUtil, loanWoWorkList, condition);
}else if(condition.getIsPlDeclare().equals("Y")) {
//创建行
SXSSFRow row15 = sheet.createRow(15);
SXSSFRow row16 = sheet.createRow(16);
SXSSFRow row17 = sheet.createRow(17);
SXSSFRow row18 = sheet.createRow(18);
SXSSFRow row19 = sheet.createRow(19);
//创建列
for(int i = 0; i < 8; ++i) {
row15.createCell(i);
row16.createCell(i);
row17.createCell(i);
row18.createCell(i);
row19.createCell(i);
}
//第十四行
row14.createCell(0).setCellValue("审查意见");
row14.setHeight(rowHeight);
//第十五行
row15.createCell(0).setCellValue("信贷管理部审查意见");
row15.createCell(1).setCellValue("");
row15.createCell(3).setCellValue("");
row15.createCell(5).setCellValue("");
row15.createCell(6).setCellValue("");
row15.setHeight((short) 2400);
//第十六行
row16.createCell(1).setCellValue("审查人：");
row16.createCell(3).setCellValue("分管负责人：");
row16.createCell(5).setCellValue("");
row16.createCell(6).setCellValue("部门负责人：");
row16.setHeight(rowHeight);
//第十七行
row17.createCell(1).setCellValue("");
row17.createCell(2).setCellValue("");
row17.createCell(3).setCellValue("");
row17.createCell(4).setCellValue("");
row17.createCell(5).setCellValue("");
row17.createCell(6).setCellValue("");
row17.createCell(7).setCellValue("");
row17.setHeight(rowHeight);
//第十八行
row18.createCell(1).setCellValue(" 年 月 日 ");
row18.createCell(3).setCellValue(" 年 月 日 ");
row18.createCell(5).setCellValue("");
row18.createCell(6).setCellValue(" 年 月 日 ");
row18.setHeight(rowHeight);
//第十九行
row19.createCell(0).setCellValue("注：1、本表适用总行部室申报呆账认定及对申报的呆账认定进行审查。\n" +
"2、原则上按每笔填写。如每笔申请呆账认定的经营责任人相同时，可批量申报及审查。\n" +
"3、如申报贷款的发放币种为其他币种时，请根据原发放币种调整申报的单位。\n" +
"4、该表内容框架可根据国家及行内政策制度、实际执行情况等进行适当调整。");
row19.setHeight((short) 1200);
//全局样式
for(int i = 0; i < 8; ++i) {
row14.getCell(i).setCellStyle(style);
row15.getCell(i).setCellStyle(style);
row16.getCell(i).setCellStyle(style);
row17.getCell(i).setCellStyle(style);
row18.getCell(i).setCellStyle(style);
row19.getCell(i).setCellStyle(style);
}
//自定义样式
row14.getCell(0).setCellStyle(centerStyle);
row15.getCell(0).setCellStyle(centerStyle);
row16.getCell(1).setCellStyle(centerStyle);
row16.getCell(3).setCellStyle(centerStyle);
row16.getCell(6).setCellStyle(centerStyle);
row16.getCell(1).setCellStyle(leftStyle);
row16.getCell(3).setCellStyle(leftStyle);
row16.getCell(6).setCellStyle(leftStyle);
row18.getCell(1).setCellStyle(centerStyle);
row18.getCell(3).setCellStyle(centerStyle);
row18.getCell(6).setCellStyle(centerStyle);
row18.getCell(1).setCellStyle(rightStyle);
row18.getCell(3).setCellStyle(rightStyle);
row18.getCell(6).setCellStyle(rightStyle);
row19.getCell(0).setCellStyle(leftStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(14, 14, 0, 7));
sheet.addMergedRegion(new CellRangeAddress(15, 15, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(15, 15, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(15, 15, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(16, 16, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(16, 16, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(16, 16, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(18, 18, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(18, 18, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(18, 18, 6, 7));
sheet.addMergedRegion(new CellRangeAddress(15, 18, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 7));
excelName = "HuiZongQingDan.xls";
exportWorkStatisUnnaturalHandle(excelUtil, loanWoWorkList, condition);
}
//获取本地文件存放目录
String localPath = ApSystemParm.getValue("FILE_TRANSFORM", "LOCALDIR");
String rootfilepath = BizUtil.createDir(localPath, runEnvs.getTrxn_code());
try {
File file = excelUtil.toFile(rootfilepath, excelName);
return BizUtil.uploadFile(runEnvs.getBusi_org_id(), runEnvs.getTrxn_code(), file);
} catch (Throwable e) {
throw ApErr.AP.E0030(excelName, e);
}
}
结果：
2209
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
