HiveBrain v1.2.0
Get Started
← Back to all entries
patterncsharpMinor

Winform that exports a datagrid to Excel

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
winformexcelthatexportsdatagrid

Problem

I've just created a winform that exports a datagrid to Excel. Originally the file was never shown, but I was asked to give the option. So I put in a checkbox and modified the code.

Is my if/else statement at the end enough or should I go about it a different way?

private void buttonExport_Click(object sender, EventArgs e)
{
    try
    {
        //copy contents of grid into clipboard, open new instance of excel, a new workbook and sheet,
        //paste clipboard contents into new sheet. 
        copyGrid();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Microsoft.Office.Interop.Excel.Application();
        xlexcel.Visible = false;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
        xlWorkBook.SaveAs("C:\\Temp\\ItemUpdate.xls", Excel.XlFileFormat.xlExcel5);
        MessageBox.Show("File Save Successfull", "Information", MessageBoxButtons.OK);
        //If box is checked, show the exported file. Otherwise quit Excel.
        if (checkBox1.Checked == true)
        {
            xlexcel.Visible = true;
        }
        else
        {
            xlexcel.Quit();
        }
    }
    catch (SystemException ex)
    {
        MessageBox.Show(ex.ToString());
    }
    //set the Selection Mode back to Cell Select to avoid conflict with sorting mode
    dataGridView1.SelectionMode = DataGridViewSelectionMode.CellSelect;

Solution

Yes, I'd say the if..else is enough, but it's a little wordy. Button.Checked is already a Boolean value, so comparing it to true is superfluous. There's also a lot of shortening of code you can do with the judicious use of using directives. Also, I had to do some fancy dancing to make sure Excel itself is truly shut down when it needs to be. Give this a look-see and let me know what you think:

namespace WindowsFormsApplication1
{
    using System;
    using System.IO;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;

    using Microsoft.Office.Interop.Excel;

    using Application = Microsoft.Office.Interop.Excel.Application;

    public partial class Form1 : Form
    {
        private Application xlExcel;

        private Workbook xlWorkBook;

        public Form1()
        {
            this.InitializeComponent();
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                this.QuitExcel();
                this.xlExcel = new Application { Visible = false };
                this.xlWorkBook = this.xlExcel.Workbooks.Add(Missing.Value);

                // Copy contents of grid into clipboard, open new instance of excel, a new workbook and sheet,
                // paste clipboard contents into new sheet.
                this.CopyGrid();

                var xlWorkSheet = (Worksheet)this.xlWorkBook.Worksheets.Item[1];

                try
                {
                    var cr = (Range)xlWorkSheet.Cells[1, 1];

                    try
                    {
                        cr.Select();
                        xlWorkSheet.PasteSpecial(cr, NoHTMLFormatting: true);
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(cr);
                    }

                    this.xlWorkBook.SaveAs(Path.Combine(Path.GetTempPath(), "ItemUpdate.xls"), XlFileFormat.xlExcel5);
                }
                finally
                {
                    Marshal.ReleaseComObject(xlWorkSheet);
                }

                MessageBox.Show("File Save Successful", "Information", MessageBoxButtons.OK);

                // If box is checked, show the exported file. Otherwise quit Excel.
                if (this.checkBox1.Checked)
                {
                    this.xlExcel.Visible = true;
                }
                else
                {
                    this.QuitExcel();
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.ToString());
            }

            // Set the Selection Mode back to Cell Select to avoid conflict with sorting mode.
            this.dataGridView1.SelectionMode = DataGridViewSelectionMode.CellSelect;
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            this.QuitExcel();
        }

        private void QuitExcel()
        {
            if (this.xlWorkBook != null)
            {
                try
                {
                    this.xlWorkBook.Close();
                    Marshal.ReleaseComObject(this.xlWorkBook);
                }
                catch (COMException)
                {
                }

                this.xlWorkBook = null;
            }

            if (this.xlExcel != null)
            {
                try
                {
                    this.xlExcel.Quit();
                    Marshal.ReleaseComObject(this.xlExcel);
                }
                catch (COMException)
                {
                }

                this.xlExcel = null;
            }
        }

        private void CopyGrid()
        {
            // I'm making this up...
            this.dataGridView1.SelectAll();

            var data = this.dataGridView1.GetClipboardContent();

            if (data != null)
            {
                Clipboard.SetDataObject(data, true);
            }
        }
    }
}

Code Snippets

namespace WindowsFormsApplication1
{
    using System;
    using System.IO;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;

    using Microsoft.Office.Interop.Excel;

    using Application = Microsoft.Office.Interop.Excel.Application;

    public partial class Form1 : Form
    {
        private Application xlExcel;

        private Workbook xlWorkBook;

        public Form1()
        {
            this.InitializeComponent();
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                this.QuitExcel();
                this.xlExcel = new Application { Visible = false };
                this.xlWorkBook = this.xlExcel.Workbooks.Add(Missing.Value);

                // Copy contents of grid into clipboard, open new instance of excel, a new workbook and sheet,
                // paste clipboard contents into new sheet.
                this.CopyGrid();

                var xlWorkSheet = (Worksheet)this.xlWorkBook.Worksheets.Item[1];

                try
                {
                    var cr = (Range)xlWorkSheet.Cells[1, 1];

                    try
                    {
                        cr.Select();
                        xlWorkSheet.PasteSpecial(cr, NoHTMLFormatting: true);
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(cr);
                    }

                    this.xlWorkBook.SaveAs(Path.Combine(Path.GetTempPath(), "ItemUpdate.xls"), XlFileFormat.xlExcel5);
                }
                finally
                {
                    Marshal.ReleaseComObject(xlWorkSheet);
                }

                MessageBox.Show("File Save Successful", "Information", MessageBoxButtons.OK);

                // If box is checked, show the exported file. Otherwise quit Excel.
                if (this.checkBox1.Checked)
                {
                    this.xlExcel.Visible = true;
                }
                else
                {
                    this.QuitExcel();
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.ToString());
            }

            // Set the Selection Mode back to Cell Select to avoid conflict with sorting mode.
            this.dataGridView1.SelectionMode = DataGridViewSelectionMode.CellSelect;
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            this.QuitExcel();
        }

        private void QuitExcel()
        {
            if (this.xlWorkBook != null)
            {
                try
                {
                    this.xlWorkBook.Close();
                    Marshal.ReleaseComObject(this.xlWorkBook);
                }
                catch (COMException)
                {
                }

                this.xlWorkBook = null;
            }

            if (this.xl

Context

StackExchange Code Review Q#36605, answer score: 3

Revisions (0)

No revisions yet.