When getting a data row from the object with the type, any cell with empty value is not included due to the underlying XML node structures. Return value Adding Empty Cells into Spreadsheet Rows Obtain values from shared string table case CellValues.SharedString:ĭ().FirstOrDefault() Process values particularly for those data types if (cell.DataType != null)
#MICROSOFT OPEN XML CONVERTER VS. EXCEL CODE#
This will create the Resources.* file group under the project's Properties folder.Īdding the following event handler routine code into the startup class: Setting two DLL files as resource files on the Resources tab of the project Properties page.
#MICROSOFT OPEN XML CONVERTER VS. EXCEL INSTALL#
NET Framework versions below 4.0, you can download and install the Open XML SDK version 2.0, replace the two DLL files with the version 2.0 files, and then re-reference them in the project.Īn automatic assembly merge process can be used in some situations requiring one consolidated executable or DLL file for easy deployment as shown in the sample application. These files are working fine for Excel 2007 - 2013 spreadsheet files and Visual Studio 2010 - 2013 projects with. The sample application includes two files with the Open XML SDK version 2.5 in the lib folder. Two Open XML SDK Library assemblies, and WindowsBase.dll, are needed for obtaining the Excel data. NET programming work if we can well handle some hassles. Using the tool could be an excellent choice for the Excel related. Although the Open XML SDK for Office is not so developer-friendly, it's a standardized and mature tool from the Microsoft development network family. Third party tools, such as Aspose, function well but have the licence limit and may be an overkill if used just for reading the data from the Excel. If we develop a server application that needs to import the data from an Excel file, it's not a good practice to use the Office InterOp library for Excel. IList dataList = ExcelReader.GetDataToList(excelPath, AddProductData) Using Open XML SDK Library We will be discussing the data mapping details later.Īs demonstrated in the sample application, to import data from the Excel file to the List with the Product model, just call this line of code by passing the file path ( excelPath) and the name of the delegated data mapping function ( AddProductData):Ĭopy Code // Get product list from the spreadsheet The dataMappingFunctionName points to a function that conducts the mapping of spreadsheet field data to object model under the hood. If you have multiple sheets, you can call the second overloaded version with explicit sheetName values multiple times to create corresponding multiple List objects for your models. The first overloaded version always takes the first worksheet in the Excel file. The function is designed to import data from a single sheet to a single generic list at a time. Public IList GetDataToList( string filePath, string sheetName, Here is the corresponding plain object model for the sample data:Ĭopy Code public IList GetDataToList( string filePath,įunc, IList, T> dataMappingFunctionName) An Excel file is included in the downloaded source with the sample data shown below: The downloaded source contains everything for running the sample application with the Visual Studio 2010 - 2013.
Standardizing the string to primitive data type conversions.
Mapping spreadsheet data to object model with friendly column names instead of index numbers.