I have a search page on my website that a user will need to export to excel. I have the following code that works but the only problem is that the entire string takes up only one cell instead of each column taking up it's own column cell in excel. So when
I select cell A1 in excel my entire row displays and B1, etc.. are all blank.
OracleCommand oCmd = new OracleCommand(tempSQL, conn);
OracleDataReader oRead = oCmd.ExecuteReader();
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=search.xls");
this.EnableViewState = false;
StringBuilder sb = new StringBuilder();
while (oRead.Read())
{
for (int col = 0; col < oRead.FieldCount - 1; col++)
{
if (!oRead.IsDBNull(col))
sb.Append(oRead.GetValue(col).ToString());
sb.Append(",");
}
Response.Write(sb.ToString() + "\n");
Response.Flush();
}
oRead.Dispose();
Response.End();
Is there a better way to do it? My problem is that I cant save the file on the server.
Using CarlosAg Excel Xml Writer, you can create Excel spreadsheets the way you want to (including formatting, calculations, etc.) and write the response stream:
<div class="code"><%@ Page Language="C#" ContentType="text/xml" %>
<%@ Import Namespace="CarlosAg.ExcelXmlWriter"
%> <%
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("Sample");
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell("Hello World from ASP.NET"));
book.Save(Response.OutputStream);
%> </div>
Darrell Norton, MVP
Darrell Norton's Blog Please click "Mark as Answer" if this helped you.
Thank you so much for your reply. I am unable to install any 3rd party controls/code on our webserver. I'm only allowed to use the basics. Is there another way?
Hi Jerry, if you will be able to put some dll into your BIN directory on the site. I can suggest you
Excel Jetcell .NET component. It has a
free version that can create excel files XLS or XLSX. It allows to export data from datatable or dataset to excel but can't add a formatting to cells of excel spreadsheet.
Jerry8989
Member
57 Points
99 Posts
Create excel on the fly within website
Jan 21, 2011 07:24 PM|LINK
I have a search page on my website that a user will need to export to excel. I have the following code that works but the only problem is that the entire string takes up only one cell instead of each column taking up it's own column cell in excel. So when I select cell A1 in excel my entire row displays and B1, etc.. are all blank.
OracleCommand oCmd = new OracleCommand(tempSQL, conn); OracleDataReader oRead = oCmd.ExecuteReader(); Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=search.xls"); this.EnableViewState = false; StringBuilder sb = new StringBuilder(); while (oRead.Read()) { for (int col = 0; col < oRead.FieldCount - 1; col++) { if (!oRead.IsDBNull(col)) sb.Append(oRead.GetValue(col).ToString()); sb.Append(","); } Response.Write(sb.ToString() + "\n"); Response.Flush(); } oRead.Dispose(); Response.End();Is there a better way to do it? My problem is that I cant save the file on the server.
Thanks
Oracle 10g excel 2003
DarrellNorto...
All-Star
86555 Points
9624 Posts
Moderator
MVP
Re: Create excel on the fly within website
Jan 21, 2011 07:50 PM|LINK
Using CarlosAg Excel Xml Writer, you can create Excel spreadsheets the way you want to (including formatting, calculations, etc.) and write the response stream:
<div class="code"><%@ Page Language="C#" ContentType="text/xml" %>
<%@ Import Namespace="CarlosAg.ExcelXmlWriter" %>
<%
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("Sample");
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell("Hello World from ASP.NET"));
book.Save(Response.OutputStream);
%> </div>
Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.
Jerry8989
Member
57 Points
99 Posts
Re: Create excel on the fly within website
Jan 21, 2011 07:59 PM|LINK
Darrell,
Thank you so much for your reply. I am unable to install any 3rd party controls/code on our webserver. I'm only allowed to use the basics. Is there another way?
Thank you
Jerry8989
Member
57 Points
99 Posts
Re: Create excel on the fly within website
Jan 24, 2011 12:46 PM|LINK
Does anyone else have any idea on how to do this without 3rd party controls?
Thank You
excel 2003
DarrellNorto...
All-Star
86555 Points
9624 Posts
Moderator
MVP
Re: Create excel on the fly within website
Apr 18, 2011 02:44 PM|LINK
You don't have to "install" it. Just copy it to the BIN directory. For all the admins will know, it is just another DLL.
Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.
Larry Neblet...
Member
24 Points
7 Posts
Re: Create excel on the fly within website
May 04, 2011 08:16 AM|LINK
Hi Jerry, if you will be able to put some dll into your BIN directory on the site. I can suggest you Excel Jetcell .NET component. It has a free version that can create excel files XLS or XLSX. It allows to export data from datatable or dataset to excel but can't add a formatting to cells of excel spreadsheet.