patterncsharpMinor
Winform that exports a datagrid to Excel
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?
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.xlContext
StackExchange Code Review Q#36605, answer score: 3
Revisions (0)
No revisions yet.