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.
https://forum.aspose.com/t/86894
Thanks
madhu
Hi
Thanks for your inquiry. Please check the Xls2Doc demo.
https://releases.aspose.com/words/net
See methods ConvertHorizontalCellMerge and ConvertVerticalCellMerge of ConverterXls2Doc class.
Also see the following link to learn how to merge cells in Word table
https://reference.aspose.com/words/net/aspose.words.tables/cellmerge/
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
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,
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
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
https://reference.aspose.com/words/net/aspose.words.tables/cellformat/horizontalmerge/
https://reference.aspose.com/words/net/aspose.words.tables/cellformat/verticalmerge/
As you can see cell merge could be NONE, FIRST and PREVIOUS. See the following link for more information.
https://reference.aspose.com/words/net/aspose.words.tables/cellmerge/
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.