Hello Team,
I am using AutoFitMergedCellsType.EachLine for both columns and rows however it is still not working. Please find below snippet of code:
worksheet.AutoFitRows(new AutoFitterOptions
{
AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine,
AutoFitWrappedTextType = AutoFitWrappedTextType.Paragraph,
MaxRowHeight = int.MaxValue,
OnlyAuto = true
});
worksheet.AutoFitColumns(new AutoFitterOptions
{
AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine,
AutoFitWrappedTextType = AutoFitWrappedTextType.Paragraph,
MaxRowHeight = int.MaxValue,
OnlyAuto = true
});
image.png (6.9 KB)
Can you please help here?
@PramodHegde,
Could you please share your complete sample code(runnable) or prefrably a sample console VS.NET application with resource (Excel file(s)) to reproduce the issue on our end, we will check it soon.
PS. please zip the project and file(s) prior attaching here.
Hi Amjad - It is very difficult to make out a console app of this project. This issue is coming whenever we do merge excel sheets. Post Merge, AutoFitRows and AutoFitColumns code do exist which I mentioned previously. Here is the code snippet of merge:
void IExcelMerger.Merge(IList workbooks, string sheetName)
{
if (!_workBook.Worksheets.Any(s => s.Name == sheetName))
{
_workBook.Worksheets.Add(sheetName);
}
int totalRowCount = 0;
foreach (var workBook in workbooks)
{
var namedRanges = workBook.Worksheets.GetNamedRanges();
foreach (var workSheet in workBook.Worksheets?.Where(w => w.VisibilityType == VisibilityType.Visible))
{
Range sourceRange = workSheet.Cells.MaxDisplayRange;
Range destinationRange = _workBook.Worksheets[sheetName].Cells.CreateRange(sourceRange.FirstRow + totalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);
destinationRange.Copy(sourceRange, new PasteOptions
{
PasteType = PasteType.All
});
if (namedRanges != null)
{
foreach (var range in namedRanges)
{
_workBook.Worksheets[sheetName].Cells.CreateRange(sourceRange.FirstRow + totalRowCount + range.FirstRow, sourceRange.FirstColumn + range.FirstColumn, range.RowCount, range.ColumnCount).Name = range.Name;
}
}
destinationRange.CopyStyle(sourceRange);
totalRowCount = sourceRange.RowCount + totalRowCount + 1;
}
}
}
@PramodHegde,
We are afraid that without a template file and sample runnable project, that can be compiled and executed here without any missing reference, we might not reproduce this issue here. Hence, cannot help you much. You may please create a separate application which can be used to analyze this issue here.
@Amjad - Can you share any sample console app for which it is working, post merging sheets with below conditions?
- Should have merge columns
- Cell has more than 8 digit number.
If you can share any such samples then it would be of great help.
@PramodHegde,
We will be looking into it and test auto-fit row/col operation regarding merged cells. We will get back to you with more details.
@PramodHegde,
We evaluated your issue in details. We tested similar scenario a bit by using some test cases. We found your mentioned issue regarding auto-fit rows/cols operation for merged cells. We have logged a ticket with an id “CELLSNET-50157” for your issue. We will look into it soon.
Once we figure it out, we will update you with a fixed version, so you could try it for your scenario/case on your end.
@Amjad_Sahi: Please find the attached console app with Input and Output files. Please use files which are in folder Files and generate merge version. If you look at file MergeOutput.xlsx at row number 316, number is displaying as #####, till I manually adjust the column width.
Kindly look at the issue and provide the fix as we are blocked.
image.png (5.5 KB)
MergeSheetsFormat.zip (6.1 MB)
@PramodHegde,
Thanks for the sample project and resource files.
I have logged it with your existing ticket “CELLSNET-50157” into our database. We will analyze your issue and try to figure it out soon.
@Amjad_Sahi: It says issue is resolved. What is the fix? How can I check ticket details?
CELLSNET-50157 ---- Status : Resolved
@PramodHegde,
We logged a ticket for the issue using our own test cases and fixed it. Now we will evaluate your test case with the fix and if it goes well, we will share the fix after performing other QA and incorporating other enhancements.
Thanks @Amjad_Sahi - Looking forward to see the fix.
@PramodHegde,
Sure, we will keep you posted on the fix.
@simon.zhao: I have replaced the new dll and added reference, however getting below exception. Please check.
System.IO.FileNotFoundException
HResult=0x80070002
Message=Could not load file or assembly ‘System.Drawing.Common, Version=4.0.2.3, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’. The system cannot find the file specified.
Source=Aspose.Cells
StackTrace:
at Aspose.Cells.WorkbookSettings…ctor(Workbook )
at Aspose.Cells.Workbook…ctor(FileFormatType fileFormatType)
at Aspose.Cells.Workbook…ctor()
at MergeSheetsFormat.Program.Main(String[] args) in C:\repos\MergeSheetsFormat\MergeSheetsFormat\Program.cs:line 17
@PramodHegde,
Please note, when using latest .NET Standard2.0 version/fix, some dependencies are required to work with v21.12.x. You may please add System.Drawing.Common library manually from the NuGet package manager to resolve the issue. Moreover, in some cases you may also require System.Text.Encoding API. You can get this information for .NET Standard by clicking on “Dependencies” node at the following link. See the details below:
@Amjad_Sahi i have used the same NuGet package which you have mentioned, now some different exception is coming. The exception is below-
System.ArgumentException
HResult=0x80070057
Message=‘IBM437’ is not a supported encoding name. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method. (Parameter ‘name’)
Source=System.Private.CoreLib
StackTrace:
at System.Text.EncodingTable.InternalGetCodePageFromName(String name)
at System.Text.EncodingTable.GetCodePageFromName(String name)
at System.Text.Encoding.GetEncoding(String name)
at …ctor()
at .(Stream )
at .(Stream )
at .Stream )
at .(String , Stream , LoadOptions )
at Aspose.Cells.Workbook.(String, LoadOptions )
at Aspose.Cells.Workbook…ctor(String file)
at MergeSheetsFormat.Program.Main(String[] args) in
\Program.cs:line 22
@PramodHegde,
I did not ask you to use Aspose.Cells.Dll from Nuget package (link). I only told you to use System.Drawing.Common version should be >= 4.7.0 and System.Text.Encoding.CodePages version should be >= 4.7.0. In short, please try using the fix (@simon.zhao provided in his post here and import the above mentioned two libraries (System.Drawing.Common and System.Text.Encoding.CodePages)) into your project. Alternatively, you may remove existing Aspose.Cells references to your existing project and then import v21.12 from nuget repos. Now remove Aspose.Cells.Dll from the project and add reference to Aspose.Cells21.12.6 For .NetStandard20.
If you still find any issue, kindly create a separate (standalone) VS.NET project using Aspose.Cells21.12.6 For .NetStandard20 (fix) and the two libraries (System.Drawing.Common and System.Text.Encoding.CodePages) we mentioned above, zip the project and upload to some file sharing service (dropbox, Google drive, etc.). We will check it soon.
@Amjad_Sahi
With your fix its working, now what is happening all columns are of some fixed size which is also there in your Aspose.Cells 21.12.0.
@PramodHegde,
Could you elaborate on it and provide more details and samples if you think it is an issue.