Pivot table not coming as tabular

Aspose.Cells Product Family
1

hi,

can anybody help me to bring pivot table columns as Tabular.
i do not want pivot colums to be tree view (+) - it should be shown as tablualr form.

I have attached Sample excel file MLCS_RegExposure_MonthEnd.xls Pivot table is in Buy sheet .

i did create excel filename MLCS_RegExposure_MonthEnd(1).xls using Aspose but pivot columns in Buy sheet does not coming as how it is in sample files Buy sheet.

thanks

2

Hi,


Please see the sample code for your requirements, kindly refer to it and update your codes accordingly. I used your “MLCS_RegExposure_MonthEnd+(1).xlsx” file and update your table in accordance with your desired table in the “MLCS_RegExposure_MonthEnd.xlsx” file.
e.g
Sample code:

var workbook = new Workbook(“e:\test2\MLCS_RegExposure_MonthEnd+(1).xlsx”);
var pivot = workbook.Worksheets[“Buy”].PivotTables[0];
for (int i = 0; i < pivot.BaseFields.Count; i++)
{
PivotField field = (PivotField)pivot.BaseFields[i];
field.DisplayName = field.Name;
field.IsAutoSubtotals = false;
field.ShowInOutlineForm = false;
field.ShowCompact = false;


}


pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;

pivot.ShowDrill = false;

workbook.Save(“e:\test2\out1.xlsx”);


Hope, this help you a bit.

Thank you.
3

thank you very much it works.

how can i make header row style as it is on Buy sheet ( i mean red color style that we have on Buy sheet i want to make simialr RED color Style to the other to sheet header columns.

do we have similar Styletype enum like below .
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;v

4

Hi,


Well, in your other sheets (e.g first sheet), List object/ Table is added (this is MS Excel feature). I checked the style of the list object/ table, it is “Table Style Light 10”. So, you got to add the list object/ table in your other sheets and specify the style of the list object/ table accordingly. I have added a few lines to my previous code after the line i.e…, pivot.ShowDrill = false; it works fine.
e.g
Sample code:


…
pivot.ShowDrill = false;

//Add a list object/ table based on the data and apply table style to the list object/table in the first sheet.
ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
//Add the list object/ table.
listObjects.Add(0, 0, workbook.Worksheets[0].Cells.MaxDataRow, workbook.Worksheets[0].Cells.MaxDataColumn, true);

listObjects[0].TableStyleType = TableStyleType.TableStyleLight10;

workbook.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.
5

This is my C# code for sheet 1.

DataTable dataTable = null;
dataTable = Reports.GetReportPivotData("5", "26-MAR-2014");
Workbook wb = new Workbook();
Worksheet sheet1 = wb.Worksheets[0];
sheet1.Name = "Resale- Caps (Buy)";
sheet1.Cells.ImportDataTable(dt2, true, 4, 0, dataTable.Rows.Count, dataTable.Columns.Count, true, "d-mmm-yyyy");

wheni copy your code it is giving reference error for TableStyleType.
also for ListObjectCollection.

6

Hi,


Please make sure to import Aspose.Cells.Tables namespace before using the List objects\ Tables or TableStyleType. Alternatively, you may use full qualified naming for the APIs e.g
e.g

Aspose.Cells.Tables.ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
…

listObjects[0].TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleLight10;


Hope, this figures out.

Thank you.

7

Thank you very much . last question. how do i change column names of Buy sheet pivot columns header needs to be like how it is in sample excel file. like below.

pivotTable.AddFieldToArea(PivotFieldType.Row, "CLT_SRCS_CD");
pivotTable.RowHeaderCaption = "Source";
pivotTable.AddFieldToArea(PivotFieldType.Row, "CLT_SRCS_BDT");
pivotTable.RowHeaderCaption = "Date";pivotTable.AddFieldToArea(PivotFieldType.Row, "CLT_SRCS_CD");
pivotTable.RowHeaderCaption = "Source";
pivotTable.AddFieldToArea(PivotFieldType.Row, "CLT_SRCS_BDT");
pivotTable.RowHeaderCaption = "Date";

I want to change heading as Sum of Qty for Sum of RAC_ACR_RBT_AMSum of RAC_ACR_RBT_AM

but first columns is coming as Date this code is not working to change columns heading of excel.

Source Date RPO_STRT_DT RPO_TMT_DT TransactionRefNumber GSCC INV Account CUST Account Copper ID Copper Name ML SEC # Cusip ISIN/Sedol EXT_ML_SC_DSC_TX RPO_PR_TY_DSC_TX TERM_TYPE TradeType TradeCategory Fitch Moody's S&P Trader Book ID Trader Book Nm INTERCOMPANY IssuerCountry Sum of Contract Amt Sum of Qty Sum of MVL Sum of Accrued Int Sum of Allow
v
8

Hi,


I think you may try:
e.g
Sample code:

pivot.RowHeaderCaption = “Source”;
pivot.RowFields[1].DisplayName = “Date”;

Thank you.
9

it works but still i can not changed column heading of Pivot data fields

sum of RAC_ORI_CSH_AM to sum of contract
sum of RPO_CLT_ITM_NOM_AMT to sum of qty
sum of RAC_ACR_RBT_AM to sum of accured
sum of RESALE_EXCESS_REPO_HAIRCUT to sum of allowsum of RAC_ORI_CSH_AM to sum of contract
sum of RPO_CLT_ITM_NOM_AMT to sum of qty
sum of RAC_ACR_RBT_AM to sum of accured
sum of RESALE_EXCESS_REPO_HAIRCUT to sum of allow

what will be code for this.

also Grnad Total row how can i filled grand total row with Yellow color.

10

Hi,


Well, you may set Data fields’ display names for your needs, see the sample lines of code below:
e.g
Sample code:

pivot.DataFields[0].DisplayName = “sum of contract”;
pivot.DataFields[1].DisplayName = “sum of qty”;


Thank you.
11

hi
I am trying to add number format but below code is not working.2 issues
i have column in sheet col range 27 to 42 all should have number format #,##0 or 123,233.00.
i have atached file for FYI


1. i want this for both sheet "Resale- Caps (Buy)" and "Resale- Caps (Sell)"

int ii = wb.Styles.Add();
Style stylenum = wb.Styles[ii];
stylenum.Number = 4;
StyleFlag styleFlag = new StyleFlag();
styleFlag.NumberFormat = true;
sheet3.Cells.ApplyColumnStyle(28, stylenum, styleFlag);
sheet3.Cells.ApplyColumnStyle(29, stylenum, styleFlag);
sheet3.Cells.ApplyColumnStyle(30, stylenum, styleFlag);

2. Also i am not able to format CLT_SRSRF_NO column as well this is number without decimal.

12

Hi,


Both of your worksheets have List object / Tables, so you got to use the following sample code to format any column/ range of cells, see the sample code below, please refer to it (I will append a few lines to my previous code segment accordingly here):
e.g
Sample code:

//apply table style to the list object/table in the first sheet.
int ii = workbook.Styles.Add();
Style stylenum = workbook.Styles[ii];
stylenum.Number = 4;
ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
//Add the list object/ table.
listObjects.Add(0, 0, workbook.Worksheets[0].Cells.MaxDataRow, workbook.Worksheets[0].Cells.MaxDataColumn, true);

listObjects[0].TableStyleType = TableStyleType.TableStyleLight10;

//Apply style to the 27th list column’s data range.
for (int i = 0; i < workbook.Worksheets[0].Cells.MaxDataRow; i++)
{
listObjects[0].DataRange[i, 27].SetStyle(stylenum);
}

workbook.Worksheets[0].AutoFitColumn(27);

workbook.Save(“e:\test2\out1.xlsx”);


Hope, this helps a bit.

Thank you.