Main:
- Code: Select all
excel_manager em = new excel_manager();
em.loadExcelFile(filename3);
em.setDebugMode(true);
em.clearData("Foaie1");
em.CopySheetFrom(filename, "pivot3");
em.close_and_save();
class functions:
- Code: Select all
public void setDebugMode(bool status)
{
sWB.IsHideWindow = !(status);
}
- Code: Select all
internal void clearData(string sheetName)
{
CellRange cr = sWB.Worksheets[sheetName].AllocatedRange;
//Clear all objects
cr.ClearAll();
}
- Code: Select all
internal void CopySheetFrom(string filename, string sheetname)
{
Workbook sourceFile = new Workbook();
if (File.Exists(filename))
{
try
{
sourceFile.LoadFromFile(filename);
sourceFile.IsHideWindow = true;
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
else
{
Console.WriteLine("File does not exist");
}
this.sWB.Worksheets["Foaie1"].CopyFrom(sourceFile.Worksheets[sheetname]);
}
- Code: Select all
public void close_and_save()
{
sWB.SaveToFile(fileName, ExcelVersion.Version2013);
}
Whole Class Code:
- 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;
}
internal void dispose()
{
this.sWB.Dispose();
this.active_sheet.Dispose();
this.cache1.Dispose();
this.filePath = "";
this.fileName = "";
this.sheetNumber = 0;
}
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();
}
internal void clearData(string sheetName, string cells_range)
{
CellRange cr = sWB.Worksheets[sheetName].Range[cells_range];
//Clear all objects
cr.ClearAll();
}
internal void clearDataSituatie2(String sheetName)
{
//Get Datasource "data"
Worksheet sheet = sWB.Worksheets[sheetName];
CellRange cr = sheet.Range[sheet.FirstRow + 3, sheet.FirstColumn + 1, sheet.LastRow - 1, sheet.LastColumn - 3];
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++;
}
}
internal void CopySheetFrom(string filename, string sheetname)
{
Workbook sourceFile = new Workbook();
if (File.Exists(filename))
{
try
{
sourceFile.LoadFromFile(filename);
sourceFile.IsHideWindow = true;
}
catch (Exception ex)
{
Console.WriteLine("Exception: ", ex.ToString());
}
}
else
{
Console.WriteLine("File does not exist");
}
this.sWB.Worksheets["Foaie1"].CopyFrom(sourceFile.Worksheets[sheetname]);
}
internal void recalculateFormulas()
{
sWB.CalculateAllValue();
}
public void fillData(String sheetName, DataTable Data, int firstrow = 1, int firstcolumn = 1)
{
changeSheet(sheetName);
active_sheet.InsertDataTable(Data, true, firstrow, firstcolumn);
}
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, 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, datasource1.LastColumn];
#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 = true;
pt2.ShowRowGrand = true;
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 create_pivot_table_three(String DataSourceSheetName, String pivotTableName)
{
try
{
//Re-create worksheet pivot3
Worksheet pivot3 = sWB.CreateEmptySheet();
pivot3.Name = "pivot3";
pivot3.AllocatedRange.AutoFitColumns();
pivot3.AllocatedRange.AutoFitRows();
pivot3.Range["B2"].Style.WrapText = true;
pivot3.Range["C2"].Style.WrapText = true;
changeSheet("pivot3");
//Get Datasource "data2"
Worksheet datasource2 = sWB.Worksheets[DataSourceSheetName];
CellRange dataRange = datasource2.Range[datasource2.FirstRow, datasource2.FirstColumn, datasource2.LastRow, datasource2.LastColumn];
#region Debug_Code
var a = dataRange.CellList;
List<String> debug = new List<string>();
foreach (var index in a)
{
debug.Add(index.DisplayedText);
}
#endregion
PivotCache cache = sWB.PivotCaches.Add(dataRange);
PivotTable pt3 = pivot3.PivotTables.Add(pivotTableName, dataRange, cache);
#region PivotTable3 Proprieties
pt3.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;
pt3.ShowRowStripes = true;
pt3.ShowColumnGrand = true;
pt3.ShowRowGrand = true;
pt3.Options.MergeLabels = true;
#endregion
#region PivotTable3 Row and Column Field Proprieties
//creating the rows
PivotField r1 = pt3.PivotFields["nume"] as PivotField;
r1.Axis = AxisTypes.Row;
pt3.Options.RowHeaderCaption = "Etichete de randuri";
PivotField r2 = pt3.PivotFields["dt"] as PivotField;
r2.Axis = AxisTypes.Row;
r2.NumberFormat = "dd-mmm";
pt3.DataFields.Add(pt3.PivotFields["total"], "Suma de total", SubtotalTypes.Sum);
pt3.DataFields.Add(pt3.PivotFields["libere"], "Suma de libere", SubtotalTypes.Sum);
#endregion
pivot3.SetRowHeight(1, 20);
pivot3.SetColumnWidth(1, 60);
pivot3.Range["B1"].Text = "";
//cache.Dispose();
}
catch (Exception ex)
{
}
}
public void update_pivot_table_one()
{
XlsPivotTable pt = sWB.Worksheets["pivot1"].PivotTables[0] as XlsPivotTable;
pt.Cache.IsRefreshOnLoad = true;
}
}