Hey there! Let's figure out why you're hitting that CellsException with ExportDataTable() and how to resolve it properly. The fallback to ExportDataTableAsString() works, but it's better to address the root cause so you can get typed data as intended.

Common Causes of the Exception #

First, let's break down why this happens:

• Mismatched Data Types: ExportDataTable() strictly enforces data type conversion. If any cell in your latitude/longitude columns has non-decimal values (like #N/A, #VALUE!, or even hidden text), it'll throw an error. ExportDataTableAsString() skips this strict check by converting everything to strings.
• Invalid Cell Values: Even if your data looks correct, some cells might have formatting issues (e.g., a latitude cell stored as text with leading/trailing spaces, or a corrupted value that Excel can interpret but Aspose.Cells can't convert to decimal).
• Row/Column Range Miscalculation: While your range calculation looks right, double-check if MaxRow includes empty rows at the bottom of your sheet—those might contain unexpected values that trigger the exception.

Step-by-Step Solutions #

1. Get the Exact Exception Details

First, modify your catch block to log the exception's message and code. This will tell you exactly which cell or value is causing the problem:

try 
{ 
    dataTable = worksheet.Cells.ExportDataTable(HeaderLineStartingRowNumber, 0, 
        worksheet.Cells.MaxRow - HeaderLineStartingRowNumber + 1, 
        worksheet.Cells.MaxColumn + 1, true); 
} 
catch (CellsException ex) 
{ 
    // Log this information to debug the issue
    Console.WriteLine($"CellsException: {ex.Message} | Error Code: {ex.Code}");
    dataTable = worksheet.Cells.ExportDataTableAsString(HeaderLineStartingRowNumber, 0, 
        worksheet.Cells.MaxRow - HeaderLineStartingRowNumber + 1, 
        worksheet.Cells.MaxColumn + 1, true); 
}

The error message will likely point to a specific row/column, making it easy to fix the problematic cell in your Excel file.

2. Clean Up Invalid Values in Excel

Open your Excel file and fix any error values or formatting issues:

• Use Find & Select > Go To Special > Errors to quickly locate cells with #N/A, #VALUE!, etc. Replace these with valid decimal values or remove the rows if they're unnecessary.
• For text-formatted decimal cells: Select the latitude/longitude columns, go to Data > Text to Columns, and follow the wizard to convert them to numeric values.

3. Force Data Types During Export

If you know the exact data types of each column, use ExportDataTableOptions to explicitly define them. This helps Aspose.Cells handle conversion more reliably, and throws clearer errors if something's off:

var exportOptions = new ExportDataTableOptions
{
    IsFieldNameShown = true,
    StartRow = HeaderLineStartingRowNumber,
    StartColumn = 0,
    NumberOfRows = worksheet.Cells.MaxRow - HeaderLineStartingRowNumber + 1,
    NumberOfColumns = worksheet.Cells.MaxColumn + 1,
    // Define the expected types for each column: Site ID (string), Latitude (decimal), Longitude (decimal)
    ColumnTypes = new Type[] { typeof(string), typeof(decimal), typeof(decimal) }
};

try
{
    dataTable = worksheet.Cells.ExportDataTable(exportOptions);
}
catch (CellsException ex)
{
    Console.WriteLine($"Export failed: {ex.Message}");
    dataTable = worksheet.Cells.ExportDataTableAsString(exportOptions);
}

4. Convert String DataTable to Typed Data (Fallback with Control)

If you need to stick with ExportDataTableAsString() but want typed columns, convert the data manually after export. This lets you handle conversion errors gracefully:

// Export as string first
dataTable = worksheet.Cells.ExportDataTableAsString(HeaderLineStartingRowNumber, 0,
    worksheet.Cells.MaxRow - HeaderLineStartingRowNumber + 1,
    worksheet.Cells.MaxColumn + 1, true);

// Update column data types
dataTable.Columns["纬度"].DataType = typeof(decimal);
dataTable.Columns["经度"].DataType = typeof(decimal);

// Convert each row, handling errors
List<DataRow> rowsToRemove = new List<DataRow>();
foreach (DataRow row in dataTable.Rows)
{
    try
    {
        row["纬度"] = decimal.Parse(row["纬度"].ToString().Trim());
        row["经度"] = decimal.Parse(row["经度"].ToString().Trim());
    }
    catch (FormatException)
    {
        // Mark invalid rows for removal (or log them)
        rowsToRemove.Add(row);
    }
}

// Remove invalid rows
foreach (var row in rowsToRemove)
{
    dataTable.Rows.Remove(row);
}
dataTable.AcceptChanges();

内容的提问来源于stack exchange，提问作者Elias Ghali