protected void PageOfficeCtrl1_Load(object sender, EventArgs e) { PageOffice.ExcelWriter.Workbook worbBook = new PageOffice.ExcelWriter.Workbook(); PageOffice.ExcelWriter.Sheet Sheetl = worbBook.OpenSheet("Sheet1"); PageOffice.ExcelWriter.Cell cell1 = Sheetl.OpenCell("A1"); cell1.Value = "[image]image/logo.jpg[/image]"; // 设置PageOffice组件服务页面 PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx"; // 设置保存文件页面 // PageOfficeCtrl1.SaveFilePage = "SaveFile.aspx"; PageOfficeCtrl1.SetWriter(worbBook); // 打开文档 PageOfficeCtrl1.WebOpen("doc/test.xls", PageOffice.OpenModeType.xlsNormalEdit, "Tom"); }
protected void Page_Load(object sender, EventArgs e) { string tempFileName = Request.QueryString["temp"]; PageOffice.ExcelWriter.Workbook wk = new PageOffice.ExcelWriter.Workbook(); PageOffice.ExcelWriter.Sheet sheet = wk.OpenSheet("Sheet1"); PageOffice.ExcelWriter.Table table = sheet.OpenTableByDefinedName("report", 10, 5, false); table.DataFields[0].Value = "轮胎"; table.DataFields[1].Value = "100"; table.DataFields[2].Value = "120"; table.DataFields[3].Value = "500"; table.DataFields[4].Value = "120%"; table.NextRow(); table.Close(); // 注意不要忘记此代码,如果缺少此句代码,不会赋值成功。 //定义单元格对象,参数“year”就是Excel模板中定义的单元格的名称 PageOffice.ExcelWriter.Cell cellYear = sheet.OpenCellByDefinedName("year"); // 给单元格赋值 cellYear.Value = "2015年"; PageOffice.ExcelWriter.Cell cellName = sheet.OpenCellByDefinedName("name"); cellName.Value = "张三"; PageOfficeCtrl1.SetWriter(wk); //隐藏菜单栏 PageOfficeCtrl1.Menubar = false; //poCtrl1.setSaveDataPage("SaveData.aspx"); //poCtrl1.addCustomToolButton("保存", "Save()", 1); //设置PageOfficeCtrl控件的服务页面 PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx"; PageOfficeCtrl1.Caption = "给Excel文档中定义名称的单元格赋值"; PageOfficeCtrl1.SaveDataPage = "SaveData.aspx"; PageOfficeCtrl1.AddCustomToolButton("保存", "Save()", 1); PageOfficeCtrl1.WebOpen("doc/" + tempFileName, PageOffice.OpenModeType.xlsSubmitForm, "操作人姓名"); }
protected void Page_Load(object sender, EventArgs e) { //定义Workbook对象 PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook(); //定义Sheet对象,"Sheet1"是打开的Excel表单的名称 PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("Sheet1"); //定义Cell对象,给单元格赋值 PageOffice.ExcelWriter.Cell cellB4 = sheet.OpenCell("B4"); cellB4.Value = "1月"; //或者直接给Cell赋值 sheet.OpenCell("C4").Value = "300"; sheet.OpenCell("D4").Value = "270"; sheet.OpenCell("E4").Value = "270"; sheet.OpenCell("F4").Value = string.Format("{0:P}", 270.0 / 300); PageOfficeCtrl1.SetWriter(workBook);// 注意不要忘记此代码,如果缺少此句代码,不会赋值成功。 //设置PageOfficeCtrl控件的服务页面 PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx"; PageOfficeCtrl1.Caption = "简单的给Excel赋值"; PageOfficeCtrl1.WebOpen("doc/test.xls", PageOffice.OpenModeType.xlsNormalEdit, "操作人姓名"); }
protected void Page_Load(object sender, EventArgs e) { //设置PageOfficeCtrl控件的服务页面 PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx"; //PageOfficeCtrl1.AddCustomToolButton("保存", "Save()", 1); PageOfficeCtrl1.Caption = "简单的给Excel赋值"; //定义Workbook对象 PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook(); //定义Sheet对象,"Sheet1"是打开的Excel表单的名称 PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("Sheet1"); //定义Cell对象 PageOffice.ExcelWriter.Cell cellB4 = sheet.OpenCell("B4"); //给单元格赋值 cellB4.Value = "1月"; //设置字体颜色 cellB4.ForeColor = Color.Red; PageOffice.ExcelWriter.Cell cellC4 = sheet.OpenCell("C4"); cellC4.Value = "300"; cellC4.ForeColor = Color.Blue; PageOffice.ExcelWriter.Cell cellD4 = sheet.OpenCell("D4"); cellD4.Value = "270"; cellD4.ForeColor = Color.Orange; PageOffice.ExcelWriter.Cell cellE4 = sheet.OpenCell("E4"); cellE4.Value = "270"; cellE4.ForeColor = Color.Green; PageOffice.ExcelWriter.Cell cellF4 = sheet.OpenCell("F4"); cellF4.Value = string.Format("{0:P}", 270.0 / 300); cellF4.ForeColor = Color.Gray; PageOfficeCtrl1.SetWriter(workBook); PageOfficeCtrl1.WebOpen("doc/test.xls", PageOffice.OpenModeType.xlsNormalEdit, "操作人姓名"); }
protected void Page_Load(object sender, EventArgs e) { string fileName = "test.xls"; PageOffice.ExcelWriter.Workbook wb = new PageOffice.ExcelWriter.Workbook(); // 设置背景 PageOffice.ExcelWriter.Table 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; PageOffice.ExcelWriter.Cell A1 = wb.OpenSheet("Sheet1").OpenCell("A1"); A1.HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignCenter; A1.VerticalAlignment = PageOffice.ExcelWriter.XlVAlign.xlVAlignCenter; A1.ForeColor = Color.FromArgb(0, 128, 128); A1.Value = "出差开支预算"; A1.Font.Bold = true; A1.Font.Size = 25; #region 画表头 // 画表头 PageOffice.ExcelWriter.Border C4Border = wb.OpenSheet("Sheet1").OpenTable("C4:C4").Border; C4Border.Weight = PageOffice.ExcelWriter.XlBorderWeight.xlThick; C4Border.LineColor = Color.Yellow; PageOffice.ExcelWriter.Table titleTable = wb.OpenSheet("Sheet1").OpenTable("B4:H5"); titleTable.Border.Weight = PageOffice.ExcelWriter.XlBorderWeight.xlThick; titleTable.Border.LineColor = Color.FromArgb(0, 128, 128); titleTable.Border.BorderType = PageOffice.ExcelWriter.XlBorderType.xlAllEdges; #endregion #region 画表体 // 画表体 PageOffice.ExcelWriter.Table bodyTable = wb.OpenSheet("Sheet1").OpenTable("B6:H15"); bodyTable.Border.LineColor = Color.Gray; bodyTable.Border.Weight = PageOffice.ExcelWriter.XlBorderWeight.xlHairline; PageOffice.ExcelWriter.Border B7Border = wb.OpenSheet("Sheet1").OpenTable("B7:B7").Border; B7Border.LineColor = Color.White; PageOffice.ExcelWriter.Border B9Border = wb.OpenSheet("Sheet1").OpenTable("B9:B9").Border; B9Border.BorderType = PageOffice.ExcelWriter.XlBorderType.xlBottomEdge; B9Border.LineColor = Color.White; PageOffice.ExcelWriter.Border C6C15BorderLeft = wb.OpenSheet("Sheet1").OpenTable("C6:C15").Border; C6C15BorderLeft.LineColor = Color.White; C6C15BorderLeft.BorderType = PageOffice.ExcelWriter.XlBorderType.xlLeftEdge; PageOffice.ExcelWriter.Border C6C15BorderRight = wb.OpenSheet("Sheet1").OpenTable("C6:C15").Border; C6C15BorderRight.LineColor = Color.Yellow; C6C15BorderRight.LineStyle = PageOffice.ExcelWriter.XlBorderLineStyle.xlDot; C6C15BorderRight.BorderType = PageOffice.ExcelWriter.XlBorderType.xlRightEdge; PageOffice.ExcelWriter.Border E6E15Border = wb.OpenSheet("Sheet1").OpenTable("E6:E15").Border; E6E15Border.LineStyle = PageOffice.ExcelWriter.XlBorderLineStyle.xlDot; E6E15Border.BorderType = PageOffice.ExcelWriter.XlBorderType.xlAllEdges; E6E15Border.LineColor = Color.Yellow; PageOffice.ExcelWriter.Border G6G15BorderRight = wb.OpenSheet("Sheet1").OpenTable("G6:G15").Border; G6G15BorderRight.BorderType = PageOffice.ExcelWriter.XlBorderType.xlRightEdge; G6G15BorderRight.LineColor = Color.White; PageOffice.ExcelWriter.Border G6G15BorderLeft = wb.OpenSheet("Sheet1").OpenTable("G6:G15").Border; G6G15BorderLeft.LineStyle = PageOffice.ExcelWriter.XlBorderLineStyle.xlDot; G6G15BorderLeft.BorderType = PageOffice.ExcelWriter.XlBorderType.xlLeftEdge; G6G15BorderLeft.LineColor = Color.Yellow; PageOffice.ExcelWriter.Table bodyTable2 = wb.OpenSheet("Sheet1").OpenTable("B6:H15"); bodyTable2.Border.Weight = PageOffice.ExcelWriter.XlBorderWeight.xlThick; bodyTable2.Border.LineColor = Color.FromArgb(0, 128, 128); bodyTable2.Border.BorderType = PageOffice.ExcelWriter.XlBorderType.xlAllEdges; #endregion #region 画表尾 // 画表尾 PageOffice.ExcelWriter.Border H16H17Border = wb.OpenSheet("Sheet1").OpenTable("H16:H17").Border; H16H17Border.LineColor = Color.FromArgb(204, 255, 204); PageOffice.ExcelWriter.Border E16G17Border = wb.OpenSheet("Sheet1").OpenTable("E16:G17").Border; E16G17Border.LineColor = Color.FromArgb(0, 128, 128); PageOffice.ExcelWriter.Table footTable = wb.OpenSheet("Sheet1").OpenTable("B16:H17"); footTable.Border.Weight = PageOffice.ExcelWriter.XlBorderWeight.xlThick; footTable.Border.LineColor = Color.FromArgb(0, 128, 128); footTable.Border.BorderType = PageOffice.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 填充单元格文本和公式 //填充单元格文本和公式 PageOffice.ExcelWriter.Cell B4 = wb.OpenSheet("Sheet1").OpenCell("B4"); B4.Font.Bold = true; B4.Value = "出差开支预算"; PageOffice.ExcelWriter.Cell H5 = wb.OpenSheet("Sheet1").OpenCell("H5"); H5.Font.Bold = true; H5.Value = "总计"; H5.HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignCenter; PageOffice.ExcelWriter.Cell B6 = wb.OpenSheet("Sheet1").OpenCell("B6"); B6.Font.Bold = true; B6.Value = "飞机票价"; PageOffice.ExcelWriter.Cell B9 = wb.OpenSheet("Sheet1").OpenCell("B9"); B9.Font.Bold = true; B9.Value = "酒店"; PageOffice.ExcelWriter.Cell B11 = wb.OpenSheet("Sheet1").OpenCell("B11"); B11.Font.Bold = true; B11.Value = "餐饮"; PageOffice.ExcelWriter.Cell B12 = wb.OpenSheet("Sheet1").OpenCell("B12"); B12.Font.Bold = true; B12.Value = "交通费用"; PageOffice.ExcelWriter.Cell B13 = wb.OpenSheet("Sheet1").OpenCell("B13"); B13.Font.Bold = true; B13.Value = "休闲娱乐"; PageOffice.ExcelWriter.Cell B14 = wb.OpenSheet("Sheet1").OpenCell("B14"); B14.Font.Bold = true; B14.Value = "礼品"; PageOffice.ExcelWriter.Cell 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"; } PageOffice.ExcelWriter.Cell E16 = wb.OpenSheet("Sheet1").OpenCell("E16"); E16.Font.Bold = true; E16.Font.Size = 11; E16.ForeColor = Color.White; E16.Value = "出差开支总费用"; E16.VerticalAlignment = PageOffice.ExcelWriter.XlVAlign.xlVAlignCenter; PageOffice.ExcelWriter.Cell 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 = PageOffice.ExcelWriter.XlVAlign.xlVAlignCenter; PageOffice.ExcelWriter.Cell H16 = wb.OpenSheet("Sheet1").OpenCell("H16"); H16.VerticalAlignment = PageOffice.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)"; PageOffice.ExcelWriter.Cell H17 = wb.OpenSheet("Sheet1").OpenCell("H17"); H17.VerticalAlignment = PageOffice.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 填充数据 // 填充数据 PageOffice.ExcelWriter.Cell C4 = wb.OpenSheet("Sheet1").OpenCell("C4"); C4.NumberFormatLocal = "¥#,##0.00;¥-#,##0.00"; C4.Value = "2500"; PageOffice.ExcelWriter.Cell 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"; PageOffice.ExcelWriter.Cell D7 = wb.OpenSheet("Sheet1").OpenCell("D7"); D7.NumberFormatLocal = "¥#,##0.00;¥-#,##0.00"; D7.Value = "875"; wb.OpenSheet("Sheet1").OpenCell("F7").Value = "1"; #endregion PageOfficeCtrl1.BorderStyle = PageOffice.BorderStyleType.BorderThin; PageOfficeCtrl1.Caption = "完全使用程序生成Excel表格"; // 打开文件 PageOfficeCtrl1.ServerPage = "pageoffice/server.aspx"; PageOfficeCtrl1.SetWriter(wb); PageOfficeCtrl1.WebOpen(Server.MapPath("doc/") + fileName, PageOffice.OpenModeType.xlsNormalEdit, "张佚名"); }