public void AEExportDataToExcel(string fileName, string[] tableNames, string[] sheetNames, DataSet ds, int[] irow, DateTime generateDate)
{
PreExitExcel();
string filename1 = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename1, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableNames.Length; i++)
{
_IniRow = irow[i];
switch (tableNames[i].ToString())
{
case "AnnuityOpenPeriod":
case "AnnuityUrgent":
case "AnnuityStatusUrgent":
_SName = "AnnuityFinal";
break;
case "AnnuityRpt":
_SName = "AnnuityRpt";
break;
case "AnnuityStatus":
_SName = "AnnuityStatus";
break;
case "EBusinessRpt":
_SName = "EBusinessRpt Grouped";
break;
default: ;
break;
}
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[_SName];
for (int j = 0; j < ds.Tables[tableNames[i]].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableNames[i]].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableNames[i]].Rows[j][k].ToString();
}
_IniRow++;
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename1, strDate);
_ExcelApp.Rows.RowHeight = "15";
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelApp.DisplayAlerts = false;
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
public void AEExportDataToExcel(string fileName, string[] tableNames, string[] sheetNames, DataSet ds, int[] irow, DateTime generateDate)
{
PreExitExcel();
string filename1 = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename1, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableNames.Length; i++)
{
_IniRow = irow[i];
switch (tableNames[i].ToString())
{
case "AnnuityOpenPeriod":
case "AnnuityUrgent":
case "AnnuityStatusUrgent":
_SName = "AnnuityFinal";
break;
case "AnnuityRpt":
_SName = "AnnuityRpt";
break;
case "AnnuityStatus":
_SName = "AnnuityStatus";
break;
case "EBusinessRpt":
_SName = "EBusinessRpt Grouped";
break;
default:;
break;
}
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[_SName];
for (int j = 0; j < ds.Tables[tableNames[i]].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableNames[i]].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableNames[i]].Rows[j][k].ToString();
}
_IniRow++;
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename1, strDate);
_ExcelApp.Rows.RowHeight = "15";
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelApp.DisplayAlerts = false;
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
public void ExportDataToExcel(string fileName, string[] tableName, string[] sheetName, DataSet ds, DateTime generateDate)
{
PreExitExcel();
string filename = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableName.Length; i++)
{
if (tableName[i] == "TriBenchMarkRpt_MatchedBacklog")
{
_IniRow = 2;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 2] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
else
{
_IniRow = 3;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
int rowcount = ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename, strDate);
_ExcelApp.Rows.RowHeight = "15";
//ExApp.DisplayAlerts = false;
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
public void ExportDataToExcel(string fileName, string[] tableName, string[] sheetName, DataSet ds, DateTime generateDate)
{
PreExitExcel();
string filename = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableName.Length; i++)
{
if (tableName[i] == "TriBenchMarkRpt_MatchedBacklog")
{
_IniRow = 2;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 2] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
else
{
_IniRow = 3;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
int rowcount = ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename, strDate);
_ExcelApp.Rows.RowHeight = "15";
//ExApp.DisplayAlerts = false;
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
