T_tb_TestReportData tTestReportData = new T_tb_TestReportData(); //检验报告数据
/// <summary>
/// 原始记录文档在线编辑
/// </summary>
/// <param name="InfoID">原始记录ID</param>
/// <returns></returns>
public ActionResult OriginalRecordDocEdit(int InfoID)
{
E_tb_OriginalRecord eOriginalRecord = _dOriginalRecord.GetModel(new E_tb_OriginalRecord()
{
RecordID = Convert.ToInt32(InfoID)
});
E_tb_Project eProject = _dProject.GetModel(Convert.ToInt32(eOriginalRecord.ProjectID));
//判断是否已存在原始记录文件，若不存在默认读取检测项目对应的模板
string filepath = eProject.FilePath;
if (System.IO.File.Exists(Server.MapPath("~/upfile/" + eOriginalRecord.FilePath)))
{
filepath = eOriginalRecord.FilePath;
}
//初始化PageOffice控件
PageOfficeCtrl pc = this.GetOfficeCtrl(
"/OriginalRecordDoc/SaveDoc?filename=" + eOriginalRecord.FilePath,
"/OriginalRecordDoc/SaveData?FilePath=" + eOriginalRecord.FilePath + "&ProjectID=" + eOriginalRecord.ProjectID + "&RecordID=" + InfoID,
filepath);
//定义提交数据范围
PageOffice.ExcelWriter.Workbook wb = new PageOffice.ExcelWriter.Workbook();
PageOffice.ExcelWriter.Sheet sheetOrder = wb.OpenSheet("Sheet1");
PageOffice.ExcelWriter.Table table = sheetOrder.OpenTable(eProject.SampleDataRange.Replace("：", ":").ToUpper());
pc.SetWriter(wb);
//判断模板类型，是否为非农残检测项目
if (eOriginalRecord.IsPesCheck != 1)
{
PageOffice.ExcelWriter.Workbook workBook = this.GetNoPesCheckWorkbook(Convert.ToInt32(eOriginalRecord.SampleID), eProject.SampleDataRange);
pc.SetWriter(workBook);// 注意不要忘记此代码，如果缺少此句代码，不会赋值成功。
}
//实例化控件输出内容
Page page = new Page();
page.Controls.Add(pc);
StringBuilder controlOutput = new StringBuilder();
using (StringWriter sw = new StringWriter(controlOutput))
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
Server.Execute(page, htw, false);
}
}
ViewBag.EditorHtml = controlOutput.ToString();
return(View("/views/OriginalRecord/FileView.cshtml"));
}
protected void Page_Load(object sender, EventArgs e)
{
PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook();
PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("Sheet1");
//定义table对象，设置table对象的设置范围
PageOffice.ExcelWriter.Table table = sheet.OpenTable("B4:D8");
//设置table对象的提交名称，以便保存页面获取提交的数据
table.SubmitName = "Info";
PageOfficeCtrl1.SetWriter(workBook);
// 设置响应单元格点击事件的js function
PageOfficeCtrl1.JsFunction_OnExcelCellClick = "OnCellClick()";
PageOfficeCtrl1.AddCustomToolButton("保存", "Save()", 1);
PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx";
PageOfficeCtrl1.SaveDataPage = "SaveData.aspx";
PageOfficeCtrl1.WebOpen("doc/test.xls", 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", 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)
{
//设置PageOfficeCtrl控件的服务页面
PageOfficeCtrl1.ServerPage = Request.ApplicationPath + "/pageoffice/server.aspx";
PageOfficeCtrl1.AddCustomToolButton("保存", "Save()", 1);
//定义Workbook对象
PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook();
//定义Sheet对象，"Sheet1"是打开的Excel表单的名称
PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("Sheet1");
//定义table对象，设置table对象的设置范围
PageOffice.ExcelWriter.Table table = sheet.OpenTable("B4:F13");
//设置table对象的提交名称，以便保存页面获取提交的数据
table.SubmitName = "Info";
PageOfficeCtrl1.SetWriter(workBook);
PageOfficeCtrl1.SaveDataPage = "SaveData.aspx";
PageOfficeCtrl1.WebOpen("doc/test.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)
{
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, "张佚名");
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null || Session["UserName"].ToString().Length <= 0)
{
Response.Redirect("Login.aspx");
}
LitDate.Text = DateTime.Now.ToShortDateString() + " " + "星期" + DateTime.Now.DayOfWeek.ToString(("D"));
PageOffice.ExcelWriter.Workbook wordBook = new PageOffice.ExcelWriter.Workbook();
PageOffice.ExcelWriter.Sheet sheet = wordBook.OpenSheet("查询表");
// 数据库连接
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|demo_excelorder.mdb";
String strSql = "SELECT OrderNum,OrderDate,CustName,SalesName,Amount from OrderMaster order by ID desc ";
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);
}
}
DataTable dt = new DataTable();
decimal totalMoney = 0;
if (ds != null)
{
dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.OpenCell("B" + (5 + i)).Value = dt.Rows[i]["OrderNum"].ToString();
if (dt.Rows[0]["OrderDate"] != null && dt.Rows[i]["OrderDate"].ToString().Length > 0)
{
sheet.OpenCell("C" + (5 + i)).Value = DateTime.Parse(dt.Rows[0]["OrderDate"].ToString()).ToShortDateString();
}
sheet.OpenCell("D" + (5 + i)).Value = dt.Rows[i]["CustName"].ToString();
sheet.OpenCell("E" + (5 + i)).Value = dt.Rows[i]["SalesName"].ToString();
sheet.OpenCell("F" + (5 + i)).Value = string.Format("{0:C2}", Convert.ToDecimal(dt.Rows[i]["Amount"].ToString()));
totalMoney += decimal.Parse(dt.Rows[i]["Amount"].ToString());
if (i % 2 == 0)
{
//设置背景色
sheet.OpenTable("B" + (5 + i) + ":F" + (5 + i)).BackColor = Color.FromArgb(253, 233, 217);
}
}
//设置前景色
sheet.OpenTable("B5:F" + (dt.Rows.Count + 4)).ForeColor = Color.FromArgb(148, 138, 84);
//水平方向对齐方式
sheet.OpenTable("B5:F" + (dt.Rows.Count + 4)).HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignLeft;
sheet.OpenTable("C5:C" + (dt.Rows.Count + 4)).HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignCenter;
sheet.OpenTable("E5:E" + (dt.Rows.Count + 4)).HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignCenter;
sheet.OpenTable("F5:F" + (dt.Rows.Count + 4)).HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignRight;
//竖直方向对齐方式
sheet.OpenTable("B5:F" + (dt.Rows.Count + 4)).VerticalAlignment = PageOffice.ExcelWriter.XlVAlign.xlVAlignCenter;
}
//合计：
//合并单元格
sheet.OpenTable("B" + (dt.Rows.Count + 5) + ":F" + (dt.Rows.Count + 5)).Merge();
//行高
sheet.OpenTable("B5:F" + (dt.Rows.Count + 6)).RowHeight = 18;
sheet.OpenTable("B" + (dt.Rows.Count + 5) + ":F" + (dt.Rows.Count + 6)).HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignLeft;
sheet.OpenTable("B" + (dt.Rows.Count + 5) + ":F" + (dt.Rows.Count + 6)).VerticalAlignment = PageOffice.ExcelWriter.XlVAlign.xlVAlignCenter;
sheet.OpenCell("B" + (dt.Rows.Count + 6)).Value = "合计";
sheet.OpenTable("C" + (dt.Rows.Count + 6) + ":E" + (dt.Rows.Count + 6)).Merge();
sheet.OpenCell("F" + (dt.Rows.Count + 6)).Value = string.Format("{0:C2}", totalMoney);
sheet.OpenTable("F" + (dt.Rows.Count + 6) + ":F" + (dt.Rows.Count + 6)).HorizontalAlignment = PageOffice.ExcelWriter.XlHAlign.xlHAlignRight;
sheet.OpenTable("B" + (dt.Rows.Count + 6) + ":F" + (dt.Rows.Count + 6)).VerticalAlignment = PageOffice.ExcelWriter.XlVAlign.xlVAlignCenter;
//设置字体：大小、名称
sheet.OpenTable("B5:F" + (dt.Rows.Count + 6)).Font.Size = 9;
sheet.OpenTable("B5:F" + (dt.Rows.Count + 6)).Font.Name = "宋体";
//设置Table的边框样式：样式、宽度、颜色(多种边框样式重叠时，需创建Table对象才可实现样式的叠加覆盖)
PageOffice.ExcelWriter.Table table = sheet.OpenTable("B" + (dt.Rows.Count + 6) + ":F" + (dt.Rows.Count + 6));
table.Border.BorderType = PageOffice.ExcelWriter.XlBorderType.xlTopEdge;
table.Border.Weight = PageOffice.ExcelWriter.XlBorderWeight.xlThin;
table.Border.LineColor = Color.FromArgb(148, 138, 84);
PageOfficeCtrl1.SetWriter(wordBook);
}
/// <summary>
/// 文件预览
/// </summary>
/// <returns></returns>
public ActionResult FileView(string filename, string SaveFilePage, string SaveDataPage, string DataRange)
{
if (!string.IsNullOrEmpty(filename))
{
while (SaveFilePage.IndexOf('|') > -1)
{
SaveFilePage = SaveFilePage.Replace('|', '&');
}
while (SaveDataPage.IndexOf('|') > -1)
{
SaveDataPage = SaveDataPage.Replace('|', '&');
}
Page page = new Page();
string controlOutput = string.Empty;
PageOffice.PageOfficeCtrl pc = new PageOffice.PageOfficeCtrl();
pc.SaveFilePage = SaveFilePage;
pc.ServerPage = "/pageoffice/server.aspx";
if (!string.IsNullOrEmpty(SaveDataPage) && !string.IsNullOrEmpty(DataRange))
{
PageOffice.ExcelWriter.Workbook wb = new PageOffice.ExcelWriter.Workbook();
PageOffice.ExcelWriter.Sheet sheetOrder = wb.OpenSheet("Sheet1");
PageOffice.ExcelWriter.Table table = sheetOrder.OpenTable(DataRange);
pc.SetWriter(wb);
pc.SaveDataPage = SaveDataPage;
}
var openmodeltype = PageOffice.OpenModeType.docAdmin;
try
{
var filenames = filename.Split('.');
switch (filenames[1])
{
case "doc":
openmodeltype = PageOffice.OpenModeType.docNormalEdit;
break;
case "docx":
openmodeltype = PageOffice.OpenModeType.docNormalEdit;
break;
case "xlsx":
openmodeltype = PageOffice.OpenModeType.xlsNormalEdit;
break;
case "xls":
openmodeltype = PageOffice.OpenModeType.xlsNormalEdit;
break;
case "pptx":
openmodeltype = PageOffice.OpenModeType.pptNormalEdit;
break;
case "ppt":
openmodeltype = PageOffice.OpenModeType.pptNormalEdit;
break;
}
}
catch
{
}
pc.WebOpen("/UpFile/" + filename, openmodeltype, "s");
page.Controls.Add(pc);
StringBuilder sb = new StringBuilder();
using (StringWriter sw = new StringWriter(sb))
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
Server.Execute(page, htw, false); controlOutput = sb.ToString();
}
}
ViewBag.EditorHtml = controlOutput;
}
return(View());
}
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);
}