Доступ к электронной таблице Excel с помощью C# иногда возвращает пустое значение для некоторых ячеек
Мне нужно получить доступ к электронной таблице Excel и вставить данные из электронной таблицы в базу данных SQL. Однако первичные ключи смешанные, большинство из них числовые, а некоторые буквенно-цифровые.
У меня проблема в том, что когда цифровые и буквенно-цифровые ключи находятся в одной и той же электронной таблице, буквенно-цифровые ячейки возвращают пустые значения, тогда как все остальные ячейки возвращают свои данные без проблем.
Я использую метод OleDb для доступа к файлу Excel. После получения данных с помощью командной строки я помещаю данные в DataAdapter, а затем заполняю DataSet. Я перебираю все строки (dr) в первой DataTable в DataSet.
Я ссылаюсь на столбцы, используя dr["..."].ToString()
Если я отлаживаю проект в Visual Studio 2008 и просматриваю "расширенные свойства", удерживая мышь над "dr", я могу просмотреть значения DataRow, но первичный ключ, который должен быть буквенно-цифровым, - {}. Другие значения заключены в кавычки, но пустое значение имеет фигурные скобки.
Это проблема C# или Excel?
Кто-нибудь когда-либо сталкивался с этой проблемой раньше, или, может быть, нашел решение / исправить?
Заранее спасибо.
10 ответов
Решение:
Строка подключения:
Поставщик =Microsoft.Jet.OLEDB.4.0; Источник данных =FilePath; Расширенные свойства ="Excel 8.0;HDR= Да;IMEX=1";
HDR=Yes;указывает, что первая строка содержит имена столбцов, а не данные.HDR=No;указывает на обратное.IMEX=1;говорит драйверу всегда читать "смешанные" (числа, даты, строки и т. д.) столбцы данных в виде текста. Обратите внимание, что этот параметр может повлиять на доступ к записи листа Excel отрицательно.
Синтаксис SQL SELECT * FROM [sheet1$], Т.е. имя листа Excel, за которым следует $ и завернутый в [] скобки.
Важный:
Проверьте [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] реестр реестра REG_DWORD "TypeGuessRows". Это ключ к тому, чтобы Excel не использовал только первые 8 строк, чтобы угадать тип данных столбцов. Установите это значение в 0 для сканирования всех строк. Это может повредить производительность.
Если книга Excel защищена паролем, вы не можете открыть ее для доступа к данным, даже указав правильный пароль в строке подключения. Если вы попытаетесь, вы получите следующее сообщение об ошибке: "Не удалось расшифровать файл".
Источник данных Excel выбирает тип столбца для всего столбца. Если одна из ячеек не точно соответствует этому типу, она оставляет такие пробелы. У нас были проблемы, когда наша машинистка вводила " 8" (пробел перед числом, поэтому Excel преобразовал его в строку для этой ячейки) в числовом столбце. Для меня будет иметь смысл попробовать методы.Net Parse, так как они более устойчивы, но я полагаю, что драйвер Excel работает не так.
Наше исправление, поскольку мы использовали службы импорта баз данных, заключалось в том, чтобы регистрировать все строки, которые "провалились" таким образом. Затем мы вернулись к документу XLS и перепечатали эти ячейки, чтобы убедиться, что базовый тип был правильным. (Мы обнаружили, что удаление пробела не устранило его - сначала нужно было очистить всю ячейку, а затем набрать "8"). Чувствует себя хакером и не уныло, но это был лучший метод, который мы нашли. Если драйвер Excel не может правильно прочитать его, вы ничего не можете сделать, чтобы получить эти данные, как только вы окажетесь в.Net.
Еще один случай, когда Office скрывает важные детали от пользователей во имя простоты, и, следовательно, усложняет задачу, когда вам нужно быть точным в использовании энергии.
{} Означает, что это какой-то пустой объект, а не строка. Когда вы наводите курсор на объект, вы должны увидеть его тип. Точно так же, когда вы используете quickwatch для просмотра dr["..."], вы должны увидеть тип объекта. Какого типа объект вы получаете?
Для совместимости с VISTA вы можете использовать драйвер EXCEL 12.0 в строке подключения. Это должно решить вашу проблему. Это сделал мой.
Я ответил на аналогичный вопрос здесь. Здесь я скопировал и вставил один и тот же ответ для вашего удобства:
У меня была такая же проблема, но я смог обойти ее, не прибегая к интерфейсу 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.
}
}
}
}
}
}
}
ItemArray - это объектный массив. Поэтому я предполагаю, что "столбец" в DataRow, на который я пытаюсь ссылаться, имеет тип объекта.
Решение:
- Вы ставите HDR= Нет, чтобы первая строка не считалась заголовком столбца. Строка соединения: Provider=Microsoft.Jet.OLEDB.4.0; Источник данных =FilePath; Расширенные свойства ="Excel 8.0;HDR= Нет;IMEX=1";
- Вы игнорируете первую строку и получаете доступ к данным любыми способами (DataTable, DataReader и т. Д.). Вы получаете доступ к столбцам по числовым индексам, а не по именам столбцов.
Это сработало для меня. Таким образом, вам не нужно изменять регистры!
Упорядочите записи в файле xls по коду ascii в порядке убывания, чтобы буквенно-цифровые поля появлялись вверху под строкой заголовка. Это гарантирует, что первая строка считанных данных будет определять тип данных как "varchar" или "nvarchar"
Привет весь этот код получает буквенно-цифровые значения также
using System.Data.OleDb;
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties="+(char)34+"Excel 8.0;IMEX=1;"+(char)34;
string CommandText = "select * from [Sheet1$]";
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
myConnection.Open();
OleDbDataAdapter myAdapter = new OleDbDataAdapter(CommandText, myConnection);
ds = null;
ds = new DataSet();
myAdapter.Fill(ds);
Это не совсем правильно! Очевидно, что Jet/ACE ВСЕГДА принимает строковый тип, если первые 8 строк пусты, независимо от IMEX=1. Даже когда я сделал записи в реестре равными 0, у меня все еще была та же проблема. Это был единственный верный способ заставить его работать:
try
{
Console.Write(wsReader.GetDouble(j).ToString());
}
catch //Lame unfixable bug
{
Console.Write(wsReader.GetString(j));
}