Выгрузка данных из SQL в Excel

Microsoft Office Excel явялется очень удобным средством для работы с табличными данными, построением отчетов и т.д. Разрботчики языка C# предусмотрели возможность для работы и с Excel. У разработчиков есть возможность выгружать данные напрямую в документ, создавать сводные отчеты, диагарммы, в общем делать всё, что можно делать напрямую в документе Excel. В этой статье, мы будем выгружать из базы данных SQL данные в документ Excel.

При написании статьи использовался Microsoft Office 2003. Но думаю, что для других версий этот код будет тоже работать.

Чтобы иметь возможность работать с Excel нем необходимо подключить ссылку на  библиотеку Microsoft Excel Object Library.  Вызвав конткстное меню на Reference выбираем Add Reference (рис. 1). В появившемся окне выбираем Microsoft.Office.Interop.Excel. 

 

Рис. 1 Добавить ссылку 

Рис. 2 Add Reference Необходимые подготовительные действия мы выполнили, теперь займемся программированием. Для начала подключим необходимое простанство имен:

Для работы с SQL

using System.Data.SqlClient;

для удобной работы с Excel создадим  псевдоним для простанства имен:

using Excel = Microsoft.Office.Interop.Excel;

Сначала займемся методом, который будет выгружать данные из SQL в DataTable:

private DataTable GetData()
{
   //строка соединения
   string connString = @"Data Source=DEVELOPER;Initial Catalog=Northwind;Integrated Security=SSPI; Connection Timeout=260";

   SqlConnection con = new SqlConnection(connString);

   DataTable dt = new DataTable();
   try
   {
      string query = @"SELECT     Customers.CompanyName, Customers.ContactName, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry
FROM         Customers INNER JOIN
                      Orders ON Customers.CustomerID = Orders.CustomerID";
      SqlCommand comm = new SqlCommand(query, con);

      con.Open();
      SqlDataAdapter da = new SqlDataAdapter(comm);
      DataSet ds = new DataSet();
      da.Fill(ds);
      dt = ds.Tables[0];
   }
   catch(Exception ex)
   {
      MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
   }
   finally
   {
      con.Close();
      con.Dispose();
   }
   return dt;
}

У нас есть данные, теперь необходимо загрузить их в ексель. Делается это очень просто, простым перебором строк и колонок DataTable:

private void button1_Click(object sender, EventArgs e)
{
    xlApp = new Excel.Application();

    try
    {
        //добавляем книгу
        xlApp.Workbooks.Add(Type.Missing);

        //делаем временно неактивным документ
        xlApp.Interactive = false;
        xlApp.EnableEvents = false;

        //выбираем лист на котором будем работать (Лист 1)
        xlSheet = (Excel.Worksheet)xlApp.Sheets[1];
        //Название листа
        xlSheet.Name = "Данные";

        //Выгрузка данных
        DataTable dt = GetData();

        int collInd = 0;
        int rowInd = 0;
        string data = "";

        //называем колонки
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            data = dt.Columns[i].ColumnName.ToString();
            xlSheet.Cells[1, i + 1] = data;

            //выделяем первую строку
            xlSheetRange = xlSheet.get_Range("A1:Z1", Type.Missing);

            //делаем полужирный текст и перенос слов
            xlSheetRange.WrapText = true;
            xlSheetRange.Font.Bold = true;
        }

        //заполняем строки
        for (rowInd = 0; rowInd < dt.Rows.Count; rowInd++)
        {
            for (collInd = 0; collInd < dt.Columns.Count; collInd++)
            {
                data = dt.Rows[rowInd].ItemArray[collInd].ToString();
                xlSheet.Cells[rowInd + 2, collInd + 1] = data;
            }
        }

        //выбираем всю область данных
        xlSheetRange = xlSheet.UsedRange;

        //выравниваем строки и колонки по их содержимому
        xlSheetRange.Columns.AutoFit();
        xlSheetRange.Rows.AutoFit();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        //Показываем ексель
        xlApp.Visible = true;

        xlApp.Interactive = true;
        xlApp.ScreenUpdating = true;
        xlApp.UserControl = true;

        //Отсоединяемся от Excel
        releaseObject(xlSheetRange);
        releaseObject(xlSheet);
        releaseObject(xlApp);
    }
}

Освобождаем ресурсы и отсоединяемся от ексель:

//Освобождаем ресуры (закрываем Excel)
void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show(ex.ToString(), "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
        GC.Collect();
    }
}

В результате, после завершения работы программы, будет открыт документ Excel с данными, которые мы хотели выгрузить.

[download id=”7″]