前不久公司的一个项目里要用到Excel报表功能,因项目组中的兄弟都没搞过这个东西,所以走了不少弯路。现总结一下JAVA操作Excel的解决方案与大家分亨,有不正确的地方请高手指正。
JAVA操作Excel目前流行的技术有:
1: javascript;
2: Apache的poi;
3: jxl;
4: Jfreechart(与其它几种配合使用)
一:报表结构较简单且格式固定 这类报表建议创建模版,用poi/jxl/javascript读取模版里的内容,然后生成新的Excel文件(POI在读取文件和生成新文件过程中会丢失公式,必须在生成的新文件里重写公式),POI读取示例代码如下:
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 out = new FileOutputStream("C://test.xls");
// 把相应的Excel 工作簿存盘
workBook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二 报表结构不确定且复杂(带分析图)这类报表建议用poi/javascript写数据部分,分析图可以用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);
cell = row.createCell((short)2);
cell.setCellStyle(contentStyle);
cell.setCellValue(1);
cell = row.createCell((short)3);
cell.setCellStyle(percentStyle);
cell.setCellFormula("B8/(B8+C8)");
cell = row.createCell((short)4);
cell.setCellStyle(contentStyle);
cell.setCellValue(3);
cell = row.createCell((short)5);
cell.setCellStyle(contentStyle);
cell.setCellValue(5);
cell = row.createCell((short)6);
cell.setCellStyle(percentStyle);
cell.setCellFormula("E8/(E8+F8)");
cell = row.createCell((short)7);
cell.setCellStyle(contentStyle);
cell.setCellValue(0);
cell = row.createCell((short)8);
cell.setCellStyle(contentStyle);
cell.setCellValue(5);
cell = row.createCell((short)9);
cell.setCellStyle(percentStyle);
cell.setCellFormula("H8/(H8+I8)");
cell = row.createCell((short)10);
cell.setCellStyle(contentStyle);
cell.setCellValue(2);
cell = row.createCell((short)11);
cell.setCellStyle(contentStyle);
cell.setCellValue(0);
cell = row.createCell((short)12);
cell.setCellStyle(percentStyle);
cell.setCellFormula("K8/(K8+L8)");
cell = row.createCell((short)13);
cell.setCellStyle(contentStyle);
cell.setCellFormula("sum(b8+e8+h8+k8)");
cell = row.createCell((short)14);
cell.setCellStyle(contentStyle);
cell.setCellFormula("sum(c8+f8+i8+l8)");
cell = row.createCell((short)15);
cell.setCellStyle(contentStyle);
cell.setCellFormula("sum(n8+o8)");
cell = row.createCell((short)16);
cell.setCellFormula("n8/p8");
cell.setCellStyle(percentStyle);
cell = row.createCell((short)17);
cell.setCellValue(0.8);
cell.setCellStyle(percentStyle);
//HSSFChart chart = new HSSFChart();
//chart.createBarChart(workbook, sheet);
ChartRecord chart = new ChartRecord();
chart.setHeight(200);
chart.setWidth(400);
chart.setX(20);
chart.setY(20);
try {
FileOutputStream fileOut = new FileOutputStream("C://test.xls");
workbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
System.out.println(e.toString());
}
}
}
三 以下方法生成的分析图与数据是分离的,当数据区的值改变时,分析图是不会随之改变的,如果要一起改变,就只能全部用无所不能的javascript来Excel了。以下为简单示例:
<HTML>
<BODY>
<script lanage="javascript">
function CreateExcel()
{
var exceldemo = new ActiveXObject("Excel.Application");
exceldemo.Visible = true;
var workbook = exceldemo.Workbooks.Add();
var sheet = workbook.ActiveSheet;
sheet.Cells(1,1).Value = "1月";
sheet.Cells(2,1).Value = "2月";
sheet.Cells(3,1).Value = "3月";
sheet.Cells(4,1).Value = "4月";
sheet.Cells(1,2).Value = "120";
sheet.Cells(2,2).Value = "250";
sheet.Cells(3,2).Value = "310";
sheet.Cells(4,2).Value = "80";
exceldemo.Visible = true;
exceldemo.UserControl = true;
oResizeRange = sheet.Range("B1:B4");
var chart = sheet.Parent.Charts.Add();
chart.ChartWizard(oResizeRange, -4100, null, 2);
chart.SeriesCollection(1).XValues = sheet.Range("A1","A4");
chart.SeriesCollection(1).Name = '月报';
chart.Location(2, sheet.Name);
sheet.Shapes("Chart 1").Top = sheet.Rows(10).Top;
sheet.Shapes("Chart 1").Left = sheet.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="生成excel" οnclick="CreateExcel()"></P>
</BODY>
</HTML>
JAVA操作Excel目前流行的技术有:
1: javascript;
2: Apache的poi;
3: jxl;
4: Jfreechart(与其它几种配合使用)
一:报表结构较简单且格式固定 这类报表建议创建模版,用poi/jxl/javascript读取模版里的内容,然后生成新的Excel文件(POI在读取文件和生成新文件过程中会丢失公式,必须在生成的新文件里重写公式),POI读取示例代码如下:
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 out = new FileOutputStream("C://test.xls");
// 把相应的Excel 工作簿存盘
workBook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二 报表结构不确定且复杂(带分析图)这类报表建议用poi/javascript写数据部分,分析图可以用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);
cell = row.createCell((short)2);
cell.setCellStyle(contentStyle);
cell.setCellValue(1);
cell = row.createCell((short)3);
cell.setCellStyle(percentStyle);
cell.setCellFormula("B8/(B8+C8)");
cell = row.createCell((short)4);
cell.setCellStyle(contentStyle);
cell.setCellValue(3);
cell = row.createCell((short)5);
cell.setCellStyle(contentStyle);
cell.setCellValue(5);
cell = row.createCell((short)6);
cell.setCellStyle(percentStyle);
cell.setCellFormula("E8/(E8+F8)");
cell = row.createCell((short)7);
cell.setCellStyle(contentStyle);
cell.setCellValue(0);
cell = row.createCell((short)8);
cell.setCellStyle(contentStyle);
cell.setCellValue(5);
cell = row.createCell((short)9);
cell.setCellStyle(percentStyle);
cell.setCellFormula("H8/(H8+I8)");
cell = row.createCell((short)10);
cell.setCellStyle(contentStyle);
cell.setCellValue(2);
cell = row.createCell((short)11);
cell.setCellStyle(contentStyle);
cell.setCellValue(0);
cell = row.createCell((short)12);
cell.setCellStyle(percentStyle);
cell.setCellFormula("K8/(K8+L8)");
cell = row.createCell((short)13);
cell.setCellStyle(contentStyle);
cell.setCellFormula("sum(b8+e8+h8+k8)");
cell = row.createCell((short)14);
cell.setCellStyle(contentStyle);
cell.setCellFormula("sum(c8+f8+i8+l8)");
cell = row.createCell((short)15);
cell.setCellStyle(contentStyle);
cell.setCellFormula("sum(n8+o8)");
cell = row.createCell((short)16);
cell.setCellFormula("n8/p8");
cell.setCellStyle(percentStyle);
cell = row.createCell((short)17);
cell.setCellValue(0.8);
cell.setCellStyle(percentStyle);
//HSSFChart chart = new HSSFChart();
//chart.createBarChart(workbook, sheet);
ChartRecord chart = new ChartRecord();
chart.setHeight(200);
chart.setWidth(400);
chart.setX(20);
chart.setY(20);
try {
FileOutputStream fileOut = new FileOutputStream("C://test.xls");
workbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
System.out.println(e.toString());
}
}
}
三 以下方法生成的分析图与数据是分离的,当数据区的值改变时,分析图是不会随之改变的,如果要一起改变,就只能全部用无所不能的javascript来Excel了。以下为简单示例:
<HTML>
<BODY>
<script lanage="javascript">
function CreateExcel()
{
var exceldemo = new ActiveXObject("Excel.Application");
exceldemo.Visible = true;
var workbook = exceldemo.Workbooks.Add();
var sheet = workbook.ActiveSheet;
sheet.Cells(1,1).Value = "1月";
sheet.Cells(2,1).Value = "2月";
sheet.Cells(3,1).Value = "3月";
sheet.Cells(4,1).Value = "4月";
sheet.Cells(1,2).Value = "120";
sheet.Cells(2,2).Value = "250";
sheet.Cells(3,2).Value = "310";
sheet.Cells(4,2).Value = "80";
exceldemo.Visible = true;
exceldemo.UserControl = true;
oResizeRange = sheet.Range("B1:B4");
var chart = sheet.Parent.Charts.Add();
chart.ChartWizard(oResizeRange, -4100, null, 2);
chart.SeriesCollection(1).XValues = sheet.Range("A1","A4");
chart.SeriesCollection(1).Name = '月报';
chart.Location(2, sheet.Name);
sheet.Shapes("Chart 1").Top = sheet.Rows(10).Top;
sheet.Shapes("Chart 1").Left = sheet.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="生成excel" οnclick="CreateExcel()"></P>
</BODY>
</HTML>