Example #1
0
        //Exports a DataView to Excel. The following steps are carried out
        //in order to export the DataView to Excel
        //Create Excel Objects
        //Create Column & Row Workbook Cell Rendering Styles
        //Fill Worksheet With DataView
        //Add Auto Shapes To Excel Worksheet
        //Select All Used Cells
        //Create Headers/Footers
        //Set Status Finished
        //Save workbook & Tidy up all objects
        //@param dv : DataView to use
        //@param path : The path to save/open the EXCEL file to/from
        //@param sheetName : The target sheet within the EXCEL file
        public void ExportToExcel(List<DataView> dvList, string path, string sheetName)
        {
            try
            {
                //Assign Instance Fields
                this.dvList = dvList;

                #region NEW EXCEL DOCUMENT : Create Excel Objects

                //create new EXCEL application
                // EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //index to hold location of the requested sheetName in the workbook sheets
                //collection
                int indexOfsheetName;

                #region FILE EXISTS

                //Does the file exist for the given path
                if (File.Exists(path))
                {
                    //Yes file exists, so open the file
                    workbook = EXL.Workbooks.Open(path,
                        0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);

                    //get the workbook sheets collection
                    sheets = workbook.Sheets;

                    //set the location of the requested sheetName to -1, need to find where
                    //it is. It may not actually exist
                    indexOfsheetName = -1;

                    //loop through the sheets collection
                    for (int i = 1; i <= sheets.Count; i++)
                    {
                        //get the current worksheet at index (i)
                        worksheet = (Worksheet)sheets.get_Item(i);

                        //is the current worksheet the sheetName that was requested
                        if (worksheet.Name.ToString().Equals(sheetName))
                        {
                            //yes it is, so store its index
                            indexOfsheetName = i;

                            //Select all cells, and clear the contents
                            Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
                            myAllRange.Select();
                            myAllRange.CurrentRegion.Select();
                            myAllRange.ClearContents();
                        }
                    }

                    //At this point it is known that the sheetName that was requested
                    //does not exist within the found file, so create a new sheet within the
                    //sheets collection
                    if (indexOfsheetName == -1)
                    {
                        //Create a new sheet for the requested sheet
                        Worksheet sh = (Worksheet)workbook.Sheets.Add(
                            Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
                            Type.Missing, Type.Missing);
                        //Change its name to that requested
                        sh.Name = sheetName;
                    }
                }

                #endregion FILE EXISTS

                #region FILE DOESNT EXIST

                //No the file DOES NOT exist, so create a new file
                else
                {
                    //Add a new workbook to the file
                    workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    //get the workbook sheets collection
                    sheets = workbook.Sheets;
                    //get the new sheet
                    worksheet = (Worksheet)sheets.get_Item(1);
                    //Change its name to that requested
                    worksheet.Name = sheetName;
                }

                #endregion FILE DOESNT EXIST

                #region get correct worksheet index for requested sheetName

                //get the workbook sheets collection
                sheets = workbook.Sheets;

                //set the location of the requested sheetName to -1, need to find where
                //it is. It will definately exist now as it has just been added
                indexOfsheetName = -1;

                //loop through the sheets collection
                for (int i = 1; i <= sheets.Count; i++)
                {
                    //get the current worksheet at index (i)
                    worksheet = (Worksheet)sheets.get_Item(i);

                    //is the current worksheet the sheetName that was requested
                    if (worksheet.Name.ToString().Equals(sheetName))
                    {
                        //yes it is, so store its index
                        indexOfsheetName = i;
                    }
                }

                //set the worksheet that the DataView should write to, to the known index of the
                //requested sheet
                worksheet = (Worksheet)sheets.get_Item(indexOfsheetName);

                #endregion get correct worksheet index for requested sheetName

                #endregion NEW EXCEL DOCUMENT : Create Excel Objects

                // Set styles 1st
                SetUpStyles();
                //Fill EXCEL worksheet with DataView values
                FillWorksheet_WithDataView();

                ////Add the autoshapes to EXCEL
                //AddAutoShapesToExcel();

                //Select all used cells within current worksheet
                SelectAllUsedCells();

                #region Finish and Release

                try
                {
                    NAR(sheets);
                    NAR(worksheet);
                    workbook.Close(true, path, Type.Missing);
                    NAR(workbook);

                    EXL.UserControl = false;

                    EXL.Quit();
                    NAR(EXL);

                    //kill the EXCEL process as a safety measure
                    KillExcel();
                    // Show that processing is finished
                    ProgressEventArgs pe = new ProgressEventArgs(100);
                    OnProgressChange(pe);

                    //MessageBox.Show("Finished adding dataview to Excel", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (COMException cex)
                {
                    MessageBox.Show("User closed Excel manually, so we don't have to do that");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error " + ex.Message);
                }

                #endregion Finish and Release
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex.Message);
            }
        }
Example #2
0
 /// Raises the OnProgressChange event for the parent form.
 public virtual void OnProgressChange(ProgressEventArgs e)
 {
     if (OnProgressHandler != null)
     {
         // Invokes the delegates.
         OnProgressHandler(this, e);
     }
 }
Example #3
0
        //Fills an Excel worksheet with the values contained in the DataView
        //parameter
        private void FillWorksheet_WithDataView()
        {
            position = 0;
            //Add DataView Columns To Worksheet
            int row = 1;
            int col = 1;

            int rowIndex = 1;//记录写入Excel中Row的序号

            int allRowsCount = 0;
            foreach (DataView dv in dvList)
            {
                allRowsCount += dv.Count + 1;//列头部有一行
            }

            foreach (DataView dv in dvList)
            {
                // Loop thought the columns
                for (int i = 0; i < dv.Table.Columns.Count; i++)
                {
                    FillExcelCell(worksheet, row, col++, dv.Table.Columns[i].ToString(), styleColumnHeadings.Name);
                }

                //Add DataView Rows To Worksheet
                row++;
                col = 1;
                rowIndex++;

                for (int i = 0; i < dv.Table.Rows.Count; i++)
                {
                    for (int j = 0; j < dv.Table.Columns.Count; j++)
                    {
                        FillExcelCell(worksheet, row, col++, dv[i][j].ToString(), styleRows.Name);
                    }

                    position = (100 * rowIndex) / (allRowsCount);
                    ProgressEventArgs pe = new ProgressEventArgs(position);
                    OnProgressChange(pe);

                    col = 1;
                    row++;
                    rowIndex++;
                }

                row = row + 2;//第二个开始空两行
            }
        }
Example #4
0
        //Fills the Excel Template File Selected With A 2D Test Array parameter
        private void FillTemplate_WithTestValues()
        {
            //Initilaise the correct Start Row/Column to match the Template
            int StartRow = 3;
            int StartCol = 2;

            position = 0;

            // Display the array elements within the Output window, make sure its correct before
            for (int i = 0; i <= myTemplateValues.GetUpperBound(0); i++)
            {
                //loop through array and put into EXCEL template
                for (int j = 0; j <= myTemplateValues.GetUpperBound(1); j++)
                {
                    //update position in progress bar
                    position = (100 / myTemplateValues.Length) * i;
                    ProgressEventArgs pe = new ProgressEventArgs(position);
                    OnProgressChange(pe);

                    //put into EXCEL template
                    Range rng = (Range)worksheet.Cells[StartRow, StartCol++];
                    rng.Select();
                    rng.Value2 = myTemplateValues[i, j].ToString();
                    rng.Rows.EntireRow.AutoFit();
                }
                //New row, so column needs to be reset
                StartCol = 2;
                StartRow++;
            }
        }
Example #5
0
        //Exports a DataView to Excel. The following steps are carried out
        //in order to export the DataView to Excel
        //Create Excel Objects And Open Template File
        //Select All Used Cells
        //Create Headers/Footers
        //Set Status Finished
        //Save workbook & Tidy up all objects
        //@param path : The path to save/open the EXCEL file to/from
        public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
        {
            try
            {
                this.myTemplateValues = myTemplateValues;
                //create new EXCEL application
                ////  EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //Yes file exists, so open the file
                //workbook = EXL.Workbooks.Open(templatePath,
                //    0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                //    true, false, 0, true, false, false);
                ////get the workbook sheets collection
                //sheets = workbook.Sheets;
                ////get the new sheet
                //worksheet = (Worksheet)sheets.get_Item(1);
                ////Change its name to that requested
                //worksheet.Name = "ATemplate";
                ////Fills the Excel Template File Selected With A 2D Test Array
                //FillTemplate_WithTestValues();
                ////Select all used cells within current worksheet
                //SelectAllUsedCells();

                #region Finish and Release

                try
                {
                    NAR(sheets);
                    NAR(worksheet);
                    workbook.Close(true, path, Type.Missing);
                    NAR(workbook);

                    EXL.UserControl = false;

                    EXL.Quit();
                    NAR(EXL);

                    //kill the EXCEL process as a safety measure
                    KillExcel();
                    // Show that processing is finished
                    ProgressEventArgs pe = new ProgressEventArgs(100);
                    OnProgressChange(pe);

                    //MessageBox.Show("Finished adding test values to Template", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (COMException)
                {
                    Console.WriteLine("User closed Excel manually, so we don't have to do that");
                }

                #endregion Finish and Release
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex.Message);
            }
        }