Hi,
I need help with transferring a formatted Excel Range as a Table in Word. For collecting the formatting information I am using Aspose.cells and for inserting it as a Table I am using Word Java Script API. I am using cell.GetSyle to get the cell formatting. Currently Aspose is not picking up any formatting info from the cells even when they are formatted which I don’t understand. So if I take an image of a range the formatting shows but not when I try to extract it using cell properties. I also need help with preserving the formatting for any case, i.e if the range is manually formatted, if it is formatted as a table, conditional formatting is applied, cell styles are used or any other way of applying formatting is used. I am aware that Word does not support all the colors, font etc that Excel does and that is not an issue to me. Let me know if there is a working solution to the above.
Good day!
@Adhirath
Please set the printing area range to the range you need to export, and then save the file directly to docx format. Please refer to the following example code and see the attachment. result.zip (19.4 KB)
Workbook wb = new Workbook(filePath + "input.xlsx");
PageSetup pageSetup = wb.getWorksheets().get(0).getPageSetup();
pageSetup.setPrintArea("C1:H10");
DocxSaveOptions options = new DocxSaveOptions();
wb.save(filePath + "out_java.docx", options);
For information on how to format data and use conditional formatting, please refer to the following documents.
@Adhirath
Could you share your Excel file here ? We will check it soon.
If the cell contains conditional formatting and you want to get the display style, please try
Style style = cell.GetDisplayStyle();
unable to attach, also is there a way for me to save the range’s formatting in an xml format readable by word?
1,
What‘s the matter about uploading the file?
2,
Please check the solution about convert range to docx in the above post.
3,
If the conditional formatting is applied , please use Cell.GetDisplayStyle()
Please note, by default the forums do not allow to attach Excel (XLS, XLSX, etc.) files. Please zip the Excel file and attach/upload the zipped archive here. We will check your issue soon.
Adhirath Excel Doc.zip (25.9 KB)
So the idea is to preserve as much formatting as possible when pasting the range as a table in word. I will be using word js api and will need an Ooxml when inserting the table. Also i use .net instead of java, would that be a probem?
Thanks for the template Excel file.
I tested your scenario/case using the provided template Excel file with Aspose.Cells for .NET v25.2, and it performed as expected. The conditionally formatted cells in the first worksheet were rendered correctly in the output OOXML (DOCX) file. I processed the entire workbook, which contains two worksheets, to generate the final DOCX file.
e.g.,
Sample code:
Workbook wb = new Workbook("e:\\test2\\Adhirath Excel Doc.xlsx");
DocxSaveOptions options = new DocxSaveOptions();
wb.Save("e:\\test2\\out1.docx", options);
By default, Aspose.Cells converts Excel worksheets to the DOCX file format as they appear in the print preview within MS Excel. Please review the attached DOCX output file and let us know if you encounter any issues with cells’ conditional formatting or other objects. We will address your concerns promptly.
out1.zip (32.5 KB)
@Adhirath
If conditional formatting and table styles apply to the cell, the Cell.GetStyle() method does not return information about conditional formatting and table styles. You need to use Cell.GetDisplayStyle() method to get the display style. Please refer to the following example code.
Workbook wb = new Workbook(filePath + "Adhirath Excel Doc.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Cell v4 = cells["V4"];
Cell d20 = cells["D20"];
Console.WriteLine("v4 style: " + v4.GetStyle().ForegroundColor);
Console.WriteLine("d20 style: " + d20.GetStyle().ForegroundColor);
Console.WriteLine("v4 display style: " + v4.GetDisplayStyle().ForegroundColor);
Console.WriteLine("d20 display style: " + d20.GetDisplayStyle().ForegroundColor);
The output:
v4 style: Color [Empty]
d20 style: Color [Empty]
v4 display style: Color [A=255, R=248, G=105, B=107]
d20 display style: Color [A=255, R=21, G=96, B=130]
Hope helps a bit.
Thank you soo much!!! Just one last thing, can I extract the Ooxml of a selected range using Aspose?
@Adhirath
There is currently no direct way to meet your needs. However, you can copy the range that needs to be processed to a newly created workbook through range copying, and then continue processing your business. Regarding how to copy range, please refer to the following document.
@Adhirath
Of course, you can also create iterators on the range that needs to be processed, and iterate to access the cells contained within the range. Please refer to the following example code.
Workbook wb = new Workbook(filePath + "input.xlsx");
Range range = wb.Worksheets[0].Cells.CreateRange("C1", "H10");
IEnumerator iter = range.GetEnumerator();
while (iter.MoveNext())
{
Cell curr = (Cell)iter.Current;
Console.WriteLine(curr.Name + ": " + curr.GetDisplayStyle().ForegroundColor);
}
Hope helps a bit.
Not an issue, really appreciate the help. Have a good day!
Just to be clear i want to get the Ooxml for the range that is being rendered in word using aspose.words not the excel range
@Adhirath
Thank you for your feedback. Regarding the business of processing the range of Aspose.Words, please create a new post on the Aspose.Words forum. The Words staff member will assist you accordingly soon.
Will do, does it work if instead of DocxSaveOptions I use OoxmlSaveOptions? will the range be saved in Ooxml format or it doesn’t work.
@Adhirath
If you use Aspose.Cells to save the sample file in docx format, please use DocxSaveOptions. If you use Words, you need to refer to the API of the Words product. Please review the following document.
Hi, this is the code I am using. For some reason other tables and charts are also showing up in the created document.
Workbook workbook = new Workbook(“C:\my\path\Adhirath Excel Doc.xlsx”);
Aspose.Cells.Range range = workbook.Worksheets.Names[RangeName].GetRange();
Aspose.Cells.PageSetup pageSetup = range.Worksheet.PageSetup;
pageSetup.PrintArea = range.RefersTo;
DocxSaveOptions saveOptions = new DocxSaveOptions();
workbook.Save(“C:\my\path\Adhirath Excel Doc.Docx”, saveOptions);
Adhirath Excel Doc.zip (27.6 KB)
here is the excel file. I am trying to print only the named range called formattedTable
Adhirath Excel Doc.zip (25.9 KB)
The other content is from the other sheet “Sheet2”, please use the following code to hide all the worksheets except the worksheet where the specified range is in.
Workbook workbook ...
//hide all the sheet except the range sheet.
range.Worksheet.IsVisible = true;
foreach (Worksheet sheet in workbook.Worksheets)
{
if(sheet.Index != range.Worksheet.Index)
{
sheet.IsVisible = false;
}
}
DocxSaveOptions saveOptions = new DocxSaveOptions();
workbook.Save(“C:\my\path\Adhirath Excel Doc.Docx”, saveOptions);