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…”);
}
}
}
}