creating pivot table using NPOI C#

Question

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!!!

Leon D · Answer

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

Bikesh Srivastava · Answer

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

Pacak · Answer

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

Delpin Susai Raj · Answer

http://npoi.codeplex.com/releases/view/19351

creating pivot table using NPOI C#

Answers (4)