private void Populate_Muncde_BSA_ccyy_Y(int finYear)
{
try
{
xlSheet = xlControl.Document.Worksheets["Sheet1"];
xlControl.BeginUpdate();
xlSheet.Workbook.BeginUpdate();
string celladdress = "A6";
//var cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
xlSheet.Cells[celladdress].Value = finYear.ToString();
//QueryDb
string index = string.Empty;
DataRow row = null;
int nStart = 5;
using (DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_BSA_ccyy_YTableAdapter TA = new DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_BSA_ccyy_YTableAdapter())
{
DAL.mSCOA_VaultDataSet.GetMuncde_BSA_ccyy_YDataTable tbl = new DAL.mSCOA_VaultDataSet.GetMuncde_BSA_ccyy_YDataTable();
TA.Fill(tbl);
DataTable tblResult = tbl;
for (int i = 0; i < tblResult.Rows.Count; i++)
{
row = tblResult.Rows[i];
index = row["Code"].ToString();
for (int j = nStart; j < 47; j++)
{
// xlField = xlWorkSheet.Rows.Cells[j, 3];
if (xlSheet.Cells[j, 2].DisplayText == index)
{
//xlField = xlWorkSheet.Rows.Cells[j, 5];
xlSheet.Cells[j, 4].Formula = "= 0" + row["EstYearEnd"].ToString();
nStart = j;
break;
}
}
}
}
}
catch (Exception ex)
{
}
finally
{
xlSheet.Workbook.EndUpdate();
xlControl.EndUpdate();
}
}
private void Populate_Muncde_OSA_ccyy_Mnn(int finYear, string monthEnd)
{
try
{
//xlWorkSheet = (Excel.Worksheet) xlWorkBook.Sheets["Sheet1"];
xlSheet = xlControl.Document.Worksheets["Sheet1"];
xlControl.BeginUpdate();
xlSheet.Workbook.BeginUpdate();
string celladdress = "A10";
//var cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
//xlSheet.Cells[celladdress].Value = finYear.ToString();
xlSheet.Cells[celladdress].Value = finYear.ToString();
//b monthend
celladdress = "B10";
//cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
//cell.Value = monthEnd;
xlSheet.Cells[celladdress].Value = monthEnd;
//c _regionalIdentifier
celladdress = "C10";
// cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
xlSheet.Cells[celladdress].Value = _regionalIdentifier;
//QueryDb
string index = string.Empty;
DataRow row = null;
int nStart = 9;// 10;
using (DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_OSA_ccyy_MnnTableAdapter TA = new DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_OSA_ccyy_MnnTableAdapter())
{
DAL.mSCOA_VaultDataSet.GetMuncde_OSA_ccyy_MnnDataTable tbl = new DAL.mSCOA_VaultDataSet.GetMuncde_OSA_ccyy_MnnDataTable();
TA.Fill(tbl);
DataTable tblResult = tbl; // DBCall.GetMuncde_OSA_ccyy_Mnn();
for (int i = 0; i < tblResult.Rows.Count; i++)
{
row = tblResult.Rows[i];
index = row["Code"].ToString();
if (!string.IsNullOrEmpty(index))
{
for (int j = nStart; j < 3896; j++)
{
//xlField = xlSheet.Rows[j, 11].Value ;
if (xlSheet.Cells[j, 10].DisplayText == index)
{
//xlField = xlWorkSheet.Rows.Cells[j, 10];
decimal val = 0;
decimal.TryParse(row[monthEnd].ToString(), out val);
xlSheet.Cells[j, 9].Formula = "= " + val.ToString();
nStart = j;
break;
}
}
}
}
}
}
catch (Exception ex)
{
}
finally
{
xlSheet.Workbook.EndUpdate();
xlControl.EndUpdate();
}
}