动态导出表格
接上次发布的导出做了一些优化和加入一些功能
优化：修改了一些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;
}
}
// 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 < tableHeaders.size(); k++) {
if (null == tableHeaders.get(k).getWidth()){
if (tableHeaders.get(k).getHeaderName().length() > 6) {
sheet.setColumnWidth(k, tableHeaders.get(k).getHeaderName().length() * 256 + 256 * 14);
} else {
sheet.setColumnWidth(k, 3766);
}
}else {
sheet.setColumnWidth(k, tableHeaders.get(k).getWidth());
}
}
return wb;
}
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 String[][] getPropertyDesByRecursionAndStyle(List<TableHeader> headers,List<List<TableStyle>> headerSty,Integer numType){
List<List<String>> list = new ArrayList<>();
setListAndStyle(list,headers,0,headerSty,numType);
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 (!header.getIsDisplay()){
continue;
}
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 void setListAndStyle(List<List<String>> list,List<TableHeader> headers,Integer n,List<List<TableStyle>> headerSty,Integer numType){
if (list.size() == n){
List<String> list0 = new ArrayList<>();
List<TableStyle> tableStyles = new ArrayList<>();
list.add(list0);
headerSty.add(tableStyles);
}
for (TableHeader header : headers) {
if (!header.getIsDisplay()){
continue;
}
if (n > 0){
for (int j = n; j > 0; j--) {
//补起上级
for (int i = list.get(j-1).size()-(j == n ? 1 : 0); i < list.get(j).size(); i++) {
list.get(j-1).add("");
headerSty.get(j-1).add(null);
}
}
//补起本级
for (int i = list.get(n).size(); i < list.get(n-1).size()-1; i++) {
list.get(n).add("");
headerSty.get(n).add(null);
}
}
if (numType == 1 && header.getType() == 1 && null == header.getChildren()){
list.get(n).add(header.getHeaderName()+TENTHOUSANDYUAN);
}else {
list.get(n).add(header.getHeaderName());
}
// if (null == header.getHeaderStyle()){
// headerSty.get(n).add(new TableStyle("GREY_25_PERCENT",0,0,0,""));
// }else {
headerSty.get(n).add(header.getHeaderStyle());
// }
if (null != header.getChildren() && header.getChildren().size() != 0){
setListAndStyle(list,header.getChildren(),n+1,headerSty,numType);
}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("");
headerSty.get(j).add(null);
}
}
}
}
}
}
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 void main(String[] args) {
// List<TableHeader> headers = new ArrayList<>();
// //一级
// TableHeader tableHeader0 = new TableHeader();
// tableHeader0.setHeaderName("0");
// headers.add(tableHeader0);
// //一级
// TableHeader tableHeader1 = new TableHeader();
// tableHeader1.setHeaderName("1");
// headers.add(tableHeader1);
// //二级
// TableHeader tableHeader2 = new TableHeader();
// tableHeader2.setHeaderName("2");
// List<TableHeader> headers2 = new ArrayList<>();
// TableHeader tableHeader20 = new TableHeader();
// tableHeader20.setHeaderName("20");
// headers2.add(tableHeader20);
// TableHeader tableHeader21 = new TableHeader();
// tableHeader21.setHeaderName("21");
// headers2.add(tableHeader21);
// tableHeader2.setChildren(headers2);
// headers.add(tableHeader2);
// //三级
// TableHeader tableHeader3 = new TableHeader();
// tableHeader3.setHeaderName("3");
//
// List<TableHeader> headers3 = new ArrayList<>();
// TableHeader tableHeader30 = new TableHeader();
// tableHeader30.setHeaderName("30");
// List<TableHeader> headers30 = new ArrayList<>();
// TableHeader tableHeader300 = new TableHeader();
// tableHeader300.setHeaderName("300");
// headers30.add(tableHeader300);
// TableHeader tableHeader301 = new TableHeader();
// tableHeader301.setHeaderName("301");
// headers30.add(tableHeader301);
// TableHeader tableHeader302 = new TableHeader();
// tableHeader302.setHeaderName("302");
// headers30.add(tableHeader302);
// tableHeader30.setChildren(headers30);
// headers3.add(tableHeader30);
// TableHeader tableHeader31 = new TableHeader();
// tableHeader31.setHeaderName("31");
// headers3.add(tableHeader31);
//
// tableHeader3.setChildren(headers3);
// headers.add(tableHeader3);
// String[][] propertyDes = getPropertyDes(headers);
// System.out.println("propertyDes = " + propertyDes);
//
// String[][] propertyDesByRecursion = getPropertyDesByRecursion(headers);
// System.out.println("propertyDesByRecursion = " + propertyDesByRecursion);
TableHeader tableHeader = new TableHeader();
tableHeader.setKey("");
tableHeader.setHeaderName("");
tableHeader.setChildren(Lists.newArrayList());
tableHeader.setType(0);
tableHeader.setIsDisplay(false);
Class cl = tableHeader.getClass();
Field[] declaredFields = cl.getDeclaredFields();
for (Field declaredField : declaredFields) {
TableAnnotation annotation = declaredField.getAnnotation(TableAnnotation.class);
ApiModelProperty apiModelProperty = declaredField.getAnnotation(ApiModelProperty.class);
if (null != annotation){
System.out.println("value() = " +annotation.value());
}
if (null != apiModelProperty){
System.out.println("value() = " +apiModelProperty.value());
}
}
}
/**
* 调用此方法需先分组，把分组的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;
}
public static List<TableHeader> getHeadersByIdType(Integer idType){
List<TableHeader> tableHeaders = new ArrayList<>();
switch (idType){
case 3:
TableHeader tableHeaderW = new TableHeader();
tableHeaderW.setKey("围合");
tableHeaderW.setHeaderName("围合");
tableHeaders.add(tableHeaderW);
case 2:
TableHeader tableHeaderX = new TableHeader();
tableHeaderX.setKey("项目");
tableHeaderX.setHeaderName("项目");
tableHeaders.add(0,tableHeaderX);
if (idType == 3){
TableHeader tableHeaderC = new TableHeader();
tableHeaderC.setKey("城市公司");
tableHeaderC.setHeaderName("城市公司");
tableHeaders.add(0,tableHeaderC);
}
case 1:
TableHeader tableHeaderY = new TableHeader();
tableHeaderY.setKey("业务单元");
tableHeaderY.setHeaderName("业务单元");
tableHeaders.add(0,tableHeaderY);
case 0:
TableHeader tableHeaderD = new TableHeader();
tableHeaderD.setKey("大区");
tableHeaderD.setHeaderName("大区");
tableHeaders.add(0,tableHeaderD);
break;
default:
break;
}
return tableHeaders;
}
}
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;
}
3028
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
