/// <summary>C:\Users\User\source\repos\ExcelWorkVariances\ExcelDataHandler\packages.config
/// Method creates Pivot table
/// </summary>
/// <param name="worksheet"></param>
public static void CreateMyPivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
Excel.Worksheet secondWorksheet = workbook.Sheets.Add();
try
{
secondWorksheet.Name = "pivot_table";
app.ActiveWindow.DisplayGridlines = false;
Excel.Range oRange = worksheet.UsedRange;
Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet
Excel.PivotCaches pch = workbook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence", Type.Missing, Type.Missing); // Create Pivot table
Excel.PivotTable pvt = secondWorksheet.PivotTables("Confidence");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("ID"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("CATEGORY"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PLACE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("NAME"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PRICE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("UNITS"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
secondWorksheet.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
app.DisplayAlerts = false;
secondWorksheet.Activate();
secondWorksheet.get_Range("B1", "B1").Select();
worksheet.Activate();
}
catch (Exception)
{
}
}
public static void CreatePivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
Excel.Worksheet secondWorksheet = (Worksheet)workbook.Sheets.Add();
try
{
Excel.Range last = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = worksheet.get_Range("G1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
Excel.Range oResizeRange = worksheet.Range[worksheet.Cells[1, 7],
worksheet.Cells[lastUsedRow, lastUsedColumn]];
secondWorksheet.Name = "pivot_table";
app.ActiveWindow.DisplayGridlines = false;
//Excel.Range oRange = worksheet.UsedRange;
Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange); // Set the Source data range from First sheet
Excel.PivotCaches pch = workbook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence and Latency", Type.Missing, Type.Missing); // Create Pivot table
Excel.PivotTable pvt = (Excel.PivotTable)secondWorksheet.PivotTables("Labels");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Confidence"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("Latency"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
secondWorksheet.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
app.DisplayAlerts = false;
secondWorksheet.Activate();
secondWorksheet.get_Range("B1", "B1").Select();
}
catch (Exception)
{
// ignored
}
//worksheet.Select();
worksheet.Activate();
}
//this method will create pivot table in excel file
public string OfficeDll()
{
string filepath = System.Web.HttpContext.Current.Server.MapPath("~/Content/ProductReport.xlsx");
int rows = 0;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(filepath);
Excel.Worksheet excelworksheet = excelWorkBook.ActiveSheet;
Excel.Worksheet sheet2 = excelWorkBook.Sheets.Add();
try
{
sheet2.Name = "Pivot Table";
excelApp.ActiveWindow.DisplayGridlines = false;
Excel.Range oRange = excelworksheet.UsedRange;
Excel.PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet
Excel.PivotCaches pch = excelWorkBook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[3, 3], "PivTbl_2", Type.Missing, Type.Missing); // Create Pivot table
Excel.PivotTable pvt = sheet2.PivotTables("PivTbl_2");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("CATEGORY"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PLACE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("NAME"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PRICE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("NoOfUnits"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
sheet2.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
excelApp.DisplayAlerts = false;
excelworksheet.Delete();
sheet2.Activate();
sheet2.get_Range("B1", "B1").Select();
excelWorkBook.SaveAs(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelApp.DisplayAlerts = false;
excelWorkBook.Close(0);
excelApp.Quit();
Marshal.ReleaseComObject(excelWorkBook);
Marshal.ReleaseComObject(excelApp);
}
catch (Exception ex)
{
excelWorkBook.Close(0);
excelApp.Quit();
Marshal.ReleaseComObject(excelWorkBook);
Marshal.ReleaseComObject(excelApp);
return(ex.Message);
}
return(filepath);
}