Экспорт данных из Excel в таблицу базы данных

[dmitriy22]

Доброе время суток !!!
По заголовку темы понятно о чем речь, но подробнее:
Есть файл excel, в нем есть записи, необходимо выгрузить данные в таблицу SQL.
Существует много способов выгрузки данных:
1. Можно или подключить библиотеку и работать с ней
2. Можно в sql запросом, к примеру,

Код: Выделить всё

SELECT * INTO test_new
FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\test.xls','SELECT * FROM [Лист1$]')

3. Или же сделать из excel такой же источник как sql, oledb или odbc и работать как с обычным ADO
Если вы знаете еще методы выгрузки,то пишите здесь, буду очень признателен расширить свой кругозор, может ваш метод будет проще

Зная это, я решил выбрать 3-й вариант, т.к. 1-й как мне показалось геморойный, 2-й не подходит, т.к. конечная таблица находится на sybase
и вот код который я сделал c помощью ODBC, т.к. не понял, что лучше:

Код: Выделить всё

// сначала достаю данные в datatable из Excel
OdbcConnection cn = new OdbcConnection();
cn.ConnectionString = string.Format(@"Driver={{Microsoft Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", workbookPath);
string strCom = "select * from [Лист1$]";
cn.Open();
OdbcCommand comm_mon = new OdbcCommand(strCom, cn);
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = comm_mon;
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);

// затем подключаю свою бд, также через ODBC, т.к. иначе будет ругаться
string strCon = "Dsn=srvr;uid=login;srvr=srvr;db=db;pwd=pwd";
OdbcConnection conn = new OdbcConnection(strCon);
       conn.Open();

//и потом пробегаясь по всем строкам и столбцам вставляю данные в таблицу, которую я до этого сделал в бд
foreach (DataRow row in dt.Rows)
                {
                    string strCom2 = "insert into tmp_export_excel select"
                    + "'" + row[0].ToString() + "',"
                    + "'" + row[1].ToString() + "',"
                    + "'" + row[2].ToString() + "',"
                    + "'" + row[3].ToString() + "',"
                    + "'" + row[4].ToString() + "',"
                    + "'" + row[5].ToString() + "',"
                    + "'" + row[6].ToString() + "',"
                    + "'" + row[7].ToString() + "',"
                    + "'" + row[8].ToString() + "',"
                    + "'" + row[9].ToString() + "',"
                    + "'" + row[10].ToString() + "',"
                    + "'" + row[11].ToString() + "',"
                    + "'" + row[12].ToString() + "',"
                    + "'" + row[13].ToString() + "',"
                    + "'" + row[14].ToString() + "',"
                    + "'" + row[15].ToString() + "',"
                    + "'" + row[16].ToString() + "',"
                    + "'" + row[17].ToString() + "'";


                            OdbcCommand cm = new OdbcCommand(strCom2, conn);
                            cm.ExecuteNonQuery();
                 }
  conn.Close();

  cn.Close();

                }


вот код для создания бд

Код: Выделить всё

create table tmp_export_excel
(
F1 VARCHAR(10) NULL,
F2 VARCHAR(20) NULL,
F3 VARCHAR(20) NULL,
F4 VARCHAR(20) NULL,
F5 VARCHAR(30) NULL,
F6 VARCHAR(10) NULL,
F7 VARCHAR(10) NULL,
F8 VARCHAR(50) NULL,
F9 VARCHAR(50) NULL,
F10 VARCHAR(30) NULL,
F11 VARCHAR(50) NULL,
F12 VARCHAR(20) NULL,
F13 VARCHAR(20) NULL,
F14 VARCHAR(20) NULL,
F15 VARCHAR(20) NULL,
F16 VARCHAR(20) NULL,
F17 VARCHAR(20) NULL,
F18 VARCHAR(20) NULL
)

в принципе это все работает, но я бы хотел сделать более гибкую систему:
т.е. возможно ли как-то сделать, чтобы не создавать таблицу в бд, т.к. я могу не знать количество и названия столбцов
и можно ли как-то всю datatble вставить в бд, не пробегаясь по строкам.
Т.е. в итоге, чтобы грузить любой excel файл, не зная сколько там столбцо и их названий, что мне нужно поменять или добавит в моем коде ?
а может у вас есть свой вариант, буду признателен, т.к. охота иметь более автоматизированную вещь, а так это получаетсякак одноразовая посуда
Жду предложений

[FireFenix]

Для этого нужно иметь маркер конца строк и столбцов, или какой-либо шаблон конца

Предположим у нас таблица с границей...
1) Поиск границ
a) Если используем API экзеля
- Пробегаемся от ячейки 11 до 1i, пока ячейка не будет содержать границу и i заносим в Columns
- Пробегаемся от 11 до i1, пока ячейка не будет содержать границу и i заносим в Rows
б) Если используем провайдер
- Получаем из DataReader'a FieldsCount в Columns
2) Создаём таблицу, при запросе которой генерируем нужное количество столбцов равное Columns

Код: Выделить всё

Dim Query as String = "CREATE TABLE table ("

for (i = 1; i <= Columns; i++) {
   if (i > 1)
      Query += ", ";

   Query += "column_" + i + " TEXT NULL";
}


Query &= ")"


3) Создаём запрос вставки в таблицу, при запросе которой генерируем столбцы (из п.2) и присваиваем поля из экзеля

Код: Выделить всё

string Query = "INSERT INTO table (";

for (i = 1; i <= Columns; i++) {
   if (i > 1)
      Query += ", ";

   Query += "column_" + i + " TEXT NULL";
}

Query += ") SET (";

Column = 1;

for (i = 1; i <= Rows; i++) {
   if (i > 1)
      Query += ", ";

   Text = Excel.Activeworkbook.Cell(Column, i).Text;
   //Типа получаем данные ячейки через Excel

   if (Text == null || string.IsNullOrWhiteSpace(System.Text)) {
      Query += "NULL";
   } else {
      Query += System.Text;
   }

   Column += 1;
}

Query += ")";


P.S. Код писал на коленке

[dmitriy22]

спасибо, в принципе идея понятна, те. использовать типа StringBuilder
интересно, а еще как можно сделать выгрузку из excel в таблицу бд и что лучше какое соединение odbc или oledb использовать?

[dmitriy22]

я тут столкнулся с глюком :
при выгрузке данных одна непонятная штука происходит и я никак не пойму в чем проблема :
вот код выгрузки

Код: Выделить всё

  ///подключаемся к Excel
                OdbcConnection cn = new OdbcConnection();
                cn.ConnectionString = string.Format(@"Driver={{Microsoft Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;HDR=No", c:\1.xls);

                 cn.Open();
   ///Получение данных из Excel
                string strComExcel = "select * from [sheet1$]";

                ///Выполнить выборку данных из Excel и положить ее в DataTable
                OdbcCommand comm_mon = new OdbcCommand(strComExcel, cn);
                OdbcDataAdapter da = new OdbcDataAdapter();
                da.SelectCommand = comm_mon;
                DataTable dt = new DataTable();

                da.Fill(dt);
                cn.Close();

когда я при помощи odbc закачиваю данные из excel в datatable, то закачиваются разные данные:
1.случай:

если у меня в excel такие данные :

Код: Выделить всё

col1     col2     col3     col4     col5     col6
  0        1          2        3         4        5
col1     col2     col3     col4     col5     col6

то на выходе я получаю

Код: Выделить всё

col1     col2     col3     col4     col5     col6

col1     col2     col3     col4     col5     col6

т.е. выгрузились пустые ячейки с цифрами

2.случай:

если у меня в excel такие данные :

Код: Выделить всё

col1     col2     col3     col4     col5     col6
cl0        1        cl2        3         4        5
col1     col2     col3     col4     col5     col6

то на выходе я получаю

Код: Выделить всё

col1     col2     col3     col4     col5     col6
cl0        1        cl2        3         4        5
col1     col2     col3     col4     col5     col6

т.е. выгрузились все нормально

почему так происходит, в первом случае не выгружаются цифры ???