利用owc生成excel並且顯示在頁面

主要利用OWC生成Excel,畫表頭和顯示從數據庫讀出來的數據,然後顯示在前台頁面上,無需客戶端安裝Office,只需要安裝owc.

 

後面利用OWC操作Excel,並且生成文件的操作

 

            SpreadsheetClass xlsheet = new SpreadsheetClass();

            設置標題#region  設置標題
            //寫標題
            //合並單元格
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1,30]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[1, 1] = "采購執行情況一覽表";
            //字體加粗
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 30]).Font.set_Bold(true);
            //單元格文本水平居中對齊
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 30]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
            //設置字體大小
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1,30]).Font.set_Size(14);
            #endregion

            設置列#region  設置列
            xlsheet.get_Range(xlsheet.Cells[2,1],xlsheet.Cells[3,1]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 1] = "序號";
            xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3,1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


       &n   xlsheet.get_Range(xlsheet.Cells[2, 2], xlsheet.Cells[3, 2]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 2] = "項目號";
            xlsheet.get_Range(xlsheet.Cells[2, 2], xlsheet.Cells[3, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[2, 3], xlsheet.Cells[3, 3]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 3] = "包號";
            xlsheet.get_Range(xlsheet.Cells[2, 3], xlsheet.Cells[3, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 4], xlsheet.Cells[3, 4]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 4] = "包預算";
            xlsheet.get_Range(xlsheet.Cells[2, 4], xlsheet.Cells[3, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[2, 5], xlsheet.Cells[3, 5]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 5] = "包計劃請購日期";
            xlsheet.get_Range(xlsheet.Cells[2, 5], xlsheet.Cells[3, 5]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[2, 6], xlsheet.Cells[3, 6]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 6] = "包計劃簽約日期";
            xlsheet.get_Range(xlsheet.Cells[2, 6], xlsheet.Cells[3,6]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 7], xlsheet.Cells[3, 7]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 7] = "包計劃運輸日期";
            xlsheet.get_Range(xlsheet.Cells[2, 7], xlsheet.Cells[3, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[2, 8], xlsheet.Cells[3, 8]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 8] = "包計劃檢驗日期";
            xlsheet.get_Range(xlsheet.Cells[2, 8], xlsheet.Cells[3, 8]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[2, 9], xlsheet.Cells[3, 9]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 9] = "項目經理";
            xlsheet.get_Range(xlsheet.Cells[2, 9], xlsheet.Cells[3, 9]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 10], xlsheet.Cells[3, 10]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2,10] = "采購經理";
            xlsheet.get_Range(xlsheet.Cells[2, 10], xlsheet.Cells[3, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 11], xlsheet.Cells[3, 11]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 11] = "采購工程師";
            xlsheet.get_Range(xlsheet.Cells[2, 11], xlsheet.Cells[3, 11]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 12], xlsheet.Cells[3, 12]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 12] = "包狀態";
            xlsheet.get_Range(xlsheet.Cells[2, 12], xlsheet.Cells[3, 12]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[2, 13], xlsheet.Cells[3, 13]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 13] = "合同號";
            xlsheet.get_Range(xlsheet.Cells[2, 13], xlsheet.Cells[3, 13]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
            xlsheet.get_Range(xlsheet.Cells[2, 14], xlsheet.Cells[3, 14]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 14] = "供應商";
            xlsheet.get_Range(xlsheet.Cells[2, 14], xlsheet.Cells[3, 14]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 15], xlsheet.Cells[3, 15]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 15] = "合同簽定日";
            xlsheet.get_Range(xlsheet.Cells[2, 15], xlsheet.Cells[3, 15]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 16], xlsheet.Cells[3, 16]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 16] = "合同金額";
            xlsheet.get_Range(xlsheet.Cells[2, 16], xlsheet.Cells[3, 16]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 17], xlsheet.Cells[3, 17]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 17] = "合同控制價";
            xlsheet.get_Range(xlsheet.Cells[2, 17], xlsheet.Cells[3, 17]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[2, 18], xlsheet.Cells[3, 18]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 18] = "出廠資料交付時間";
            xlsheet.get_Range(xlsheet.Cells[2, 18], xlsheet.Cells[3, 18]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[2, 19], xlsheet.Cells[3, 19]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 19] = "出廠前檢驗時間";
&           xlsheet.get_Range(xlsheet.Cells[2, 19], xlsheet.Cells[3, 19]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[2, 20], xlsheet.Cells[3, 20]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 20] = "性能試驗時間";
            xlsheet.get_Range(xlsheet.Cells[2, 20], xlsheet.Cells[3, 20]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[2, 21], xlsheet.Cells[2, 24]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 21] = "交貨時間";
            xlsheet.get_Range(xlsheet.Cells[2, 21], xlsheet.Cells[2, 24]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.ActiveSheet.Cells[3, 21] = "1";
            xlsheet.ActiveSheet.Cells[3, 22] = "2";
            xlsheet.ActiveSheet.Cells[3, 23] = "3";
            xlsheet.ActiveSheet.Cells[3, 24] = "4";


            xlsheet.get_Range(xlsheet.Cells[2, 25], xlsheet.Cells[2, 30]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[2, 25] = "付款條件";
            xlsheet.get_Range(xlsheet.Cells[2, 25], xlsheet.Cells[2, 30]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.ActiveSheet.Cells[3, 25] = "1";
            xlsheet.ActiveSheet.Cells[3, 26] = "2";
            xlsheet.ActiveSheet.Cells[3, 27] = "3";
            xlsheet.ActiveSheet.Cells[3, 28] = "4";
            xlsheet.ActiveSheet.Cells[3, 29] = "5";
            xlsheet.ActiveSheet.Cells[3, 30] = "6";

#endregion

            寫入數據#region  寫入數據
            int row = 4;//從第四行開始

            IList list = this.m_ProcurementPackageManager.GetAllObjectsList();

            int i = 1;
            foreach (ProcurementPackage package in list)
            ...{
                xlsheet.ActiveSheet.Cells[row,1] = i.ToString();
                xlsheet.ActiveSheet.Cells[row, 2] = package.Project.ProjectCode;
                xlsheet.ActiveSheet.Cells[row, 3] = package.PackageCode;
                xlsheet.ActiveSheet.Cells[row, 4] = package.PriceBudget;
                xlsheet.ActiveSheet.Cells[row, 5] = ConvertDateTimeToString(package.PlanRequestDate);
                xlsheet.ActiveSheet.Cells[row, 6] = ConvertDateTimeToString(package.PlanSignDate); 
                xlsheet.ActiveSheet.Cells[row, 7] = ConvertDateTimeToString(package.PlanTransportDate);
                xlsheet.ActiveSheet.Cells[row, 8] = ConvertDateTimeToString(package.PlanCheckDate);


                xlsheet.ActiveSheet.Cells[row, 9] = this.m_ProcurementPackageManager.GetPackageGroupUser(package.UniqueID, PackageGroupEnum.ProjectManager);
                xlsheet.ActiveSheet.Cells[row, 10] = this.m_ProcurementPackageManager.GetPackageGroupUser(package.UniqueID, PackageGroupEnum.PurchasingManager);
                xlsheet.ActiveSheet.Cells[row, 11] = this.m_ProcurementPackageManager.GetPackageGroupUser(package.UniqueID, PackageGroupEnum.ProcurementEngineer);
                xlsheet.ActiveSheet.Cells[row, 12] = package.FlagDSC;



                int j  = 0;
                foreach (ContractMaster contract in package.ContractList)
                ...{
                    xlsheet.ActiveSheet.Cells[row + j, 13] = contract.UniCode;
                    xlsheet.ActiveSheet.Cells[row + j, 14] = contract.ShortList.LongList.CompanyName;
                    xlsheet.ActiveSheet.Cells[row + j, 15] = ConvertDateTimeToString(contract.SignDate);
                    xlsheet.ActiveSheet.Cells[row + j, 16] = contract.TotalAmount;
                    xlsheet.ActiveSheet.Cells[row + j, 17] = contract.ControlPrice;
                    xlsheet.ActiveSheet.Cells[row + j, 18] = ConvertDateTimeToString(contract.FactoryDataDeliveryTime);
                    xlsheet.ActiveSheet.Cells[row + j, 19] = ConvertDateTimeToString(contract.FactoryInspectionTime);
                    xlsheet.ActiveSheet.Cells[row + j, 20] = ConvertDateTimeToString(contract.PerformanceTestTime);

                    xlsheet.ActiveSheet.Cells[row + j, 21] = ConvertDateTimeToString(contract.DeliveryTime1);
                    xlsheet.ActiveSheet.Cells[row + j, 22] = ConvertDateTimeToString(contract.DeliveryTime2);
                    xlsheet.ActiveSheet.Cells[row + j, 23] = ConvertDateTimeToString(contract.DeliveryTime3);
                    xlsheet.ActiveSheet.Cells[row + j, 24] = ConvertDateTimeToString(contract.DeliveryTime4);


                    xlsheet.ActiveSheet.Cells[row + j, 25] = contract.PaymentTerm1;
                    xlsheet.ActiveSheet.Cells[row + j, 26] = contract.PaymentTerm2;
                    xlsheet.ActiveSheet.Cells[row + j, 27] = contract.PaymentTerm3;
                    xlsheet.ActiveSheet.Cells[row + j, 28] = contract.PaymentTerm4;
                    xlsheet.ActiveSheet.Cells[row + j, 29] = contract.PaymentTerm5;
                    xlsheet.ActiveSheet.Cells[row + j, 30] = contract.PaymentTerm6;

                    j++;
                }


                for (int m = 1; m <= 12; m++)
                ...{
                    xlsheet.get_Range(xlsheet.Cells[row, m], xlsheet.Cells[row + j-1, m]).set_MergeCells(true);

                        xlsheet.get_Range(xlsheet.Cells[row, m], xlsheet.Cells[row + j-1, m]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
                    xlsheet.get_Range(xlsheet.Cells[row, m], xlsheet.Cells[row + j-1, m]).set_VerticalAlignment(XlVAlign.xlVAlignCenter);
                }
               

                row++;
                i++;
            }

            #endregion

            生成文件#region  生成文件
            try
               ...{
                   string sFileUrl = Convert.ToString(this.SessionInfo.GetIniKeyValue("UploadDir", "UpLoadDir"));
                   string fileUrl = Server.MapPath(sFileUrl) + "\";
                   xlsheet.Export(fileUrl + "_TestOWC.html", SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportHtml);
               }
               catch(Exception  ee)
                            ...{

                   throw new Exception(ee.Message);
               }
            #endregion

 

這樣其實有一個問題,要控制生成的報表在不同的文件夾下面,否則會不同的人調用相同的報表.由於在最後並沒有采用這種方案,所以也沒有時間考慮這些了

前台用Activex顯示Excel

 

  <object classid="clsid:0002E559-0000-0000-C000-000000000046"    width="1000"   id="Spreadsheet1"   height="300">
    <param name="DataType" value="HtmlURL">
    <param name="HTMLURL" value="<%=Agent.ServerURL %>ebChainUpLoad/_TestOWC.Html">
    <param name="AllowPropertyToolbox" value="-1">
    <param name="AutoFit" value="0">
    <param name="Calculation" value="-4105">
    <param name="Caption" value="采購執行情況一覽表">
    <param name="DisplayColumnHeadings" value="-1">
    <param name="DisplayGridlines" value="-1">
    <param name="DisplayHorizontalScrollBar" value="-1">
    <param name="DisplayOfficeLogo" value="-1">
    <param name="DisplayPropertyToolbox" value="0">
    <param name="DisplayRowHeadings" value="-1">
    <param name="DisplayTitleBar" value="0">
    <param name="DisplayToolbar" value="-1">
    <param name="DisplayVerticalScrollBar" value="-1">
    <param name="DisplayWorkbookTabs" value="-1">
    <param name="EnableEvents" value="-1">
    <param name="MaxHeight" value="80%">
    <param name="MaxWidth" value="100%">
    <param name="MoveAfterReturn" value="-1">
 &nb     <param name="MoveAfterReturnDirection" value="-4121">
    <param name="RightToLeft" value="0">
    <param name="ScreenUpdating" value="-1">
    <param name="LockedDown" value="0">
    <param name="ConnectedToChart" value="0">
    <param name="DefaultQueryOnLoad" value="-1">
    <param name="EnableUndo" value="-1">
    <table width=''100%'' cellpadding=''0'' cellspacing=''0'' border=''0'' height=''8''>
    <tr><td bgColor=''#336699'' height=''25'' width=''10%''>&nbsp;</td>
    <td bgColor=''#666666''width=''85%''><font face=''宋體'' color=''white'' size=''4''>
    <b>&nbsp; 缺少 Microsoft Office Web Components</b></font></td></tr><tr>
    <td bgColor=''#cccccc'' width=''15''>&nbsp;</td><td bgColor=''#cccccc'' width=''500px''><br>
     <font face=''宋體'' size=''2''>此網頁要求 Microsoft Office Web Components。<p align=''center''>
      <a href=''C:/IUware Online/Microsoft Office Professional Enterprise Edition 2003/files/owc11/setup.exe''>
      單擊此處安裝 Microsoft Office Web Components。</a>.</p></font><p><font face=''宋體'' size=''2''> 
      此網頁同時要求 Microsoft Internet Explorer 5.01 或更高版本。</p>
      <p align=''center''><a href=''http://www.microsoft.com/Windows/IE/default.htm''> 單擊此處安裝最新的 Internet Explorer</a>.
      </font><br>&nbsp;</td></tr></table></object>