Pivot chart

[lisa.usai]

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

[rachel.lei]

Hello,

Thanks for your inquiry.
I used the following code to create line-type (without markers) pivot chart, but found the generated chart contains markers, as shown in the attached picture. Are you experiencing the same issue?

Code: Select all

            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);

Anyway, I have posted this issue to our Dev team with the ticket SPIREXLS-2907 for further investigation and fixing. If there is any update, we will inform you. Sorry for the inconvenience caused.

Sincerely,
Rachel
E-iceblue support team

[lisa.usai]

Hi Rachel,
Thanks for your answer!
Yes, I have the same issue.
First I've created a pivot table from a DataTable, then I've created the line-style pivot chart but the generated chart contains markers.
Here the code I've used (with version of Free Spire.Xls n. 10.10.1.61040):

Code: Select all

 
        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);
}



Thanks a lot,
Lisa

[rachel.lei]

Hello,

Thanks for your feedback and providing your code.
If there is any update about this issue, we will let you know.

Sincerely,
Rachel
E-iceblue support team

[lisa.usai]

Hi Rachel,
Have you some update about this issue?

Sincerely

Lisa

[rachel.lei]

Hello,

Thanks for your following up.
Glad to tell you that this issue has been resolved. We will soon provide you with a new version after performing QA and incorporating other enhancements and fixes. Thanks for your patience.

Sincerely,
Rachel
E-iceblue support team

[lisa.usai]

Hi Rachel,
glad to know that the issue has been fixed!

I'll wait for the new version.

Sincerely

Lisa

[rachel.lei]

Hello,

Thanks for your patient waiting.
Glad to inform that we just released Spire.XLS Pack(Hotfix) Version:10.12.0 which fixed the issue of SPIREXLS-2907, please download it from the following links to test.
Website link: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/10.12.0

Sincerely,
Rachel
E-iceblue support team

[lisa.usai]

Hi Rachel,
I've just downloaded the version you suggested me, to test it.
Unfortunately doesn't work.
I've simply replaced the old dll with the new, without modify my code.
I've the following error:
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error143840_01.xml</logFileName><summary>Rilevati errori nel file 'C:\Users\usai\Desktop\pivotnew.xlsx'</summary><removedParts><removedPart>Parte rimossa: Parte /xl/drawings/drawing1.xml. (Forma disegno)</removedPart></removedParts></recoveryLog>

The chart was removed from the excel created (and a new tab Evaluation Warning was added).
Have you any suggestion?

Thanks in advanced

Lisa

[rachel.lei]

Hello,

Thanks for your feedback.
This issue may be related to the data you used to create the chart. To help us investigate it more accurately, please provide the DataTable data you used (or a runnable project that could reproduce your issue). You could send it to us (support@e-iceblue.com) via email.

Besides, regarding the Evaluation Warning message you mentioned, note that Spire.XLS v10.12.0 is our paid version and it will add an evaluation warning message to the result file without applying a valid license. We are very sorry that latest bug fixes will be firstly included in our paid version, and we only upgrade our free version irregularly.

Sincerely,
Rachel
E-iceblue support team

[lisa.usai]

Thank you Rachel.
I've send you a little project via email.

Lisa

[lisa.usai]

Hi Rachel,
debugging my code I found that the problem is the instruction:

r1prova.Axis = AxisTypes.Row;

Without this row the pivot chart is created (though meaningless)
With this row the pivot chart is removed from the file.

Following the code:

Code: Select all

  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);

[rachel.lei]

Hi Lisa,

Thanks for your sharing and sorry for the late reply as weekend.
I tested the following code with Spire.XLS (10.12.0) but found that it works fine. Attached is the Excel file I generated, please check. To help us look into your issue further, please provide your OS information (e.g. Windows7, 64 bit) and region settings (e.g. China, Chinese). Thanks in advance.

Code: Select all

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);

Sincerely,
Rachel
E-iceblue support team

[lisa.usai]

Hi Rachel,
I tested your code, but still don't work!
here my pc settings:
OS: Windows 10 Enterprise 64bit
regional settings: Italy

[rachel.lei]

Hi Lisa,

Thanks for your response.
I tested your case in a similar environment and finally reproduced your issue. I have logged it in our bug tracking system as SPIREXLS-3013. We will let you know if there is any update.
Sorry for the inconvenience caused.

Sincerely,
Rachel
E-iceblue support team