Set borders to a large range

[VincentB]

Hello,

I want to create large list (over 5,000 lines).
I don't have any problems to write values but i have error when i set borders :
"Maximum number of extended formats exceeded."

I do something like that

Code: Select all

for (int i = 1; i <= 5000; i++)
{
    // Assigning values
    var range = sheet.Range["A" + i];
    range.Text = "Account" + i;

    //Customize borders
    range.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
    range.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.DashDot;
    range.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Dotted;
    range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Hair;
}


I have got same problem if i set border out of the loop like that.

Code: Select all

sheet.AllocatedRange.Borders.LineStyle = LineStyleType.Thin;
sheet.AllocatedRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.AllocatedRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;


I looked in the .xlsx file (xl/styles.xml) and I noticed that the tag "cellXfs" contains too many line.
It's maybe a idea.

Do you have a workaround to set borders at a large range ?
To set a style of range to lot of ranges ?

Thanks for your help.

[Betsy.jiang]

Dear VincentB,

Thanks for your inquiry.
I have tested two scenarioes you mentioned with the latest Spire.XLS Pack Hotfix Version:7.10.79, but didn't encounter any issue. Please try to use this version, if the issue still exists, please provide us entire code you were using for testing.
Here is my testing code.

Code: Select all

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            //Test 10000 times
            for (int i = 1; i <= 10000; i++)
            {
                // Assigning values
                var range = sheet.Range["A" + i];
                range.Text = "Account" + i;

                //Scenario 1
                range.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
                range.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.DashDot;
                range.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Dotted;
                range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Hair;
            }

            //Scenario 2
            //sheet.AllocatedRange.Borders.LineStyle = LineStyleType.Thin;
            //sheet.AllocatedRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            //sheet.AllocatedRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;

            workbook.SaveToFile("8703.xlsx",ExcelVersion.Version2010);

Sincerely,
Betsy
E-iceblue support team

[VincentB]

The difference between our test come from loading the file.
Indeed, in file creation there is no problem. I reproduced the problem when i load file and update.

Code: Select all

var workbook = new Workbook();
workbook.LoadFromFile("EmptyFile.xlsx"); // Work on an empty file for the test
var sheet = workbook.Worksheets[0];

for (int i = 1; i <= 5000; i++)
{
    var range = sheet.Range["A" + i];
    range.Text = "Account" + i;

    range.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
    range.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.DashDot;
    range.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Dotted;
    range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Hair;
}

workbook.Save();


I work with Excel 2016.

Thanks for your help.

[Betsy.jiang]

Dear VincentB,

Thanks for your reply.
I have created a sample file and tested the code, it will throw an exception when save the file, I have posted it to our Dev team.
For the issue you mentioned "Maximum number of extended formats exceeded", the better to provide us the sample document you were using, thanks.

Sincerely,
Betsy
E-iceblue support team

[VincentB]

Of course,

sample.rar contains a list that I want to update with recent data and format (border, color, font ...).

I can't have reproduced the error "Maximum number of extended formats exceeded" by POC...
I just reproduced the Save() error.

If y need something else please.
Thanks.

[Betsy.jiang]

Dear VincentB,

Thanks for your information.
I have reproduce Save() error, and posted it to our Dev team. We will inform you once there is any progress.
Sorry for inconvenience.

Sincerely,
Betsy
E-iceblue support team

[Betsy.jiang]

Dear VincentB,

Thanks for your waiting.
Now the issue has been resolved in Spire.XLS Pack Hotfix Version:7.11.13, welcome to test it.
If there is any qeustion, welcome to get it back to us.

Sincerely,
Betsy
E-iceblue support team