主要利用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;
设置标题#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;