Научная нотация при импорте из Excel в .Net

У меня есть задание C # /. Net, которое импортирует данные из Excel и затем обрабатывает его. Наш клиент отключает файлы, и мы обрабатываем их (важно, потому что у меня нет никакого контроля над исходным файлом).

Я использую библиотеку OleDb, чтобы заполнить набор данных (я ненавижу писать этот код. Серьезно, есть ли какой-нибудь код, который разработчик .Net боится писать больше?). Файл содержит некоторые цифры, такие как 30829300, 30071500 и т. Д. Тип данных для этих столбцов – «Текст».

Эти цифры преобразуются в научную нотацию при импорте данных. Есть ли способ предотвратить это?

-Крис

Библиотека OleDb, чаще всего, испортит ваши данные в электронной таблице Excel. Это во многом потому, что он заставляет все в макет столбца фиксированного типа, угадывая тип каждого столбца из значений в первых 8 ячейках в каждом столбце. Если он догадывается неправильно, вы получаете цифры, преобразованные в научную нотацию. Blech!

Чтобы этого избежать, вам лучше пропустить OleDb и прочитать лист непосредственно. Вы можете сделать это, используя COM-интерфейс Excel (также blech!) Или сторонний .NET-совместимый считыватель. SpreadsheetGear – одна из таких библиотек, которая работает достаточно хорошо и имеет интерфейс, который очень похож на интерфейс COM Excel.

Одним из способов решения этой проблемы является изменение оператора select вместо SELECT *:

"SELECT Format([F1], 'General Number') From [Sheet1$]" -or- "SELECT Format([F1], \"#####\") From [Sheet1$]" 

Однако это приведет к взрыву, если ваши ячейки содержат более 255 символов со следующей ошибкой: «Операция с несколькими шагами OLE DB сгенерировала ошибки. Проверьте каждое значение статуса OLE DB, если оно доступно. Работа не была выполнена».

К счастью, мой клиент не заботился об ошибках в этом сценарии.

На этой странице есть куча хороших вещей, чтобы попробовать: http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Если вы посмотрите на фактический .XSLX-файл с помощью Open XML SDK 2.0 Productivity Tool (или просто разархивируйте файл и просмотрите XML в блокноте), вы увидите, что Excel 2007 фактически хранит необработанные данные в научном формате.

Например, 0,00001 хранится как 1.0000000000000001E-5

 <x:cr="C18" s="11" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <x:v>1.0000000000000001E-5</x:v> </x:c> 

Глядя на ячейку в Excel, она отображается как 0.00001 как в ячейке, так и в строке формул. Поэтому не всегда верно, что OleDB вызывает проблему.

Используя эту строку подключения:

 Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\" 

с Excel 2010 я заметил следующее. Если файл Excel открыт при запуске OLEDB SELECT, вы получаете текущую версию ячеек, а не сохраненные значения файла. Кроме того, строковые значения, возвращаемые для длинного числа, десятичного значения и даты, выглядят следующим образом:

 5.0130370071e+012 4.08 36808 

Если файл не открыт, возвращаемые значения:

 5013037007084 £4.08 Monday, October 09, 2000 

Я обнаружил, что самый простой способ – выбрать формат Zip, а не текстовый формат для столбцов с большими «числами».

Вы пробовали лить значение поля в (int) или, возможно, (Int64), когда вы его читаете?

Просмотрите параметр строки подключения IMEX = 1 и параметр реестра TypeGuessRows в Google. По правде говоря, нет простого способа обойти это, потому что читатель вводит типы данных столбцов, просматривая первые несколько строк (по умолчанию 8). Если строки содержат все числа, вам не повезло.

К сожалению, обходной путь, который я использовал в прошлом, заключается в использовании параметра строки подключения HDR = NO и установки значения параметра реестра TypeGuessRows равным 1, что заставляет его считывать первую строку в качестве достоверных данных для определения своего типа данных, а не заголовок. Это взлом, но он работает. Код считывает первую строку (содержащую заголовок) в виде текста, а затем соответственно устанавливает тип данных.

Изменение реестра – это боль (и не всегда возможная), но я бы рекомендовал впоследствии восстановить исходное значение.

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

Таким образом, в целом, есть множество хаков, чтобы обойти это, но ничего действительно надежного.

У меня была такая же проблема, но мне удалось обойти это без использования интерфейса COM COM или стороннего программного обеспечения. Это связано с небольшими затратами на обработку, но, похоже, работает для меня.

  1. Сначала прочитайте данные, чтобы получить имена столбцов
  2. Затем создайте новый DataSet с каждым из этих столбцов, установив каждый из своих DataTypes в строку.
  3. Прочтите данные снова в этот новый набор данных. Вуаля – научная нотация теперь исчезла, и все читается как струна.

Вот какой код, который иллюстрирует это, и как дополнительный бонус, это даже StyleCopped!

 public void ImportSpreadsheet(string path) { string extendedProperties = "Excel 12.0;HDR=YES;IMEX=1"; string connectionString = string.Format( CultureInfo.CurrentCulture, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"{1}\"", path, extendedProperties); using (OleDbConnection connection = new OleDbConnection(connectionString)) { using (OleDbCommand command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM [Worksheet1$]"; connection.Open(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(command)) using (DataSet columnDataSet = new DataSet()) using (DataSet dataSet = new DataSet()) { columnDataSet.Locale = CultureInfo.CurrentCulture; adapter.Fill(columnDataSet); if (columnDataSet.Tables.Count == 1) { var worksheet = columnDataSet.Tables[0]; // Now that we have a valid worksheet read in, with column names, we can create a // new DataSet with a table that has preset columns that are all of type string. // This fixes a problem where the OLEDB provider is trying to guess the data types // of the cells and strange data appears, such as scientific notation on some cells. dataSet.Tables.Add("WorksheetData"); DataTable tempTable = dataSet.Tables[0]; foreach (DataColumn column in worksheet.Columns) { tempTable.Columns.Add(column.ColumnName, typeof(string)); } adapter.Fill(dataSet, "WorksheetData"); if (dataSet.Tables.Count == 1) { worksheet = dataSet.Tables[0]; foreach (var row in worksheet.Rows) { // TODO: Consume some data. } } } } } } } 

Я googled вокруг этого состояния. Здесь мои шаги solulition

  • Для шаблона excel file

1-формат Excel coloumn в качестве текстового макроса для вывода сообщений об ошибках для Number -> text conversion

  Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ErrorCheckingOptions.BackgroundChecking = Ture End Sub Private Sub Workbook_Open() Application.ErrorCheckingOptions.BackgroundChecking = False End Sub 
  • На Codebehind

3- во время чтения данных для импорта попытайтесь разобрать входящие данные в Int64 или Int32 ….

Мне интересно узнать, есть ли у кого-нибудь ответ на этот вопрос. Я все вверх и вниз по сеткам и пробовал все комбинации IMEX и HDR. IMEX = 1 является единственным, с которым мне удалось получить дату, валюту и общие значения чисел. Но большие цифры по-прежнему проявляются как научные. Мне нужно только читать файлы и изменять электронные таблицы, реестр, сторонний вариант не является вариантом.

  • C # ace oledb 12 файл только для чтения
  • System.Data.OleDb.OleDbException: он уже открыт исключительно другим пользователем или вам требуется разрешение на просмотр и запись его данных
  • Соединение OLEDB для MS Excel
  • Странное поведение при написании для Excel с оледом
  • Выберите адрес ячейки из Excel с помощью OLEDB в C #
  • Функциональность Excel не работает при публикации приложения winform с clickonce
  • Как я могу прочитать только первую строку из файла Excel с помощью OleDbDataAdapter?
  • ADO с файлами XLSX в Delphi XE
  • Как выбрать конкретные столбцы слияния из листа excel с помощью ole db c #
  • «Поставщик« Microsoft.ACE.OLEDB.12.0 »не зарегистрирован на локальном компьютере« Ошибка при импорте процесса xlsx на сервер sql
  • Заполнение столбцов DataGrid с данными Excel в VB
  • Interesting Posts

    vbScript открывает excel, но не загружает макрос / модули?

    закрыть файл excel во время отображения для обновления

    Скопируйте лист Excel в другую книгу Excel без формул

    Чтение поврежденного excel с использованием C #

    Как сохранить XML-файл ** EXCEL ** с помощью linq?

    Вычитание часов и минут с момента ввода в Excel 2013

    Создайте 1 список в Excel из 2 других списков

    Изменить ссылку Лист Excel – объект графика Powerpoint VBA

    Функция InStr не работает

    Excel VBA: ожидание другого приложения для завершения действия OLE, когда макрос пытается открыть другую книгу

    Как я могу удалить элемент из массива?

    Версия EXCEL datetime изменилась на «42507» после импорта в SQL Server

    SQL to Excel Export, сегодняшние данные дают ошибку Excel в отношении расширения коррупции / файла

    Функция «Влево / Вправо», чтобы получить номер и разделить?

    Excel: как генерировать серийный номер каждый раз, когда соседний столбец попадает в новый символ

    Давайте будем гением компьютера.