public static OpenXml_SpreadSheet open_SpreadSheet(this string pathToFileToLoad, bool isEditable)
{
try
{
var spreadSheet = new OpenXml_SpreadSheet();
spreadSheet.IsEditable = isEditable;
spreadSheet.FilePath = pathToFileToLoad;
spreadSheet.SpreadSheetDocument = SpreadsheetDocument.Open(pathToFileToLoad, isEditable);
// this needs to be more robust to handle the cases where there is no SharedStringTablePart
spreadSheet.StringTable = (SharedStringTable)spreadSheet.SpreadSheetDocument
.WorkbookPart
.GetPartsOfType <SharedStringTablePart>()
.First()
.SharedStringTable;
//.toList()[0];
foreach (var sheet in spreadSheet.sheets_Raw())
{
spreadSheet.add_Sheet(sheet);
}
return(spreadSheet);
}
catch (Exception ex)
{
ex.log("in open_SpreadSheet");
return(null);
}
}
public static List <Sheet> sheets_Raw(this OpenXml_SpreadSheet spreadSheet)
{
return((from Sheet sheet in spreadSheet.SpreadSheetDocument
.WorkbookPart
.Workbook
.GetFirstChild <Sheets>()
select sheet).toList());
}
public static OpenXml_SpreadSheet show_SpreadSheetGUI()
{
var panel = O2Gui.open <Panel>("OpenXml viewer", 1000, 500);
var spreadSheet = new OpenXml_SpreadSheet();
spreadSheet.showInControl(panel);
return(spreadSheet);
}
public static OpenXml_SpreadSheet_WorkSheet workSheet(this OpenXml_SpreadSheet spreadSheet, string name)
{
foreach (var worksheet in spreadSheet.workSheets())
{
if (worksheet.Name == name)
{
return(worksheet);
}
}
return(null);
}
public static T showInControl <T>(this OpenXml_SpreadSheet spreadSheet, T control)
where T : System.Windows.Forms.Control
{
control.clear();
var topPanel = control.add_Panel();
var tableList = topPanel.add_TableList();
var treeView = tableList.insert_Left <Panel>(200).add_TreeView();
treeView.afterSelect <OpenXml_SpreadSheet_WorkSheet>(
(workSheet) => {
try
{
tableList.clearTable();
var rowsData = workSheet.rowsData();
tableList.add_Columns(rowsData[0]);
rowsData.RemoveAt(0);
foreach (var row in rowsData)
{
tableList.add_Row(row);
}
}
catch (Exception ex)
{
ex.log("in OpenXml_SpreadSheet_WorkSheet treeView.afterSelect");
}
//tableList.show(workSheet.rows());
});
//foreach(var workSheet in )
Action <OpenXml_SpreadSheet> loadSpreadSheetData =
(_spreadSheet) => {
treeView.clear();
tableList.clearTable();
if (_spreadSheet.notNull())
{
treeView.add_Nodes(_spreadSheet.WorkSheets);
treeView.selectFirst();
}
};
Action <string> loadSpreadSheetFile =
(fileToLoad) => {
"Loading File: {0}".info(fileToLoad);
loadSpreadSheetData(fileToLoad.open_SpreadSheet());
};
treeView.onDrop(loadSpreadSheetFile);
tableList.getListViewControl().onDrop(loadSpreadSheetFile);
//load data
loadSpreadSheetData(spreadSheet);
return(control);
}
public static OpenXml_SpreadSheet add_Sheet(this OpenXml_SpreadSheet spreadSheet, Sheet sheet)
{
var workSheet = new OpenXml_SpreadSheet_WorkSheet(spreadSheet);
workSheet.Sheet = sheet;
workSheet.ID = sheet.Id.Value;
workSheet.Name = sheet.Name.Value;
workSheet.WorksheetPart = (WorksheetPart)spreadSheet.SpreadSheetDocument.WorkbookPart.GetPartById(workSheet.ID);
var sharedStrings = workSheet.OpenXml_SpreadSheet.sharedStrings();
foreach (var row in workSheet.rows_Raw())
{
workSheet.Rows.Add(new OpenXml_SpreadSheet_Row(row.rowData(sharedStrings)));
}
spreadSheet.WorkSheets.Add(workSheet);
return(spreadSheet);
}
public static System.Windows.Forms.Control showInForm(this OpenXml_SpreadSheet spreadSheet)
{
var panel = O2Gui.open <Panel>("OpenXml viewer", 1000, 500);
return(spreadSheet.showInControl(panel));
}
public static List <SharedStringItem> sharedStrings(this OpenXml_SpreadSheet spreadSheet)
{
return((from SharedStringItem item in spreadSheet.StringTable.toList()
select item).toList());
}
public static List <OpenXml_SpreadSheet_WorkSheet> workSheets(this OpenXml_SpreadSheet spreadSheet)
{
return(spreadSheet.WorkSheets);
}
public OpenXml_SpreadSheet_WorkSheet(OpenXml_SpreadSheet openXml_SpreadSheet)
{
OpenXml_SpreadSheet = openXml_SpreadSheet;
Rows = new List <OpenXml_SpreadSheet_Row>();
}
