Generate Excel Spreadsheet On The Fly

I would thing that you would have to go down to a pretty granular level: some ideas below.

Dim oApp as new Excel.Application
Dim oWS as Excel.Worksheet = oApp.Worksheets(1)

Then inside your recordset loop you could use s
while not rs.eof
          i = i + 1
         oWorksheet.Cells(i,"A") = rs.fields(0).value
         oWorksheet.Cells(i,"B") = rs.fields(1).value
         oWorksheet.Cells(i,"C") = rs.fields(2).value
         oWorksheet.Cells(i,"D") = rs.fields(3).value
         rs.movenext
end while

Does this help?

Create Dynamic ASP.NET Excel Workbooks In C#

By Peter A. Bromberg, Ph.D.

http://www.eggheadcafe.com/articles/20021012.asp

private void CreateExcelWorkbook(string spName, SqlParameter[] parms)
{
string strCurrentDir = Server.MapPath(".") + "\\";
RemoveFiles(strCurrentDir); // utility method to clean up old files
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
 
try
{
GC.Collect();// clean up any other excel guys hangin' around...
oXL = new Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//get our Data
 
string strConnect = System.Configuration.ConfigurationSettings.AppSettings["connectString"];
SPGen sg = new SPGen(strConnect,spName,parms);
SqlDataReader myReader = sg.RunReader();
// Create Header and sheet...
int iRow =2;
for(int j=0;j<myReader.FieldCount;j++)
{
oSheet.Cells[1, j+1] = myReader.GetName(j).ToString();
}
// build the sheet contents
while (myReader.Read())
{
for(int k=0;k < myReader.FieldCount;k++)
{
oSheet.Cells[iRow,k+1]= myReader.GetValue(k).ToString();
}
iRow++;
}// end while
myReader.Close();
myReader=null;
//Format A1:Z1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "Z1").Font.Bold = true;
oSheet.get_Range("A1", "Z1").VerticalAlignment =Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:Z.
oRng = oSheet.get_Range("A1", "Z1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
string strFile ="report" + System.DateTime.Now.Ticks.ToString() +".xls";
oWB.SaveAs( strCurrentDir + strFile,Excel.XlFileFormat.xlWorkbookNormal,
     null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,null,null);
// Need all following code to clean up and extingush all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
oSheet=null;
oWB=null;
oXL = null;
GC.Collect(); // force final cleanup!
string strMachineName = Request.ServerVariables["SERVER_NAME"];
errLabel.Text="<A href=http://" + strMachineName +"/ExcelGen/" +strFile + ">Download Report</a>";
 
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
errLabel.Text= errorMessage ;
}

Select allOpen in new window

Another lovely solution at Code project i Have seen One click ASP.NET export button control for Excel/CSV file

http://www.codeproject.com/KB/custom-controls/Excel_CSV_export_button.aspx

There are various ways one can export data using ASP.NET. Usually, it is done by coding a separate page and adding various HTTP headers and responses.

Hey mildurai & PlatoConsultan,

After creating "excel.application" object, how can we write to the Response?



PlatoConsultant,

The code you gave me seems to generate the excel file with all data in the 1st column as well.


Thanks again!

P.S. I can us

When I use Exce.Application in my aspx page, I even got "System.UnauthorizedAccess: Access is denied " exceptopn.

cheny...
just cooking dinner for my kids, can help later tonight after sending them off to sleep - I'm in the same timezone as you.

Thanks mildurai :-)

I googled & fixed the security issue.

Hi mildurait & sybe,


You are the legend!


 I have tried an example on my own PC.Tomorrow if I succeed as well in the office, I will add points for you. (I only need 1 worksheet.)


Thanks heaps again! (also many thanks to PlatoConsultant)

Hey All,

For reason, the XLS file I generated has no background grid line. Do you know how can I add it?

I'd suggest that further research of xml approach will help you with this, else as sybe says, just format the table using css styles, including the style information the <head> section of of the html code.

Hey All again.

I think if I use "Response.AppendHeader("Content-type", "application/vnd.ms-excel")", it is for M$ Excel 2003. When I try to use Excel 2007 open it, I got the alert message:

"The file you are trying to open is in a different format than specified by the file extension..............", and file_type becomes "application", rather than "excel spreadsheet"..


What I am thinking is:

1,  Still using xls as extension name, rather than xlsx because 2007 can open 2003 document without conversion;

2, There are some solution on web to sovle it by changing the registry of client PCs. But I can't do it;

3, I also found something like changing the content-type to be: application/vnd.openxmlformats-officedocument.spreadsheetml. But it's still the same.


Any ideas? Many thanks!

Thanks all. I will oepn another thread

So far so good. I think I shall post another thread to continue with the Excel 2007 issue.


Thanks all again!