PageOffice 开发者中心
目录

动态生成Excel文件

# 动态生成Excel文件

  • 查看本示例演示效果
  • 本示例关键代码的编写位置，请参考“开始 - 快速上手”里您所使用的开发语言框架的最简集成代码

注意

本文中展示的代码均为关键代码，复制粘贴到您的项目中，按照实际的情况，例如文档路径，用户名等做适当修改即可使用。

使用WorkbookWriter及其相关类，用纯代码编程的方式在一个空白的Excel文件中“绘制”一个带公式、带数据、带丰富样式的表格。

# 后端代码

    WorkbookWriter wb = new WorkbookWriter();
// 设置背景
ExcelTableWriter 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);
ExcelCellWriter 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);

// 画表头
ExcelBorder C4Border = wb.openSheet("Sheet1").openTable("C4:C4").getBorder();
C4Border.setWeight(XlBorderWeight.xlThick);
C4Border.setLineColor(Color.yellow);

ExcelTableWriter 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));

// 画表体
ExcelTableWriter bodyTable = wb.openSheet("Sheet1").openTable("B6:H15");
bodyTable.getBorder().setLineColor(Color.gray);
bodyTable.getBorder().setWeight(XlBorderWeight.xlHairline);

ExcelBorder B7Border = wb.openSheet("Sheet1").openTable("B7:B7").getBorder();
B7Border.setLineColor(Color.white);

ExcelBorder B9Border = wb.openSheet("Sheet1").openTable("B9:B9").getBorder();
B9Border.setBorderType(XlBorderType.xlBottomEdge);
B9Border.setLineColor(Color.white);

ExcelBorder C6C15BorderLeft = wb.openSheet("Sheet1").openTable("C6:C15").getBorder();
C6C15BorderLeft.setLineColor(Color.white);
C6C15BorderLeft.setBorderType(XlBorderType.xlLeftEdge);

ExcelBorder C6C15BorderRight = wb.openSheet("Sheet1").openTable("C6:C15").getBorder();
C6C15BorderRight.setLineColor(Color.yellow);
C6C15BorderRight.setLineStyle(XlBorderLineStyle.xlDot);
C6C15BorderRight.setBorderType(XlBorderType.xlRightEdge);

ExcelBorder E6E15Border = wb.openSheet("Sheet1").openTable("E6:E15").getBorder();
E6E15Border.setLineStyle(XlBorderLineStyle.xlDot);
E6E15Border.setBorderType(XlBorderType.xlAllEdges);
E6E15Border.setLineColor(Color.yellow);

ExcelBorder G6G15BorderRight = wb.openSheet("Sheet1").openTable("G6:G15").getBorder();
G6G15BorderRight.setBorderType(XlBorderType.xlRightEdge);
G6G15BorderRight.setLineColor(Color.white);

ExcelBorder G6G15BorderLeft = wb.openSheet("Sheet1").openTable("G6:G15").getBorder();
G6G15BorderLeft.setLineStyle(XlBorderLineStyle.xlDot);
G6G15BorderLeft.setBorderType(XlBorderType.xlLeftEdge);
G6G15BorderLeft.setLineColor(Color.yellow);

ExcelTableWriter 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);

// 画表尾
ExcelBorder H16H17Border = wb.openSheet("Sheet1").openTable("H16:H17").getBorder();
H16H17Border.setLineColor(new Color(204, 255, 204));

ExcelBorder E16G17Border = wb.openSheet("Sheet1").openTable("E16:G17").getBorder();
E16G17Border.setLineColor(new Color(0, 128, 128));

ExcelTableWriter 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));

//填充单元格文本和公式
ExcelCellWriter B4 = wb.openSheet("Sheet1").openCell("B4");
B4.getFont().setBold(true);
B4.setValue("出差开支预算");
ExcelCellWriter H5 = wb.openSheet("Sheet1").openCell("H5");
H5.getFont().setBold(true);
H5.setValue("总计");
H5.setHorizontalAlignment(XlHAlign.xlHAlignCenter);
ExcelCellWriter B6 = wb.openSheet("Sheet1").openCell("B6");
B6.getFont().setBold(true);
B6.setValue("飞机票价");
ExcelCellWriter B9 = wb.openSheet("Sheet1").openCell("B9");
B9.getFont().setBold(true);
B9.setValue("酒店");
ExcelCellWriter B11 = wb.openSheet("Sheet1").openCell("B11");
B11.getFont().setBold(true);
B11.setValue("餐饮");
ExcelCellWriter B12 = wb.openSheet("Sheet1").openCell("B12");
B12.getFont().setBold(true);
B12.setValue("交通费用");
ExcelCellWriter B13 = wb.openSheet("Sheet1").openCell("B13");
B13.getFont().setBold(true);
B13.setValue("休闲娱乐");
ExcelCellWriter B14 = wb.openSheet("Sheet1").openCell("B14");
B14.getFont().setBold(true);
B14.setValue("礼品");
ExcelCellWriter 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("机票单价（返）");
wb.openSheet("Sheet1").openCell("C8").setValue("其他");
wb.openSheet("Sheet1").openCell("C9").setValue("每晚费用");
wb.openSheet("Sheet1").openCell("C10").setValue("其他");
wb.openSheet("Sheet1").openCell("C11").setValue("每天费用");
wb.openSheet("Sheet1").openCell("C12").setValue("每天费用");
wb.openSheet("Sheet1").openCell("C13").setValue("总计");
wb.openSheet("Sheet1").openCell("C14").setValue("总计");
wb.openSheet("Sheet1").openCell("C15").setValue("总计");

wb.openSheet("Sheet1").openCell("G6").setValue("  张");
wb.openSheet("Sheet1").openCell("G7").setValue("  张");
wb.openSheet("Sheet1").openCell("G9").setValue("  晚");
wb.openSheet("Sheet1").openCell("G10").setValue("  晚");
wb.openSheet("Sheet1").openCell("G11").setValue("  天");
wb.openSheet("Sheet1").openCell("G12").setValue("  天");

wb.openSheet("Sheet1").openCell("H6").setFormula("=D6*F6");
wb.openSheet("Sheet1").openCell("H7").setFormula("=D7*F7");
wb.openSheet("Sheet1").openCell("H8").setFormula("=D8*F8");
wb.openSheet("Sheet1").openCell("H9").setFormula("=D9*F9");
wb.openSheet("Sheet1").openCell("H10").setFormula("=D10*F10");
wb.openSheet("Sheet1").openCell("H11").setFormula("=D11*F11");
wb.openSheet("Sheet1").openCell("H12").setFormula("=D12*F12");
wb.openSheet("Sheet1").openCell("H13").setFormula("=D13*F13");
wb.openSheet("Sheet1").openCell("H14").setFormula("=D14*F14");
wb.openSheet("Sheet1").openCell("H15").setFormula("=D15*F15");

for (int i = 0; i < 10; i++) {
    //设置数据以货币形式显示
    wb.openSheet("Sheet1").openCell("D" + (6 + i)).setNumberFormatLocal("￥#,##0.00;￥-#,##0.00");
    wb.openSheet("Sheet1").openCell("H" + (6 + i)).setNumberFormatLocal("￥#,##0.00;￥-#,##0.00");
}

ExcelCellWriter E16 = wb.openSheet("Sheet1").openCell("E16");
E16.getFont().setBold(true);
E16.getFont().setSize(11);
E16.setForeColor(Color.white);
E16.setValue("出差开支总费用");
E16.setVerticalAlignment(XlVAlign.xlVAlignCenter);
ExcelCellWriter E17 = wb.openSheet("Sheet1").openCell("E17");
E17.getFont().setBold(true);
E17.getFont().setSize(11);
E17.setForeColor(Color.white);
E17.setFormula("=IF(C4>H16,\"低于预算\",\"超出预算\")");
E17.setVerticalAlignment(XlVAlign.xlVAlignCenter);
ExcelCellWriter 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)");
ExcelCellWriter 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)");

// 填充数据
ExcelCellWriter C4 = wb.openSheet("Sheet1").openCell("C4");
C4.setNumberFormatLocal("￥#,##0.00;￥-#,##0.00");
C4.setValue("2500");
ExcelCellWriter 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");
ExcelCellWriter D7 = wb.openSheet("Sheet1").openCell("D7");
D7.setNumberFormatLocal("￥#,##0.00;￥-#,##0.00");
D7.setValue("875");
wb.openSheet("Sheet1").openCell("F7").setValue("1");

PageOfficeCtrl poCtrl = new PageOfficeCtrl(request);
poCtrl.setWriter(wb);
poCtrl.webOpen("D:\\test.xlsx", OpenModeType.xlsNormalEdit, "张三");

    PageOfficeNetCore.Excel.WorkbookWriter wb = new PageOfficeNetCore.Excel.WorkbookWriter();
// 设置背景
PageOfficeNetCore.Excel.ExcelTableWriter backGroundTable = wb.OpenSheet("Sheet1").OpenTable("A1:P200");
backGroundTable.Border.LineColor = Color.White;

// 设置标题
wb.OpenSheet("Sheet1").OpenTable("A1:H2").Merge();
wb.OpenSheet("Sheet1").OpenTable("A1:H2").RowHeight = 30;
PageOfficeNetCore.Excel.ExcelCellWriter A1 = wb.OpenSheet("Sheet1").OpenCell("A1");
A1.HorizontalAlignment = PageOfficeNetCore.ExcelWriter.XlHAlign.xlHAlignCenter;
A1.VerticalAlignment = PageOfficeNetCore.ExcelWriter.XlVAlign.xlVAlignCenter;
A1.ForeColor = Color.FromArgb(0, 128, 128);
A1.Value = "出差开支预算";
A1.Font.Bold = true;
A1.Font.Size = 25;

#region 画表头
// 画表头
PageOfficeNetCore.Excel.ExcelBorder C4Border = wb.OpenSheet("Sheet1").OpenTable("C4:C4").Border;
C4Border.Weight = PageOfficeNetCore.ExcelWriter.XlBorderWeight.xlThick;
C4Border.LineColor = Color.Yellow;

PageOfficeNetCore.Excel.ExcelTableWriter titleTable = wb.OpenSheet("Sheet1").OpenTable("B4:H5");
titleTable.Border.Weight = PageOfficeNetCore.ExcelWriter.XlBorderWeight.xlThick;
titleTable.Border.LineColor = Color.FromArgb(0, 128, 128);
titleTable.Border.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlAllEdges;
#endregion

#region 画表体
// 画表体
PageOfficeNetCore.Excel.ExcelTableWriter bodyTable = wb.OpenSheet("Sheet1").OpenTable("B6:H15");
bodyTable.Border.LineColor = Color.Gray;
bodyTable.Border.Weight = PageOfficeNetCore.ExcelWriter.XlBorderWeight.xlHairline;

PageOfficeNetCore.Excel.ExcelBorder B7Border = wb.OpenSheet("Sheet1").OpenTable("B7:B7").Border;
B7Border.LineColor = Color.White;

PageOfficeNetCore.Excel.ExcelBorder B9Border = wb.OpenSheet("Sheet1").OpenTable("B9:B9").Border;
B9Border.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlBottomEdge;
B9Border.LineColor = Color.White;

PageOfficeNetCore.Excel.ExcelBorder C6C15BorderLeft = wb.OpenSheet("Sheet1").OpenTable("C6:C15").Border;
C6C15BorderLeft.LineColor = Color.White;
C6C15BorderLeft.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlLeftEdge;
PageOfficeNetCore.Excel.ExcelBorder C6C15BorderRight = wb.OpenSheet("Sheet1").OpenTable("C6:C15").Border;
C6C15BorderRight.LineColor = Color.Yellow;
C6C15BorderRight.LineStyle = PageOfficeNetCore.ExcelWriter.XlBorderLineStyle.xlDot;
C6C15BorderRight.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlRightEdge;

PageOfficeNetCore.Excel.ExcelBorder E6E15Border = wb.OpenSheet("Sheet1").OpenTable("E6:E15").Border;
E6E15Border.LineStyle = PageOfficeNetCore.ExcelWriter.XlBorderLineStyle.xlDot;
E6E15Border.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlAllEdges;
E6E15Border.LineColor = Color.Yellow;

PageOfficeNetCore.Excel.ExcelBorder G6G15BorderRight = wb.OpenSheet("Sheet1").OpenTable("G6:G15").Border;
G6G15BorderRight.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlRightEdge;
G6G15BorderRight.LineColor = Color.White;
PageOfficeNetCore.Excel.ExcelBorder G6G15BorderLeft = wb.OpenSheet("Sheet1").OpenTable("G6:G15").Border;
G6G15BorderLeft.LineStyle = PageOfficeNetCore.ExcelWriter.XlBorderLineStyle.xlDot;
G6G15BorderLeft.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlLeftEdge;
G6G15BorderLeft.LineColor = Color.Yellow;

PageOfficeNetCore.Excel.ExcelTableWriter bodyTable2 = wb.OpenSheet("Sheet1").OpenTable("B6:H15");
bodyTable2.Border.Weight = PageOfficeNetCore.ExcelWriter.XlBorderWeight.xlThick;
bodyTable2.Border.LineColor = Color.FromArgb(0, 128, 128);
bodyTable2.Border.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlAllEdges;
#endregion

#region 画表尾
// 画表尾
PageOfficeNetCore.Excel.ExcelBorder H16H17Border = wb.OpenSheet("Sheet1").OpenTable("H16:H17").Border;
H16H17Border.LineColor = Color.FromArgb(204, 255, 204);

PageOfficeNetCore.Excel.ExcelBorder E16G17Border = wb.OpenSheet("Sheet1").OpenTable("E16:G17").Border;
E16G17Border.LineColor = Color.FromArgb(0, 128, 128);

PageOfficeNetCore.Excel.ExcelTableWriter footTable = wb.OpenSheet("Sheet1").OpenTable("B16:H17");
footTable.Border.Weight = PageOfficeNetCore.ExcelWriter.XlBorderWeight.xlThick;
footTable.Border.LineColor = Color.FromArgb(0, 128, 128);
footTable.Border.BorderType = PageOfficeNetCore.ExcelWriter.XlBorderType.xlAllEdges;
#endregion

#region 设置行高列宽
// 设置行高列宽
wb.OpenSheet("Sheet1").OpenTable("A1:A1").ColumnWidth = 1;
wb.OpenSheet("Sheet1").OpenTable("B1:B1").ColumnWidth = 20;
wb.OpenSheet("Sheet1").OpenTable("C1:C1").ColumnWidth = 15;
wb.OpenSheet("Sheet1").OpenTable("D1:D1").ColumnWidth = 10;
wb.OpenSheet("Sheet1").OpenTable("E1:E1").ColumnWidth = 8;
wb.OpenSheet("Sheet1").OpenTable("F1:F1").ColumnWidth = 3;
wb.OpenSheet("Sheet1").OpenTable("G1:G1").ColumnWidth = 12;
wb.OpenSheet("Sheet1").OpenTable("H1:H1").ColumnWidth = 20;

wb.OpenSheet("Sheet1").OpenTable("A16:A16").RowHeight = 20;
wb.OpenSheet("Sheet1").OpenTable("A17:A17").RowHeight = 20;
#endregion

// 设置表格中字体大小为10
for (int i = 0; i < 12; i++)
{
    for (int j = 0; j < 7; j++)
    {
        wb.OpenSheet("Sheet1").OpenCellRC(4 + i, 2 + j).Font.Size = 10;
    }
}

#region 填充单元格背景颜色

// 填充单元格背景颜色
for (int i = 0; i < 10; i++)
{
    wb.OpenSheet("Sheet1").OpenCell("H" + (6 + i).ToString()).BackColor = Color.FromArgb(255, 255, 153);
}

wb.OpenSheet("Sheet1").OpenCell("E16").BackColor = Color.FromArgb(0, 128, 128);
wb.OpenSheet("Sheet1").OpenCell("F16").BackColor = Color.FromArgb(0, 128, 128);
wb.OpenSheet("Sheet1").OpenCell("G16").BackColor = Color.FromArgb(0, 128, 128);
wb.OpenSheet("Sheet1").OpenCell("E17").BackColor = Color.FromArgb(0, 128, 128);
wb.OpenSheet("Sheet1").OpenCell("F17").BackColor = Color.FromArgb(0, 128, 128);
wb.OpenSheet("Sheet1").OpenCell("G17").BackColor = Color.FromArgb(0, 128, 128);

wb.OpenSheet("Sheet1").OpenCell("H16").BackColor = Color.FromArgb(204, 255, 204);
wb.OpenSheet("Sheet1").OpenCell("H17").BackColor = Color.FromArgb(204, 255, 204);
#endregion

#region 填充单元格文本和公式

//填充单元格文本和公式
PageOfficeNetCore.Excel.ExcelCellWriter B4 = wb.OpenSheet("Sheet1").OpenCell("B4");
B4.Font.Bold = true;
B4.Value = "出差开支预算";
PageOfficeNetCore.Excel.ExcelCellWriter H5 = wb.OpenSheet("Sheet1").OpenCell("H5");
H5.Font.Bold = true;
H5.Value = "总计";
H5.HorizontalAlignment = PageOfficeNetCore.ExcelWriter.XlHAlign.xlHAlignCenter;
PageOfficeNetCore.Excel.ExcelCellWriter B6 = wb.OpenSheet("Sheet1").OpenCell("B6");
B6.Font.Bold = true;
B6.Value = "飞机票价";
PageOfficeNetCore.Excel.ExcelCellWriter B9 = wb.OpenSheet("Sheet1").OpenCell("B9");
B9.Font.Bold = true;
B9.Value = "酒店";
PageOfficeNetCore.Excel.ExcelCellWriter B11 = wb.OpenSheet("Sheet1").OpenCell("B11");
B11.Font.Bold = true;
B11.Value = "餐饮";
PageOfficeNetCore.Excel.ExcelCellWriter B12 = wb.OpenSheet("Sheet1").OpenCell("B12");
B12.Font.Bold = true;
B12.Value = "交通费用";
PageOfficeNetCore.Excel.ExcelCellWriter B13 = wb.OpenSheet("Sheet1").OpenCell("B13");
B13.Font.Bold = true;
B13.Value = "休闲娱乐";
PageOfficeNetCore.Excel.ExcelCellWriter B14 = wb.OpenSheet("Sheet1").OpenCell("B14");
B14.Font.Bold = true;
B14.Value = "礼品";
PageOfficeNetCore.Excel.ExcelCellWriter B15 = wb.OpenSheet("Sheet1").OpenCell("B15");
B15.Font.Bold = true;
B15.Font.Size = 10;
B15.Value = "其他费用";

wb.OpenSheet("Sheet1").OpenCell("C6").Value = "机票单价（往）";
wb.OpenSheet("Sheet1").OpenCell("C7").Value = "机票单价（返）";
wb.OpenSheet("Sheet1").OpenCell("C8").Value = "其他";
wb.OpenSheet("Sheet1").OpenCell("C9").Value = "每晚费用";
wb.OpenSheet("Sheet1").OpenCell("C10").Value = "其他";
wb.OpenSheet("Sheet1").OpenCell("C11").Value = "每天费用";
wb.OpenSheet("Sheet1").OpenCell("C12").Value = "每天费用";
wb.OpenSheet("Sheet1").OpenCell("C13").Value = "总计";
wb.OpenSheet("Sheet1").OpenCell("C14").Value = "总计";
wb.OpenSheet("Sheet1").OpenCell("C15").Value = "总计";

wb.OpenSheet("Sheet1").OpenCell("G6").Value = "  张";
wb.OpenSheet("Sheet1").OpenCell("G7").Value = "  张";
wb.OpenSheet("Sheet1").OpenCell("G9").Value = "  晚";
wb.OpenSheet("Sheet1").OpenCell("G10").Value = "  晚";
wb.OpenSheet("Sheet1").OpenCell("G11").Value = "  天";
wb.OpenSheet("Sheet1").OpenCell("G12").Value = "  天";

wb.OpenSheet("Sheet1").OpenCell("H6").Formula = "=D6*F6";
wb.OpenSheet("Sheet1").OpenCell("H7").Formula = "=D7*F7";
wb.OpenSheet("Sheet1").OpenCell("H8").Formula = "=D8*F8";
wb.OpenSheet("Sheet1").OpenCell("H9").Formula = "=D9*F9";
wb.OpenSheet("Sheet1").OpenCell("H10").Formula = "=D10*F10";
wb.OpenSheet("Sheet1").OpenCell("H11").Formula = "=D11*F11";
wb.OpenSheet("Sheet1").OpenCell("H12").Formula = "=D12*F12";
wb.OpenSheet("Sheet1").OpenCell("H13").Formula = "=D13*F13";
wb.OpenSheet("Sheet1").OpenCell("H14").Formula = "=D14*F14";
wb.OpenSheet("Sheet1").OpenCell("H15").Formula = "=D15*F15";

for (int i = 0; i < 10; i++)
{
    wb.OpenSheet("Sheet1").OpenCell("D" + (6 + i).ToString()).NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
    wb.OpenSheet("Sheet1").OpenCell("H" + (6 + i).ToString()).NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
}

PageOfficeNetCore.Excel.ExcelCellWriter E16 = wb.OpenSheet("Sheet1").OpenCell("E16");
E16.Font.Bold = true;
E16.Font.Size = 11;
E16.ForeColor = Color.White;
E16.Value = "出差开支总费用";
E16.VerticalAlignment = PageOfficeNetCore.ExcelWriter.XlVAlign.xlVAlignCenter;
PageOfficeNetCore.Excel.ExcelCellWriter E17 = wb.OpenSheet("Sheet1").OpenCell("E17");
E17.Font.Bold = true;
E17.Font.Size = 11;
E17.ForeColor = Color.White;
E17.Formula = "=IF(C4>H16,\"低于预算\",\"超出预算\")";
E17.VerticalAlignment = PageOfficeNetCore.ExcelWriter.XlVAlign.xlVAlignCenter;
PageOfficeNetCore.Excel.ExcelCellWriter H16 = wb.OpenSheet("Sheet1").OpenCell("H16");
H16.VerticalAlignment = PageOfficeNetCore.ExcelWriter.XlVAlign.xlVAlignCenter;
H16.NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
H16.Font.Name = "Arial";
H16.Font.Size = 11;
H16.Font.Bold = true;
H16.Formula = "=SUM(H6:H15)";
PageOfficeNetCore.Excel.ExcelCellWriter H17 = wb.OpenSheet("Sheet1").OpenCell("H17");
H17.VerticalAlignment = PageOfficeNetCore.ExcelWriter.XlVAlign.xlVAlignCenter;
H17.NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
H17.Font.Name = "Arial";
H17.Font.Size = 11;
H17.Font.Bold = true;
H17.Formula = "=(C4-H16)";
#endregion

#region 填充数据
// 填充数据
PageOfficeNetCore.Excel.ExcelCellWriter C4 = wb.OpenSheet("Sheet1").OpenCell("C4");
C4.NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
C4.Value = "2500";
PageOfficeNetCore.Excel.ExcelCellWriter D6 = wb.OpenSheet("Sheet1").OpenCell("D6");
D6.NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
D6.Value = "1200";
wb.OpenSheet("Sheet1").OpenCell("F6").Font.Size = 10;
wb.OpenSheet("Sheet1").OpenCell("F6").Value = "1";
PageOfficeNetCore.Excel.ExcelCellWriter D7 = wb.OpenSheet("Sheet1").OpenCell("D7");
D7.NumberFormatLocal = "￥#,##0.00;￥-#,##0.00";
D7.Value = "875";
wb.OpenSheet("Sheet1").OpenCell("F7").Value = "1";

#endregion

PageOfficeNetCore.PageOfficeCtrl poCtrl = new PageOfficeNetCore.PageOfficeCtrl(Request);
poCtrl.SetWriter(wb);
poCtrl.WebOpen("doc/test.xls", PageOfficeNetCore.OpenModeType.xlsNormalEdit, "tom");

    // Make sure to add code blocks to your code group

    # 前端代码

    本示例无前端关键代码。

    上次更新: 2025/07/21, 15:21:36
    PageOffice | Copyright © 2013-2026 卓正软件 京ICP备12010902号-2 京公网安备 11010502019270号