private static void MakeExcel(
string pivotStyle,
string tableStyle,
XlChartType charType,
DataTable dataSource,
Excel.Workbook eWorkbook,
int counter,
string sheetTitle,
string pivotTableName,
List <string> pivotRowFields,
List <string> pivotValueFields,
List <string> pivotColumnFields,
List <string> pivotReportFields,
List <string> sliderFields)
{
var startRow = 1;
var endIndex = dataSource.Rows.Count + startRow;
var sheets = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets;
var sheet = (Excel.Worksheet)sheets.Add();
sheet.Name = sheetTitle + (++counter);
for (var i = 1; i < dataSource.Columns.Count + 1; i++)
{
sheet.Cells[startRow, i] = dataSource.Columns[i - 1].ColumnName;
if (dataSource.Columns[i - 1].DataType == Type.GetType("System.DateTime"))
{
sheet.Range[sheet.Cells[startRow + 1, i], sheet.Cells[endIndex, i]].NumberFormat =
Cthread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
}
else if (dataSource.Columns[i - 1].DataType == Type.GetType("System.Decimal"))
{
sheet.Range[sheet.Cells[startRow + 1, i], sheet.Cells[endIndex, i]].NumberFormat =
$"#{Cthread.CurrentThread.CurrentCulture.NumberFormat.NumberGroupSeparator}##0{Cthread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator}00";
}
}
try
{
sheet.Range[sheet.Cells[startRow + 1, 1], sheet.Cells[endIndex, dataSource.Columns.Count]].Value2 =
General.Convert(dataSource);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
General.FormatAsTable(sheet.Range[sheet.Cells[startRow, 1], sheet.Cells[endIndex, dataSource.Columns.Count]],
"SourceData" + counter, tableStyle, false);
//eApplication.Visible = true;
//sheet.Activate();
//sheet.Application.ActiveWindow.SplitRow = 1;
//sheet.Application.ActiveWindow.SplitColumn = 1;
//sheet.Application.ActiveWindow.FreezePanes = true;
//// Now apply autofilter
//var firstRow = (Excel.Range)sheet.Rows[1];
//firstRow.AutoFilter(1,
// Type.Missing,
// Excel.XlAutoFilterOperator.xlAnd,
// Type.Missing,
// true);
sheet.Range[sheet.Cells[startRow, 1], sheet.Cells[endIndex, dataSource.Columns.Count]].Columns.AutoFit();
//sheet.Columns.AutoFit();
var pivotData = sheet.Range["A1",
General.GetColumnName(dataSource.Columns.Count) + (dataSource.Rows.Count + 1)];
var pivotWorkSheet = General.AddPivot(
pivotStyle,
pivotData,
dataSource,
counter,
pivotTableName,
pivotRowFields,
pivotColumnFields,
pivotValueFields,
pivotReportFields);
General.AddChart(pivotWorkSheet, sheetTitle, pivotData, charType);
General.AddSlicers(eWorkbook, (Excel.PivotTable)pivotWorkSheet.PivotTables(pivotTableName),
pivotWorkSheet, sliderFields);
pivotWorkSheet.Select();
//Excel.Application oApp = Globals.ThisAddIn.Application;
//oApp.Visible = true;
//oApp.ScreenUpdating = true;
//oApp.UserControl = true;
//oApp.Interactive = true;
}
