public void createReport(string saveFileName, string basePath, string sqlQueryPath, string fileNameFormat) { System.IO.StreamReader inputStream = new StreamReader(basePath + sqlQueryPath); string sqlCmdText = inputStream.ReadToEnd(); inputStream.DiscardBufferedData(); inputStream.Close(); inputStream.Dispose(); SqlConnection sqlConn = new SqlConnection(CCFBGlobal.connectionString); sqlConn.Open(); SqlCommand sqlCmd = new SqlCommand(sqlCmdText, sqlConn); SqlDataAdapter sqlDataAdpt = new SqlDataAdapter(sqlCmd); System.Data.DataTable tblWork = new System.Data.DataTable(); int nbrRows = sqlDataAdpt.Fill(tblWork); if (nbrRows > 0) { Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application(); appExcel.Visible = true; Microsoft.Office.Interop.Excel._Workbook xlsWorkBook; Microsoft.Office.Interop.Excel._Worksheet xlsSheet; xlsSheet = null; object missing = Type.Missing; try { xlsWorkBook = appExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); xlsSheet = (_Worksheet)xlsWorkBook.ActiveSheet; int idxCol = 1; foreach (DataColumn dCol in tblWork.Columns) { xlsSheet.Cells[1, dCol.Ordinal + 1] = dCol.Caption; } int newRow = 1; foreach (DataRow dRow in tblWork.Rows) { newRow++; foreach (DataColumn dCol in tblWork.Columns) { idxCol = dCol.Ordinal + 1; switch (dCol.DataType.ToString()) { case "System.String": xlsSheet.Cells[newRow, idxCol] = dRow[dCol.Ordinal].ToString(); break; case "System.Int32": xlsSheet.Cells[newRow, idxCol] = Convert.ToInt32(dRow[dCol.Ordinal]); break; default: xlsSheet.Cells[1, dCol.Ordinal] = dCol.Caption; break; } } } //excelRange = (Microsoft.Office.Interop.Excel.Range)appExcel.Selection; //excelRange.Columns.AutoFit(); string tmp = "A:" + CCFBGlobal.ExcelColumnLetter(tblWork.Columns.Count); appExcel.ActiveSheet.Columns(tmp).EntireColumn.AutoFit(); appExcel.ActiveSheet.Rows("1:1").Select(); appExcel.Selection.Font.Bold = true; appExcel.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"; appExcel.ActiveSheet.PageSetup.PrintTitleColumns = ""; System.IO.StreamReader fileFormat = new StreamReader(basePath + fileNameFormat); string txtFmt = ""; do { txtFmt = fileFormat.ReadLine(); string [] fmtdata = txtFmt.Split(']'); fmtdata[1] = fmtdata[1].TrimStart(' '); switch (fmtdata[0].Replace("[", "")) { case "Border": DoBorder(appExcel, fmtdata[1], fmtdata[2]); break; case "ColumnTotals": doColumnTotals(appExcel, fmtdata[1]); break; case "ColumnAlignCenter": doColumnAlignCenter(appExcel, fmtdata[1]); break; case "CenterHeaderText": appExcel.ActiveSheet.PageSetup.CenterHeader = fmtdata[1]; break; case "CenterFooterText": appExcel.ActiveSheet.PageSetup.CenterFooter = fmtdata[1]; break; case "Filename": saveFileName = fmtdata[1]; break; case "Margins": string[] sMargins = fmtdata[1].Split(','); appExcel.ActiveSheet.PageSetup.LeftMargin = appExcel.Application.InchesToPoints(Convert.ToDouble(sMargins[0])); appExcel.ActiveSheet.PageSetup.RightMargin = appExcel.Application.InchesToPoints(Convert.ToDouble(sMargins[1])); appExcel.ActiveSheet.PageSetup.TopMargin = appExcel.Application.InchesToPoints(Convert.ToDouble(sMargins[2])); appExcel.ActiveSheet.PageSetup.BottomMargin = appExcel.Application.InchesToPoints(Convert.ToDouble(sMargins[3])); appExcel.ActiveSheet.PageSetup.HeaderMargin = appExcel.Application.InchesToPoints(Convert.ToDouble(sMargins[4])); appExcel.ActiveSheet.PageSetup.FooterMargin = appExcel.Application.InchesToPoints(Convert.ToDouble(sMargins[5])); break; case "Orientation": if (fmtdata[1] == "Landscape") { appExcel.ActiveSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; } else { appExcel.ActiveSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; } break; case "PaperSize": if (fmtdata[1] == "Legal") { appExcel.ActiveSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLegal; } else { appExcel.ActiveSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLetter; } break; case "RepeatHeader": appExcel.ActiveCell.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell).Select(); string lastRow = (appExcel.ActiveCell.Row + 1).ToString(); tmp = fmtdata[1].Replace("1", lastRow); appExcel.ActiveSheet.Range(fmtdata[1]).Select(); appExcel.Selection.Copy(); appExcel.ActiveSheet.Range(tmp).Select(); appExcel.ActiveSheet.Paste(); break; case "RightFooterText": appExcel.ActiveSheet.PageSetup.RightFooter = fmtdata[1]; break; case "Sheetname": appExcel.ActiveSheet.Name = fmtdata[1]; break; case "Zoom": appExcel.ActiveSheet.PageSetup.Zoom = Convert.ToInt32(fmtdata[1]); break; default: break; } } while (fileFormat.EndOfStream == false); fileFormat.DiscardBufferedData(); fileFormat.Close(); fileFormat.Dispose(); //for (int j = 1; j <= tblWork.Columns.Count; j++) //{ // xlsSheet.Columns("C:C").Select; // excelRange.EntireColumn.AutoFit(); //} xlsWorkBook.SaveAs(saveFileName, XlFileFormat.xlWorkbookNormal, null, null, null, null, XlSaveAsAccessMode.xlShared, null, null, null, null, null); appExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); xlsSheet = null; xlsWorkBook = null; appExcel = null; //error = false; CCFBGlobal.openDocumentOutsideCCFB(saveFileName.ToString()); } catch (Exception ex) { appExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); //error = true; xlsSheet = null; xlsWorkBook = null; appExcel = null; //MessageBox.Show("Error accessing Excel: " + ex.ToString()); } } }