//表题
//表头
//数据
//合并区域
//是否显示网格线
//行号
//外边框加粗
//内边框普通
//设置打印区域
//打印设置
public static Workbook getWorkbook(ProjectStatReporter reporter){
List<String> x=reporter.getX();
List<String> f=reporter.getStatField();
List<List<String>> y=reporter.getY();
Workbook workbook = new HSSFWorkbook();
Sheet sheet=workbook.createSheet();
//x行 和度量值
Row rowx=sheet.createRow(0);
Row rowf=sheet.createRow(1);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
for(int i=0;i<x.size();i++){
//x
RichTextString richString = new HSSFRichTextString(x.get(i));
richString.applyFont(font);
createCell(workbook, rowx, i*f.size()+1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,richString);
//度量值
for(int j=0;j<f.size();j++){
RichTextString richString2 = new HSSFRichTextString(f.get(j));
richString2.applyFont(font);
createCell(workbook, rowf, i*f.size()+1+j, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,richString2);
}
}
//y数据
for(int i=0;i<y.size();i++){
Row row=sheet.createRow(i+2);
for(int j=0;j<y.get(i).size();j++){
if(j==0){//第一列 字体加粗
RichTextString richString = new HSSFRichTextString(y.get(i).get(j));
richString.applyFont(font);
createCell(workbook, row, j, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,richString);
}else{
createCell(workbook, row, j, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,y.get(i).get(j));
}
}
}
//合并区域
sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));//左上角的空白
for(int i=0;i<x.size();i++){//合并x
sheet.addMergedRegion(new CellRangeAddress(0,0,i*f.size()+1,i*f.size()+1+f.size()-1));
}
//样式:不显示网格线 居中 行号25.5 外边框加粗 内边框普通 row0 1 字体宋体加粗 col 1 字体宋体加粗
//不显示网格线
sheet.setDisplayGridlines(false);
//居中 row0 1 字体宋体加粗 col 1 字体宋体加粗
//调用createcell()
//行号25.5
for(int i=0;i<2+y.size();i++){
Row row=sheet.getRow(i);
row.setHeightInPoints((float) 25.5);
}
//外边框加粗
CellRangeAddress region=new CellRangeAddress(0,2+y.size()-1,0,x.size()*f.size());
RegionUtil.setBorderBottom(CellStyle.BORDER_THICK, region, sheet, workbook);
RegionUtil.setBorderLeft(CellStyle.BORDER_THICK, region, sheet, workbook);
RegionUtil.setBorderRight(CellStyle.BORDER_THICK, region, sheet, workbook);
RegionUtil.setBorderTop(CellStyle.BORDER_THICK, region, sheet, workbook);
//内边框普通
//调用createcell() 中设置
//返回
//测试
Sheet sheet1 = workbook.createSheet("new sheet");
sheet1.groupRow( 5, 14 );
sheet1.groupRow( 7, 14 );
sheet1.groupRow( 16, 19 );
sheet1.groupColumn( (short)4, (short)7 );
sheet1.groupColumn( (short)9, (short)12 );
sheet1.groupColumn( (short)10, (short)11 );
return workbook;
}
private static void createCell(Workbook wb, Row row, int column, short halign, short valign,String text){
Cell cell = row.createCell(column);
cell.setCellValue(text);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cell.setCellStyle(cellStyle);
}
private static void createCell(Workbook wb, Row row, int column, short halign, short valign,RichTextString richText){
Cell cell = row.createCell(column);
cell.setCellValue(richText);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cell.setCellStyle(cellStyle);
}