creating pivot table using NPOI C#

Ranganathan Arumugham

10y8.3kVisual Basic .NET

HI,

We are trying to create PivotTable using NPOI C# library on existing excel sheet by creating a new sheet.

The code executes fine, when we try to open the excel, it gives us following error:

Removed Part: /xl/pivotTables/pivotTable1.xml part with XML error. (PivotTable view) Load error. Line 2, column 561.

Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

Any Help!!!

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D9y ago
You can check this free excel library:http://www.e-iceblue.com/Introduce/free-xls-component.html, it supports creating pivot table.

Reference:
http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Pivot-Table-Create-Pivot-Table-in-Excel-with-C-/VB.NET.html
0
Bikesh Srivastava9y ago
Hi,
You can see this example...
CREATE TABLE #REVENUE
(
ID             INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[MONTH]        VARCHAR(8) NOT NULL,
SALES          DECIMAL(8,2) NOT NULL,
EmpId int
)

INSERT INTO #REVENUE
([MONTH],SALES,EmpId)
VALUES
('JAN-2015', 200000.16,1),
('FEB-2015', 220000.17,1),
('MAR-2015', 227000.55,2),
('APR-2015', 247032.75,1),
('MAY-2015', 287652.75,2),
('JUN-2015', 265756.75,2),
('JUN-2016', 265.75,3)
select * from #REVENUE

truncate table #Revenue
---------------Simple Pivot example-------
SELECT * FROM
(SELECT
    [MONTH],
    SALES,EmpId
FROM #REVENUE)X
PIVOT
(
    AVG(SALES)
    for [MONTH] in ([JAN-2015],[FEB-2015],[MAR-2015],[APR-2015],[MAY-2015])
)p

-------------Dynamic pivot table----
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME([MONTH])
                    FROM #REVENUE
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
print  @cols
SELECT @query =
'SELECT * FROM
(SELECT
    [MONTH],
    SALES,EmpId
FROM #REVENUE )X
PIVOT
(
    AVG(SALES)
    for [MONTH] in (' + @cols + ')
) P'

EXEC SP_EXECUTESQL @query
0
Pacak9y ago
Take a look to the EPPlus library ( http://epplus.codeplex.com/ ) - it can be used for creating Excel PivotTables.

Also you may use NReco PivotData component for primary data aggregation (from DataTable, CSV file or SQL source) and automated Excel PivotTable generation: https://www.nrecosite.com/pivotdata/create-excel-pivot-table.aspx

0
Delpin Susai Raj10y ago
http://npoi.codeplex.com/releases/view/19351
0