Free Support Forum - aspose.com

Pivot table Related

Hi ,

How can i transfer the data from the Data table in to the pivot table.
Simple data table having four colmun the same data structure i want to add it to the Pivot table.

Please any on can help me out for this.

Thanks

Hi,

Well, I think you may try to extract the datatable: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/importing-data-to-worksheets.html to your worksheet as the data source for your pivot table using Cells.ImportDataTable() method.

Also, kindly check how one can implement a pivot table:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/pivot-table.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/how-to-create-a-pivottable.html

Thank you.

Hi Amjad

I already saw all these thing.

The link which u have posted i tried that its working.

Actually my problem is i have four columns in my data table same structure only i want in the Pivot table.

if i take first column as Row and rest three as data fields its giving some diffrent output i.e. values itself are coming as columns.

Please let me know about this.

or can u tell me how to pass the data table 1 or more rows as data fields ?

Thanks


Hi,

We would appreciate if you could elaborate it more and create a sample test code with the resultant output file (containing the pivot table) to show the issue you are mentioning, we will check it soon. Also, kindly post your expected output file (manually creating in MS Excel containing the pivot table etc.).

Thank you.


Hi

I have attached the data table output and the data which i am getting in the pivot table.

how can i add another two data table columns of data from the data table on to the Pivot table.

Thank you :slight_smile: :slight_smile:

Code:
SqlCommand cmd2 = new SqlCommand(Query, con);
cmd2.CommandTimeout = 1500000;
DataTable paidtable = new DataTable();
SqlDataAdapter adpaid = new SqlDataAdapter(cmd2);
adpaid.Fill(paidtable);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

sheet.Cells.ImportDataTable(paidtable, true, “A1”);

PivotTables pivotTables = sheet.PivotTables;

int index = pivotTables.Add("=A1:D6", “E3”, “PivotTable2”);

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = false;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

pivotTable.AddFieldToArea(PivotFieldType.Data, 1);

pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Report4;

//Saving the Excel file
workbook.Save(“C:\ExcelSheets\Excel” + DateTime.Now.ToString(“yyyyMMddHHmm”) + “.xls”);

Hi,

Thanks for considering Aspose.

I think now we can undestand your need. Kindly try to change your code as follows:

SqlCommand cmd2 = new SqlCommand(Query, con);
cmd2.CommandTimeout = 1500000;
DataTable paidtable = new DataTable();
SqlDataAdapter adpaid = new SqlDataAdapter(cmd2);
adpaid.Fill(paidtable);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

sheet.Cells.ImportDataTable(paidtable, true, "A1");

PivotTables pivotTables = sheet.PivotTables;

int index = pivotTables.Add("=A1:D6", "E3", "PivotTable2");

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = false;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

pivotTable.AddFieldToArea(PivotFieldType.Data, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(PivotFieldType.Data, 3);
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);

pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Report4;

//Saving the Excel file
workbook.Save("C:\\ExcelSheets\\Excel" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");

Thank you.



Hi

Where i can get the document realated to the Pivot Table.
I want to learn all the concept related to the pivot table.

Thank you so much…

:slight_smile: :slight_smile:


Hi,

Kindly go through the documentation topics in the section: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/worthy-features.html for using pivot tables. Hopefully we can add more docs related pivot tables soon.

Feel free to contact us if you need further assistance.

Thank you.



Thank you so much for your Imd reply…

If posiible i want some document which is fully realted to the pivot table concepts.

I saw the all the related information which there in Aspose.com

I want some more data about the Pivot tables.

Thanks.

Hi,

Could you help us to analyze on which specific attributes we should add more topic(s) in the pivot tables documenation. I think you may try to implement some pivot tables manually in MS Excel and check if the ralated api usage is there in the documenation. If there is no help / info related the feature, we will add the related notes first.

Thank you.

Hi

How Can i transfer the data table data in one sheet and Pivot table data in another sheet i tried using the Worksheets class but i am unable to get the output.

Can u please tell me how to do this …

Code is here :

Workbook workbook = new Workbook();


Worksheet sheet = workbook.Worksheets[0];


sheet.Name = “Inpatient Stay Report”;
Cells cells = sheet.Cells;


Cell cell = cells[“A1”];
cell.PutValue(“Report Name”);
cell.Style.Font.IsBold = true;
cell = cells[“B1”];
cell.PutValue(“Inpatient Stay”);


cell = cells[“A2”];
cell.PutValue(“Generated Time”);
cell.Style.Font.IsBold = true;
cell = cells[“B2”];
cell.PutValue(DateTime.Now.ToLongDateString());

cell = cells[“C2”];
cell.PutValue(DateTime.Now.TimeOfDay);

sheet.Cells.ImportDataTable(paidtable, true, “A6”);
//sheet.Hyperlinks.Add(“D1”, 1, 5, “www.Google.com”);


PivotTables pivotTables = sheet.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=A6:D11", “A14”, “Inpatient stay”);
PivotTable pivotTable = pivotTables[index];


//Accessing the instance of the newly added PivotTable



//Unshowing grand totals for rows.

// pivotTable.RowGrand = false;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(PivotFieldType.Data, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(PivotFieldType.Data, 3);
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);


pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Report4;
workbook.Worksheets.ActiveSheetIndex = 1;


workbook.Save(“C:\ExcelSheets\Excel” + DateTime.Now.ToString(“yyyyMMddHHmm”) + “.xls”);

thanks…

Hi,

Well, you are using the same sheet to input source data and for creating pivot table report. You should add a new sheet for the pivot table report.

E.g., I write/adjust a few lines to your code and to add a new sheet for the pivot table report.

Workbook workbook = new Workbook();

//Get the first worksheet i.e. the default sheet that works as a source sheet.

//The sheet name would be 'Sheet1' by default.
Worksheet sheet = workbook.Worksheets[0];

Cells cells = sheet.Cells;


Cell cell = cells["A1"];
cell.PutValue("Report Name");
cell.Style.Font.IsBold = true;
cell = cells["B1"];
cell.PutValue("Inpatient Stay");


cell = cells["A2"];
cell.PutValue("Generated Time");
cell.Style.Font.IsBold = true;
cell = cells["B2"];
cell.PutValue(DateTime.Now.ToLongDateString());

cell = cells["C2"];
cell.PutValue(DateTime.Now.TimeOfDay);
//Import data to the first sheet i.e. 'Sheet1'
sheet.Cells.ImportDataTable(paidtable, true, "A6");
//sheet.Hyperlinks.Add("D1", 1, 5, www.Google.com);

//Add a new worksheet to the workbook.

Worksheet newSheet = workbook.Worksheets[workbook.Worksheets.Add()];

//Name the worksheet.

newSheet.Name = "Inpatient Stay Report";


PivotTables pivotTables = newSheet.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=Sheet1!A6:D11", "A14", "Inpatient stay");
PivotTable pivotTable = pivotTables[index];


//Accessing the instance of the newly added PivotTable

//Unshowing grand totals for rows.

// pivotTable.RowGrand = false;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(PivotFieldType.Data, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(PivotFieldType.Data, 3);
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);


pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Report4;
workbook.Worksheets.ActiveSheetIndex = 1;


workbook.Save("C:\\ExcelSheets\\Excel" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");

Thank you.

thank you…:slight_smile: