Hi Salman,
This is Madhu and I am using Apsoe words and Cells. Here I have a problem that how to copy selected range of cells data to word document using Aspose with Java. Can you help me. I am copying using the following code. for (int rowIndex = 74; rowIndex
Dear Madhu,
I have posted your email in Aspose.Cells forum where our developers will assist you.
Have a good time.
Hi,
It seems your post with example code is not complete. Would you please give us your code and describe your problem with more details so we can give some help? Thank you.
Hi ,
Here I am mentioning my code to get data from Excel and paste it into word. It is working but, the formatting and colour schemes are not working.
Workbook workbook = new Workbook();
workbook.open(fstream);
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);
for(int i=0;i<workbook.getNumerOfSheets();i++)
{
Worksheet worksheet=(Worksheet)workbook.getSheet(i);
//int rowCount = worksheet.getCells().getMaxDataRow();
// int columnCount = worksheet.getCells().getMaxDataColumn();
for (int rowIndex = 74; rowIndex <= 127; rowIndex++)
{
builder.getRowFormat().setHeight(worksheet.getRow(rowIndex).getHeight());
builder.getRowFormat().setAlignment(worksheet.getRow(rowIndex).getStyle().getHAlignment());
for (int columnIndex = 7; columnIndex <= 12; columnIndex++)
{
builder.getCellFormat().setWidth(worksheet.getCells().getColumnWidth(columnIndex) );
builder.insertCell();
builder.getFont().setSize(worksheet.getCell(rowIndex,columnIndex).getStyle().getFont().getSize());
worksheet.getCell(rowIndex,columnIndex).setStyle(worksheet.getCell(rowIndex,columnIndex).getStyle());
if(!worksheet.getCell(rowIndex,columnIndex).getStringValue().equalsIgnoreCase("0"))
builder.write(worksheet.getCell(rowIndex,columnIndex).getStringValue());
}
builder.endRow();
}
builder.endTable();
builder.insertBreak(BreakType.PARAGRAPH_BREAK);
}
}
//Save output document
doc.save("D:\\SMEStar\\NSIC_Cases\\Harika\\Harikadrugs_ratingnote_june242008_unlinked.doc",SaveFormat.DOC);
//doc.save(wordfile);
Over there I specified for a particular set of Excel fields like (H74 to O127 means 54 rows and 8 columns) .
But I have to paste 25 objects (tables) like that into word Document from Excel sheet.
It will take time to repeat the loop for each table in real time.
So, can you suggest me any other solution or advise me how to get same formatting in the word document as like excel sheet.
With the above code the display of table of content is happening. But, formatting is not happening.
Now it is clear I think.
Thnaks
Madhu
Hi Johnson,
Did you get my posted information.
I am waiting for you reply
I need formating issue from Excel to word
Thanks
Madhu
Hi Madhu,
For your code, I have made some change on the aspect of performance of Aspose.Cells. I am not familiar with the API of Aspose.Words, but I try to show some needed extra conversion from Excel to Word, such as Color. Please use attached version to run following code:
Workbook workbook = new Workbook();
workbook.open("t.xls");
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);
Worksheets worksheets = workbook.getWorksheets();
final int columnStart = 0, columnEnd = 2;
int[] arrColWidth = new int[columnEnd-columnStart+1];
int countOfSheets = worksheets.size();
for(int i = 0; i < countOfSheets; i++)
{
Worksheet worksheet = worksheets.getSheet(i);
Cells cells = worksheet.getCells();
for(int columnIndex = columnStart; columnIndex <= columnEnd; columnIndex++)
{
arrColWidth[columnIndex-columnStart] = cells.getColumnWidthPixel(columnIndex);
}
for(int rowIndex = 0; rowIndex <= 4; rowIndex++)
{
RowFormat rowFormat = builder.getRowFormat();
rowFormat.setHeight(cells.getRowHeight(rowIndex));
Row row = cells.getRow(rowIndex);
int hAlign = RowAlignment.LEFT;
switch(row.getStyle().getHAlignment())
{
case HorizontalAlignmentType.CENTRED:
case HorizontalAlignmentType.CENTRED_ACROSS:
hAlign = RowAlignment.CENTER;
break;
case HorizontalAlignmentType.RIGHT:
hAlign = RowAlignment.RIGHT;
break;
}
rowFormat.setAlignment(hAlign);
for(int columnIndex = columnStart; columnIndex <= columnEnd; columnIndex++)
{
com.aspose.words.Cell cellDoc = builder.insertCell();
CellFormat cellFormat = builder.getCellFormat();
//use checkCell taking place of getCell to avoid creating Cell object for empty cells
Cell cell = row.checkCell(columnIndex);
Style cellStyle;
if(cell == null)
{
//use Cells.getCellStyle for non-defined cell to avoid creating Cell object for empty cells
cellStyle = cells.getCellStyle(rowIndex, columnIndex);
}
else
{
cellStyle = cell.getStyle();
}
cellFormat.setWidth(arrColWidth[columnIndex-columnStart]);
builder.getFont().setSize(cellStyle.getFont().getSize());
//worksheet.getCell(rowIndex,columnIndex).setStyle(worksheet.getCell(rowIndex,columnIndex).getStyle());
//set Style to Excel's cell? maybe you want to set style for cell of table in Doc, such as set Color as following:
com.aspose.words.Font fontDoc = builder.getFont();
Color colorCell = cellStyle.getFont().getColor();
fontDoc.setColor(new java.awt.Color(colorCell.getRed(), colorCell.getGreen(), colorCell.getBlue()));
if(cell != null)
{
String cellValue = cell.getStringValue();
if(!cellValue.equalsIgnoreCase("0")) //if want to check cell's value is blank, please replace condition as cell.getValueType != CellValueType.NULL
{
builder.write(cellValue);
}
}
}
builder.endRow();
}
builder.endTable();
builder.insertBreak(BreakType.PARAGRAPH_BREAK);
}
// Save output document
doc.save("t1.doc",SaveFormat.DOC);
Hi Madhu,
Please ignore the attachment in my previous reply, something wrong when archive it. and please try this attachment. Thank you.
Hi join,
What exactly this .jar file does?
Is it able to get the properties from Excel to Word by implementing some of the methods ?
Thanks
Madhu
Hi,
For this jar, we only provide some new API for performance consideration. such as Cells.getCellStyle(), such APIs can avoid initializing some empty objects. To get properties from Excel to Word, you have to write your own code to implement it, my code in my previous reply for your post is just some sample.