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; } }