.net - 将大量数据从 .NET 对象加载到 Excel

Question

我正在开发一个使用 excel 作为其界面的数据库应用程序。本质上，它是一个通过 .NET 库连接到数据库的 excel 宏。

主要问题是客户端需要能够管理大量数据(5000-20000 行，大约 160 列)。我已经设法最大限度地减少数据库访问的延迟，并将整个数据 block 作为字符串直接加载到电子表格范围的 .Value 属性中(格式化为字符串 [，])。它快速、简单、高效。

但现在客户需要支持 excel 格式。因为，我需要excel来识别日期、数字等等。这在支持非统一数据类型的同时:日期列可能包含日期格式，但也可能包含文本或其他内容。

我尝试了几种方法:
- 继续加载字符串，根据预期的日期类型对列进行预格式化(例如，ws.range(range.EntireColumn.Address).NumberFormat = "0")。它没有用。
- 像往常一样加载整个 block ，然后通过重新分配整行来强制 Excel 重新加载具有非文本数据类型的列:

With ws.range(ws.Cells(4, 11), ws.Cells(ws.UsedRange.rows.Count, 11))
    .NumberFormat = "dd/mm/yyyy"
    .Value = .Value
End With

但是当我需要重新格式化大约 7000 行左右的文档上的 30 列时，这个开始变得很慢。

- 最后，我尝试修改函数，以便它创建一个 object[,] 数组，其中的元素具有正确的数据类型(datetime、int 等)。同样，excel 在解压通用对象时速度非常慢。

我唯一的猜测是我可以按数据类型加载 block ，但这与我当前的方法非常困惑。

有没有其他选择可以提高速度或减少转换？

提前致谢。

编辑:

澄清一下:excel 充当重客户端，允许多个用户(每个用户都有自己的 excel)读取和更新远程数据库。最大的问题实际上是处理同时写入等的表锁。现在的问题是数据量变得如此之大，以至于我需要做一些事情来加快导入和导出时间。我已经使导出时间可以忍受(大多数时候用户不会将那么多数据上传到数据库)，但是导入时间很慢，主要瓶颈是写入电子表格。

此外，在我进来之前，用户已经习惯了他们在另一家公司使用的类似工具，因此他们非常坚持保持相同的格式。该应用程序向 Web 服务发送请求，从该服务下载包含信息的 CSV。然后将该 CSV 作为数据源加载到 Excel 中。这种方法对我无效，因为我没有对数据库所在机器的管理访问权限，也没有任何其他我可以用作 WS 桥梁的机器。我只有数据库本身的管理权限。我想我可以从 dll 中的查询数据构建 CSV，然后使用与它们相同的方法。

Answer 1

如果你想创建格式化的 Excel 数据文件 在您的 .NET 代码中，这是可以执行此操作的流行库列表:

http://closedxml.codeplex.com/ (使用方便)

http://freenetexcel.codeplex.com/

http://epplus.codeplex.com/

http://npoi.codeplex.com/

和文章 Create Excel (.XLS and .XLSX) file from C#对各种选项进行了更长的讨论

如果你想 从 Excel VBA 更新远程数据库 代码然后文章 VBA code to loop and update MS access database column from Excel这个谷歌查询:“site:stackoverflow.com excel vba update database”

如果你想 隐形打开 Excel 数据文件 由您的 .NET 后端创建，用于进一步处理/合并然后文章 Open Excel file for reading with VBA without display有一些提示如何做到这一点

如果你想 从更多人那里获得更好的答案然后尝试将问题明确缩小为 1 个明确的问题 ( https://stackoverflow.com/help/how-to-ask)，并以 Minimal, Complete, and Verifiable example 的形式提供一些代码