java pageoffice 导出e_PageOffice动态生成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);

Border B9Border = wb.openSheet("Sheet1").openTable("B9:B9").getBorder();

B9Border.setBorderType(XlBorderType.xlBottomEdge);

B9Border.setLineColor(Color.white);

Border C6C15BorderLeft = wb.openSheet("Sheet1").openTable("C6:C15").getBorder();

C6C15BorderLeft.setLineColor(Color.white);

C6C15BorderLeft.setBorderType(XlBorderType.xlLeftEdge);

Border C6C15BorderRight = wb.openSheet("Sheet1").openTable("C6:C15").getBorder();

C6C15BorderRight.setLineColor(Color.yellow);

C6C15BorderRight.setLineStyle(XlBorderLineStyle.xlDot);

C6C15BorderRight.setBorderType(XlBorderType.xlRightEdge);

Border E6E15Border = wb.openSheet("Sheet1").openTable("E6:E15").getBorder();

E6E15Border.setLineStyle(XlBorderLineStyle.xlDot);

E6E15Border.setBorderType(XlBorderType.xlAllEdges);

E6E15Border.setLineColor(Color.yellow);

Border G6G15BorderRight = wb.openSheet("Sheet1").openTable("G6:G15").getBorder();

G6G15BorderRight.setBorderType(XlBorderType.xlRightEdge);

G6G15BorderRight.setLineColor(Color.white);

Border G6G15BorderLeft = wb.openSheet("Sheet1").openTable("G6:G15").getBorder();

G6G15BorderLeft.setLineStyle(XlBorderLineStyle.xlDot);

G6G15BorderLeft.setBorderType(XlBorderType.xlLeftEdge);

G6G15BorderLeft.setLineColor(Color.yellow);

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("C1:C1").setColumnWidth(15);

wb.openSheet("Sheet1").openTable("D1:D1").setColumnWidth(10);

wb.openSheet("Sheet1").openTable("E1:E1").setColumnWidth(8);

wb.openSheet("Sheet1").openTable("F1:F1").setColumnWidth(3);

wb.openSheet("Sheet1").openTable("G1:G1").setColumnWidth(12);

wb.openSheet("Sheet1").openTable("H1:H1").setColumnWidth(20);

wb.openSheet("Sheet1").openTable("A16:A16").setRowHeight(20);

wb.openSheet("Sheet1").openTable("A17:A17").setRowHeight(20);

// 设置表格中字体大小为10

for (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("F16").setBackColor(new Color(0, 128, 128));

wb.openSheet("Sheet1").openCell("G16").setBackColor(new Color(0, 128, 128));

wb.openSheet("Sheet1").openCell("E17").setBackColor(new Color(0, 128, 128));

wb.openSheet("Sheet1").openCell("F17").setBackColor(new Color(0, 128, 128));

wb.openSheet("Sheet1").openCell("G17").setBackColor(new Color(0, 128, 128));

wb.openSheet("Sheet1").openCell("H16").setBackColor(new Color(204, 255, 204));

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 B6 = wb.openSheet("Sheet1").openCell("B6");

B6.getFont().setBold(true);

B6.setValue("飞机票价");

Cell B9 = wb.openSheet("Sheet1").openCell("B9");

B9.getFont().setBold(true);

B9.setValue("酒店");

Cell B11 = wb.openSheet("Sheet1").openCell("B11");

B11.getFont().setBold(true);

B11.setValue("餐饮");

Cell B12 = wb.openSheet<