public void exportDataExcel(DataSet dsTemp, string reportName)
{
ArrayList paramArraylist = new ArrayList();
string Excelpath = ConfigurationSettings.AppSettings["ExcelFile"] + "CarrierReports.xlsx";
string dateTime = "CarrierReports " + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString() + ".xlsx";
string Destpath = ConfigurationSettings.AppSettings["ExcelFile"] + dateTime;
DataSet DsExportedData = (DataSet)dsTemp;
File.Copy(Excelpath, Destpath);
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Workbook workbookDummy = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Range chartrange;
#region Excel
//workbook = XcelApp.Workbooks.Open(Destpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true); SVN
try
{
if (DsExportedData != null)
{
if (DsExportedData.Tables[0] != null)
{
#region TemplateData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateData = (DataTable)DsExportedData.Tables[0];
for (int j = 0; j < dtTemplateData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "D1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.Save();
#endregion
}
if (DsExportedData.Tables[1] != null)
{
#region TemplateBillingData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateBillingData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateBillingData = (DataTable)DsExportedData.Tables[1];
for (int j = 0; j < dtTemplateBillingData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateBillingData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateBillingData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "E1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.RefreshAll();
workbook.Save();
#endregion
}
else
{
}
}
else
{
}
// workbook.Close(); SVN
}
catch (Exception ex)
{
// workbook.Close(); SVN
ErrorHandler.WriteError(ex.Message);
}
#endregion
}
