public void SetFields()
{
if (this.Sheet != null)
{
this.Fields = new string[this.Height, this.Width];
for (int i = 0; i < this.Fields.Length; i++)
{
for (int j = 0; j < this.Width; j++)
{
string text = TExcel.GetCellValue(this.Sheet, this.StartRow + i, this.StartCol + j);
if (text.StartsWith("@"))
{
if (text.StartsWith("@@"))
{
this.Fields[i, j] = text;
}
else
{
this.Fields[i, j] = text.TrimStart("@".ToCharArray());
}
}
}
}
}
}
protected void btnExcel_ServerClick(object sender, System.EventArgs e)
{
try
{
DataTable tb = GetDataGridTable();
// tb.Columns.Add(new DataColumn("ProjectName", typeof(string)));
// foreach(DataRow dr in tb.Rows)
// {
// dr["ProjectName"] = BLL.ProjectRule.GetProjectName(this.txtProjectCode.Value);
// }
//导Excel
TExcel excel = new TExcel(Response, Request, Server, Session);
try
{
excel.StartRow = 6;
excel.StartCol = 1;
excel.ColumnHeadRow = 5;
// excel.StartFieldIndex = 3;
excel.DataSource = tb;
//新建工作簿
excel.TemplateFileName = "房屋预拨通知单.xls";
excel.TemplateSheetName = "Sheet1";
excel.AddWorkbook();
//表头表尾数据
excel.SetCellValue("A2", this.lblOutAspect.Text);
excel.SetCellValue("F2", this.lblOutListName.Text);
excel.SetCellValue("B9", this.lblConferMark.Text);
excel.SetCellValue("D9", this.lblRemark.Text);
excel.SetCellValue("E12", this.lblOutDate.Text);
//页脚
// excel.Sheet.PageSetup.RightFooter = "编号：" + this.lblOutListName.Text;
excel.DataToSheet();
//保存
excel.SaveWorkbook();
excel.ShowClient();
}
finally
{
excel.Dispose();
}
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "导出到Excel出错：" + ex.Message));
}
}
/// <summary>
/// 分摊表Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnApportionExcel_ServerClick(object sender, System.EventArgs e)
{
try
{
string ProjectCode = Request["ProjectCode"] + "";
DataTable tb = BLL.CostRule.CostApportionExcel(ProjectCode, BLL.CostRule.GetApportionAreaField(ProjectCode));
if (tb.Rows.Count == 0)
{
Response.Write(JavaScript.Alert(true, "无数据"));
return;
}
DataView dv = new DataView(tb, "", "BuildingName", DataViewRowState.CurrentRows);
//导Excel
TExcel excel = new TExcel(Response, Request, Server, Session);
try
{
excel.StartRow = 6;
excel.StartCol = 1;
excel.ColumnHeadRow = 5;
// excel.StartFieldIndex = 3;
excel.DataSource = dv;
//新建工作簿
excel.TemplateFileName = "成本分摊表.xls";
excel.TemplateSheetName = "Sheet1";
excel.AddWorkbook();
//表头表尾数据
excel.SetCellValue("C2", BLL.ProjectRule.GetProjectName(ProjectCode));
excel.DataToSheet();
//保存
excel.SaveWorkbook();
excel.ShowClient();
}
finally
{
excel.Dispose();
}
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "分摊表Excel出错：" + ex.Message));
}
}
protected void btnExcel_ServerClick(object sender, System.EventArgs e)
{
DataTable tb = LoadDataGrid(0, false);
if (tb == null)
{
return;
}
if (tb.Rows.Count == 0)
{
Response.Write(JavaScript.Alert(true, "无数据"));
return;
}
//导Excel
TExcel excel = new TExcel(Response, Request, Server, Session);
try
{
excel.StartRow = 6;
excel.StartCol = 1;
excel.ColumnHeadRow = 5;
// excel.StartFieldIndex = 3;
excel.DataSource = tb;
//新建工作簿
excel.TemplateFileName = "成本核算表.xls";
excel.TemplateSheetName = "Sheet1";
excel.AddWorkbook();
//表头表尾数据
excel.SetCellValue("C2", BLL.ProjectRule.GetProjectName(this.txtProjectCode.Value));
excel.SetCellValue("G2", this.sltSearchBuildingName.Value.Trim());
//页脚
// excel.Sheet.PageSetup.RightFooter = "单位负责人：" + "陆**" + " 报出日期：" + DateTime.Today;
excel.DataToSheet();
//保存
excel.SaveWorkbook();
excel.ShowClient();
}
finally
{
excel.Dispose();
}
}
public static void DataToColumnHead(DataRow[] drs, Worksheet sheet, int StartRow, int StartCol, int ColHeight, int ColWidth, string[,] Fields, ref int RowNum)
{
if ((drs.Length != 0) && (Fields != null))
{
int num = StartRow;
int num2 = StartCol;
int length = drs.Length;
for (int i = 0; i < length; i++)
{
DataRow dr = drs[i];
for (int j = 0; j < ColHeight; j++)
{
for (int k = 0; k < ColWidth; k++)
{
string columnName = Fields[j, k];
if ((columnName != null) && (columnName.Trim() != ""))
{
Excel.Range range = TExcel.GetCell(sheet, num + j, num2 + k);
try
{
string str;
if (((str = columnName.ToUpper()) != null) && (string.IsInterned(str) == "@@ROWNUM"))
{
RowNum++;
range.Value = (int)RowNum;
}
else
{
range.Value = TExcel.GetItemValue(dr, columnName, false);
}
}
catch
{
}
finally
{
TExcel.DisposeObject(range);
}
}
}
}
num2 += ColWidth;
}
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Excel(bool CloseWindow)
{
try
{
BLL.CostBudgetDynamic dyn = m_dyn;
if (dyn == null)
{
dyn = GenerateDynamic();
}
TExcel excel = new TExcel(Response, Request, Server, Session);
try
{
excel.StartRow = 10;
excel.ColumnHeadRow = 9;
//取1级费用项
DataView dv;
if (dyn.NeedApport) //有分摊时，多了一级总计
{
dv = new DataView(dyn.tb, "Deep=3", "SortID", DataViewRowState.CurrentRows);
}
else
{
dv = new DataView(dyn.tb, "Deep=2", "SortID", DataViewRowState.CurrentRows);
}
excel.DataSource = dv;
//新建工作簿
excel.TemplateFileName = "项目费用表.xls";
// excel.TemplateSheetName = "汇总";
excel.TemplateSheetName = "";
excel.AddWorkbook();
//新建若干“分项”页
for (int k = 2; k <= dv.Count; k++)
{
((Excel.Worksheet)excel.Book.Worksheets[2]).Copy(TExcel.m_Opt, excel.Book.Worksheets[2]);
}
//转到汇总页
excel.Sheet = (Excel.Worksheet)excel.Book.Worksheets[1];
//表头信息
excel.SetCellValue(1, 1, BLL.ProjectRule.GetProjectName(dyn.ProjectCode));
//报表日期
if (this.txtCostBudgetBackupCode.Value != "") //备份
{
excel.SetCellValue(1, 3, dyn.entityBackup.GetDateTime("BackupDate", "yyyy-MM-dd HH:mm:ss"));
}
else
{
excel.SetCellValue(1, 3, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
excel.SetCellValue(1, 5, dyn.GetTargetCheckDate());
excel.SetCellValue(1, 6, dyn.entitySet.GetDecimal("HouseCount"));
excel.SetCellValue(1, 7, dyn.entitySet.GetDecimal("BuildingArea"));
excel.SetCellValue(1, 8, dyn.entitySet.GetDecimal("HouseArea"));
excel.DataSource = dv;
excel.DataToSheet();
//按第2级费用项分组
excel.StartRow = 11;
excel.ColumnHeadRow = 9;
/*
* //2级费用项临时表头
* DataTable tbLevel2 = dyn.entitySet.CurrentTable.Copy();
* BLL.ConvertRule.DataTableAddColumn(dyn.tb, tbLevel2);
*/
//3级费用项临时表（包括合同明细）
DataTable tbDtl = dyn.tb.Clone();
tbDtl.Columns.Add("ReportDtlSno", typeof(int));
DataColumn[] pk = new DataColumn[1];
pk[0] = tbDtl.Columns["ReportDtlSno"];
tbDtl.PrimaryKey = pk;
DataRow drNew;
int i = 0;
foreach (DataRowView drv in dv)
{
i++;
// BLL.ConvertRule.DataRowCopy(drv.Row, tbLevel2.Rows[0], dyn.tb, tbLevel2);
string ParentCostCode = BLL.ConvertRule.ToString(drv["CostCode"]);
string ParentCostName = BLL.ConvertRule.ToString(drv["CostName"]);
//取第2级费用项的子项
DataView dv2 = new DataView(dyn.tb, "ParentCode='" + ParentCostCode + "'", "SortID", DataViewRowState.CurrentRows);
//复制到临时表
tbDtl.Rows.Clear();
int ReportDtlSno = 0;
foreach (DataRowView drv2 in dv2)
{
DataRow dr2 = drv2.Row;
string CostCode = BLL.ConvertRule.ToString(dr2["CostCode"]);
//3级费用项
drNew = tbDtl.NewRow();
BLL.ConvertRule.DataRowCopy(dr2, drNew, dyn.tb, tbDtl);
ReportDtlSno++;
drNew["ReportDtlSno"] = ReportDtlSno;
tbDtl.Rows.Add(drNew);
//3级费用项的合同明细
DataRow[] drsContract = dyn.tbContract.Select("CostCode = '" + CostCode + "'", "ContractID, ContractName", DataViewRowState.CurrentRows);
AddContract(tbDtl, drNew, drsContract, ref ReportDtlSno);
//3级费用项的预留金额
DataRow[] drsNoContract = dyn.tbNoContract.Select("CostCode = '" + CostCode + "'");
AddNoContract(tbDtl, drNew, drsContract, drsNoContract, ref ReportDtlSno);
}
//合计行
drNew = tbDtl.NewRow();
BLL.ConvertRule.DataRowCopy(drv.Row, drNew, dyn.tb, tbDtl);
ReportDtlSno++;
drNew["ReportDtlSno"] = ReportDtlSno;
drNew["CostName"] = "合计";
tbDtl.Rows.Add(drNew);
/*
* //第2个2级费用项开始，每次都要新建工作页
* if (i > 1)
* {
* excel.TemplateSheetName = "分项";
* excel.AddWorksheet(i, true);
* }
*/
//转到分项页
excel.Sheet = (Excel.Worksheet)excel.Book.Worksheets[i + 1];
//一个2级费用项打印一页
excel.Sheet.Name = ParentCostName.Replace("/", "");
excel.DataToSheetSingle(drv.Row);
//2级费用项表头信息
// excel.SetCellValue(1, 1, drv["CostName"]);
DataView dvDtl = new DataView(tbDtl, "", "ReportDtlSno", DataViewRowState.CurrentRows);
excel.DataSource = dvDtl;
excel.DataToSheet();
/*
* //合同明细只有一条时，隐藏合同明细
* int colIsHide = 15;
* for(int k=0;k<dv2.Count;k++)
* {
* int row = excel.StartRow + k;
* string IsHide = TExcel.GetCellValue(excel.Sheet, row, colIsHide);
* if (IsHide == "1")
* {
* ((Excel.Range)excel.Sheet.Cells[row, 1]).EntireRow.Hidden = true;
* }
* }
*
* //隐藏列IsHide
* ((Excel.Range)excel.Sheet.Cells[1, colIsHide]).EntireColumn.Hidden = true;
*/
}
//缺省选中第1页
((Excel.Worksheet)excel.Book.Worksheets[1]).Select(TExcel.m_Opt);
//保存
excel.SaveWorkbook();
excel.ShowClient();
}
finally
{
excel.Dispose();
}
if (CloseWindow)
{
Response.Write(Rms.Web.JavaScript.WinClose(true));
}
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "导出Excel出错：" + ex.Message));
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Excel(bool CloseWindow)
{
try
{
string ProjectCode = this.txtProjectCode.Value;
string StartYm = "" + Request.QueryString["StartYm"];
string EndYm = "" + Request.QueryString["EndYm"];
string PBSType = "" + Request.QueryString["PBSType"];
string PBSCode = "" + Request.QueryString["PBSCode"];
BLL.ContractPayScheduleBySubject sch = new RmsPM.BLL.ContractPayScheduleBySubject(ProjectCode);
sch.PBSType = this.txtPBSType.Value;
sch.PBSCode = this.txtPBSCode.Value;
sch.StartYm = StartYm;
sch.EndYm = EndYm;
sch.Generate();
TExcel excel = new TExcel(Response, Request, Server, Session);
try
{
excel.StartRow = 10;
excel.ColumnHeadRow = 9;
//去掉1级合计
/*
* DataView dv;
* if (dyn.NeedApport) //有分摊时，多了一级总计
* {
* dv = new DataView(dyn.tb, "Deep=3", "SortID", DataViewRowState.CurrentRows);
* }
* else
* {
* dv = new DataView(dyn.tb, "Deep=2", "SortID", DataViewRowState.CurrentRows);
* }
*/
//新建工作簿
excel.TemplateFileName = "付款一览表按科目.xls";
excel.TemplateSheetName = "";
excel.AddWorkbook();
//表头信息
string ProjectName = BLL.ProjectRule.GetProjectName(sch.ProjectCode);
if (PBSType != "")
{
ProjectName += "[" + this.sltPBS.Items[this.sltPBS.SelectedIndex].Text + "]";
}
excel.SetCellValue(1, 1, ProjectName);
//报表日期
excel.SetCellValue(1, 3, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//最后两列是年月明细
int colYm = excel.Sheet.UsedRange.Columns.Count - 1;
if ((StartYm != "") && (EndYm != "")) //有年月明细
{
int MonthCount = BLL.StringRule.GetMonthsBetween(StartYm, EndYm);
//插空列
TExcel.InsertBlankColumn(excel.Sheet, colYm, 1, 2, MonthCount);
int col = colYm - 1;
for (int i = 0; i < MonthCount; i++)
{
col++;
string ym = BLL.StringRule.YmAddMonths(StartYm, i);
//写excel的明细字段定义
excel.SetCellValue(excel.ColumnHeadRow, col, "PayoutMoneyYm_" + ym);
}
}
else //无年月明细
{
//隐藏最后两列（年月明细）
TExcel.HideColumn(excel.Sheet, colYm);
TExcel.HideColumn(excel.Sheet, colYm + 1);
}
excel.DataSource = sch.tb;
excel.DataToSheet();
//保存
excel.SaveWorkbook();
excel.ShowClient();
}
finally
{
excel.Dispose();
}
if (CloseWindow)
{
Response.Write(Rms.Web.JavaScript.WinClose(true));
}
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "导出Excel出错：" + ex.Message));
}
}
/// <summary>
/// 组头
/// </summary>
/// <param name="Sheet"></param>
/// <param name="RowIndex"></param>
/// <param name="GroupFieldValue"></param>
protected void MyProcessGroupHeader(Excel.Worksheet Sheet, int RowIndex, string GroupFieldValue, DataRow drGroup)
{
TExcel.SetCellValue(Sheet, RowIndex, 1, BLL.ProjectRule.GetProjectName(GroupFieldValue));
}
