I wrote a post on my blog <not a shameless plug, I don't write many posts> with a basic algorithym to output an excel spreadsheet from code. I've done it in several different languages including vb.net. What I want to emphasize is that it is possible to
do with decent results without resorting to creating an instance of an excel app object or simply writing out table html.
Please mark as answered if I helped.
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
I'm sure there are a few corners cut here, but the basics are there. I also had a problem writing the xml straight to the context.response, so I wrote it to a file first and then transmited the file. I'm sure someone here can work that out.
Code sample
Please mark as answered if I helped.
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
No, I don't, but the code doesn't change much. take off semicolons, dim statements instead of c# variable declaration, array indexers are () instead of []. Otherwise, pretty much the same.
--JJ
Please mark as answered if I helped.
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
If I am not wrong the source code will require the user to click Open / Save / Cancel to continue either save or open the excel file. Is there any way to force the system automatically save the file? (without making any changes to the system setting). I
tried to use the HTML writer to render the DataGrid control to excel, it works fine but just has a funny header character in the first row.
I tried to use Excel.Application method to open the excel file and delete the row, but it requires Microsoft.Office.Interop class, :( is there any other method to force Save button click?
I tried running this program using a simple excel template of my own and using your same code. I have an asp page with a button so when u click it it generates the sample.xls file. Only problem is when i open it(or save and open it) i get a strict parse
error and the sample.xls file wont open. But, the sample.xls file that is saved in my project folder opens perfectly in excel and displays the data just like it should. Does anyone have any idea on why this is happening and how I can fix it?
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using Excel;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
It has been quite a while since I looked at this, but ...
when you get the strict parse error it gives you an error message, something like Details can be found in the file at.... and gives you a temporary file location. If you find that file, you should see some additional information in terms of the error.
--JJ
Please mark as answered if I helped.
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
I had this problem too, but I solved it by just deleting the following line:
Context.Response.Write (rptxml);
This line is just before the line which creates the streamWrite object that will save your file on server; the problem occurs when the server transmits the file to the client, since Excel will get the information from both: the physical file and the buffer.
This way Excel will get duplicated data, but somehow it will save into the client's machine a well-formed .xml file that you can later open from the client's file system with no problems at all.
One way to see how the data is being duplicated when writing rptxml to the buffer, is by writing the result to a .txt file instead of writing it to an .xls one, and then opening it from the client's file system. You will see that the same information is
written twice into the same file.
jose_jimenez
Contributor
2504 Points
484 Posts
Dynamically Generate An Excel spreadsheet in asp.net
Oct 24, 2006 01:47 AM|LINK
I wrote a post on my blog <not a shameless plug, I don't write many posts> with a basic algorithym to output an excel spreadsheet from code. I've done it in several different languages including vb.net. What I want to emphasize is that it is possible to do with decent results without resorting to creating an instance of an excel app object or simply writing out table html.
Take a look at http://randomnext.blogspot.com/2006/08/15-minutes-to-create-dynamic-excel.html
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
jose_jimenez
Contributor
2504 Points
484 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
Nov 10, 2006 08:48 PM|LINK
I had someone ask for a sample of the code so I'm attaching some here:
using System;
using System.Web;
using System.IO;
public class Handler : IHttpHandler {
public void ProcessRequest (HttpContext context) {
//set the content type
context.Response.Clear();
context.Response.Charset = "";
context.Response.AddHeader("content-disposition", "attachment;filename=Sample2.xls");
context.Response.ContentType = "application/vnd.ms-excel";
// read in the SampleRpt.xml file
string rptxml;
StreamReader sr = new StreamReader(context.Server.MapPath("App_Data/SampleRpt.xml"));
rptxml = sr.ReadToEnd();
sr.Close();
sr.Dispose();
// read in the Samplerow.xml file
string rowxml;
StreamReader sr2 = new StreamReader(context.Server.MapPath("App_Data/SampleRow.xml"));
rowxml = sr2.ReadToEnd();
sr2.Close();
sr2.Dispose();
// create the rows
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string thisRow;
thisRow = rowxml;
thisRow = thisRow.Replace("[[name]]", "J Jimenez");
thisRow = thisRow.Replace("[[date]]", "2006-01-01");
thisRow = thisRow.Replace("[[amount]]", "12.5");
sb.Append(thisRow);
thisRow = rowxml;
thisRow = thisRow.Replace("[[name]]", "J J");
thisRow = thisRow.Replace("[[date]]", "2006-02-01");
thisRow = thisRow.Replace("[[amount]]", "75.00");
sb.Append(thisRow);
thisRow = rowxml;
thisRow = thisRow.Replace("[[name]]", "Someone Else");
thisRow = thisRow.Replace("[[date]]", "2006-03-01");
thisRow = thisRow.Replace("[[amount]]", "35.28");
sb.Append(thisRow);
thisRow = rowxml;
thisRow = thisRow.Replace("[[name]]", "Oh Bother");
thisRow = thisRow.Replace("[[date]]", "2006-04-01");
thisRow = thisRow.Replace("[[amount]]", "58.69");
sb.Append(thisRow);
rptxml = rptxml.Replace("[[rowCount]]", "4");
rptxml = rptxml.Replace("[[rows]]", sb.ToString());
// write out the xml
// context.Response.Write(rptxml); //removed this line which caused multiples of the same file.
StreamWriter sw = new StreamWriter(context.Server.MapPath("./Sample.xls"));
sw.Write(rptxml);
sw.Close();
sw.Dispose();
context.Response.TransmitFile("Sample.xls");
}
public bool IsReusable {
get {
return false;
}
}
}
the xml referenced here is:
SampleRpt.xml
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>jjimenez</Author> <LastAuthor>jjimenez</LastAuthor> <Created>2006-11-10T20:34:11Z</Created> <Company>Member company of the AEGON Group</Company> <Version>10.6817</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <DownloadComponents/> <LocationOfComponents HRef="file:///\\"/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>10875</WindowHeight> <WindowWidth>13260</WindowWidth> <WindowTopX>480</WindowTopX> <WindowTopY>30</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s18" ss:Name="Currency"> <NumberFormat ss:Format="_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)"/> </Style> <Style ss:ID="s21"> <Font x:Family="Swiss" ss:Bold="1"/> </Style> <Style ss:ID="s22"> <NumberFormat ss:Format="Short Date"/> </Style> <Style ss:ID="s23" ss:Parent="s18"> <Font x:Family="Swiss" ss:Bold="1"/> </Style> </Styles> <Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="6" x:FullColumns="1" x:FullRows="1"> <Column ss:AutoFitWidth="0" ss:Width="137.25"/> <Column ss:Index="3" ss:StyleID="s18" ss:AutoFitWidth="0"/> <Row> <Cell ss:StyleID="s21"><Data ss:Type="String">name</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">date</Data></Cell> <Cell ss:StyleID="s23"><Data ss:Type="String">amount</Data></Cell> </Row> [[rows]] <Row> <Cell ss:Index="3" ss:Formula="=SUM(R[-[[rowCount]]]C:R[-1]C)"><Data ss:Type="Number">0.0</Data></Cell> </Row> </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Print> <ValidPrinterInfo/> <HorizontalResolution>600</HorizontalResolution> <VerticalResolution>600</VerticalResolution> </Print> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>7</ActiveRow> <ActiveCol>2</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> <Worksheet ss:Name="Sheet2"> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> <Worksheet ss:Name="Sheet3"> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook>SampleRow.xml
I'm sure there are a few corners cut here, but the basics are there. I also had a problem writing the xml straight to the context.response, so I wrote it to a file first and then transmited the file. I'm sure someone here can work that out.
Code sample
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
DBoldt
Member
7 Points
2 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
Jul 05, 2007 05:46 PM|LINK
Do you also have a VB.NET code example?
jose_jimenez
Contributor
2504 Points
484 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
Jul 05, 2007 06:53 PM|LINK
No, I don't, but the code doesn't change much. take off semicolons, dim statements instead of c# variable declaration, array indexers are () instead of []. Otherwise, pretty much the same.
--JJ
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
adman666
Member
468 Points
71 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
Aug 17, 2007 12:12 AM|LINK
I got this working nicely in VB.NET with a few simple alterations:
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=Sample2.xls")
Response.Charset = ""
EnableViewState = False
<!--Read in files and do replace here -->
Response.Write(rptxml)
Response.End()
Also a good tool for converting C# to VB is Instant VB from http://www.tangiblesoftwaresolutions.com/
Thanks.
qqer
Member
4 Points
12 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
Apr 30, 2008 01:25 AM|LINK
Hi,
If I am not wrong the source code will require the user to click Open / Save / Cancel to continue either save or open the excel file. Is there any way to force the system automatically save the file? (without making any changes to the system setting). I tried to use the HTML writer to render the DataGrid control to excel, it works fine but just has a funny header character in the first row.
I tried to use Excel.Application method to open the excel file and delete the row, but it requires Microsoft.Office.Interop class, :( is there any other method to force Save button click?
qqer
Member
4 Points
12 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
Apr 30, 2008 03:11 PM|LINK
hi,
i manage to solve the auto save problem now. below is the link to my post:
http://forums.asp.net/p/1252833/2330243.aspx#2330243
ajax_kid
Member
4 Points
7 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
May 11, 2008 02:57 AM|LINK
hi,
I tried running this program using a simple excel template of my own and using your same code. I have an asp page with a button so when u click it it generates the sample.xls file. Only problem is when i open it(or save and open it) i get a strict parse error and the sample.xls file wont open. But, the sample.xls file that is saved in my project folder opens perfectly in excel and displays the data just like it should. Does anyone have any idea on why this is happening and how I can fix it?
here is the SampleRpt.xml file:
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Abel</Author>
<LastAuthor>Abel</LastAuthor>
<Created>2008-05-10T00:19:22Z</Created>
<LastSaved>2008-05-10T00:21:24Z</LastSaved>
<Company> Barr-era</Company>
<Version>10.3501</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents href="file:///E:\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9210</WindowHeight>
<WindowWidth>11355</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#00CCFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s22">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="25" x:FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="106.5"/>
<Column ss:AutoFitWidth="0" ss:Width="179.25"/>
<Column ss:AutoFitWidth="0" ss:Width="79.5"/>
<Row>
<Cell ss:StyleID="s21">
<Data ss:Type="String">SID</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Student Name</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">User ID</Data>
</Cell>
</Row>
[[rows]]
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>200</HorizontalResolution>
<VerticalResolution>200</VerticalResolution>
<NumberofCopies>0</NumberofCopies>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
and SampleRow.xml:
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="Number">[[sid]]</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">[[name]]</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">[[userID]]</Data>
</Cell>
</Row>
finally the code:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using Excel;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Context.Response.Clear();
Context.Response.Charset = "";
Context.Response.AddHeader("content-disposition", "attachment;filename=Sample2.xls");
Context.Response.ContentType = "application/vnd.ms-excel";
// read in the SampleRpt.xml file
string rptxml;
StreamReader sr = new StreamReader(Context.Server.MapPath("App_Data/SampleRpt.xml"));
rptxml = sr.ReadToEnd();
sr.Close();
sr.Dispose();
// read in the Samplerow.xml file
string rowxml;
StreamReader sr2 = new StreamReader(Context.Server.MapPath("App_Data/SampleRow.xml"));
rowxml = sr2.ReadToEnd();
sr2.Close();
sr2.Dispose();
// create the rows
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string thisRow;
thisRow = rowxml;
thisRow = thisRow.Replace("[[sid]]", "1242254");
thisRow = thisRow.Replace("[[name]]", "Smith,John");
thisRow = thisRow.Replace("[[userID]]", "jsmith");
sb.Append(thisRow);
thisRow = rowxml;
thisRow = thisRow.Replace("[[sid]]", "5555555");
thisRow = thisRow.Replace("[[name]]", "Moreno,Alex");
thisRow = thisRow.Replace("[[userID]]", "amoreno");
sb.Append(thisRow);
thisRow = rowxml;
thisRow = thisRow.Replace("[[sid]]", "12525745");
thisRow = thisRow.Replace("[[name]]", "Vivino,Jimmy");
thisRow = thisRow.Replace("[[userID]]", "jimmyv");
sb.Append(thisRow);
thisRow = rowxml;
thisRow = thisRow.Replace("[[sid]]", "1542845");
thisRow = thisRow.Replace("[[name]]", "Dartles,Tim");
thisRow = thisRow.Replace("[[userID]]", "dartles");
sb.Append(thisRow);
rptxml = rptxml.Replace("[[rows]]", sb.ToString());
// write out the xml
Context.Response.Write(rptxml);
StreamWriter sw = new StreamWriter(Context.Server.MapPath("./Sample.xls"));
sw.Write(rptxml);
sw.Close();
sw.Dispose();
Context.Response.TransmitFile("Sample.xls");
}
}
any help would be greatly appreciated. thanks.
jose_jimenez
Contributor
2504 Points
484 Posts
Re: Dynamically Generate An Excel spreadsheet in asp.net
May 30, 2008 06:40 PM|LINK
It has been quite a while since I looked at this, but ...
when you get the strict parse error it gives you an error message, something like Details can be found in the file at.... and gives you a temporary file location. If you find that file, you should see some additional information in terms of the error.
--JJ
I don't answer personal emails unless I know you or of you. Feel free to post in the forum to get an answer from me.
jlhidalgo
Member
2 Points
1 Post
Re: Dynamically Generate An Excel spreadsheet in asp.net
Nov 11, 2008 01:59 PM|LINK
I had this problem too, but I solved it by just deleting the following line:
Context.Response.Write (rptxml);
This line is just before the line which creates the streamWrite object that will save your file on server; the problem occurs when the server transmits the file to the client, since Excel will get the information from both: the physical file and the buffer. This way Excel will get duplicated data, but somehow it will save into the client's machine a well-formed .xml file that you can later open from the client's file system with no problems at all.
One way to see how the data is being duplicated when writing rptxml to the buffer, is by writing the result to a .txt file instead of writing it to an .xls one, and then opening it from the client's file system. You will see that the same information is written twice into the same file.
As soon as I deleted that line all went perfect.