Научная запись при импорте из Excel в.Net
У меня есть работа C#/.NET, которая импортирует данные из Excel, а затем обрабатывает их. Наш клиент отбрасывает файлы, и мы обрабатываем их (важно, потому что я не имею никакого контроля над исходным файлом).
Я использую библиотеку OleDb, чтобы заполнить набор данных (я ненавижу писать этот код. Серьезно, есть ли какой-нибудь код, которого разработчик.Net боится писать больше, чем это?). Файл содержит некоторые числа, такие как 30829300, 30071500 и т. Д. Тип данных для этих столбцов - "Текст".
Эти числа преобразуются в научные записи, когда я импортирую данные. Есть ли способ предотвратить это?
-Крис
10 ответов
Библиотека OleDb чаще всего запутывает ваши данные в электронной таблице Excel. Во многом это связано с тем, что все встраивается в макет столбца фиксированного типа, определяя тип каждого столбца из значений в первых 8 ячейках каждого столбца. Если он угадает, вы получите строки цифр, преобразованные в научную нотацию. Blech!
Чтобы избежать этого, лучше пропустить OleDb и прочитать лист непосредственно. Вы можете сделать это с помощью COM-интерфейса Excel (также!) Или стороннего.NET Excel-совместимого считывателя. 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/
Используя эту строку подключения:
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
Если вы посмотрите на фактический файл.XSLX с помощью инструмента повышения производительности Open XML SDK 2.0 (или просто разархивируете файл и просмотрите XML в блокноте), вы увидите, что Excel 2007 на самом деле хранит необработанные данные в научном формате.
Например, 0,00001 хранится как 1.0000000000000001E-5.
<x:c r="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 вызывает проблему.
У меня была такая же проблема, но я смог обойти ее, не прибегая к интерфейсу Excel COM или стороннему программному обеспечению. Это требует немного дополнительной обработки, но, похоже, работает для меня.
- Сначала прочитайте данные, чтобы получить имена столбцов
- Затем создайте новый DataSet с каждым из этих столбцов, установив для каждого из их DataTypes строку.
- Снова прочитайте данные в этот новый набор данных. Вуаля - научная запись сейчас исчезла, и все читается как строка.
Вот некоторый код, который иллюстрирует это, и в качестве дополнительного бонуса, он даже 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.
}
}
}
}
}
}
}
Найдите параметр строки подключения IMEX=1 и параметр реестра TypeGuessRows в Google. По правде говоря, не существует простого способа обойти это, потому что читатель выводит типы данных столбца, просматривая первые несколько строк (по умолчанию 8). Если строки содержат все числа, то вам не повезло.
Неудачный обходной путь, который я использовал в прошлом, состоит в том, чтобы использовать параметр строки подключения HDR=NO и установить значение параметра реестра TypeGuessRows равным 1, что вынуждает его читать первую строку как допустимые данные для определения типа данных, а не заголовок Это взлом, но это работает. Код читает первую строку (содержащую заголовок) как текст, а затем соответственно устанавливает тип данных.
Изменение реестра является проблемой (и не всегда возможно), но я бы порекомендовал восстановить первоначальное значение впоследствии.
Если ваши данные импорта не имеют строки заголовка, то альтернативным вариантом является предварительная обработка файла и вставка символа "перед каждым из чисел в столбце сбоя". Это приводит к тому, что данные столбца обрабатываются как текст.
Итак, в общем, есть куча хаков, чтобы обойти это, но на самом деле ничего надежного.
Мне интересно знать, получил ли кто-нибудь ответ на это. Я был на всех уровнях и пробовал все комбинации IMEX и HDR. IMEX=1 - единственный, с которым мне удалось извлечь значения даты, валюты и общих чисел. Но большие числа все еще показывают как научный. Мне нужно только читать файлы и менять электронные таблицы, реестр, сторонний не вариант.
Вы пытались преобразовать значение поля в (int) или, возможно, (Int64) во время чтения?
Я обнаружил, что проще всего выбрать формат Zip, а не текстовый формат для столбцов с большими "числами".
Я получил одно решение откуда-то еще, но оно отлично сработало для меня. Нет необходимости вносить какие-либо изменения в код, просто отформатируйте ячейки столбцов Excel как "Общие" вместо любого другого форматирования, такого как "число" или "текст", тогда даже "Выбрать * из [$Sheet1]" или "Выбрать имя столбца из [$Sheet1] будет читать отлично даже с большими числовыми значениями более 9 цифр
Я погуглил вокруг этого состояния.. Вот мои шаги решения
- Для шаблона файла Excel
1-формат Excel столбец как текст 2- написать макрос, чтобы отключить предупреждения об ошибках для числа -> преобразование текста
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ErrorCheckingOptions.BackgroundChecking = Ture
End Sub
Private Sub Workbook_Open()
Application.ErrorCheckingOptions.BackgroundChecking = False
End Sub
- На коде позади
3- во время чтения данных для импорта попытайтесь проанализировать входящие данные в Int64 или Int32....