In this lesson we will export our micosoft access data rendered in datagridview to microsoft excel. MS Access is a database program that we can use to store data while MS Excel is a spreadsheet program.
This lesson is part of our multi-series Contacts Management System development course in C#. In the course we are developing a full system using C# windows forms and microsoft access as our database.
In this particular class as we've said, we will export data on button click to a ms excel file in a location we specify. If the file already exists we can choose to replace it or ignore. This choice we are making at runtime.
The exported file is beautifully formatted and you can open it with any Microsoft Office Excel program.
Why Export Data to MS Excel?
Here are the advantages of exporting your data to ms excel:
- Backup - You backup your data to a portable ms excel which you can upload to an online server or copy to another PC or hard drive.
- Printing - If you want to easily print your data and or easily control how the printed copy will look. For instance change colors of the rows, cells, even insert logos, then a full blown MS Office can allow that kind of control and customization. Thus you export data and then print form excel.
- Reports - If you want to produce reports for your data. And you want to control the look and feel of the reports. For example you want to use charts etc. Then you can export your data to excel and then produce and print your reports from there.
Let's start.
Step 1: Create our Class and Add Imports
Create a class called Utils and add imports and namespace:
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Reflection;
using ContactManager.Data.Model;
using OfficeExcel = Microsoft.Office.Interop.Excel;
namespace ContactManager.Common
{
class Utils
{Step 2: Create a method to return a dataset from a generic list
The method we create is static. and will convert a DataSet object from a genric List. That List will be passed as a parameter:
/**
* Let's create a method to turn a list of contacts into a dataset
*/
public static DataSet GetDataSet(List<Contact> contacts)
{First instantiate a DataSet and DataTable object:
DataSet ds = new DataSet();
DataTable dt = new DataTable();Then add the columns to our DataTable:
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Phone1");
dt.Columns.Add("Phone2");
dt.Columns.Add("Email");
dt.Columns.Add("Remarks");Then loop through our List, adding rows to our DataTable:
foreach (Contact c in contacts)
{
dt.Rows.Add(c.Id.ToString(), c.Name, c.Phone1, c.Phone2, c.Email, c.Remarks);
}Then set the DataTable name and add it to our DataSet:
dt.TableName = "Contacts";
ds.Tables.Add(dt);
Finally return the DataSet:
return ds;
}Step 3: Create Method to Export to MS Excel
The method is static and will return a string. That string will either be a success message or an error message. The method is taking a DataSet object and the path to export our excel file to:
public static String exportToExcel(DataSet ds, string strPath)
{We'll do it in a try-catch block:
try
{
Inside the try-catch block start by making the following definitions:
const int inHeaderLength = 3;
Missing Default = Missing.Value;
strPath += @"/Contacts" + ".xlsx";Then we instantiate our OfficeExcel.Application:
//Prepare a representation of Microsoft Excel Application using
// OfficeExcel.Application interface
OfficeExcel.Application excelApp = new OfficeExcel.Application();Then we add a Workbook to our OfficeExcel:
//Add a Workbook to our Excel using the Add() method of the excelApp.Workbooks
// collection
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);We will then loop through our DataSet, getting the dataTables there and further looping through the dataTables to get the rows and columns there.
FUL CODE
Here is the full code for the class
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Reflection;
using ContactManager.Data.Model;
using OfficeExcel = Microsoft.Office.Interop.Excel;
namespace ContactManager.Common
{
class Utils
{
/**
* Let's create a method to turn a list of contacts into a dataset
*/
public static DataSet GetDataSet(List<Contact> contacts)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Phone1");
dt.Columns.Add("Phone2");
dt.Columns.Add("Email");
dt.Columns.Add("Remarks");
foreach (Contact c in contacts)
{
dt.Rows.Add(c.Id.ToString(), c.Name, c.Phone1, c.Phone2, c.Email, c.Remarks);
}
dt.TableName = "Contacts";
ds.Tables.Add(dt);
return ds;
}
/**
* Let's create a method to export a dataset to an excel file at a specified path
*/
public static String exportToExcel(DataSet ds, string strPath)
{
try
{
const int inHeaderLength = 3;
Missing Default = Missing.Value;
strPath += @"/Contacts" + ".xlsx";
//Prepare a representation of Microsoft Excel Application using
// OfficeExcel.Application interface
OfficeExcel.Application excelApp = new OfficeExcel.Application();
//Add a Workbook to our Excel using the Add() method of the excelApp.Workbooks
// collection
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
//Let's Loop through our datatables in our dataset
foreach (DataTable dt in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default,
excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
//We will give our worksheet a name based on our datatable
excelWorkSheet.Name = dt.TableName;
//We will loop through all our columns in our current datatable.
for (int i = 0; i < dt.Columns.Count; i++)
//Then Write Column Name
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] =
dt.Columns[i].ColumnName.ToUpper();
//Then we will loop through our rows in our current datatable
for (int m = 0; m < dt.Rows.Count; m++)
{
//Then write our row cells for each row
for (int n = 0; n < dt.Columns.Count; n++)
{
var inColumn = n + 1;
var inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] =
dt.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
excelWorkSheet.Range["A" + inRow, "G" + inRow].Interior.Color =
ColorTranslator.FromHtml("#FCE4D6");
}
}
//Excel Header
OfficeExcel.Range cellRang = excelWorkSheet.Range["A1", "G3"];
cellRang.Merge(false);
cellRang.Interior.Color = Color.White;
cellRang.Font.Color = Color.Gray;
cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
excelWorkSheet.Cells[1, 1] = "Your Contacts";
//Style table column names
cellRang = excelWorkSheet.Range["A4", "G4"];
cellRang.Font.Bold = true;
cellRang.Font.Color = ColorTranslator.ToOle(Color.White);
cellRang.Interior.Color = ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.Range["F4"].EntireColumn.HorizontalAlignment =
OfficeExcel.XlHAlign.xlHAlignRight;
excelWorkSheet.Range["F5"].EntireColumn.NumberFormat = "0.00";
excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
OfficeExcel.Worksheet lastWorkSheet =
(OfficeExcel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
var worksheet = excelWorkBook.Sheets[1] as OfficeExcel._Worksheet;
if (worksheet != null)
worksheet.Activate();
else
{
return "NULL WORKSHEET";
}
excelWorkBook.SaveAs(strPath, Default, Default, Default, false, Default,
OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default,
Default);
excelWorkBook.Close();
excelApp.Quit();
return "SUCCESS";
}
catch (Exception ex)
{
return ex.Message;
}
}
}
}
//end
Now move over to the next class.