File with Pivot table corrupted after saving with NPOI

[Khalidaba]

Hello,
cannot open the file after manipulate it on my projet even if i dont do anything on it like this :

` //i get my file from the directory where it save
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(fs);

            //And save it in an other folder
             try
            {
                workbook.Write(bos);
            }
            finally
            {
                bos.Close();
            }
            fileContents = bos.ToByteArray();
            File.WriteAllBytes(@"C:\user\New folder\test.xlsx", fileContents);

`
when i open it say

and here the error screen when i trust the source

it seems the color and theme dont load correctly but cells and sheets (even those hide) are here ...
BEFOR

After

testPivot.xlsx

[tonyqus]

There is a big gap in pivotTable openxml serialization. I'm scheduling a fix in NPOI 2.5.6

[Syrlander]

@tonyqus I would like to take a look at the issue, as I'm working with a spreadsheet containing multiple pivot tables. Currently the only package I can get to work in my case is the Microsoft Interop package for Excel which is incredibly slow.

I don't have much prior experience working with OpenXML, could you maybe point me in the direction of some related documentation?

[tonyqus]

@Syrlander thank you for your interest. Maybe OpenXML SDK is a good choice for you. But I definitely welcome you contribute some code to NPOI. I do need a hand on this project.

There are some open source documents for OpenXML for you to get started with
https://docs.microsoft.com/en-us/office/open-xml/working-with-pivottables
dotnet/Open-XML-SDK#612
https://www.todaysoftmag.com/article/848/getting-started-with-openxml

Please keep these questions in mind.

1. Where are these pivot table defined? In which openxml files?
2. the goal of these openxml files such as pivottable.xml, pivotcachedefinition and so on
3. how are these pivot elements embeded into sheet.xml (sheet.xml is the main definition of each sheet)

Btw, do you use telegram? Maybe you can join the NPOI user group at https://t.me/npoidevs

[tonyqus]

#622 #697

[tonyqus]

Pivot table openxml code of NPOI is located at
https://github.com/nissl-lab/npoi/blob/master/OpenXmlFormats/Spreadsheet/PivotTable

[KnyazSh]

Hello, @tonyqus. I am actively working on solving this problem. I have already done a lot of work on adding the missing code. I anticipate that I will be submitting a PR for your review in the near future.

[solarding]

Hello, @tonyqus. I am actively working on solving this problem. I have already done a lot of work on adding the missing code. I anticipate that I will be submitting a PR for your review in the near future.

Hi @KnyazSh, just tried your latest commits, unfortunately the error remains.
Are you still working on it?

[KnyazSh]

Hi @solarding, No. I thought I'd already finished. Can you write more about the errors and send me an example file?

[solarding]

Hi @solarding, No. I thought I'd already finished. Can you write more about the errors and send me an example file?

The file it created is corrupted that excel can not open it.

Portfolio2020.xlsx
Portfolio202201261723.xlsx

public void testc()
{
IWorkbook workbook;

        var fn = _PFAD + "Portfolio2020.xlsx";
        var newFn = _PFAD + $"Portfolio{DateTime.Now:yyyyMMddHHmm}.xlsx";
        File.Copy(fn, newFn);
        using (var fs = new FileStream(fn, FileMode.Open, FileAccess.Read))
        {
            workbook = WorkbookFactory.Create(fs);                
            var dict = GetStockInfos(workbook);

            var shtAnalysis = workbook.GetSheet("Analysis");
            for (int i = 1; i < 100; i++)
            {
                var r = shtAnalysis.GetRow(i);
                if (r == null) break;
                var stockname = r.GetCell(0)?.StringCellValue;
                if (string.IsNullOrWhiteSpace(stockname) || !dict.ContainsKey(stockname)) continue;
                var stockCount = r.GetCell(3)?.NumericCellValue;
                if (!stockCount.HasValue || stockCount.Value <= 0) continue;
                var price = 100;//Stock.GetQuote(dict[stockname]);
                var marketValue = price * stockCount.Value;
                var cellMarketValue = r.GetCell(5);
                cellMarketValue.SetCellValue(marketValue);
            }
            shtAnalysis.GetRow(0).GetCell(5).SetCellValue(DateTime.Today.ToShortDateString());
        }
        using (var fs1 = new FileStream(newFn, FileMode.OpenOrCreate, FileAccess.Write))
            workbook.Write(fs1);

        workbook.Close();

    }

[solarding]

Hi @KnyazSh could you reproduce the problem?

[tonyqus]

I've created the new branch Pivot-table

[tonyqus]

@KnyazSh Hi, I'm thinking if I should involve your pivot table implementation in NPOI 2.5.6. Any thoughts?

[KnyazSh]

Hi @tonyqus and @solarding.
I'm sorry I've been gone so long. I made another correction (#786) on the file analysis from @solarding.
I also found a small error in the test example from @solarding: please don't use File.Copy(fn, newFn);, because it breaks the outgoing stream a little bit. Just remove it and everything will work for you. But, as I said above, a small problem I found and solved.

[JeanPhilippeLux]

Hi, do you have a date for the release of the new version with this fix please ? I try to compile NPOI 2.6.0 by myseleft but I get an error so I can't test the new version.

thank you very much.