By Sacha Barber
Introduction
There is not really much to say about this article except that it shows how to automate Microsoft Excel through C#. More precisley, what it shows is how to export a C# GridView/DataView to Excel, and how to format the resultant Excel spreadsheet cells. It also shows how to place WordArt and how to use a custom template, where a few values are filled in at predetermined cell positions.
The Associated GUI
Normally, I would say not to worry too much about the GUI, but in this one, there are some nice features like how do use the BackgroundWorker object and how to use Invoke to marshal threads to allow different threads to change GUI component properties. It also shows the use of anonomous delegates which is something new to .NET (old news in Java).
This is all really covered in the following areas.
//need to call InvokeRequired to check if thread
//need marshalling, to get the thread onto
//the same thread as the thread who owns the controls
if (this.InvokeRequired)
{
this.Invoke(new EventHandler(delegate
{
progressBar1.Value = e.ProgressValue;
}));
}
else
{
progressBar1.Value = e.ProgressValue;
}
In this snippet, we can see two of the three points mentioned above (although this is nothing to do with Excel automation, it is code of interest I hope), but the export2Excel object raises an event, which is meant to update the GUI. But the GUI component handles were created on a different thread, so we need to use Invoke to get onto the correct thread to change the GUI component properties or call their methods, so why not chuck in an anonomous delegate to demo this.
The code is probably the best place to look at this, for a complete understanding. This article is really about the excel automation so lets stick to that. I just thought I mention this stuff here.
Excel office automation
So what does this code do??
Well it does two things:
* It exports a GridView to Excel and colors cells, applies formatting etc., and also adds some WordArt. All the normal Excel stuff that one might want to do. It then saves this as a new Excel document, as specified by the user.
* It also uses a premade template and puts some values in the correct cell positions and saves that as a new Excel document, as specified by the user.
Using the code
The demo project attached actually contains a Visual Studio 2005 solution, with the following three classes:
Program class
This is the main entry point into the GridviewToExcel application. Essentially, all this class does is create a new Form1 object.
using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace GridviewToExcel
{
static class Program
{
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Form1 class (Designer code not shown, see zip file)
Queries access database (part of the zip) to create a new GridView, and exports the data to a new Excel document, or creates a new Excel document from an existing template, based on what button was clicked.
Collapse
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
namespace GridviewToExcel
{
public partial class Form1 : Form
{
//instance fields
private export2Excel export2XLS;
private DataSet _dataSet;
public Form1()
{
InitializeComponent();
}
//load the gridview using the local access database
private void Form1_Load(object sender, EventArgs e)
{
Directory.SetCurrentDirectory(Application.StartupPath +
@"..\..\..\");
String accessPath = Directory.GetCurrentDirectory() +
@"\Northwind.mdb";
// Set the connection and sql strings
// assumes your mdb file is in your root
string connString =
@"Provider=Microsoft.JET.OLEDB.4.0;data source=" + accessPath;
string sqlString = "SELECT * FROM customers";
OleDbDataAdapter dataAdapter = null;
_dataSet = null;
try
{
// Connection object
OleDbConnection connection = new OleDbConnection(connString);
// Create data adapter object
dataAdapter = new OleDbDataAdapter(sqlString, connection);
// Create a dataset object and fill with data
// using data adapter's Fill method
_dataSet = new DataSet();
dataAdapter.Fill(_dataSet, "customers");
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Problem with DB access-\n\n connection: "
+ connString + "\r\n\r\n query: " + sqlString
+ "\r\n\r\n\r\n" + ex.ToString());
this.Close();
return;
}
DataView dvCust = _dataSet.Tables["customers"].DefaultView;
dg1.DataSource = dvCust;
}
//Do a straight export to a new Excel document
//of the gridviews grid data
private void btn2Excel_Click(object sender, EventArgs e)
{
//show a file save dialog and ensure the user selects
//correct file to allow the export
saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (!saveFileDialog1.FileName.Equals(String.Empty))
{
FileInfo f = new FileInfo(saveFileDialog1.FileName);
if (f.Extension.Equals(".xls"))
{
StartExport(saveFileDialog1.FileName);
}
else
{
MessageBox.Show("Invalid file type");
}
}
else
{
MessageBox.Show("You did pick a location " +
"to save file to");
}
}
}
//starts the export to new excel document
//@param filepath : the file to export to
private void StartExport(String filepath)
{
btn2Excel.Enabled = false;
btnUseTemplate.Enabled = false;
//create a new background worker, to do the exporting
BackgroundWorker bg = new BackgroundWorker();
bg.DoWork += new DoWorkEventHandler(bg_DoWork);
bg.RunWorkerCompleted +=
new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);
bg.RunWorkerAsync(filepath);
//create a new export2XLS object, providing
//DataView as a input parameter
export2XLS = new export2Excel();
export2XLS.prg +=
new export2Excel.ProgressHandler(export2XLS_prg);
}
//do the new excel document work using the background worker
private void bg_DoWork(object sender, DoWorkEventArgs e)
{
//get the Gridviews DataView
DataView dv = _dataSet.Tables["customers"].DefaultView;
//Pass the path and the sheet to use
export2XLS.ExportToExcel(dv, (String)e.Argument, "newSheet1");
}
//Do a export to a new Excel document,
//with the use of a custom template
private void btnUseTemplate_Click(object sender, EventArgs e)
{
//show a file save dialog and ensure the user selects
//correct file to allow the export
saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (!saveFileDialog1.FileName.Equals(String.Empty))
{
FileInfo f = new FileInfo(saveFileDialog1.FileName);
if (f.Extension.Equals(".xls"))
{
StartExportUseTemplate(saveFileDialog1.FileName);
}
else
{
MessageBox.Show("Invalid file type");
}
}
else
{
MessageBox.Show("You did pick a location" +
" to save file to");
}
}
}
//Create a new excel document from a given template
//@param filepath : the file to export to
private void StartExportUseTemplate(String filepath)
{
btn2Excel.Enabled = false;
btnUseTemplate.Enabled = false;
//create a new background worker, to do the exporting
BackgroundWorker bg = new BackgroundWorker();
bg.DoWork += new DoWorkEventHandler(bg_DoWorkUseTemplate);
bg.RunWorkerCompleted +=
new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);
bg.RunWorkerAsync(filepath);
//create a new export2XLS object, providing
//DataView as a input parameter
export2XLS = new export2Excel();
export2XLS.prg +=
new export2Excel.ProgressHandler(export2XLS_prg);
}
//Do the adding to custom template to create new excel document,
//work using the background worker
private void bg_DoWorkUseTemplate(object sender, DoWorkEventArgs e)
{
//create some data to whack in the template
String[,] templateValues = { { "task1",
"CompletedBy1", "CompletedDate1" },
{ "task2", "CompletedBy2", "CompletedDate2" }
};
//The template path to use
Directory.SetCurrentDirectory(Application.StartupPath +
@"..\..\..\");
String templatePath = Directory.GetCurrentDirectory() +
@"\TaskList.xlt";
//Pass the template path and the test values and fill
//the template
export2XLS.UseTemplate((String)e.Argument, templatePath,
templateValues);
}
//Update the progress bar with the a value
private void export2XLS_prg(object sender, ProgressEventArgs e)
{
//need to call InvokeRequired to check
//if thread need marshalling, to get the thread onto
//the same thread as the thread who owns the controls
if (this.InvokeRequired)
{
this.Invoke(new EventHandler(delegate
{
progressBar1.Value = e.ProgressValue;
}));
}
else
{
progressBar1.Value = e.ProgressValue;
}
}
//show a message to the user when the background worker has finished
//and re-enable the export buttons
private void bg_RunWorkerCompleted(object sender,
RunWorkerCompletedEventArgs e)
{
btn2Excel.Enabled = true;
btnUseTemplate.Enabled = true;
MessageBox.Show("Finished");
}
}
}
export2Excel class does the excel automation
This is where all the real office automation stuff occurs.
Collapse
using System;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Runtime.InteropServices; // For COMException
using System.Reflection; // For Missing.Value and BindingFlags
using System.Diagnostics; // to ensure EXCEL process is really killed
namespace GridviewToExcel
{
#region export2Excel CLASS
/// This class processes the DataView that it is provided and
/// Exports this DataView to an Excel document.
public class export2Excel
{
#region InstanceFields
//Instance Fields
public delegate void ProgressHandler(object sender,
ProgressEventArgs e);
public event ProgressHandler prg;
private DataView dv;
private Style styleRows;
private Style styleColumnHeadings;
private Microsoft.Office.Interop.Excel.Application EXL;
private Workbook workbook;
private Sheets sheets;
private Worksheet worksheet;
private string[,] myTemplateValues;
private int position;
#endregion
#region Constructor
//Constructs a new export2Excel object. The user must
//call the createExcelDocument method once a valid export2Excel
//object has been instantiated
public export2Excel()
{
}
#endregion
#region EXCEL : ExportToExcel
//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(DataView dv,string path, string sheetName)
{
try
{
//Assign Instance Fields
this.dv = dv;
#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
#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
#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
#endregion
// 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();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//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);
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
#endregion
#region EXCEL : UseTemplate
//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();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//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");
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
#endregion
#region STEP 1 : Create Column & Row Workbook Cell Rendering Styles
//Creates 2 Custom styles for the workbook These styles are
// styleColumnHeadings
// styleRows
//These 2 styles are used when filling
//the individual Excel cells with the
//DataView values. If the current cell relates
//to a DataView column heading
//then the style styleColumnHeadings will be used
//to render the current cell.
//If the current cell relates to a DataView row
//then the style styleRows will
//be used to render the current cell.
private void SetUpStyles()
{
// Style styleColumnHeadings
try
{
styleColumnHeadings = workbook.Styles["styleColumnHeadings"];
}
// Style doesn't exist yet.
catch
{
styleColumnHeadings =
workbook.Styles.Add("styleColumnHeadings", Type.Missing);
styleColumnHeadings.Font.Name = "Arial";
styleColumnHeadings.Font.Size = 14;
styleColumnHeadings.Font.Color =
(255 << 16) | (255 << 8) | 255;
styleColumnHeadings.Interior.Color =
(0 << 16) | (0 << 8) | 0;
styleColumnHeadings.Interior.Pattern =
Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
}
// Style styleRows
try
{
styleRows = workbook.Styles["styleRows"];
}
// Style doesn't exist yet.
catch
{
styleRows = workbook.Styles.Add("styleRows", Type.Missing);
styleRows.Font.Name = "Arial";
styleRows.Font.Size = 10;
styleRows.Font.Color = (0 << 16) | (0 << 8) | 0;
styleRows.Interior.Color =
(192 << 16) | (192 << 8) | 192;
styleRows.Interior.Pattern =
Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
}
}
#endregion
#region STEP 2 : Fill Worksheet With DataView
//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;
// Loop thought the columns
for (int i = 0; i <>
{
fillExcelCell(worksheet, row, col++,
dv.Table.Columns[i].ToString(),
styleColumnHeadings.Name);
}
//Add DataView Rows To Worksheet
row = 2;
col = 1;
for (int i = 0; i <>
{
for (int j = 0; j <>
{
fillExcelCell(worksheet, row, col++, dv[i][j].ToString(),
styleRows.Name);
}
col = 1;
row++;
position = (100 / dv.Table.Rows.Count) * row + 2;
ProgressEventArgs pe = new ProgressEventArgs(position);
OnProgressChange(pe);
}
}
#endregion
#region STEP 3 : Fill Individual Cell and Render Using Predefined Style
//Formats the current cell based on the Style setting parameter name
//provided here
//@param worksheet : The worksheet
//@param row : Current row
//@param col : Current Column
//@param Value : The value for the cell
//@param StyleName : The style name to use
private void fillExcelCell(Worksheet worksheet, int row, int col,
Object Value, string StyleName)
{
Range rng = (Range)worksheet.Cells[row, col];
rng.Select();
rng.Value2 = Value.ToString();
rng.Style = StyleName;
rng.Columns.EntireColumn.AutoFit();
rng.Borders.Weight = XlBorderWeight.xlThin;
rng.Borders.LineStyle = XlLineStyle.xlContinuous;
rng.Borders.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
}
#endregion
#region STEP 4 : Add Auto Shapes To Excel Worksheet
//Add some WordArt objecs to the Excel worksheet
private void AddAutoShapesToExcel()
{
//Method fields
float txtSize = 80;
float Left = 100.0F;
float Top = 100.0F;
//Have 2 objects
int[] numShapes = new int[2];
Microsoft.Office.Interop.Excel.Shape[] myShapes =
new Microsoft.Office.Interop.Excel.Shape[numShapes.Length];
try
{
//loop through the object count
for (int i = 0; i < numShapes.Length; i++)
{
//Add the object to Excel
myShapes[i] =
worksheet.Shapes.AddTextEffect(
MsoPresetTextEffect.msoTextEffect1, "DRAFT",
"Arial Black", txtSize, MsoTriState.msoFalse,
MsoTriState.msoFalse, (Left * (i * 3)), Top);
//Manipulate the object settings
myShapes[i].Rotation = 45F;
myShapes[i].Fill.Visible =
Microsoft.Office.Core.MsoTriState.msoFalse;
myShapes[i].Fill.Transparency = 0F;
myShapes[i].Line.Weight = 1.75F;
myShapes[i].Line.DashStyle =
MsoLineDashStyle.msoLineSolid;
myShapes[i].Line.Transparency = 0F;
myShapes[i].Line.Visible =
Microsoft.Office.Core.MsoTriState.msoTrue;
myShapes[i].Line.ForeColor.RGB =
(0 << 16) | (0 << 8) | 0;
myShapes[i].Line.BackColor.RGB =
(255 << 16) | (255 << 8) | 255;
}
}
catch (Exception ex)
{
}
}
#endregion
#region STEP 5 : Select All Used Cells
//Selects all used cells for the Excel worksheet
private void SelectAllUsedCells()
{
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
}
#endregion
#region STEP 6 : Fill Template With Test Values
//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++;
}
}
#endregion
#region Kill EXCEL
//As a safety check go through all processes and make
//doubly sure excel is shutdown. Working with COM
//have sometimes noticed that the EXL.Quit() call
//doesn't always do the job
private void killExcel()
{
try
{
Process[] ps = Process.GetProcesses();
foreach (Process p in ps)
{
if (p.ProcessName.ToLower().Equals("excel"))
{
p.Kill();
}
}
}
catch (Exception ex)
{
MessageBox.Show("ERROR " + ex.Message);
}
}
#endregion
#region Events
/// Raises the OnProgressChange event for the parent form.
public virtual void OnProgressChange(ProgressEventArgs e)
{
if (prg != null)
{
// Invokes the delegates.
prg(this, e);
}
}
#endregion
}
#endregion
#region ProgressEventArgs CLASS
/// Provides the ProgressEventArgs
public class ProgressEventArgs : EventArgs
{
#region Instance Fields
//Instance fields
private int prgValue = 0;
#endregion
#region Public Constructor
/// Constructs a new ProgressEventArgs object
/// using the parameters provided
/// @param prgValue : new progress value
public ProgressEventArgs(int prgValue)
{
this.prgValue = prgValue;
}
#endregion
#region Public Methods/Properties
/// Returns the progress value
public int ProgressValue
{
get { return prgValue; }
}
#endregion
}
#endregion
}
Posted at |
☆ |
Labels: Export