动态导出表格
接上次发布的导出做了一些优化和加入一些功能
优化:修改了一些bug,和优化判断逻辑
新功能:加入样式自定义,可自定义表头和表内容样式
新方法方法名为:summaryTableExportS,注意分辨
其中新增了:自定义样式方法,分页方法
这里加入MyStringUtil中两个方法 parseNumberToTenThousand(金额,单位为万元的可是化)
parseNumber(金额,单位为元的格式化)
这两个方法单独写在下面,也可以去看看另一篇 常用工具
package com.es.utils;
import com.es.charge.tabel.*;
import com.es.report.finance.comprehensive.vo.AdvancePaymentAccountReportVo;
import com.google.common.collect.Lists;
import com.es.util.MyStringUtil;
import com.es.util.TableAnnotation;
import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Stream;
/**
* @Author: dengx
* @Date: 2021/11/5
* @Description:
*/
public class ExportUtil {
private static final String TENTHOUSANDYUAN = "(万元)";
// public static final String[] FIXED_DETAIL_HEADERS = {"资源名称", "客户名称", "票据号", "票据类型", "发票代码", "收费时间", "收费员", "来源", "付款方式"};
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();
}
}
}
public static void summaryTableExportS(CustomTable model, HttpServletResponse response) {
// CustomTable model1 = new CustomTable();
// List<PaymentManagementSummaryInquiryVo> vos = getTestData();
// List<TableHeader> header = getHeader(vos);
// model1.setHeaders(header);
// List<TableData> tableData = getTableData(vos);
// model1.setDataList(tableData);
String name = model.getTableName();
OutputStream outputStream = null;
try {
// 在此处创建wk,是excel的文档对象,用于接收service层处理后的数据;
HSSFWorkbook wk = new HSSFWorkbook();
// 创建一个查询参数对象,此对象代码如下UserHolidyParam 代码所示;
// 调用service层的进一步处理方法,将查询参数对象以及response返回对象传递过去
wk = getTable2(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,2,3级)
* @param model
* @param name
* @return
*/
public static HSSFWorkbook getTable(CustomTable model, String name) {
List<String> temporaryTable = model.getTemporaryTable();
if (null == temporaryTable){
temporaryTable = new ArrayList<>();
}
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 : temporaryTable) {
if (fixedDetailHeader.equals(s)) {
b = true;
break;
}
}
if (!b) {
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;
}
/**
* 老板(1,2,3级) + 表头样式决定数据样式
* @param model
* @param name
* @return
*/
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;
if (!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;
}
/**
* @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;
}
/**
*
* @param cellStyle
* @param colour 颜色
* @param frame 边框 -1:无 0:全
* @param alignment 对齐(自带垂直居中) -1 靠左;0 水平居中; 1 靠右
* @param headerOrData 表头 or 表内容 0:表头 1:表内容
* @return
*/
public static HSSFCellStyle getStyleS(HSSFCellStyle cellStyle, String colour, Integer frame,Integer alignment,Integer headerOrData){
// 垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置对齐
switch (alignment){
case -1:
//水平居左
cellStyle.setAlignment(HorizontalAlignment.LEFT);
break;
case 0:
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
break;
case 1:
//水平靠右
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
break;
default:
break;
}
//边框
if (frame == 0){
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
if (headerOrData == 0){
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());
}else {
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
}
}
//设置颜色
// 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
switch (colour){
case "BLACK" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
break;
case "BROWN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex());
break;
case "OLIVE_GREEN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.OLIVE_GREEN.getIndex());
break;
case "DARK_GREEN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_GREEN.getIndex());
break;
case "DARK_TEAL" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_TEAL.getIndex());
break;
case "DARK_BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_BLUE.getIndex());
break;
case "INDIGO" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.INDIGO.getIndex());
break;
case "GREY_80_PERCENT" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_80_PERCENT.getIndex());
break;
case "ORANGE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
break;
case "DARK_YELLOW" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_YELLOW.getIndex());
break;
case "GREEN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
break;
case "TEAL" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.TEAL.getIndex());
break;
case "BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
break;
case "BLUE_GREY" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
break;
case "GREY_50_PERCENT" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
break;
case "RED" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
break;
case "LIGHT_ORANGE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());
break;
case "LIME" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
break;
case "SEA_GREEN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());
break;
case "AQUA" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());
break;
case "LIGHT_BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_BLUE.getIndex());
break;
case "VIOLET" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
break;
case "GREY_40_PERCENT" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex());
break;
case "PINK" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PINK.getIndex());
break;
case "GOLD" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
break;
case "YELLOW" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
break;
case "BRIGHT_GREEN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
break;
case "TURQUOISE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.TURQUOISE.getIndex());
break;
case "DARK_RED" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
break;
case "SKY_BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
break;
case "PLUM" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PLUM.getIndex());
break;
case "GREY_25_PERCENT" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
break;
case "ROSE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ROSE.getIndex());
break;
case "LIGHT_YELLOW" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
break;
case "LIGHT_GREEN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());
break;
case "LIGHT_TURQUOISE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
break;
case "PALE_BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
break;
case "LAVENDER" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
break;
case "WHITE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
break;
case "CORNFLOWER_BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
break;
case "LEMON_CHIFFON" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LEMON_CHIFFON.getIndex());
break;
case "MAROON" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.MAROON.getIndex());
break;
case "ORCHID" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ORCHID.getIndex());
break;
case "CORAL" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.CORAL.getIndex());
break;
case "ROYAL_BLUE" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
break;
case "LIGHT_CORNFLOWER_BLUE":
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
break;
case "TAN" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.TAN.getIndex());
break;
case "AUTOMATIC" :
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AUTOMATIC.getIndex());
break;
default:
break;
}
return cellStyle;
}
public static HSSFFont getFontStyleS(HSSFFont fontStyle, String colour){
if (null == colour){
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
return fontStyle;
}
switch (colour){
case "BLACK" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
break;
case "BROWN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex());
break;
case "OLIVE_GREEN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.OLIVE_GREEN.getIndex());
break;
case "DARK_GREEN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.DARK_GREEN.getIndex());
break;
case "DARK_TEAL" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.DARK_TEAL.getIndex());
break;
case "DARK_BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.DARK_BLUE.getIndex());
break;
case "INDIGO" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.INDIGO.getIndex());
break;
case "GREY_80_PERCENT" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.GREY_80_PERCENT.getIndex());
break;
case "ORANGE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
break;
case "DARK_YELLOW" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.DARK_YELLOW.getIndex());
break;
case "GREEN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
break;
case "TEAL" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.TEAL.getIndex());
break;
case "BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
break;
case "BLUE_GREY" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
break;
case "GREY_50_PERCENT" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
break;
case "RED" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
break;
case "LIGHT_ORANGE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());
break;
case "LIME" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
break;
case "SEA_GREEN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());
break;
case "AQUA" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex());
break;
case "LIGHT_BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIGHT_BLUE.getIndex());
break;
case "VIOLET" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
break;
case "GREY_40_PERCENT" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex());
break;
case "PINK" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.PINK.getIndex());
break;
case "GOLD" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
break;
case "YELLOW" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
break;
case "BRIGHT_GREEN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
break;
case "TURQUOISE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.TURQUOISE.getIndex());
break;
case "DARK_RED" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
break;
case "SKY_BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
break;
case "PLUM" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.PLUM.getIndex());
break;
case "GREY_25_PERCENT" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
break;
case "ROSE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.ROSE.getIndex());
break;
case "LIGHT_YELLOW" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
break;
case "LIGHT_GREEN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());
break;
case "LIGHT_TURQUOISE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
break;
case "PALE_BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
break;
case "LAVENDER" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
break;
case "WHITE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
break;
case "CORNFLOWER_BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
break;
case "LEMON_CHIFFON" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LEMON_CHIFFON.getIndex());
break;
case "MAROON" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.MAROON.getIndex());
break;
case "ORCHID" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.ORCHID.getIndex());
break;
case "CORAL" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.CORAL.getIndex());
break;
case "ROYAL_BLUE" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
break;
case "LIGHT_CORNFLOWER_BLUE":
fontStyle.setColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
break;
case "TAN" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.TAN.getIndex());
break;
case "AUTOMATIC" :
fontStyle.setColor(HSSFColor.HSSFColorPredefined.AUTOMATIC.getIndex());
break;
default:
fontStyle.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
break;
}
return fontStyle;
}
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;
}
/**
* 将多级转为一级
* @param headers
* @return
*/
public static List<TableHeader> getHeaderNo2(List<TableHeader> headers){
List<TableHeader> tableHeaders = new ArrayList<>();
for (TableHeader tableHeader : tableHeaders) {
if (null == tableHeader.getChildren()){
tableHeaders.add(tableHeader);
}else {
List<TableHeader> children = tableHeader.getChildren();
for (TableHeader child : children) {
if (null == child){
tableHeaders.add(child);
}else {
List<TableHeader> children1 = child.getChildren();
for (TableHeader header : children1) {
if (null == header.getChildren()){
tableHeaders.add(header);
}
}
}
}
}
}
return tableHeaders;
}
/**
* 将多级转为一级,使用递归
* @param headers
* @return
*/
public static List<TableHeader> getHeaderNo2S(List<TableHeader> headers,List<TableHeader> newHeaders){
for (TableHeader header : headers) {
if (header.getIsDisplay()){
if (null == header.getChildren()){
newHeaders.add(header);
}else {
getHeaderNo2S(header.getChildren(),newHeaders);
}
}
}
return newHeaders;
}
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;
}
public static HSSFWorkbook getTable2(CustomTable model,String name){
// String[][] propertyDes = getPropertyDes(model.getHeaders());
List<List<TableStyle>> headerSty = new ArrayList<>();
// String[][] propertyDes = getPropertyDesByRecursion(model.getHeaders());
String[][] propertyDes = getPropertyDesByRecursionAndStyle(model.getHeaders(),headerSty,model.getNumType());
String colour;
Integer frame;
Integer alignment;
Integer headerOrData;
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);
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);
Boolean setStyle = true;
if (headerSty.size() >= i+1){
List<TableStyle> tableStyles = headerSty.get(i);
if (tableStyles.size() >= j+1){
TableStyle tableStyle = tableStyles.get(j);
if (null != tableStyle){
HSSFCellStyle styleS = getStyleS(wb.createCellStyle(), tableStyle.getColour(), tableStyle.getFrame(), tableStyle.getAlignment(), 0);
cell.setCellStyle(styleS);
styleS.setFont(getFontStyleS(wb.createFont(),tableStyle.getFontColour()));
setStyle = false;
}
}
}
if (setStyle){
cell.setCellStyle(cellStyle);
}
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<TableHeader> tableHeaders = new ArrayList<>();
getHeaderNo2S(model.getHeaders(),tableHeaders);
List<TableData> dataList1 = model.getDataList();
List<String> totalStr = model.getTotalStr();
if (totalStr != null) {
if (tableHeaders.size() == totalStr.size()) {
TableData tableData = new TableData();
Map<String, Object> map2 = new HashMap<>(10);
for (int i = 0; i < totalStr.size(); i++) {
map2.put(tableHeaders.get(i).getKey(), totalStr.get(i).replace(",",""));
}
tableData.setData(map2);
dataList1.add(tableData);
}
}
for (int i = 0; i < dataList1.size(); i++) {
HSSFRow row = sheet.createRow(propertyDes.length + i);
TableData tableData = dataList1.get(i);
Map<String, Object> data = tableData.getData();
for (int j = 0; j < tableHeaders.size(); j++) {
for (String s : data.keySet()) {
HSSFCell cell = row.createCell(j);
if (tableHeaders.get(j).getKey().equals(s)) {
System.out.println("s = " + data.get(s));
Boolean b = tableHeaders.get(j).getType() == 0;
if (b) {
if (!"".equals(tableHeaders.get(j).getDataStyle())){
//待补充样式
TableStyle dataStyle = tableHeaders.get(j).getDataStyle();
if (null != dataStyle){
colour = dataStyle.getColour();
alignment = dataStyle.getAlignment();
frame = dataStyle.getFrame();
headerOrData = dataStyle.getHeaderOrData();
cell.setCellStyle(getStyleS(wb.createCellStyle(),colour,frame,alignment,headerOrData));
}
}else {
cell.setCellStyle(cellStyle6);
}
cell.setCellValue(data.get(s).toString());
} else {
if (model.getNumType() == 1){
cell.setCellValue(MyStringUtil.parseNumberToTenThousand(data.get(s).toString().replace(",","")));
}else {
cell.setCellValue(MyStringUtil.parseNumber(data.get(s).toString()));
}
if (null != tableHeaders.get(j).getDataStyle()){
//待补充样式
TableStyle dataStyle = tableHeaders.get(j).getDataStyle();
if (null != dataStyle){
colour = dataStyle.getColour();
alignment = dataStyle.getAlignment();
frame = dataStyle.getFrame();
headerOrData = dataStyle.getHeaderOrData();
cell.setCellStyle(getStyleS(wb.createCellStyle(),colour,frame,alignment,headerOrData));
}
}else {
cell.setCellStyle(cellStyle0);
}
}
break;
MyStringUtil中的方法
public static String parseNumberToTenThousand(String bd) {
if (null == bd || "".equals(bd)){
return "";
}
if (isNumeric2(bd)){
BigDecimal bigDecimal = new BigDecimal(bd);
BigDecimal divide = bigDecimal.divide(new BigDecimal("10000"), 6, BigDecimal.ROUND_HALF_UP);
return parseNumber(divide.toString());
}
return bd;
}
public static String parseNumber(String bd) {
if (null == bd || "".equals(bd)){
return "";
}
if (isNumeric2(bd)){
DecimalFormat df = new DecimalFormat(",###,##0.00");
return df.format(new BigDecimal(bd));
}
return bd;
}