Hello,
I created a new PivotTable in an existing workbook which contains a worksheet for data source and a worksheet for the new pivot table.
I saved the workbook as xlsx file and Html file.
The html file lose style and format.
below my code for creating the pivot table:
public static void CreatePivotTable(string inputFileName) {
var workbook = new Workbook(inputFileName);
var worksheet = workbook.Worksheets["PivotTable"];
worksheet.IsGridlinesVisible = false;
var titleCell = worksheet.Cells["E2"];
var style = titleCell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.FromArgb(201, 201, 201);
style.BackgroundColor = style.ForegroundColor;
style.Font.Size = 22;
style.Font.Name = "Calibri";
style.Font.Color = Color.White;
titleCell.SetStyle(style);
worksheet.Cells["E2"].PutValue("Synthesis FO");
worksheet.Cells.Merge(1, 4, 4, 3);
int index = worksheet.PivotTables.Add("=Data!A1:BC411", "B7", "PivotTable");
var pivotTable = worksheet.PivotTables[index];
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium4;
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;
pivotTable.ShowDrill = false;
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.AddFieldToArea(PivotFieldType.Row, "Desk");
pivotTable.AddFieldToArea(PivotFieldType.Row, "Action");
pivotTable.AddFieldToArea(PivotFieldType.Row, "ExDate");
pivotTable.AddFieldToArea(PivotFieldType.Row, "OstType");
pivotTable.AddFieldToArea(PivotFieldType.Row, "ReInvestFacility");
pivotTable.AddFieldToArea(PivotFieldType.Row, "CodeBBG");
pivotTable.AddFieldToArea(PivotFieldType.Data, "Size");
pivotTable.AddFieldToArea(PivotFieldType.Data, "SizeTd");
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
int indexFilter1 = pivotTable.AddFieldToArea(PivotFieldType.Page, "LockedPtf");
int indexFilter2 = pivotTable.AddFieldToArea(PivotFieldType.Page, "ExcludedOstType");
int indexFilter3 = pivotTable.AddFieldToArea(PivotFieldType.Page, "DiffExDate");
int indexFilter4 = pivotTable.AddFieldToArea(PivotFieldType.Page, "Message");
var deskField = pivotTable.RowFields["Desk"];
deskField.InsertBlankRow = true;
deskField.ShowInOutlineForm = false;
deskField.SetSubtotals(PivotFieldSubtotalType.None, true);
deskField.IsAutoSort = true;
deskField.IsAscendSort = true;
var actionField = pivotTable.RowFields["Action"];
actionField.ShowInOutlineForm = true;
actionField.ShowCompact = true;
actionField.InsertBlankRow = true;
actionField.SetSubtotals(PivotFieldSubtotalType.None, true);
var exDateField = pivotTable.RowFields["ExDate"];
exDateField.ShowInOutlineForm = false;
exDateField.SetSubtotals(PivotFieldSubtotalType.None, true);
exDateField.IsAutoSort = true;
exDateField.IsAscendSort = true;
exDateField.InsertBlankRow = false;
exDateField.NumberFormat = "dd-mmm-yy";
var ostTypeField = pivotTable.RowFields["OstType"];
ostTypeField.ShowInOutlineForm = false;
ostTypeField.SetSubtotals(PivotFieldSubtotalType.None, true);
ostTypeField.InsertBlankRow = false;
var reifField = pivotTable.RowFields["ReInvestFacility"];
reifField.ShowInOutlineForm = false;
reifField.SetSubtotals(PivotFieldSubtotalType.None, true);
reifField.InsertBlankRow = false;
var codeBBGField = pivotTable.RowFields["CodeBBG"];
codeBBGField.ShowInOutlineForm = false;
codeBBGField.SetSubtotals(PivotFieldSubtotalType.None, true);
codeBBGField.IsAutoSort = true;
codeBBGField.IsAscendSort = true;
codeBBGField.InsertBlankRow = false;
var sizeField = pivotTable.DataFields["Size"];
sizeField.DragToColumn = true;
sizeField.DisplayName = "Size";
sizeField.NumberFormat = "#,##0";
var sizeTdField = pivotTable.DataFields["SizeTd"];
sizeTdField.DragToColumn = true;
sizeTdField.DisplayName = "SizeTd";
sizeTdField.NumberFormat = "#,##0";
pivotTable.PageFields[indexFilter1].CurrentPageItem = 0;
pivotTable.PageFields[indexFilter2].CurrentPageItem = 1;
pivotTable.PageFields[indexFilter4].CurrentPageItem = 0;
worksheet.AutoFitColumns();
pivotTable.RefreshData();
pivotTable.CalculateData();
string outputXlsxFileName = string.Format(@"{0}\PivotSampleOut.xlsx", Directory.GetParent(Environment.CurrentDirectory).Parent.FullName);
string outputHtmlFileName = string.Format(@"{0}\PivotSampleOut.html", Directory.GetParent(Environment.CurrentDirectory).Parent.FullName);
workbook.Save(outputXlsxFileName, SaveFormat.Xlsx);
workbook.Save(outputHtmlFileName, new HtmlSaveOptions(SaveFormat.Html) { ExportActiveWorksheetOnly = true });
}
Thanks.
@Oussama_Mokni,
Thanks for the sample code and details.
Could you provide your template Excel file (you may zip the input file prior attaching), we will check it soon.
I can not join the excel template file so I created a csv file which contains data and i updated my code to load the data from the csv file into a datatable, after I create a new workbook with two worksheets, one worksheet for data where i export the datatable, and one worksheet for the pivot table.
Please find attached :
-
the input csv file which contains the data. (Data.csv)
-
the html output file (PivotSampleOut.html)
-
the xlsx output file (PivotSampleOut.xlsx)
-
the sample code (program.cs)
class Program
{
static void Main(string[] args)
{
License license = new License();
license.SetLicense(“Aspose.Total.lic”);
string datafileName = string.Format(@"{0}\Data.csv", Directory.GetParent(Environment.CurrentDirectory).Parent.FullName);
var workbook = new Workbook(datafileName, new TxtLoadOptions { Separator = ';' });
var worksheet = workbook.Worksheets[workbook.Worksheets.ActiveSheetIndex];
var data = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, new ExportTableOptions() { ExportColumnName = true });
CreatePivotTable (data);
}
public static void CreatePivotTable(DataTable data)
{
var workbook = new Workbook();
var dataSourceWorksheet = workbook.Worksheets.Add("Data");
dataSourceWorksheet.Cells.ImportDataTable(data, true, 0, 0);
var pivotWorksheet = workbook.Worksheets.Add("PivotTable");
// Title
var titleCell = pivotWorksheet.Cells["E2"];
var style = titleCell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.FromArgb(150, 54, 52);
style.BackgroundColor = style.ForegroundColor;
style.Font.Size = 22;
style.Font.Name = "Calibri";
style.Font.Color = Color.White;
titleCell.SetStyle(style);
pivotWorksheet.Cells["E2"].PutValue("Synthesis FO");
pivotWorksheet.Cells.Merge(1, 4, 4, 3);
pivotWorksheet.IsGridlinesVisible = false;
pivotWorksheet.AutoFitColumns();
// Pivot Table
int index = pivotWorksheet.PivotTables.Add("=Data!A1:N411", "B7", "PivotTable");
var pivotTable = pivotWorksheet.PivotTables[index];
// Rows
pivotTable.AddFieldToArea(PivotFieldType.Row, "Desk");
pivotTable.AddFieldToArea(PivotFieldType.Row, "Action");
pivotTable.AddFieldToArea(PivotFieldType.Row, "ExDate");
pivotTable.AddFieldToArea(PivotFieldType.Row, "OstType");
pivotTable.AddFieldToArea(PivotFieldType.Row, "ReInvestFacility");
pivotTable.AddFieldToArea(PivotFieldType.Row, "CodeBBG");
// Data
pivotTable.AddFieldToArea(PivotFieldType.Data, "Size");
pivotTable.AddFieldToArea(PivotFieldType.Data, "SizeTd");
// Filters
pivotTable.AddFieldToArea(PivotFieldType.Page, "LockedPtf");
pivotTable.AddFieldToArea(PivotFieldType.Page, "ExcludedOstType");
pivotTable.AddFieldToArea(PivotFieldType.Page, "DiffExDate");
pivotTable.AddFieldToArea(PivotFieldType.Page, "Message");
// Columns
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
var deskField = pivotTable.RowFields["Desk"];
deskField.SetSubtotals(PivotFieldSubtotalType.None, true);
deskField.InsertBlankRow = true;
deskField.ShowInOutlineForm = false;
deskField.IsAutoSort = true;
deskField.IsAscendSort = true;
var actionField = pivotTable.RowFields["Action"];
actionField.SetSubtotals(PivotFieldSubtotalType.None, true);
actionField.InsertBlankRow = true;
actionField.ShowInOutlineForm = true;
actionField.ShowCompact = true;
var exDateField = pivotTable.RowFields["ExDate"];
exDateField.SetSubtotals(PivotFieldSubtotalType.None, true);
exDateField.InsertBlankRow = false;
exDateField.ShowInOutlineForm = false;
exDateField.IsAutoSort = true;
exDateField.IsAscendSort = true;
exDateField.NumberFormat = "dd-mmm-yy";
var ostTypeField = pivotTable.RowFields["OstType"];
ostTypeField.SetSubtotals(PivotFieldSubtotalType.None, true);
ostTypeField.InsertBlankRow = false;
ostTypeField.ShowInOutlineForm = false;
var reifField = pivotTable.RowFields["ReInvestFacility"];
reifField.SetSubtotals(PivotFieldSubtotalType.None, true);
reifField.InsertBlankRow = false;
reifField.ShowInOutlineForm = false;
var codeBBGField = pivotTable.RowFields["CodeBBG"];
codeBBGField.SetSubtotals(PivotFieldSubtotalType.None, true);
codeBBGField.ShowInOutlineForm = false;
codeBBGField.IsAutoSort = true;
codeBBGField.IsAscendSort = true;
codeBBGField.InsertBlankRow = false;
var sizeField = pivotTable.DataFields["Size"];
sizeField.DragToColumn = true;
sizeField.DisplayName = "Size";
sizeField.NumberFormat = "#,##0";
var sizeTdField = pivotTable.DataFields["SizeTd"];
sizeTdField.DragToColumn = true;
sizeTdField.DisplayName = "SizeTd";
sizeTdField.NumberFormat = "#,##0";
pivotTable.PageFields["LockedPtf"].CurrentPageItem = 0;
pivotTable.PageFields["ExcludedOstType"].CurrentPageItem = 1;
pivotTable.PageFields["Message"].CurrentPageItem = 0;
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium3;
pivotTable.ShowDrill = false;
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.RefreshData();
pivotTable.CalculateData();
workbook.Worksheets.ActiveSheetIndex = pivotWorksheet.Index;
string outputXlsxFileName = string.Format(@"{0}\PivotSampleOut.xlsx", Directory.GetParent(Environment.CurrentDirectory).Parent.FullName);
string outputHtmlFileName = string.Format(@"{0}\PivotSampleOut.html", Directory.GetParent(Environment.CurrentDirectory).Parent.FullName);
workbook.Save(outputXlsxFileName, SaveFormat.Xlsx);
workbook.Save(outputHtmlFileName, new HtmlSaveOptions(SaveFormat.Html) { ExportActiveWorksheetOnly = true });
}
}
Hello,
Attached a sample project.
I added a new csv file for tests (Data2.csv), when i use the data in, i have an index out of range exception when i CalculateData().
With the file Data.csv, I have no exception but the data, the format and the style in the output html file are wrong.
Regards,
Oussama
@Oussama_Mokni,
We were able to observe the issue where formatting/style is different than the XLSX file while using Data.csv in your post here. Although you are using quite an older version which creates HTML with lot of differences as compared to latest version 19.4.x, but as still few issues are there, we have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-46733 - Style/Format lost while saving Pivot Table as HTML
Regarding your second issue, we were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-46734 - Exception while calculating PivotTable
P.S. We do not rectify issues in the previous versions, therefore these issues will be resolved in our upcoming new releases. You will require to upgrade to new versions to get these issues resolved.
1 Like
Hello,
Thank you for your reply.
For the first issue, the results are not the same when saving workbook as html instead of xlsx. See the two attached screenshots for the two pivot tables. (some data is missing: first column / date format is lost / filters are lost). With the last sample project you should be able to reproduce the same results and observe the issue.
For the second issue, it was a data source and filter problem, The value which I set in the code to be selected in the filter element does not exist in the data source. When I created the fake data in the second csv file i removed some values which was used as selected items in the filters. I updated my code like this:
// Filters
var filterIndex0 = GetItemIndex(“FALSE”, pivotTable.PageFields[“LockedPtf”].Items);
if (filterIndex0 != -1)
{
pivotTable.PageFields[“LockedPtf”].CurrentPageItem = filterIndex0;
}
var filterIndex1 = GetItemIndex(“FALSE”, pivotTable.PageFields[“ExcludedOstType”].Items);
if (filterIndex1 != -1)
{
pivotTable.PageFields[“ExcludedOstType”].CurrentPageItem = filterIndex1;
}
var filterIndex2 = GetItemIndex(“FO ACTION REQUIRED”, pivotTable.PageFields[“Message”].Items);
if (filterIndex2 != -1)
{
pivotTable.PageFields[“Message”].CurrentPageItem = filterIndex2;
}
private static short GetItemIndex(string item, string[] items)
{
short index = -1;
for (var i = 0; i < items.Length; i++)
{
if (string.Equals(items[i], item, StringComparison.CurrentCultureIgnoreCase))
{
index = (short)i;
break;
}
}
return index;
}
With the new code, I have no exceptions but I have the same issue as with the original data: the data in the pivot table in the html output result is not correct.
However how we can proceed for fixes ? Our licence allow us to have the 7.2.0 version. To migrate to a new version my manager told me to make a request who must send to other teams who must validate and after we must make another request to the financial service. In fact the administrative procedure is slow and it takes time, else I’m not sure if they will accept or not to purchase.
I have an urgent request, so I have to generate the HTML Pivot Table correctly. Is there a workaround that i can do please ? Maybe saving the file and reopen it and after export it as html ?
Thanks,
Oussama
@Oussama_Mokni
Thanks for details.
I have logged the screenshots with the issue for differences in the HTML file and will update you in this topic once we will have some news for you. Before buying license, you may get the 30 days temporary license to check this fix without any limitations.
Thanks for your reply.
Before using the 30 days temporary license I have to know if my company will accept or not to purchase the new version. I think they don’t have the budget.
Else for your information, after analyzing the code i found that the problem with the html pivot table comes from the 3 following properties under the PivotField:
- ShowInOutlineForm
- ShowCompact
- InsertBlankRow
When i set all of them to false, i have no missing values (see attached result). As a result It’s not the requested design and still don’t have filters data.
Maybe it will help you, else how we can proceed if you find the problem ?
Is there another solution except purchasing the new version ?
Thanks,
Oussama
Hello,
Another information, the pivot table in the xlsx file was good because the option RefreshDataOnOpeningFile was set at true, so when i open the excel file the pivot table is refreshed and the data is recalculated ! That’s why the pivot table is correct. When i disabled this option (RefreshDataOnOpeningFile = false) i have the same result as the HTML file and when refreshing data from Excel the pivot table is correctly recalculated.
So I think the two methods:
- RefreshData()
- CalculateData()
Don’t work correctly.
Thanks,
Oussama.
@Oussama_Mokni,
Regarding your images here for the first issue, I am afraid that we cannot entertain any issue in the old versions as per the policy here and all the issues are tested using latest version only. As you have tested the issue with an older version and sent the comparison images where data issue is also there, please note that we cannot correct this issue in the old version. According to our tests, these issues are not there using the latest version so we will not consider them while resolving the remaining formatting issue.
There is no other option available except upgrading the license to get a resolution of the first issue. As all the testing is done by you using the older version, therefore little confusions are raising here. You may test the issues with the latest version Aspose.Cells for .NET 19.4.x only and share the feedback like for this test it with the latest version and if it is different than the first issue, create a separate thread for it. We will analyze the issue with the latest versions and provide our feedback.
Till now for the first issue, we have observed some background color problem only which is under consideration. I am sharing the HTML here for your reference which is created with the latest version.
Also if you face some other issue, please create a separate thread as it helps in the followup of each issue properly.
PivotSampleOut19.4.5.zip (4.3 KB)
@Oussama_Mokni,
When you add a new CSV file for tests (Data2.csv), because the PivotField named ExcludedOstType" only has one item(“TRUE”), but you set PivotField.CurrentPageItem = 1 after adding it to PageField area. So you will get the exception.
Please change the following code:
pivotTable.PageFields["ExcludedOstType"].CurrentPageItem = 1;
to:
pivotTable.PageFields["ExcludedOstType"].CurrentPageItem = 0;
and let us know your feedback.
@Oussama_Mokni,
Please try our latest version/fix: Aspose.Cells for .NET v19.5.2 (attached)
Your issue (logged earlier as “CELLSNET-46733”) should be fixed in it.
Let us know your feedback.
Aspose.Cells19.5.2 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.5.2 For .Net4.0.Zip (4.9 MB)
The issues you have found earlier (filed as CELLSNET-46733) have been fixed in Aspose.Cells for .NET v19.6. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi