Pull Data into Excel Spreadsheet Using .Net

There are kinds of ways to export data from database into the spreadsheet. Database vendor provides database tools to export data. These tools can export the data to xml, sql statements or csv files which can be imported into Excel. However, it is not the best method. While, the interoperability feature of .Net can pull the data directly into the spreadsheet.

A form which is used to enter connection information and query is included in Query2Excel design. This form has ExcelHelper class which has many functions, such as activation function, formatting functions. Also, it has AddItemToSpreadsheet method to allow to adding text to a cell.

Excel has lots of object libraries which are used to control Excel, while, .Net allows us to attach to Excel by building a COM callable wrapper around the Excel COM Object library. Once you add the Excel COM object library reference, you can take Excel as a set of objects in C#. And then using the following code, we can active an Excel spreadsheet.

1. Open a New Excel Spreadsheet

Open a new excel spreadsheet

_excel = new Excel.ApplicationClass();

Create a new COM object for excel

Excel.Workbook workbook = _excel.Workbooks.Add(Type.Missing);

Add a new workbook
_excel.Visible = true;

Make the excel application visible
Worksheet ws = (Worksheet)_excel.ActiveSheet;

Activate the active worksheet in the workbook
ws.Activate();

2. Set a value inside a cell

public void AddItemToSpreadsheet(int row, int column, Worksheet ws, string item)
{
((Range)ws.Cells[row, column]).Value2 = item; // set the cell value at a row and column
}

3. Set Excel Column Width in .Net

public void SetColumnWidth(Worksheet ws, int col, int width)
{
((Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;
}
// autofit to contents
public void AutoFitColumn(Worksheet ws, int col)
{
((Range)ws.Cells[1, col]).EntireColumn.AutoFit();
}

4. Query an ODBC Database and fill the Database with the Query Results

private void PerformQueryIntoDataSet()
{
// set the odbc select command to a query
// contained inside the users query text box
odbcSelectCommand1.CommandText = txtQuery.Text;
_ds = new DataSet();
try
{
// fill the dataset from the query
odbcDataAdapter1.Fill(_ds);
// set the maximum for the progress bar
progressBar1.Maximum = _ds.Tables[0].Rows.Count;
// send the dataset to excel
FillExcelSpreadsheet();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

5. Read Excel Columns into the DataSet

///

/// Fills the top row of the excel spreadsheet
///
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"))
{
// set the column width in excel
_excel.SetColumnWidth(ws, colcount, 25);
}
colcount++;
}