Appending data from database to Excel in Asp.Net

Question

I have developed an application in which I have to insert data from database to Excel, the size of inserting data is predefined.

Suppose there are 20 records of any city. Now at first I have to insert only 5 records then after viewing some message to user next 5 data will append to the same Excel file and so on.

Following is my code to insert data to Excel and the size is defined in the Web.Config file.

Web.Config Code:

<appSettings>
  <add key="UserRecord" value="6"/>
</appSettings>

Code:

public void InsertRecordToExcel()
    {
        DAL ObjDal = new DAL();
        string record = ConfigurationManager.AppSettings["UserRecord"];
        try
        {
            int n = Convert.ToInt32(record);
            string filename = Server.MapPath("UserDataSheet");
            string City = TxtCityName.Text;
            DataTable dt = new DataTable();
            dt = ObjDal.GetData(City);
            while(dt.Rows.Count>0)
            {
                StringWriter writer = new StringWriter();
                HtmlTextWriter htmlWriter = new HtmlTextWriter(writer);
                GridView gridView = new GridView();
                DataTable dtn = new DataTable();
                gridView.DataSource = dt.AsEnumerable().Take(n).CopyToDataTable();
                gridView.AutoGenerateColumns = true;
                gridView.DataBind();
                gridView.RenderControl(htmlWriter);
                htmlWriter.Close();
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");
                Response.Charset = "";
                Response.Write(writer.ToString());
                Response.End();
                LblMessage.Text = n + " " + "Record Added Successfully";
            }                
        }

        catch (Exception ex)
        {
            throw ex;
        }
    }

What is the procedure of appending a data to the Excel file?

You can't append to an excel file that you have already sent to the user, they have downloaded it. You are not even doing that really you are just sending them HTML which excel will turn into a spreadsheet when opened. — Ben Robinson, Sep 11 '14 at 08:27
@BenRobinson is there any approach to do that? Please provide me some code for that.. — Nimit Joshi, Sep 11 '14 at 08:32
I am not here to write your code for you and I don't really understand what you need. — Ben Robinson, Sep 11 '14 at 08:33
i simply want to insert some size of data to the excel and after providing a message again append the rest data to the excel. @BenRobinson — Nimit Joshi, Sep 11 '14 at 08:35
Why do you need to do the first step and store the data in an excel file. Why not just send a message then send all the data. Your existing code will do that latter, you just need to provide the correct data. — Ben Robinson, Sep 11 '14 at 08:43

score 1 · Accepted Answer · edited May 23 '17 at 12:02

You can create excel files for example just with xmlwriter. Or the more comfortable way would be to use a library for that. (Create Excel (.XLS and .XLSX) file from C#)

with Library:

    void export_Click(object sender, EventArgs e){
        byte[] data = File.ReadAllBytes("export.xls"); //The created excel file with the library, if the library supports getting the excel as a stream you can use the method below to stream it.
        string filename = "export.xls"; 

        Response.Clear();
        Response.ClearHeaders();
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename="+filename);
        Response.AddHeader("Content-Type", "application/Excel");
        Response.ContentType = "application/vnd.xls";
        Response.AddHeader("Content-Length", bytesInStream.Length.ToString());
        Response.BinaryWrite(data);
        Response.End();       
    }

The way with xml:

     void export_Click(object sender, EventArgs e){
        MemoryStream ms = ExportDataTableToWorksheet(ds, ds2, true);
        byte[] bytesInStream = ms.ToArray(); 

        string filename = "export.xls";

        Response.Clear();
        Response.ClearHeaders();
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename="+filename);
        Response.AddHeader("Content-Type", "application/Excel");
        Response.ContentType = "application/vnd.xls";
        Response.AddHeader("Content-Length", bytesInStream.Length.ToString());
        Response.BinaryWrite(bytesInStream);
        Response.End();       
    }

    public static MemoryStream ExportDataTableToWorksheet(DataSet ds)
    {
        MemoryStream ms = new MemoryStream();
        XmlTextWriter writer = new XmlTextWriter(ms, Encoding.UTF8);

        writer.Formatting = Formatting.Indented;

        // <?xml version="1.0"?>
        writer.WriteStartDocument();

        // <?mso-application progid="Excel.Sheet"?>
        writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

        // <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
        writer.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");

        // Namespace definitions
        writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
        writer.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
        writer.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
        writer.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

        // <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        writer.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

        // Documentattributes Author, Date, Company
        writer.WriteElementString("Author", Environment.UserName);
        writer.WriteElementString("LastAuthor", Environment.UserName);
        writer.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
        writer.WriteElementString("Company", "Unknown");
        writer.WriteElementString("Version", "11.8122");

        // </DocumentProperties>
        writer.WriteEndElement();

        // <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        writer.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

        // Workbook-Preferences
        writer.WriteElementString("WindowHeight", "13170");
        writer.WriteElementString("WindowWidth", "17580");
        writer.WriteElementString("WindowTopX", "120");
        writer.WriteElementString("WindowTopY", "60");
        writer.WriteElementString("ProtectStructure", "False");
        writer.WriteElementString("ProtectWindows", "False");

        // </ExcelWorkbook>
        writer.WriteEndElement();

        // <Styles>
        writer.WriteStartElement("Styles");

        // <Style ss:ID="Default" ss:Name="Normal">
        writer.WriteStartElement("Style");
        writer.WriteAttributeString("ss", "ID", null, "Default");
        writer.WriteAttributeString("ss", "Name", null, "Normal");

        // <Alignment ss:Vertical="Bottom"/>
        writer.WriteStartElement("Alignment");
        writer.WriteAttributeString("ss", "Vertical", null, "Bottom");
        writer.WriteEndElement();

        // Verbleibende Sytle-Eigenschaften leer schreiben
        writer.WriteElementString("Borders", null);
        writer.WriteElementString("Font", null);
        writer.WriteElementString("Interior", null);
        writer.WriteElementString("NumberFormat", null);
        writer.WriteElementString("Protection", null);

        // </Style>
        writer.WriteEndElement();

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

        // <Worksheet ss:Name="xxx">
        writer.WriteStartElement("Worksheet");
        writer.WriteAttributeString("ss", "Name", null, "export");

        // <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
        writer.WriteStartElement("Table");

        // setting the column count
        writer.WriteAttributeString("ss", "ExpandedColumnCount", null, "1");
        else writer.WriteAttributeString("ss", "ExpandedRowCount", null, (ds.Tables[0].Rows.Count).ToString());
        writer.WriteAttributeString("x", "FullColumns", null, "1");
        writer.WriteAttributeString("x", "FullRows", null, "1");
        writer.WriteAttributeString("ss", "DefaultColumnWidth", null, "100");

        foreach (DataRow row in ds.Tables[0].Rows)
        {
            // <Row>
            writer.WriteStartElement("Row");

            // Alle Zellen der aktuellen Zeile durchlaufen

            writeCell(writer, (string)row["name"]);

            // </Row>
            writer.WriteEndElement();
        }
        // </Table>
        writer.WriteEndElement();

        // <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
        writer.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");

        // pagesetup
        writer.WriteStartElement("PageSetup");
        writer.WriteStartElement("Header");
        writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
        writer.WriteEndElement();
        writer.WriteStartElement("Footer");
        writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
        writer.WriteEndElement();
        writer.WriteStartElement("PageMargins");
        writer.WriteAttributeString("x", "Bottom", null, "0.984251969");
        writer.WriteAttributeString("x", "Left", null, "0.78740157499999996");
        writer.WriteAttributeString("x", "Right", null, "0.78740157499999996");
        writer.WriteAttributeString("x", "Top", null, "0.984251969");
        writer.WriteEndElement();
        writer.WriteEndElement();

        // <Selected/>
        writer.WriteElementString("Selected", null);

        // <Panes>
        writer.WriteStartElement("Panes");

        // <Pane>
        writer.WriteStartElement("Pane");

        //
        writer.WriteElementString("Number", "1");
        writer.WriteElementString("ActiveRow", "1");
        writer.WriteElementString("ActiveCol", "1");

        // </Pane>
        writer.WriteEndElement();

        // </Panes>
        writer.WriteEndElement();

        // <ProtectObjects>False</ProtectObjects>
        writer.WriteElementString("ProtectObjects", "False");

        // <ProtectScenarios>False</ProtectScenarios>
        writer.WriteElementString("ProtectScenarios", "False");

        // </WorksheetOptions>
        writer.WriteEndElement();

        // </Worksheet>
        writer.WriteEndElement();

        // </Workbook>
        writer.WriteEndElement();

        writer.Flush();
        writer.Close();
        return ms;
    }

    /// <summary>
    /// Removes control characters and other non-UTF-8 characters
    /// </summary>
    /// <param name="inString">The string to process</param>
    /// <returns>A string with no control characters or entities above 0x00FD</returns>
    public static string RemoveTroublesomeCharacters(string input)
    {
        var isValid = new Predicate<char>(value =>
    (value >= 0x0020 && value <= 0xD7FF) ||
    (value >= 0xE000 && value <= 0xFFFD) ||
    value == 0x0009 ||
    value == 0x000A ||
    value == 0x000D);

        return new string(Array.FindAll(input.ToCharArray(), isValid));

    }

    static void writeCell(XmlTextWriter writer, string value)
    {
        // <Cell>
        writer.WriteStartElement("Cell");

        // <Data ss:Type="String">xxx</Data>
        writer.WriteStartElement("Data");
        writer.WriteAttributeString("ss", "Type", null, "String");

        // Zelleninhakt schreiben
        writer.WriteValue(value);

        // </Data>
        writer.WriteEndElement();

        // </Cell>
        writer.WriteEndElement();
    }

Thanks for the code. Where can i define the size of the record? — Nimit Joshi, Sep 11 '14 at 08:40
writer.WriteAttributeString("ss", "ExpandedColumnCount", null, "1"); writer.WriteAttributeString("ss", "ExpandedRowCount",..) here you define how much columns and rows needed. probably you can easily get the size out of the datatable — user1519979, Sep 11 '14 at 08:44

score 0 · Answer 2 · answered Sep 11 '14 at 08:42

0

"after viewing some message to user" - what is this message and where are you showing this ? Assuming this message is on web/desktop just insert the first 5 records into an excel, maintain the excel workbook instance, show the message, insert next set of data to the active workbook.

One more idea will be to play with a datatable and only when the user downloads the excel do you fill the excel

It will be helpful if you can explain the significance of showing the message

answered Sep 11 '14 at 08:42

Arpan Mukherjee

45
1
5

Suppose a user want to show the record of some city and when the user click on the submit button, then the message will be displayed on the label – Nimit Joshi Sep 11 '14 at 08:46

Appending data from database to Excel in Asp.Net

2 Answers2