how to programmatically display data into an excel worksheet

03-02-2005, 22:17 cEciLlE

I have an excel application project type in my VS.net now i need to get the
data from my ms sql server then display it programmatically in my excel
application without using design time controls. How can i do that?

as of now, i have created the connection to the database and do a select
statement and have the result save into a datatable. how can i transfer the
data into the excel ranges/cells?

thanks

Re: how to programmatically display data into an excel worksheet

03-03-2005, 20:37 Apurva Sinha \(MS\)

Hi Cecille,
You wrote:
> as of now, i have created the connection to the database and do a select
> statement and have the result save into a datatable. how can i transfer
> the
> data into the excel ranges/cells?

If you are using VSTO 2005 Beta1, you can databind your ADO.NET DataTable to
a VSTO ListObject host control, much like you would to a DataGridView
winform control in a winform project. There is a demo at following link:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032255887&CountryCode=US

Following blog maybe helpful too: http://weblogs.asp.net/vsto2

If you are using VSTO 2003, the best thing maybe to store the data from the
ADO.NET DataTable in a 2D object array and then set a range of cells to the
2D array. Following code from the Sheet1 code-behind class of a C# VSTO 2005
project , but it doesnt use any VSTO controls. So, the logic is to read data
from a DataTable, create a 2D array and populate an Excel range.

// ADO.NET data objects created from generated NorthwindDataSet
type.
private NorthwindDataSet ds;
private NorthwindDataSet.ProductsDataTable dt;
private NorthwindDataSetTableAdapters.ProductsTableAdapter adapter;

/*********************************************************
* We need to get the data from the database into an
* ADO.NET DataSet. Then we put the data in a 2D array
* which is used to populate the ListObject data range.
* This is in no way binding the ListObject to ADO.NET
* DataSet.
*********************************************************/
private void PopulateListObject()
{
// Read data from the database.
ds = new NorthwindDataSet();
dt = new NorthwindDataSet.ProductsDataTable();
adapter = new
NorthwindDataSetTableAdapters.ProductsTableAdapter();
adapter.Fill(dt);

// Resize the ListObject to accomodate the data.
int numOfColumns = dt.Columns.Count;
int numOfRows = dt.Rows.Count;

Excel.ListObject lo = ListObjects[1]; // ListObject on Sheet1.
// Resize the Excel ListObject. One extra row for header.
Excel.Range rng = lo.Range.get_Resize(
numOfRows + 1, numOfColumns);
lo.Resize(rng);

// Get the DataTable headers and set ListObject header
// range values to this array of strings.
string[] headers = new string[numOfColumns];
for (int i = 0; i < numOfColumns; i++)
headers[i] = dt.Columns[i].ColumnName;

lo.HeaderRowRange.set_Value(
Excel.XlRangeValueDataType.xlRangeValueDefault, headers);

// Read data from the data table as string
// into a 2D Array and set the ListObject data.
object[,] datArray = new object[numOfRows, numOfColumns];
for (int i = 0; i < numOfRows; i++)
for (int j = 0; j < numOfColumns; j++)
datArray[i, j] = dt.Rows[i].ItemArray[j];

lo.DataBodyRange.set_Value(
Excel.XlRangeValueDataType.xlRangeValueDefault, datArray);

}

Hope this helps...
Apurva Sinha
VSTO Customer Solutions