Detecting Merged Cells

Aspose.Cells Product Family
1

Hi,

We are using the Aspose.Grid.Web component to manipulate Excel data. We then use the Aspose.Cells component to look at the data and generate alternate displays of the data. We have a worksheet that has some merged cells in it. It displays as merged on the Grid. However, when I look at the data programmatically in the Cells component, I can not detect the merged cells. I have looked at the Cell.IsMerged field and the Cells.MergedCells fields. The Cell.IsMerged is always false and the Cells.MergedCells collection has no elements in it. Can you please tell me how to detect merged cells programmatically?

Thanks!

2

Hi,

Thank you for considering Aspose.

Well, cells.MergedCells returns an ArrayList collection of all the Merged cells. I checked it and it works abs fine. Please see the following sample code which I used to get the merged cells in a worksheet,
Sample Code:-

Workbook workbook = new Workbook();

workbook.Open(@"c:\test.xls");

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = workbook.Worksheets[0].Cells;

int startCell =0;

int StartRow = 0;

int EndColumn = 0;

int EndRow = 0;

for (int i=0; i< cells.MergedCells.Count;i++)

{

CellArea ca = (CellArea)cells.MergedCells[i];

startCell = ca.StartColumn;

StartRow = ca.StartRow;

EndColumn = ca.EndColumn;

EndRow = ca.EndRow;

//or you can perform any operations with the cell area object as per you requirement

}

This problem may be occurring due to the use of some old version of Aspose.Cells, please try the attached latest version and if you still face any problem, please share your code and template file and we will look into it.

Thank You & Best Regards,

3

Hi,

Thanks for your reply. Our setup is a little different. We are using the Grid component to create the spreadsheet and saving it to a database. I have found a workaround that seems to work by checking the CellType and keying off of IsNull. Can you try that scenario and see if you can recreate the issue?

Also - We find that the cell formatting (e.g. horizontal align) is often lost when editing a spreadsheet in the grid. If I hit F5 to refresh, most of the time it comes back. Is this is known issue? Should I start another post for this issue?

Thanks,

Scott Butler

4

Hi Scott,

Our setup is a little different. We are using the Grid component to create the spreadsheet and saving it to a database. I have found a workaround that seems to work by checking the CellType and keying off of IsNull. Can you try that scenario and see if you can recreate the issue?

Do you use Aspose.Grid.Web for this task or use Aspose.Cells for .NET APIs. I am still not sure about the issue you are getting and which APIs of which component you are using. We need to check your code. Could you elaborate it more and create a sample test project to show it, we will check it soon.

Also - We find that the cell formatting (e.g. horizontal align) is often lost when editing a spreadsheet in the grid. If I hit F5 to refresh, most of the time it comes back. Is this is known issue? Should I start another post for this issue?

Well, no there is not any known issue regarding cell formattings. I have implemented it using Aspose.Grid.Web and could not reproduce any issue regarding horizontal alignment. Could you create a sample project, zip it and post it here to reproduce the issue, we will check it soon. Also, mention your environment, browser type, .NET framework, GridWeb version and other details etc. And I think it's better you create a new thread for this issue.

Thank you.


5

Hi,

Here is the setup for the CellMerge issue. We are using Aspose Web Grid for .net to enter spreadsheet type data. We save the data from the Web Grid to a database, not to a file. When we read in the data from the database to the Web Grid, the cell merge displays as expected. However, if we read that data into the Aspose Cells for .NET component, the cell merge data is not there. We are using the latest version of all your components, VS 2008 sp1 and .net framework 3.5 sp1.

If you cannot recreate this, I can create a sample project for you.

I will start a new thread for the other issue.

Thanks,

Scott

6

Hi,

Thanks for providing further details.

How do you read data into Aspose.Cells for .NET, do you import data from datatable to some worksheet using Cells.ImportDataTable() method of Aspose.Cells APIs? And do you use data-binding mode of the GridWeb? If you are directly importing data from datattable into the worksheet, you have to manually merge the cells for your requirements. We appreciate if you can create a sample test to show it more clearly.

Thanks for your work.

7

Hi,

I will try to get you a sample project later today or tomorrow. In the meantime, here is the code on how we use the components:

// read data from db into Web.Grid component using SQL data reader
byte[] bData = (byte[])row["ReferenceRangeDetails"];
MemoryStream ms = new MemoryStream(bData);
gwReferenceRange.WebWorksheets.ImportExcelFile(ms);

// save data to database from Web.Grid component
SqlParameter sp = new SqlParameter("@ReferenceRangeDetails", SqlDbType.Image);
e.Command.Parameters["@ReferenceRangeDetails"] = sp;

// get the reference range stream
MemoryStream ms = new MemoryStream();
gwReferenceRange.WebWorksheets.SaveToExcelFile(ms);
sp.Value = ms.GetBuffer();

// read data from database into the cells component for processing
System.IO.MemoryStream ms = new System.IO.MemoryStream((byte[])oDetails);
Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook();
workBook.LoadData(ms);

Note that we never touch Excel and the file is never written to disk. The spreadsheet is created in the Web.Grid component, and then the data is put into the Cells component for processing. Once you look at the worksheet in the workbook from above (in the cells component), the merge information is not there. Note that if you look at the grid component, it properly displays the merged cells.

HTH

8

Hi,

Thank you for considering Aspose.

You are using Workbook.LoadData(ms) method to open the generated Excel sheet. This method is used to load the data only. It will not load any formatting in the workbook. Please try Workbook.Open(ms) method, this will load all the data with the workbook formatting. Hopefully, this will resolve your merged cells issue. If you still face any problem, please share your sample application.

Thank You & Best Regards,

9

That was the key!

Thanks!