Free Support Forum - aspose.com

Aspose.Cell.MaxColumn issue

Hi All,


I am facing few issues with Aspose.Cells

1. I have an excel sheet with first row last column cell as spanned in two cells and in rows below the first row the last cells are blank.When I use Aspose.Cells.MaxDataColumn,the last column is not counted.
and when I use Aspose.Cells.MaxColumn ,the last column is counted

2. If I use MaxColumns I am having issues in some other excel content.Some unnecessary blank columns are counted.

Now what I need is the column count considering merged cell for Aspose Worksheet.

Please help me out


Hi,


Well, you need to understand the MaxDataColumn and MaxColumn properties what they will return to you. The MaxColumn would always include the blank formatted cells where as MaxDataColumn would not include them. So, you have to use these properties accordingly for your requirements. Both properties i.e. Cells.MaxDataColumn / Cells.MaxDataRow and Cells.MaxColumn/Cells.MaxRow attributes to get the farthest column and row indexes. So, you should change your sample code accordingly.

For complete reference for using these attributes and others in that series, please check the thread: http://www.aspose.com/community/forums/178764/column-and-row-count-invalid/showthread.aspx#178764

If you still have any confusion, let us know.

Hi,

MaxColumn/MaxRow will always be greater or equal to MaxDataColumn/MaxDataRow and MaxDataColum/MaxDataRow gives you farthest index having data.

It means, MaxDataColumn/MaxDataRow will not count the blank columns/rows coming after the last cell having data.

Hi All,


Thanks for ur response.To discuss the issue in a better way I am uploading two excel files .Book1.xlsx and Book2.xlsx.
1. I need the merged cell to be counted for Book1.xlsx i,e A,B,C,D should be counted
and
2. I need the last 3 blank columns in Book2.xlsx to be not counted .i.e E,F,G should not be counted
while calculating maxcolumncount

Now please tell me which will be the property which will work for both the scenario

Hi,


Well, there is no better way to do it with a single property according to your needs, but you may try to manually adjust by using your own code. See the sample code for your reference. I am using MaxDataColumn for both your files.
e.g

Sample code:

Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook(“e:\test2\asposecellsmax\Book1.xlsx”);
Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];
Aspose.Cells.Cells cells = worksheet.Cells;
int columnindex = cells.MaxDataColumn;

//Check the last cell is merged, then add to maxdatacolumn count.
if (cells[0, columnindex + 1].IsMerged)
{
columnindex++;
}

MessageBox.Show(columnindex.ToString());


Thank you.

Hi Amjad,


Thanks for the reply.But it may be a case that any of the column in any of the row can have merged cells and and the merge can be 2-n cells. How we can modify the code for such scenario

Thanks
Partha

Hi,

You do not need to worry about the merged cells that lie between maximum column and maximum row. Aspose.Cells will take care of them. The only special case is that if last cell itself is merged and you will have to use a special check as have been given in above post.

Hi,


I am afraid, as I said earlier there seems no better way to accomplish this, we cannot set any defined logic and we have to iterate each cell in the columns next to index returned by MaxDataColumn attribute to check if the cells are merged or not.

I think for some cases, you may try to delete the blank columns first before using MaxColumn attribute, it may work fine.
e.g

Sample code:

Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook(“e:\test2\asposecellsmax\Book2.xlsx”);
Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];
Aspose.Cells.Cells cells = worksheet.Cells;
//Delete the blank columns in the sheet first.
cells.DeleteBlankColumns();
int columnindex = cells.MaxColumn;
MessageBox.Show(columnindex.ToString()); //3 - OK

Thanks guys


DeleteBlankColumns() method cannot be used in my case,as this function deletes the merged blank cells also,which I do not want.
based on your feedback I am working on the code for handling the scenario ,once it is ready I will post it so that the code can be validated by you experts


Cheers
Partha

Below is the function I have written for getting correct column count as per my requirement .Please let me know if any issue is there in the code


private static int GetWorkBookMaxColumn(Aspose.Cells.Workbook asposeWorkbook)
{
int initRowCount = asposeWorkbook.Worksheets[0].Cells.MaxDataRow + 1;
int initColCount = asposeWorkbook.Worksheets[0].Cells.MaxColumn ;
int maxColCount = asposeWorkbook.Worksheets[0].Cells.MaxDataColumn;
for (int x = 0; x < initRowCount; x++)
{
for (int y = 0; y < (initColCount - maxColCount); y++)
{
if (asposeWorkbook.Worksheets[0].Cells[x,maxColCount+y+1].IsMerged)
{
maxColCount++;
}

}

}
return maxColCount+1;
}


Thanks
Partha

Hi Partha,


The code looks OK and it might fits your needs accordingly.

Thank you.