import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import static org.apache.poi.ss.usermodel.BorderStyle.DOUBLE;
public class ExcelUtil {
private HSSFWorkbook workbook;
private HSSFSheet sheet;
public ExcelUtil(){
this.workbook = new HSSFWorkbook();
this.sheet = this.workbook.createSheet("sheet");
}
public void create(){
//HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = this.workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
//setColumnWidth(short columnIndex, short width)
this.sheet.setColumnWidth(0, (short) (5.13*256)); // 4.42
this.sheet.setColumnWidth(1, (short) (5.72*256)); // 5
this.sheet.setColumnWidth(2, (short) (4.5*256)); // 3.79
this.sheet.setColumnWidth(3, (short) (6.13*256));
this.sheet.setColumnWidth(4, (short) (2.75*256));
this.sheet.setColumnWidth(5, (short) (6.25*256));
this.sheet.setColumnWidth(6, (short) (4.88*256));
this.sheet.setColumnWidth(7, (short) (9.38*256));
this.sheet.setColumnWidth(8, (short) (7.63*256));
this.sheet.setColumnWidth(9, (short) (5.63*256));
this.sheet.setColumnWidth(10, (short) (6.69*256));
this.sheet.setColumnWidth(11, (short) (3*256));
this.sheet.setColumnWidth(12, (short) (2.38*256));
this.sheet.setColumnWidth(13, (short) (12.59*256));
//HSSFSheet sheet = this.workbook.createSheet("sheet");
//第一行
HSSFRow row0 = this.sheet.createRow(0);
row0.setHeight((short) (30.75*20));
HSSFCell cell_0 = row0.createCell(0);
cell_0.setCellStyle(style);
cell_0.setCellValue("湖南湘桓工程检测有限公司");
//设置字体大小
setFontSize(cell_0,14,true,"黑体");
//合并单元格
this.sheet.addMergedRegion(cellRangeAddress(0, 0, 0, 13));
//第二行
HSSFRow row1 = this.sheet.createRow(1);
row1.setHeight((short) (27.75*20));
HSSFCell cell_1 = row1.createCell(0);
cell_1.setCellStyle(style);
cell_1.setCellValue("管材检验报告");
//设置字体大小
setFontSize(cell_1,18,true,"黑体");
//合并单元格
CellRangeAddress region_cell_1 = cellRangeAddress(1, 1, 0, 13);
this.sheet.addMergedRegion(region_cell_1);
/*RegionUtil.setBorderBottom(1, region_cell_1, this.sheet,this.workbook); // 下边框
RegionUtil.setBorderLeft(1, region_cell_1, this.sheet,this.workbook); // 左边框
RegionUtil.setBorderRight(1, region_cell_1, this.sheet,this.workbook); // 有边框
RegionUtil.setBorderTop(1, region_cell_1, this.sheet,this.workbook); // 上边框*/
//第三行
HSSFRow row2 = this.sheet.createRow(2);
row2.setHeight((short) (30*20));
HSSFCell cell_2_1 = row2.createCell(0);
cell_2_1.setCellStyle(style);
cell_2_1.setCellValue("工程编号");
//设置字体大小
setFontSize(cell_2_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_2_1 = cellRangeAddress(2, 2, 0, 1);
this.sheet.addMergedRegion(region_cell_2_1);
//设置边框
setBorder(region_cell_2_1);
HSSFCell cell_2_2 = row2.createCell(2);
cell_2_2.setCellStyle(style);
cell_2_2.setCellValue("S2021020");
//设置字体大小
setFontSize(cell_2_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_2_2 = cellRangeAddress(2, 2, 2, 4);
this.sheet.addMergedRegion(region_cell_2_2);
//设置边框
setBorder(region_cell_2_2);
HSSFCell cell_2_3 = row2.createCell(5);
cell_2_3.setCellStyle(style);
cell_2_3.setCellValue("委托单号");
//设置字体大小
setFontSize(cell_2_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_2_3 = cellRangeAddress(2, 2, 5, 6);
this.sheet.addMergedRegion(region_cell_2_3);
//设置边框
setBorder(region_cell_2_3);
HSSFCell cell_2_4 = row2.createCell(7);
cell_2_4.setCellStyle(style);
cell_2_4.setCellValue("S2021020-210607-3");
//设置字体大小
setFontSize(cell_2_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_2_4 = cellRangeAddress(2, 2, 7, 8);
this.sheet.addMergedRegion(region_cell_2_4);
//设置边框
setBorder(region_cell_2_4);
HSSFCell cell_2_5 = row2.createCell(9);
cell_2_5.setCellStyle(style);
cell_2_5.setCellValue("报告编号");
//设置字体大小
setFontSize(cell_2_5,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_2_5 = cellRangeAddress(2, 2, 9, 10);
this.sheet.addMergedRegion(region_cell_2_5);
//设置边框
setBorder(region_cell_2_5);
HSSFCell cell_2_6 = row2.createCell(11);
cell_2_6.setCellStyle(style);
cell_2_6.setCellValue("S2021020-Y1-12");
//设置字体大小
setFontSize(cell_2_6,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_2_6 = cellRangeAddress(2, 2, 11, 13);
this.sheet.addMergedRegion(region_cell_2_6);
//设置边框
setBorder(region_cell_2_6);
//第四行
HSSFRow row3 = this.sheet.createRow(3);
row3.setHeight((short) (30*20));
HSSFCell cell_3_1 = row3.createCell(0);
cell_3_1.setCellStyle(style);
cell_3_1.setCellValue("建设单位");
//设置字体大小
setFontSize(cell_3_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_3_1 = cellRangeAddress(3, 3, 0, 1);
this.sheet.addMergedRegion(region_cell_3_1);
//设置边框
setBorder(region_cell_3_1);
HSSFCell cell_3_2 = row3.createCell(2);
cell_3_2.setCellStyle(style);
cell_3_2.setCellValue("");
//设置字体大小
setFontSize(cell_3_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_3_2 = cellRangeAddress(3, 3, 2, 8);
this.sheet.addMergedRegion(region_cell_3_2);
//设置边框
setBorder(region_cell_3_2);
HSSFCell cell_3_3 = row3.createCell(9);
cell_3_3.setCellStyle(style);
cell_3_3.setCellValue("检测类别");
//设置字体大小
setFontSize(cell_3_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_3_3 = cellRangeAddress(3, 3, 9, 10);
this.sheet.addMergedRegion(region_cell_3_3);
//设置边框
setBorder(region_cell_3_3);
HSSFCell cell_3_4 = row3.createCell(11);
cell_3_4.setCellStyle(style);
cell_3_4.setCellValue("");
//设置字体大小
setFontSize(cell_3_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_3_4 = cellRangeAddress(3, 3, 11, 13);
this.sheet.addMergedRegion(region_cell_3_4);
//设置边框
setBorder(region_cell_3_4);
//第五行
HSSFRow row4 = this.sheet.createRow(4);
row4.setHeight((short) (30*20));
HSSFCell cell_4_1 = row4.createCell(0);
cell_4_1.setCellStyle(style);
cell_4_1.setCellValue("工程名称");
//设置字体大小
setFontSize(cell_4_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_4_1 = cellRangeAddress(4, 4, 0, 1);
this.sheet.addMergedRegion(region_cell_4_1);
//设置边框
setBorder(region_cell_4_1);
HSSFCell cell_4_2 = row4.createCell(2);
cell_4_2.setCellStyle(style);
cell_4_2.setCellValue("");
//设置字体大小
setFontSize(cell_4_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_4_2 = cellRangeAddress(4, 4, 2, 8);
this.sheet.addMergedRegion(region_cell_4_2);
//设置边框
setBorder(region_cell_4_2);
HSSFCell cell_4_3 = row4.createCell(9);
cell_4_3.setCellStyle(style);
cell_4_3.setCellValue("样品名称");
//设置字体大小
setFontSize(cell_4_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_4_3 = cellRangeAddress(4, 4, 9, 10);
this.sheet.addMergedRegion(region_cell_4_3);
//设置边框
setBorder(region_cell_4_3);
HSSFCell cell_4_4 = row4.createCell(11);
cell_4_4.setCellStyle(style);
cell_4_4.setCellValue("");
//设置字体大小
setFontSize(cell_4_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_4_4 = cellRangeAddress(4, 4, 11, 13);
this.sheet.addMergedRegion(region_cell_4_4);
//设置边框
setBorder(region_cell_4_4);
//第六行
HSSFRow row5 = this.sheet.createRow(5);
row5.setHeight((short) (30*20));
HSSFCell cell_5_1 = row5.createCell(0);
cell_5_1.setCellStyle(style);
cell_5_1.setCellValue("委托单位");
//设置字体大小
setFontSize(cell_5_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_5_1 = cellRangeAddress(5, 5, 0, 1);
this.sheet.addMergedRegion(region_cell_5_1);
//设置边框
setBorder(region_cell_5_1);
HSSFCell cell_5_2 = row5.createCell(2);
cell_5_2.setCellStyle(style);
cell_5_2.setCellValue("");
//设置字体大小
setFontSize(cell_5_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_5_2 = cellRangeAddress(5, 5, 2, 8);
this.sheet.addMergedRegion(region_cell_5_2);
//设置边框
setBorder(region_cell_5_2);
HSSFCell cell_5_3 = row5.createCell(9);
cell_5_3.setCellStyle(style);
cell_5_3.setCellValue("检测依据");
//设置字体大小
setFontSize(cell_5_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_5_3 = cellRangeAddress(5, 5, 9, 10);
this.sheet.addMergedRegion(region_cell_5_3);
//设置边框
setBorder(region_cell_5_3);
HSSFCell cell_5_4 = row5.createCell(11);
cell_5_4.setCellStyle(style);
cell_5_4.setCellValue("");
//设置字体大小
setFontSize(cell_5_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_5_4 = cellRangeAddress(5, 5, 11, 13);
this.sheet.addMergedRegion(region_cell_5_4);
//设置边框
setBorder(region_cell_5_4);
//第七行
HSSFRow row6 = this.sheet.createRow(6);
row6.setHeight((short) (30*20));
HSSFCell cell_6_1 = row6.createCell(0);
cell_6_1.setCellStyle(style);
cell_6_1.setCellValue("生产厂家");
//设置字体大小
setFontSize(cell_6_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_6_1 = cellRangeAddress(6, 6, 0, 1);
this.sheet.addMergedRegion(region_cell_6_1);
//设置边框
setBorder(region_cell_6_1);
HSSFCell cell_6_2 = row6.createCell(2);
cell_6_2.setCellStyle(style);
cell_6_2.setCellValue("");
//设置字体大小
setFontSize(cell_6_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_6_2 = cellRangeAddress(6, 6, 2, 8);
this.sheet.addMergedRegion(region_cell_6_2);
//设置边框
setBorder(region_cell_6_2);
HSSFCell cell_6_3 = row6.createCell(9);
cell_6_3.setCellStyle(style);
cell_6_3.setCellValue("样品编号");
//设置字体大小
setFontSize(cell_6_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_6_3 = cellRangeAddress(6, 6, 9, 10);
this.sheet.addMergedRegion(region_cell_6_3);
//设置边框
setBorder(region_cell_6_3);
HSSFCell cell_6_4 = row6.createCell(11);
cell_6_4.setCellStyle(style);
cell_6_4.setCellValue("");
//设置字体大小
setFontSize(cell_6_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_6_4 = cellRangeAddress(6, 6, 11, 13);
this.sheet.addMergedRegion(region_cell_6_4);
//设置边框
setBorder(region_cell_6_4);
//第八行
HSSFRow row7 = this.sheet.createRow(7);
row7.setHeight((short) (30*20));
HSSFCell cell_7_1 = row7.createCell(0);
cell_7_1.setCellStyle(style);
cell_7_1.setCellValue("样品规格");
//设置字体大小
setFontSize(cell_7_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_7_1 = cellRangeAddress(7, 7, 0, 1);
this.sheet.addMergedRegion(region_cell_7_1);
//设置边框
setBorder(region_cell_7_1);
HSSFCell cell_7_2 = row7.createCell(2);
cell_7_2.setCellStyle(style);
cell_7_2.setCellValue("");
//设置字体大小
setFontSize(cell_7_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_7_2 = cellRangeAddress(7, 7, 2, 8);
this.sheet.addMergedRegion(region_cell_7_2);
//设置边框
setBorder(region_cell_7_2);
HSSFCell cell_7_3 = row7.createCell(9);
cell_7_3.setCellStyle(style);
cell_7_3.setCellValue("工程部位");
//设置字体大小
setFontSize(cell_7_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_7_3 = cellRangeAddress(7, 7, 9, 10);
this.sheet.addMergedRegion(region_cell_7_3);
//设置边框
setBorder(region_cell_7_3);
HSSFCell cell_7_4 = row7.createCell(11);
cell_7_4.setCellStyle(style);
cell_7_4.setCellValue("");
//设置字体大小
setFontSize(cell_7_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_7_4 = cellRangeAddress(7, 7, 11, 13);
this.sheet.addMergedRegion(region_cell_7_4);
//设置边框
setBorder(region_cell_7_4);
//第九行
HSSFRow row8 = this.sheet.createRow(8);
row8.setHeight((short) (30*20));
HSSFCell cell_8_1 = row8.createCell(0);
cell_8_1.setCellStyle(style);
cell_8_1.setCellValue("代表数量");
//设置字体大小
setFontSize(cell_8_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_8_1 = cellRangeAddress(8, 8, 0, 1);
this.sheet.addMergedRegion(region_cell_8_1);
//设置边框
setBorder(region_cell_8_1);
HSSFCell cell_8_2 = row8.createCell(2);
cell_8_2.setCellStyle(style);
cell_8_2.setCellValue("1000m");
//设置字体大小
setFontSize(cell_8_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_8_2 = cellRangeAddress(8, 8, 2, 4);
this.sheet.addMergedRegion(region_cell_8_2);
//设置边框
setBorder(region_cell_8_2);
HSSFCell cell_8_3 = row8.createCell(5);
cell_8_3.setCellStyle(style);
cell_8_3.setCellValue("收样日期");
//设置字体大小
setFontSize(cell_8_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_8_3 = cellRangeAddress(8, 8, 5, 6);
this.sheet.addMergedRegion(region_cell_8_3);
//设置边框
setBorder(region_cell_8_3);
HSSFCell cell_8_4 = row8.createCell(7);
cell_8_4.setCellStyle(style);
cell_8_4.setCellValue("2021/6/7");
//设置字体大小
setFontSize(cell_8_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_8_4 = cellRangeAddress(8, 8, 7, 8);
this.sheet.addMergedRegion(region_cell_8_4);
//设置边框
setBorder(region_cell_8_3);
HSSFCell cell_8_5 = row8.createCell(9);
cell_8_5.setCellStyle(style);
cell_8_5.setCellValue("试验日期");
//设置字体大小
setFontSize(cell_8_5,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_8_5 = cellRangeAddress(8, 8, 9, 10);
this.sheet.addMergedRegion(region_cell_8_5);
//设置边框
setBorder(region_cell_8_5);
HSSFCell cell_8_6 = row8.createCell(11);
cell_8_6.setCellStyle(style);
cell_8_6.setCellValue("2021/6/7");
//设置字体大小
setFontSize(cell_8_6,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell_8_6 = cellRangeAddress(8, 8, 11, 13);
this.sheet.addMergedRegion(region_cell_8_6);
//设置边框
setBorder(region_cell_8_6);
//第十行
HSSFRow row9 = this.sheet.createRow(9);
row9.setHeight((short) (30*20));
HSSFCell cell_9 = row9.createCell(0);
cell_9.setCellStyle(style);
cell_9.setCellValue("试验结果");
//设置字体大小
setFontSize(cell_9,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_9 = cellRangeAddress(9, 9, 0, 13);
this.sheet.addMergedRegion(region_cell_9);
//设置边框
setBorder(region_cell_9);
//第十一行
HSSFRow row10 = this.sheet.createRow(10);
row10.setHeight((short) (30*20));
HSSFCell cell_10_1 = row10.createCell(0);
cell_10_1.setCellStyle(style);
cell_10_1.setCellValue("检测项目");
//设置字体大小
setFontSize(cell_10_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_10_1 = cellRangeAddress(10, 10, 0, 4);
this.sheet.addMergedRegion(region_cell_10_1);
//设置边框
setBorder(region_cell_10_1);
HSSFCell cell_10_2 = row10.createCell(5);
cell_10_2.setCellStyle(style);
cell_10_2.setCellValue("标准要求");
//设置字体大小
setFontSize(cell_10_2,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_10_2 = cellRangeAddress(10, 10, 5, 8);
this.sheet.addMergedRegion(region_cell_10_2);
//设置边框
setBorder(region_cell_10_2);
HSSFCell cell_10_3 = row10.createCell(9);
cell_10_3.setCellStyle(style);
cell_10_3.setCellValue("检测结果");
//设置字体大小
setFontSize(cell_10_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell_10_3 = cellRangeAddress(10, 10, 9, 12);
this.sheet.addMergedRegion(region_cell_10_3);
//设置边框
setBorder(region_cell_10_3);
HSSFCell cell_10_4 = row10.createCell(13);
//设置边框
style.setBorderBottom((short)1);
style.setBorderLeft((short)1);
style.setBorderRight((short)1);
style.setBorderTop((short)1);
cell_10_4.setCellStyle(style);
cell_10_4.setCellValue("单项评定");
//设置字体大小
setFontSize(cell_10_4,10,true,"黑体");
//检测结果有多少行
int start_number = 10;
int result_count = 5 ;
for (int i = 1 ; i <= result_count ; i++){
int rownum = start_number + i;
HSSFRow row = this.sheet.createRow(rownum);
row.setHeight((short) (30*20));
HSSFCell cell1 = row.createCell(0);
cell1.setCellStyle(style);
cell1.setCellValue("检测项目");
//设置字体大小
setFontSize(cell1,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell1 = cellRangeAddress(rownum, rownum, 0, 4);
this.sheet.addMergedRegion(region_cell1);
//设置边框
setBorder(region_cell1);
HSSFCell cell2 = row.createCell(5);
cell2.setCellStyle(style);
cell2.setCellValue("标准要求");
//设置字体大小
setFontSize(cell2,10,true,"黑体");
//合并单元格
CellRangeAddress region_cell2 = cellRangeAddress(rownum, rownum, 5, 8);
this.sheet.addMergedRegion(region_cell2);
//设置边框
setBorder(region_cell2);
HSSFCell cell3 = row.createCell(9);
cell3.setCellStyle(style);
cell3.setCellValue("检测结果");
//设置字体大小
setFontSize(cell3,10,false,"黑体");
//合并单元格
CellRangeAddress region_cell3 = cellRangeAddress(rownum, rownum, 9, 12);
this.sheet.addMergedRegion(region_cell3);
//设置边框
setBorder(region_cell3);
HSSFCell cell4 = row.createCell(13);
//设置边框
style.setBorderBottom((short)1);
style.setBorderLeft((short)1);
style.setBorderRight((short)1);
style.setBorderTop((short)1);
cell4.setCellStyle(style);
cell4.setCellValue("单项评定");
//设置字体大小
setFontSize(cell4,10,false,"黑体");
}
int restart_number = start_number + result_count; //重新开始的行数
System.out.println("restart_number="+restart_number);
//重置后--第一行
int rownum1 = restart_number+1;
HSSFRow arow1 = this.sheet.createRow(rownum1);
arow1.setHeight((short) (30*20));
HSSFCell acell = arow1.createCell(0);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
acell.setCellStyle(style);
acell.setCellValue("结论:");
//设置字体大小
setFontSize(acell,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell = cellRangeAddress(rownum1, rownum1, 0, 13);
this.sheet.addMergedRegion(region_acell);
//设置边框
setBorder(region_acell);
//重置后--第二行
int rownum2 = restart_number+2;
HSSFRow arow2 = this.sheet.createRow(rownum2);
arow2.setHeight((short) (54*20));
HSSFCell acell2 = arow2.createCell(0);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
acell2.setCellStyle(style);
acell2.setCellValue("");
//设置字体大小
setFontSize(acell2,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell2 = cellRangeAddress(rownum2, rownum2, 0, 13);
this.sheet.addMergedRegion(region_acell2);
//设置边框
setBorder(region_acell2);
//重置后--第三行
int rownum3 = restart_number+3;
HSSFRow arow3 = this.sheet.createRow(rownum3);
arow3.setHeight((short) (30*20));
HSSFCell acell_3_1 = arow3.createCell(0);
acell_3_1.setCellStyle(style);
acell_3_1.setCellValue("批准");
//设置字体大小
setFontSize(acell_3_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell_3_1 = cellRangeAddress(rownum3, rownum3, 0, 1);
this.sheet.addMergedRegion(region_acell_3_1);
//设置边框
setBorder(region_acell_3_1);
HSSFCell acell_3_2 = arow3.createCell(2);
acell_3_2.setCellStyle(style);
acell_3_2.setCellValue("");
//设置字体大小
setFontSize(acell_3_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_acell_3_2 = cellRangeAddress(rownum3, rownum3, 2, 3);
this.sheet.addMergedRegion(region_acell_3_2);
//设置边框
setBorder(region_acell_3_2);
HSSFCell acell_3_3 = arow3.createCell(4);
acell_3_3.setCellStyle(style);
acell_3_3.setCellValue("审核");
//设置字体大小
setFontSize(acell_3_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell_3_3 = cellRangeAddress(rownum3, rownum3, 4, 5);
this.sheet.addMergedRegion(region_acell_3_3);
//设置边框
setBorder(region_acell_3_3);
HSSFCell acell_3_4 = arow3.createCell(6);
acell_3_4.setCellStyle(style);
acell_3_4.setCellValue("");
//设置字体大小
setFontSize(acell_3_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_acell_3_4 = cellRangeAddress(rownum3, rownum3, 6, 7);
this.sheet.addMergedRegion(region_acell_3_4);
//设置边框
setBorder(region_acell_3_4);
HSSFCell acell_3_5 = arow3.createCell(8);
acell_3_5.setCellStyle(style);
acell_3_5.setCellValue("检测");
//设置字体大小
setFontSize(acell_3_5,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell_3_5 = cellRangeAddress(rownum3, rownum3, 8, 9);
this.sheet.addMergedRegion(region_acell_3_5);
//设置边框
setBorder(region_acell_3_5);
HSSFCell acell_3_6 = arow3.createCell(10);
acell_3_6.setCellStyle(style);
acell_3_6.setCellValue("");
//设置字体大小
setFontSize(acell_3_6,10,false,"黑体");
//合并单元格
CellRangeAddress region_acell_3_6 = cellRangeAddress(rownum3, rownum3, 10, 13);
this.sheet.addMergedRegion(region_acell_3_6);
//设置边框
setBorder(region_acell_3_6);
//重置后--第四行
int rownum4 = restart_number+4;
HSSFRow arow4 = this.sheet.createRow(rownum4);
arow4.setHeight((short) (30*20));
HSSFCell acell_4_1 = arow4.createCell(0);
acell_4_1.setCellStyle(style);
acell_4_1.setCellValue("报告日期");
//设置字体大小
setFontSize(acell_4_1,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell_4_1 = cellRangeAddress(rownum4, rownum4, 0, 1);
this.sheet.addMergedRegion(region_acell_4_1);
//设置边框
setBorder(region_acell_4_1);
HSSFCell acell_4_2 = arow4.createCell(2);
acell_4_2.setCellStyle(style);
acell_4_2.setCellValue("");
//设置字体大小
setFontSize(acell_4_2,10,false,"黑体");
//合并单元格
CellRangeAddress region_acell_4_2 = cellRangeAddress(rownum4, rownum4, 2, 3);
this.sheet.addMergedRegion(region_acell_4_2);
//设置边框
setBorder(region_acell_4_2);
HSSFCell acell_4_3 = arow4.createCell(4);
acell_4_3.setCellStyle(style);
acell_4_3.setCellValue("送样人");
//设置字体大小
setFontSize(acell_4_3,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell_4_3 = cellRangeAddress(rownum4, rownum4, 4, 5);
this.sheet.addMergedRegion(region_acell_4_3);
//设置边框
setBorder(region_acell_4_3);
HSSFCell acell_4_4 = arow4.createCell(6);
acell_4_4.setCellStyle(style);
acell_4_4.setCellValue("");
//设置字体大小
setFontSize(acell_4_4,10,false,"黑体");
//合并单元格
CellRangeAddress region_acell_4_4 = cellRangeAddress(rownum4, rownum4, 6, 7);
this.sheet.addMergedRegion(region_acell_4_4);
//设置边框
setBorder(region_acell_4_4);
HSSFCell acell_4_5 = arow4.createCell(8);
acell_4_5.setCellStyle(style);
acell_4_5.setCellValue("见证人单位及姓名");
//设置字体大小
setFontSize(acell_4_5,10,true,"黑体");
//合并单元格
CellRangeAddress region_acell_4_5 = cellRangeAddress(rownum4, rownum4, 8, 9);
this.sheet.addMergedRegion(region_acell_4_5);
//设置边框
setBorder(region_acell_4_5);
HSSFCell acell_4_6 = arow4.createCell(10);
acell_4_6.setCellStyle(style);
acell_4_6.setCellValue("");
//设置字体大小
setFontSize(acell_4_6,10,false,"黑体");
//合并单元格
CellRangeAddress region_acell_4_6 = cellRangeAddress(rownum4, rownum4, 10, 13);
this.sheet.addMergedRegion(region_acell_4_6);
//设置边框
setBorder(region_acell_4_6);
//重置后--第五行
int rownum5 = restart_number+5;
HSSFRow arow5 = this.sheet.createRow(rownum5);
arow5.setHeight((short) (20*20));
HSSFCell acell_5_1 = arow5.createCell(0);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//--重置设置边框
style.setBorderBottom((short)0);
style.setBorderLeft((short)0);
style.setBorderRight((short)0);
style.setBorderTop((short)0);
acell_5_1.setCellStyle(style);
acell_5_1.setCellValue("注:1.本报告检测数据手写、涂改无效；复制报告未加盖本单位公章无效；");
//设置字体大小
setFontSize(acell_5_1,10,true,"黑体");
//合并单元格
this.sheet.addMergedRegion(cellRangeAddress(rownum5, rownum5, 0, 10));
HSSFCell acell_5_2 = arow5.createCell(11);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
acell_5_2.setCellStyle(style);
acell_5_2.setCellValue("共1页,第1页");
//设置字体大小
setFontSize(acell_5_2,10,true,"黑体");
//合并单元格
this.sheet.addMergedRegion(cellRangeAddress(rownum5, rownum5, 11, 13));
//重置后--第六行
int rownum6 = restart_number+6;
HSSFRow arow6 = this.sheet.createRow(rownum6);
arow6.setHeight((short) (20*20));
HSSFCell acell_6_1 = arow6.createCell(0);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
acell_6_1.setCellStyle(style);
acell_6_1.setCellValue(" 2.报告无“CMA章”和“报告专用章”无效；无检测、审核、批准人签字无效；");
//设置字体大小
setFontSize(acell_6_1,10,true,"黑体");
//合并单元格
this.sheet.addMergedRegion(cellRangeAddress(rownum6, rownum6, 0, 13));
//重置后--第七行
int rownum7 = restart_number+7;
HSSFRow arow7 = this.sheet.createRow(rownum7);
arow7.setHeight((short) (20*20));
HSSFCell acell_7_1 = arow7.createCell(0);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
acell_7_1.setCellStyle(style);
acell_7_1.setCellValue(" 3.对报告若有异议，应在报告发出后15个工作日内提出；");
//设置字体大小
setFontSize(acell_7_1,10,true,"黑体");
//合并单元格
this.sheet.addMergedRegion(cellRangeAddress(rownum7, rownum7, 0, 13));
File file = new File("D:\JAVAWORK\demo3\demo.xlsx");
FileOutputStream fout = null;
try {
fout = new FileOutputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
this.workbook.write(fout);
} catch (IOException e) {
e.printStackTrace();
}
try {
fout.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//设置字体和大小
protected HSSFFont createFont(int fontSize,boolean bold ,String fontName){
HSSFFont f0 = this.workbook.createFont();
f0.setFontHeightInPoints((short) fontSize);//字号
if(bold){
f0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
}
f0.setFontName(fontName);
return f0;
}
//合并单元格
protected CellRangeAddress cellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol){
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
return region;
}
/**
* 设置单元格字体大小
*/
private void setFontSize(Cell cell,int fontSize,boolean bold ,String fontName) {
Font font = this.workbook.createFont();
font.setFontName(fontName);
font.setFontHeightInPoints((short)fontSize);
if(bold){
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
}
//解决单元格样式覆盖的问题
CellStyle cStyle = this.workbook.createCellStyle();
cStyle.cloneStyleFrom(cell.getCellStyle());
cStyle.setWrapText(true);
cStyle.setFont(font);
cell.setCellStyle(cStyle);
}
private void setBorder(CellRangeAddress cellRangeAddress){
RegionUtil.setBorderBottom(1, cellRangeAddress, this.sheet,this.workbook); // 下边框
RegionUtil.setBorderLeft(1, cellRangeAddress, this.sheet,this.workbook); // 左边框
RegionUtil.setBorderRight(1, cellRangeAddress, this.sheet,this.workbook); // 有边框
RegionUtil.setBorderTop(1, cellRangeAddress, this.sheet,this.workbook); // 上边框
}
}