publicclass ReadModelDemo {
publicstaticvoid createExcelFromTemplate()
{
// 读取模板Excel
HSSFWorkbook workBook = null;
try {
workBook = new HSSFWorkbook(new FileInputStream("C://model.xls"));
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//得到这个workbook模版后,就可以插入数据了
//......
workBook.createSheet().createRow(6).createCell((short)2).setCellValue(15);
//......
try {// 新建一输出文件流
FileOutputStream ōut = new FileOutputStream("C://test.xls");
// 把相应的Excel 工作簿存盘
workBook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二 报表结构不确定且复杂(带分析图)这类报表建议用poi/javascrīpt写数据部分,分析图可以用jfreechart来画,画完后插入到excel中(poi 3.0以后支持图片插入)以下示例为POI写数据和格式的部分,jfreechart 生成图片及插入到Excel的代码略:
import java.io.FileOutputStream;
import org.apache.poi.hssf.record.ChartRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFChart;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.Region;
publicclass CreateExcelDemo {
/**
*@paramargs
*/
publicstaticvoid main(String[] args) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell((short)0);
//表名样式
HSSFCellStyle titlecellstyle = workbook.createCellStyle();
titlecellstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐
HSSFFont titleFont = workbook.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
titleFont.setUnderline(HSSFFont.U_SINGLE); //单下画线
titleFont.setFontName("Arial"); //字体
titleFont.setFontHeightInPoints((short)14); //大小
titlecellstyle.setFont(titleFont);
//表头样式
HSSFCellStyle formTitleStyle = workbook.createCellStyle();
formTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
formTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
formTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
formTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
formTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont formTitleFont = workbook.createFont();
formTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
formTitleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short)10);
formTitleStyle.setFont(formTitleFont);
//表内容样式
HSSFCellStyle contentStyle = workbook.createCellStyle();
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont contentFont = workbook.createFont();
contentFont.setFontName("Arial");
contentFont.setFontHeightInPoints((short)12);
contentStyle.setFont(contentFont);
//百分数显示样式
HSSFCellStyle percentStyle = workbook.createCellStyle();
percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
percentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
percentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
percentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
percentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
percentStyle.setDataFormat((short)9);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont percentFont = workbook.createFont();
percentFont.setFontName("Arial");
percentStyle.setFont(percentFont);
//写入内容
cell.setCellStyle(titlecellstyle);
cell.setCellValue("PII-ENG采购申请流程控制分析报告");
row = sheet.createRow((short)1);
row = sheet.createRow((short)2);
cell = row.createCell((short)0);
cell.setCellValue("报告日期:2007-10-25 (月报)");
row = sheet.createRow((short)3);
HSSFRichTextString str1 = new HSSFRichTextString("作业标准:(1) HKD0-10万(14日);" +
"(2) HKD10万-50万(14日);(3) >=HKD50万(37日); (4) 豁免申请(37日)");
HSSFRichTextString str2 = new HSSFRichTextString("指标:作业标准内完成数量/总数量>=80%");
row.createCell((short)0).setCellValue(str1);
sheet.createRow((short)4).createCell((short)0).setCellValue(str2);
row=sheet.createRow((short)5);
cell = row.createCell((short)0);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("时段"));
cell = row.createCell((short)1);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("HKD0-10万"));
cell = row.createCell((short)4);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("HKD10万-50万"));
cell = row.createCell((short)7);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString(">HKD50万"));
cell = row.createCell((short)10);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("豁免申请"));
cell = row.createCell((short)13);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("当月合计"));
cell = row.createCell((short)17);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("指标"));
row = sheet.createRow((short)6);
cell = row.createCell((short)1);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("<=14日"));
cell = row.createCell((short)2);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString(">14日"));
cell = row.createCell((short)3);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("达标率"));
cell = row.createCell((short)4);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("<=14日"));
cell = row.createCell((short)5);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString(">14日"));
cell = row.createCell((short)6);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("达标率"));
cell = row.createCell((short)7);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("<=37日"));
cell = row.createCell((short)8);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString(">37日"));
cell = row.createCell((short)9);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("达标率"));
cell = row.createCell((short)10);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("<=37日"));
cell = row.createCell((short)11);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString(">37日"));
cell = row.createCell((short)12);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("达标率"));
cell = row.createCell((short)13);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("达标数"));
cell = row.createCell((short)14);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("不达标数"));
cell = row.createCell((short)15);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("合计"));
cell = row.createCell((short)16);
cell.setCellStyle(formTitleStyle);
cell.setCellValue(new HSSFRichTextString("合计达标率"));
//合并单元格
sheet.addMergedRegion(new Region(5,(short)0,6,(short)0));//(赵始行,起始列,结束行,结束列)
sheet.addMergedRegion(new Region(5,(short)1,5,(short)3));
sheet.addMergedRegion(new Region(5,(short)4,5,(short)6));
sheet.addMergedRegion(new Region(5,(short)7,5,(short)9));
sheet.addMergedRegion(new Region(5,(short)10,5,(short)12));
sheet.addMergedRegion(new Region(5,(short)13,5,(short)16));
sheet.addMergedRegion(new Region(5,(short)17,6,(short)17));
//加入数据,设置公式
row = sheet.createRow((short)7);
cell = row.createCell((short)0);
cell.setCellStyle(contentStyle);
cell.setCellValue(new HSSFRichTextString("1月"));
cell = row.createCell((short)1);
cell.setCellStyle(contentStyle);
cell.setCellValue(256);