Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Dec 17, 2013 11:59 pm

Hi. Sometimes when I export a worksheet to XLSX the web server crashes. I believe it's crashing when I set the version to be excel 2010, and not on the actual export to stream. Exporting to XLS works! From the investigation I've done I can't blame specific data. I can export the same data on my local machine but on a server it fails, and after a few fails it crashes the app pool. Below is my code. I'm using the latest version 7.1.26.

Code: Select all
        private Workbook AsWorkbook(PhocasMaster master, Database database, bool monochrome)
        {
            SpireLicense();

            List<string> formatting;
            var dataTable = AsDataTable(master, database, out formatting);
            if (dataTable.Columns["Code"] != null)
                dataTable.Columns["Code"].ColumnName = database.Synonyms["Code"];

            var workbook = new Workbook();
            var worksheet = workbook.Worksheets[0];
            worksheet.InsertDataTable(dataTable, true, 1, 1, true);

            var headerRows = 1;

            // add total row if required
            if (!IsCompany() && !IsTransaction() && View.Mode != "Search")
            {
                var totalData = AsTotalData(master, database);

                headerRows++;
                worksheet.InsertRow(headerRows);
                worksheet.InsertDataTable(totalData, false, headerRows, 1, true);
            }

            // add pareto row if required
            if (!IsCompany() && !IsTransaction() && View.Mode != "Search" && View.ShowPareto)
            {
                var paretoData = AsParetoData(master, database);

                headerRows++;
                worksheet.InsertRow(headerRows);
                worksheet.InsertDataTable(paretoData, false, headerRows, 1, true);
            }

            // bold header rows
            for (var i = 0; i < headerRows; i++)
                worksheet.Rows[i].Style.Font.IsBold = true;

            // number format
            CellRange formatRange;
            if (String.IsNullOrEmpty(View.Dimension))
                formatRange = worksheet.Range[2, 1, worksheet.LastRow, worksheet.LastColumn];
            else
                formatRange = worksheet.Range[2, View.PropertyLists.Single(p => p.Dimension == View.Dimension).Properties.Count + 1, worksheet.LastRow, worksheet.LastColumn];
            formatRange.NumberFormat = "0.00";

            if (!monochrome)
            {
                // Conditional Formatting
                CellRange range = worksheet.Range[headerRows + 1, 1, worksheet.LastRow, worksheet.LastColumn];

                // Negative Values
                var negCondFormat = range.ConditionalFormats.AddCondition();
                negCondFormat.FormatType = ConditionalFormatType.CellValue;
                negCondFormat.FirstFormula = "0";
                negCondFormat.Operator = ComparisonOperatorType.Less;
                negCondFormat.FontColor = System.Drawing.Color.Red;

                // Zero Values
                var zeroCondFormat = range.ConditionalFormats.AddCondition();
                zeroCondFormat.FormatType = ConditionalFormatType.CellValue;
                zeroCondFormat.FirstFormula = "0";
                zeroCondFormat.Operator = ComparisonOperatorType.Equal;
                zeroCondFormat.BackColor = System.Drawing.ColorTranslator.FromHtml("#ccffcc");

                workbook.CalculateAllValue();
            }

            for (int i = 0; i < worksheet.Columns.Count(); i++)
            {
                // Columns not 0-indexed
                worksheet.AutoFitColumn(i + 1);
            }

            return workbook;
        }

        public MemoryStream AsXLSX(PhocasMaster master, Database database, bool monochrome)
        {
            var memoryStream = new MemoryStream();
            var workbook = AsWorkbook(master, database, monochrome);
            workbook.Version = ExcelVersion.Version2010;
            workbook.SaveToStream(memoryStream);
            memoryStream.Seek(0, SeekOrigin.Begin);
            return memoryStream;
        }

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Wed Dec 18, 2013 6:13 am

Hello Will,

Thanks for your inquiry.
Is there any exception information about the crash? Could you please provide us the log file? How much is your data? Please try the following code in your web server and let us know the result. And please make sure that you have sufficient amount of memory reserved for the process.
Code: Select all
public MemoryStream AsXLSX(PhocasMaster master, Database database, bool monochrome)
        {
            var memoryStream = new MemoryStream();
            var workbook = AsWorkbook(master, database, monochrome);
            workbook.Version = ExcelVersion.Version2010;
            workbook.SaveToHttpResponse(...);//save it as file
           // workbook.SaveToStream(memoryStream);
            //memoryStream.Seek(0, SeekOrigin.Begin);
            return memoryStream;
        }

Thanks in advance,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Dec 20, 2013 12:31 am

Unfortunately there is no useful information about the crash in either the event logs or IIS logs. It appears that it kills the process and quickly. It is not failing on the save but on setting the version. The following code (with the save commented out) still causes a crash on the server. As I said before, it works for all other exports except XLSX, and the amount of data is quite small.

Code: Select all
        public MemoryStream AsXLSX(PhocasMaster master, Database database, bool monochrome)
        {
            var memoryStream = new MemoryStream();
            var workbook = AsWorkbook(master, database, monochrome);
            workbook.Version = ExcelVersion.Version2010;
            //workbook.SaveToStream(memoryStream);
            //memoryStream.Seek(0, SeekOrigin.Begin);
            return memoryStream;
        }

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Fri Dec 20, 2013 7:47 am

Hello,

Thanks for your feedback.
What is your server environment? Could you please try to use try..catch block to catch the exception of the crash?
Thanks in advance?
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Thu Dec 26, 2013 9:01 am

Hello Will,

Sorry that we didn't always reproduce the issue you experienced. What is your server environment? Could you please try to use try..catch block to catch the exception of the crash?
Thanks in advance,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Wed Jan 08, 2014 9:22 am

Hello Will,

Any update about the issue? Could you please give us some feedback if convenience? If the issue still occurs, Please provide us the below detailed information, it may help us to reproduce your issue quickly and offer the solution to you.

1. Dll file list (name + version), for example:

Spire.Doc.dll v4.9.32.3040
Spire.License.dll v4.9.32.3040
Spire.Pdf.dll v2.8.86.3040
2. Operation System, for example:

Windows 7 Enterprise Edition SP1 x64
3. Project can reproduce the issue

All code about this problem, a simple project which can reproduce this problem will be better.

4. Test Document or Test data

Please provide your test document to us if convenient, it may help us reproduce your issue quickly. We promise to keep your document confidential and we will not use it for any other purpose. Besides, you could also remove the security data of your document and then send it to us.

Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jan 20, 2014 10:25 pm

Hi Gary,

I've updated Spire.XLS and that has seemed to make it more stable. If the issue continues I'll dig deep and provide all the information I can.

Will

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Tue Jan 21, 2014 1:28 am

Hi Will,

Thanks for your feedback.

If there are any questions, welcome to get it back to us.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Feb 25, 2014 10:42 pm

This issue is happening again. We can replicate it on our build server but I can't replicate it locally even with the same data.
It fails with the below data in a DataTable on the server (7 columns - 2 string 5 double).
USPA_177-002 Au Pair Network International 164737.63 98241.73 90751 66495.9 1.81527068572247

It appears to be crashing on this line.
Code: Select all
workbook.Version = ExcelVersion.Version2010;

Sometimes it will crash the whole application pool, other times it only crashes the thread. If you have any ideas of how I can capture more debug information for you please let me know.

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Wed Feb 26, 2014 6:13 am

Hello Will,

Thanks for your inquiry.
What 's the culture for your build server? And there are some solutions as below you could try.
Solution 1. Don't specify its version, delete the version setting.
Solution 2. Specify the version when create the workbook in your method(private Workbook AsWorkbook(PhocasMaster master, Database database, bool monochrome)).
Code: Select all
var workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;
var worksheet = workbook.Worksheets[0];


Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Mar 03, 2014 9:32 am

Hello Will,

Have you tried the solutions? Is the issue happened again? Could you please give us some feedback if convenience?

If there are any questions, welcome to get it back to us.

Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Mar 17, 2014 2:16 am

Hi, sorry I haven't replied earlier. We are currently in the middle of planning a new release so when we are developing we will do some more testing on this issue. I'll get back to you. :)

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Mon Mar 17, 2014 7:56 am

Hi,

Thanks for the reply.
If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Return to Spire.XLS