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

Free Support Forum - aspose.com

Use of Pivottable.format corrupt file

I create Pivot table using the data in another sheet. In Pivot table I but N/A if cell is empty in data field area . I want to format that so that text will be right align . But when I use the pivotTable.Format . My saved file corrupt.



namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SaveFile();
System.Console.WriteLine(“Completed…”);
}

private static void SaveFile()
{

var sheetName = “AllData”;
IDictionary<string, string> headings = new Dictionary<string, string>();
headings.Add(“Rows”, “Sub Name”);
headings.Add(“Data”, “EA,PA,EB,PB”);

var tableHeaders = headings;
var workbook = new Workbook(“C:\BatchData\-1\1\sample.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];

worksheet.AutoFilter.Range = String.Format("{0}:{1}", worksheet.Cells.FirstCell.Name, worksheet.Cells.LastCell.Name);

string datasrc = String.Format("{0}!{1}:{2}", sheetName, worksheet.Cells.FirstCell.Name, worksheet.Cells.LastCell.Name);

// string pivotSheetName = String.Format("{0} {1}", sheetName, “PrivotTable”);
string pivotSheetName = “CalculatedData”;
//Worksheet ws = workbook.Worksheets.Add(pivotSheetName);
Worksheet ws = workbook.Worksheets.Insert(0, SheetType.Worksheet, pivotSheetName);

var iPivotIndex = ws.PivotTables.Add(datasrc, “A8”, “PivotTables”);
PivotTable pivotTable = ws.PivotTables[iPivotIndex];
//Create Rows for Pivot Table
string rowHeaders;
tableHeaders.TryGetValue(“Rows”, out rowHeaders);
string[] rowDataHeaders = rowHeaders != null ? rowHeaders.Split(’,’) : null;

if (rowDataHeaders != null)
{
foreach (string rowItem in rowDataHeaders)
{
pivotTable.AddFieldToArea(PivotFieldType.Row, rowItem.Trim()); pivotTable.RowFields[rowItem.Trim()].SetSubtotals(PivotFieldSubtotalType.None, false);
}
}
if (rowDataHeaders.Length > 1)
pivotTable.RowFields[0].HideDetail(true);
//Create data columns Pivot Table
string dataHeaders;
tableHeaders.TryGetValue(“Data”, out dataHeaders);
string[] dataColumns = dataHeaders != null ? dataHeaders.Split(’,’) : null;
if (dataColumns != null)
{
foreach (string item in dataColumns)
{
pivotTable.AddFieldToArea(PivotFieldType.Data, item.Trim());
if (pivotTable.DataFields[item.Trim()] != null)
pivotTable.DataFields[item.Trim()].NumberFormat = “#,##0.00”;
}
}

//Create filter columns Pivot Table
string filterHeader;
tableHeaders.TryGetValue(“Page”, out filterHeader);
string[] filterColumns = filterHeader != null ? filterHeader.Split(’,’) : null;
if (filterColumns != null)
{
foreach (string item in filterColumns)
{
pivotTable.AddFieldToArea(PivotFieldType.Page, item.Trim());
pivotTable.IsMultipleFieldFilters = true;
}
}

//Create filter columns Pivot Table
string columnHeader;
tableHeaders.TryGetValue(“Column”, out columnHeader);
string[] columns = columnHeader != null ? tableHeaders[“Column”].Split(’,’) : null;

if (columns != null)
{
foreach (string item in columns)
{
pivotTable.AddFieldToArea(PivotFieldType.Column, item.Trim());
}
}


pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;
if (dataColumns.Length > 1)
pivotTable.ColumnFields.Add(pivotTable.DataField);



pivotTable.RowGrand = false;
if (!(sheetName.Equals(“Review(Unit)”, StringComparison.OrdinalIgnoreCase) || sheetName.Equals(“Review(Subcon)”, StringComparison.OrdinalIgnoreCase)))
pivotTable.ColumnGrand = false;


pivotTable.DisplayNullString = true;
pivotTable.NullString = “N/A”;
// pivotTable.CalculateData();

// style for Pivot Table
ws.IsGridlinesVisible = false;

Style style = new Style();
style.HorizontalAlignment = TextAlignmentType.Right;
// style.ForegroundColor = System.Drawing.Color.Red;
pivotTable.Format(11,4, style);
pivotTable.CalculateData();
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium2;

try
{
var savefileName = “ts1.xlsx”;
if (savefileName.Substring(savefileName.Length - 5).Equals(".xlsx"))
{
// Excel 2007

workbook.Save(“C:\Data\” + savefileName, SaveFormat.Xlsx);
// workbook.Save( savefileName, SaveFormat.Xlsx);
}
else
{
// Excel 97-2003
workbook.Save(“C:\Data\” + savefileName, SaveFormat.Excel97To2003);
}
System.Console.WriteLine(“Saved…”);
}
catch(Exception ex)
{
System.Console.WriteLine(“Error…”);
}

}
}
}

Hi,


Thanks for providing us sample code and some details.

We observed that the output attached file is corrupt. Could you also provide us your template “sample.xlsx” used in your code segment, so we can evaluate your issue properly.

Thank you.

Please see the attached sample.xlsx file

FYI I am using ASPOSE.Cells 7.1.1.0


Thanks

Hi,


Thanks for the template file.

Please try our latest version/ fix: Aspose.Cells for .NET v8.6.1.6

I have tested your scenario/ case using your template file and sample code, it works fine. Please find attached the output Excel file which is fine and I don’t find any error when opening the file into MS Excel.

Let us know if you still have any issue.

Thank you.

Thanks with new dll it is working.


Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest aspose.cells dll. Let us know if you encounter any other issue, we will be glad to look into it and help you further.