Java 导出复杂格式 excel
- java 导出普通格式 excel
- 新思路创键模板，设置固定样式，替换值
- 采用模板形式，替换值
- 代码
- 衍生导出复杂表头，动态表体excel
java 导出普通格式 excel
java 导出 excel 网上方法很多，但只适合固定表头，表体没有单元格合并的excel，且比较麻烦，一般三行代码一个单元格；
例如：标题要加粗字体跟文本字体不一样，每一行单元格合并也不一样，
有些文本要剧中，有些为本左对齐等等
① 创建行；② 创建列；③设置样式；④设置值。如此方法网上很多 ，请您移驾百度
新思路创键模板，设置固定样式，替换值
要导出如图表格
采用模板形式，替换值
模板如图
代码
生成工具包
/**
* 替换Excel模板文件内容
*
* @param map 文档数据
* @param sourceFilePath Excel模板文件路径
* @param targetFilePath Excel生成文件路径
*/
public static void replaceModel(Map<String, String> map, String sourceFilePath, String targetFilePath) {
try (POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(sourceFilePath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
// 输出文件
FileOutputStream fileOut = new FileOutputStream(targetFilePath);
) {
HSSFSheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
//获取单元格内容
HSSFRow row = sheet.getRow(i);
int cells = row.getPhysicalNumberOfCells();
for (int cenum = 0; cenum < cells; cenum++) {
HSSFCell cell = row.getCell((short) cenum);
if (cell != null) {
cell.setCellType(CellType.STRING);
String str = cell.getStringCellValue();
// 去掉空格
String key = str.trim();
if (map.get(key) != null)
cell.setCellValue(map.get(key));
}
}
}
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
throw new RRException("生成 excel 失败");
}
}测试
public static void main(String[] args) {
Map<String, String> map = new HashMap<>();
map.put("title", "关于xx申请xxx个人xxx的调查表");
map.put("time", "日期：" + DateUtil.format(new Date(), DateUtil.strFormat2));
map.put("deptName", "支行（部）:奔跑的小番茄测试部门");
map.put("applyAmt", "720000元");
map.put("applyTerm", "36个月");
map.put("loanUse", "日常消费");
map.put("custName", "张三");
map.put("certNo", "966866199506154875");
map.put("custPhone", "1855988373");
map.put("maritalStatus", "已婚");
map.put("spouseCustName", "里斯");
map.put("spouseCertno", "33662259988");
map.put("spousePhone", "19666866329");
map.put("InfoAddress", "武xxxxx镇xxxxx号");
map.put("creditInfo", "申请xxx在其他3家⾦融机构有信⽤卡1.0张，⽬前⽤信10000.0元，无不良记录。\n" +
"申请⼈配偶xxx在其他3家⾦融机构有信⽤卡16.0张，⽬前⽤信215.0元，无不良记录。\n" +
"申请人征信无贷款记录。");
map.put("riskInfo", "申请人xxx风控系统评分为21.00分:\n" +
"指标1 明细：3个月内申请信息关联多个身份证 分数: 6;申请人信息命中低风险关注名单 分数: 2;\n" +
"申请人配偶xxxx风控系统评分为19.00分:\n" +
"指标1 明细：3个月内身份证关联多个申请信息 分数: 12;3个月内申请信息关联多个身份证 分数: 24;");
map.put("comonType", "单独所有");
map.put("rightnature", "私有");
map.put("immovaNo", "1329");
map.put("righttype", "你猜");
map.put("use", "生产经营");
map.put("area", "1205");
map.put("evaluationPrice", "19666元");
map.put("address", "xxxx");
map.put("b002", "否");
map.put("b003", "否");
map.put("b004", "否");
map.put("b005", "否");
map.put("b006", "否");
map.put("b007", "否");
replaceModel(map, "D:\\demo.xls", "D:\\reple888.xls");
}包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>找到输出文件 已完美生成，且样式跟模板样式一致，代码简化很多
注意事项
- 以上代码只支持 .xls 结尾的excel，因为模板是固定的没必要做适配
- 如果是 .xlsx 结尾的 excel ; 使用 Workbook wb = new XSSFWorkbook(fs)
- 最后一步 拿到生成文件流，如果不由生成到服务器，下载请自行优化
需要包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>衍生导出复杂表头，动态表体excel
要生成如下表格
模板
代码
public static void replaceModel(Map<String, String> map,List<Map<String, String>> list, String sourceFilePath, String targetFilePath) {
try (POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(sourceFilePath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
// 输出文件
FileOutputStream fileOut = new FileOutputStream(targetFilePath);
) {
HSSFSheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
//获取单元格内容 读取表头
HSSFRow row = sheet.getRow(i);
int cells = row.getPhysicalNumberOfCells();
for (int cenum = 0; cenum < cells; cenum++) {
HSSFCell cell = row.getCell((short) cenum);
if (cell != null) {
cell.setCellType(CellType.STRING);
String str = cell.getStringCellValue();
// 去掉空格
String key = str.trim();
if (map.get(key) != null)
cell.setCellValue(map.get(key));
}
}
}
HSSFCellStyle heji = ExportExcelUtil.createCellStyle(wb, (short) 16, true, true);
HSSFCellStyle head = ExportExcelUtil.createCellStyle(wb, (short) 13, false, true);
for (int i = 0; i < list.size(); i++) {
Map<String, String> map1 = list.get(i);
// 计算表体 从第四行开始追加
// 创建行
HSSFRow row = sheet.createRow(4+i);
// 设置行高
row.setHeight((short) 500);
// 创建列
HSSFCell cell0 = row.createCell((short) 0);
// 设置样式
cell0.setCellStyle(head);
// 设置值
cell0.setCellValue(i);
HSSFCell cell1 = row.createCell((short) 1);
cell1.setCellValue(map1.get("deptName"));
cell1.setCellStyle(head);
HSSFCell cell2 = row.createCell((short) 2);
cell2.setCellValue(map1.get("userNum"));
cell2.setCellStyle(head);
HSSFCell cell3 = row.createCell((short) 3);
cell3.setCellValue(map1.get("userName"));
cell3.setCellStyle(head);
HSSFCell cell4 = row.createCell((short) 4);
cell4.setCellValue(map1.get("no"));
cell4.setCellStyle(head);
HSSFCell cell5 = row.createCell((short) 5);
cell5.setCellValue(map1.get("amt"));
cell5.setCellStyle(head);
HSSFCell cell6 = row.createCell((short) 6);
cell6.setCellValue(map1.get("no2"));
cell6.setCellStyle(head);
HSSFCell cell7 = row.createCell((short) 7);
cell7.setCellValue(map1.get("amt2"));
cell7.setCellStyle(head);
HSSFCell cell8 = row.createCell((short) 8);
cell8.setCellValue(map1.get("rate"));
cell8.setCellStyle(head);
HSSFCell cell9 = row.createCell((short) 9);
cell9.setCellValue(map1.get("rateYear"));
cell9.setCellStyle(head);
HSSFCell cell10 = row.createCell((short) 10);
cell10.setCellValue(map1.get("desc"));
cell10.setCellStyle(head);
}
CellRangeAddress callRangeAddress = new CellRangeAddress(4+list.size(), 4+list.size(), 0, 2);
sheet.addMergedRegion(callRangeAddress);
// 最后合计 合计
HSSFRow row = sheet.createRow(4+list.size());
row.setHeight((short) 800);
HSSFCell cell0 = row.createCell((short) 0);
cell0.setCellStyle(heji);
cell0.setCellValue("合计");
HSSFCell cell3 = row.createCell((short) 3);
cell3.setCellValue("56");
cell3.setCellStyle(head);
HSSFCell cell4 = row.createCell((short) 4);
cell4.setCellValue("65");
cell4.setCellStyle(head);
HSSFCell cell5 = row.createCell((short) 5);
cell5.setCellValue("58");
cell5.setCellStyle(head);
HSSFCell cell6 = row.createCell((short) 6);
cell6.setCellValue(85);
cell6.setCellStyle(head);
HSSFCell cell7 = row.createCell((short) 7);
cell7.setCellValue(69);
cell7.setCellStyle(head);
HSSFCell cell8 = row.createCell((short) 8);
cell8.setCellValue(20);
cell8.setCellStyle(head);
HSSFCell cell9 = row.createCell((short) 9);
cell9.setCellValue("20");
cell9.setCellStyle(head);
HSSFCell cell10 = row.createCell((short) 10);
cell10.setCellValue("统计");
cell10.setCellStyle(head);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
throw new RRException("生成 excel 失败");
}
}
/**
* 测试数据
*/
public static void main(String[] args) {
Map<String, String> map = new HashMap<>();
map.put("time","日期：2020年8月17日");
map.put("componty","单位：万元、户");
List<Map<String, String>> list = new ArrayList<>();
Map<String, String> data1 = new HashMap<>();
data1.put("deptName","支行1");
data1.put("userNum","10");
data1.put("userName","张三");
data1.put("no","12");
data1.put("amt","1524");
data1.put("no2","0");
data1.put("amt2","支行1");
data1.put("rate","18%");
data1.put("rateYear","支行1");
data1.put("desc","你猜");
list.add(data1);
replaceModel(map, list,"D:\\test.xls", "D:\\te888555.xls");
}
public class ExportExcelUtil {
/**
* @param workbook
* @param fontsize
* @return 单元格样式
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize, boolean flag, boolean flag1) {
HSSFCellStyle style = workbook.createCellStyle();
//是否水平居中
if (flag1) {
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
}
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
if (flag) {
font.setBold(true);
}
font.setFontHeightInPoints(fontsize);
font.setFontName("宋体");
//加载字体
style.setFont(font);
return style;
}
}总结：
- java 导出excel 纯代码很繁琐，用模板加代码创建会方便横夺
- 如果表头表体都没有复杂样式及单元格合并，建议使用纯代码生成
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
