Spire.XLS Pivot table in C#

[Dharam]

Hi ,

I am trying to generate dynamic Pivot table in C# .
1) I wanna set dynamic Range for sheet .
my code - CellRange dataRange = sheet.Range["A1:Y17037"];
As dataset rows generate dynamically so number of rows will be different every tym.How we can set Range[Rows available in data Source]


Thanks in Advance.

[Betsy.jiang]

Dear Dharam,

Thanks for your inquiry.
1. If all data in the sheet is the data that you want to use to create pivot table, you could simply use this code.

Code: Select all

CellRange ranges = sheet.Range[1, 1, sheet.LastRow, sheet.LastColumn];

If not, you could get the column count first from your dataset and then convert it to Excel column name. Please refer to following code:

Code: Select all

            DataTable dt = dataSet.Tables[0];
            int row = dt.Rows.Count;
            int col = dt.Columns.Count;
            string colName = GetExcelColumnName(col);
            //your code...
            string range = String.Format("A1:" + "{0}" + row.ToString(), colName);
            CellRange dataRange = sheet.Range[range];
            //your code...
        }
        private static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;
            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }
            return columnName;
        }

Thanks,
Betsy
E-iceblue support team

[Dharam]

Dear Betsy,

It works
Thank you so much for such a quick reply

[Betsy.jiang]

Dear Dharam,

Thanks for your feedback.
Any question, just feel free to contact us.
Have a nice day.

Sincerely,
Betsy
E-iceblue support team

[Dharam]

Hi ,

Can u help,

1)How to set Classic Pivot Table Layout.
2)How to set All column width to AutoFit.
3)And How to set Border for Pivot Table

I have tried all possible ways but not able to get suitable result.

Thanks in Advance

[Wade.shao]

Dear Dharam,

Thanks for your inquiry.
There are the answers to your questions.
1.You can set Classic Pivot Table Layout with the setting "XlsPivotTable.Options.ShowGridDropZone = true";
2.You can download and test the following code:

Code: Select all

pt.CalculateData();//necessary
    sheet2.AutoFitColumn(1);

3.I will look into it and reply you as soon as possible.Once there's any progress, I will let you know.

Sincerely,
Wade
E-iceblue support team

[Dharam]

Dear Wade,

Thanks for your reply,

But still not able to use the code in right way.

1) "XlsPivotTable.Options.ShowGridDropZone = true";
showing Error: object reference is required for non-static filed

not able to use it with worksheet object(e.g-sheet2.XlsPivotTable.Options.ShowGridDropZone = true) as well as pivot table object(e.g-pt.XlsPivotTable.Options.ShowGridDropZone = true).
2)sheet.AutoFitColumn(1) also not working


FYI using free version of Spire.XLS

[Dharam]

Pls anyone can help me to set classic pivot layout in C#

[Wade.shao]

Dear Dharam,

So sorry for the convenience caused by free version.
Since there are some limitations to this function in free version and we have no plan to update the free version,
I suggest that you use our commercial version(Spire.XLS Pack(Hotfix) Version:8.5.1).
In addition, please refer to the below code:

Code: Select all

//1.set Classic Pivot Table Layout
pt.Options.ShowGridDropZone = true;

//2.autofit columns generated by the pivot table
pt.CalculateData();
sheet.AutoFitColumn(1);

//3.custom the style of border
string styleName = "ptborder";
pt.CustomTableStyleName = styleName;
PivotTableStyle pivotTableStyle = new PivotTableStyle(styleName);
PivotStyle pivotStyle = workbook.CreatePivotStyle();
pivotStyle.Borders[BordersLineType.EdgeLeft].Color = Color.Blue;
pivotStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
pivotStyle.Borders[BordersLineType.EdgeRight].Color = Color.Red;
pivotStyle.Borders[BordersLineType.EdgeTop].Color = Color.Red;
pivotStyle.Borders[BordersLineType.EdgeBottom].Color = Color.Red;
pivotStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thick;
//pivotStyle.Borders.LineStyle = LineStyleType.Thin;

//apply the style where you need to add
pivotTableStyle.Styles.Add(PivotTableElement.wholeTable, pivotStyle);
workbook.AddPivotTableStyle(pivotTableStyle);

Sincerely,
Wade
E-iceblue support team

[Dharam]

Hi Wade ,

Once again thanks for your valuable reply.
You are right that functions are not available to free version.

Somehow "pt.Options.ShowGridDropZone = true; " is working .
But when i open excel sheet ,Classic Style is selected in the sheet but its not reflecting in the View.

Can u help with that , I doubt this one also not available in free version ?

[Wade.shao]

Dear Dharam,

Thanks for your inquiry.
Kindly note that classic layout is just a form of layout. it has nothing to do with the form of display.
When setting the pivot table to be "classic layout", Spire.XLS would show the report in compact form by default. If you need the tabular form, please set the "RowLayout" like below.

Code: Select all

//set Classic Pivot Table Layout
         pt.Options.ShowGridDropZone = true;
         pt.Options.RowLayout = PivotTableLayoutType.Tabular;

Sincerely,
Wade
E-iceblue support team

[Dharam]

Hi Wade

You are just Awesome Buddy . Its works
Thanks you so much .

Can u help me with selecting default filter value
my code--
PivotReportFilter filter = new PivotReportFilter("AssignmentStatus", true);
filter.IsMultipleSelect = false;
List<string> list=new List<string>();
list.Add("BT");
filter.FilterItemStrings = list;
pt.ReportFilters.Add(filter); //code have no issue but not reflecting in excel sheet

I wanna BT should be selected as default value when someone open the sheet.

[Wade.shao]

Dear Dharam,

Thanks for your inquiry.
After an initial test, I could set the filter value successfully with your code. please provide the following information to help us do further investigation.
1.The input file and full code.
2.The system configuration. Eg, Win 7, 64 bit.
3.The region information. Eg, China/Chinese.
Thanks in advance.

Sincerely,
Wade
E-iceblue support team

[Dharam]

1) original file can't share
code- Workbook workbook = new Workbook();
workbook.LoadFromFile("mypath");
Worksheet sheet2 = workbook.CreateEmptySheet();
sheet2.Name = "Sheet Name";
CellRange dataRange = sheet2.Range[1, 1, sheet2.LastRow, sheet2.LastColumn];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet2.PivotTables.Add("Sheet Name", sheet2.Range["A1"], cache);
var a = pt.PivotFields["col name1"];
a.Axis = AxisTypes.Row;
pt.Options.RowHeaderCaption = "Header";

var b = pt.PivotFields["Col name2"];
b.Axis = AxisTypes.Column;

pt.DataFields.Add(pt.PivotFields["Col name3"], "Count of col name3", SubtotalTypes.Count);
PivotReportFilter filter = new PivotReportFilter("AssignmentStatus", true);
filter.IsMultipleSelect = false;
List<string> list=new List<string>();
list.Add("BT");
filter.FilterItemStrings = list;
pt.ReportFilters.Add(filter);

pt.EnableDrilldown = true;

pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;
pt.Options.ShowGridDropZone = true;
------------------------------------------------------------------------

or if i use pt.filter.FieldString="BT" its shows exception The given key was not present in the dictionary..

2)window 7professional,64bit,
3)India

[Betsy.jiang]

Dear Dharam,

Thanks for your information.
After further investigation, I found the property "IsMultipleSelect" should be set as "true" firstly, then set the default value. Yet saving the result will throw the same error "the given key..." like yours, I have posted this issue to our Dev team. We will let you know if there is any update.
Besides, to help us fix your issue fully, you'd better to provide your input file and the expected file you want. You could send it to us(support@e-iceblue.com) via email, we promise to keep it confidential.

Sincerely,
Betsy
E-iceblue support team