Presentation
I'm using C# and Spire.Xls to edit excel files and to interrogate a SQL Database.
Each time I run the program (WPF) I want to open exisiting Excel file to delete and create sheets and pivot tables based on SQL database information. 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 Code
try
{
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");
em.removeSheet("pivot2", "PivotTable4");
//em.close_and_save();
//em.create_pivot_table_two("date", "PivotTable4");
em.close_and_save();
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.ToString());
}
removeSheet Function
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
public void close_and_save()
{
sWB.SaveToFile(fileName, ExcelVersion.Version2013);
}
Create pivot table code
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
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;
}
}
UPDATE1
After further testing of one whole day I come to the conclusion that running another file using this code works just fine and I can create 2 pivot tables.
Opening this file using 7zip we can observe 2 files:
- pivotCacheDefinition1.xls
- pivotCacheDefinition2.xls
The New Problem When I use the OG original file intented to run the program throws exception after removing sheet pivot2 and saving.
Opening the original file intented to run (after exception corrpution and running the code) we observe only 1 file:
- pivotCacheDefinition2.xls
Seems the problem is file related more than code related? Maybe, but I can't figure out why.
Link to the post of debug pictures and exception: imgur debug images
