Чтение Excel в ASP.NET: данные не читаются, если столбец имеет разные форматы данных

Вы столкнулись с одной из многих забавных функций двигателя JET. Этот в основном отбирает все данные в каждой строке для одного столбца и пытается угадать формат данных. Если вы хотите, чтобы ваш код «просто работал», в этом поможет параметр реестра. Однако имейте в виду, что этот параметр реестра повлияет на то, как JET работает со всем импортом в системе, а не только с вашим конкретным импортом.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"ImportMixedTypes"="Text"
"TypeGuessRows"=dword:00000000

Этот параметр реестра сообщает JET о необходимости проверять формат каждой строки в столбце, прежде чем угадывать формат. Если он обнаружит смешанное содержимое, он импортирует строку как текст.

По умолчанию при подборе типа JET проверяет первые 25 строк.

В качестве альтернативы вы можете изменить TypeGuessRows на 1, и JET будет проверять первую строку только при угадывании типа. Это означает, что если первая строка - это число, а вторая строка - это строка, JET будет считать, что все строки являются числами, и вы не сможете их прочитать с помощью ADO.NET.

Еще одно предостережение: будьте осторожны при редактировании реестра. Вы можете очень быстро уничтожить свою систему, если не будете проявлять осторожность.

Обновление: похоже, что Microsoft действительно не рекомендует с помощью служб Excel COM на серверах. Тем не менее, многие разработчики используют как не-.NET (как мой работодатель), так и .NET (см. здесь) окружающей среды, так как альтернативы дороги. Все проблемы в основном решаемы (за исключением потенциальных проблем с масштабируемостью и производительностью в приложениях большого объема и в некоторых случаях проблем с лицензией). Дорогостоящие альтернативы используют сторонние решения, такие как это.

Вы не должны использовать OleDbConnection, если у вас есть данные разных типов в одном столбце. Вы можете попробовать читать из Excel, используя, например, Excel COM / OLE API (скомпилировано из здесь, может содержать ошибки):

Включите в проект следующую ссылку:

Библиотека объектов Microsoft Excel 10.0

Библиотека объектов Microsoft Office 10.0

Включите пространство имен Excel.

  using Excel;
  ...
      Excel.ApplicationClass xl = new Excel.Application();
      xl.Visible = false;
      xl.UserControl = false;
      Excel.Workbook theWorkbook = xl.Workbooks.Open(
         fileName, 0, true, 5,
          "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
          0, true); 
     Excel.Sheets sheets = theWorkbook.Worksheets;
     Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
     System.Array myvalues;
     Excel.Range range = worksheet.get_Range("A1", "E1".ToString());
     myvalues = (System.Array)range.Cells.Value;

Важный! Вы должны освободить используемые ресурсы. Из здесь:

// Need all following code to clean up and extingush all references!!!
theWorkbook.Close(null,null,null);
xl.Workbooks.Close();
xl.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (range);
System.Runtime.InteropServices.Marshal.ReleaseComObject (sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject (xl);
System.Runtime.InteropServices.Marshal.ReleaseComObject (worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (theWorkbook);
worksheet=null;
sheets=null;
theWorkbook=null;
xl = null;
GC.Collect(); // force final cleanup!

SpreadsheetGear для .NET может читать, писать, вычислять и т. д. книги Excel и позволяет получить доступ к базовые данные (число, текст, логика, ошибка) любой ячейки или форматированный текст любой ячейки с использованием таких API, как IWorksheet.Cells [rowIndex, colIndex] .Value или IWorksheet.Cells [rowIndex, colIndex] .Text. Ограничений по типу данных в каждом столбце / ячейке нет. SpreadsheetGear - это 100% безопасный код .NET (без COM-взаимодействия, без небезопасных собственных вызовов и т. Д.), Поэтому его проще развернуть, чем другие варианты, особенно в серверных сценариях.

Вы можете увидеть живые образцы здесь и загрузить бесплатную пробную версию здесь.

Отказ от ответственности: я владею SpreadsheetGear LLC

Когда все остальное не удалось, это то, что я сделал ... При импорте из excel я указал HDR = NO в строке подключения. Это импортировало заголовок как первую строку, таким образом, все типы данных столбца были текстовыми. После этого простая функция для упоминания имени столбца для таблицы данных. Что-то вроде приведенного ниже кода ...

private DataTable NameHeaderRows(DataTable dt)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        dt.Columns[i].ColumnName = dt.Rows[0][i].ToString();

    }
    dt.Rows.RemoveAt(0);
    return dt;
}

Я знаю, что это утомительно, но не нашел подходящего решения. Любое другое предложение приветствуется.