Query Tool to Excel using C# and .NET

Page 3 of 3
  1. Introduction
  2. .NET and Excel
  3. Reading & Writing Data into Excel

Reading & Writing Data into Excel

Once we've filled the DataSet, we are ready to populate the data from the DataSet into Excel. First we cycle through each DataColumn in the DataSet to get the column names for the top row of the spreadsheet. Upon getting the column name, we then bold the text to distinguish it from the data in the column. We can then use the other format methods in ExcelHelper to fit the column header to the column and set the date columns to the appropriate format.

Listing 7 - Reading Columns from Excel into the DataSet

/// <summary>
/// Fills the top row of the excel spreadsheet
/// </summary>
void FillColumnHeaders(Worksheet ws)
{
    int colcount = 1;
    // go through each column and stick the column name
    // in excel
    foreach (DataColumn dc in _ds.Tables[0].Columns)
    {
        string nextItem = dc.ColumnName;
        _excel.AddItemToSpreadsheet(1, colcount, ws, nextItem);
        // fit the column in excel to the header name
        _excel.AutoFitColumn(ws, colcount);
        // check for date time data and format
        if (dc.DataType == System.Type.GetType("System.DateTime"))
        {
            // format for date time in excel
            _excel.FormatColumn(ws, colcount, "mmm-d-yyyy hh:mm:ss.000");
            // set the column width in excel
            _excel.SetColumnWidth(ws, colcount, 25);
        }
        colcount++;
    }
    // bold the header row
    _excel.BoldRow(1, ws);
}

Once we've populated the columns, we are now ready to populate Excel with the data from the DataSet. Listing 8 takes data from each DataRow in the DataSet and places it the excel spreadsheet again using the ExcelHelper class.

Listing 8 - Reading the Data into Excel

/// <summary>
/// Fill the Data from the dataset
/// </summary>
/// <param name="ws"></param>
void FillDataRows(Worksheet ws)
{
    int rowcount = 2;
    int colcount = 1;
    // go through each row of the data set and read the data
    foreach (DataRow dr in _ds.Tables[0].Rows)
    {
        colcount = 1;
        object[] items = dr.ItemArray;
        // go through each column in the row and read the data
        // inside the row
        foreach (object o in items)
        {
            string nextItem = "";
            // if the data is date time, format the data
            if (o is DateTime)
            {
                nextItem = ((DateTime)o).ToString("MMM-d-yyyy hh:mm:ss.fff");
            }
            else
            {
                nextItem = o.ToString();
            }
            // add the next data item to the spreadsheet
            _excel.AddItemToSpreadsheet(rowcount, colcount, ws, nextItem);
            colcount++;
        }
        // populate the rows filled array, which
        // are the parameters for the invoke call
        // to the GUI (progress bar and count label)
        _rowsFilledArray[0] = rowcount;
        // set the progress bar and row count label every 10 rows
        if (rowcount % 10 == 0)
        {
            this.BeginInvoke(SetRowsFilled, _rowsFilledArray);
        }
        // increment the row count
        rowcount++;
    }
}

Conclusion

The interoperability feature in .NET gives you nice control into your Office applications. My only complaints are that sometimes its a little complicated meandering your way through the complex COM object hierarchies in the Office applications. Also, the population seems a little bit slow going through the COM automation interface. It might be faster to populate a ListView or DataGrid in .NET for example. The advantage of populating an excel spreadsheet, however, is you automatically get all of the extremely powerful features contained inside the Excel application once the data is populated (graphing, assigning formulas, presentation, etc.). Anyway, if you are going to excel in your programming, you might want to extract your data using C# and .NET.

You might also like...

Comments

About the author

Mike Gold United States

Mike Gold is President of Microgold Software Inc. and Creator of WithClass 2000 a UML Design Tool for C#. In the last few years Mike has consulted for companies such as Merrill Lynch and Chase M...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”