利用owc生成excel并且显示在页面

主要利用OWC生成Excel,画表头和显示从数据库读出来的数据,然后显示在前台页面上,无需客户端安装office,只需要安装owc.一年以前写的,似乎很烦锁,哪位有高见请指教!
后面利用OWC操作EXCEL,并且生成文件的操作

 

            SpreadsheetClass xlsheet = new SpreadsheetClass();

            
#region  设置标题
            
//写标题
            
//合并单元格
            xlsheet.get_Range(xlsheet.Cells[11], xlsheet.Cells[1,30]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
11= "采购执行情况一览表";
            
//字体加粗
            xlsheet.get_Range(xlsheet.Cells[11], xlsheet.Cells[130]).Font.set_Bold(true);
            
//单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[11], xlsheet.Cells[130]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
            
//设置字体大小
            xlsheet.get_Range(xlsheet.Cells[11], 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[
21= "序号";
            xlsheet.get_Range(xlsheet.Cells[
21], xlsheet.Cells[3,1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
22], xlsheet.Cells[32]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
22= "项目号";
            xlsheet.get_Range(xlsheet.Cells[
22], xlsheet.Cells[32]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[
23], xlsheet.Cells[33]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
23= "包号";
            xlsheet.get_Range(xlsheet.Cells[
23], xlsheet.Cells[33]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
24], xlsheet.Cells[34]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
24= "包预算";
            xlsheet.get_Range(xlsheet.Cells[
24], xlsheet.Cells[34]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[
25], xlsheet.Cells[35]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
25= "包计划请购日期";
            xlsheet.get_Range(xlsheet.Cells[
25], xlsheet.Cells[35]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[
26], xlsheet.Cells[36]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
26= "包计划签约日期";
            xlsheet.get_Range(xlsheet.Cells[
26], xlsheet.Cells[3,6]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
27], xlsheet.Cells[37]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
27= "包计划运输日期";
            xlsheet.get_Range(xlsheet.Cells[
27], xlsheet.Cells[37]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[
28], xlsheet.Cells[38]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
28= "包计划检验日期";
            xlsheet.get_Range(xlsheet.Cells[
28], xlsheet.Cells[38]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[
29], xlsheet.Cells[39]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
29= "项目经理";
            xlsheet.get_Range(xlsheet.Cells[
29], xlsheet.Cells[39]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
210], xlsheet.Cells[310]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
2,10= "采购经理";
            xlsheet.get_Range(xlsheet.Cells[
210], xlsheet.Cells[310]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
211], xlsheet.Cells[311]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
211= "采购工程师";
            xlsheet.get_Range(xlsheet.Cells[
211], xlsheet.Cells[311]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
212], xlsheet.Cells[312]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
212= "包状态";
            xlsheet.get_Range(xlsheet.Cells[
212], xlsheet.Cells[312]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[
213], xlsheet.Cells[313]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
213= "合同号";
            xlsheet.get_Range(xlsheet.Cells[
213], xlsheet.Cells[313]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
214], xlsheet.Cells[314]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
214= "供应商";
            xlsheet.get_Range(xlsheet.Cells[
214], xlsheet.Cells[314]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
215], xlsheet.Cells[315]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
215= "合同签定日";
            xlsheet.get_Range(xlsheet.Cells[
215], xlsheet.Cells[315]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
216], xlsheet.Cells[316]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
216= "合同金额";
            xlsheet.get_Range(xlsheet.Cells[
216], xlsheet.Cells[316]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
217], xlsheet.Cells[317]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
217= "合同控制价";
            xlsheet.get_Range(xlsheet.Cells[
217], xlsheet.Cells[317]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.get_Range(xlsheet.Cells[
218], xlsheet.Cells[318]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
218= "出厂资料交付时间";
            xlsheet.get_Range(xlsheet.Cells[
218], xlsheet.Cells[318]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[
219], xlsheet.Cells[319]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
219= "出厂前检验时间";
            xlsheet.get_Range(xlsheet.Cells[
219], xlsheet.Cells[319]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[
220], xlsheet.Cells[320]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
220= "性能试验时间";
            xlsheet.get_Range(xlsheet.Cells[
220], xlsheet.Cells[320]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);



            xlsheet.get_Range(xlsheet.Cells[
221], xlsheet.Cells[224]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
221= "交货时间";
            xlsheet.get_Range(xlsheet.Cells[
221], xlsheet.Cells[224]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.ActiveSheet.Cells[
321= "1";
            xlsheet.ActiveSheet.Cells[
322= "2";
            xlsheet.ActiveSheet.Cells[
323= "3";
            xlsheet.ActiveSheet.Cells[
324= "4";


            xlsheet.get_Range(xlsheet.Cells[
225], xlsheet.Cells[230]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[
225= "付款条件";
            xlsheet.get_Range(xlsheet.Cells[
225], xlsheet.Cells[230]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);


            xlsheet.ActiveSheet.Cells[
325= "1";
            xlsheet.ActiveSheet.Cells[
326= "2";
            xlsheet.ActiveSheet.Cells[
327= "3";
            xlsheet.ActiveSheet.Cells[
328= "4";
            xlsheet.ActiveSheet.Cells[
329= "5";
            xlsheet.ActiveSheet.Cells[
330= "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


            
生成文件

 

这样其实有一个问题,要控制生成的报表在不同的文件夹下面,否则会不同的人调用相同的报表.由于在最后并没有采用这种方案,所以也没有时间考虑这些了

前台用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">
    
<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。<align='center'>
      
<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>
      
<align='center'><href='http://www.microsoft.com/windows/ie/default.htm'> 单击此处安装最新的 Internet Explorer</a>.
      
</font><br>&nbsp;</td></tr></table></object>
posted @ 2008-06-27 21:45  孤独侠客  阅读(1177)  评论(0编辑  收藏  举报