动态多级表头导出
一. 实体类
1. 表格类
包含表名，表头，表数据，分页，汇总数据（用于前端汇总，也可不要），展示形式（这里分为万元展示和元展示）
@Data
public class CustomTable {
private String tableName;
private List<TableHeader> headers;
private List<TableData> dataList;
private CustomTablePage page;
private List<String> totalStr;
/**
* 0 元 1 万元
*/
private Integer numType = 0;
}
2. 表头类
包含key值（用于区分可能出现相同表头名字），表头名，子集（用于多级表头），和一些个性参数
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel("表格")
public class TableHeader {
@TableAnnotation(value = "键")
@ApiModelProperty(value = "表头键")
private String key;
private String headerName;
private List<TableHeader> children;
/**
* 0 中文描述 1 数字
*/
private Integer type = 0;
/**
* 是否显示:默认true，false 不显示
*/
private Boolean isDisplay = true;
}
3. 表数据类
这里以键值对形式储存，一个tabelData 代表着一行，一个key代表一列
@Data
public class TableData {
private Map<String,Object> data;
}
4. 分页类
此类是为了后端做分页用的，导出时用不到
为什么需要单独用个类呢？是为了不在sql中分组
@Data
public class CustomTablePage {
private Integer size;
private Integer num;
private Integer total;
}
二. 导出方法
外部调用的方法，关键在于getTable(model, name)这个方法，这里以三种形式来实现
public static void summaryTableExport(CustomTable model, HttpServletResponse response, String name) {
// CustomTable model1 = new CustomTable();
// List<PaymentManagementSummaryInquiryVo> vos = getTestData();
// List<TableHeader> header = getHeader(vos);
// model1.setHeaders(header);
// List<TableData> tableData = getTableData(vos);
// model1.setDataList(tableData);
OutputStream outputStream = null;
try {
// 在此处创建wk，是excel的文档对象，用于接收service层处理后的数据；
HSSFWorkbook wk = new HSSFWorkbook();
// 创建一个查询参数对象，此对象代码如下UserHolidyParam 代码所示；
// 调用service层的进一步处理方法，将查询参数对象以及response返回对象传递过去
wk = getTable(model, name);
// 给生成的Excel表格命名
String str = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
String fileName = URLEncoder.encode(name + ".xls", "utf-8");
// response.setCharacterEncoding("UTF-8");
// response.setContentType("application/json");
// response.setHeader("Content-disposition", "attachment;filename=" + new String(name.getBytes(),"iso-8859-1"));
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
// 默认Excel名称
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "UTF-8"));
// 将返回对象中的需要输出的数据取出
outputStream = response.getOutputStream();
// 使用write方法写入到Excel对象中去
wk.write(outputStream);
// 关闭Excel对象
wk.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
一些前置的方法
1.获得表头样式
/**
* @param cellStyle
* @param type1 1 表头，2内容
* @param type2 -1：无样式（靠右）0:无样式(靠左) 1普通表头样式（灰色，有边框）2黄色表头样式（黄色有边框）3橙色表头样式（橙色有边框）4黄色内容样式（黄色无边框）5橙色内容样式（橙色无边框）
* @return
*/
public static HSSFCellStyle getStyle(HSSFCellStyle cellStyle, Integer type1, Integer type2) {
// 垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
switch (type1) {
case 1:
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
switch (type2) {
case 1:
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
break;
case 2:
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
break;
case 3:
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
break;
default:
break;
}
break;
case 2:
//水平靠右
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
// 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
switch (type2) {
case 4:
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
break;
case 5:
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
break;
default:
break;
}
break;
default:
switch (type2) {
case 0:
//水平居右
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
break;
default:
//水平居左
cellStyle.setAlignment(HorizontalAlignment.LEFT);
break;
}
break;
}
return cellStyle;
}
2.获得每列的最小表头（这里采用最原始的，后面会有使用递归方式的）
public static List<String> getHeaderNo(List<TableHeader> headers) {
List<String> ss = new ArrayList<>();
for (TableHeader t : headers) {
if (!t.getIsDisplay()){
continue;
}
if (null != t.getChildren()) {
List<TableHeader> children = t.getChildren();
for (TableHeader child : children) {
if (null != child.getChildren()) {
List<TableHeader> children1 = child.getChildren();
for (TableHeader tableHeader : children1) {
if (null != tableHeader.getChildren()){
List<TableHeader> children2 = tableHeader.getChildren();
for (TableHeader header : children2) {
ss.add(header.getKey());
}
}else {
ss.add(tableHeader.getKey());
}
}
} else {
ss.add(child.getKey());
}
}
} else {
ss.add(t.getKey());
}
}
return ss;
}
3.将表头拼成一个字符串（用于下面绘制表格）
public static String headerToString(List<TableHeader> headers) {
String s = "";
for (TableHeader header : headers) {
if (!header.getIsDisplay()){
continue;
}
s += header.getHeaderName();
if (null != header.getChildren()) {
List<TableHeader> children = header.getChildren();
s += ":";
for (TableHeader child : children) {
s += child.getHeaderName();
if (null != child.getChildren()) {
//三级
s += "=";
List<TableHeader> children1 = child.getChildren();
for (TableHeader tableHeader : children1) {
s += tableHeader.getHeaderName() + "@";
}
s = MyStringUtil.delEndFindString(s, "@");
s += "_";
} else {
//只有二级
s += "_";
}
}
s = MyStringUtil.delEndFindString(s, "_");
s += ",";
} else {
//只有一级
s += ",";
}
}
s = MyStringUtil.delEndFindString(s, ",");
return s;
}
方法一：
优点：测试最多，适用与大多数场景
缺点： 仅能1,2,3级动态表头，且表头不能为一些特殊字符串
public static HSSFWorkbook getTable(CustomTable model, String name) {
HSSFWorkbook wb = new HSSFWorkbook();
//无样式靠左
HSSFCellStyle cellStyle6 = getStyle(wb.createCellStyle(), 0, -1);
//无样式靠右
HSSFCellStyle cellStyle0 = getStyle(wb.createCellStyle(), 0, 0);
//灰色表头
HSSFCellStyle cellStyle = getStyle(wb.createCellStyle(), 1, 1);
//黄色表头
HSSFCellStyle cellStyle2 = getStyle(wb.createCellStyle(), 1, 2);
//橙色表头
HSSFCellStyle cellStyle3 = getStyle(wb.createCellStyle(), 1, 3);
//黄色内容
HSSFCellStyle cellStyle4 = getStyle(wb.createCellStyle(), 2, 4);
//橙色内容
HSSFCellStyle cellStyle5 = getStyle(wb.createCellStyle(), 2, 5);
//创建sheet
HSSFSheet sheet = wb.createSheet(name);
// sheet.setColumnWidth(0, 3766);
// sheet.setDefaultColumnWidth(22);
// sheet.setDefaultRowHeightInPoints(22);
List<String> headerNo = getHeaderNo(model.getHeaders());
HSSFRow row1 = sheet.createRow(0);
HSSFRow row2 = sheet.createRow(1);
HSSFRow row3 = sheet.createRow(2);
String titleString = headerToString(model.getHeaders());
String[] headers = titleString.split(",");
//i是headers的索引，n是Excel的索引
for (short i = 0, n = 0; i < headers.length; i++) {
HSSFCell cellT = row1.createCell(n);
HSSFRichTextString text = null;
//有2级标题
if (headers[i].contains(":")) {
//有3级标题 TA:TA1=TA11@TA12_TA2
if (headers[i].contains("=")) {
//确定3级标题的个数，确定1级标题的总长度，同时也是把各级标题分解开 //分级1级标题temp[0]是标题文字，temp[1]是子标题
String[] temp = headers[i].split(":");
text = new HSSFRichTextString(temp[0]);
//获取temp2级标题的数组
String[] childlv2 = temp[1].split("_");
cellT.setCellStyle(cellStyle);
int ttlength = 0;
short row2index = n;
short row3index = n;
//循环计算全部的2级标题对应的子标题总数
for (int k = 0; k < childlv2.length; k++) {
//取到2级标题的第一个
String childlv2_1 = childlv2[k];
HSSFRichTextString textLV2 = null;
//根据n的index进行循环
HSSFCell cellChildlv2 = row2.createCell(n);
cellChildlv2.setCellStyle(cellStyle);
//一层层的向下取，取到3级，并向下进行补充
if (childlv2_1.contains("=")) {//2级子节点，有3级子节点
String[] childlv2_all = childlv2_1.split("=");
textLV2 = new HSSFRichTextString(childlv2_all[0]);
String childlv3_1 = childlv2_all[1];
//这里说明2级子节点有多个3级子节点，那么2级子节点就需要合并，同时为
if (childlv3_1.contains("@")) {
String[] childlv3_all = childlv3_1.split("@");
ttlength = ttlength + childlv3_all.length;
//这里进行2级节点的合并，因为有多个
sheet.addMergedRegion(new CellRangeAddress(1, 1, (short) n, (short) (n + childlv3_all.length - 1)));
//开始写3级节点
for (String childlv3Text : childlv3_all) {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if ("小计".equals(childlv3Text)) {
cellStyle1 = cellStyle3;
} else {
cellStyle1 = cellStyle;
}
row1.createCell(n).setCellStyle(cellStyle);
HSSFCell cellChildlv3 = row3.createCell(row3index++);
cellChildlv3.setCellStyle(cellStyle1);
cellChildlv3.setCellValue(new HSSFRichTextString(childlv3Text));
//进行EXCEL索引叠加
n++;
}
//补充2级节点的空cell
for (int x = 0; x < childlv3_all.length - 1; x++) {
HSSFCell cellChildlv2Blank = row2.createCell(++row2index);
cellChildlv2Blank.setCellStyle(cellStyle);
}
} else {
//这里说明2级子节点只有一个3级子节点，那么就不用合并和补充空格啦
ttlength = ttlength + 1;
//写入3级节点的cell
HSSFCell cellChildlv3 = row3.createCell(row3index++);
cellChildlv3.setCellStyle(cellStyle);
cellChildlv3.setCellValue(new HSSFRichTextString(childlv3_1));
n++;//Excel索引节点的递增
}
} else {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if (childlv2_1.contains("小计")) {
cellStyle1 = cellStyle3;
cellChildlv2.setCellStyle(cellStyle1);
} else {
cellStyle1 = cellStyle;
}
//2级子几点没有3及子节点
textLV2 = new HSSFRichTextString(childlv2_1);
ttlength = ttlength + 1;
row2.createCell(n).setCellStyle(cellStyle1);
row3.createCell(n).setCellStyle(cellStyle1);
//这个2级节点没有子节点，那么就要合并3row
sheet.addMergedRegion(new CellRangeAddress(1, 2, row3index, row3index));
// 补充3row的cell空格
HSSFCell cellChildlv3Blank = row3.createCell(row3index++);
cellChildlv3Blank.setCellStyle(cellStyle1);
//进行Excel的索引递增，避免写到一个格子里面去
n++;
}
cellChildlv2.setCellValue(textLV2);
}
//进行3层总长度的cell合并
sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) (n - ttlength), (short) (n - 1)));
//插入第一行的补充的空格
short tr1 = n;
//循环补充父标题的空格,因为已经定义啦一个cell所以要减1
// for(int j = 0; j < ttlength -1; j++){
//因为开始已经定义啦一个cell所以就是 ++tr1
// HSSFCell cellTitleBlank = row1.createCell(++tr1);
// cellTitleBlank.setCellStyle(cellStyle);
// }
} else {
//只有2级标题//子标题的分割
String[] temp = headers[i].split(":");
text = new HSSFRichTextString(temp[0]);
String[] childlv2 = temp[1].split("_");
cellT.setCellStyle(cellStyle);
//只有2及标题，那么1级标题要占1行，2级标题占2行
sheet.addMergedRegion(new CellRangeAddress(0, 0, n, (short) (n + childlv2.length - 1)));
//2级标题占两行所以需要和3行合并
for (int o = n; o < n + childlv2.length; o++) {
sheet.addMergedRegion(new CellRangeAddress(1, 2, o, (short) o));
}
short tr1 = n;
short tr2 = n;
//对对应的空行进行补充,第一行
//循环补充父标题的空格,因为已经定义啦一个cell所以要减1
for (int j = 0; j < childlv2.length - 1; j++) {
//因为开始已经定义啦一个cell所以就是 ++tr1
HSSFCell cellTitleBlank = row1.createCell(++tr1);
cellTitleBlank.setCellStyle(cellStyle);
}
//对第二行进行补充空格，从头开始 //未定义cell，所以不减1
for (int k = 0; k < childlv2.length; k++) {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if ("小计".equals(childlv2[k])) {
cellStyle1 = cellStyle3;
} else {
cellStyle1 = cellStyle;
}
//之前未进行定义，所以是tr2++
HSSFCell cellTitleBlank = row2.createCell(tr2++);
cellTitleBlank.setCellStyle(cellStyle1);
row2.createCell(n).setCellStyle(cellStyle1);
row3.createCell(n).setCellStyle(cellStyle1);
cellTitleBlank.setCellValue(new HSSFRichTextString(childlv2[k]));
//这里进行啦EXCEL的索引递增，不然会都写到一个格子里面去
n++;
}
}
} else {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if ("合计（实收-退款）".equals(headers[i])) {
cellStyle1 = cellStyle2;
} else {
cellStyle1 = cellStyle;
}
//只有1级标题
text = new HSSFRichTextString(headers[i]);
//没有子标题的时候自己独占两行
row1.createCell(n).setCellStyle(cellStyle1);
row2.createCell(n).setCellStyle(cellStyle1);
row3.createCell(n).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(0, 2, n, n));
cellT.setCellStyle(cellStyle1);
n++;
}
// cellT.setCellStyle(cellStyle);
cellT.setCellValue(text);
}
sheet.autoSizeColumn((short) 0);
sheet.autoSizeColumn((short) 1);
sheet.autoSizeColumn((short) 2);
List<TableData> dataList1 = model.getDataList();
List<String> totalStr = model.getTotalStr();
if (totalStr != null) {
if (headerNo.size() == totalStr.size()) {
TableData tableData = new TableData();
Map<String, Object> map = new HashMap<>(10);
for (int i = 0; i < totalStr.size(); i++) {
map.put(headerNo.get(i), totalStr.get(i));
}
tableData.setData(map);
dataList1.add(tableData);
}
}
for (int i = 0; i < dataList1.size(); i++) {
HSSFRow row = sheet.createRow(3 + i);
TableData tableData = dataList1.get(i);
Map<String, Object> data = tableData.getData();
for (int j = 0; j < headerNo.size(); j++) {
for (String s : data.keySet()) {
HSSFCell cell = row.createCell(j);
// HSSFCellStyle cellStyle1 = wb.createCellStyle();
if (headerNo.get(j).contains("小计")) {
// cellStyle1 = cellStyle5;
cell.setCellStyle(cellStyle5);
} else if ("合计（实收-退款）".equals(headerNo.get(j))) {
// cellStyle1 = cellStyle4;
cell.setCellStyle(cellStyle4);
} else {
// cellStyle1 = cellStyle0;
cell.setCellStyle(cellStyle0);
}
if (headerNo.get(j).equals(s)) {
System.out.println("s = " + data.get(s));
Boolean b = false;
for (String fixedDetailHeader : FIXED_DETAIL_HEADERS) {
if (fixedDetailHeader.equals(s)) {
b = true;
break;
}
}
if (!b && !s.contains("小计")) {
b = !MyStringUtil.isNumeric3(data.get(s).toString());
}
if (b) {
// cellStyle1 = cellStyle6;
cell.setCellStyle(cellStyle6);
cell.setCellValue(data.get(s).toString());
} else {
if (null != model.getNumType() && model.getNumType() == 1){
cell.setCellValue(MyStringUtil.parseNumberToTenThousand(data.get(s).toString()));
}else {
cell.setCellValue(MyStringUtil.parseNumber(data.get(s).toString()));
}
}
break;
}
}
// cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
}
}
// setSizeColumn(sheet, headerNo.size());
// for (int i = 0; i < model.getDataList().size(); i++) {
// HSSFRow row = sheet.createRow(3+i);
// for (int j = 0; j < 9; j++) {
// row.createCell(j).setCellValue(i);
// }
// }
for (int k = 0; k < headerNo.size(); k++) {
if (headerNo.get(k).length() > 6) {
sheet.setColumnWidth(k, headerNo.get(k).length() * 256 + 256 * 14);
} else {
sheet.setColumnWidth(k, 3766);
}
}
return wb;
}
方法二
优点：可以更多级，换了种绘制表格方式，更清晰些
缺点：测试的少，还没优化完善，只能说能用
这个方法还需要一个新增一个方法getPropertyDes(model.getHeaders());（在此方法下）
public static HSSFWorkbook getTable2(CustomTable model,String name){
String[][] propertyDes = getPropertyDes(model.getHeaders());
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(name);
//无样式靠左
HSSFCellStyle cellStyle6 = getStyle(wb.createCellStyle(), 0, -1);
//无样式靠右
HSSFCellStyle cellStyle0 = getStyle(wb.createCellStyle(), 0, 0);
//灰色表头
HSSFCellStyle cellStyle = getStyle(wb.createCellStyle(), 1, 1);
//黄色表头
HSSFCellStyle cellStyle2 = getStyle(wb.createCellStyle(), 1, 2);
//橙色表头
HSSFCellStyle cellStyle3 = getStyle(wb.createCellStyle(), 1, 3);
//黄色内容
HSSFCellStyle cellStyle4 = getStyle(wb.createCellStyle(), 2, 4);
//橙色内容
HSSFCellStyle cellStyle5 = getStyle(wb.createCellStyle(), 2, 5);
HSSFCellStyle style = getStyle(wb.createCellStyle(), 0, -1);
int mergerNum = 0 ; //合并数
//给单元格设置值
for(int i=0; i< propertyDes.length; i++){
HSSFRow row = sheet.createRow(i);
row.setHeight((short)700);
for(int j=0; j<propertyDes[i].length; j++){
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(propertyDes[i][j]);
}
}
Map<Integer,List<Integer>> map = new HashMap<Integer, List<Integer>>(); // 合并行时要跳过的行列
//合并列
for(int i=0; i<propertyDes[propertyDes.length-1].length; i++){
if("".equals(propertyDes[propertyDes.length-1][i])){
for(int j=propertyDes.length-2; j >=0 ;j--){
if(!"".equals(propertyDes[j][i])){
sheet.addMergedRegion(new CellRangeAddress(j,propertyDes.length-1,i,i)); // 合并单元格
break;
}else{
if(map.containsKey(j)){
List<Integer> list = map.get(j);
list.add(i);
map.put(j, list);
}else{
List<Integer> list = new ArrayList<Integer>();
list.add(i);
map.put(j, list);
}
}
}
}
}
//合并行
for(int i=0; i< propertyDes.length-1; i++){
for(int j=0; j<propertyDes[i].length; j++){
List<Integer> list = map.get(i);
if(list==null ||(list!=null&&!list.contains(j))){
if("".equals(propertyDes[i][j])){
mergerNum++ ;
if(mergerNum != 0 && j == (propertyDes[i].length-1)){
sheet.addMergedRegion(new CellRangeAddress(i,i,j-mergerNum,j)); // 合并单元格
mergerNum = 0 ;
}
}else {
if(mergerNum != 0){
sheet.addMergedRegion(new CellRangeAddress(i,i,j-mergerNum-1,j-1)); // 合并单元格
mergerNum = 0 ;
}
}
}
}
}
List<String> headerNo = getHeaderNo(model.getHeaders());
List<TableData> dataList1 = model.getDataList();
List<String> totalStr = model.getTotalStr();
if (totalStr != null) {
if (headerNo.size() == totalStr.size()) {
TableData tableData = new TableData();
Map<String, Object> map2 = new HashMap<>(10);
for (int i = 0; i < totalStr.size(); i++) {
map2.put(headerNo.get(i), totalStr.get(i));
}
tableData.setData(map2);
dataList1.add(tableData);
}
}
for (int i = 0; i < dataList1.size(); i++) {
HSSFRow row = sheet.createRow(4 + i);
TableData tableData = dataList1.get(i);
Map<String, Object> data = tableData.getData();
for (int j = 0; j < headerNo.size(); j++) {
for (String s : data.keySet()) {
HSSFCell cell = row.createCell(j);
// HSSFCellStyle cellStyle1 = wb.createCellStyle();
if (headerNo.get(j).contains("小计")) {
// cellStyle1 = cellStyle5;
cell.setCellStyle(cellStyle5);
} else if ("合计（实收-退款）".equals(headerNo.get(j))) {
// cellStyle1 = cellStyle4;
cell.setCellStyle(cellStyle4);
} else {
// cellStyle1 = cellStyle0;
cell.setCellStyle(cellStyle0);
}
if (headerNo.get(j).equals(s)) {
System.out.println("s = " + data.get(s));
Boolean b = false;
for (String fixedDetailHeader : FIXED_DETAIL_HEADERS) {
if (fixedDetailHeader.equals(s)) {
b = true;
break;
}
}
if (!b && !s.contains("小计")) {
b = !MyStringUtil.isNumeric3(data.get(s).toString());
}
if (b) {
// cellStyle1 = cellStyle6;
cell.setCellStyle(cellStyle6);
cell.setCellValue(data.get(s).toString());
} else {
if (model.getNumType() == 1){
cell.setCellValue(MyStringUtil.parseNumberToTenThousand(data.get(s).toString()));
}else {
cell.setCellValue(MyStringUtil.parseNumber(data.get(s).toString()));
}
}
break;
}
}
// cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
}
}
// setSizeColumn(sheet, headerNo.size());
// for (int i = 0; i < model.getDataList().size(); i++) {
// HSSFRow row = sheet.createRow(3+i);
// for (int j = 0; j < 9; j++) {
// row.createCell(j).setCellValue(i);
// }
// }
for (int k = 0; k < headerNo.size(); k++) {
if (headerNo.get(k).length() > 6) {
sheet.setColumnWidth(k, headerNo.get(k).length() * 256 + 256 * 14);
} else {
sheet.setColumnWidth(k, 3766);
}
}
return wb;
}
生成二维数组方法，这里就是把多级表头转为 二维数组，代表每行每列（此方法下升级版方法才能实现更高级）
public static String[][] getPropertyDes(List<TableHeader> headers){
List<List<String>> list = new ArrayList<>();
for (int i = 0; i < 4; i++) {
List<String> list1 = new ArrayList<>();
list.add(list1);
}
for (int i = 0; i < headers.size(); i++) {
TableHeader tableHeader = headers.get(i);
if (null != tableHeader.getChildren()){
list.get(0).add(tableHeader.getHeaderName());
List<TableHeader> children = tableHeader.getChildren();
for (int j = 0; j < children.size(); j++) {
TableHeader tableHeader1 = children.get(j);
if (null != tableHeader1.getChildren()){
list.get(1).add(tableHeader1.getHeaderName());
List<TableHeader> children1 = tableHeader1.getChildren();
for (int k = 0; k < children1.size(); k++) {
TableHeader tableHeader2 = children1.get(k);
if (null != tableHeader2.getChildren()){
List<TableHeader> children2 = tableHeader2.getChildren();
list.get(2).add(tableHeader2.getHeaderName());
for (int l = 0; l < children2.size(); l++) {
TableHeader tableHeader3 = children2.get(l);
if (l == 0){
list.get(3).add(tableHeader3.getHeaderName());
}else {
list.get(0).add("");
list.get(1).add("");
list.get(2).add("");
list.get(3).add(tableHeader3.getHeaderName());
}
}
}else {
//只有三级
if (k == 0){
list.get(2).add(tableHeader2.getHeaderName());
list.get(3).add("");
}else {
list.get(0).add("");
list.get(1).add("");
list.get(2).add(tableHeader2.getHeaderName());
list.get(3).add("");
}
}
}
}else {
//只有两级
if(j == 0){
list.get(1).add(tableHeader1.getHeaderName());
list.get(2).add("");
list.get(3).add("");
}else {
list.get(0).add("");
list.get(1).add(tableHeader1.getHeaderName());
list.get(2).add("");
list.get(3).add("");
}
}
}
}else {
//只是一级
list.get(0).add(tableHeader.getHeaderName());
list.get(1).add("");
list.get(2).add("");
list.get(3).add("");
}
}
for (List<String> strings : list) {
System.out.println(strings);
System.out.println("\t");
}
String[][] propertyDes = new String[list.size()][list.get(0).size()];
for (int i = 0; i < list.size(); i++) {
List<String> strings = list.get(i);
for (int j = 0; j < strings.size(); j++) {
propertyDes[i][j] = strings.get(j);
}
}
return propertyDes;
}
生成二维数据方法（高级）
public static String[][] getPropertyDesByRecursion(List<TableHeader> headers){
List<List<String>> list = new ArrayList<>();
setList(list,headers,0);
for (List<String> strings : list) {
System.out.println(strings);
System.out.println("\t");
}
String[][] propertyDes = new String[list.size()][list.get(0).size()];
for (int i = 0; i < list.size(); i++) {
List<String> strings = list.get(i);
for (int j = 0; j < strings.size(); j++) {
propertyDes[i][j] = strings.get(j);
}
}
return propertyDes;
}
public static void setList(List<List<String>> list,List<TableHeader> headers,Integer n){
if (list.size() == n){
List<String> list0 = new ArrayList<>();
list.add(list0);
}
for (TableHeader header : headers) {
if (n > 0){
for (int j = n; j > 0; j--) {
//补起上级
for (int i = list.get(j-1).size()-1; i < list.get(j).size(); i++) {
list.get(j-1).add("");
}
}
//补起本级
for (int i = list.get(n).size(); i < list.get(n-1).size()-1; i++) {
list.get(n).add("");
}
}
list.get(n).add(header.getHeaderName());
if (null != header.getChildren() && header.getChildren().size() != 0){
setList(list,header.getChildren(),n+1);
}else {
//补起下级
if (list.size() > n){
for (int j = n+1; j < list.size(); j++) {
for (int i = list.get(j).size(); i <list.get(j-1).size() ; i++) {
list.get(j).add("");
}
}
}
}
}
}
方法三
优点：此方法为方法二的升级版，使用递归的方式，去除了复杂的二维数组， 实现更多级的表格
缺点：测的少，能用
这里也需要采用生成一级头方法
public static HSSFWorkbook getTable3(CustomTable model, String name) {
HSSFWorkbook wb = new HSSFWorkbook();
//无样式靠左
HSSFCellStyle cellStyle6 = getStyle(wb.createCellStyle(), 0, -1);
//无样式靠右
HSSFCellStyle cellStyle0 = getStyle(wb.createCellStyle(), 0, 0);
//灰色表头
HSSFCellStyle cellStyle = getStyle(wb.createCellStyle(), 1, 1);
//黄色表头
HSSFCellStyle cellStyle2 = getStyle(wb.createCellStyle(), 1, 2);
//橙色表头
HSSFCellStyle cellStyle3 = getStyle(wb.createCellStyle(), 1, 3);
//黄色内容
HSSFCellStyle cellStyle4 = getStyle(wb.createCellStyle(), 2, 4);
//橙色内容
HSSFCellStyle cellStyle5 = getStyle(wb.createCellStyle(), 2, 5);
//创建sheet
HSSFSheet sheet = wb.createSheet(name);
// sheet.setColumnWidth(0, 3766);
// sheet.setDefaultColumnWidth(22);
// sheet.setDefaultRowHeightInPoints(22);
List<TableHeader> headerNo = getHeaderNo2(model.getHeaders());
HSSFRow row1 = sheet.createRow(0);
HSSFRow row2 = sheet.createRow(1);
HSSFRow row3 = sheet.createRow(2);
String titleString = headerToString(model.getHeaders());
String[] headers = titleString.split(",");
//i是headers的索引，n是Excel的索引
for (short i = 0, n = 0; i < headers.length; i++) {
HSSFCell cellT = row1.createCell(n);
HSSFRichTextString text = null;
//有2级标题
if (headers[i].contains(":")) {
//有3级标题 TA:TA1=TA11@TA12_TA2
if (headers[i].contains("=")) {
//确定3级标题的个数，确定1级标题的总长度，同时也是把各级标题分解开 //分级1级标题temp[0]是标题文字，temp[1]是子标题
String[] temp = headers[i].split(":");
text = new HSSFRichTextString(temp[0]);
//获取temp2级标题的数组
String[] childlv2 = temp[1].split("_");
cellT.setCellStyle(cellStyle);
int ttlength = 0;
short row2index = n;
short row3index = n;
//循环计算全部的2级标题对应的子标题总数
for (int k = 0; k < childlv2.length; k++) {
//取到2级标题的第一个
String childlv2_1 = childlv2[k];
HSSFRichTextString textLV2 = null;
//根据n的index进行循环
HSSFCell cellChildlv2 = row2.createCell(n);
cellChildlv2.setCellStyle(cellStyle);
//一层层的向下取，取到3级，并向下进行补充
if (childlv2_1.contains("=")) {//2级子节点，有3级子节点
String[] childlv2_all = childlv2_1.split("=");
textLV2 = new HSSFRichTextString(childlv2_all[0]);
String childlv3_1 = childlv2_all[1];
//这里说明2级子节点有多个3级子节点，那么2级子节点就需要合并，同时为
if (childlv3_1.contains("@")) {
String[] childlv3_all = childlv3_1.split("@");
ttlength = ttlength + childlv3_all.length;
//这里进行2级节点的合并，因为有多个
sheet.addMergedRegion(new CellRangeAddress(1, 1, (short) n, (short) (n + childlv3_all.length - 1)));
//开始写3级节点
for (String childlv3Text : childlv3_all) {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if ("小计".equals(childlv3Text)) {
cellStyle1 = cellStyle3;
} else {
cellStyle1 = cellStyle;
}
row1.createCell(n).setCellStyle(cellStyle);
HSSFCell cellChildlv3 = row3.createCell(row3index++);
cellChildlv3.setCellStyle(cellStyle1);
cellChildlv3.setCellValue(new HSSFRichTextString(childlv3Text));
//进行EXCEL索引叠加
n++;
}
//补充2级节点的空cell
for (int x = 0; x < childlv3_all.length - 1; x++) {
HSSFCell cellChildlv2Blank = row2.createCell(++row2index);
cellChildlv2Blank.setCellStyle(cellStyle);
}
} else {
//这里说明2级子节点只有一个3级子节点，那么就不用合并和补充空格啦
ttlength = ttlength + 1;
//写入3级节点的cell
HSSFCell cellChildlv3 = row3.createCell(row3index++);
cellChildlv3.setCellStyle(cellStyle);
cellChildlv3.setCellValue(new HSSFRichTextString(childlv3_1));
n++;//Excel索引节点的递增
}
} else {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if (childlv2_1.contains("小计")) {
cellStyle1 = cellStyle3;
cellChildlv2.setCellStyle(cellStyle1);
} else {
cellStyle1 = cellStyle;
}
//2级子几点没有3及子节点
textLV2 = new HSSFRichTextString(childlv2_1);
ttlength = ttlength + 1;
row2.createCell(n).setCellStyle(cellStyle1);
row3.createCell(n).setCellStyle(cellStyle1);
//这个2级节点没有子节点，那么就要合并3row
sheet.addMergedRegion(new CellRangeAddress(1, 2, row3index, row3index));
// 补充3row的cell空格
HSSFCell cellChildlv3Blank = row3.createCell(row3index++);
cellChildlv3Blank.setCellStyle(cellStyle1);
//进行Excel的索引递增，避免写到一个格子里面去
n++;
}
cellChildlv2.setCellValue(textLV2);
}
//进行3层总长度的cell合并
sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) (n - ttlength), (short) (n - 1)));
//插入第一行的补充的空格
short tr1 = n;
//循环补充父标题的空格,因为已经定义啦一个cell所以要减1
// for(int j = 0; j < ttlength -1; j++){
//因为开始已经定义啦一个cell所以就是 ++tr1
// HSSFCell cellTitleBlank = row1.createCell(++tr1);
// cellTitleBlank.setCellStyle(cellStyle);
// }
} else {
//只有2级标题//子标题的分割
String[] temp = headers[i].split(":");
text = new HSSFRichTextString(temp[0]);
String[] childlv2 = temp[1].split("_");
cellT.setCellStyle(cellStyle);
//只有2及标题，那么1级标题要占1行，2级标题占2行
sheet.addMergedRegion(new CellRangeAddress(0, 0, n, (short) (n + childlv2.length - 1)));
//2级标题占两行所以需要和3行合并
for (int o = n; o < n + childlv2.length; o++) {
sheet.addMergedRegion(new CellRangeAddress(1, 2, o, (short) o));
}
short tr1 = n;
short tr2 = n;
//对对应的空行进行补充,第一行
//循环补充父标题的空格,因为已经定义啦一个cell所以要减1
for (int j = 0; j < childlv2.length - 1; j++) {
//因为开始已经定义啦一个cell所以就是 ++tr1
HSSFCell cellTitleBlank = row1.createCell(++tr1);
cellTitleBlank.setCellStyle(cellStyle);
}
//对第二行进行补充空格，从头开始 //未定义cell，所以不减1
for (int k = 0; k < childlv2.length; k++) {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if ("小计".equals(childlv2[k])) {
cellStyle1 = cellStyle3;
} else {
cellStyle1 = cellStyle;
}
//之前未进行定义，所以是tr2++
HSSFCell cellTitleBlank = row2.createCell(tr2++);
cellTitleBlank.setCellStyle(cellStyle1);
row2.createCell(n).setCellStyle(cellStyle1);
row3.createCell(n).setCellStyle(cellStyle1);
cellTitleBlank.setCellValue(new HSSFRichTextString(childlv2[k]));
//这里进行啦EXCEL的索引递增，不然会都写到一个格子里面去
n++;
}
}
} else {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if ("合计（实收-退款）".equals(headers[i])) {
cellStyle1 = cellStyle2;
} else {
cellStyle1 = cellStyle;
}
//只有1级标题
text = new HSSFRichTextString(headers[i]);
//没有子标题的时候自己独占两行
row1.createCell(n).setCellStyle(cellStyle1);
row2.createCell(n).setCellStyle(cellStyle1);
row3.createCell(n).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(0, 2, n, n));
cellT.setCellStyle(cellStyle1);
n++;
}
// cellT.setCellStyle(cellStyle);
cellT.setCellValue(text);
}
sheet.autoSizeColumn((short) 0);
sheet.autoSizeColumn((short) 1);
sheet.autoSizeColumn((short) 2);
List<TableData> dataList1 = model.getDataList();
List<String> totalStr = model.getTotalStr();
if (totalStr != null) {
if (headerNo.size() == totalStr.size()) {
TableData tableData = new TableData();
Map<String, Object> map = new HashMap<>(10);
for (int i = 0; i < totalStr.size(); i++) {
map.put(headerNo.get(i).getKey(), totalStr.get(i));
}
tableData.setData(map);
dataList1.add(tableData);
}
}
for (int i = 0; i < dataList1.size(); i++) {
HSSFRow row = sheet.createRow(3 + i);
TableData tableData = dataList1.get(i);
Map<String, Object> data = tableData.getData();
for (int j = 0; j < headerNo.size(); j++) {
for (String s : data.keySet()) {
HSSFCell cell = row.createCell(j);
// HSSFCellStyle cellStyle1 = wb.createCellStyle();
if (headerNo.get(j).getKey().contains("小计")) {
// cellStyle1 = cellStyle5;
cell.setCellStyle(cellStyle5);
} else if ("合计（实收-退款）".equals(headerNo.get(j).getKey())) {
// cellStyle1 = cellStyle4;
cell.setCellStyle(cellStyle4);
}else if(null != headerNo.get(j).getType()){
if (headerNo.get(j).getType() == 0){
cell.setCellStyle(cellStyle6);
}else {
cell.setCellStyle(cellStyle0);
}
} else {
// cellStyle1 = cellStyle0;
cell.setCellStyle(cellStyle0);
}
if (headerNo.get(j).getKey().equals(s)) {
System.out.println("s = " + data.get(s));
Boolean b = false;
for (String fixedDetailHeader : FIXED_DETAIL_HEADERS) {
if (fixedDetailHeader.equals(s)) {
b = true;
break;
}
}
if (!b && !s.contains("小计")) {
b = !MyStringUtil.isNumeric3(data.get(s).toString());
}
if (b) {
// cellStyle1 = cellStyle6;
cell.setCellValue(data.get(s).toString());
} else {
if (null != model.getNumType() && model.getNumType() == 1){
cell.setCellValue(MyStringUtil.parseNumberToTenThousand(data.get(s).toString()));
}else {
cell.setCellValue(MyStringUtil.parseNumber(data.get(s).toString()));
}
}
break;
}
}
// cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
}
}
// setSizeColumn(sheet, headerNo.size());
// for (int i = 0; i < model.getDataList().size(); i++) {
// HSSFRow row = sheet.createRow(3+i);
// for (int j = 0; j < 9; j++) {
// row.createCell(j).setCellValue(i);
// }
// }
for (int k = 0; k < headerNo.size(); k++) {
if (headerNo.get(k).getKey().length() > 6) {
sheet.setColumnWidth(k, headerNo.get(k).getKey().length() * 256 + 256 * 14);
} else {
sheet.setColumnWidth(k, 3766);
}
}
return wb;
}
生成一级头方法
public static List<TableHeader> getHeaderNo2S(List<TableHeader> headers,List<TableHeader> newHeaders){
for (TableHeader header : headers) {
if (null == header.getChildren()){
newHeaders.add(header);
}else {
getHeaderNo2S(header.getChildren(),newHeaders);
}
}
return newHeaders;
}
其他工具（后续慢慢补充）
1.生成page类方法
/**
* 调用此方法需先分组，把分组的map传入
* 此方法返回分页类，并分割map
* @param collect
* @param num
* @param size
* @return
*/
public static CustomTablePage getPage(Map<String,?> collect, Integer num, Integer size){
Integer total = collect.size();
CustomTablePage page = new CustomTablePage();
if (total<=size){
page.setNum(1);
page.setSize(size);
page.setTotal(total);
}else {
page.setSize(size);
page.setTotal(total);
int sn = 0;
int en = 0;
int maxNum = total%size == 0 ?total/size:total/size+1;
page.setNum(num*size>total?maxNum:num);
sn = (num-1)*size >= total?(maxNum-1)*size:(num-1)*size;
en = num*size > total?total:num*size;
// collect = collect.subList(sn,en);
List<String> stringList = new ArrayList<>();
stringList.addAll(collect.keySet());
List<String> stringList2 = stringList.subList(sn,en);
stringList.removeAll(stringList2);
for (String s : stringList) {
collect.remove(s);
}
}
return page;
}
2373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
