Microsoft ACE OLEDB连接在有166,110行时创建空的Excel [英] Microsoft ACE OLEDB connection creating empty Excel when there are 166,110 rows

问题描述

我正在用C#编程并使用oledbconnection.这是标准的连接字符串，例如

I am programming in C# and using an oledbconnection. This is the standard connection string e.g.

using (OleDbConnection conn = new OleDbConnection(
          "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
           saveFilenameAndLocation + 
          ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"
      ))  

当行数不多时，这可以成功工作并创建包含正确数据的Excel电子表格.但是，每当电子表格中的行数增加到较大的大小时(例如，当前在166,110行中失败)，它将生成一个空的电子表格，且工作表标签名称设置为A266FF2A662E84b639DA.

This works successfully and creates my Excel spreadsheet full of the correct data, when the number of rows isn't excessive. However, whenever the number of rows in the spreadsheet increases to a large size (e.g. currently it is failing on 166,110 rows), it produces an empty spreadsheet with the worksheet tab name set to A266FF2A662E84b639DA.

不可能重写它以便不使用OLEDB连接，有什么想法为什么当行大小增加时它不起作用?

It is not possible to rewrite this so that it doesn't use an OLEDB connection, any ideas why it doesn't work when the row size increases?

推荐答案

我不确定您的应用程序环境，但是从ASP.NET应用程序生成Excel文件时，我已经看到了这一点.

I am not sure about your application environment, but I have seen this when generating Excel files from an ASP.NET app.

一旦数据量超过一定大小(根据我的经验，此大小约为1 MB)，提供程序将在生成输出时尝试创建一个临时文件.我在64位系统上使用32位提供程序遇到了此问题.如果您使用此配置在服务帐户下运行，则创建这些文件的位置为

Once the amount of data exceeds a certain size (~1 MB in my experience), the provider will attempt to create a temp file while generating the output. I have come across this issue using the 32-bit provider on 64-bit systems. If you are running under a service account with this configuration then the location where these files are created is

C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO

但是，默认情况下，只有administrators和SYSTEM可以访问此位置，并且如果提供程序在非特权帐户下运行并且无法创建临时文件，则该位置将以静默方式失败，并仅通过A266FF2A662E84b639DA工作表返回其默认的空文件".

This location, however, is only accessible to administrators and SYSTEM by default, and if the provider is running under a non-privileged account and is unable to create the temp file it will fail silently and just return its default "empty file" with the A266FF2A662E84b639DA worksheet.

您需要做的是授予运行该应用程序的帐户(例如Network Service或IIS AppPool \)沿路径"C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files"的读取/执行/列出内容权限，然后对Content.MSO文件夹本身.

What you need to do is grant the account of the application that runs under (for example Network Service or IIS AppPool\) read/execute/list contents permissions along the path "C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files" and then full access to the Content.MSO folder itself.

如果提供程序匹配您系统的位数，那么我怀疑您需要对C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO执行上述过程，尽管我从未测试过.

If the provider matches the bitness of your system then I suspect you need to perform the process above for C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO instead, although I have never tested this.

我要感谢sysinternals提供的procmon可以帮助我解决此问题.

I want to extend my thanks to sysinternals for providing procmon that helped me troubleshoot this issue.

这篇关于Microsoft ACE OLEDB连接在有166,110行时创建空的Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！