@Test
public void test1() {
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFFont font = workbook.createFont();
// font.setBold();
font.setFontHeightInPoints((short)14);
//设置样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
// cellStyle.setAlignment(H);
HSSFSheet sheet = workbook.createSheet("月报表测试");
CellRangeAddress cellRangeAddress =new CellRangeAddress(0, 0, 0, 11);
sheet.addMergedRegion(cellRangeAddress);
//第一行 index0
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString("杭州商道科技有限公司"));
//第二行
cellRangeAddress =new CellRangeAddress(1, 1, 3, 6);
sheet.addMergedRegion(cellRangeAddress);
row = sheet.createRow(1);
HSSFCell dateCell = row.createCell(3);
dateCell.setCellType(HSSFCell.CELL_TYPE_STRING);
dateCell.setCellStyle(cellStyle);
dateCell.setCellValue("时间间隔XXX");
//第三行
row = sheet.createRow(2);
row.createCell(0).setCellValue("一、");
row.createCell(1).setCellValue("基本资料");
//第四行
row = sheet.createRow(3);
row.createCell(1).setCellValue("小区名称");
cellRangeAddress =new CellRangeAddress(3, 3, 2, 11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("XXXXXXX");
row = sheet.createRow(4);
row.createCell(1).setCellValue("座落地点：");
cellRangeAddress =new CellRangeAddress(4, 4, 2, 11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");
//第6行
row = sheet.createRow(5);
row.createCell(1).setCellValue("建成年月：");
cellRangeAddress =new CellRangeAddress(5, 5, 2, 4);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("年月日：xxxxx");
row.createCell(5).setCellValue("联系人");
cellRangeAddress =new CellRangeAddress(5, 5, 6, 8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(6).setCellValue("XXX");
row.createCell(9).setCellValue("电话");
cellRangeAddress =new CellRangeAddress(5, 5, 10, 11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(10).setCellValue("XXX");
//第7行
row = sheet.createRow(6);
row.createCell(1).setCellValue("住户：");
row.createCell(2).setCellValue("(XX)");
row.createCell(3).setCellValue("(户)");
cellRangeAddress =new CellRangeAddress(6, 6, 4, 5);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(4).setCellValue("共计（ ）");
row.createCell(6).setCellValue("幢");
cellRangeAddress =new CellRangeAddress(6, 6, 7, 8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("发卡张数");
cellRangeAddress =new CellRangeAddress(6, 6, 9, 10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("xxxx");
//第9行
row = sheet.createRow(8);
row.createCell(0).setCellValue("二、");
cellRangeAddress =new CellRangeAddress(8, 8, 1, 2);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(1).setCellValue("维修用材料台账");
row.createCell(6).setCellValue("三、");
cellRangeAddress =new CellRangeAddress(8, 8, 7, 9);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("维修工时记录");
//第10行
row = sheet.createRow(9);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");
row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
//填充数据
for (int i =0; i <10; i++) {
row = sheet.createRow(9 + i +1);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");
row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
}
//第n+10行
row = sheet.createRow(9 +10 +1);
//cellRangeAddress=new CellRangeAddress(19,19,0,4);
//sheet.addMergedRegion(cellRangeAddress);
row.createCell(0).setCellValue("累计:");
row.createCell(1).setCellValue("xxx");
row.createCell(7).setCellValue("累计:");
row.createCell(8).setCellValue("xxx");
CreateXl createXl =new CreateXl();
//输出文件流
try {
FileOutputStream outputStream =new FileOutputStream(createXl.outputFile);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
System.out.println("文件生成。。。。。。");
}catch (Exception e) {
System.out.println("已经运行。。。。");
e.printStackTrace();
}
}
class CreateXlextends HttpServlet {
public StringoutputFile ="/Users/ginger/Desktop/test.xls";
private void createCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {
HSSFCell cell = row.createCell(col);
cell.setCellValue(val);
HSSFCellStyle cellStyle = wb.createCellStyle();
// cellStyle.setAlignment(HSSFCellStyle.);
cell.setCellStyle(cellStyle);
}
}
