protected void Page_Load(object sender, EventArgs e) { PageOffice.ExcelWriter.Workbook wk = new PageOffice.ExcelWriter.Workbook(); PageOffice.ExcelWriter.Sheet sheet = wk.OpenSheet("Sheet1"); PageOffice.ExcelWriter.Table table = sheet.OpenTableByDefinedName("report", 4, 5, true); int rowCount = 12;//假设将要自动填充数据的实际记录条数为12 for (int i = 1; i <= rowCount; i++) { table.DataFields[0].Value = i + "月"; table.DataFields[1].Value = "100"; table.DataFields[2].Value = "120"; table.DataFields[3].Value = "500"; table.DataFields[4].Value = "120%"; table.NextRow(); } table.Close(); //定义另一个table PageOffice.ExcelWriter.Table table2 = sheet.OpenTableByDefinedName("report2", 4, 5, true); int rowCount2 = 4;//假设将要自动填充数据的实际记录条数为12 for (int i = 1; i <= rowCount2; i++) { table2.DataFields[0].Value = i + "季度"; table2.DataFields[1].Value = "300"; table2.DataFields[2].Value = "300"; table2.DataFields[3].Value = "300"; table2.DataFields[4].Value = "100%"; table2.NextRow(); } table2.Close(); PageOfficeCtrl1.SetWriter(wk);// 注意不要忘记此代码,如果缺少此句代码,不会赋值成功。 //设置PageOfficeCtrl控件的服务页面 PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx"; PageOfficeCtrl1.Caption = "给Excel文档中定义名称的单元格赋值"; PageOfficeCtrl1.SaveDataPage = "SaveData.aspx"; PageOfficeCtrl1.AddCustomToolButton("保存", "Save()", 1); PageOfficeCtrl1.WebOpen("doc/test4.xls", PageOffice.OpenModeType.xlsSubmitForm, "操作人姓名"); }
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) { 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(); PageOfficeCtrl1.SetWriter(wk);// 注意不要忘记此代码,如果缺少此句代码,不会赋值成功。 //设置PageOfficeCtrl控件的服务页面 PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx"; PageOfficeCtrl1.Caption = "给Excel文档中定义名称的单元格赋值"; PageOfficeCtrl1.SaveDataPage = "SaveData.aspx"; PageOfficeCtrl1.AddCustomToolButton("保存", "Save()", 1); PageOfficeCtrl1.WebOpen("doc/test.xls", PageOffice.OpenModeType.xlsSubmitForm, "操作人姓名"); }
protected void Page_Load(object sender, EventArgs e) { if (Session["UserName"] == null || Session["UserName"].ToString().Length <= 0) { Response.Redirect("Login.aspx"); } if (Request.QueryString["ID"] != null && Request.QueryString["ID"].Trim().Length > 0) { id = Request.QueryString["ID"]; } else { Response.Redirect("OrderList.aspx"); } LitDate.Text = DateTime.Now.ToShortDateString() + " " + "星期" + DateTime.Now.DayOfWeek.ToString(("D")); // 数据库连接 string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|demo_excelorder.mdb"; string strSql = "select * from OrderMaster where ID = " + id; DataSet ds = new DataSet(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(strSql, connection); command.Fill(ds, "ds"); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } } // 填充数据 PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook(); //workBook.DisableSheetDoubleClick = true; PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("销售订单"); if (ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { #region 客户信息 sheet.OpenCell("D5").Value = dt.Rows[0]["CustName"].ToString(); sheet.OpenCell("D5").SubmitName = "CustName";//单元格提交数据 sheet.OpenCell("I5").Value = dt.Rows[0]["OrderNum"].ToString(); sheet.OpenCell("I5").SubmitName = "OrderNum";//单元格提交数据 sheet.OpenCell("D6").Value = dt.Rows[0]["CustDistrict"].ToString(); sheet.OpenCell("D6").SubmitName = "CustDistrict";//单元格提交数据 sheet.OpenCell("I6").Value = dt.Rows[0]["OrderDate"].ToString(); sheet.OpenCell("I6").SubmitName = "OrderDate";//单元格提交数据 sheet.OpenCell("D18").Value = dt.Rows[0]["MakerName"].ToString(); sheet.OpenCell("D18").SubmitName = "UserName";//单元格提交数据 sheet.OpenCell("H18").Value = dt.Rows[0]["SalesName"].ToString(); sheet.OpenCell("H18").SubmitName = "SalesName";//单元格提交数据 sheet.OpenCell("I16").SubmitName = "Amount";//单元格提交数据 sheet.OpenCell("I16").ReadOnly = true;//将Excel模版中有公式的单元格设置为只读格式,以免覆盖掉公式 #endregion #region 产品信息 strSql = "select * from OrderDetail where OrderID =" + dt.Rows[0]["ID"]; DataSet ds2 = new DataSet(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(strSql, connection); command.Fill(ds2, "ds2"); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } } //定义table对象 PageOffice.ExcelWriter.Table tableD = sheet.OpenTable("D9:D15");//定义table对象 tableD.ReadOnly = true;//将table设置成只读 PageOffice.ExcelWriter.Table table = sheet.OpenTable("C9:H15"); table.SubmitName = "OrderDetail"; //表提交数据 if (ds2.Tables.Count > 0) { DataTable dt2 = ds2.Tables[0]; if (dt2.Rows.Count > 0) { for (int j = 0; j < dt2.Rows.Count; j++) { for (int i = 0; i < table.DataFields.Count; i++) { if (i != 1)//跳过Excel模版中有公式的列,让Excel自动计算该列的值 { table.DataFields[i].Value = dt2.Rows[j][i + 1].ToString(); } } table.NextRow();//换行 } } } table.Close();//关闭table #endregion } } PageOfficeCtrl1.SetWriter(workBook); }
protected void Page_Load(object sender, EventArgs e) { if (Session["UserName"] == null || Session["UserName"].ToString().Length <= 0) { Response.Redirect("Login.aspx"); } if (Request.QueryString["ID"] != null && Request.QueryString["ID"].Trim().Length > 0) { id = Request.QueryString["ID"]; } else { Response.Redirect("OrderList.aspx"); } LitDate.Text = DateTime.Now.ToShortDateString() + " " + "星期" + DateTime.Now.DayOfWeek.ToString(("D")); string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|demo_excelorder.mdb"; string strSql = "select * from OrderMaster where ID = " + id; DataSet ds = new DataSet(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(strSql, connection); command.Fill(ds, "ds"); connection.Close(); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } } PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook(); workBook.DisableSheetDoubleClick = true; workBook.DisableSheetRightClick = true; PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("销售订单"); if (ds != null) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { #region 客户信息 sheet.OpenCell("D5").Value = dt.Rows[0]["CustName"].ToString(); sheet.OpenCell("I5").Value = dt.Rows[0]["OrderNum"].ToString(); sheet.OpenCell("D6").Value = dt.Rows[0]["CustDistrict"].ToString(); sheet.OpenCell("I6").Value = dt.Rows[0]["OrderDate"].ToString(); sheet.OpenCell("D18").Value = dt.Rows[0]["MakerName"].ToString(); sheet.OpenCell("H18").Value = dt.Rows[0]["SalesName"].ToString(); #endregion #region 产品信息 strSql = "select * from OrderDetail where OrderID = " + dt.Rows[0]["ID"]; DataSet ds2 = new DataSet(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(strSql, connection); command.Fill(ds2, "ds2"); connection.Close(); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } } if (ds2 != null) { DataTable dt2 = ds2.Tables[0]; if (dt2.Rows.Count > 0) { PageOffice.ExcelWriter.Table table = sheet.OpenTable("C9:H15"); for (int j = 0; j < dt2.Rows.Count; j++) { for (int i = 0; i < table.DataFields.Count; i++) { if (dt2.Rows[j][i + 1] != null) { table.DataFields[i].Value = dt2.Rows[j][i + 1].ToString(); } else { table.DataFields[i].Value = ""; } } int result = 0; if (int.TryParse(dt2.Rows[j]["Quantity"].ToString(), out result) && int.TryParse(dt2.Rows[j]["Price"].ToString(), out result)) { sheet.OpenCell("I" + (9 + j)).Value = (int.Parse(dt2.Rows[j]["Quantity"].ToString()) * int.Parse(dt2.Rows[j]["Price"].ToString())).ToString(); } table.NextRow(); } table.Close(); } } #endregion } } else { Response.Write("<script>alert('订单号为" + id + "'的订单信息不存在!);location.href='OrderList.aspx'</script>"); } workBook.DisableSheetSelection = true; PageOfficeCtrl1.SetWriter(workBook); }