Using FileCleaner namespace to ignore reading additional empty cells in Excel

Sometimes will reading Excel files into DataTable, you will find hundreds of additional empty columns and rows imported into the DataTable. These additional empty cells may led to OutofMemoryException while trying to read Excel files.

Using FileCleaner.MsExcelCleaner class you have the choice to:

1. Delete all empty Rows and Columns
2. Get the used Rang for each worksheet to be able to pass it to the Converters.MsExcelImport class
3. Remove top empty rows and get used range

(1) Delete all empty Rows and Columns

using(FileCleaner.MsExcelCleaner xClean = new FileCleaner.MsExcelCleaner(5000,ExcelCleanOperationType.CleanExcel){

    xClean.CleanExcel(@"D:\SchemaMapperTest\Password_Test.xlsx");

}

(2) Get the used Rang for each worksheet

string strUsedRange;

using(FileCleaner.MsExcelCleaner xClean = new FileCleaner.MsExcelCleaner(5000,ExcelCleanOperationType.GetUsedRange){

    strUsedRange = xClean.CleanExcel(@"D:\SchemaMapperTest\Password_Test.xlsx");

}

using (Converters.MsExcelImport smExcel = new Converters.MsExcelImport(@"D:\SchemaMapperTest\Password_Test.xlsx",strUsedRange))
{
    //Read Excel
    smExcel.BuildConnectionString();
    var lst = smExcel.GetSheets();
    //Read only from the first worksheet and consider the first row as header
    dtExcel = smExcel.GetTableByName(lst.First(), true, 0);
}

(3) Remove top empty rows and get used range

string strUsedRange;

using(FileCleaner.MsExcelCleaner xClean = new FileCleaner.MsExcelCleaner(5000,ExcelCleanOperationType.RemoveTopEmptyAndGetUsedRange){

    strUsedRange = xClean.CleanExcel(@"D:\SchemaMapperTest\Password_Test.xlsx");

}

using (Converters.MsExcelImport smExcel = new Converters.MsExcelImport(@"D:\SchemaMapperTest\Password_Test.xlsx",strUsedRange))
{
    //Read Excel
    smExcel.BuildConnectionString();
    var lst = smExcel.GetSheets();
    //Read only from the first worksheet and consider the first row as header
    dtExcel = smExcel.GetTableByName(lst.First(), true, 0);
}