Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
631 views
in Technique[技术] by (71.8m points)

c# - Microsoft ACE OLEDB connection creating empty Excel when there are 166,110 rows

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'"
      ))  

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.

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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:WindowsSysWOW64configsystemprofileAppDataLocalMicrosoftWindowsTemporary Internet FilesContent.MSO

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.

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:WindowsSysWOW64configsystemprofileAppDataLocalMicrosoftWindowsTemporary Internet Files" and then full access to the Content.MSO folder itself.

If the provider matches the bitness of your system then I suspect you need to perform the process above for C:WindowsSystem32configsystemprofileAppDataLocalMicrosoftWindowsTemporary Internet FilesContent.MSO instead, although I have never tested this.

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...