Free Support Forum - aspose.com

Font not being set properly when using copy with PasteOptions


#1

When using the Range.Copy function with PasteOptions, the font is not being set properly. In this example, the chart and trend lines are also not being copied but for now I am more interested in the font being set properly for now. Please run the attached source code with the attached Excel file and observe cell A1 in the new file. You will see that the correct font is not being set.

Information on the chart and trend lines will also be helpful.

Workbook sourceWorkbook = new Workbook(“c:_sample.xlsx”);
Cells sourcCells = sourceWorkbook.getWorksheets().get(3).getCells();
Range sourceRange = sourcCells.createRange(“A1”, “O10”);

Workbook targetWorkbook = new Workbook();
Cells targetCells = targetWorkbook.getWorksheets().get(0).getCells();
Range targetRange = targetCells.createRange(“A1”, “O10”);

PasteOptions pasteOptions = new PasteOptions();
// pasteOptions.setPasteType(PasteType.COLUMN_WIDTHS + PasteType.ROW_HEIGHTS + PasteType.FORMATS + PasteType.VALUES_AND_NUMBER_FORMATS);
pasteOptions.setPasteType(PasteType.ALL);

targetRange.copy(sourceRange, pasteOptions);
targetWorkbook.save(“c:_copy.xlsx”);

_sample.zip (54.5 KB)


#2

@DanielAbc123,

Please spare us little time as we are working on this issue and will write back soon after analysis.


#3

@DanielAbc123,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42848 - Fonts setting and other objects not copied using Range.copy() function

#4

@DanielAbc123,

1: For the fonts not being set properly when using copy with PasteOptions

Please copy theme and default style of the source file.

2: Chart are also not being copied.

Please change the range as A10:P10.

3: Trend lines are are also not being copied.

We will fix this issue later.

Please try the following code:

String dataDir = "D:\\FileTemp\\";
Workbook sourceWorkbook = new Workbook("D:\\FileTemp\\_sample.xlsx");
Cells sourcCells = sourceWorkbook.getWorksheets().get(3).getCells();
Range sourceRange = sourcCells.createRange("A1", "P10");

Workbook targetWorkbook = new Workbook();
targetWorkbook.copyTheme(sourceWorkbook);
targetWorkbook.setDefaultStyle(sourceWorkbook.getDefaultStyle());

Cells targetCells = targetWorkbook.getWorksheets().get(0).getCells();        
Range targetRange = targetCells.createRange("A1", "P10");

PasteOptions pasteOptions = new PasteOptions();
pasteOptions.setPasteType(PasteType.COLUMN_WIDTHS + PasteType.ROW_HEIGHTS + PasteType.FORMATS + PasteType.VALUES_AND_NUMBER_FORMATS);
pasteOptions.setPasteType(PasteType.ALL);
targetRange.copy(sourceRange, pasteOptions);

// Save the Excel file        
targetWorkbook.save(dataDir +"dest.xlsx");

Let us know your feedback.


#5

@DanielAbc123,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.


#6

Thanks for the quick attention on this. I have not tried your proposed code changes yet but I will soon. In the meantime, I am noticing a much greater issue that needs attention. Apparently Aspose.Cells is having a problem returning an HTML string from a workbook that it creates and that MS Excel can successfully open. Originally, I have about 20 HTML views I need to capture from a workbook using Aspose.Cells but some of them take over 4 minutes ( maybe this is another issue that can be looked at ). I thought to take snapshots of them into a new workbook using the Range.copy() to see if the performance issue was related to the much larger workbook BUT I am getting a [java.lang.ArrayIndexOutOfBoundsException]! At first this was an in-memory operation but I got the aforementioned exception so I resorted to saving it as a file and then tried processing the file but I get the same exception. I want to keep this as an in-memory operation without creating a physical file and I know how to do that BUT the exception issue needs to be resolved. Please help! :smiley:

The stack trace is below and the attached screenshot also shows that the error is coming from line 393 at the Workbook.save() function.

---------stack—

35488 [ERROR] o.a.c.c.C.[.[.[.[.c.c.b.epm.AppConfig] : Servlet.service() for servlet [com.private.private.private.AppConfig] in context with path [] threw exception [java.lang.ArrayIndexOutOfBoundsException: -1] with root cause
java.lang.ArrayIndexOutOfBoundsException: -1
at java.util.ArrayList.elementData(ArrayList.java:418)
at java.util.ArrayList.get(ArrayList.java:431)
at com.aspose.cells.zahj.g(Unknown Source)
at com.aspose.cells.zahj.a(Unknown Source)
at com.aspose.cells.zahl.b(Unknown Source)
at com.aspose.cells.zahh.a(Unknown Source)
at com.aspose.cells.zahm.b(Unknown Source)
at com.aspose.cells.zahm.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.private.private.private.XXXWorkbook.getUnsanitizedHtmlFromRange(XXXWorkbook.java:393)

---------code—

private String getUnsanitizedHtmlFromRange(Range sourceRange) throws Exception {
Stopwatch stopwatch = Stopwatch. createStarted ();
//Set the sheet where the namedRange is found as the active sheet because
//we are forcing our HtmlSaveOptions to only export the active worksheet.
Workbook targetWorkbook = new Workbook();
Cells targetCells = targetWorkbook.getWorksheets().get(0).getCells();
Range targetRange = targetCells.createRange(0, 0, sourceRange.getRowCount(), sourceRange.getColumnCount());
PasteOptions pasteOptions = new PasteOptions();
//pasteOptions.setPasteType(PasteType.COLUMN_WIDTHS + PasteType.ROW_HEIGHTS + PasteType.FORMATS + PasteType.VALUES_AND_NUMBER_FORMATS); pasteOptions.setPasteType(PasteType. ALL );
targetRange.copy(sourceRange, pasteOptions);
Worksheet targetWorksheet = targetRange.getWorksheet();

targetWorkbook.getWorksheets().setActiveSheetIndex(targetWorksheet.getIndex());
targetWorksheet.getPageSetup().setPrintArea(targetRange.getAddress());
targetWorkbook.save( "d: \ " + sourceRange.getWorksheet().getName() + ".xlsx" );

//Now set the necessary HtmlSaveOptions so that we can get our HTML view. HtmlSaveOptions options = new HtmlSaveOptions(SaveFormat. HTML );
options.setDisableDownlevelRevealedComments( true );
options.setExcludeUnusedStyles( true );
options.setExportActiveWorksheetOnly( true );
options.setExportDocumentProperties( false );
options.setExportFrameScriptsAndProperties( false );
options.setExportImagesAsBase64( false );
options.setExportPrintAreaOnly( true );
options.setExportSimilarBorderStyle( true );
options.setExportWorkbookProperties( false );
options.setExportWorksheetCSSSeparately( false );
options.setExportWorksheetProperties( false );
options.setParseHtmlTagInCell( true );
options.setHtmlCrossStringType(HtmlCrossType. FIT_TO_CELL );
//Save the file to BAOS, trim the BOM, and return as string ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

Workbook targetWorkbook2 = new Workbook( "d: \ AXDI_Restricted_Test.xlsx" ); // + sourceRange.getWorksheet().getName() + ".xlsx"); targetWorkbook2.save(outputStream, options);

String trimmed = trimUTF8BOM (outputStream.toString( "UTF-8" ).replaceAll( "mso-.+:.+;" , "" ));
logger .info(String. format ( "<— Time taken to get unsanitized HTML(%s) from Range: %s" , trimmed.length(),
stopwatch.toString()));
return trimmed;
}

Aspose Error.zip (160.6 KB)


#7

@DanielAbc123,

We were able to observe the issue where exception is raised while saving XLSX file into memory stream but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42849 - IndexOutOfBoundsException exception while converting XLSX to HTML

Regarding the performance issue, please mention the source range which is used in your testing. I have tried it using entire data range but could not observe it. Better if you provide separate runnable code segment for our testing.


#8

@DanielAbc123,

This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42849”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.


#9

The issues you have found earlier (filed as CELLSJAVA-42848,CELLSJAVA-42849) have been fixed in Aspose.Cells for Java 19.3. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi