Excel中的ASP.NET输出费用报表

On this project I am working on right now, one of the newest feature requests is to output expense information which is stored into expense report that matches an Excel worksheet they already use for all of their expense reporting.

在我正在进行的这个项目中,最新的功能请求之一是输出费用信息,该信息存储在费用报告中,该报告与他们已用于所有费用报告的Excel工作表相匹配。

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

我很好奇是否有办法让我可以使用这个excel工作表(已完成所有布局)并只填写我们系统跟踪的部分,然后将其作为.xls文件提供给用户而不必执行Excel自动化或任何其他需要在服务器上安装Excel的方法。

Note: development environment is ASP.NET 2.0

注意:开发环境是ASP.NET 2.0

7 个解决方案

#1

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

我很确定你可以使用OleDB来打开excel文件(使用模板)

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

链接到第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

链接到第2条

Once you have opened the connection you should be able to read and write to it however you like!

打开连接后,您应该能够随意读取和写入连接!

#2

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

你可以试试Spire.XLS(http://www.e-iceblue.com/xls/xlsintro.htm)

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

它不是免费的,但在阅读和创建XLS文档方面做得很好,而无需在服务器上安装Excel。

#3

One of the ways that I have done this is using Office XML to generate the documents. Since you have a specific format that the document needs to be in, things might be a bit harder, but the way I have done things before in the past is to export the data that needs to be displayed on the report to an XML file (either saved on disk or in the memory) and then apply an XSL transform to convert things over to the correctly formatted document that Excel can load. For reference, here are some links for you:

我这样做的一种方法是使用Office XML生成文档。由于您具有文档需要的特定格式,事情可能会更难,但我之前做过的事情是将需要在报表上显示的数据导出到XML文件(保存在磁盘或内存中,然后应用XSL转换将事物转换为Excel可以加载的格式正确的文档。供参考,以下是一些链接:

XML in Office Developer Portal

Office Developer Portal中的XML

Convert XML to an Excel Spreadsheet using XSL

使用XSL将XML转换为Excel电子表格

#4

Try Aspose.Cells - it works great for this.

试试Aspose.Cells - 它非常适合这个。

#5

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

用于Asp.NetAJAX的Telerik RAD Grid具有很好的Excel导出功能。

#6

Another two solutions are:

另外两个解决方案是:

use OpenOffice (they have a mode where no UI is loaded/shown)

使用OpenOffice(他们有一个没有加载/显示UI的模式)

use the FileHelpers library (you can read/write data to an excel file)

使用FileHelpers库(您可以读取/写入excel文件的数据)

I must say I don't have experience with either of them... but they sound like solid alternatives beside the JET.OLEDB provider and the Xml-to-Excel through Xsl (which I both have used in the past) mentioned by Chris and Rob.

我必须说我对其中任何一个都没有经验...但它们听起来像JET.OLEDB提供者旁边的可靠替代品和Chris提到的Xsl(我过去都使用过的Xml-to-Excel)和罗布

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

只是想在你的路上帮助你并提一些替代方案(我不指望多少分:))

#7

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

虽然这不会处理您现有的电子表格,但生成Excel文件的一种非常简单且低技术的方法是创建具有正确内容类型的html文件并使用.xls扩展名。当Excel(在客户端上)打开文件时,它被视为本机电子表格。

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 would not include all the indentation and spacing in the real file! I am using Excel 2003.

警告:当然你不会在真实文件中包含所有缩进和间距!我正在使用Excel 2003。

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

这种技术的另一个小优点是这些文件生成效率非常高。

Downsides: I've not found a way to turn html into multiple Worksheets, or to do anything particularly technical.

缺点:我没有找到办法将html转换成多个工作表,或者做任何特别技术性的事情。

#1