主要利用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%'
><
font
face
='宋体'
color
='white'
size
='4'
>
<
b
>
缺少 Microsoft Office Web Components
</
b
></
font
></
td
></
tr
><
tr
>
<
td
bgColor
='#cccccc'
width
='15'
>
</
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
>
</
td
></
tr
></
table
></
object
>
扫一扫
945
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
