Exemple #1
0
        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"));
        }
Exemple #2
0
    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, "操作人姓名");
    }
Exemple #6
0
    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, "操作人姓名");
    }
Exemple #7
0
    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, "张佚名");
    }
Exemple #8
0
    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());
        }
Exemple #10
0
    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);

    }
Exemple #11
0
    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);
    }