java导出复杂格式的Excel（poi组件）
原文链接：https://blog.csdn.net/qq_42612200/article/details/105860179
效果
POM.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
样式设置-》https://www.cnblogs.com/fqfanqi/p/6172223.html
MyExcelUtil类
public class MyExcelUtil {
/**
* 导出 Excel
*
* @param response HttpServletResponse 响应对象
* @param fileName 导出时的 项目名称
* @param getselectTo 需要要导出的数据
*/
public static void run(HttpServletResponse response,String fileName,List<collectionAndPaymentListBean> getselectTo) {
/** 第一步，创建一个Workbook，对应一个Excel文件 */
HSSFWorkbook wb = new HSSFWorkbook();
/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */
HSSFSheet sheet = wb.createSheet("excel导出标题");
/** 第三步，设置样式以及字体样式*/
//标题样式
HSSFCellStyle titleStyle = createTitleCellStyle(wb);
//创建表头样式
HSSFCellStyle headerStyle = createHeadCellStyle(wb);
//内容样式
HSSFCellStyle contentStyle = createContentCellStyle(wb);
// 日期格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//设置全局 单元格 默认 宽度
sheet.setDefaultColumnWidth(12);
/** 第四步，创建标题 ,合并标题单元格 */
// 行号
int rowNum = 0;
// 创建第一页的第一行，索引从0开始
HSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 800);// 设置行高
String title = "工程项目结算明细表";
HSSFCell c00 = row0.createCell(0);
c00.setCellValue(title);
c00.setCellStyle(titleStyle);
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));//标题合并单元格操作，17为总列数
// 第二行
HSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short) 500);
for (int i = 0; i < 18; i++) {
HSSFCell tempCell = row1.createCell(i);
tempCell.setCellStyle(headerStyle);
if (i == 0) {
tempCell.setCellValue("编制单位：广东东篱环境股份有限公司 ");
} else if (i == 5) {
tempCell.setCellStyle(headerStyle);
tempCell.setCellValue("制表日期: "+sdf.format(new Date()));
} else if(i == 9) {
tempCell.setCellValue(" 单位：人民币/元");
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 8));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 17));
// 第三行
for (collectionAndPaymentListBean c : getselectTo) {
HSSFRow row2 = sheet.createRow(rowNum++);
row2.setHeight((short) 500);
for (int i = 0; i < 18; i++) {
HSSFCell tempCell = row2.createCell(i);
tempCell.setCellStyle(headerStyle);
if (i == 0) {
tempCell.setCellValue("项目名称： "+c.getProjectName());
} else if (i == 8) {
tempCell.setCellValue("合同编号: "+c.getContractNo());
} else if(i == 14) {
tempCell.setCellValue("业主: ");
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 13));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 17));
break;
}
// 第四行
for (collectionAndPaymentListBean c : getselectTo) {
HSSFRow row3 = sheet.createRow(rowNum++);
row3.setHeight((short) 500);
for (int i = 0; i < 18; i++) {
HSSFCell tempCell = row3.createCell(i);
tempCell.setCellStyle(headerStyle);
if (i == 0) {
tempCell.setCellValue("合同总额： "+c.getContractNo());
} else if (i == 5) {
Date parse = null;
try {
parse = sdf.parse(c.getReceiveBillDateTo());
} catch (ParseException e) {
e.printStackTrace();
}
tempCell.setCellValue("合同日期: "+sdf.format(parse));
} else if(i == 10) {
tempCell.setCellValue("负责人: "+c.getProjectLeadNameTo());
}else if(i== 14){
tempCell.setCellValue("联系电话: "+c.getPhone());
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 9));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 10, 13));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 14, 17));
break;
}
// 第五行
HSSFRow row4 = sheet.createRow(rowNum++);
row4.setHeight((short) 500);
for (int i = 0; i < 18; i++) {
HSSFCell tempCell = row4.createCell(i);
tempCell.setCellStyle(headerStyle);
if (i == 0) {
tempCell.setCellValue("管理费率 ");
} else if (i == 3) {
tempCell.setCellValue("增值税率");
} else if(i == 6) {
tempCell.setCellValue("印花税率");
}else if(i== 9){
tempCell.setCellValue("资料保证金比率");
}else{
tempCell.setCellValue("");
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 2));
sheet.addMergedRegion(new CellRangeAddress(4, 4, 3, 5));
sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 8));
sheet.addMergedRegion(new CellRangeAddress(4, 4, 9, 11));
sheet.addMergedRegion(new CellRangeAddress(4, 4, 12, 17));
// 第六行
for (collectionAndPaymentListBean c : getselectTo) {
HSSFRow row5 = sheet.createRow(rowNum++);
row5.setHeight((short) 500);
for (int i = 0; i < 18; i++) {
HSSFCell tempCell = row5.createCell(i);
tempCell.setCellStyle(headerStyle);
if (i == 0) {
tempCell.setCellValue(c.getManagement());
} else if (i == 3) {
tempCell.setCellValue(c.getTaxes());
} else if(i == 6) {
tempCell.setCellValue(c.getDuty());
}else if(i== 9){
tempCell.setCellValue(c.getDatasets());
}else{
tempCell.setCellValue("");
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 2));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 3, 5));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 8));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 9, 11));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 12, 17));
break;
}
// 第七行 分割
rowNum++;
// 第八行
HSSFRow row7 = sheet.createRow(rowNum++);
row7.setHeight((short) 500);
for (int i = 0; i < 18; i++) {
HSSFCell tempCell = row7.createCell(i);
tempCell.setCellStyle(headerStyle);
if (i == 0) {
tempCell.setCellValue("序号 ");
}else if (i == 1) {
tempCell.setCellValue("'收款日期");
}else if(i == 2) {
tempCell.setCellValue("发票金额");
}else if(i== 3){
tempCell.setCellValue("业主拨款");
}else if(i== 4){
tempCell.setCellValue("管理费");
}else if(i== 5){
tempCell.setCellValue("税费");
}else if(i== 6){
tempCell.setCellValue("资料保证金");
}else if(i== 7){
tempCell.setCellValue("通讯费");
}else if(i== 8){
tempCell.setCellValue("交通费");
}else if(i== 9){
tempCell.setCellValue("办公费");
}else if(i== 10){
tempCell.setCellValue("保险费");
}else if(i== 11){
tempCell.setCellValue("差旅费");
}else if(i== 12){
tempCell.setCellValue("利息");
}else if(i== 13){
tempCell.setCellValue("履约保证金");
}else if(i== 14){
tempCell.setCellValue("保理手续费");
}else if(i== 15){
tempCell.setCellValue("工资社保公积金");
}else if(i== 16){
tempCell.setCellValue("扣款合计");
}else if(i== 17){
tempCell.setCellValue("实付款");
}
}
for (collectionAndPaymentListBean c : getselectTo) {
HSSFRow tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short) 500);
// 循环单元格填入数据
for (int j = 0; j < 18; j++) {
HSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
String tempValue = null;
if (j == 0) {
//序号
tempValue = c.getIsNewRecord();
} else if (j == 1) {
// 收款日期
if(null == c.getReceiveBillDate()){
tempValue = "";
}else{
tempValue = c.getReceiveBillDate().substring(0,10);
}
} else if (j == 2) {
// 发票金额
if(null == c.getAmount()){
tempValue = "0.00";
}else{
tempValue = c.getAmount()+"";
}
} else if (j == 3) {
// 业主拨款
if(null == c.getHomeowners()){
tempValue = "0.00";
}else{
tempValue = c.getHomeowners()+"";
}
} else if (j == 4) {
// 管理费
if(null == c.getCoffee()){
tempValue = "0.00";
}else{
tempValue = c.getCoffee()+"";
}
}else if (j == 5) {
// 税费
if(null == c.getVorhanden()){
tempValue = "0.00";
}else{
tempValue = c.getVorhanden()+"";
}
} else if(j == 6){
// 资料保证金
if(null == c.getSecurity()){
tempValue = "0.00";
}else{
tempValue = c.getSecurity()+"";
}
} else if(j == 7){
//通讯费
if(null == c.getCommunications()){
tempValue = "0.00";
}else{
tempValue = c.getCommunications()+"";
}
} else if(j == 8){
// 交通费
if(null == c.getTransportation()){
tempValue = "0.00";
}else{
tempValue = c.getTransportation()+"";
}
} else if(j == 9){
// 办公费
if(null == c.getWork()){
tempValue = "0.00";
}else{
tempValue = c.getWork()+"";
}
} else if(j == 10){
// 保险费
if(null == c.getSetAInsPremium()){
tempValue = "0.00";
}else{
tempValue = c.getSetAInsPremium()+"";
}
} else if(j == 11){
// 差旅费
if(null == c.getResearchTravel()){
tempValue = "0.00";
}else{
tempValue = c.getResearchTravel()+"";
}
} else if(j == 12){
// 利息
if(null == c.getAccrualAmount()){
tempValue = "0.00";
}else{
tempValue = c.getAccrualAmount()+"";
}
} else if(j == 13){
// 履约保证金
if(null == c.getPerformance()){
tempValue = "0.00";
}else{
tempValue = c.getPerformance()+"";
}
} else if(j == 14){
// 保理手续费
if(null == c.getFactoring()){
tempValue = "0.00";
}else{
tempValue = c.getFactoring()+"";
}
} else if(j == 15){
// 工资社保公积金
if(null == c.getCoffeehous()){
tempValue = "0.00";
}else{
tempValue = c.getCoffeehous()+"";
}
} else if(j == 16){
// 扣款合计
if(null == c.getOfdeductions()){
tempValue = "0.00";
}else{
tempValue = c.getOfdeductions()+"";
}
} else if(j == 17){
// 实付款
if(null == c.getPaymentImpl()){
tempValue = "0.00";
}else{
tempValue = c.getPaymentImpl()+"";
}
} else if(j == 18){
// 备注
tempValue = "0.00";
}
tempCell.setCellValue(tempValue);
}
}
try {
//调用下载 导出
downloadExcel(response,wb ,fileName+".xls");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//下载
public static void downloadExcel(HttpServletResponse response,HSSFWorkbook wb ,String fileName) throws IOException {
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName).getBytes(), "iso-8859-1"));
OutputStream out = null;
try {
out = response.getOutputStream();
wb.write(out);
out.flush();
} catch (IOException e) {
e.printStackTrace();
}finally {
out.close();
}
}
/**
* 创建标题样式
* @param wb
* @return
*/
private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setBold(true); //字体加粗
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建表头样式
* @param wb
* @return
*/
private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
cellStyle.setAlignment(HorizontalAlignment.LEFT); //水平居中
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); //上边框
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setBold(true); //字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
cellStyle.setFont(headerFont); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建内容样式
* @param wb
* @return
*/
private static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
HSSFCellStyle 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号字体
HSSFFont font = wb.createFont();
font.setColor((short)8);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
return cellStyle;
}
}
