使用C＃读取和写入Excel的最快方式 [英] Fastest way to read from and write to Excel using C#
问题描述
Possible Duplicate:
What is the best / fastest way to export large set of data from C# to excel
我正在创建一个具有以下功能的Web应用程序：
I am creating a web application with the following functionality:
-
假设管理员将一些数据存储在预定义的Excel模板文件中。他将访问管理页面并上传此Excel。应用程序读取此Excel并相应更新数据。
Assume an admin stores some data in a predefined Excel template file. He will visit the Admin page and upload this Excel. The application reads this Excel and updates data in the database accordingly.
存储在数据库中的数据必须显示给访问ViewDetails页面的任何用户。现在，数据应该在屏幕上显示给用户，并且必须提供下载到Excel选项。另外，用户可以选择过滤数据并排序数据。
The data stored in the database has to be displayed to any user visiting the ViewDetails page. Now, the data should be displayed on screen to the user as well as a Download to Excel option has to be provided. Also, user will have option of filtering the data and sorting the data.
假设行数为10000 +，以及每行文本/数字数据的大约10-15列。
Assume that number of rows will be 10000+, and about 10-15 columns of text/numeric data in each row.
我目前的想法是使用Interop.Excel对象读取Excel模板并上传数据库中的数据。对于阅读，我通常会将所有数据提取到DataTable中，然后根据需要显示。我打算使用DataView进行排序和过滤。但是我从来没有使用过这种数据的数据，我不清楚使用上述方法的性能。
My current thoughts are to use the Interop.Excel object to read the Excel template and upload the data in the database. For reading, I usually fetch all my data into a DataTable and then display it as required. I plan to use DataView for my sorting and filtering purposes. However I have never worked with data of this magnitude and I am not sure about the performance using the above method.
我的问题是，最好的和最快的方式是去实现我的要求？
我读到某处，需要大约2-3分钟才能将这么多数据写入Excel文件。
My question is, what is the best and fastest way to go about realizing my requirement? I read somewhere that it will take about 2-3 min to write that much data into an Excel file.
我也遇到了OpenXML：
Open XML是一种用于文字处理文档，演示文稿和电子表格的开放标准，可以在不同平台上由多个应用程序自由实现。
I also came across OpenXML: "Open XML is an open standard for word-processing documents, presentations, and spreadsheets that can be freely implemented by multiple applications on different platforms."
我可以用它来更好地实现要求吗？还有其他一些方法吗？
我最近开始开发C＃Web应用程序，并且首次使用Excel电子表格，所以如果有任何明显的答案我不知道，请与我联系。
感谢提前：）
Can I use it for better realizing the requirement or is there some other way? I started developing C# web applications recently, and working with Excel spreadsheets for the first time, so please bear with me if there are any obvious answers I do not know about. Thanks in Advance :)
推荐答案
我建议使用epplus加载和保存excel文件。
这是非常快速和多才多艺的。特别加载它很容易：
I would recommend to use epplus to load and save the excel-file. It is very fast and versatile. Especially loading it is easy:
Try
Dim existingFile As New FileInfo(path)
Using ExcelPackage As New ExcelPackage(existingFile)
Dim Wsht As ExcelWorksheet = Nothing
Try
Wsht = ExcelPackage.Workbook.Worksheets(1)
Catch ex As Exception
End Try
Dim tbl As New DataTable
Dim hasHeader = True
For i = 1 To Wsht.Dimension.End.Column
tbl.Columns.Add()
'this is very "dumb" if you need better column names just iterate over the file and get them
Next
Dim startRow = 1 '1 due to the excel quirk
For rowNum = startRow To Wsht.Dimension.End.Row
Dim wsRow = Wsht.Cells(rowNum, 1, rowNum, Wsht.Dimension.End.Column)
Dim row = tbl.NewRow
For Each cell In wsRow
row(cell.Start.Column - 1) = cell.Text
Next
tbl.Rows.Add(row)
Next
'do something with the tbl datatable
End Using
Catch ex As Exception
End Try
缺点：目前只支持xlsx文件
The drawback: only xlsx files are currently supported
这篇关于使用C＃读取和写入Excel的最快方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
