OOXML Excel Library Addition
A new assembly, GridExcelHelper, has been added to the Objective Grid for .NET set of assemblies to assist in the reading and writing of Excel 2007 or higher xlsx files using the Open Office XML SDK. The GridExcelHelper library references the following assemblies in addition to the common System assemblies:
DocumentFormat.OpenXml — Open Office XMLThe GridExcelHelper project is broken down into the following C# files:
GridExcel.csExcelFiles.csThe common namespace used throughout the GridExcelHelper assembly is Stingray.Excel.
The solution files, GridExcelHelper<version>.sln, and folder, GridExcelHelper, are located in <stingray-installdir>\Src. GridExcelHelper project files are also added to the solution files for building GridControl. The library build output is located in <stingray-installdir>\Assemblies.
GridExcelHelper is the base class for the assembly. It is defined in GridExcel.cs. This file contains the public API for all functions that deal with the contents of an XLSX file and the grid control. This class contains the following public API, presented in category order:
Public data members
enum WarningReason{ OpenFailed, SaveFailed, OpenRenamed, SaveRenamed }
enum WarningReason{ Open, Save }
struct CellIndex containing
int nRow
int nCol
Public API (non-virtual)
void AddEditedCell(int nRow, int nCol)
void CopyToGrid(ExcelFile excelFile, out int rowMax, out int colMax)
string GetFileName(FileDialogMode mode)
void Init()
string LoadGridFromExcelFile(string newFileName, string sheetName,
out int rowMax, out int colMax)
string SaveGridToExcelFile(string newFileName, string sheetName)
The public API functions do the following:
AddEditedCell keeps a list of all modified cells.CopyToGrid copies the data from each cell reference to the grid control and then closes the current spreadsheet.GetFileName launches either the OpenFileDialog or the SaveFileDialog to obtain a filename based on the mode passed to it.LoadGridFromExcelFile loads the given xlsx file into the grid control.SaveGridToExcelFile saves all the grid control cell modifications to the given Excel xlsx file and sheet.Public API (virtual with a default implementation)
virtual void UpdateExcelFromGrid(Sheet sheet, ExcelFile excelFile)
Default implementation details may be viewed in the source file <StingrayInstalldir>\Objective Grid for .NET\Solutions\GridExcelHelper\GridExcel.cs.
The functions ClearGrid, SetGridValue, GetGridCellValue and Warning contain a Debug.Assert statement as the default implementation. These functions are left to the user to define.
Implementation definition examples can be seen in the tutorial sample <StingrayInstalldir>\Objective Grid for .NET\Tutorials\ExcelReadWrite_CS.
ExcelFile is a base class for the assembly. It is defined in ExcelFile.cs. This file contains the public API for all functions that deal with the xlsx file. This class contains the following public API, presented in category order:
Properties
string failedFileName { get; set; }
Stream currentFileStream { get; set; }
string currentSheetName { get; set; }
SpreadsheetDocument currentSpreadSheet { get; set; }
WorkbookPart currentWorkbookPart { get; set; }
Worksheet currentWorkSheet { get; set; }
Public API
ExcelFile()
static Boolean IsFileLocked(FileInfo file)
string ColName(string CellReference)
static string ColumnNameFromIndex(uint columnIndex)
int ExcelColumnNameToNumber(string columnName)
int RowNumber(string CellReference)
void UpdateCell(Sheet sheet, string value, int nRow, int nCol)
bool UpdateValue(Sheet sheet, string cellRef, string value, bool isString)
string ExcelGetCellValue(Sheet theSheet, string cellRef)
The public API functions do the following:
ExcelFile is the constructor for this class.IsFileLocked uses a flag to determine if the specified file is being used or not. If the specified file is being processed or is not found, IsFileLocked returns TRUE.ColName extracts the column's cell reference and returns the column name as a string.ColumnNameFromIndex converts a column number to a column name, i.e. A, B, C, ..., AA, AB, ...ExcelColumnNameToNumber converts the column's string name to a 32-bit integer value.RowNumber converts the row's string cell reference to a 32-bit integer and returns the numeric row number.UpdateCell calls UpdateValue.UpdateValue either retrieves the index of an existing string or inserts the string into the shared string table. Then UpdateValue gets the index of the new item and saves the worksheet.ExcelGetCellValue retrieves a reference to the worksheet part, and then use its worksheet property to get a reference to the cell whose address matches the address supplied. If the cell exists, the cell's value is returned. Otherwise, if the cell does not exist, an empty string is returned.Public API (virtual with default implementation)
virtual Sheet CopyFileAndOpen(string fileName, string sheetName)
virtual Sheet OpenFile(string fileName, string sheetName)
virtual Sheet Save(string fileName, string sheetName)
virtual Sheet SaveAs(string fileName, string newFileName, string sheetName)
Here is what the supplied default implementations do. You can override them, of course.
CopyFileAndOpen copies the Excel xlsx file to a new xlsx file and then opens the copy.OpenFile opens the current Excel xlsx file if it is not locked. Otherwise, CopyFileAndOpen is called.Save opens the original Excel xlsx file, updates all cell references, and then saves the file.SaveAs opens the original Excel xlsx file, updates all cell references, then saves the file under a new filename as defined by the user.Default implementation details may be viewed in the source file <StingrayInstalldir>\Src\GridExcelHelper\ExcelFile.cs.
