How to create Slicer in excel 2013 using aspose.cells

Aspose.Cells Product Family
1

Hi,


I want to create a slicer in excel 2013 using aspose.cells.
If I create a table and slicer manually and then try loading data using aspose.cells, I get invalid content error. So it looks like I need to load data, create a table using aspose.cells and then create slicer. Can you help me doing it.

Davinder Singh
Deloitte USI

2

Hi,

Thanks for your posting and using Aspose.Cells.

It is not currently possible to create slicer using Aspose.Cells API nor you can manipulate the existing slicer. However, you can load your source excel file containing your existing slicer with Aspose.Cells API fine.

If you are having a problem loading the excel file with slicer, then please provide it to us, we will look into it and fix the bug.

Please also download and try the latest version: Aspose.Cells for .NET (Latest Version) and see if it makes any difference and resolves this issue of loading excel file with existing slicer.

3

Hi,


Thanks for a quick reply.

What I understand is that we need to have data in table/pivot in order to create a slicer.
I have a blank excel template to begin with. I need to load data inside it using Aspose and then I also need to have slicer in it. Can you please suggest how to proceed with this.

-Davinder Singh
4

Hi Davinder,

As discussed earlier, Aspose.Cells APIs are currently not capable of creating, manipulating or refreshing the Slicers. However, if the input spreadsheet has a slicer that could be built manually then Aspose.Cells APIs can retain it while saving back the spreadsheet.

5

I have created a slicer manually in a excel template, attached here.


I am using the below code. However I receive corrupted excel after code is executed.

-------------------------------------------------------------------------------------------------------------

protected void lnkReport_Click(object o, EventArgs e)
{
string importFile = string.Empty;
string wbookName = string.Empty;

int insertRowIndex = 10;
DataTable dt = null;

importFile = CommonHelpers.GetServerPathForTemplate(taxyear, “POC.xlsx”);

Workbook wbook = new Workbook(importFile);
Worksheet wsheet1 = wbook.Worksheets[0];
wbookName = “POC_test.xlsx”;

Cells cells = wsheet1.Cells;

//Adding data to excel
Cell cell = cells[“A10”];
cell.PutValue(“1000”);
cell = cells[“B10”];
cell.PutValue(“Sample Partner 1”);
cell = cells[“C10”];
cell.PutValue(“40000”);
cell = cells[“D10”];
cell.PutValue(“Sample Investment 1”);
cell = cells[“E10”];
cell.PutValue(“400001”);
cell = cells[“F10”];
cell.PutValue(“Sample Tranche 1A”);
cell = cells[“G10”];
cell.PutValue(“500”);
cell = cells[“H10”];
cell.PutValue(“ADD”);
cell = cells[“I10”];
cell.PutValue(“5 - 5 - Interest Income”);
cell = cells[“J10”];
cell.PutValue(“4800”);

cell = cells[“A11”];
cell.PutValue(“2000”);
cell = cells[“B11”];
cell.PutValue(“Sample Partner 2”);
cell = cells[“C11”];
cell.PutValue(“42000”);
cell = cells[“D11”];
cell.PutValue(“Sample Investment 2”);
cell = cells[“E11”];
cell.PutValue(“420001”);
cell = cells[“F11”];
cell.PutValue(“Sample Tranche 2A”);
cell = cells[“G11”];
cell.PutValue(“500”);
cell = cells[“H11”];
cell.PutValue(“ADD”);
cell = cells[“I11”];
cell.PutValue(“5 - 5 - Interest Income”);
cell = cells[“J11”];
cell.PutValue(“500”);

//creating table of data
Aspose.Cells.Tables.ListObjectCollection listObjects = wsheet1.ListObjects;
listObjects.Add(8, 0, wsheet1.Cells.MaxDataRow, wsheet1.Cells.MaxDataColumn, true);

int maxdatacol = wsheet1.Cells.MaxDataColumn;
int maxdatarow = wsheet1.Cells.MaxDataRow;

string MaxCellname = CellsHelper.CellIndexToName(maxdatarow, maxdatacol);
string pivotRange = “=Sample!A9:” + MaxCellname;


//Adding a new sheet for Pivot
Worksheet sheet2 = wbook.Worksheets[wbook.Worksheets.Add()];
sheet2.Name = “Pivot”;
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
int index = pivotTables.Add(pivotRange, “B3”, “PivotTable1”);
//creating Pivot
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 8);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 9);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 5);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 7);



wbook.Worksheets.ActiveSheetIndex = wbook.Worksheets[“Sample”].Index;
CommonHelpers.PrepareWorkBook(ref wbook).Save(this.Response, wbookName, ContentDisposition.Attachment, new OoxmlSaveOptions());
this.Response.Flush();
this.Response.End();
}
6

Hi Davinder,

Thank you for providing the code and the sample spreadsheet.

We have noticed that you are adding a new ListObject on the same location as of an existing ListObject. Due to this reason, the resultant spreadsheet becomes corrupted and the Excel repair procedure removes the contents from table2.xml file to correct the references.

Please modify the code as following to avoid this problem.

C#

//creating table of data
Aspose.Cells.Tables.ListObjectCollection listObjects = wsheet1.ListObjects;

//Instead of adding a new ListObject, resize the existing ListObject to accommodate the newly inserted data
//listObjects.Add(8, 0, wsheet1.Cells.MaxDataRow, wsheet1.Cells.MaxDataColumn, true);
listObjects[0].Resize(8, 0, wsheet1.Cells.MaxDataRow, wsheet1.Cells.MaxDataColumn, true);
7

Hi,


I have made the suggested change, but still I am getting the same error. Could it be the problem with the excel template? Aspose.cells version is 8.5.0.0.

-Davinder
8

Ok, problem was the Aspose.cells version. Using 8.5.2.0, it is working fine.

Thank you.

-Davinder Singh
9

Hi,


Good to know that your issue is sorted out by the latest version/fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
10

Hi,


I want to save the above excel file in excel binary format (xlsb). For that I am making below 3 changes in the given code-

importFile = CommonHelpers.GetServerPathForTemplate(taxyear, “POC.xlsb”);

wbookName = “POC_test.xlsb”;

CommonHelpers.PrepareWorkBook(ref wbook).Save(this.Response, wbookName, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsb));

But I am getting error when i open the saved file. Is there anything else I need to do?

-Davinder Singh
11

Hi,


Thanks for the template file.

After an initial test, I was able to find the issue by using your template POC.xlsx file with our latest version/fix: Aspose.Cells for .NET v8.6.0. I used the following sample code, the output file is not opened fine into MS Excel 2010, Ms Excel prompts “Excel found unreadable content…” error and removes a part of Slicer control:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\POC.xlsx”);
workbook.Save(“e:\test2\out1.xlsb”, SaveFormat.Xlsb);

I have logged a ticket with an id “CELLSNET-43921” for your issue. We will look into your issue to figure it out soon.

Thank you.


12

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-43921 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

Please note we have fixed the issue of corrupted file. But we do not support slicers in xlsb file.


13

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.6.0.1 (attached).

We have fixed the issue of corrupted file but
the slicers are missing as we do not support slicers in the XLSB file format.

Thank you.
14

Hi Amjad,


Infact, after using this version, I found there is no error when opening binary excel file but also I can see the existing slicers are still present in the binary excel file. This is good but I need to confirm if this is expected behavior because your last reply says that even existing slicers will not work in case of binary excel file. I have attached the output binary excel file.

-Davinder
15

Hi,


Thanks for the sample XLSB file.

You are right as I tested as well. The slicers are retained in the template file. You may use the fix and let us know if you find any issue regarding slicers (e.g its functionality or integration with data etc. in the template file in MS Excel). I have also intimated the product team to get complete insight and details on it.

We will get back to you soon.

Thank you.
16

Hi,


Well, slicers are retained in the template file when you open and re-save the Excel file. Please ignore my comment in one of my previous post as we meant the Slicer feature (creating or manipulate Slicers via APIs) is still not supported.

If you find any issue regarding existing slicers in the template file, let us know, we will check it soon.

Thank you.
17

Hi


Is this functionality in the pipeline for release. Would be very useful to have.

Cheers
18

Hi Stephen Jackett,


Well, I am afraid, creating or manipulating Slicers are not supported yet, the feature is also not going to be supported in near future.

We are sorry for any inconvenience caused!
19

Has there been any additional update to create/manipulating slicer in Excel?

20

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid there is no update for you at this moment regarding this issue. However, we have logged your comment in our database against this issue and requested the product team to provide some fix or ETA for this issue. Once there is some news for you, we will update you asap by posting in this thread.