Net4x.SpreadsheetLibrary.Base 1.9.9.8

SpreadsheetLibrary.Base

Utilities for reading, writing and converting spreadsheet and CSV data into ADO.NET DataTable / DataSet instances and for exporting DataTable content to Excel files. The project provides a small, dependency-light API built on top of NPOI and a CSV parser to support multiple .NET runtimes.

Features

• Read Excel files (.xlsx, .xls, .xlsm) from FileInfo or byte arrays into DataTable using ExcelReader.
• Convert DataTable / DataSet to Excel workbooks and save to file or obtain as a byte array.
• Export DataTable to CSV string with culture-aware separators and safe quoting.
• Convenience helpers to open a generated XLS file with the default system viewer (ShowTableAsExcel).
• Internal utilities to handle NPOI workbook creation, styling, autosizing and formula evaluation.

Main types and extensions

• ExcelReader.ToDataTable(FileInfo path, ExcelReaderOptions? options)

  • Reads a worksheet into a DataTable. Options control format, sheet selection, header row index, duplicate column handling and maximum columns.

• ExcelReader.ToDataTable(byte[] bytes, ExcelReaderOptions? options)

  • Same as above but from an in-memory byte array.

• DataTableExtension (extensions on DataTable)

  • ToExcel(...) overloads � save a DataTable as an Excel worksheet (XLS/XLSX) to a file.
  • ToExcelByteArray(...) � return workbook bytes for further processing or download.
  • ToCsv(...) � convert a DataTable to CSV using a CultureInfo to determine the list separator and formatting.
  • ShowTableAsExcel(...) � write a temporary XLS file and open it using the system shell (convenience for debugging/manual inspection).

• NpoiHelper (internal)

  • Creates workbooks, populates sheets from DataTables, applies simple styles, autosizes columns and supports reading cell values and formula evaluation.
  • Supports both HSSF (.xls) and XSSF (.xlsx, .xlsm) formats.

Options

• ExcelReaderOptions
  • Format (default Xlsx) � input format used to load workbook.
  • SheetIndex / SheetName � select a specific worksheet (mutually exclusive).
  • HeaderRowIndex � zero-based index of the header row; used to build column names.
  • RemoveEmptyRows (default true) � omit rows that are entirely empty.
  • AllowDuplicateColumns (default true) � when false, duplicate header names throw; when true duplicates are renamed to keep unique column names.
  • MaxColumns � optional maximum number of columns to read.

Usage examples

Read an Excel file into a DataTable:

var options = new ExcelReaderOptions { SheetIndex = 0, HeaderRowIndex = 0 };
var dataTable = new FileInfo("test.xlsx").ToDataTable(options);

Read from a byte array:

var dataTable = bytes.ToDataTable(options);

Save a DataTable to an Excel file (XLSX):

dataTable.ToExcel("output.xlsx", isXlsx: true);

Get workbook as bytes (for download or further processing):

var bytes = dataTable.ToExcelByteArray("Sheet1", isXlsx: true);

Convert to CSV:

var csv = dataTable.ToCsv(CultureInfo.InvariantCulture);

Notes and recommendations

• The library relies on NPOI for workbook manipulation and ICSharpCode.SharpZipLib for ZIP handling of XLSX streams. Add the appropriate runtime packages for your target framework.
• ToDataTable will throw on invalid configuration (for example, when both SheetName and SheetIndex are set). Use ExcelReaderOptions.Validate() to ensure options are consistent.
• The helpers are intended for small-to-medium datasets stored in memory. For very large spreadsheets consider streaming or chunked processing.
• When writing files, callers may provide an ILogger for diagnostic messages; the save helpers swallow IO exceptions and log informational messages instead of throwing.

Compatibility

• The project contains code paths for multiple .NET targets (net3.5/net4x/netstandard) and uses conditional compilation where necessary. Ensure you reference a matching set of NPOI packages compatible with your chosen target framework.

License / Contributing

See the repository root for license and contribution guidelines.