示例#1
0
        private void SaveAllDataToExcel(string PathToOutput)
        {
            string FileName     = "AllAccess_";
            string localDateStr = DateTime.Now.ToString().Replace(":", "").Replace(".", "");
            string PathFull     = PathToOutput + "" + FileName + "_" + localDateStr + ".xlsx";

            /*
             * Excel.Application excelapp = new Excel.Application();
             * Excel.Workbook workbook = excelapp.Workbooks.Add();
             * Excel.Worksheet worksheet = workbook.ActiveSheet;
             * foreach (Excel.Worksheet ws in workbook.Worksheets)
             * {
             *  if (worksheet != ws)
             *  {
             *      ws.Delete();
             *  }
             * }
             *
             * if (dataGridViewFBUSearch.RowCount > 0)
             * {
             *  Excel.Worksheet worksheet5 = workbook.Worksheets.Add();
             *  worksheet5.Name = "FBUTree";
             *  FillWorkSheet(dataGridViewFBUSearch, worksheet5);
             * }
             * if (dataGridViewFBUManager.RowCount > 0)
             * {
             *  Excel.Worksheet worksheet4 = workbook.Worksheets.Add();
             *  worksheet4.Name = "FBUManager";
             *  FillWorkSheet(dataGridViewFBUManager, worksheet4);
             * }
             * if (dataGridViewInvoicing.RowCount > 0)
             * {
             *  Excel.Worksheet worksheet3 = workbook.Worksheets.Add();
             *  worksheet3.Name = "Invoicing";
             *  FillWorkSheet(dataGridViewInvoicing, worksheet3);
             * }
             * if (dataGridViewTRM.RowCount > 0)
             * {
             *  Excel.Worksheet worksheet2 = workbook.Worksheets.Add();
             *  worksheet2.Name = "TRM";
             *  FillWorkSheet(dataGridViewTRM, worksheet2);
             * }
             * if (dataGridViewED.RowCount > 0)
             * {
             *  Excel.Worksheet worksheet1 = workbook.Worksheets.Add();
             *  worksheet1.Name = "ED";
             *  FillWorkSheet(dataGridViewED, worksheet1);
             * }
             * if (workbook.Worksheets.Count > 1)
             * {
             *  worksheet.Delete();
             * }
             *
             * string localDateStr = DateTime.Now.ToString().Replace(":", "").Replace(".", "");
             * excelapp.AlertBeforeOverwriting = false;
             * workbook.SaveAs(PathToOutput + "AllAccess_" + localDateStr + ".xlsx");
             * excelapp.Quit();
             *
             * MessageBox.Show("Save completed. " + PathToOutput + "\\AllAccess_" + localDateStr + ".xlsx");
             */

            Int32     SheetsCount  = 0;
            ExcelData ExcelDataNew = new ExcelData();

            if (dataGridViewED.RowCount > 0)
            {
                ExcelDataNew.ExportDataSet((DataTable)dataGridViewED.DataSource, PathFull, "ED", true);
                SheetsCount++;
            }
            if (dataGridViewTRM.RowCount > 0)
            {
                ExcelDataNew.ExportDataSet((DataTable)dataGridViewTRM.DataSource, PathFull, "TRM", false);
                SheetsCount++;
            }
            if (dataGridViewInvoicing.RowCount > 0)
            {
                ExcelDataNew.ExportDataSet((DataTable)dataGridViewInvoicing.DataSource, PathFull, "Invoicing", false);
                SheetsCount++;
            }
            if (dataGridViewFBUManager.RowCount > 0)
            {
                ExcelDataNew.ExportDataSet((DataTable)dataGridViewFBUManager.DataSource, PathFull, "FBUManager", false);
                SheetsCount++;
            }
            if (dataGridViewFBUSearch.RowCount > 0)
            {
                ExcelDataNew.ExportDataSet((DataTable)dataGridViewFBUSearch.DataSource, PathFull, "FBUTree", false);
                SheetsCount++;
            }

            Excel.Application ObjExcel    = new Excel.Application();
            Excel.Workbook    ObjWorkBook = ObjExcel.Workbooks.Open(PathFull, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            for (Int32 i = 1; i <= SheetsCount; i++)
            {
                Excel.Worksheet ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[i];
                for (int j = 1; j < 10; j++)
                {
                    ObjWorkSheet.Rows[1].Columns[j].EntireRow.Font.Bold = true;
                    ObjWorkSheet.Rows[1].Columns[j].EntireColumn.AutoFit();
                }
            }
            ObjExcel.AlertBeforeOverwriting = false;
            ObjWorkBook.Save();
            ObjExcel.Quit();

            MessageBox.Show("Save completed. " + PathFull);
        }
示例#2
0
        //Results to excel
        private void SaveDataToExcel(string PathToOutput)
        {
            DataGridView DataGridIOutput = new DataGridView();
            string       FileName        = this.tabControlSystems.SelectedTab.Name;
            string       localDateStr    = DateTime.Now.ToString().Replace(":", "").Replace(".", "");
            string       PathFull        = PathToOutput + "" + FileName + "_" + localDateStr + ".xlsx";

            switch (FileName)
            {
            case "ED":
                DataGridIOutput = dataGridViewED;
                break;

            case "TRM":
                DataGridIOutput = dataGridViewTRM;
                break;

            case "Invoicing":
                DataGridIOutput = dataGridViewInvoicing;
                break;

            case "FBUManager":
                DataGridIOutput = dataGridViewFBUManager;
                break;

            case "FBUTree":
                DataGridIOutput = dataGridViewFBUSearch;
                break;

            case "FBUVersion":
                DataGridIOutput = dataGridViewFBUVersion;
                break;
            }

            /*
             * Excel.Application excelapp = new Excel.Application();
             * Excel.Workbook workbook = excelapp.Workbooks.Add();
             * Excel.Worksheet worksheet = workbook.Worksheets.Add();
             * foreach (Excel.Worksheet ws in workbook.Worksheets)
             * {
             *  if (worksheet != ws)
             *  {
             *      ws.Delete();
             *  }
             * }
             *
             * worksheet.Name = this.tabControlSystems.SelectedTab.Name;
             *
             * FillWorkSheet(DataGridIOutput, worksheet);
             * excelapp.AlertBeforeOverwriting = false;
             * workbook.SaveAs(PathFull);
             * excelapp.Quit();
             *
             * MessageBox.Show("Save completed. " + PathFull);
             */

            ExcelData ExcelDataNew = new ExcelData();

            ExcelDataNew.ExportDataSet((DataTable)DataGridIOutput.DataSource, PathFull, FileName, true);

            Excel.Application ObjExcel     = new Excel.Application();
            Excel.Workbook    ObjWorkBook  = ObjExcel.Workbooks.Open(PathFull, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            Excel.Worksheet   ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1];
            for (int j = 1; j < 10; j++)
            {
                ObjWorkSheet.Rows[1].Columns[j].EntireRow.Font.Bold = true;
                ObjWorkSheet.Rows[1].Columns[j].EntireColumn.AutoFit();
            }
            ObjExcel.AlertBeforeOverwriting = false;
            ObjWorkBook.Save();
            ObjExcel.Quit();

            MessageBox.Show("Save completed. " + PathFull);
        }