C # use an EXCEL Pivot table to export multiple rows and columns

Public static string ExportToProvit (System. web. UI. page objPage, string [] Pvalues) {// Pvalues [0] = row region data, Pvalues [1] = column region data, Pvalues [2] = data region data Microsoft. office. interop. excel. application m_objExcelApp; Microsoft. office. interop. excel. workbook m_objExcelWorkBook; Microsoft. office. interop. excel. worksheet m_objExcelWorkSheet; Microsoft. office. interop. excel. worksheet m_objExcelWorkSheet2; string strAbsoluteP Ath = clsCommon. getUploadFilePath (objPage, clsCommon. genmUploadFileKind. templete, ""); string strRelativePath = clsCommon. getUploadFileUrl (objPage, clsCommon. genmUploadFileKind. templete, ""); string strFileName = Pvalues [3]. toString (); if (strFileName! = Null) {strFileName = strFileName. split ('/') [strFileName. split ('/'). length-1];} m_objExcelApp = new Microsoft. office. interop. excel. application (); m_objExcelApp.DisplayAlerts = false; m_objExcelWorkBook = m_objExcelApp.Workbooks.Open (strAbsolutePath + strFileName, Type. missing, true, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing); m_objExcelWorkSheet = (Microsoft. office. interop. excel. worksheet) m_objExcelWorkBook.Sheets [""]; DataTable dt = (DataTable) HttpContext. current. session ["PVDT"]; int row = 1; int col = 1; if (dt! = Null) {row = dt. rows. count + 1; col = dt. columns. count; HttpContext. current. session ["PVDT"] = null; // mod by chairuirui 2013-1-21} Microsoft. office. interop. excel. export tcaches objPivot = m_objexcelworkbook.w.tcaches (); objes. add (Microsoft. office. interop. excel. xlPivotTableSourceType. xlDatabase, "total data! R1C1: R "+ row +" C "+ col + ""). createPivotTable (m_objExcelWorkSheet.Cells [3, 1], "pivot table", Type. missing, Type. missing); // "total data! R2C1: R52C30 "original data range SHEET name! R start Row C start column: Total number of R data rows C Total number of data columns Microsoft. office. interop. excel. range objRange = (Microsoft. office. interop. excel. range) m_objExcelWorkSheet.Cells [3, 1]; objRange. select (); Microsoft. office. interop. excel. optional tTable objTable = (Microsoft. office. interop. excel. optional tTable) m_objexcelworksheet.w.ttables ("pivot table"); // The Pivot table where data is stored if (Pvalues [0]! = "") {String [] pvX = Pvalues [0]. split ('@'); for (int I = 1; I <= pvX. length; I ++) {Microsoft. office. interop. excel. required tfield objField = (Microsoft. office. interop. excel. extends tfield) objTable. pivotFields (pvX [I-1]. toString (); // value the row data objField. orientation = Microsoft. office. interop. excel. xl1_tfieldorientation. xlRowField; objField. position = "" + I + ""; // indicates the order of the same row data and different row data} if (Pvalues [1]! = "") {String [] pvY = Pvalues [1]. split ('@'); for (int I = 1; I <= pvY. length; I ++) {Microsoft. office. interop. excel. required tfield objFieldY = (Microsoft. office. interop. excel. extends tfield) objTable. pivotFields (pvY [I-1]); // value column data objFieldY. orientation = Microsoft. office. interop. excel. xl1_tfieldorientation. xlColumnField; objFieldY. position = "" + I + "" ;}} if (Pvalues [2]! = "") {String [] pvZ = Pvalues [2]. split ('@'); for (int I = 1; I <= pvZ. length; I ++) {Microsoft. office. interop. excel. required tfield objFieldN = (Microsoft. office. interop. excel. extends tfield) objTable. pivotFields (pvZ [I-1]. toString (); // value the data area data objFieldN. orientation = Microsoft. office. interop. excel. xl1_tfieldorientation. xlDataField; objFieldN. position = "" + I + "" ;}} reverse (strAbsolutePath + strFileName); reverse = false; m_objExcelApp.Workbooks.Close (); m_objExcelApp.Quit (); return strRelativePath + strFileName ;}