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