public static string ExportToProvit(System.Web.UI.Page objPage, string[] Pvalues)
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;
}
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(strAbsolutePath + strFileName);
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
return strRelativePath + strFileName;
}
{
// 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;
}
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(strAbsolutePath + strFileName);
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
return strRelativePath + strFileName;
}