C#

csecharithjayasshc wrote:

First Question:
Can I use all the excel functions with .Net ?
Do you have any good samples ?

csecharithjayasshc wrote:

Second Question:
This is not a windows application, this is a web server. Can I generate excel report by using .Net web application?

//create random file so as not to conflict
string dataFile = "<path to your excel file>";
		
//make sure the temp file is not read only (copied from template) as this stops the OLE connection from updating
File.SetAttributes(dataFile, FileAttributes.Normal);

//inject data
System.Data.OleDb.OleDbConnection tempExcelConnection = new System.Data.OleDb.OleDbConnection();
tempExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
tempExcelConnection.ConnectionString += "Data Source=" + dataFile + ";";
tempExcelConnection.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes;"";";
System.Data.OleDb.OleDbCommand tempExcelCommand = new System.Data.OleDb.OleDbCommand();
tempExcelCommand.Connection = tempExcelConnection;
			
System.Data.SqlClient.SqlDataReader dr = null;

tempExcelCommand.Connection.Open();

// TODO: Get data for SqlDataReader

if (dr.HasRows)
{
	while(dr.Read())
	{
		//NB. Ths query expects a named range called "mynamedrange" containing 5 columns
		//the data will be inserted after the named range so should only include the column headings
		tempExcelCommand.CommandText = String.Format("INSERT INTO [mynamedrange] VALUES ({0}, {1}, {2}, {3}, {4})", dr[0], dr[1], dr[2], dr[3], dr[4]);
		tempExcelCommand.ExecuteNonQuery();
	}
}

dr.Close();
tempExcelCommand.Connection.Close();

ImranTech wrote:

all the records related to that primary key value

1111 11112222 111122223333 1111222233334444 11112222333344445555 111122223333444455556666

1111
2222
3333
4444
5555
6666