Web server crashes for some XLSX exports.

[will.marriott]

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;
        }


[Gary.zhang]

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

[will.marriott]

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;
        }


[Gary.zhang]

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

[Gary.zhang]

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

[Gary.zhang]

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

[will.marriott]

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

[Gary.zhang]

Hi Will,

Thanks for your feedback.

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

Sincerely,
Gary
E-iceblue support team

[will.marriott]

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.

[Gary.zhang]

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

[Gary.zhang]

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

[will.marriott]

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.

[Gary.zhang]

Hi,

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