Everything works fine, I empty the 2 sheets used as datasource and delete the sheet for pivot1 (pivot table 1) and create a new sheet with the same name and create a new pivot table based on
the datasource sheet (2 datasource sheet for 3 pivot tables, 2 of them share one datasource).
Problem
After that I try to do the same process for pivot table 2, deleting pivot2 sheet and recreate. Code executes until the end (delete and create) but after i try to save the file I get The entry pivotCacheDefinition2.xml already exists in the zip and the file becomes corrupted.
After more testing I found out that even if I only remove/delete the sheet pivot2 and save the file (excluding the creation process from the execution) it gives me the same error. So I can only delete and create 1 pivot table. How to solve this?
Main file code
- Code: Select all
excel_manager em = new excel_manager();
//excel_editor ee = new excel_editor();
em.loadExcelFile("C:\\test\\situatie_pivoti_27.07 - 07.00.xlsx");
em.setDebugMode(true);
em.clearData("date");
em.clearData("date2");
em.addDateHeaders();
em.fillData("date", ds.Tables["dataset1"]);
em.fillData("date2", ds.Tables["dataset2"]);
em.removeSheet("pivot1", "PivotTable1");
em.create_pivot_table_one("date", "PivotTable1");
//problem here
em.removeSheet("pivot2", "PivotTable4");
em.close_and_save(); //Exception here
removeSheet Code
- Code: Select all
public void removeSheet(String sheetName, String pivotTableName="")
{
try
{
if (sheetName.Contains("pivot"))
{
sWB.Worksheets[sheetName].Clear();
sWB.Worksheets[sheetName].ClearData();
sWB.Worksheets[sheetName].PivotTables.Remove(pivotTableName);
}
sWB.Worksheets.Remove(sheetName);
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
Save the file code
- Code: Select all
public void close_and_save()
{
sWB.SaveToFile(fileName, ExcelVersion.Version2013);
}
Create pivot table code
- Code: Select all
public void create_pivot_table_two(String DataSourceSheetName, String pivotTableName)
{
try
{
//Re-create worksheet pivot2
Worksheet pivot2 = sWB.CreateEmptySheet();
pivot2.Name = "pivot2";
pivot2.AllocatedRange.AutoFitColumns();
pivot2.AllocatedRange.AutoFitRows();
changeSheet("pivot2");
//Get Datasource "data"
Worksheet datasource1 = sWB.Worksheets[DataSourceSheetName];
CellRange dataRange = datasource1.Range[datasource1.FirstRow, datasource1.FirstColumn, datasource1.LastRow - 340, datasource1.LastColumn - 1];
#region Debug_Code
//var a = dataRange.CellList;
//List<String> debug = new List<string>();
//foreach (var index in a)
//{
// debug.Add(index.DisplayedText);
//}
#endregion
//Create Pivot Table 2
//PivotCache cache = this.cache1;
PivotCache cache = sWB.PivotCaches.Add(dataRange);
PivotTable pt2 = pivot2.PivotTables.Add(pivotTableName, dataRange, cache);
#region PivotTable2 Proprieties
pt2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark3;
pt2.ShowRowStripes = true;
pt2.ShowColumnGrand = false;
pt2.ShowRowGrand = false;
pt2.Options.MergeLabels = true;
#endregion
#region PivotTable2 Row and Column Field Proprieties
//creating the rows
PivotField r1 = pt2.PivotFields["nume"] as PivotField;
r1.Axis = AxisTypes.Row;
pt2.Options.RowHeaderCaption = "Row Labels";
PivotField r2 = pt2.PivotFields["numar"] as PivotField;
r2.Axis = AxisTypes.Row;
PivotField r3 = pt2.PivotFields["dela-panala"] as PivotField;
r3.Axis = AxisTypes.Row;
//creating the columns with dates
PivotField c = pt2.PivotFields["dt"] as PivotField;
c.Axis = AxisTypes.Column;
c.NumberFormat = "dd-mmm";
c.SortType = PivotFieldSortType.Ascending;
pt2.DataFields.Add(pt2.PivotFields["ocupate"], "efectuate", SubtotalTypes.Sum);
pt2.DataFields.Add(pt2.PivotFields["total"], "maxim", SubtotalTypes.Sum);
#endregion
pivot2.SetRowHeight(1, 20);
pivot2.SetColumnWidth(1, 60);
//cache.Dispose();
}catch (Exception ex)
{
}
}
excel_manager Full Class
- Code: Select all
class excel_manager
{
Workbook sWB;
Worksheet active_sheet;
PivotCache cache1;
String filePath;
String fileName;
String[] date_headers = new string[] {"nume","numar", "total",
"ocupate", "dt", "dela-panala"};
String[] date2_headers = new string[] { "nume", "dt", "libere", "total" };
int sheetNumber;
public excel_manager()
{
sWB = new Workbook();
filePath = "";
fileName = "";
sheetNumber = 0;
cache1 = null;
}
public void loadExcelFile(String path)
{
if (File.Exists(path))
{
try
{
this.filePath = path;
sWB.LoadFromFile(filePath);
sWB.IsHideWindow = true;
fileName = sWB.FileName;
sheetNumber = sWB.Worksheets.Count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
else
{
Console.WriteLine("File does not exist");
}
}
public void setDebugMode(bool status)
{
sWB.IsHideWindow = !(status);
}
public void changeSheet(int index)
{
if (index > 0 && index <= sheetNumber)
{
try
{
active_sheet = sWB.Worksheets[index];
active_sheet.Activate();
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
else
{
Console.WriteLine("Invalid Inex for Sheet!");
}
}
public void changeSheet(String sheetName)
{
try
{
active_sheet = sWB.Worksheets[sheetName];
active_sheet.Activate();
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
internal void clearData(string sheetName)
{
CellRange cr = sWB.Worksheets[sheetName].AllocatedRange;
//Clear all objects
cr.ClearAll();
}
public void removeSheet(String sheetName, String pivotTableName="")
{
try
{
if (sheetName.Contains("pivot"))
{
sWB.Worksheets[sheetName].Clear();
sWB.Worksheets[sheetName].ClearData();
sWB.Worksheets[sheetName].PivotTables.Remove(pivotTableName);
}
sWB.Worksheets.Remove(sheetName);
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
public void removeSheet(int index)
{
if (index > 0 && index <= sheetNumber)
{
try
{
sWB.Worksheets.Remove(index);
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
else
{
Console.WriteLine("Invalid Inex for Sheet!");
}
}
public void addSheet(String sheetName)
{
sWB.Worksheets.Add(sheetName);
}
public void close_and_save()
{
sWB.SaveToFile(fileName, ExcelVersion.Version2013);
}
internal void addDateHeaders()
{
changeSheet("date");
CellRange range1 = active_sheet.Range[active_sheet.FirstRow, active_sheet.FirstColumn, active_sheet.FirstRow, date_headers.Length];
int c1 = 0;
foreach (var i in range1)
{
i.Text = date_headers[c1];
i.Style.Font.IsBold = true;
c1++;
}
changeSheet("date2");
CellRange range2 = active_sheet.Range[active_sheet.FirstRow, active_sheet.FirstColumn, active_sheet.FirstRow, date2_headers.Length];
int c2 = 0;
foreach (var i in range2)
{
i.Text = date2_headers[c2];
i.Style.Font.IsBold = true;
c2++;
}
}
public void fillData(String sheetName, DataTable Data)
{
changeSheet(sheetName);
active_sheet.InsertDataTable(Data, true, 1, 1);
}
public void create_pivot_table_one(String DataSourceSheetName, String pivotTableName)
{
try
{
//Re-create worksheet pivot1
Worksheet pivot1 = sWB.CreateEmptySheet();
pivot1.Name = "pivot1";
pivot1.AllocatedRange.AutoFitColumns();
pivot1.AllocatedRange.AutoFitRows();
//Get Datasource "data"
Worksheet datasource1 = sWB.Worksheets[DataSourceSheetName];
CellRange dataRange = datasource1.Range[datasource1.FirstRow, datasource1.FirstColumn, datasource1.LastRow - 340, datasource1.LastColumn - 1];
#region Debug_Code
//var a = dataRange.CellList;
//List<String> debug = new List<string>();
//foreach (var index in a)
//{
// debug.Add(index.DisplayedText);
//}
#endregion
//Create Pivot Table 1
PivotCache cache = sWB.PivotCaches.Add(dataRange);
cache1 = cache;
PivotTable pt1 = pivot1.PivotTables.Add(pivotTableName, dataRange, cache);
#region PivotTable1 Proprieties
pt1.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark4;
pt1.ShowColumnGrand = false;
pt1.ShowRowGrand = false;
pt1.ShowRowStripes = true;
pt1.Options.MergeLabels = true;
#endregion
#region PivotTable1 Row and Column Field Proprieties
//creating the rows
PivotField r1 = pt1.PivotFields["nume"] as PivotField;
r1.Axis = AxisTypes.Row;
pt1.Options.RowHeaderCaption = "Row Labels";
//creating the columns with dates
PivotField c = pt1.PivotFields["dt"] as PivotField;
c.Axis = AxisTypes.Column;
c.NumberFormat = "dd-mmm";
c.SortType = PivotFieldSortType.Ascending;
pt1.DataFields.Add(pt1.PivotFields["numar"], "numar_gh", SubtotalTypes.Count); //this won't work
pt1.DataFields.Add(pt1.PivotFields["total"], "maxim", SubtotalTypes.Sum);
pt1.DataFields.Add(pt1.PivotFields["ocupate"], "efectuate", SubtotalTypes.Sum);
#endregion
pivot1.SetRowHeight(1, 20);
pivot1.SetColumnWidth(1, 60);
//cache.Dispose();
}
catch (Exception ex)
{
}
}
public void create_pivot_table_two(String DataSourceSheetName, String pivotTableName)
{
try
{
//Re-create worksheet pivot2
Worksheet pivot2 = sWB.CreateEmptySheet();
pivot2.Name = "pivot2";
pivot2.AllocatedRange.AutoFitColumns();
pivot2.AllocatedRange.AutoFitRows();
changeSheet("pivot2");
//Get Datasource "data"
Worksheet datasource1 = sWB.Worksheets[DataSourceSheetName];
CellRange dataRange = datasource1.Range[datasource1.FirstRow, datasource1.FirstColumn, datasource1.LastRow - 340, datasource1.LastColumn - 1];
#region Debug_Code
//var a = dataRange.CellList;
//List<String> debug = new List<string>();
//foreach (var index in a)
//{
// debug.Add(index.DisplayedText);
//}
#endregion
//Create Pivot Table 2
//PivotCache cache = this.cache1;
PivotCache cache = sWB.PivotCaches.Add(dataRange);
PivotTable pt2 = pivot2.PivotTables.Add(pivotTableName, dataRange, cache);
#region PivotTable2 Proprieties
pt2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark3;
pt2.ShowRowStripes = true;
pt2.ShowColumnGrand = false;
pt2.ShowRowGrand = false;
pt2.Options.MergeLabels = true;
#endregion
#region PivotTable2 Row and Column Field Proprieties
//creating the rows
PivotField r1 = pt2.PivotFields["nume"] as PivotField;
r1.Axis = AxisTypes.Row;
pt2.Options.RowHeaderCaption = "Row Labels";
PivotField r2 = pt2.PivotFields["numar"] as PivotField;
r2.Axis = AxisTypes.Row;
PivotField r3 = pt2.PivotFields["dela-panala"] as PivotField;
r3.Axis = AxisTypes.Row;
//creating the columns with dates
PivotField c = pt2.PivotFields["dt"] as PivotField;
c.Axis = AxisTypes.Column;
c.NumberFormat = "dd-mmm";
c.SortType = PivotFieldSortType.Ascending;
pt2.DataFields.Add(pt2.PivotFields["ocupate"], "efectuate", SubtotalTypes.Sum);
pt2.DataFields.Add(pt2.PivotFields["total"], "maxim", SubtotalTypes.Sum);
#endregion
pivot2.SetRowHeight(1, 20);
pivot2.SetColumnWidth(1, 60);
//cache.Dispose();
}catch (Exception ex)
{
}
}
public void update_pivot_table_one()
{
XlsPivotTable pt = sWB.Worksheets["pivot1"].PivotTables[0] as XlsPivotTable;
pt.Cache.IsRefreshOnLoad = true;
}
}
.
