AutoFitWidth not working when reading an Xml Spreadsheet file

Hello,

Our application is using Aspose.Cells for converting data from XML Spreadsheet format to XLSX format but when testing the latest version of Aspose.Cells we discovered that it is no longer possible to read files containing columns with AutoFitWidth set.

The following exception is raised:

System.ArgumentException occurred
HResult=-2147024809
Message=Invalid column index.
Source=Aspose.Cells
StackTrace:
at . . (Int32 )
InnerException:

During testing I found that the error was introduced between versions 7.3.2.0 and 7.3.4.0. See attached file for example on a file that can no longer be read.

thanks,
Johnny Andersen.

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version Aspose.Cells
for .NET v7.4.0.5
and see if it resolves your issue.

If your problem still occurs, then please provide us your simple sample runnable project and source files replicating this issue with the latest version.

We will look into your issue and help you asap

Hello,

I tried to use the fixed version you provided but it did not solve the issue.

To reproduce the issue I have compiled a simple simple console application with the code at the end of this post. Run the executable by using the file attached in the first post of this issue as command line parameter.

The program will raise the same exception as before when running version 7.2.4 and upwards, but works fine with older versions of Aspose.Cells.

thanks, Johnny Andersen

namespace AsposeCellsConvert
{
    using System.Diagnostics;
    using System.IO;
<span style="color: blue;">using</span> Aspose.Cells;

<span style="color: blue;">class</span> <span style="color: rgb(43, 145, 175);">Program</span>
{
    <span style="color: blue;">static</span> <span style="color: blue;">void</span> Main(<span style="color: blue;">string</span>[] args)
    {
        <span style="color: blue;">if</span> (args.Length < 1)
        {
            <span style="color: rgb(43, 145, 175);">Trace</span>.WriteLine(<span style="color: rgb(163, 21, 21);">"Invalid number of arguments: Expecting AsposeCellsConvert.exe <source>"</span>);
            <span style="color: blue;">return</span>;
        }

        <span style="color: blue;">string</span> l_sourceFile = args[0];

        <span style="color: blue;">if</span> (!<span style="color: rgb(43, 145, 175);">File</span>.Exists(l_sourceFile))
        {
            <span style="color: rgb(43, 145, 175);">Trace</span>.WriteLine(<span style="color: rgb(163, 21, 21);">"Specified source file does not exist: "</span> + l_sourceFile);
            <span style="color: blue;">return</span>;
        }

        <span style="color: blue;">string</span> l_targetFile = l_sourceFile + <span style="color: rgb(163, 21, 21);">".xlsx"</span>;

        Aspose.Cells.<span style="color: rgb(43, 145, 175);">Workbook</span> l_workbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>(l_sourceFile);

        l_workbook.Save(l_targetFile, <span style="color: rgb(43, 145, 175);">FileFormatType</span>.Xlsx);
    }
}

}

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to replicate this issue using the latest version with the following code.

We have logged this issue in our database. We will look into this issue and fix it. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41433.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\AutoFitWidth+Error.xml”;


Aspose.Cells.Workbook l_workbook = new Workbook(filePath);


Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issues.

Please download and try this fix: Aspose.Cells for .NET v7.4.1.1 and let us know your feedback.

Hello,

our project is targeting the .NET Client Profile, so I would require a version of Aspose.Cells.dll for this. Could you send me a link to the correct version?

thanks,

Johnny Andersen

Hi,


Alright, we will provide you the .NET Client profile version of the fixed version in the next week early (e.g on next Monday or Tuesday).

Stay in touch.

Thank you.

Hello,

would it be possible to get a fix out for me faster? My application is in the late release stage and is scheduled for release to customers monday..

thanks, Johnny Andersen

Hi,


I am afraid, our concerned developer is off duty now and tomorrow starts the week end, so we can at most provide you the fix (for .NET Client profile framework) on Monday or so.

Thanks for your understanding!

Hi,


Please find attached the .NET client profile version of the latest fix v7.4.1.1 for your needs.

Thank you.

Hello,

I have installed and tested the fix. Aspose.Cells does not crash anymore when AutoFitWidth is set, but the columns are not actually sized when the file is opened in Excel. So the proposed solution still does not restore functionality as it was in version 7.3.2.0.

thanks,

Johnny Andersen

Hi,


Please elaborate your issue more and give us your template files (input + output) and paste your sample runnable code (or console application (you may zip it prior attaching here)) to reproduce the issue on our end. We will check your issue soon.

By the way, you should also give it a try in MS Excel manually for your Auto-fit row/column process and check if it works fine in MS Excel. For your information, Aspose.Cells does work the same way as MS Excel does, so if MS Excel cannot auto-extend the width properly for some of your merged areas, Aspose.Cells might not work well too.

Thanks for your understanding!

Hello,

I have tested the fix withy the simple code written earlier in this issue (https://forum.aspose.com/t/97638)

The testdata is also the same, but I attach a file that illustrates the problem further. In Cell A1 there is a text that is a bit longer than the other cells - Column A is not expanded to fit this text (See attached output file for result).

thanks,

Johnny Andersen

Hi,


Well, if you could open your file “AutoFitWidth+Error.xml” into MS Excel, you will clearly see that the cell A1 does not show the contents fully either, see the screen shot attached.

For your needs, you have to apply Auto-fit column’ s width operation before saving to XLSX file format, it works fine. See the sample code below:

Sample code:

Workbook wbk = new Workbook(“e:\test2\AutoFitWidth+Error.xml”);
//Apply the Auto-fit feature.
wbk.Worksheets[0].AutoFitColumn(0);
wbk.Save(“e:\test2\out_AutoFitWidth+Error.xlsx”, SaveFormat.Xlsx);

Thank you.


Hello,

the reason why I reported this issue is that our application is converting data from XML Spreadsheet format to Excel format (xlsx) without knowing the structure of the file. We do not know whether a column is supposed to be fixed or autofit at the time of the conversion.

I understand that I can call AutoFitWidth for a column in a spreadsheet but as I do not know which columns should be autofit we have relied on Aspose.Cells to retain the setting in the XML Spreadsheet file when writing the XLSX Format.

This worked perfectly fine until it was changed from version 7.3.2.0 to 7.3.4.0. If you could please run the test case with Aspose 7.3.2.0 you would notice that the first column in the resulting file would adjust its width to the contents when opened in Excel. In newer versions of Aspose.Cells (that is from 7.3.4.0 onwards) The AutoFit information seems to be lost when writing XLSX.

We would very much like to get this functionality back as the AutoFit functionality is an importent feature when formatting documents to our end-users.

thanks,

johnny Andersen

Hi,


Thanks for further details.

I think you are right. When I used v7.3.2 to read your template XML (SpreadsheetML) file and saved it to XLSX file format, the cell A1 shows the contents fully but version v7.4.1.x does not show the contents completely unless we manually Auto-fit the column in the code.

Here is the simplest code that I used with v7.3.2.

Sample code:

Workbook wbk = new Workbook(“e:\test2\AutoFitWidth+Error.xml”);
wbk.Save(“e:\test2\out1_AutoFitWidth+Error.xlsx”, SaveFormat.Xlsx);

For the sake of our investigation if it is an issue, expected behavior due to change, I have logged a ticket with an id “CELLSNET-41456” for your issue. We will look into your issue soon.

Thank you.

Hello,

and thanks for checking out the issue further. Please get in touch again if you need more information.

Johnny Andersen

Hi,


Please try the attached fix/version: Aspose.Cells for .NET v7.4.1.2, your issue should be fixed in it.

Thank you.

Hello,

could you also post a version for :NET 4 Client Profile?

thanks,

Johnny Andersen

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We will provide you a client profile version tomorrow or as soon as possible.