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. — Coding Flow
– Coding Flow, Commented Sep 11, 2014 at 8:27
@BenRobinson is there any approach to do that? Please provide me some code for that.. — Nimit Joshi
– Nimit Joshi, Commented Sep 11, 2014 at 8:32
I am not here to write your code for you and I don't really understand what you need. — Coding Flow
– Coding Flow, Commented Sep 11, 2014 at 8: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
– Nimit Joshi, Commented Sep 11, 2014 at 8:35

Community · Accepted Answer · 2017-05-23 12:02:00Z

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

Arpan Mukherjee · Accepted Answer · 2014-09-11 08:42:39Z

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, 2014 at 8:42

Arpan Mukherjee

451 silver badge5 bronze badges

1 Comment

Nimit Joshi Over a year ago

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

Collectives™ on Stack Overflow

Appending data from database to Excel in Asp.Net

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related