使用poi手动拼接Excle复杂格式导出
先贴一下Excel的格式
如图，有各种合并单元格，边框有无，甚至有一个单元格中字体不同的问题。其中序号，验收项，评分，执行时间，执行人下的这些列要跟据获取到的list长度动态生成。在拼接的过程中中还遇到了合并单元格边框显示不全的问题，以下是使用poi完成此Excel的代码
还是先导入一下poi的jar吧
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11</version>
</dependency>
package cn.bluethink.eguan.okrtask.excelutil;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import cn.bluethink.eguan.model.core.EgTavern;
import cn.bluethink.eguan.model.okrtask.EgOkrTask;
import cn.bluethink.eguan.okrtask.entity.TaskKREntity;
/**
*工作任务导出Excel工具
*
/
public class Excel {
static int k =1;//用作序号
static int i=0;//循环生成表格
static int n=0;//取taskKRList内容使用
/*
* 导出
*/
public static void ExcelOut(EgOkrTask egOkrTask, List<TaskKREntity> taskKRList, HttpServletResponse response) throws Exception {
// 1、创建工作簿
HSSFWorkbook wb = new HSSFWorkbook();
// 上下左右边框 ，10 号 加粗 宋体 ，水平垂直居中 ，不换行
HSSFCellStyle style1 = createCellStyle(wb, (short) 10);
// 上下左右边框 ，12 号 不加粗 宋体 ，水平垂直居中，不换行
HSSFCellStyle style2 = createCellStyle(wb, (short) 12);
// 上下左右边框 ，12 号 不加粗 宋体 ，水平居左 、垂直居中，换行
HSSFCellStyle style3 = createCellStyleNeiRong(wb, (short) 12);
// 上左右边框 ，14 号 加粗 宋体 ，水平居左 、垂直居中，换行
HSSFCellStyle style4 = createCellStyle4(wb, (short) 14);
// 右边框 ，12 号 不加粗 宋体 ，水平居左 、垂直居中，换行
HSSFCellStyle style5 = createCellStyle5(wb, (short) 12);
// 无边框 ，12 号 加粗 宋体 ，居右 、垂直居中，不换行
HSSFCellStyle style6 = createCellStyle6(wb, (short) 12);
// 下，右边框 ，12 号 加粗 宋体 ，居右 、垂直居中，不换行
HSSFCellStyle style7 = createCellStyle7(wb, (short) 12);
// 下，右，上边框 ，16 号 加粗 宋体 ，水平居右 、垂直居中，不换行
HSSFCellStyle style8 = createCellStyle8(wb, (short) 16);
// 下，左，上边框 ，12 号 不加粗 宋体 ，水平居中 、垂直居中，不换行
HSSFCellStyle style9 = createCellStyle9(wb, (short) 12);
// 下，右，上边框 ，12 号 不加粗 宋体 ，水平居左 、垂直居中，不换行
HSSFCellStyle style10 = createCellStyle10(wb, (short) 12);
// 2、创建工作表
HSSFSheet sheet = wb.createSheet("工作任务|目标单");
// 设置默认列宽,行高
sheet.setDefaultColumnWidth(25);//默认宽度
sheet.setDefaultRowHeight((short) (13.5 * 20));
sheet.setColumnWidth(0, (int) (7 * 256 + 184));// 第一列宽度,excel中单元格的宽度为列的宽的（随便一想就知道）
sheet.setColumnWidth(1, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(2, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(3, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(4, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(5, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(6, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(7, (int) (3.5 * 256 + 184));
sheet.setColumnWidth(8, (int) (16.38 * 256 + 184));
sheet.setColumnWidth(9, (int) (9 * 256 + 184));
sheet.setColumnWidth(10, (int) (8.38 * 256 + 184));
sheet.setColumnWidth(11, (int) (14.25 * 256 + 184));
// 3、创建行
// 3.1、创建头标题行；并且设置头标题
HSSFRow row0 = sheet.createRow(0);
HSSFCell row0Cell0 = row0.createCell(0);
HSSFRow row1 = sheet.createRow(1);// 创建第二行，只是为了合并第一行，不做操作
// 加载单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
//下边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//左边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//上边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
// 创建字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short)16);
font.setFontName("华文新魏");
font.setColor((short)40);
style.setFont(font);
if(egOkrTask.getPos() instanceof EgTavern){
EgTavern EgTavern=(EgTavern) egOkrTask.getPos();
if(EgTavern!=null){
row0Cell0.setCellValue(EgTavern.getFaction().getName()==null?"":EgTavern.getFaction().getName());
}
}
row0.setHeight((short) (13.5 * 20));// 第一行高度
row1.setHeight((short) (13.5 * 20));// 第二行高度
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 1));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(0, 1, 0, 1), style);
HSSFCell row0Cell2 = row0.createCell(2);
Boolean btrain = egOkrTask.getBtrain()==null?false:egOkrTask.getBtrain();
String taskName=btrain?"培训":"工作"; //是否是训练任务
row0Cell2.setCellValue(taskName+"任务单");
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 11));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(0, 1, 2, 11), style8);
HSSFRow row2 = sheet.createRow(2);
row2.setHeight((short)(20*20));
HSSFCell row2Cell0 = row2.createCell(0);
row2Cell0.setCellValue("任务名称");
row2Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(2, 2, 0, 1), style2);
HSSFCell row2Cell2 = row2.createCell(2);
row2Cell2.setCellValue(egOkrTask.getName()==null?"":egOkrTask.getName());//任务名称
row2Cell2.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 7));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(2, 2, 2, 7), style2);
HSSFCell row2Cell8 = row2.createCell(8);
row2Cell8.setCellValue("任务编号");
row2Cell8.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(2, 2, 9, 11), style2);
HSSFRow row3 = sheet.createRow(3);
HSSFCell row3Cell0 = row3.createCell(0);
row3.setHeight((short)(20*20));
row3Cell0.setCellValue("所属项目");
row3Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(3, 3, 0, 1), style2);
HSSFCell row3Cell2 = row3.createCell(2);
if(egOkrTask.getCuser().getName()!=null){
row3Cell2.setCellValue(egOkrTask.getPos().getName());//所属项目 为 任务所属驿馆
row3Cell2.setCellStyle(style2);
}
sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 7));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(3, 3, 2, 7), style2);
HSSFCell row3Cell8 = row3.createCell(8);
row3Cell8.setCellStyle(style2);
row3Cell8.setCellValue("任务标识");
HSSFCell row3Cell9 = row3.createCell(9);
String timing=null;
switch (egOkrTask.getTiming()) {
case 0:
timing="日";
break;
case 1:
timing="周";
break;
case 2:
timing="月";
break;
case 3:
timing="季度";
break;
case 4:
timing="年";
break;
}
row3Cell9.setCellValue(timing);
row3Cell9.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 9, 11));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(3, 3, 9, 11), style2);
HSSFRow row4 = sheet.createRow(4);
row4.setHeight((short)(20*20));
HSSFCell row4Cell0 = row4.createCell(0);
row4Cell0.setCellValue("所需技能");
row4Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(4, 4, 0, 1), style2);
sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 11));//合并单元格
setRegionStyle(sheet, new CellRangeAddress(4, 4, 2, 11), style2);
HSSFRow row5 = sheet.createRow(5);
row5.setHeight((short)(13.5*20));
HSSFRow row6 = sheet.createRow(6);
row6.setHeight((short)(13.5*20));
HSSFRow row7 = sheet.createRow(7);
row7.setHeight((short)(13.5*20));
HSSFRow row8 = sheet.createRow(8);
row8.setHeight((short)(13.5*20));
HSSFRow row9 = sheet.createRow(9);
row9.setHeight((short)(13.5*20));
HSSFCell row5Cell0 = row5.createCell(0);
row5Cell0.setCellValue("任务描述");
row5Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(5, 9, 0, 1));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(5, 9, 0, 1), style2);
HSSFCell row5Cell2 = row5.createCell(2);
if(egOkrTask.getContent()!=null){
row5Cell2.setCellValue(egOkrTask.getContent()); //任务描述
row5Cell2.setCellStyle(style3);
}
sheet.addMergedRegion(new CellRangeAddress(5, 9, 2, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(5, 9, 2, 11), style3);
HSSFRow row10 = sheet.createRow(10);
row10.setHeight((short)(20*20));
HSSFCell row10Cell0 = row10.createCell(0);
row10Cell0.setCellValue("任务开始日期");
row10Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 1));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(10, 10, 0, 1), style2);
HSSFCell row10Cell2 = row10.createCell(2);
if(egOkrTask.getUtime()!=null){
String formatDate = formatDate(egOkrTask.getUtime());
row10Cell2.setCellValue(formatDate);//任务开始日期为 任务创建日期
row10Cell2.setCellStyle(style2);
}
sheet.addMergedRegion(new CellRangeAddress(10, 10, 2, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(10, 10, 2, 7), style2);
HSSFCell row10Cell8 = row10.createCell(8);
row10Cell8.setCellValue("发布人");
if(egOkrTask.getCuser().getName()!=null){
HSSFCell row10Cell9 = row10.createCell(9);
row10Cell9.setCellValue(egOkrTask.getCuser().getName());
row10Cell9.setCellStyle(style2);
}
row10Cell8.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(10, 10, 9, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(10, 10, 9, 11), style2);
HSSFRow row11 = sheet.createRow(11);
row11.setHeight((short)(20*20));
HSSFCell row11Cell0 = row11.createCell(0);
row11Cell0.setCellValue("预计结束时间");
row11Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(11, 11, 0, 1));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(11, 11, 0, 1), style2);
HSSFCell row11Cell2 = row11.createCell(2);
if(egOkrTask.getEtime()!=null){
String formatDate = formatDate(egOkrTask.getEtime());
row11Cell2.setCellValue(formatDate);//预计结束日期为 任务截止日期
row11Cell2.setCellStyle(style2);
}
sheet.addMergedRegion(new CellRangeAddress(11, 11, 2, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(11, 11, 2, 7), style2);
HSSFCell row11Cell8 = row11.createCell(8);
row11Cell8.setCellValue("执行人");
row11Cell8.setCellStyle(style2);
if(egOkrTask.getDouser().getName()!=null){
HSSFCell row11Cell9 = row11.createCell(9);
row11Cell9.setCellValue(egOkrTask.getDouser().getName());
row11Cell9.setCellStyle(style2);
}
sheet.addMergedRegion(new CellRangeAddress(11, 11, 9, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(11, 11, 9, 11), style2);
HSSFRow row12 = sheet.createRow(12);
row12.setHeight((short)(20*20));
HSSFCell row12Cell0 = row12.createCell(0);
row12Cell0.setCellValue("预估工作日(天)");
row12Cell0.setCellStyle(style3);
sheet.addMergedRegion(new CellRangeAddress(12, 12, 0, 1));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(12, 12, 0, 1), style2);
if(egOkrTask.getEtime()!=null && egOkrTask.getUtime()!=null){
long day=0;
day= (egOkrTask.getEtime().getTime()-egOkrTask.getUtime().getTime())/(24*60*60*1000)+1;
HSSFCell row12Cell2 = row12.createCell(2);
row12Cell2.setCellValue(day);
row12Cell2.setCellStyle(style2);
}
sheet.addMergedRegion(new CellRangeAddress(12, 12, 2, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(12, 12, 2, 7), style2);
HSSFCell row12Cell8 = row12.createCell(8);
row12Cell8.setCellValue("实际工作日(天)");
row12Cell8.setCellStyle(style3);
sheet.addMergedRegion(new CellRangeAddress(12, 12, 9, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(12, 12, 9, 11), style2);
HSSFRow row13 = sheet.createRow(13);
row13.setHeight((short)(20*20));
HSSFCell row13Cell0 = row13.createCell(0);
row13Cell0.setCellValue("序号");
row13Cell0.setCellStyle(style2);
HSSFCell row13Cell1 = row13.createCell(1);
row13Cell1.setCellValue("验收项");
row13Cell1.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(13, 13, 1, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(13, 13, 1, 7), style2);
HSSFCell row13Cell8 = row13.createCell(8);
row13Cell8.setCellValue("评分(0-100)分");
row13Cell8.setCellStyle(style2);
HSSFCell row13Cell9 = row13.createCell(9);
row13Cell9.setCellValue("执行时间");
row13Cell9.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(13, 13, 9, 10));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(13, 13, 9, 10), style2);
HSSFCell row13Cell11 = row13.createCell(11);
row13Cell11.setCellValue("执行人");
row13Cell11.setCellStyle(style2);
//此处单元格要循环执行生成同样格式的单元格
int size = taskKRList.size();
for(i=0;i<size*4;i=i+4){
if(n<size){
HSSFRow row14 = sheet.createRow(i+14);
row14.setHeight((short)(13.5*20));
HSSFCell row14Cell0 = row14.createCell(0);
row14Cell0.setCellValue(k);//序号
HSSFCell row14Cell1 = row14.createCell(1);
if(taskKRList.get(n).getContent()!=null){
row14Cell1.setCellValue(taskKRList.get(n).getContent()==null?"":taskKRList.get(n).getContent());//验收项
}
HSSFCell row14Cell8 = row14.createCell(8);
if(taskKRList.get(n).getScore()!=null){
row14Cell8.setCellValue( taskKRList.get(n).getScore()==null?null:taskKRList.get(n).getScore());//评分
}
HSSFCell row14Cell10 = row14.createCell(10);
if(taskKRList.get(n).getEtime()!=null){
String formatDate = formatDate(taskKRList.get(n).getEtime());
row14Cell10.setCellValue(formatDate);//计划时间 就是截止时间
row14Cell10.setCellStyle(style10);
}
HSSFCell row14Cell11 = row14.createCell(11);
if(taskKRList.get(n).getDouname()!=null){
row14Cell11.setCellValue(taskKRList.get(n).getDouname()==null?"":taskKRList.get(n).getDouname());//执行人
}
HSSFRow row15 = sheet.createRow(i+15);
row15.setHeight((short)(13.5*20));
HSSFRow row16 = sheet.createRow(i+16);
row16.setHeight((short)(16*20));
HSSFCell row16Cell10 = row16.createCell(10);
if(taskKRList.get(n).getFtime()!=null){
String formatDate = formatDate(taskKRList.get(n).getFtime());
row16Cell10.setCellValue(formatDate);//完成时间
row16Cell10.setCellStyle(style10);
}
HSSFRow row17 = sheet.createRow(i+17);
row17.setHeight((short)(16*20));
sheet.addMergedRegion(new CellRangeAddress(i+14, i+17, 0, 0));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+14, i+17, 0, 0), style2);
row14Cell0.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(i+14, i+15, 1, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+14, i+15, 1, 7), style2);
HSSFCell row16Cell1 = row16.createCell(1);
row16Cell1.setCellValue("备注:" );//taskKRList.get(n).getRemark()
row16Cell1.setCellStyle(style9);
HSSFCell row16Cell2 = row16.createCell(2);
row16Cell2.setCellValue(taskKRList.get(n).getRemark()==null?"":taskKRList.get(n).getRemark());
sheet.addMergedRegion(new CellRangeAddress(i+16, i+16, 2, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+16, i+16, 2, 7), style10);
HSSFCell row17Cell1 = row17.createCell(1);
row17Cell1.setCellValue("评价:" );//taskKRList.get(n).getEvaluate()
row17Cell1.setCellStyle(style9);
HSSFCell row17Cell2 = row17.createCell(2);
row17Cell2.setCellValue(taskKRList.get(n).getEvaluate()==null?"":taskKRList.get(n).getEvaluate());
sheet.addMergedRegion(new CellRangeAddress(i+17, i+17, 2, 7));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+17, i+17, 2, 7), style10);
sheet.addMergedRegion(new CellRangeAddress(i+14, i+17, 8, 8));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+14, i+17, 8, 8), style2);
HSSFCell row14Cell9 = row14.createCell(9);
row14Cell9.setCellValue("计划时间:");
sheet.addMergedRegion(new CellRangeAddress(i+14, i+15, 9, 9));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+14, i+15, 9, 9), style1);
row14Cell9.setCellStyle(style1);
HSSFCell row16Cell9 = row16.createCell(9);
row16Cell9.setCellValue("完成时间");
sheet.addMergedRegion(new CellRangeAddress(i+16, i+17, 9, 9));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+16, i+17, 9, 9), style1);
row16Cell9.setCellStyle(style1);
sheet.addMergedRegion(new CellRangeAddress(i+14, i+15, 10, 10));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+14, i+15, 10, 10), style1);
sheet.addMergedRegion(new CellRangeAddress(i+16, i+17, 10, 10));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+16, i+17, 10, 10), style1);
sheet.addMergedRegion(new CellRangeAddress(i+14, i+17, 11, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(i+14, i+17, 11, 11), style2);
k++;
n++;
}
}
int lastRow=i+14;//循环生成单元格最后一行
HSSFRow lastRow0 = sheet.createRow(lastRow);
HSSFCell lastRow0Cell0 = lastRow0.createCell(0);
lastRow0Cell0.setCellValue("验收意见:");
lastRow0.setHeight((short)(30 * 20));
sheet.addMergedRegion(new CellRangeAddress(lastRow, lastRow, 0, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(lastRow, lastRow, 0, 11), style4);
HSSFRow lastRow1 = sheet.createRow(lastRow+1);
lastRow1.setHeight((short)(63 * 20));
if(btrain){//true时为培训任务，若为培训任务，验收意见从表中导出
HSSFCell lastRow1Cell0 = lastRow1.createCell(0);
lastRow1Cell0.setCellValue(egOkrTask.getEvaluate()==null?"":egOkrTask.getEvaluate());
}
sheet.addMergedRegion(new CellRangeAddress(lastRow+1, lastRow+1, 0, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(lastRow+1, lastRow+1, 0, 11), style5);
HSSFRow lastRow2 = sheet.createRow(lastRow+2);
lastRow2.setHeight((short)(20 * 20));
HSSFCell lastRow2Cell0= lastRow2.createCell(0);
lastRow2Cell0.setCellValue("签名: ");
lastRow2Cell0.setCellStyle(style6);
sheet.addMergedRegion(new CellRangeAddress(lastRow+2, lastRow+2, 0, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(lastRow+2, lastRow+2, 0, 11), style6);
HSSFRow lastRow3 = sheet.createRow(lastRow+3);
lastRow3.setHeight((short)(20 * 20));
HSSFCell lastRow3Cell0= lastRow3.createCell(0);
lastRow3Cell0.setCellValue("验收日期： ");
lastRow3Cell0.setCellStyle(style7);
sheet.addMergedRegion(new CellRangeAddress(lastRow+3, lastRow+3, 0, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(lastRow+3, lastRow+3, 0, 11), style7);
HSSFRow lastRow4 = sheet.createRow(lastRow+4);
lastRow4.setHeight((short)(13.5 * 20));
HSSFCell lastRow4Cell0 = lastRow4.createCell(0);
lastRow4Cell0.setCellValue("备注:");
lastRow4Cell0.setCellStyle(style3);
HSSFRow lastRow5 = sheet.createRow(lastRow+5);
lastRow5.setHeight((short)(27 * 20));
sheet.addMergedRegion(new CellRangeAddress(lastRow+4, lastRow+5, 0, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(lastRow+4, lastRow+5, 0, 11), style3);
HSSFRow lastRow6 = sheet.createRow(lastRow+6);
lastRow6.setHeight((short)(13.5 * 20));
HSSFCell lastRow6Cell0 = lastRow6.createCell(0);
lastRow6Cell0.setCellValue("注：预估工作时间是指实际用于该人物时间，不是开始日期到预估结束日期的时间跨度。");
style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFRow lastRow7 = sheet.createRow(lastRow+7);
lastRow7.setHeight((short)(13.5 * 20));
sheet.addMergedRegion(new CellRangeAddress(lastRow+6, lastRow+7, 0, 11));//合并单元格
setRegionStyle(sheet,new CellRangeAddress(lastRow+6, lastRow+7, 0, 11), style1);
// 选择路径 filename 文件名
OutputStream os = response.getOutputStream();
response.reset();
response.setHeader("Content-Disposition",
"attachment;filename=" + new String("任务导出模板".getBytes("gbk"), "iso8859-1") + ".xlsx");// 解决表头编码问题
response.setContentType("application/msexcel");
wb.write(os);
os.close();
}
/**
* 创建单元格样式
*
* @param workbook
* 工作簿
* @param fontSize
* 字体大小
* @return 单元格样式
*/
private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
//下边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//左边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//上边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//右边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 创建字体
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
font.setFontName("宋体");
// 加载字体
style.setFont(font);
return style;
}
/*
* 内容样式
*/
private static HSSFCellStyle createCellStyleNeiRong(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
//下边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//左边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//上边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//右边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
// 创建字体
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
font.setFontName("宋体");
// 加载字体
style.setFont(font);
return style;
}
private static HSSFCellStyle createCellStyle4(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style4 = workbook.createCellStyle();
//左边框
style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//上边框
style4.setBorderTop(HSSFCellStyle.BORDER_THIN);
//右边框
style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
style4.setWrapText(true);
style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居左
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints(fontSize);
font.setFontName("宋体");
style4.setFont(font);
return style4;
}
private static HSSFCellStyle createCellStyle5(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style5 = workbook.createCellStyle();
style5.setWrapText(true);
style5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style5.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居左
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
HSSFFont font4 = workbook.createFont();
font4.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font4.setFontHeightInPoints(fontSize);
font4.setFontName("宋体");
style5.setFont(font4);
return style5;
}
private static HSSFCellStyle createCellStyle6(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style6 = workbook.createCellStyle();
style6.setBorderRight(HSSFCellStyle.BORDER_THIN);
style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//居右
style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
HSSFFont font3 = workbook.createFont();
font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font3.setFontHeightInPoints(fontSize);
font3.setFontName("宋体");
style6.setFont(font3);
return style6;
}
private static HSSFCellStyle createCellStyle7(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style7 = workbook.createCellStyle();
style7.setBorderRight(HSSFCellStyle.BORDER_THIN);
style7.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style7.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//居右
style7.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
HSSFFont font3 = workbook.createFont();
font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font3.setFontHeightInPoints(fontSize);
font3.setFontName("宋体");
style7.setFont(font3);
return style7;
}
private static HSSFCellStyle createCellStyle8(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style8 = workbook.createCellStyle();
style8.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
style8.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
//下边框
style8.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//右边框
style8.setBorderRight(HSSFCellStyle.BORDER_THIN);
//上边框
style8.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font5 = workbook.createFont();
font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font5.setFontHeightInPoints(fontSize);
font5.setFontName("宋体");
style8.setFont(font5);
return style8;
}
private static HSSFCellStyle createCellStyle9(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style9 = workbook.createCellStyle();
style9.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
style9.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
//下边框
style9.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//左边框
style9.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//上边框
style9.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font5 = workbook.createFont();
font5.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font5.setFontHeightInPoints(fontSize);
font5.setFontName("宋体");
style9.setFont(font5);
return style9;
}
private static HSSFCellStyle createCellStyle10(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle style10 = workbook.createCellStyle();
style10.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
style10.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居左
//下边框
style10.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//右边框
style10.setBorderRight(HSSFCellStyle.BORDER_THIN);
//上边框
style10.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font5 = workbook.createFont();
font5.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font5.setFontHeightInPoints(fontSize);
font5.setFontName("宋体");
style10.setFont(font5);
return style10;
}
//解决合并单元格边框问题
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region,
HSSFCellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
HSSFRow row = sheet.getRow(i);
if (row == null)
row = sheet.createRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
cell.setCellValue("");
}
cell.setCellStyle(cs);
}
}
}
//日期格式化
public static String formatDate(Date date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
String newDate = sdf.format(date);
return newDate;
}
}