利用owc生成excel并且显示在页面-CSDN博客

主要利用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);

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" >

< 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%' >   </ td >

< td bgColor ='#666666'width='85%' >

缺少 Microsoft Office Web Components </ td ></ tr >< tr >

< td bgColor ='#cccccc' width ='15' >   </ td >< td bgColor ='#cccccc' width ='500px' >

此网页要求 Microsoft Office Web Components。

< a href ='C:/IUware Online/Microsoft Office Professional Enterprise Edition 2003/files/owc11/setup.exe' >

单击此处安装 Microsoft Office Web Components。 </ a >.

此网页同时要求 Microsoft Internet Explorer 5.01 或更高版本。

< a href ='http://www.microsoft.com/windows/ie/default.htm' > 单击此处安装最新的 Internet Explorer </ a >.

</ td ></ tr ></ table ></ object >

分类: .NET

标签: owc, excel

本文转自孤独侠客博客园博客，原文链接：http://www.cnblogs.com/lonely7345/archive/2008/06/27/1231353.html，如需转载请自行联系原作者

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

“相关推荐”对你有帮助么？