/**
* 带分类标题导出Excel的方法
*
* @param title excel中的sheet名称
* @param header 两列的头的标题 单层表头集合
* @param parentHeader 分类列 双层表头父表头
* @param childSize 分类列行数 将子标题长度保存起来
* @param childHeader 分类列行数 双层表头子表头
* @param columns 列名 标题key,用于取表格中各列的值
* @param result 结果集
* @param response 响应
* @param pattern 时间格式
* @param columnWidth 列宽 每列的宽度集合
* @param mergeColumn 需要合并几列,1表示合并第一列,2表示第一列和第二列都要合并
*/
public void exportoExcel(String title, List<String> header, List<String> parentHeader, List<Integer> childSize,
List<String> childHeader, List<String> columns, Collection<T> result, HttpServletResponse response,
String pattern, List<Integer> columnWidth, int mergeColumn){
OutputStream out = null;
try {
//设置头信息为一次只能下载一个
response.setContentType("application/binary;charset=UTF-8");
//设置前端能拿取到除了固定的访问头之外的访问头
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Access-Control-Allow-Credentials", "true");
String fileName = ExportChineseProcessing.chineseProcess(title, ".xls");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"; filename*=utf-8''" + fileName);
out = response.getOutputStream();
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
//设置表头样式
ExportUtil exportUtil = new ExportUtil(workbook, sheet);
HSSFCellStyle headStyle = exportUtil.getHeadStyle();
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFCellStyle leftBodyStyle = exportUtil.getBodyStyle(HorizontalAlignment.LEFT);
HSSFCellStyle rightBodyStyle = exportUtil.getBodyStyle(HorizontalAlignment.RIGHT);
//构建大表头,第一行
//和并单元格,1和2表示行的开始到行的结束,3和4表示列的开始到列结束
//参数:起始行号，终止行号， 起始列号，终止列号
CellRangeAddress cra = new CellRangeAddress(0, (short) 0, 0, (short) (columns.size() - 1));
sheet.addMergedRegion(cra);
Row row = sheet.createRow(0);
row.setHeight((short) 1000);
Cell row0 = row.createCell(0);
row0.setCellValue(title);
row0.setCellStyle(headStyle);
//小表头 第二行和第三行合并
//是否需要合并列
boolean isMergeRow = false;
//单元格集合,为后续添加边框用
List<CellRangeAddress> cellSetBorderList = new ArrayList<>();
//根据header_2的个数,填充多少个需要合并两行的列,剩余的分别在各自的行上横向合并
row = sheet.createRow(1);
//如果双层表头父表头和字表头都为空则不合并
if (parentHeader != null && parentHeader.size() != 0 && childHeader != null && childHeader.size() != 0) {
if (header != null && header.size() > 0) {
for (int i = 0; i < header.size(); i++) {
cra = new CellRangeAddress(1, 2, i, i);
sheet.addMergedRegion(cra);
cellSetBorderList.add(cra);
final Cell cell = row.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(header.get(i));
isMergeRow = true;
}
}
//双层表头
//header_cate双层表头父表头
int startCol = header == null ? 0 : header.size();
int endCol = header == null ? -1 : header.size() - 1;
for (int i = 0; i < parentHeader.size(); i++) {
//header.size()表示单层标题的个数,columns.get(i)表示第几个双层标题下的子标题个数
//如果父表体的长度不等于1则合并,否则直接填充值
if (childSize.get(i) != 1) {
cra = new CellRangeAddress(1, 1, startCol += (i == 0 ? 0 : childSize.get(i - 1)),
endCol += (childSize.get(i)));
sheet.addMergedRegion(cra);
cellSetBorderList.add(cra);
} else {
startCol += (i == 0 ? 0 : childSize.get(i - 1));
endCol += (childSize.get(i));
}
final Cell cell = row.createCell(startCol);
cell.setCellStyle(headStyle);
cell.setCellValue(parentHeader.get(i));
}
//双层表头子表头
row = sheet.createRow(2);
for (int i = 0; i < childHeader.size(); i++) {
final Cell cell = row.createCell(i + (header == null ? 0 : header.size()));
cell.setCellStyle(headStyle);
cell.setCellValue(childHeader.get(i));
}
} else {
for (int i = 0; i < header.size(); i++) {
final Cell cell = row.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(header.get(i));
}
}
//将第一列上填充值
int index = 2;
if (isMergeRow) {
index = 3;
}
for (T t : result) {
row = sheet.createRow(index++);
for (short j = 0; j < mergeColumn; j++) {
String fieldName = columns.get(j);
Cell cell = row.createCell(j);
//判断是否有取对象中的值
String[] name = null;
if (fieldName.contains(".")) {
String[] strName = fieldName.split("\\.");
fieldName = strName[0];
name = strName[1].replace("[", "").replace("]", "")
.split("\\+");
}
//列名
Object value;
value = getValueOrFormatValue(t, fieldName, name);
//设置值
String textValue = null;
textValue = getTextValue(pattern, workbook, sheet, patriarch, row, index, j, name, value, textValue);
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
cell.setCellStyle(leftBodyStyle);
}
}
//是否需要合并列
boolean isMergeCell = false;
//下面去值时从第几行开始
index = 2;
if (isMergeRow) {
index = 3;
}
//如果所有的行数大与2则开始判断是否要合并
if (sheet.getLastRowNum() > 2) {
//得到第三行
HSSFRow row_1 = sheet.getRow(index);
//这里暂时用于mergeColumn列中的上下行合并
for (int j = 0; j < mergeColumn; j++) {
//得到第一列
HSSFCell cell_1 = row_1.getCell(j);
//得到第一列的值
String departname = cell_1.getStringCellValue();
//从第四行开与前一行比较,如果一致则设置值为空
for (int i = (index + 1); i <= sheet.getLastRowNum(); i++) {
HSSFRow rows = sheet.getRow(i);
//此处表示对单元格进行内容相同合并处理
HSSFCell cells_1 = rows.getCell(j);
//这里值相同则设置位空，方便之后的合并
if (departname.equals(cells_1.getStringCellValue())) {
cells_1.setCellValue("");
isMergeCell = true;
} else {
departname = cells_1.getStringCellValue();
}
}
}
}
if (isMergeCell) {
//将为空的单元格与之前不为空的合并
for (int j = 0; j < mergeColumn; j++) {
int sk = (isMergeRow ? 3 : 2);
for (int i = (isMergeRow ? 4 : 3); i <= sheet.getLastRowNum(); i++) {
HSSFRow rows = sheet.getRow(i);
HSSFCell cell_0 = rows.getCell(j);
//如果为空但还没对比到最后一行，继续循环
if ("".equals(cell_0.getStringCellValue())) {
if (i == sheet.getLastRowNum()) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(sk, i, j, j);
//如果已经对比到最后一行，开始合并
sheet.addMergedRegion(cellRangeAddress);
cellSetBorderList.add(cellRangeAddress);
}
} else {
//不为空且i-1不为sk则合并
if (sk != i - 1) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(sk, i - 1, j, j);
sheet.addMergedRegion(cellRangeAddress);
cellSetBorderList.add(cellRangeAddress);
}
sk = i;
}
}
}
}
//添加边框
for (CellRangeAddress cellRangeAddress : cellSetBorderList) {
exportUtil.setBorder(cellRangeAddress);
}
// 遍历集合数据，产生数据行
if (result != null) {
for (T t : result) {
row = sheet.getRow(index++);
for (short i = (short) mergeColumn; i < columns.size(); i++) {
Cell cell = row.createCell(i);
String fieldName = columns.get(i);
//判断是否有取对象中的值
String[] name = null;
if (fieldName.contains(".")) {
String[] strName = fieldName.split("\\.");
fieldName = strName[0];
name = strName[1].replace("[", "").replace("]", "")
.split("\\+");
}
Object value;
//格式化数据或通过可以获取数据
value = getValueOrFormatValue(t, fieldName, name);
String textValue = null;
textValue = getTextValue(pattern, workbook, sheet, patriarch, row, index, i, name, value, textValue);
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
cell.setCellStyle(rightBodyStyle);
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
cell.setCellStyle(leftBodyStyle);
}
}
}
}
}
// 设置表格默认列宽度为20个字节
if (columnWidth == null || columnWidth.size() == 0) {
sheet.setDefaultColumnWidth((short) 10);
} else {
for (int i = 0; i < columns.size(); i++) {
sheet.setColumnWidth(i, columnWidth.get(i) * 256);
}
}
// 自适应调整宽度
// for (int i = 0; i < columns.size(); i++) {
// sheet.autoSizeColumn(i);
// }
workbook.write(out);
out.flush();
out.close();
}catch (Exception e){
e.printStackTrace();
}finally {
try {
if (out != null){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
poi导出excel方法
最新推荐文章于 2024-11-19 16:02:23 发布
扫一扫
8749
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
