IW1QLH

ASPX.CS C# - How to create a XML MS-Excel file

    // Save this file NAME.XML.XLS
    protected System.IO.Stream MakeXML()
    {

        System.IO.MemoryStream stream = new System.IO.MemoryStream(1024);
        System.Xml.XmlTextWriter xml = new System.Xml.XmlTextWriter(stream, System.Text.Encoding.UTF8);

        // <?xml>
        xml.WriteStartDocument();
        xml.WriteComment("Open this file with Microsoft Excel");

        // <Workbook ...>
        xml.WriteStartElement("Workbook");
        xml.WriteAttributeString("xmlns", null, null, "urn:schemas-microsoft-com:office:spreadsheet");
        xml.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
        xml.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
        xml.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
        xml.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

        // <Styles>
        xml.WriteStartElement("Styles");
        // <Style Default>
        xml.WriteStartElement("Style");
        xml.WriteAttributeString("ss", "ID", null, "Default");
        xml.WriteAttributeString("ss", "Name", null, "Normal");
        xml.WriteStartElement("Alignment");
        xml.WriteAttributeString("ss", "Vertical", null, "Bottom");
        xml.WriteEndElement();
        xml.WriteEndElement();

        // <Style ID=s30 NumberFormat=#,##0.00 Borders>
        xml.WriteStartElement("Style");
        xml.WriteAttributeString("ss", "ID", null, "s30");
        xml.WriteStartElement("NumberFormat");
        xml.WriteAttributeString("ss", "Format", null, "#,##0.00");
        xml.WriteEndElement();
        xml.WriteStartElement("Borders");
        xml.WriteStartElement("Border");
        xml.WriteAttributeString("ss", "Position", null, "Bottom");
        xml.WriteAttributeString("ss", "LineStyle", null, "Continuous");
        xml.WriteAttributeString("ss", "Weight", null, "1");
        xml.WriteEndElement();
        xml.WriteStartElement("Border");
        xml.WriteAttributeString("ss", "Position", null, "Left");
        xml.WriteAttributeString("ss", "LineStyle", null, "Continuous");
        xml.WriteAttributeString("ss", "Weight", null, "1");
        xml.WriteEndElement();
        xml.WriteStartElement("Border");
        xml.WriteAttributeString("ss", "Position", null, "Right");
        xml.WriteAttributeString("ss", "LineStyle", null, "Continuous");
        xml.WriteAttributeString("ss", "Weight", null, "1");
        xml.WriteEndElement();
        xml.WriteStartElement("Border");
        xml.WriteAttributeString("ss", "Position", null, "Top");
        xml.WriteAttributeString("ss", "LineStyle", null, "Continuous");
        xml.WriteAttributeString("ss", "Weight", null, "1");
        xml.WriteEndElement();
        xml.WriteEndElement();
        xml.WriteEndElement();

        // ... here other styles

        // </Styles>
        xml.WriteEndElement();

        // <Worksheet ss:Name="Sheet1">
        xml.WriteStartElement("Worksheet");
        xml.WriteAttributeString("ss", "Name", null, "Sheet1");

        // <Table>
        xml.WriteStartElement("Table");

        // <Colums >
        xml.WriteStartElement("Column");
        xml.WriteAttributeString("ss", "Width", null, "15");
        xml.WriteEndElement();
        xml.WriteStartElement("Column");
        xml.WriteAttributeString("ss", "Hidden", null, "1");
        xml.WriteAttributeString("ss", "Width", null, "40");
        xml.WriteEndElement();

        // HEADER
        xml.WriteStartElement("Row");
        xml.WriteEndElement();
        xml.WriteStartElement("Row");
        WriteCell(xml, null, null, "Default", "String", null, "Name");
        xml.WriteEndElement();
        xml.WriteStartElement("Row");
        WriteCell(xml, null, null, "Default", "String", null, "Phone");
        xml.WriteEndElement();
        xml.WriteStartElement("Row");
        WriteCell(xml, null, null, "Default", "String", null, "Price");
        xml.WriteEndElement();
        WriteCell(xml, null, null, "Default", "String", null, "Quantity");
        xml.WriteEndElement();
        WriteCell(xml, null, null, "Default", "String", null, "Amount");
        xml.WriteEndElement();

        // RIGHE
        foreach (DataSetCustomer.CustomerRow row in CustomerTable)
        {
            xml.WriteStartElement("Row");
            WriteCell(xml, null, null, "s30", "String", null, row.Name);
            WriteCell(xml, null, null, "s30", "String", null, row.Phone);
            WriteCell(xml, null, null, "s30", "Number", null, row.Price);
            WriteCell(xml, null, null, "s30", "Number", null, row.Quantity);
            WriteCell(xml, null, null, "s30", "Number", "=RC[-2]*RC[-1]", (row.Price * row.Quantity).ToString());
            xml.WriteEndElement();
        }

        // </Table>
        xml.WriteEndElement();

        // <WorksheetOptions>
        xml.WriteStartElement("WorksheetOptions");
        xml.WriteAttributeString("xmlns", null, null, "urn:schemas-microsoft-com:office:excel");
        xml.WriteStartElement("PageSetup");
        xml.WriteStartElement("Layout");
        xml.WriteAttributeString("x", "Orientation", null, "Landscape");
        xml.WriteEndElement();
        xml.WriteStartElement("Footer");
        xml.WriteAttributeString("x", "Data", null, "&S&T&D&P/&M");
        xml.WriteEndElement();
        xml.WriteEndElement();

        // </WorksheetOptions">
        xml.WriteEndElement();

        // </Worksheet">
        xml.WriteEndElement();

        // </Workbook>
        xml.WriteEndElement();
        xml.WriteEndDocument();

        xml.Flush();
        stream.Position = 0;
        return stream;
    }

    protected void WriteCell(System.Xml.XmlTextWriter xml, string Index, string MergeAcross, string StyleID, string Type, string Formula, string Value)
    {
        // <Cell ...>
        xml.WriteStartElement("Cell");
        if (Index != null)
            xml.WriteAttributeString("ss", "Index", null, Index);
        if (MergeAcross != null)
            xml.WriteAttributeString("ss", "MergeAcross", null, MergeAcross);
        if (StyleID != null)
            xml.WriteAttributeString("ss", "StyleID", null, StyleID);
        if (Formula != null)
            xml.WriteAttributeString("ss", "Formula", null, Formula);
        // <Data ...>
        if (Value != null)
        {
            xml.WriteStartElement("Data");
            xml.WriteAttributeString("ss", "Type", null, Type);
            xml.WriteValue(Value);
            xml.WriteEndElement();
        }
        // </Cell>
        xml.WriteEndElement();
    }

< Prev		Next >