We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Pivot table not coming as tabular

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

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.

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

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.

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.

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.

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

Hi,


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

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

Thank you.

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.

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.

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.

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.