Excel Report

  • Thread starter Thread starter KC
  • Start date Start date
K

KC

Hi Gurus,
We have a requirement for multiple huge report (over 50 page each) that
needs to be outputted on excel. Data item for the report comes from two
different sources; one from a UI where users can define their items and
change values (name value pair kind of scenario) and some data from
different tables on a database. Users need to flexibility of
entering/changing UI data.
We are brain storming with ideas of how to do it in a right way. We are
throwing idea of writing it on ASP.NET (scaling is not an issue here. Not
too many user will run this report). We are also thinking of saving the user
entered data as XML, getting DB data as XML (SQLXML), writing a report
engine (a dll) and having VBA code to calculate and output the data. We are
throwing idea of creating name ranges on excel template and saving meta data
on database and mapping each sheet and name range into data point. I know,
we are confused of what may be the best way to go about it.
Based on your experience, what may be the best way to do it. Please shed
some light.

Thanks,
KC
 
Lots of options here.
If you are READING data from the db with ADO.NET, you should be aware that
you can also WRITE data to MS Excel via ADO.NET.

This means you may be able to grab a dataset from the db, and then use a
different DataAdapter to write that dataset to MS Excel.

references
http://support.microsoft.com/?kbid=316934
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetsdk_43.asp

Basic example follows.

After creating the XLS file via ADO.NET , you could tweak it or format it
using Excel automation - adding highlights or formatting to the cells and so
on. Lots of examples of this in the .NET SDK samples.


- - -
Dino
Microsoft Developer Division

====================================================

public class TestAdoNetExcel {

System.Data.OleDb.OleDbConnection conn;
System.Random r= new System.Random();

private void CreateTable() {
System.Console.WriteLine("Create...");

string strSql = "CREATE TABLE SampleTable ( Ix NUMBER, CustName
char(255), Stamp datetime )";

System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(strSql, conn);
try {
conn.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e2){
if (!e2.Message.Trim().EndsWith("already exists."))
System.Console.WriteLine("Error while creating. " + e2);
else
System.Console.WriteLine("Table already exists...");
}
finally {
conn.Close();
}

}

private void Insert() {
System.Console.WriteLine("Insert...");

string strSql = "insert into [SampleTable] ([ix],[CustName],[Stamp])
values(@p1,@p2,@p3)";

System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(strSql, conn);

cmd.Parameters.Add("@p1", System.Data.OleDb.OleDbType.Numeric).Value =
r.Next(42);
cmd.Parameters.Add("@p2", System.Data.OleDb.OleDbType.VarChar).Value =
"Some text";
cmd.Parameters.Add("@p3", System.Data.OleDb.OleDbType.Date).Value =
System.DateTime.Now;

try {
conn.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e2){
System.Console.WriteLine("Error while inserting. " + e2);
}
finally {
conn.Close();
}
}


public void Run() {

const string Filename= "adonet-excel.xls";
//const string Filename= "Book1.xls";

const string strConnect =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Filename + ";" +
"Extended Properties=\"Excel 8.0;HDR=yes;\""; //
FIRSTROWHASNAMES=1;READONLY=false\"

System.Console.WriteLine("ADO.NET -> XLS...");

//if (System.IO.File.Exists(Filename))
//System.IO.File.Delete(Filename);

try {
conn = new System.Data.OleDb.OleDbConnection(strConnect);
CreateTable();
int N= r.Next(5)+2;
for(int i=0; i < N; i++) Insert();
}
catch (System.Exception ex) {
System.Console.WriteLine("Exception: " + ex.Message+ "\n " +
ex.StackTrace);
}

System.Console.WriteLine("launching Excel...");
System.Diagnostics.Process.Start(Filename);
}


public static void Main() {
TestAdoNetExcel test= new TestAdoNetExcel();
test.Run();
}

}
 
You can also automate Excel to add a QueryTable, which would directly insert
data from a db query into the MS Excel sheet. No ADO.NET programming;
simple.

See this KB for an example app:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306023

----
Dino



Dino Chiesa said:
Lots of options here.
If you are READING data from the db with ADO.NET, you should be aware that
you can also WRITE data to MS Excel via ADO.NET.

This means you may be able to grab a dataset from the db, and then use a
different DataAdapter to write that dataset to MS Excel.

references
http://support.microsoft.com/?kbid=316934
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetsdk_43.asp

Basic example follows.

After creating the XLS file via ADO.NET , you could tweak it or format it
using Excel automation - adding highlights or formatting to the cells and so
on. Lots of examples of this in the .NET SDK samples.


- - -
Dino
Microsoft Developer Division

====================================================

public class TestAdoNetExcel {

System.Data.OleDb.OleDbConnection conn;
System.Random r= new System.Random();

private void CreateTable() {
System.Console.WriteLine("Create...");

string strSql = "CREATE TABLE SampleTable ( Ix NUMBER, CustName
char(255), Stamp datetime )";

System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(strSql, conn);
try {
conn.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e2){
if (!e2.Message.Trim().EndsWith("already exists."))
System.Console.WriteLine("Error while creating. " + e2);
else
System.Console.WriteLine("Table already exists...");
}
finally {
conn.Close();
}

}

private void Insert() {
System.Console.WriteLine("Insert...");

string strSql = "insert into [SampleTable] ([ix],[CustName],[Stamp])
values(@p1,@p2,@p3)";

System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(strSql, conn);

cmd.Parameters.Add("@p1", System.Data.OleDb.OleDbType.Numeric).Value =
r.Next(42);
cmd.Parameters.Add("@p2", System.Data.OleDb.OleDbType.VarChar).Value =
"Some text";
cmd.Parameters.Add("@p3", System.Data.OleDb.OleDbType.Date).Value =
System.DateTime.Now;

try {
conn.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e2){
System.Console.WriteLine("Error while inserting. " + e2);
}
finally {
conn.Close();
}
}


public void Run() {

const string Filename= "adonet-excel.xls";
//const string Filename= "Book1.xls";

const string strConnect =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Filename + ";" +
"Extended Properties=\"Excel 8.0;HDR=yes;\""; //
FIRSTROWHASNAMES=1;READONLY=false\"

System.Console.WriteLine("ADO.NET -> XLS...");

//if (System.IO.File.Exists(Filename))
//System.IO.File.Delete(Filename);

try {
conn = new System.Data.OleDb.OleDbConnection(strConnect);
CreateTable();
int N= r.Next(5)+2;
for(int i=0; i < N; i++) Insert();
}
catch (System.Exception ex) {
System.Console.WriteLine("Exception: " + ex.Message+ "\n " +
ex.StackTrace);
}

System.Console.WriteLine("launching Excel...");
System.Diagnostics.Process.Start(Filename);
}


public static void Main() {
TestAdoNetExcel test= new TestAdoNetExcel();
test.Run();
}

}


KC said:
Hi Gurus,
We have a requirement for multiple huge report (over 50 page each) that
needs to be outputted on excel. Data item for the report comes from two
different sources; one from a UI where users can define their items and
change values (name value pair kind of scenario) and some data from
different tables on a database. Users need to flexibility of
entering/changing UI data.
We are brain storming with ideas of how to do it in a right way. We are
throwing idea of writing it on ASP.NET (scaling is not an issue here. Not
too many user will run this report). We are also thinking of saving the user
entered data as XML, getting DB data as XML (SQLXML), writing a report
engine (a dll) and having VBA code to calculate and output the data. We are
throwing idea of creating name ranges on excel template and saving meta data
on database and mapping each sheet and name range into data point. I know,
we are confused of what may be the best way to go about it.
Based on your experience, what may be the best way to do it. Please shed
some light.

Thanks,
KC
Click to expand...
Click to expand...
 
If scaling is not an issue, you may definitely want to consider the Office
web components. You may be able to retrieve the data on the server as xml
and load it into the excel spreadsheet which will be hosted in the webpage.
This excel spreadsheet provides the 'full' functionality of microsoft excel.
There are some draw backs though which mostly revolve around licensing.
Also, this is a client-side solution.

You may also want to consider server side excel by using COM interop excel
objects. The catch here is that this solution does not scale well.

You may also want to consider a third party solution excel application. One
I can think of is excel writer from softartisans. The price is about a
grand.
 
Back
Top