Hi hemma123,
Please refer the below code. You need to modify as per your requirement.
C#
private void GeData()
{
string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=192.168.0.1\SQL2005;DataBase=Test;UID=sa;PWD=pass@123";
string command = "SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5 FROM PivotData";
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing);
Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
Excel.PivotCache pivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, (Excel.Range)sheet.get_Range("A1", "E10"));
pivotCache.Connection = connection;
pivotCache.MaintainConnection = true;
pivotCache.CommandText = command;
pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Type.Missing);
Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, app.ActiveCell, "PivotTable1", Type.Missing, Type.Missing);
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
Excel.PivotFields rowField = (Excel.PivotFields)pivotTable.PivotFields(Type.Missing);
int fieldCount = rowField.Count;
for (int i = 1; i <= fieldCount; i++)
{
if ("Colunm" + i != "Colunm2" && "Colunm" + i != "Colunm5")
{
Excel.PivotField field = (Excel.PivotField)pivotTable.PivotFields("Column" + i);
field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
}
}
pivotTable.AddDataField(pivotTable.PivotFields("Column4"), "Sum of Column4", Excel.XlConsolidationFunction.xlSum);
}
Vb.Net
Private Sub GeData()
Dim connection As String = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123"
Dim command As String = "SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5 FROM PivotData"
Dim app As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Excel.Workbook = DirectCast(app.Workbooks.Add(Type.Missing), Microsoft.Office.Interop.Excel.Workbook)
Dim sheet As Excel.Worksheet = DirectCast(workbook.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
Dim pivotCache As Excel.PivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, DirectCast(sheet.Range("A1", "E10"), Excel.Range))
pivotCache.Connection = connection
pivotCache.MaintainConnection = True
pivotCache.CommandText = command
pivotCache.CommandType = Excel.XlCmdType.xlCmdSql
Dim pivotTables As Excel.PivotTables = DirectCast(sheet.PivotTables(Type.Missing), Excel.PivotTables)
Dim pivotTable As Excel.PivotTable = pivotTables.Add(pivotCache, app.ActiveCell, "PivotTable1", Type.Missing, Type.Missing)
pivotTable.SmallGrid = False
pivotTable.ShowTableStyleRowStripes = True
pivotTable.TableStyle2 = "PivotStyleLight1"
Dim rowField As Excel.PivotFields = DirectCast(pivotTable.PivotFields(Type.Missing), Excel.PivotFields)
Dim fieldCount As Integer = rowField.Count
For i As Integer = 1 To fieldCount
If "Colunm" & i <> "Colunm2" AndAlso "Colunm" & i <> "Colunm5" Then
Dim field As Excel.PivotField = DirectCast(pivotTable.PivotFields("Column" & i), Excel.PivotField)
field.Orientation = Excel.XlPivotFieldOrientation.xlRowField
End If
Next
pivotTable.AddDataField(pivotTable.PivotFields("Column4"), "Sum of Column4", Excel.XlConsolidationFunction.xlSum)
End Sub