Hi,
I have a question.
In a line pivot chart is possible to have a simple line, without marker?
If yes, how?
Thanks in advance
Lisa
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"test.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange dataRange = sheet.Range["A1:C7"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["E10"], cache);
PivotField pf = pt.PivotFields["Product"] as PivotField;
pf.Axis = AxisTypes.Row;
PivotField pf2 = pt.PivotFields["Month"] as PivotField;
pf2.Axis = AxisTypes.Row;
pt.DataFields.Add(pt.PivotFields["Count"], "SUM of Count", SubtotalTypes.Sum);
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
pt.CalculateData();
//create a line chart based on the pivot table
Chart chart = sheet.Charts.Add(ExcelChartType.Line, pt);
//set chart position
chart.TopRow = 19;
chart.LeftColumn = 1;
chart.RightColumn = 5;
chart.BottomRow = 33;
chart.ChartTitle = "Pivot Chart";
//Save the document
string output = "CreatePivotChart.xlsx";
workbook.SaveToFile(output, ExcelVersion.Version2013);
public void CreatePivotChart(DataTable data, string sheetname, string chartname)
{
Workbook reportWorkbook = new Workbook();
reportWorkbook.Worksheets.Clear();
reportWorkbook.Worksheets.Add(sheetname);
reportWorkbook.Worksheets[sheetname].InsertDataTable(data, true, 1, 1);
for (int i = 0; i < reportWorkbook.Worksheets[sheetname].Columns.Count(); i++)
{
reportWorkbook.Worksheets[sheetname].Columns[i].ConvertToNumber();
reportWorkbook.Worksheets[sheetname].Columns[i].NumberFormat = "General";
}
Worksheet sheetReport = reportWorkbook.Worksheets[sheetname];
string pivotSheetName = sheetname + "_PVT";
Worksheet sheetPivot = reportWorkbook.CreateEmptySheet(pivotSheetName);
CellRange dataRange = sheetReport.Range[1, 1, sheetReport.LastRow, sheetReport.LastColumn];
PivotCache cache = reportWorkbook.PivotCaches.Add(dataRange);
PivotTable pt = sheetPivot.PivotTables.Add(chartname, sheetPivot.Range["J1"], cache);
var r1 = pt.PivotFields["Time"];
r1.Axis = AxisTypes.Row;
r1.NumberFormat = "hh:mm:ss";
pt.Options.RowHeaderCaption = "Time";
pt.DataFields.Add(pt.PivotFields[selectedColumn[1]], selectedColumn[1], SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields[selectedColumn[2]], selectedColumn[2], SubtotalTypes.Sum);
Chart chart = sheetPivot.Charts.Add(ExcelChartType.Line, pt);
// Set chart position
chart.Top = 0;
chart.LeftColumn = 1;
chart.RightColumn = 10;
chart.BottomRow = 25;
//chart.Line.Weight = 0.25; // Don't work
//chart.Line.Style = ShapeLineStyleType.LineThinThin; // Don't work
chart.ChartTitle = chartname;
reportWorkbook.SaveToFile("prova.xlsx", ExcelVersion.Version2016);
}
Workbook prova = new Workbook();
prova.LoadFromFile("pivot.xlsx");
Worksheet sheetprova = prova.Worksheets[0];
Worksheet sheet2 = prova.CreateEmptySheet();
sheet2.Name = "Pivot Table";
CellRange dataRangeprova = sheetprova.Range["A1:E40"];
PivotCache cacheprova = prova.PivotCaches.Add(dataRangeprova);
PivotTable ptprova = sheet2.PivotTables.Add("Pivot Table", sheet2.Range["G1"], cacheprova);
var r1prova = ptprova.PivotFields["Time"];
r1prova.Axis = AxisTypes.Row;
r1prova.NumberFormat = "hh:mm:ss";
ptprova.Options.RowHeaderCaption = "Time";
ptprova.DataFields.Add(ptprova.PivotFields["SorterA1 CumulativeThroughput"], "SorterA1 CumulativeThroughput", SubtotalTypes.Sum);
Chart chartprova = sheet2.Charts.Add(ExcelChartType.Line, ptprova);
//set chart position
chartprova.TopRow = 19;
chartprova.BottomRow = 38;
//set chart title
chartprova.ChartTitle = "Pivot Chart";
prova.SaveToFile("CreatePivotChart.xlsx", ExcelVersion.Version2016);
Workbook prova = new Workbook();
prova.LoadFromFile("pivot.xlsx");
Worksheet sheetprova = prova.Worksheets[0];
Worksheet sheet2 = prova.CreateEmptySheet();
sheet2.Name = "Pivot Table";
CellRange dataRangeprova = sheetprova.Range["A1:E40"];
PivotCache cacheprova = prova.PivotCaches.Add(dataRangeprova);
PivotTable ptprova = sheet2.PivotTables.Add("Pivot Table", sheet2.Range["G1"], cacheprova);
var r1prova = ptprova.PivotFields["Time"];
r1prova.Axis = AxisTypes.Row;
r1prova.NumberFormat = "hh:mm:ss";
ptprova.Options.RowHeaderCaption = "Time";
ptprova.DataFields.Add(ptprova.PivotFields["SorterA1 CumulativeThroughput"], "SorterA1 CumulativeThroughput", SubtotalTypes.Sum);
ptprova.DataFields.Add(ptprova.PivotFields["SorterA2 CumulativeThroughput"], "SorterA2 CumulativeThroughput", SubtotalTypes.Sum);
ptprova.CalculateData();
Chart chartprova = sheet2.Charts.Add(ExcelChartType.Line, ptprova);
//set chart position
chartprova.TopRow = 19;
chartprova.BottomRow = 38;
//set chart title
chartprova.ChartTitle = "Pivot Chart";
prova.SaveToFile("CreatePivotChart.xlsx", ExcelVersion.Version2016);