前不久公司的一个项目里要用到Excel报表功能，因项目组中的兄弟都没搞过这个东西，所以走了不少弯路。现总结一下JAVA操作Excel的解决方案与大家分亨，有不正确的地方请高手指正。
JAVA操作Excel目前流行的技术有：
1： javascript;
2: Apache的poi;
3: jxl;
4: Jfreechart（与其它几种配合使用）
一：报表结构较简单且格式固定 这类报表建议创建模版，用poi/jxl/javascript读取模版里的内容，然后生成新的Excel文件（POI在读取文件和生成新文件过程中会丢失公式，必须在生成的新文件里重写公式），POI读取示例代码如下：
public
class
ReadModelDemo {
public
static
void
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;
public
class
CreateExcelDemo {
/**
*
@param
args
*/
public
static
void
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);
setCellValue(
"PII-ENG
采购申请流程控制分析报告
"
);
row = sheet.createRow((
short
)1);
row = sheet.createRow((
short
)2);
cell = row.createCell((
short
)0);
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>
个人觉得Apache和sun公司在对word和Excel的支持上做得不是很理想，虽然有一个poi和jxl，但使用起来还不如javascript 方便，更要命的是poi和jxl都不支持在Excel中画分析图。要画图的地方只能用模版或者是用jfreechart来画。如果谁有更好的解决方案，请一起交流交流。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
