Excel in a .NET Application

It has been a really hard work week after too many hours of coding. Thankfully I can get a whole week rest after tomorrow.

Last week, after a meeting with my project manager and my users, I had been assigned to build an application which includes import/export data from/to an excel spreadsheet functionality.

VS .NET allows us to add Excel reference. Once Excel reference is added into an application, you can take control of every aspect of Excel by using a rich library of objects provided by Excel itself, e.g. add formatting, additional worksheets and so on.

Previously, I had written a few posts related with import and export data from/to excel file, and I will summarized all of them in this post by using C#.

Connection String
To read data from Excel file, you will need a connection string to connect to the Excel file, as following:

string sfile = "C:\Book1.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfile + ";" + "Extended Properties=Excel 8.0;";

sfile represents the excel file path that you wish to read from.

Reading Data from Excel into DataSet
Here is a sample of reading an Excel spreadsheet by using OleDb and DataSet.

System.Data.DataTable dbSchema = new System.Data.DataTable();
OleDbConnection conn;
OleDbDataAdapter da;
DataSet ds;

string sfile = "C:\Book1.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfile + ";" + "Extended Properties=Excel 8.0;";

conn = new OleDbConnection(strConn);
conn.Open();

// Get all sheetnames from an excel file into data table
dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dbSchema != null || dbSchema.Rows.Count > 0)
{
    // Loop through all worksheets
    for (int i = 0; i < dbSchema.Rows.Count; i++)
    {
        string sheetname = dbSchema.Rows[i]["TABLE_NAME"].ToString();

        da = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "]", strConn);
        da.TableMappings.Add("Table", "dt_Excel");

        ds = new DataSet();
        da.Fill(ds);

        if (ds.Tables != null || ds.Tables[0].Rows.Count > 0)
        {
            // your codes here…
        }
    }
}

conn.Close();

Create an instance of Excel and Open a New Excel Spreadsheet
Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;

// Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);

// Get the first worksheet
xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

// Activate current worksheet
xlsWorksheet.Activate();

Here, we have our first worksheet ready for insert data.

Write Table Headers and Data from DataSet into Excel
To add column headers into worksheet, we need to use Cells property of the worksheet to set a value into a specific row and column in the spreadsheet. The Cells property lets you treat both rows and columns as numbers start with index 1.

// Write Column Headers into first row of Excel Worksheet
xlsWorksheet.Cells[1, 1] = "Name";
xlsWorksheet.Cells[1, 2] = "NRIC";
xlsWorksheet.Cells[1, 3] = "Address";
xlsWorksheet.Cells[1, 4] = "MonthSalary";
xlsWorksheet.Cells[1, 5] = "YearSalary";

int row = 1;
// Write data to Excel worksheet from dataset
foreach (DataRow dr in dt.Rows)
{
row += 1;
xlsWorksheet.Cells[row, 1] = dr["Name"];
xlsWorksheet.Cells[row, 2] = dr["NRIC"];
xlsWorksheet.Cells[row, 3] = dr["Address"];
xlsWorksheet.Cells[row, 4] = dr["MonthSalary"];
xlsWorksheet.Cells[row, 5] = dr["YearSalary"];
}

Column and Row Format in Excel
Formatting spreadsheet is quite important in certain circumstances. Below I will show you a few formatting examples.

// Freeze the columm headers (first row)
xlsWorksheet.get_Range("A2", "E2").Select();
xlsApp.ActiveWindow.FreezePanes = true;

// Set the font name of the worksheet through the EntireColumn property of the cell
xlsWorksheet.get_Range("A1", "E1").Cells.EntireColumn.Font.Name = "Calibri";

// Bolding the entire row of header (first row) through the EntireRow property of the cell
xlsWorksheet.get_Range("A1", "E1").EntireRow.Font.Bold = true;

// Auto fit the entire worksheet
xlsWorksheet.get_Range("A1", "E1").EntireColumn.AutoFit();

// Set column of MonthSalary and YearSalary to 2 decimal positions
xlsWorksheet.get_Range("D2", "E2").Cells.EntireColumn.NumberFormat = "##0.00";
// or for date format -> …Cells.EntireColumn.NumberFormat = "dd-mmm-yy";

Clean Up Excel Objects
// Save the file and clean up all Excel object
string filename = "C:\test1.xls";
xlsApp.DisplayAlerts = false;
xlsWorkbook.Close(true, filename, null);
xlsApp.Quit();
xlsWorksheet = null;
xlsWorkbook = null;
xlsApp = null;

Hope this can help you. Cheers!


Other related posts:
1. Export Data to Excel (Not HTML Tables)
2. Import data from CSV file into data set
3. Import Data from MS Access to Excel File
4. Import Data from Excel File (.cont)
5. A Simple Solution for Export ASP .NET Data Grid to Excel
6. How to Import Data from Excel File