Ejemplo n.º 1
0
        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());
                }
            }
        }