none
OLEDB/Возвращаемый тип данных при "Extended Properties=Excel 12.0 Xml" RRS feed

  • Вопрос

  • Доброго времени суток. Использую C# Visual Studio 2017Community.

    1. Создаю"cmd.ExecuteNonQuery()" таблицу EXCEL: Create table [Test](Record_type FirstName VarChar(27),TestName int)

    Строка подключения: conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                                                testx + "; Extended Properties=Excel 12.0 Xml";

    или

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                                                testx + "; Extended Properties="\Excel 12.0 Xml;HDR=YES;IMEX=0\"";


    2. Заполняю адаптер:MyAdapter.Fill(MyDataSet); //Таблица содержит только одну строку заголовка.

    3.Проверяю тип данных для колонки "TestName ". Type type = MyDataSet.Tables[0].Columns[1].DataType;

    4. После заполнения числами столбца "TestName" "Insert into [CDR] (Record_type,TestName) Values (?,?)"

      MyAdapter.Update(MyDataSet);

    Excel отображает числа как строки, а нужно как числа.

    *************************************************************

    Для строк подключения ниже числа в Excel отображаются как числа :

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " +
                         testx + "; Extended Properties=Excel 8.0";

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                         testx + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"";

    Для этих строк подключения  тип данных для колонки "TestName ". Type type = MyDataSet.Tables[0].Columns[1].DataType возвращается как "Double"

    в отличие от строки подключения где
    Extended Properties=Excel 12.0 Xml.

    **************************************************************************

    Пока не могу сделать чтобы числа отобразились как числа в Excel, а не как строки для Extended Properties=Excel 12.0 Xml.



    15 марта 2019 г. 2:18

Ответы

  • OLE DB ориентирован на работу с реляционными БД, где каждый столбец таблицы имеет определенный тип. Однако, документы Excel не являются реляционными БД; ячейки в столбце могут иметь смешанные типы, а формат отображения является вообще отдельным атрибутом, не имеющим отношение к данным. Когда вы подключаетесь к Excel-документу через OLE DB с параметром IMEX=0, драйвер БД пытается угадать тип столбца, анализируя некоторое количество начальных строк и выбирая тот, который встречается чаще. Если же IMEX=1, то драйвер должен воспринимать все как текст (но из-за бага в Jet это в действительности не работает, пока не установить определенные ключи в реестре). Способа принудительно воспринимать все как число нет.

    Словом, если вам нужен продвинутый контроль над типами данных, OLE DB - неподходящее средство. Используйте вместо него:

    Office Automation - для клиентских приложений, запускаемых на компьютерах с установленным Office

    Open XML SDK - для серверных приложений, или для приложений, запускаемых на компьютерах без Office

    • Помечено в качестве ответа Y_VS 15 марта 2019 г. 6:08
    15 марта 2019 г. 3:27
  • Насколько я помню, да, но я не уверен, распространяется это только на XLS или также и на XLSX. Вы можете проверить это сами, создав таблицу, в которой первые ~20 строк заняты числами, а далее идут текстовые значения, и попытавшись считать ее. При IMEX=1 все значения должны считаться как текст. Если числа считаются как числа, а текст - как NULL, это тот же баг, что и в Jet.
    • Помечено в качестве ответа Y_VS 15 марта 2019 г. 6:06
    15 марта 2019 г. 5:12
  • Получилось. Оказалось это было из-за "conn.Close();".
    Когда я ранее проверял(Type type = MyDataSet.Tables[0].Columns[1].DataType;) какой тип у столбца, для которого установил "int" в команде "create table",
    то получал "string" вместо "Double".

    *******************************

    cmd.ExecuteNonQuery(); //Создание таблицы Excel.
    conn.Close();
    MyAdapter.Fill(MyDataSet); //Заполнение адаптера заголовками.
    Type type = MyDataSet.Tables[0].Columns[1].DataType;
    Далее код где используется адаптер с командами Insert, заполнение DataTable и MyAdapter.Update(MyDataSet);.

    ********************************************

    Когда закомментировал conn.Close();, то после выполнения "Type type = MyDataSet.Tables[0].Columns[1].DataType;" тип стал Double.
    А "conn.Close();"нужно поставить обязательно после MyAdapter.Update(MyDataSet); Если поставить перед то работать не будет правильно.
    Получается что  для правильно сохранения нужно что бы было открыто соединение до окончания выполнения MyAdapter.Update(MyDataSet);.


    • Помечено в качестве ответа Y_VS 15 марта 2019 г. 20:51
    15 марта 2019 г. 20:51

Все ответы

  • Доброго времени суток. Использую C# Visual Studio 2017Community.

    1. Создаю"cmd.ExecuteNonQuery()" таблицу EXCEL: Create table [Test](Record_type FirstName VarChar(27),TestName int)

    Строка подключения: conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                                                testx + "; Extended Properties=Excel 12.0 Xml";

    или

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                                                testx + "; Extended Properties="\Excel 12.0 Xml;HDR=YES;IMEX=0\"";


    2. Заполняю адаптер:MyAdapter.Fill(MyDataSet); //Таблица содержит только одну строку заголовка.

    3.Проверяю тип данных для колонки "TestName ". Type type = MyDataSet.Tables[0].Columns[1].DataType;

    4. После заполнения числами столбца "TestName" "Insert into [CDR] (Record_type,TestName) Values (?,?)"

      MyAdapter.Update(MyDataSet);

    Excel отображает числа как строки, а нужно как числа.

    *************************************************************

    Для строк подключения ниже числа в Excel отображаются как числа :

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " +
                         testx + "; Extended Properties=Excel 8.0";

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                         testx + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"";

    Для этих строк подключения  тип данных для колонки "TestName ". Type type = MyDataSet.Tables[0].Columns[1].DataType возвращается как "Double"

    в отличие от строки подключения где
    Extended Properties=Excel 12.0 Xml.

    **************************************************************************

    Пока не могу сделать чтобы числа отобразились как числа в Excel, а не как строки для Extended Properties=Excel 12.0 Xml.



    Это:

    возвращаемый тип для:

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " +
                         testx + "; Extended Properties=Excel 8.0";

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " +
                         testx + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"";

    15 марта 2019 г. 2:27
  • OLE DB ориентирован на работу с реляционными БД, где каждый столбец таблицы имеет определенный тип. Однако, документы Excel не являются реляционными БД; ячейки в столбце могут иметь смешанные типы, а формат отображения является вообще отдельным атрибутом, не имеющим отношение к данным. Когда вы подключаетесь к Excel-документу через OLE DB с параметром IMEX=0, драйвер БД пытается угадать тип столбца, анализируя некоторое количество начальных строк и выбирая тот, который встречается чаще. Если же IMEX=1, то драйвер должен воспринимать все как текст (но из-за бага в Jet это в действительности не работает, пока не установить определенные ключи в реестре). Способа принудительно воспринимать все как число нет.

    Словом, если вам нужен продвинутый контроль над типами данных, OLE DB - неподходящее средство. Используйте вместо него:

    Office Automation - для клиентских приложений, запускаемых на компьютерах с установленным Office

    Open XML SDK - для серверных приложений, или для приложений, запускаемых на компьютерах без Office

    • Помечено в качестве ответа Y_VS 15 марта 2019 г. 6:08
    15 марта 2019 г. 3:27
  • OLE DB ориентирован на работу с реляционными БД, где каждый столбец таблицы имеет определенный тип. Однако, документы Excel не являются реляционными БД; ячейки в столбце могут иметь смешанные типы, а формат отображения является вообще отдельным атрибутом, не имеющим отношение к данным. Когда вы подключаетесь к Excel-документу через OLE DB с параметром IMEX=0, драйвер БД пытается угадать тип столбца, анализируя некоторое количество начальных строк и выбирая тот, который встречается чаще. Если же IMEX=1, то драйвер должен воспринимать все как текст (но из-за бага в Jet это в действительности не работает, пока не установить определенные ключи в реестре). Способа принудительно воспринимать все как число нет.

    Словом, если вам нужен продвинутый контроль над типами данных, OLE DB - неподходящее средство. Используйте вместо него:

    Office Automation - для клиентских приложений, запускаемых на компьютерах с установленным Office

    Open XML SDK - для серверных приложений, или для приложений, запускаемых на компьютерах без Office

    Спасибо!

    "но из-за бага в Jet это в действительности не работает " это имеется в виду строка подключения с "Provider=Microsoft.Jet.OLEDB.4.0; ?

    Если да то и для "Provider=Microsoft.ACE.OLEDB.12.0" с Extended Properties=Excel 8.0 тоже баг?

    15 марта 2019 г. 4:23
  • Насколько я помню, да, но я не уверен, распространяется это только на XLS или также и на XLSX. Вы можете проверить это сами, создав таблицу, в которой первые ~20 строк заняты числами, а далее идут текстовые значения, и попытавшись считать ее. При IMEX=1 все значения должны считаться как текст. Если числа считаются как числа, а текст - как NULL, это тот же баг, что и в Jet.
    • Помечено в качестве ответа Y_VS 15 марта 2019 г. 6:06
    15 марта 2019 г. 5:12
  • Проверил ещё как при "Extended Properties=Excel 12.0 Xml" сохраняется время и дата из DataTable в Excel.

    Время как время, дата как дата.

    Числа как строки :(

    Не хочется уходить от OleDB, тем более я только делаю только Insert-ы из DataTable в Excel. Смешанных типов нет.(целые числа, дата, время).

    15 марта 2019 г. 13:41
  • Если вы файл заполняете с нуля, можно попробовать создавать таблицы SQL-запросом с явным указанием типов, а не использовать то, что создано самим Excel. Как-то так:

    CREATE TABLE [Table] ([Column1] TEXT, [Column2] FLOAT)

    15 марта 2019 г. 14:12
  • Я так и делаю. В Excel-е я ничего не создаю. В C# через cmd.ExecuteNonQuery() создаю таблицу.


    15 марта 2019 г. 14:48
  • Ага, увидел. Сейчас попробовал сам, у меня вроде отображает как число

     
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.OleDb;
    
    namespace ConsoleTest
    {  
        class Program
        {   
            static void Main(string[] args)
            {
                OleDbConnection conn = new OleDbConnection();            
                conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = \"D:\\Test\\book.xlsx\"; Extended Properties=Excel 12.0 Xml";
                conn.Open();
    
                OleDbCommand cmd = new OleDbCommand("Create table [Test](A TEXT,B FLOAT)", conn);
                var res = cmd.ExecuteNonQuery();
                Console.WriteLine("Create table " + res.ToString());
    
                cmd = new OleDbCommand("Insert into [Test] (A,B) Values ('hello',1.1)", conn);
                res = cmd.ExecuteNonQuery();
                Console.WriteLine("Insert " + res.ToString());
    
                Console.ReadKey();
            }
        }
    
    
    }

    Результат:

    1,1 выровнено по правому краю, как число.


    15 марта 2019 г. 17:01
  • Спасибо. Буду смотреть. Я ещё после создания таблица через дата адаптер вызываю метод fill,

    заполняю datatable и выполняю update.

    А с целыми числами тоже все нормально?

    15 марта 2019 г. 17:16
  • С целыми числами тоже все нормально если делать по вашему примеру.
    15 марта 2019 г. 17:32
  • Если в DataTable при заполнении через адаптер где-то генерируется тип string вместо правильного типа, скорее всего, придется кое-что переписать. Либо заполнять DataTable вручную, явно задавая правильный тип для столбцов, либо менять InsertCommand у адаптера, вставляя преобразование в нужный тип. 
    15 марта 2019 г. 18:11
  • Да. Буду смотреть.

    Если  вручную подставляю одну команду из вашего примера(т.е. записываю в excel одну строку) перед выполнением Update, то после выполнения Update весь столбец становится числовым.

    15 марта 2019 г. 18:32
  • Получилось. Оказалось это было из-за "conn.Close();".
    Когда я ранее проверял(Type type = MyDataSet.Tables[0].Columns[1].DataType;) какой тип у столбца, для которого установил "int" в команде "create table",
    то получал "string" вместо "Double".

    *******************************

    cmd.ExecuteNonQuery(); //Создание таблицы Excel.
    conn.Close();
    MyAdapter.Fill(MyDataSet); //Заполнение адаптера заголовками.
    Type type = MyDataSet.Tables[0].Columns[1].DataType;
    Далее код где используется адаптер с командами Insert, заполнение DataTable и MyAdapter.Update(MyDataSet);.

    ********************************************

    Когда закомментировал conn.Close();, то после выполнения "Type type = MyDataSet.Tables[0].Columns[1].DataType;" тип стал Double.
    А "conn.Close();"нужно поставить обязательно после MyAdapter.Update(MyDataSet); Если поставить перед то работать не будет правильно.
    Получается что  для правильно сохранения нужно что бы было открыто соединение до окончания выполнения MyAdapter.Update(MyDataSet);.


    • Помечено в качестве ответа Y_VS 15 марта 2019 г. 20:51
    15 марта 2019 г. 20:51
  • Точно, есть такой подводный камень. Так как Jet/ACE - это файл-серверный движок, он использует промежуточные буферы и окончательное обновление данных в целевом файле может происходить только после закрытия соединения. Для обычной работы с файлами есть Flush, а для OLE DB его аналога я не нашел.
    16 марта 2019 г. 7:50