Hello:
I have a simple test case (attached) which tries to merge multiple spreadsheets into one. It does so by copying all the worksheets from each spreadsheet into destination spreadsheet. I use Worksheet.copy() interface to copy the contents. Looks like colors are not being copied correctly.
run the test case as:
java test.TestMergeWorkbook "SmartMarkerDesigner.xls"
open the resulting spreadsheet and notice the ‘invoice’ tab is devoid of all the colors (text and background).
Appreciate your help.
Hi,
Thanks for providing us the template project with file.
Well, it is not an issue rather MS Excel behavior. Actually you did use some custom colors which are not present on the MS Excel(97-2003) standard color palette (which has standard 56 colors (0-55indexed)). You can confirm manually implementing the copy operation in MS Excel (97-2003).
So, you need to add a few lines to your code before copy operation. In fact you need to change the color palette for your destination workbook based on the colors of your source workbook.
See the following code (you need to add the lines. I have checked adding these lines to your code and it works fine.
Sample code:
FileInputStream inputWorkbook = new FileInputStream(workbook);
sourceWorkbook.open(inputWorkbook);
Worksheets sourceWorksheets = sourceWorkbook.getWorksheets();
Color[] colors = sourceWorkbook.getPalette().getColors();
for (int i = 0; i < colors.length; i++)
destWorkbook.getPalette().setColor(i, colors[i]);
for (int i=0; i < sourceWorksheets.size(); i++) {
Worksheet newWorksheet = destWorksheets.addSheet();
Worksheet sourceWorksheet = sourceWorksheets.getSheet(i);
// add worksheet name only if the same doesn’t already present.
if (destWorksheets.getSheet(sourceWorksheet.getName()) == null)
{
newWorksheet.setName(sourceWorksheet.getName());
}
newWorksheet.copy(sourceWorksheet);
}
}
Thank you.
Thanks for a quick response Amjad. The provided code works well but it also indicates that Worksheet.copy is not responsible for copying any of the source workbook’s ‘meta’ information. Can I suggest this as an enhancement to Aspose.Cells team?
Meanwhile, what other such attributes should I be worried about not getting copied correctly? Fonts? Styles?
Appreciate your help.
Hi,
Well, Worksheet.copy works in the same way as MS Excel, you can confirm manually implementing the copy operation in MS Excel (97-2003). Open your template file into MS Excel 2003, now open a new workbook in MS Excel 2003 also. now click on the Invoice tab, now right click on Invoice tab and click Move or Copy… option, now in Move or Copy dialog, select the second workbook e.g Book1.xls in to copy drop down and check the checkbox “Create a copy”. So, you will see that the colors are changed in your second workbook even you have copied the copy from source workbook. This is how Aspose.Cells for Java works. The reason is simple, in your destination workbook, Ms excel has default colors (56 colors) and your custom colors in your source workbook are not there in your destination workbook.
If you don’t want to change the palette (using my additional lines of code), you may use Xlsx (MS Excel 2007) formatted template files for the task, now you don’t need to change the palette because the color palette of Ms Excel 2007 is more enhanced and vast enough, it has huge colors list in its palette.
what other such attributes should I be worried about not getting copied correctly? Fonts? Styles?
Well, there is nothing to worry about as only color palette is effected. So, the fonts with its attributes are not changed at all. If you find such issue, you may contact us and we will check it soon.
Thank you.