C# EXCEL 透视表使用 多行多列的导出透视表
public static string ExportToProvit(System.Web.UI.Page objPage, string[] Pvalues){// Pvalues[0]=行区域数据,Pvalues[1]=列区域数据,Pvalues[2]=数据区域数据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 strAbsolutePath = 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.PivotCaches objPivot = m_objExcelWorkBook.PivotCaches();objPivot.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, "总数据!R1C1:R" + row + "C" + col + "").CreatePivotTable(m_objExcelWorkSheet.Cells[3, 1], "透视表", Type.Missing, Type.Missing);//"总数据!R2C1:R52C30" 原数据范围SHEET名称!R起始行C起始列:R数据总行数C数据总列数Microsoft.Office.Interop.Excel.Range objRange = (Microsoft.Office.Interop.Excel.Range)m_objExcelWorkSheet.Cells[3, 1];objRange.Select();Microsoft.Office.Interop.Excel.PivotTable objTable = (Microsoft.Office.Interop.Excel.PivotTable)m_objExcelWorkSheet.PivotTables("透视表"); //数据存放的透视表if (Pvalues[0] != ""){string[] pvX=Pvalues[0].Split('@');for (int i = 1; i <= pvX.Length; i++){Microsoft.Office.Interop.Excel.PivotField objField = (Microsoft.Office.Interop.Excel.PivotField)objTable.PivotFields(pvX[i-1].ToString()); //赋值行数据objField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;objField.Position = "" + i + ""; //表示同为行数据,不同的行数据的顺序}}if (Pvalues[1] != ""){string[] pvY = Pvalues[1].Split('@');for (int i = 1; i <= pvY.Length; i++){Microsoft.Office.Interop.Excel.PivotField objFieldY = (Microsoft.Office.Interop.Excel.PivotField)objTable.PivotFields(pvY[i-1]); //赋值列数据objFieldY.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;objFieldY.Position = ""+i+"";}}if (Pvalues[2] != ""){string[] pvZ = Pvalues[2].Split('@');for (int i = 1; i <= pvZ.Length; i++){Microsoft.Office.Interop.Excel.PivotField objFieldN = (Microsoft.Office.Interop.Excel.PivotField)objTable.PivotFields(pvZ[i-1].ToString()); //赋值数据区域数据objFieldN.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;objFieldN.Position = ""+i+"";}}m_objExcelWorkBook.SaveCopyAs(补充:软件开发 , C# ,
上一个:C#查看、创建和删除系统任务计划程序
下一个:在C#中什么时候用分号?