Импорт в базу данных из документа Microsoft Excel при помощи EPPlus за 10 шагов

Microsoft Excel очень популярная программа (и это вполне заслужено).  С версии Excel 2007 появился формат «xlsx». Этот формат открытый и его используют также и другие программы, например, OpenOffice Calc.  Вот с документов такого формата и будут импортироваться данные в базу.

Простым пользователям нравится работать с таблицами в Экселе. Логика работы программы понятна и проста. Поэтому данные очень часто представляются именно в таком виде. Разберем  один из вариантов импорта в реляционную базу данных SQL Server. Хотя точно такой прием может быть применен и для импорта в любую другую базу, например MySQl.

Для работы с xlsx понадобится библиотека EPPlus (Allows Excel files(xlsx;xlsm) to be created on the server). Взять ее можно http://epplus.codeplex.com

Конкретно будет использоваться версия EPPlus.dll 4.0.4.0, проект asp.net mvc razor, язык программирования C#.

  1. Создадим UploadController.cs
  2. Добавим туда неймспейсы:
using OfficeOpenXml;
using System.IO;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
  1. Создадим метод со страницей формы загрузки
[HttpGet]
public ActionResult Index()
{
    return View();
}
  1. Создадим вьюху для этого метода Index.cshtml
@using (Html.BeginForm("Index","Upload",FormMethod.Post,new {enctype = "multipart/form-data"}))
{
    
    <fieldset>
        <label for="file">Выберите файл для загрузки</label>
        <input type="file" name="file" id="file" />
    </fieldset>

    
    @Html.ValidationSummary()
    <input type="submit" value="Импортировать" />
}

Обратите внимание у формы должен быть прописан атрибут enctype. Без него загрузка работать не будет.

  1. Добавим HttpPost метод Index. В него будет переданы данные из формы.
[HttpPost]
public ActionResult Upload(HttpPostedFileBase file)

 

  1. Отправить в форму можно какой угодно файл, а не только в формате xlsx. Поэтому добавляем простейшую валидацию.
if (file == null || file.InputStream == null)
{
    ModelState.AddModelError("file", "Файл не выбран");
    return View();
}

if (Path.GetExtension(file.FileName) != ".xlsx")
{
    ModelState.AddModelError("file", "Выберите файл с расширением xlsx");
    return View();
}

Импорт будет состоять из следующих этапов:

  • генерация SQL скрипта на основе данных из файла
  • выполнение SQL скрипта в базе получателе

Условно считаем, что таблица получатель называется [TableDestination].

  1. Добавим вспомогательный метод в контроллер. Он будет использоваться для чтения данных из конкретной ячейки таблицы.
private string GV(ExcelWorksheet sheet,int rowNo, int cellNo)
{
    return sheet.Cells[rowNo, cellNo].Value != null ? sheet.Cells[rowNo, cellNo].Value.ToString() : "";
}
  1. Генерация скрипта, привожу кусок кода целиком.
StringBuilder sbSql = new StringBuilder();
//если будут импортироваться даты это настройка переключит текущую сессию SQL Server в формат дат ДД.ММ.ГГГГ
sbSql.Append("set dateformat dmy;");
//очистка таблицы получателя
sbSql.Append("DELETE FROM [TableDestination];");
using (var xls = new ExcelPackage(file.InputStream))
{
    //тут подставьте имя своего листа, импортировать можно из любого листа
    using (var sheet = xls.Workbook.Worksheets["лист1"])
    {
        for (int j = sheet.Dimension.Start.Row + 1; j &lt;= sheet.Dimension.End.Row; j++)
        {
            if (sheet.Cells[j, 1].Value == null) continue;

            sbSql.AppendFormat(@"INSERT INTO [TableDestination] ([Column1],[Column2],[Column3]) VALUES ('{0}','{1}','{2}');", GV(sheet, j, 2), GV(sheet, j, 3), GV(sheet, j, 4));
        }
    }
}
  1. Выполняем запрос
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString))
{
    var command = new SqlCommand(sbSql.ToString(), connection);
    connection.Open();
    int result = command.ExecuteNonQuery();
}

 

  1. Ну и осталось только сообщить об успешной операции пользователю. Например так:
return Content("success");

Хотелось бы добавить, что предложенный вариант импорта не единственно возможный, различных библиотек для работы  c xlsx много. Мы работаем с EPPlus не только для чтения и для генерации документов и все задачи решаем вполне успешно.

Если же данный метод у вас работает слишком медленно, то советую обратить внимание на SqlBulkCopy. Эта библиотека работает очень быстро и подойдет  даже для импорта миллионов строк данных.

Ваше мнение по поводу данного метода очень важно мне и другим читателям. Не поленитесь и напишите комментарий, если вас заинтересовала  эта статья.

Импорт в базу данных из документа Microsoft Excel при помощи EPPlus за 10 шагов: 2 комментария

  1. Я писал комментарий на страничке блога в контакте, не прочитав статьи. Но теперь я понял смысл этой библиотеки, и мне понравилось! Но прочитал статью бегло поэтому есть 2 вопроса: 1)Насколько я понял из вышеприведенного текста—это просто способ с сайта залить файл экселя в таблицу sql server. 2) Но как я полагаю эта же библиотека поможет из экселя эти данные размещать на сайте непосредственно для ….ну положим заказчика .Другими словами, развивая вопрос номер два предположим стоит такая задача: Есть конторка, в которой бугалтера мастера и любители и даже фанаты экселя. Все в нем хранят и делают. Контора например в Орле. И есть головной офис например в Москве. В Москве хотят видеть переодически данные (возможно даже результат обработки этих таблиц, сводку из этих таблиц) на сайте компании (в закрытом или открытом доступе вопрос вторичный). Так вот главбух в Орле подходит к сисадмину и говорит -чт мол хотят видеть и при этом мы (бугалтера) все время теряем свои файлы экселя и за них боимся. Таким образом сисадмин благодаря этой библиотеке может и эксель бекапить в базу на sql server и на сайт выкладывать сводки, предварительно их обработав в коде? Так я понял? Подходит эта библиотека для такого решения?

    1. Эта библиотека может читать и генерировать файлы в формате xlsx.
      По конкретному вопросу:
      В принципе можно создать под каждый документ таблицу в базе и скрипт для копирования данных. Но это все таки не однозначный процесс и скопируются только данные, а вот формулы и прочее останется.
      Для этой задачи я бы просто предложил использовать любой SVN , например GIT или Mercurial

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *