POI动态合并行和列
效果图
主要代码
package cn.com.gtmc.glaf2.framework.utils;
import org.apache.poi.hssf.usermodel.;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
/**
-
Copyright (C), 2015-2017, its
-
FileName: PoiExportAutoMergeRowAndColumn
-
Author: hdk
-
Date: 2020-10-28 0:00
-
Description: 基于poi动态导出合并头,合并列工具类,
-
mergeRow 合并行
-
genMergeCellByContent 合并头
-
History:
-
-
作者姓名 修改时间 版本号 描述
*/
public class PoiExportAutoMergeRowAndColumn {
private static final Logger logger = LoggerFactory.getLogger(PoiExportAutoMergeRowAndColumn.class);public static
void exportTest(String excelName, List list, LinkedHashMap<String, Object> fieldMap) { // 设置默认文件名为当前时间:年月日时分秒 if (excelName == null || excelName == "") { excelName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); } try { //创建一个WorkBook,对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); //在Workbook中,创建一个sheet,对应Excel中的工作薄(sheet) HSSFSheet sheet = wb.createSheet(excelName); //创建单元格,并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); //创建一个居中格式 //style.setAlignment(ALIGN_CENTER); style.setAlignment(HorizontalAlignment.CENTER); // 填充工作表 fillSheet(sheet, list, fieldMap, style); FileOutputStream dd = new FileOutputStream("c://test.xls"); //将文件输出 OutputStream ouputStream = dd; wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { logger.info("导出Excel失败!"); logger.error(e.getMessage()); }
}
/**
-
根据字段名获取字段对象
-
@param fieldName 字段名
-
@param clazz 包含该字段的类
-
@return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// logger.info("根据字段名获取字段对象:getFieldByName()");
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();// 如果本类中存在该字段,则返回
for (Field field : selfFields) {
//如果本类中存在该字段,则返回
if (field.getName().equals(fieldName)) {
return field;
}
}// 否则,查看父类中是否存在此字段,如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
//递归
return getFieldByName(fieldName, superClazz);
}// 如果本类和父类都没有,则返回空
return null;
}
/**
-
根据字段名获取字段值
-
@param fieldName 字段名
-
@param o 对象
-
@return 字段值
-
@throws Exception 异常
*/
public static Object getFieldValueByName(String fieldName, Object o)
throws Exception {if (o instanceof HashMap) {
return ((HashMap) o).get(fieldName);
}// logger.info("根据字段名获取字段值:getFieldValueByName()");
Object value = null;
//根据字段名得到字段对象
Field field = getFieldByName(fieldName, o.getClass());//如果该字段存在,则取出该字段的值
if (field != null) {
field.setAccessible(true);//类中的成员变量为private,在类外边使用属性值,故必须进行此操作
value = field.get(o);//获取当前对象中当前Field的value
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 "
+ fieldName);
}return value;
}
/**
-
根据带路径或不带路径的属性名获取属性值,即接受简单属性名,
-
如userName等,又接受带路径的属性名,如student.department.name等
-
@param fieldNameSequence 带路径的属性名或简单属性名
-
@param o 对象
-
@return 属性值
-
@throws Exception 异常
*/
public static Object getFieldValueByNameSequence(String fieldNameSequence,
Object o) throws Exception {
// logger.info("根据带路径或不带路径的属性名获取属性值,即接受简单属性名:getFieldValueByNameSequence()");
Object value = null;// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split("\.");
if (attributes.length == 1) {
value = getFieldValueByName(fieldNameSequence, o);
} else {
// 根据数组中第一个连接属性名获取连接属性对象,如student.department.name
Object fieldObj = getFieldValueByName(attributes[0], o);
//截取除第一个属性名之后的路径
String subFieldNameSequence = fieldNameSequence
.substring(fieldNameSequence.indexOf(".") + 1);
//递归得到最终的属性对象的值
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
-
向工作表中填充数据
-
@param sheet excel的工作表名称
-
@param list 数据源
-
@param fieldMap 中英文字段对应关系的Map
-
@param style 表格中的格式
-
@throws Exception 异常
*/
public staticvoid fillSheet(HSSFSheet sheet, List list,
LinkedHashMap<String, Object> fieldMap, HSSFCellStyle style) throws Exception {
// logger.info("向工作表中填充数据:fillSheet()");
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];// 填充数组
int count = 0;
for (Map.Entry<String, Object> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue().toString();
count++;
}
int firstRow = 0;
int lastRow = 3;
int firstCol = 0;
int lastCol = 0;//在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 填充表头
for (int i = 0; i < cnFields.length; i++) {
String val = cnFields[i];
HSSFCell cell = null;
String[] split = val.split("-");
if (split.length == 4) {
genMergeHeader(split, sheet, i, style);//生成合并列
continue;
}cell = row.createCell(i); cell.setCellValue(val); if (split.length != 4) { firstCol = i; lastCol = i; CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); sheet.addMergedRegion(cellRangeAddress); RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet); RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet); } HSSFFont font = sheet.getWorkbook().createFont(); style.setAlignment(HorizontalAlignment.CENTER); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12);// 设置字体大小 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setBorderBottom(BorderStyle.THIN); //下边框 style.setBorderLeft(BorderStyle.THIN);//左边框 style.setBorderTop(BorderStyle.THIN);//上边框 style.setBorderRight(BorderStyle.THIN);//右边框 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell.setCellStyle(style); sheet.setColumnWidth(i, val.length() * 1000); sheet.autoSizeColumn(i);
}
//todo合并单元格列头
genMergeCellByContent(0, 2, 6, 56, sheet);
// 填充内容
String[] arrayList = new String[enFields.length];
HashMap hashMap = new HashMap();for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 4);// 获取单个对象 T item = list.get(index); for (int i = 0; i < enFields.length; i++) { Object objValue = getFieldValueByNameSequence(enFields[i], item); String fieldValue = objValue == null ? "" : objValue.toString(); row.createCell(i).setCellValue(fieldValue); }
}
for (Object o : hashMap.keySet()) {
Integer i = (Integer) o;
List o1 = (ArrayList) hashMap.get(o);
CellRangeAddress cellRangeAddress = new CellRangeAddress((Integer) o1.get(0) + 3, (Integer) o1.get(o1.size() - 1) + 4, i, i);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
}
/**
-
根据内容合并单元格
-
@param firstRow
-
@param lastRow
-
@param firstCol
-
@param lastCol
-
@param sheet
*/
private static void genMergeCellByContent(int firstRow, int lastRow, int firstCol, int lastCol, HSSFSheet sheet) {class Range {
public String vlaue;
public int rowIndex;
public int firstCol;
public int lastCol;
}
String valueTemp = "";
int firstColTemp = 0;
int lastColTemp = 0;List
list = new ArrayList (); for (int i = firstRow; i <= lastRow; i++) {
Row row = sheet.getRow(i);
valueTemp = "";
for (int j = firstCol; j <= lastCol; j++) {
String stringCellValue = row.getCell(j).getStringCellValue();
if (valueTemp.equals(stringCellValue)) {
lastColTemp = j;
if (j == lastCol) {
Range range = new Range();
range.rowIndex = i;
range.vlaue = valueTemp;
range.firstCol = firstColTemp;
range.lastCol = lastColTemp;
list.add(range);
}
} else {
//找到合并单元信息。 存到list中
if (!valueTemp.equals("")) {
Range range = new Range();
range.rowIndex = i;
range.vlaue = valueTemp;
range.firstCol = firstColTemp;
range.lastCol = lastColTemp;
list.add(range);
}valueTemp = stringCellValue; firstColTemp = j; } }
}
for (Range range : list) {
sheet.addMergedRegion(new CellRangeAddress(range.rowIndex, range.rowIndex, range.firstCol, range.lastCol));
}
}
/**
-
合并单元格
-
@param split
*/
private static void genMergeHeader(String[] split, HSSFSheet sheet, int columnIndex, HSSFCellStyle style) {
for (int i = 0; i < split.length; i++) {HSSFRow row = null; row = sheet.getRow(i); if (row == null) { row = sheet.createRow(i); } HSSFCell cell = row.createCell(columnIndex); String s = split[i]; cell.setCellValue(s); HSSFFont font = sheet.getWorkbook().createFont(); style.setAlignment(HorizontalAlignment.CENTER); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12);// 设置字体大小 font.setBold(true); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(VerticalAlignment.CENTER); cell.setCellStyle(style); sheet.setColumnWidth(columnIndex, s.length() * 700); // sheet.autoSizeColumn(columnIndex);
}
}
public static
void exportHeader(String excelName, List list, LinkedHashMap<String, Object> fieldMap) { // 设置默认文件名为当前时间:年月日时分秒 if (excelName == null || excelName == "") { excelName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); } try { //创建一个WorkBook,对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); //在Workbook中,创建一个sheet,对应Excel中的工作薄(sheet) HSSFSheet sheet = wb.createSheet(excelName); //创建单元格,并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); //创建一个居中格式 style.setAlignment(HorizontalAlignment.CENTER); // 填充工作表 fillSheet(sheet, list, fieldMap, style); //合并行 mergeRow(sheet, (List<HashMap>)list); FileOutputStream dd = new FileOutputStream("D://out//test.xls"); //将文件输出 OutputStream ouputStream = dd; wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { System.out.println(e.getMessage()); }
}
/**
-
合并行
-
@param sheet
-
@param list
*/
private static void mergeRow(HSSFSheet sheet,Listlist){
//获取待合并单元格下标
List<MergeRowBuilder.ItemLink> genindexList = new MergeRowBuilder().BuidIndex(list);
//遍历集合进行合并,每个相邻单元格的信息都存储在一个链表里
for (MergeRowBuilder.ItemLink itemList : genindexList) {
for (MergeRowBuilder.Item o : itemList.listItem) {
Integer[] tempArr = new Integer[2];
MergeRowBuilder.getIndex(o, tempArr);//获取要和并的起始行和最后一行的下标
CellRangeAddress cellRangeAddress = new CellRangeAddress(tempArr[0]+4, tempArr[1]+4, o.getY(), o.getY());
sheet.addMergedRegionUnsafe(cellRangeAddress);}
}
}
public static void main(String[] args) {
LinkedHashMap<String, Object> headerHashMap = new LinkedHashMap<String, Object>(); LinkedHashMap<String, Object> hashMapSecond = new LinkedHashMap<String, Object>(); LinkedHashMap<String, Object> hashMapThird = new LinkedHashMap<String, Object>(); LinkedHashMap<String, Object> hashMapLeaf = new LinkedHashMap<String, Object>(); headerHashMap.put("序号", "序号"); headerHashMap.put("单位名称", "单位名称"); headerHashMap.put("单位类别", "单位类别"); headerHashMap.put("指标评分", "指标评分"); headerHashMap.put("百分制得分", "百分制得分"); headerHashMap.put("信息系统名称", "信息系统名称"); hashMapLeaf.put("防火(4)", ""); hashMapLeaf.put("问题", ""); hashMapLeaf.put("防雷(4)", "防火(4)"); hashMapLeaf.put("问题", ""); hashMapLeaf.put("场地(4)", "防火(4)"); hashMapLeaf.put("问题", ""); hashMapLeaf.put("滤空(4)", "防火(4)"); hashMapLeaf.put("问题", ""); hashMapLeaf.put("访问控制(4)", "防火(4)"); hashMapLeaf.put("问题", ""); hashMapThird.put("机房综合安全(20)", hashMapLeaf); hashMapSecond.put("信息安全建设(40)", hashMapThird); headerHashMap.put("检查评分细项", hashMapSecond); LinkedHashMap<String, Object> fieldMap = new LinkedHashMap<String, Object>(); fieldMap.put("a", "序号"); fieldMap.put("b", "单位名称"); fieldMap.put("c", "单位类别"); fieldMap.put("d", "指标评分"); fieldMap.put("e", "百分制得分"); fieldMap.put("f", "信息系统名称"); fieldMap.put("g6", "检查评分细项-信息安全建设(40)-机房综合安全(20)-防火(4)"); fieldMap.put("g7", "检查评分细项-信息安全建设(40)-机房综合安全(20)-问题"); fieldMap.put("g8", "检查评分细项-信息安全建设(40)-机房综合安全(20)-防雷(4)"); fieldMap.put("g9", "检查评分细项-信息安全建设(40)-机房综合安全(20)-问题"); fieldMap.put("g10", "检查评分细项-信息安全建设(40)-机房综合安全(20)-场地(4)"); fieldMap.put("g11", "检查评分细项-信息安全建设(40)-机房综合安全(20)-问题"); fieldMap.put("g12", "检查评分细项-信息安全建设(40)-机房综合安全(20)-滤空(4)"); fieldMap.put("g13", "检查评分细项-信息安全建设(40)-机房综合安全(20)-问题"); fieldMap.put("g14", "检查评分细项-信息安全建设(40)-机房综合安全(20)-访问控制(4)"); fieldMap.put("g15", "检查评分细项-信息安全建设(40)-机房综合安全(20)-问题"); fieldMap.put("g16", "检查评分细项-信息安全建设(40)-基础架构设施(15)-硬件设施(5)"); fieldMap.put("g17", "检查评分细项-信息安全建设(40)-基础架构设施(15)-问题"); fieldMap.put("g18", "检查评分细项-信息安全建设(40)-基础架构设施(15)-网络架构(5)"); fieldMap.put("g19", "检查评分细项-信息安全建设(40)-基础架构设施(15)-问题"); fieldMap.put("g20", "检查评分细项-信息安全建设(40)-基础架构设施(15)-安全保障(5)"); fieldMap.put("g21", "检查评分细项-信息安全建设(40)-基础架构设施(15)-问题"); fieldMap.put("g22", "检查评分细项-信息安全管理(20)-组织与工作(10)-信息安全主管领导(5)"); fieldMap.put("g23", "检查评分细项-信息安全管理(20)-组织与工作(10)-问题"); fieldMap.put("g24", "检查评分细项-信息安全管理(20)-组织与工作(10)-信息安全员(5)"); fieldMap.put("g25", "检查评分细项-信息安全管理(20)-组织与工作(10)-问题"); fieldMap.put("g26", "检查评分细项-信息安全管理(20)-安全与制度(10)-信息安全制度管理(5)"); fieldMap.put("g27", "检查评分细项-信息安全管理(20)-安全与制度(10)-问题"); fieldMap.put("g28", "检查评分细项-信息安全管理(20)-安全与制度(10)-岗位信息安全责任制度(5)"); fieldMap.put("g29", "检查评分细项-信息安全管理(20)-安全与制度(10)-问题"); fieldMap.put("g30", "检查评分细项-信息安全防护(40)-安全保护管理(15)-漏洞评测(15)"); fieldMap.put("g31", "检查评分细项-信息安全防护(40)-安全保护管理(15)-问题"); fieldMap.put("g32", "检查评分细项-信息安全防护(40)-信息安全应急(8)-信息安全应急预案(4)"); fieldMap.put("g33", "检查评分细项-信息安全防护(40)-信息安全应急(8)-问题"); fieldMap.put("g34", "检查评分细项-信息安全防护(40)-信息安全应急(8)-信息安全应急演练(4)"); fieldMap.put("g35", "检查评分细项-信息安全防护(40)-信息安全应急(8)-问题"); fieldMap.put("g36", "检查评分细项-信息安全防护(40)-Web应用系统(10)-身份验证(3)"); fieldMap.put("g37", "检查评分细项-信息安全防护(40)-Web应用系统(10)-问题"); fieldMap.put("g38", "检查评分细项-信息安全防护(40)-Web应用系统(10)-上传安全(3)"); fieldMap.put("g39", "检查评分细项-信息安全防护(40)-Web应用系统(10)-问题"); fieldMap.put("g40", "检查评分细项-信息安全防护(40)-Web应用系统(10)-抗拒绝服务(4)"); fieldMap.put("g41", "检查评分细项-信息安全防护(40)-Web应用系统(10)-问题"); fieldMap.put("g42", "检查评分细项-信息安全防护(40)-服务器操作系统(12)-Windows(6)"); fieldMap.put("g43", "检查评分细项-信息安全防护(40)-服务器操作系统(12)-问题"); fieldMap.put("g44", "检查评分细项-信息安全防护(40)-服务器操作系统(12)-Linux(6)"); fieldMap.put("g45", "检查评分细项-信息安全防护(40)-服务器操作系统(12)-问题"); fieldMap.put("g46", "检查评分细项-扣分指标- -安全事件(最多扣20分)"); fieldMap.put("g47", "检查评分细项-扣分指标- -问题"); fieldMap.put("g48", "检查评分细项-扣分指标- -媒体曝光(最多扣10分)"); fieldMap.put("g49", "检查评分细项-扣分指标- -问题"); fieldMap.put("g50", "检查评分细项-扣分指标- -材料报送效率(最多扣10分)"); fieldMap.put("g51", "检查评分细项-扣分指标- -问题"); fieldMap.put("g52", "检查评分细项-扣分指标- -开展信息安全等级保护工作(最多扣10分)"); fieldMap.put("g53", "检查评分细项-扣分指标- -问题"); fieldMap.put("g54", " -加分指标- -等保建设"); fieldMap.put("g55", " -加分指标- -流量分析"); fieldMap.put("g56", " -加分指标- -日志分析"); List<HashMap> list = new ArrayList<HashMap>(); HashMap hm = new HashMap(); hm.put("a", "#1"); hm.put("b", "专"); hm.put("c", "低积载"); hm.put("d", "4"); hm.put("e", "5"); hm.put("f", "6"); hm.put("g", "7"); hm.put("g1", "8"); HashMap hm11 = new HashMap(); hm11.put("a", "#1"); hm11.put("b", "专"); hm11.put("c", "高积载"); hm11.put("d", "4"); hm11.put("e", "5"); hm11.put("f", "6"); hm11.put("g", "7"); hm11.put("g1", "8"); HashMap hm1 = new HashMap(); hm1.put("a", "#1"); hm1.put("b", "共同"); hm1.put("c", "32"); hm1.put("d", "43"); hm1.put("e", "54"); hm1.put("f", "65"); hm1.put("g", "74"); hm1.put("g1", "83"); HashMap hm2 = new HashMap(); hm2.put("a", "#2"); hm2.put("b", "专"); hm2.put("c", "322"); hm2.put("d", "43a"); hm2.put("e", "54a"); hm2.put("f", "65a"); hm2.put("g", "74a"); hm2.put("g1", "83a"); HashMap hm3 = new HashMap(); hm3.put("a", "#2"); hm3.put("b", "共同"); hm3.put("c", "322"); hm3.put("d", "43a"); hm3.put("e", "54a"); hm3.put("f", "65a"); hm3.put("g", "74a"); hm3.put("g1", "83a"); HashMap hm4 = new HashMap(); hm4.put("a", "5"); hm4.put("b", "5"); hm4.put("c", "322"); hm4.put("d", "43a"); hm4.put("e", "54a"); hm4.put("f", "65a"); hm4.put("g", "74a"); hm4.put("g1", "83a"); list.add(hm); list.add(hm11); list.add(hm1); list.add(hm2); list.add(hm3); list.add(hm4); exportHeader("test", list, fieldMap);
}
-
}