Introduction:
In this article I will introduce a way to create excel files from datagrid in windows forms. Plus I will introduce a way to close the excel process which is created with the creation of the excel file
On to creation:
First thing we need to do is reference Excel. Go to project-.add reference ->.net and select Microsoft.Office.Tools.Excel
Now we are ready to go.
Let us create a class (let’s call it CreateExcel). The basic function in this class will be for the creation of the Excel and we will pass a GridView and the full name of the excel to be created. Now lets get our hands into code:
public void createIt(System.Windows.Forms.DataGridView myGridView, String FullFileName)
{
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try
{
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
Int32 i, j;
//header
for (j = 0; j < myGridView.ColumnCount; j++)
{
oSheet.Cells[1, j + 1] = myGridView.Columns[j].HeaderText;
}
//Rows
for (i = 0; i < myGridView.RowCount; i++)
{
for (j = 0; j < myGridView.ColumnCount; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1];
oRng.NumberFormat = "@";
oSheet.Cells[i + 2, j + 1] = Convert.ToString(myGridView.Rows
.Cells[j].Value);
}
}
//save it
oWB.SaveAs(FullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel4Workbook, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
}
What we do basically is after some initializations iterate to get the headers of columns and then iterate through rows and columns to create the excel cells. We also set the format of each cell to text so we don’t have issues with big numbers
(
oRng.NumberFormat = "@";
)
Now on to the big question. There are several approaches to stop the created excel proccess. Flushing, closing, using garbage collector. None of these seems to do our trick . So taking the hard road we will make sure processes gets killed … by killing it :D
We will need a Hashtable to store the process Ids of excel processes before creating our process. GetExcellProcesses can do the trick
private void GetExcellProcesses()
{
try
{
//lets get all excel processes
Process[] AllProcesses = Process.GetProcessesByName("excel");
myExcelHashtable = new Hashtable();
int iCount = 0;
foreach (Process ExcelProcess in AllProcesses)
{
myExcelHashtable.Add(ExcelProcess.Id, iCount);
iCount = iCount + 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
Then we will need to kill our created process when we are finished
private void KillMyExcelProcess()
{
try
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// we need to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
if (myExcelHashtable.ContainsKey(ExcelProcess.Id) == false)
ExcelProcess.Kill();
}
AllProcesses = null;
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
So here it is , the complete class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Collections;
namespace xxx
{
class CreateExcel
{
Hashtable myExcelHashtable;
public void createIt(System.Windows.Forms.DataGridView myGridView, String FullFileName)
{
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try
{
// get process ids before excel creation
GetExcellProcesses();
//on to excel creation
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
Int32 i, j;
//header
for (j = 0; j < myGridView.ColumnCount; j++)
{
oSheet.Cells[1, j + 1] = myGridView.Columns[j].HeaderText;
}
//Rows
for (i = 0; i < myGridView.RowCount; i++)
{
for (j = 0; j < myGridView.ColumnCount; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1];
oRng.NumberFormat = "@";
oSheet.Cells[i + 2, j + 1] = Convert.ToString(myGridView.Rows.Cells[j].Value);
}
}
//save it
oWB.SaveAs(FullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel4Workbook, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
// kill the right process after export completed
KillMyExcelProcess();
}
}
private void GetExcellProcesses()
{
try
{
//lets get all excel processes
Process[] AllProcesses = Process.GetProcessesByName("excel");
myExcelHashtable = new Hashtable();
int iCount = 0;
foreach (Process ExcelProcess in AllProcesses)
{
myExcelHashtable.Add(ExcelProcess.Id, iCount);
iCount = iCount + 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
private void KillMyExcelProcess()
{
try
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// we need to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
if (myExcelHashtable.ContainsKey(ExcelProcess.Id) == false)
ExcelProcess.Kill();
}
AllProcesses = null;
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
}
}