Clever Geek Handbook

ASP.NET expense report in Excel

In this project I'm currently working on, one of the newest feature requests is to output the expense information that's stored in an expense report that matches the Excel sheet they already use for all of their expense reports.

I was curious if there is a way I could take this excel worksheet (with all its layout already done) and just fill in the parts that our system is tracking and then serve them as an .xls file for the user without having to do Excel automation or any other method that requires Excel to be installed on the server.

Note: development environment is ASP.NET 2.0

0


source to share


7 replies


I am sure you can use OleDB to open the excel file (using template)

Example 1

string ConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;
                    Data Source=C:\Test Projects\Excel example\Excel - reading an excel file\ReadThis.xls;
                    Extended Properties=Excel 5.0";

//Create the connection
 System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);

Link to article 1



Example 2

string filename = @"C:\testdata.xls";

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + filename + ";" +

"Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection(connectionString);

objConn.Open();

OleDbCommand ObjCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

OleDbDataAdapter objAdp = new OleDbDataAdapter();

objAdp.SelectCommand = ObjCommand;

DataSet myDataSet = new DataSet();

objAdp.Fill(myDataSet);

DataTable dataTable = myDataSet.Tables["Sheet1$"];

Link to article 2

Once you've opened a connection, you can read and write however you like!

+3


source


You can try Spire.XLS ( http://www.e-iceblue.com/xls/xlsintro.htm )



It's not free, but it does a great job of reading and creating XLS documents without the need for Excel to be present on the server.

+1


source


One of the ways I have done this is using Office XML to generate documents. Since you have a specific format in which the document should be, things can be a little more complicated, but the way I have done before is to export the data that should be displayed in the report to an XML file (either saved to disk or in memory). and then apply the XSL transform to transform everything into a well-formatted document that Excel can load. For your reference, here are some links for you:

+1


source


Try Aspose.Cells - it works great for this.

0


source


Telerik RAD Grid for Asp.NetAJAX has nice Excel export.

0


source


Two more solutions:

  • use OpenOffice (they have a mode where no user interface is loaded / shown)
  • use FileHelpers library (you can read / write data to excel file)

I have to say that I have no experience with both of these ... but they sound like solid alternatives alongside the JET.OLEDB and Xml-to-Excel provider via Xsl (which I have used in the past) mentioned by Chris and Rob.

Just wanted to help you on your way and mention some alternatives (I don't expect a lot of points :))

0


source


While this won't handle your existing spreadsheets, a very simple and low-tech way to generate Excel files is to create an html file with the correct content type and use the .xls extension. When Excel (on the client) opens a file, it is treated as if it were a native spreadsheet.

Example:

<html>
    <head>
        <meta http-equiv="Content-Type" content="application/vnd.ms-excel" />
        <title></title>
    </head>
    <body>
        <table>
            <tr>
                <td><b>Forename</b></td>
                <td><b>Gender</b></td>
            </tr>
            <tr>
                <td>Simon</td>
                <td>Male</td>
            </tr>
            <tr>
                <td>Katy</td>
                <td>Female</td>
            </tr>
        </table>
    </body>
</html>

Caveats: Of course, you wouldn't include all the indentation and spacing in a real file! I am using Excel 2003.

Another small bonus of this method is that these files are very efficient to generate.

Disadvantages: I haven't found a way to turn the html into multiple Worksheets or do something particularly technical.

0


source






More articles:


All Articles