java导出功能（多级表头，复杂表头）
前言
大概内容：
多级表头，复杂表头的导出功能都可以仿照这个例子去编写
提示：以下是本篇文章正文内容，下面案例可供参考
一、实现的效果
二、调用工具类
业务层调用
//假设这是需要导出的数据
List<MonitorFoldLineTable> data = new ArrayList();
//调用导出工具类
FileExportUtil.testExcelDemo(data,response);
对应的实体类
public class MonitorFoldLineTable {
private String cdbh;
private String sd;
private Double dcsg;
private Double dcdsf;
private Double dcc;
private Double ljsg;
private Double ljdsf;
private Double ljc;;
private Double bxsg;
private Double bxdsf;
private Double bxc;
private String time;
}
三、详细工具类
直接copy改吧改吧就可以使用，有详细注释
import com.iS3.manager.monitoring.domain.vo.MonitorFoldLineTable;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Author: yangyongzhuo
* @Date: 2021/07/21/8:58
* @Description:
*/
public class FileExportUtil {
/**
* @param list 需要写入excel的数据 从数据库或者其他途径读取
* @Description: 导出监测对比数据的工具类
* @return:
* @Author: yangyongzhuo
* @Date: 2021/7/25 9:29
*/
public static void testExcelDemo(List<MonitorFoldLineTable> list, HttpServletResponse response) {
/** 第一步，创建一个Workbook，对应一个Excel文件 */
XSSFWorkbook wb = new XSSFWorkbook();
/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */
XSSFSheet sheet = wb.createSheet("excel导出标题");
/** 第三步，设置样式以及字体样式*/
XSSFCellStyle titleStyle = createTitleCellStyle(wb);
XSSFCellStyle headerStyle = createHeadCellStyle(wb);
XSSFCellStyle contentStyle = createContentCellStyle(wb);
/** 第四步，创建标题 ,合并标题单元格 */
// 行号
int rowNum = 0;
// 创建第一页的第一行，索引从0开始
XSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 600);// 设置行高
String[] row_Text = {"测点编号", "单次变化量", "", "", "累计变化量", "", "", "变形速率", "", "", "监测时间"};
for (int i = 0; i < row_Text.length; i++) {
XSSFCell c00 = row0.createCell(i);
c00.setCellValue(row_Text[i]);
c00.setCellStyle(headerStyle);
}
// 合并单元格，参数依次为起始列，结束列，起始行，结束行 （索引0开始）
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//标题合并单元格操作，4为总列数
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));//标题合并单元格操作，4为总列数
sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));//标题合并单元格操作，4为总列数
sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));//标题合并单元格操作，4为总列数
sheet.addMergedRegion(new CellRangeAddress(0, 1, 10, 10));//标题合并单元格操作，4为总列数
//第二行
XSSFRow row2 = sheet.createRow(rowNum++);
row2.setHeight((short) 700);
String[] row_third = {"", "施工监测", "第三方监测", "前后差值", "施工监测", "第三方监测", "前后差值", "施工监测", "第三方监测", "前后差值"};
for (int i = 0; i < row_third.length; i++) {
XSSFCell tempCell = row2.createCell(i);
tempCell.setCellValue(row_third[i]);
tempCell.setCellStyle(headerStyle);
}
for (MonitorFoldLineTable value : list) {
XSSFRow tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short) 500);
// 循环单元格填入数据
for (int j = 0; j < 12; j++) {
//列宽自适应，j为自适应的列，true就是自适应，false就是不自适应，默认不自适应
sheet.autoSizeColumn(j, true);
XSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
String tempValue = "";
switch (j) {
case 0:
tempValue = value.getCdbh();
break;
case 1:
tempValue = value.getSd();
break;
case 2:
tempValue = value.getDcsg().toString();
break;
case 3:
tempValue = value.getDcdsf().toString();
break;
case 4:
tempValue = value.getDcc().toString();
break;
case 5:
tempValue = value.getLjsg().toString();
break;
case 6:
tempValue = value.getLjdsf().toString();
break;
case 7:
tempValue = value.getLjc().toString();
break;
case 8:
tempValue = value.getBxsg().toString();
break;
case 9:
tempValue = value.getBxdsf().toString();
break;
case 10:
tempValue = value.getBxc().toString();
break;
case 11:
tempValue = value.getTime();
break;
}
tempCell.setCellValue(tempValue);
}
}
//导出到浏览器下载
buildExcelDocument("监测数据", wb, response);
}
/**
* @Description: [导出到浏览器]
* @Param: [fileName, wb, response]
* @return: void
* @Author: yangyongzhuo
* @Date: 2021/7/25 9:40
*/
private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
try {
response.setContentType("application/octet-stream");
// 可自行定义编码格式
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
//清除jsp编译html文件的空白,防止excel出现空行
response.flushBuffer();
//写出
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(wb);
}
}
//---------------------------------------以下是封装的样式-----------------------------
/**
* 创建标题样式
*
* @param wb
* @return
*/
private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setBold(true); //字体加粗
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建表头样式
*
* @param wb
* @return
*/
private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
headerFont.setBold(true); //字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
cellStyle.setFont(headerFont); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建内容样式
*
* @param wb
* @return
*/
private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
// 生成12号字体
XSSFFont font = wb.createFont();
font.setColor((short) 8);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
return cellStyle;
}
}
1045
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
