We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

How to get Merge cells data in Excel to Word

Hi,

This is madhu and my doubt is that,

How can I get mergerd cells data in Excel sheet to word document.

Actually I posted this thing in Excel form. But, they replied to ask you guys.

Here I am giving the link of my psot.

<A href="https://forum.aspose.com/t/86894</A></P> <P>Thanks</P> <P>madhu</P>

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your inquiry. Please check the Xls2Doc demo.

http://www.aspose.com/community/files/51/file-format-components/aspose.words/entry112559.aspx

See methods ConvertHorizontalCellMerge and ConvertVerticalCellMerge of ConverterXls2Doc class.

Also see the following link to learn how to merge cells in Word table

http://www.aspose.com/documentation/file-format-components/aspose.words-for-.net-and-java/aspose.words.tables.cellmerge.html

I hope this could be useful for you.

Best regards.

Hi Alexey,

As I am not familiar with Aspose Words that much, I am not able to get the solution for my requirement with the links provided by you.

What exactly I need help from you is that ,

I have a excel sheet where my data is entered and I want to export that data to word document(As earlier we discussed the same thing).

I exported my data to Word using IMailMerge interface.

But, in Excel there are some Merge cells (As attached to the previous post) and I want to get the data to my word document as it is.

But, I could not able to get those merge cells data as it is.

Please help me with some related code in Java.

I have to add that merge cells as it is at certain location in Word document.

How can I do that.

Thanks

Madhu

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your explanation. I don’t think that mail merge is the best approach to convert excel sheet to word table. In the link I provided there is Excel2Word demo that allows you converting excel sheets to Word document. In the attachment you can find java version of Converter class.

But unfortunately I can’t find the way how to determine whether cells in the Excel worksheet are merged horizontally or vertically. In the .NET version of Aspose.Cells I can get merged region and determine whether cell is merged horizontally or vertically. For example see the following C# code that determines whether cell is merged horizontally.

///

/// Convert Excel HorizontalCellMerge to Word HorizontalCellMerge

///

/// Input Excel cell

/// CellMerge type

private CellMerge ConvertHorizontalCellMerge(Aspose.Cells.Cell excelCell)

{

//By default cells are not merged

CellMerge wordsCellMerge = CellMerge.None;

//Get merged region

Aspose.Cells.Range mergedRange = excelCell.GetMergedRange();

if (mergedRange == null)

{

//Cells are not merged

wordsCellMerge = CellMerge.None;

}

else

{

if (excelCell.Column == mergedRange.FirstColumn && mergedRange.ColumnCount > 1)

{

//Cell is merged with next

wordsCellMerge = CellMerge.First;

}

else if (mergedRange.ColumnCount > 1)

{

//Cell is merged with previouse

wordsCellMerge = CellMerge.Previous;

}

else

{

//Cell is not merged

wordsCellMerge = CellMerge.None;

}

}

return wordsCellMerge;

}

But in the Java version there is no way to get merged region and determine how cell is merged with other cells. You can only determine whether cell is merged or not but can’t determine horizontally or vertically. Here is method that I was trying to use as a workaround.

///

/// Convert Excel HorizontalCellMerge to Word HorizontalCellMerge

///

/// Input Excel cell

/// CellMerge type

private int ConvertHorizontalCellMerge(com.aspose.cells.Cell excelCell)

{

//By default cells are not merged

int wordsCellMerge = CellMerge.NONE;

int columnIndex = excelCell.getColumnIndex();

int rowIndex = excelCell.getRowIndex();

if (!excelCell.isMerged())

{

//Cells are not merged

wordsCellMerge = CellMerge.NONE;

}

else

{

//Get next cell

com.aspose.cells.Cell nextExcelCell = excelCell.getCells().getCell(rowIndex, columnIndex + 1);

if (columnIndex == 0 && nextExcelCell.isMerged())

{

wordsCellMerge = CellMerge.FIRST;

}

else if (columnIndex > 0)

{

//Get proviouse cell

com.aspose.cells.Cell prevExcelCell = excelCell.getCells().getCell(rowIndex, columnIndex - 1);

if (prevExcelCell.isMerged())

wordsCellMerge = CellMerge.PREVIOUS;

else if (nextExcelCell.isMerged())

wordsCellMerge = CellMerge.FIRST;

else

wordsCellMerge = CellMerge.NONE;

}

else

{

//Cell is not merged

wordsCellMerge = CellMerge.NONE;

}

}

return wordsCellMerge;

}

As you can see I was trying to determine whether the next cell and the previous cell are merged with current. But this approach does not work if you have two cell merged vertically in one column and two merged in the next column, because isMerged() will return true for all four cells. For example here is what you have in Excel

Merged

Merged

And here is what you will get in Word

Merged

I hope this information could be useful for you.

Also I think that you should ask Aspose.Cells team how to determine whether cell is merged vertically or horizontally.

Best regards.

Hi again,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

I found the solution for java. Here are methods.

///

/// Convert Excel HorizontalCellMerge to Word HorizontalCellMerge

///

/// Input Excel cell

/// CellMerge type

private int ConvertHorizontalCellMerge(com.aspose.cells.Cell excelCell)

{

//By default cells are not merged

int wordsCellMerge = CellMerge.NONE;

int columnIndex = excelCell.getColumnIndex();

int rowIndex = excelCell.getRowIndex();

if (!excelCell.isMerged())

{

//Cells are not merged

wordsCellMerge = CellMerge.NONE;

}

else

{

ArrayList mergedCells = excelCell.getCells().getMergedCells();

CellArea mergedArea = null;

//Search for merged area that includes curren cell

for (int areaIndex = 0; areaIndex < mergedCells.size(); areaIndex++)

{

CellArea area = mergedCells.get(areaIndex);

if (rowIndex <= area.getEndRow() && rowIndex >= area.getStartRow() &&

columnIndex <= area.getEndColumn() && columnIndex >= area.getStartColumn())

{

mergedArea = area;

break;

}

}

if (mergedArea != null)

{

if (mergedArea.getStartColumn() == mergedArea.getEndColumn())

{

//Cell is not merged horisontaly

wordsCellMerge = CellMerge.NONE;

}

else

{

//Cell is first in region

if (columnIndex == mergedArea.getStartColumn())

wordsCellMerge = CellMerge.FIRST;

//Cell is merged with previouse

else

wordsCellMerge = CellMerge.PREVIOUS;

}

}

}

return wordsCellMerge;

}

///

/// Convert Excel VerticalCellMerge to Word VerticalCellMerge

///

/// Input Excel cell

/// CellMerge type

private int ConvertVerticalCellMerge(com.aspose.cells.Cell excelCell)

{

//By default cells are not merged

int wordsCellMerge = CellMerge.NONE;

int columnIndex = excelCell.getColumnIndex();

int rowIndex = excelCell.getRowIndex();

if (!excelCell.isMerged())

{

//Cells are not merged

wordsCellMerge = CellMerge.NONE;

}

else

{

ArrayList mergedCells = excelCell.getCells().getMergedCells();

CellArea mergedArea = null;

//Search for merged area that includes curren cell

for (int areaIndex = 0; areaIndex < mergedCells.size(); areaIndex++)

{

CellArea area = mergedCells.get(areaIndex);

if (rowIndex <= area.getEndRow() && rowIndex >= area.getStartRow() &&

columnIndex <= area.getEndColumn() && columnIndex >= area.getStartColumn())

{

mergedArea = area;

break;

}

}

if (mergedArea != null)

{

if (mergedArea.getStartRow() == mergedArea.getEndRow())

{

//Cell is not merged verticaly

wordsCellMerge = CellMerge.NONE;

}

else

{

//Cell is first in region

if (rowIndex == mergedArea.getStartRow())

wordsCellMerge = CellMerge.FIRST;

//Cell is merged with previouse

else

wordsCellMerge = CellMerge.PREVIOUS;

}

}

}

return wordsCellMerge;

}

And in the attachment you can find upgraded converter class and output document.

Best regards.

Hi alexey,

Thanks for your quick replies.

I will check out the code and I will get back to you.

Thanks a lot.

Madhu

Hi alexey,

I tested the code. I am able to recognize whether cells are merged or not.

But, how can I sepecify the same to word document.

Means, how can I send that property to word document.

My code is like as follows

for (int rowIndex = 6; rowIndex <= 12; rowIndex++)
{
ArrayList list=new ArrayList();
int x=0;
int merge=0;
for(int colIndex=1;colIndex<=5;colIndex++)
{
if(worksheet.getCell(rowIndex,colIndex).getStringValue().equalsIgnoreCase("0"))
{
exceldata="";
}
else
{

merge=ConvertVerticalCellMerge(worksheet.getCells().getCell(rowIndex, colIndex));
exceldata=worksheet.getCells().getCell(rowIndex, colIndex).getStringValue();
}
list.add(x,exceldata);
x++;
}
lists.add(new com.crisil.Map(list));
a++;
}

How can I send the merge cell property to word document.

Thanks

Madhu

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your inquiry. The table cell has VerticalMerge and HorizontalMerge properties; these properties indicate how cell is merged with other cells. See the following links for more information

http://www.aspose.com/documentation/file-format-components/aspose.words-for-.net-and-java/com/aspose/words/cellformat.html#HorizontalMerge

http://www.aspose.com/documentation/file-format-components/aspose.words-for-.net-and-java/com/aspose/words/cellformat.html#VerticalMerge

As you can see cell merge could be NONE, FIRST and PREVIOUS. See the following link for more information.

http://www.aspose.com/documentation/file-format-components/aspose.words-for-.net-and-java/com/aspose/words/cellmerge.html

Also you can find an example if you look at the code I attached in my previous post.

Hope this could help you.

Best regards.