NPOI setting a new workbook ThemesTable copied from another workbook ThemesTable does nothing

[EugenM88]

Hello everyone! Thank you very much for improving this wonderfull program, so far i was able to do alot with it however i think i have found a very strange bug that makes me unable to achieve the desired final result on a programmatically created workbook.
I am simply splitting a workbook of several sheets into new programmatically created workbooks containing only 1 sheet each, for example a workbook that has 100 sheets will then be split into 100 files/workbooks that each contain 1 sheet.
I have noticed that some of those workbooks have a different Theme for colors, namely one of them have the office 2006-2012 color theme enabled, obviously the new workbook created programmatically should reflect the same color theme as that will change the color selection behavior of the new workbooks. I have found no documentation regarding my issue but i believe this can be very easy to fix, please see details below for relevant information:

NPOI Version

2.7.1

File Type

• XLSX

Reproduce Steps

These lines of code will suffice to test:

new_workbook.GetStylesSource().SetTheme(old_workbook.GetStylesSource().GetTheme());
var stream = await sampleFile.OpenStreamForWriteAsync();
stream.SetLength(0);//stream.position = 0 can corrupt existing file, instead use setlength to fully overwrite it!
new_workbook.Write(stream, false);

Issue Description

As you can see the line of code above should be sufficient to change the page layout colors theme of the new workbook to reflect the same color theme of the old workbook, even though debugging programatically that line of code above semms to actually change the said theme, for some reason when opening excel such change does not reflect inside the app, at the page layout tab if we open the colors menu the different theme does not appear to be selected and the default colors do not reflect the theme i have selected.

Perhaps i am missing something? if so could you please point me in the right direction on how to change the color theme of a workbook to a different one? I know it should be easy but i have found no examples over the net and i've searched alot!

Thanks in advance for all of your help!

[EugenM88]

I have inspected the issue further, what is strange is that when saving the new workbook file and opening it using a normal zip software, it does not contain a theme folder inside the xl folder, copying the theme folder from another similar file is not sufficient to actually change the selected theme inside the workbook

File saved by NPOI with a different theme, yet no theme folder inside

A copy paste file that i have changed the theme manually by using microsoft excel application and saving...

Does now have the said theme folder inside...

I have tried to copy the theme folder of the excel edited file into my "untouched" file but when opening said file the theme still does not seem to be changing at all.

I have also edited the original post to include the way i save the workbook, perhaps that is necessary for debugging.

[wuzxc1230123]

You need to provide complete code
@EugenM88

[EugenM88]

Hello, certainly, here it is:

List<StorageFile> filesAssigned = new List<StorageFile>();//collection populated from user input, irrelevant to NPOI.

 var o_stream_operation = await filesAssigned[0].OpenReadAsync();

 var o_stream = o_stream_operation.AsStreamForRead();

 NPOI.XSSF.UserModel.XSSFWorkbook old_workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(o_stream);
NPOI.XSSF.UserModel.XSSFWorkbook new_workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();

ISheet worksheet = old_workbook.GetSheetAt(0);
((XSSFSheet)worksheet).CopyTo(new_workbook, worksheet.SheetName, true, false);

new_workbook.GetStylesSource().SetTheme(old_workbook.GetStylesSource().GetTheme());
var stream = await sampleFile.OpenStreamForWriteAsync();
stream.SetLength(0);//stream.position = 0 can corrupt existing file, instead use setlength to fully overwrite it!
new_workbook.Write(stream, false);

Thank you for your time! I really hope this will help you fix the issue as i have no other way to set the theme for the new workbook!

[tonyqus]

Can you provide the original Excel file containing the theme?

[EugenM88]

excelToSend.xlsx
Here it is!
As you can notice when exporting the colors do not properly represent the original colors, look namely at cells that says "Cinzento" or "Rosa".

You can also look at the color theme which has on the original file selected office2007-office2010 and yet on the new workbook with the copied worksheet it does not reflect even that.