JAVA无需JXL和POI用PageOffice自动生成Excel表格.docx

JAVA无需JXL和POI用PageOffice自动生成Excel表格很多情况下，软件开发者需要从数据库读取数据，然后将数据动态填充到手工预先准备好的Excel模板文件里，这对于生成复杂格式的Excel报表文件非常有用，这个功能应用PageOffice的基本动态填充功能即可实现。但若是用户想动态生成一个没有固定模版格式的Excel报表时，换句话说，没有办法事先准备一个固定格式的模板时，就需要开发人员用后台代码实现Excel报表的动态生成功能了，即通过后台代码在Excel的工作表上画出相应表格，实现Excel文件的从零到有。这里的“零”指的是Excel空白文件。下面我就如何通过后台代码实现在空白Excel文件中画表格，这一问题的具体步骤和大家分享一下。就以通过后台自动生成一张“出差开支预算表”为例来向大家介绍一下吧。第一步：拷贝文件到WEB项目的“WEB-INF/lib”目录下。拷贝PageOffice示例中下的“WEB-INF/lib”路径中的pageoffice.cab和pageoffice.jar到新建项目的“WEB-INF/lib”目录下。第二步：修改WEB项目的配置文件。将如下代码添加到配置文件中：poservercom.zhuozhengsoft.pageoffice.poserver.Serverposerver/poserver.doposerver/pageoffice.cabposerver/popdf.cabposerver/sealsetup.exeadminsealcom.zhuozhengsoft.pageoffice.poserver.AdminSealadminseal/adminseal.doadminseal/loginseal.doadminseal/sealimage.domhtmessage/rfc822adminseal-password123456第三步：在WEB项目的WebRoot目录下添加文件夹存放word模板文件，在此命名为“doc”，将要打开的空白Excel文件拷贝到该文件夹下，我要打开的Excel文件为“test.xls”。第四步：在WEB项目的WebRoot目录下添加动态页面excel.jsp。在该页面后台中添加自定义标签库：“”引入PageOffice类库：“”。在前台HTML页面中添加PageOfficeCtrl控件：“”，并设置控件所在层（）的高和宽示。第五步：在excel.jsp的后台页面，利用PageOfficeCtrl控件画出相应的Excel表格，部分代码如下：Workbook wb = new Workbook();/ 设置表格背景色Table backGroundTable = wb.openSheet(Sheet1).openTable(A1:P200);/ 设置表格边框颜色backGroundTable.getBorder().setLineColor(Color.white);/ 设置标题wb.openSheet(Sheet1).openTable(A1:H2).merge();/合并单元格/ 打开表格并设置行高wb.openSheet(Sheet1).openTable(A1:H2).setRowHeight(30);/ 定义单元格Cell A1 = wb.openSheet(Sheet1).openCell(A1);/ 设置单元格水平、垂直对齐方式A1.setHorizontalAlignment(XlHAlign.xlHAlignCenter);A1.setVerticalAlignment(XlVAlign.xlVAlignCenter);/ 设置单元格前景色A1.setForeColor(new Color(0, 128, 128);/给单元格赋值A1.setValue(出差开支预算);/设置字体：加粗、大小wb.openSheet(Sheet1).openTable(A1:A1).getFont().setBold(true);wb.openSheet(Sheet1).openTable(A1:A1).getFont().setSize(25);/ 画表头Border C4Border = wb.openSheet(Sheet1).openTable(C4:C4).getBorder();/ 设置表格边框的宽度、颜色C4Border.setWeight(XlBorderWeight.xlThick);C4Border.setLineColor(Color.yellow);/ 定义表格对象Table titleTable = wb.openSheet(Sheet1).openTable(B4:H5);/ 设置表格的边框样式、宽度、颜色titleTable.getBorder().setBorderType(XlBorderType.xlAllEdges);titleTable.getBorder().setWeight(XlBorderWeight.xlThick);titleTable.getBorder().setLineColor(new Color(0, 128, 128);/ 画表体Table bodyTable = wb.openSheet(Sheet1).openTable(B6:H15);bodyTable.getBorder().setLineColor(Color.gray);bodyTable.getBorder().setWeight(XlBorderWeight.xlHairline);Border B7Border = wb.openSheet(Sheet1).openTable(B7:B7).getBorder();B7Border.setLineColor(Color.white);. . .Table bodyTable2 = wb.openSheet(Sheet1).openTable(B6:H15);bodyTable2.getBorder().setWeight(XlBorderWeight.xlThick);bodyTable2.getBorder().setLineColor(new Color(0, 128, 128);bodyTable2.getBorder().setBorderType(XlBorderType.xlAllEdges);/ 画表尾Border H16H17Border = wb.openSheet(Sheet1).openTable(H16:H17).getBorder();H16H17Border.setLineColor(new Color(204, 255, 204);Border E16G17Border = wb.openSheet(Sheet1).openTable(E16:G17).getBorder();E16G17Border.setLineColor(new Color(0, 128, 128);Table footTable = wb.openSheet(Sheet1).openTable(B16:H17);footTable.getBorder().setWeight(XlBorderWeight.xlThick);footTable.getBorder().setLineColor(new Color(0, 128, 128);footTable.getBorder().setBorderType(XlBorderType.xlAllEdges);/ 设置表格的行高列宽wb.openSheet(Sheet1).openTable(A1:A1).setColumnWidth(1);wb.openSheet(Sheet1).openTable(B1:B1).setColumnWidth(20);. . .wb.openSheet(Sheet1).openTable(A16:A16).setRowHeight(20);wb.openSheet(Sheet1).openTable(A17:A17).setRowHeight(20);/ 设置表格中字体大小为10for (int i = 0; i 12; i+) /excel表格行号for (int j = 0; j 7; j+) /excel表格列号wb.openSheet(Sheet1).openCellRC(4 + i, 2 + j).getFont().setSize(10);/ 填充单元格背景颜色for (int i = 0; i 10; i+) wb.openSheet(Sheet1).openCell(H + (6 + i).setBackColor(new Color(255, 255, 153);wb.openSheet(Sheet1).openCell(E16).setBackColor(new Color(0, 128, 128);. . .wb.openSheet(Sheet1).openCell(H17).setBackColor(new Color(204, 255, 204);/填充单元格文本和公式Cell B4 = wb.openSheet(Sheet1).openCell(B4);B4.getFont().setBold(true);B4.setValue(出差开支预算);Cell H5 = wb.openSheet(Sheet1).openCell(H5);H5.getFont().setBold(true);H5.setValue(总计);H5.setHorizontalAlignment(XlHAlign.xlHAlignCenter);. . .Cell B15 = wb.openSheet(Sheet1).openCell(B15);B15.getFont().setBold(true);B15.getFont().setSize(10);B15.setValue(其他费用);wb.openSheet(Sheet1).openCell(C6).setValue(机票单价（往）);wb.openSheet(Sheet1).openCell(C7).setValue(机票单价（返）);. . ./ 设置单元格中的公式:setFormula(string)wb.openSheet(Sheet1).openCell(H15).setFormula(=D15*F15);for (int i = 0; i H16,低于预算,超出预算);E17.setVerticalAlignment(XlVAlign.xlVAlignCenter);Cell H16 = wb.openSheet(Sheet1).openCell(H16);H16.setVerticalAlignment(XlVAlign.xlVAlignCenter);H16.setNumberFormatLocal(￥#,#0.00;￥-#,#0.00);H16.getFont().setName(Arial);H16.getFont().setSize(11);H16.getFont().setBold(true);H16.setFormula(=SUM(H6:H15);Cell H17 = wb.openSheet(Sheet1).openCell(H17);H17.setVerticalAlignment(XlVAlign.xlVAlignCenter);H17.setNumberFormatLocal(￥#,#0.00;￥-#,#0.00);H17.getFont().setName(Arial);H17.getFont().setSize(11);H17.getFont().setBold(true);H17.setFormula(=(C4-H16);/ 填充数据Cell C4 = wb.openSheet(Sheet1).openCell(C4);C4.setNumberFormatLocal(￥#,#0.00;￥-#,#0.00);C4.setValue(2500);Cell D6 = wb.openSheet(Sheet1).openCell(D6);D6.setNumberFormatLocal(￥#,#0.00;￥-#,#0.00);D6.setValue(1200);wb.openSheet(Sheet1).openCell(F6).getFont().setSize(10);wb.openSheet(Sheet1).openCell(F6).setValue(1);Cell D7 = wb.openSheet(Sheet1).openCell(D7);D7.setNumberFormatLocal(￥#,#0.00;￥-#,#0.00);D7.setValue(875);wb.openSheet(Sheet1).openCell(F7).setValue(1);PageOfficeCtrl poCtrl1 = new PageOfficeCtrl(request);poCtrl1.setWriter(wb);poCtrl1.setServerPage(poserver.do)