Dynamically Generate An Excel spreadsheet in asp.net RSS

• • Reply
  

  ajax_kid

  Member

  4 Points

  7 Posts

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Nov 11, 2008 10:50 PM|LINK

   thanks jlhidalgo, i deleted that line and now it works perfect. Also thanks for the additional information.
  

• • Reply
  

  stapes

  Member

  121 Points

  124 Posts

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Mar 31, 2009 11:12 AM|LINK

  I have tried the method given here, and the reulting output is a total mess. Here is my source code:

  using System;

  using System.Data;

  using System.Configuration;

  using System.Collections;

  using System.Web;

  using System.IO;

  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 Excel;

   

  public partial class ScriptExamples_ExcelXML : 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);

  string filePath = Context.Server.MapPath("~/Outputs/Sample.xls");StreamWriter sw = new StreamWriter(Context.Server.MapPath("~/Outputs/Sample.xls"));

  sw.Write(rptxml);

  sw.Close();

  sw.Dispose();

   

  Context.Response.TransmitFile(filePath );

  }

   

  }

   

   

  After execution, the file that is displayed looks like a replica of the screen I am using to run it from, complete with the button that is on that page. I found the Sample.xls file it had output and it looks a total mess. Here it is saved as XML:

  <?xml version="1.0"?>

  <?mso-applicati
  n progid="Excel.Sheet"?>

  <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">

  <LastAuthor>stephen</LastAuthor>

  <Created>2009-03-31T11:10:54Z</Created>

  <Version>11.9999</Version>

  </DocumentProperties>

  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>12405</WindowHeight>

  <WindowWidth>19020</WindowWidth>

  <WindowTopX>120</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="s22">

  <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>

  </Style>

  </Styles>

  <Worksheet ss:Name="Sample">

  <Table ss:ExpandedClumnCount="7" ss:ExpandedRwCount="4" x:FullColumns="1"

  x:FullRows="1">

  <Row ss:AutoFitHeight="0" ss:Height="25.5">

  <Cell ss:MergeAcross="6" ss:StyleID="s22"><Data ss:Type="String">jjimenez jjimenez 2006-11-10T20:34:11Z Member company of the AEGON Group 10.6817 1875 13260 480 30 False False </Data></Cell>

  </Row>

  <Row>

  <Cell><Data ss:Type="String">name date amount Smith,John [[date]]T00:00:00.000 [[amount]] Moreno,Alex [[date]]T0:00:00.000 [[amount]] Vivino,Jimmy [[date]]T00:00:00.000 [[amount]] Dartles,Tim [[date]]T00:00:00.00 [[amount]] 0.0</Data></Cell>

  </Row>

  <Row ss:Index="4">

  <Cell><Data ss:Type="String">600 600 3 7 2 False False False False False False </Data></Cell>

  </Row>

  </Table>

  <WorksheetOpti
  ns xmlns="urn:schemas-microsoft-com:office:excel">

  <Selected/>

  <DoNotDisplayGridlines/>

  <Panes>

  <Pane>

  <Number>3</Number>

  <ActiveRow>11</ActiveRow>

  <ActiveCol>11</ActiveCol>

  </Pane>

  </Panes>

  <ProtectObjects>False</ProtectObjects>

  <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOpti
  ns>

  </Worksheet>

   

  </Workbook>

• • Reply
  

  stapes

  Member

  121 Points

  124 Posts

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Mar 31, 2009 11:28 AM|LINK

  Me again.

  Do you think you could show us a sample using a more conventionally formatted spreadsheet with multiple rows and columns of data? The sample given is a total mess.

• • Reply
  

  stapes

  Member

  121 Points

  124 Posts

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Mar 31, 2009 12:55 PM|LINK

  I think I am going to try and make a universal version, with every single cell in the spreadsheet tagged, and all the possible styles identified. I also need to generate charts and graphs, but I think I can do that by making a seperate spreadsheet of all the charts & graphs and linking them to my output file.

• • Reply
  

  deterry

  Member

  2 Points

  1 Post

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Dec 24, 2009 03:41 PM|LINK

  I'm a little late to the game, but I just tried to use the exact same code above and I'm receiving a different error.  Well, I first received the same as you, so I deleted the Context.Response.Write(rptxml); line.  Now I receive an "Unknown" error, and when I open up the log file it shows:

  XML PARSE ERROR: Non-unique or duplicate attribute

   

  So then I changed the code to create a Sample.txt file.  When I open this text file, I see that the HTML from my Default.aspx page is appended to the bottom of the file, after the xml.  When I remove this HTML and change the txt extension to xls, I can open the file fine in Excel.  Clearly, this weird attachment of the Default.aspx HTML code to the end of the xml file is the problem.  What gives?   I'm a beginner at all this stuff, so if it's something obvious and I'm just not catching it... I apologize now.

  Thank you for any help!!

• • Reply
  

  SignOnTheDot...

  Member

  2 Points

  1 Post

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Mar 04, 2010 05:50 PM|LINK

  I don't know if it's because I'm using Office 2007, but this did not work at all for me. I created a report file manually to test and when I open it with excel I get a warning that the file format does not match the extension; the file loads but it's all garbled.

• • Reply
  

  abdulwakeel

  Participant

  1288 Points

  305 Posts

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Apr 02, 2010 09:11 AM|LINK

  Working with excel you have to learn About
  Three Classess
   

   Application
   WorkBook
   WorkSheet

  Application class is used for Excel Application

  Pleae click on as answer . if you feel i get some thing from this post
• • Reply
  

  arbie

  Member

  2 Points

  1 Post

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Jun 17, 2010 04:15 PM|LINK

  Whilst I appreciate this is microsoft technology, my app must work across all browsers and the example you give (at the start of this thread) does not work in:

  • Opera
  • Firefox
  • Chrome

   

  • Edited by arbie on Jun 17, 2010 04:16 PM
  • Edited by arbie on Jun 17, 2010 04:16 PM
  • Edited by arbie on Jun 17, 2010 04:17 PM
• • Reply
  

  zzzxtreme

  Member

  22 Points

  21 Posts

  Re: Dynamically Generate An Excel spreadsheet in asp.net

  Jun 18, 2010 01:31 AM|LINK

  
  

  Response.Clear()
  Response.Buffer = True
  Response.ContentType = "application/vnd.ms-excel"
  Response.AppendHeader("content-disposition", "attachment; filename=" & filename)
  Response.Charset = ""
  Response.Write(DataTable2ExcelString(MyDataTable))
  Response.End()
  
  

  
  

  
  

  
  

  Public Function DataTable2ExcelString(ByVal dt As Data.DataTable) As String
  
  Dim sbTop As New Text.StringBuilder()
  sbTop.AppendLine("<?xml version=""1.0""?>")
  sbTop.AppendLine("<?mso-application progid=""Excel.Sheet""?>")
  sbTop.AppendLine("<Workbook")
  sbTop.AppendLine(" xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")
  sbTop.AppendLine(" xmlns:o=""urn:schemas-microsoft-com:office:office""")
  sbTop.AppendLine(" xmlns:x=""urn:schemas-microsoft-com:office:excel""")
  sbTop.AppendLine(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
  sbTop.AppendLine(" xmlns:html=""http://www.w3.org/TR/REC-html40"">")
  sbTop.AppendLine(" <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
  sbTop.AppendLine(" <Author>D</Author>")
  sbTop.AppendLine(" <LastAuthor></LastAuthor>")
  sbTop.AppendLine(" <Created></Created>")
  sbTop.AppendLine(" <Company></Company>")
  sbTop.AppendLine(" <Version></Version>")
  sbTop.AppendLine(" </DocumentProperties>")
  sbTop.AppendLine(" <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")
  sbTop.AppendLine(" <ProtectStructure>False</ProtectStructure>")
  sbTop.AppendLine(" <ProtectWindows>False</ProtectWindows>")
  sbTop.AppendLine(" </ExcelWorkbook>")
  sbTop.AppendLine(" <Styles>")
  sbTop.AppendLine(" <Style ss:ID=""Default"" ss:Name=""Normal"">")
  sbTop.AppendLine(" <Alignment ss:Vertical=""Bottom"" />")
  sbTop.AppendLine(" <Borders />")
  sbTop.AppendLine(" <Font />")
  sbTop.AppendLine(" <Interior />")
  sbTop.AppendLine(" <NumberFormat />")
  sbTop.AppendLine(" <Protection />")
  sbTop.AppendLine(" </Style>")
  sbTop.AppendLine(" <Style ss:ID=""s21"">")
  sbTop.AppendLine(" <Font x:Family=""Swiss"" ss:Bold=""1"" />")
  sbTop.AppendLine(" </Style>")
  sbTop.AppendLine(" </Styles>")
  sbTop.AppendLine(" <Worksheet ss:Name=""Sheet1"">")
  sbTop.AppendLine(" <Table>")
  
  
  sbTop.AppendLine("<Row>")
  Dim i As Integer
  For i = 0 To dt.Columns.Count - 1
  sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt.Columns(i).ColumnName & "").Replace("<","&lt;").Replace(">","&gt;") & "</Data></Cell>")
  Next
  sbTop.AppendLine("</Row>")
  
  'Items
  Dim x As Integer
  dim sType as String
  For x = 0 To dt.Rows.Count - 1
  sbTop.Append("<Row>")
  For i = 0 To dt.Columns.Count - 1
  if dt.Columns(i).DataType is gettype(Decimal) then
  sType = "Number"
  else
  sType = "String"
  end if
  sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt.Rows(x)(i) & "").Replace("<","&lt;").Replace(">","&gt;").Replace(vbnewline,"&#10;") & "</Data></Cell>")
  Next
  sbTop.Append("</Row>")
  Next
  
  sbTop.AppendLine(" </Table>")
  sbTop.AppendLine(" </Worksheet>")
  sbTop.AppendLine("</Workbook>")
  
  Return sbTop.ToString()
  
  End Function
  
  

  Unconventional, college drop-out programmer. I love using GOTO