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 }