ASP 101 - Quick Tips

Quick Tips

Saving Data to an Excel File

People really love Microsoft Excel. Many users are so comfortable with Excel they seem to want to use it for everything. Which is probably why I get so many requests about using Excel with ASP.NET. As it turns out, the real question most users want answered is how can they store data from a Web page in an Excel-readable format?

While using a single-user application like Excel in a server environment is generally a very bad idea, there's no reason you can't generate data files that Excel is happy to open. I've previously published a few examples of this type of thing which utilize Excel's ability to import HTML tables quite nicely: Displaying Data from ASP in Excel and our Excel Sample.

This time around, the goal is more getting a chunk of data into Excel in order to do something with it rather then simply using Excel as a client-side data viewer. To that end, this ASP.NET code snippet is going to take data entered by a user and save it to a CSV (comma-separated values) file. Then you can take the .csv file and open it using Excel and manipulate it to your heart's content.


<%@ Page Language="VB" %>

<%@ Import Namespace="System.IO" %>

<script runat="server">

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim sbTextToWrite As New StringBuilder



        sbTextToWrite.Append(Now().ToString & ",")

        sbTextToWrite.Append(txtName.Text & ",")

        sbTextToWrite.Append(txtAddress.Text & ",")

        sbTextToWrite.Append(txtCity.Text & ",")

        sbTextToWrite.Append(txtState.Text & ",")

        sbTextToWrite.Append(txtZip.Text & ",")

        sbTextToWrite.Append(txtPhone.Text)

        sbTextToWrite.AppendLine()



        File.AppendAllText(Server.MapPath("datafile.csv"), sbTextToWrite.ToString)

    End Sub

</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

    <title>Form To CSV (aka. Excel File)</title>

</head>

<body>

<form id="myForm" runat="server">



<p>

    Name: <asp:TextBox ID="txtName" runat="server" /><br />

    Address: <asp:TextBox ID="txtAddress" runat="server" /><br />

    City: <asp:TextBox ID="txtCity" runat="server" /><br />

    State: <asp:TextBox ID="txtState" runat="server" /><br />

    Zip: <asp:TextBox ID="txtZip" runat="server" /><br />

    Phone: <asp:TextBox ID="txtPhone" runat="server" /><br />

    <asp:Button ID="btnSubmit" runat="server"

        OnClick="btnSubmit_Click"

        Text="Save Contact Info"

    />

</p>



</form>

</body>

</html>

In an effort to keep things simple, I've left making the form pretty and the validation for you to implement. Aside from that and making sure the NTFS permissions on the file system are set to allow you to write to the data file, the sample script should work as it stands.