locked
Dynamically Generate An Excel spreadsheet in asp.net RRS feed

  • Question

  • User-1348799698 posted

    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

     

    Monday, October 23, 2006 9:47 PM

All replies

  • User-1348799698 posted

    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

     

       <Row>
        <Cell><Data ss:Type="String">[[name]]</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">[[date]]T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">[[amount]]</Data></Cell>
       </Row>
     The resulting xls file contains:
     
     
    <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>
       
       <Row>
        <Cell><Data ss:Type="String">J Jimenez</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-01-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">12.5</Data></Cell>
       </Row>
       <Row>
        <Cell><Data ss:Type="String">J J</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-02-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">75.00</Data></Cell>
       </Row>
       <Row>
        <Cell><Data ss:Type="String">Someone Else</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-03-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">35.28</Data></Cell>
       </Row>
       <Row>
        <Cell><Data ss:Type="String">Oh Bother</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-04-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">58.69</Data></Cell>
       </Row>
       <Row>
        <Cell ss:Index="3" ss:Formula="=SUM(R[-4]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>
    <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>
       
       <Row>
        <Cell><Data ss:Type="String">J Jimenez</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-01-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">12.5</Data></Cell>
       </Row>
       <Row>
        <Cell><Data ss:Type="String">J J</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-02-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">75.00</Data></Cell>
       </Row>
       <Row>
        <Cell><Data ss:Type="String">Someone Else</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-03-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">35.28</Data></Cell>
       </Row>
       <Row>
        <Cell><Data ss:Type="String">Oh Bother</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2006-04-01T00:00:00.000</Data></Cell>
        <Cell><Data ss:Type="Number">58.69</Data></Cell>
       </Row>
       <Row>
        <Cell ss:Index="3" ss:Formula="=SUM(R[-4]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>
    
     

    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.

    Friday, November 10, 2006 4:48 PM
  • User2000122272 posted

    Do you also have a VB.NET code example?

    Thursday, July 5, 2007 1:46 PM
  • User-1348799698 posted

    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

    Thursday, July 5, 2007 2:53 PM
  • User746351338 posted

    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/
     

    Thursday, August 16, 2007 8:12 PM
  • User-328508779 posted

    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?

     

    Tuesday, April 29, 2008 9:25 PM
  • User-328508779 posted

    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

    Wednesday, April 30, 2008 11:11 AM
  • User537800477 posted

     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.
     

    Saturday, May 10, 2008 10:57 PM
  • User-1348799698 posted

    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 

    Friday, May 30, 2008 2:40 PM
  • User-630751429 posted

    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.

    Tuesday, November 11, 2008 9:59 AM
  • User537800477 posted

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

    Tuesday, November 11, 2008 6:50 PM
  • User-1176147176 posted

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

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

    </WorksheetOptins>

    </Worksheet>

     

    </Workbook>

    Tuesday, March 31, 2009 7:12 AM
  • User-1176147176 posted

    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.

    Tuesday, March 31, 2009 7:28 AM
  • User-1176147176 posted

    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.

    Tuesday, March 31, 2009 8:55 AM
  • User-1508050996 posted

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

    Thursday, December 24, 2009 11:41 AM
  • User-13720565 posted

    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.

    Thursday, March 4, 2010 1:50 PM
  • User1789523204 posted

    Working with excel you have to learn About
    Three Classess
     

     Application
     WorkBook
     WorkSheet

    Application class is used for Excel Application

    Friday, April 2, 2010 5:11 AM
  • User1865247316 posted

    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

     

    Thursday, June 17, 2010 12:15 PM
  • User-2079636174 posted


    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

    Thursday, June 17, 2010 9:31 PM