Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
VPS в 21 локации

От 104 рублей в месяц

Безлимитный трафик. Защита от ДДоС.

🔥 VPS до 5.7 ГГц под любые задачи с AntiDDoS в 7 локациях

💸 Гифткод CITFORUM (250р на баланс) и попробуйте уже сейчас!

🛒 Скидка 15% на первый платеж (в течение 24ч)

Скидка до 20% на услуги дата-центра. Аренда серверной стойки. Colocation от 1U!

Миграция в облако #SotelCloud. Виртуальный сервер в облаке. Выбрать конфигурацию на сайте!

Виртуальная АТС для вашего бизнеса. Приветственные бонусы для новых клиентов!

Виртуальные VPS серверы в РФ и ЕС

Dedicated серверы в РФ и ЕС

По промокоду CITFORUM скидка 30% на заказ VPS\VDS

2006 г.

Excel ЧаВо

Александр Шабля, Королевство Delphi
Все вопросы Excel.Application Worksbooks и Worksheets Cells, Range, Rows и Columns Chart, Shape, Picture



Как получить данные из ADODataSet?

Если у вас есть открытый RecordSet (свойство всех наследников TCustomADODataSet), то из него в любую ячейку листа можно получить данные. В примере данные из ADODataSet1 будут вставлены в область, начиная с ячейки A2.

Delphi:

    ASheet := (XL.Sheets[1] as _Worksheet);
    ASheet.Range['A2', EmptyParam].CopyFromRecordset(
      ADODataSet1.Recordset,
      EmptyParam,
      EmptyParam
    );

C#:

      ADODB._Recordset objRS = null;
      object objRecAff = null;
      ADODB.Connection objConn = new ADODB.Connection();
      // Excel
      Excel.Worksheet oSheet = null;
      Excel.Range oRng = null;
      Excel.Application XL = new Excel.Application();
      try {
        XL.Visible = true;
        objConn.Open("Provider=\"Microsoft.Jet.OLEDB.4.0\";Data Source=\"" +
          Environment.GetFolderPath(Environment.SpecialFolder.CommonProgramFiles) +
          "\\Borland Shared\\Data\\dbdemos.mdb\";Persist Security Info=False;",
          "", "", 0);
        objRS = (ADODB._Recordset) objConn.Execute("employee", out objRecAff,
          (int) ADODB.CommandTypeEnum.adCmdTable);
        XL.Workbooks.Add(Type.Missing);  
        oSheet = (Excel.Worksheet) XL.ActiveSheet;
        oRng = oSheet.get_Range("A1", Type.Missing);
        for (int i = 0; i < objRS.Fields.Count; i++) {
          oRng.get_Offset(0, i).Value2 = objRS.Fields[i].Name;
        }
        oRng = oSheet.get_Range("A2", Type.Missing);
        oRng.CopyFromRecordset(objRS, Type.Missing, Type.Missing);
        oRng = oSheet.UsedRange.EntireColumn;
        oRng.AutoFit();
        XL.ActiveWorkbook.Saved = true;
      }
      finally {
        objConn.Close();
        objConn = null;
        objRS = null;
        objRecAff = null;
        oRng = null;
        oSheet = null;
        XL.UserControl = true;
        XL = null;
      }

Т.к. в ADO.NET не существует такого объекта как RecordSet, то в C# приходится подключаться к старому знакомому ADODB (не забудьте добавить в References проекта "Microsoft ActiveX Data Objects 2.8 Library" на вкладке "COM Imports").

How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET

CopyFromRecordset Method

How To Transfer Data from an ADO Recordset to Excel with Automation

Range.CopyFromRecordset Method


Как получить данные в Excel, используя QueryTable?

Delphi:

var
  AQueryTbl: ExcelQueryTable;
...
  XL := StartExcel;
  try
    ASheet := (XL.Sheets[1] as _Worksheet);
    R := ASheet.Range['A1', EmptyParam]; // начальная ячейка для данных

    AQueryTbl := ASheet.QueryTables.Add(
      'OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;' +
      'Data Source=' + ExpandFileName('DbDemos.xls') + ';' +
      'Extended Properties="Excel 8.0;";',
      R,
      'select * from [employee$]'

    );
    AQueryTbl.RefreshStyle := xlInsertEntireRows;
    AQueryTbl.Refresh(False);

C#:

      Excel.Worksheet oSheet = null;
      Excel.Range oRng = null;
      Excel.QueryTable oQT = null;
      Excel.Application XL = new Excel.Application();
      try
      {
        XL.Visible = true;
        XL.Workbooks.Add(Type.Missing);  
        oSheet = (Excel.Worksheet) XL.ActiveSheet;
        oRng = oSheet.get_Range("A1", Type.Missing);
        oQT = (Excel.QueryTable) oSheet.QueryTables.Add(
          "OLEDB;Provider=\"Microsoft.Jet.OLEDB.4.0\";Data Source=\"" +
          Environment.GetFolderPath(Environment.SpecialFolder.CommonProgramFiles) +
          "\\Borland Shared\\Data\\dbdemos.mdb\";Persist Security Info=False;",
          oRng, 
          "select * from Employee"

        );
        oQT.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
        oQT.Refresh(false);
        XL.ActiveWorkbook.Saved = true;
      }
      finally
      {
        oQT = null;
        oRng = null;
        oSheet = null;
        XL.UserControl = true;
        XL = null;
      }

После получения данных, в книгу будет добавлена именованная область ячеек, содержащая импортированную таблицу.

QueryTables Property

Add Method

QueryTable Object


Как подключить книгу Excel как базу данных, используя поставщика данных Jet OLE DB Provider?

Для подключения книги Excel как базы данных нужно воспользоваться Microsoft Jet OLE DB провайдером и указать в свойстве соединения Extended Properties=Excel 8.0.

Delphi:

const
  ConStr =
    'Provider=Microsoft.Jet.OLEDB.4.0;' +
    'Data Source=%s;' +
    'Extended Properties="Excel 8.0;HDR=Yes;";';

var
  Conn: TADOConnection;
...
    Conn.ConnectionString := Format(ConStr, [ExpandFileName('DbDemos.xls')]);
    Conn.Open;

C#:

      System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
      oConn.ConnectionString =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        Environment.CurrentDirectory + @"\DbDemos.xls;" +
        "Extended Properties=\"Excel 8.0;HDR=Yes;\";";
      oConn.Open;

ADO Provider Properties and Settings

Connect to Excel with ADO

ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases

How To Transfer Data from ADO Data Source to Excel with ADO

OLE DB Tutorial (C# Programmer's Reference)


Как получить данные из таблицы (листа) Excel SQL запросом?

Данные из листа Excel, подключенного через ADO, можно показать в DBGrid, добавлять, править, удалять строки. Используем подключение из предыдущего примера.

Delphi:

    ADODataSet1.Connection := Conn;

    ADODataSet1.CommandText :=
      'select * from [Лист1$]'#10 +
      'where [HireDate] >= #01/01/1994#';
    ADODataSet1.Open;

// Можно использовать именованную область ячеек, названную "MyRange"

    ADODataSet1.CommandText := 'select * from MyRange';
    ADODataSet1.Open;

// Возможно использовать указаную область ячеек
    ADODataSet1.ParamCheck := False; // ОБЯЗАТЕЛЬНО, иначе exception!!!
    ADODataSet1.CommandText := 'select * from [Лист1$A12:F42]';

C#:

      System.Data.OleDb.OleDbCommand oCmd = new System.Data.OleDb.OleDbCommand();
      System.Data.OleDb.OleDbDataAdapter oAdapt = new System.Data.OleDb.OleDbDataAdapter();
      System.Data.DataSet oDS = new System.Data.DataSet("DbDemos");
      oCmd.Connection = oConn;
      // Выборка данных из одной таблицы (листа) с именем "Employee"
      oCmd.CommandText = "select * from [Employee$]";
      // Выборка данных из именованной области ячеек c именем "MyRange"
      oCmd.CommandText = "select * from MyRange";
      // Выборка из заданной области ячеек
      oCmd.CommandText = "select * from [Employee$A10:F40]";
      // Выборка из 3 связанных таблиц (листов) книги
      oCmd.CommandText =
        "SELECT O.OrderNo, O.SaleDate, O.PaymentMethod, O.ItemsTotal,\n" +
        "E.FirstName, E.LastName, C.Company\n" +
        "FROM [customer$] AS C\n" +
        "  INNER JOIN ([employee$] AS E\n" +
        "  INNER JOIN [orders$] AS O ON E.EmpNo = O.EmpNo)\n" +
        "  ON C.CustNo = O.CustNo";
      // Получам данные в DataSet посредством OleDbDataAdapter'а
      oAdapt.SelectCommand = oCmd;
      oAdapt.Fill(oDS, "Employee");
      // Покажем данные в DataGrid'е
      dataGrid1.DataSource = oDS.Tables["Employee"];

Внимание! Если данные были получены в DataSet из именованной области ячеек, то, при попытке добавить новые строки, вы получите исключение "Cannot expand named range". Если данные были получены с указанием области ячеек, то новые записи будут добавляться после последней строки диапазона, но, если будет вызван метод Requey, эти новые строки не будут включены в DataSet.

Retrieve and Edit Excel Data with ADO

How to query and display excel data by using ASP.NET, ADO.NET, and Visual C# .NET

You receive error messages when you try to use ADO.NET OLEDbDataAdapter to modify an Excel workbook

How To Use ADO with Excel Data from Visual Basic or VBA


Как вставить данные из таблицы Access в таблицу Excel, используя ADO?

Заметьте, что при вставке данных таблица в Excel должна быть уже предварительно создана, т.е. должен быть лист с именем, например, "Country" и в первой строке листа заданы имена полей таблицы.

Delphi:

const
  InsCmd =
    'insert into [Country$]'#10 + // Country - имя листа в книге

    'select * from Country in "%s"'; // Country - имя таблицы в 
var Conn: TADOConnection;
...
    Conn.Execute(Format(InsCmd, ['dbdemos.mdb']));

INSERT INTO Statement

IN Clause


Какие существуют ограничения при использовании книги Excel как БД?

  • Размер листа (таблицы): 65 536 строк на 256 столбцов;
  • Содержимое ячейки (текст): 32 767 символов;
  • Листов в книге: ограничено доступной памятью;
  • Имен в книге: ограничено доступной памятью.

Хотя книга Excel и может выступать в качестве базы данных, не стоит думать, что это полноценная БД. Таблицы там "голые" — ни индексов, ни триггеров, ни хранимых процедур и других возможностей "стандартных" баз данных. Также велика вероятность неправильного определения типа поля таблицы при открытии ее в DataSet при помощи ADO.

Огромная благодарность Елене Филипповой за помощь и поддержку при написании FAQ.

How To Use ADO with Excel Data from Visual Basic or VBA

Excel Source

Excel Destination

Working with ADO.NET Datasets in Microsoft Office

Microsoft Excel 2003 Language Reference

Understanding the Excel Object Model from a .NET Developer's Perspective

Chapter 6: ActiveX Data Objects

Automating Microsoft Office 97 and Microsoft Office 2000



К материалу прилагаются файлы:


Все вопросы Excel.Application Worksbooks и Worksheets Cells, Range, Rows и Columns Chart, Shape, Picture
VPS/VDS серверы. 30 локаций на выбор

Серверы VPS/VDS с большим диском

Хорошие условия для реселлеров

4VPS.SU - VPS в 17-ти странах

2Gbit/s безлимит

Современное железо!

Бесплатный конструктор сайтов и Landing Page

Хостинг с DDoS защитой от 2.5$ + Бесплатный SSL и Домен

SSD VPS в Нидерландах под различные задачи от 2.6$

✅ Дешевый VPS-хостинг на AMD EPYC: 1vCore, 3GB DDR4, 15GB NVMe всего за €3,50!

🔥 Anti-DDoS защита 12 Тбит/с!

Новости мира IT:

Архив новостей

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 495 7861149
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2019 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...