none
Excel File Empty When Code Deployed to Server RRS feed

  • Question

  • Hi

    An odd one. I have a C# web application (using .NET 4.7.1) which has a feature to download the data into an Excel spreadsheet. There is a template with all the headers in place, the code creates a temporary file, copies the contents from the template to that file and then populates it using ADO.

    This process works without issue when deployed to the testing servers, and if I modify the web.config to point to the 'problem' connection, I get the download as expected (file size is ~5MB), but as soon as I deploy the code to the server, the resulting file is just an empty template. For the resulting download, none of the sheets (there are 12 of them) exceed 80 columns and all but one contain less than 4,000 rows, with that one being ~7,500 rows.

    There is a similar process to extract filtered data, and this works without issue (using the same code) though this file is smaller (same number of sheets/columns as above but less rows - file size is ~4.7MB). 

    I know it's not database permissions as have had traces applied and all the queries work and return data, so it's something about the resulting file and the web server.

    Are there any limitations to ACE/OLEDB? The connection string used is:

    $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={exportFile};Extended Properties='Excel 12.0 xml;HDR=Yes'"

    Where {exportFile} is a copy of the template held on the server whilst being populated.

    The fact it works locally and on the test servers, plus for the smaller download, to me, proves that the code is sound, but there is just something about the larger export but I have no idea what it could be.

    Are there any file size limitations using ACE/OLEDB?

    Any help or advice gratefully received

    Thanks

    Martin

    Friday, January 31, 2020 9:36 AM

Answers

  • Thanks all

    We eventually found the issue after lots of time spent with Fiddler and similar, and a post we came across online based on those findings and it turned out that it was permissions to one of the system folders used when creating the temporary file and caching the results.

    Thanks again

    Martin

    Friday, February 14, 2020 9:00 AM

All replies

  • You mentioned test servers vs server. If you're targeting IIS then IIS is probably running as x64 which means you'd need the version of the provider that is x64. However you should enable logging in your app to determine where the problem is. We have no way of knowing what your code is doing and the fact that it isn't doing anything seems to indicate you have an exception that is silently being eaten. 

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, January 31, 2020 3:15 PM
    Moderator
  • We have been running traces on the process, and oddly, when re-creating the site on a test server, producing the same file fails to provide any data again. We initially thought that it was insufficient resources but increasing the resources made no difference in the results.

    Looking at the traces, the only difference between the working one and the non-working one is that the data isn't being written to the file (the lines are just not present in the trace for the larger export).

    I'll have to keep investigating, as I said, I know it works as the same code produces results for the smaller dataset, but fails to do so for the larger one.

    Thanks

    Martin

    Friday, January 31, 2020 4:12 PM
  • Hi Martin.Swanston,
    You can try to convert xls to xlsx.
    Here is a code example you can refer to.

    static void Main(string[] args)
            {
                string fileName = @"C:\Users\Desktop\test.xlsx";
                ReadExcelFileSAX(fileName);
            }
            static void ReadExcelFileSAX(string fileName)
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                    OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                    string text;
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(CellValue))
                        {
                            text = reader.GetText();
                            Console.Write(text + " ");
                        }
                    }
                    Console.WriteLine();
                    Console.ReadKey();
                }
            }

    More details you can refer to this link.
    Hope it is helpful for you.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 13, 2020 6:14 AM
  • Have you considered watching via Fiddler? This may expose problems not seen using tracing.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 13, 2020 12:32 PM
    Moderator
  • Thanks all

    We eventually found the issue after lots of time spent with Fiddler and similar, and a post we came across online based on those findings and it turned out that it was permissions to one of the system folders used when creating the temporary file and caching the results.

    Thanks again

    Martin

    Friday, February 14, 2020 9:00 AM