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#.
- Создадим UploadController.cs
- Добавим туда неймспейсы:
using OfficeOpenXml; using System.IO; using System.Text; using System.Configuration; using System.Data.SqlClient;
- Создадим метод со страницей формы загрузки
[HttpGet] public ActionResult Index() { return View(); }
- Создадим вьюху для этого метода 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. Без него загрузка работать не будет.
- Добавим HttpPost метод Index. В него будет переданы данные из формы.
[HttpPost] public ActionResult Upload(HttpPostedFileBase file)
- Отправить в форму можно какой угодно файл, а не только в формате 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].
- Добавим вспомогательный метод в контроллер. Он будет использоваться для чтения данных из конкретной ячейки таблицы.
private string GV(ExcelWorksheet sheet,int rowNo, int cellNo) { return sheet.Cells[rowNo, cellNo].Value != null ? sheet.Cells[rowNo, cellNo].Value.ToString() : ""; }
- Генерация скрипта, привожу кусок кода целиком.
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 <= 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)); } } }
- Выполняем запрос
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString)) { var command = new SqlCommand(sbSql.ToString(), connection); connection.Open(); int result = command.ExecuteNonQuery(); }
- Ну и осталось только сообщить об успешной операции пользователю. Например так:
return Content("success");
Хотелось бы добавить, что предложенный вариант импорта не единственно возможный, различных библиотек для работы c xlsx много. Мы работаем с EPPlus не только для чтения и для генерации документов и все задачи решаем вполне успешно.
Если же данный метод у вас работает слишком медленно, то советую обратить внимание на SqlBulkCopy. Эта библиотека работает очень быстро и подойдет даже для импорта миллионов строк данных.
Ваше мнение по поводу данного метода очень важно мне и другим читателям. Не поленитесь и напишите комментарий, если вас заинтересовала эта статья.
Я писал комментарий на страничке блога в контакте, не прочитав статьи. Но теперь я понял смысл этой библиотеки, и мне понравилось! Но прочитал статью бегло поэтому есть 2 вопроса: 1)Насколько я понял из вышеприведенного текста—это просто способ с сайта залить файл экселя в таблицу sql server. 2) Но как я полагаю эта же библиотека поможет из экселя эти данные размещать на сайте непосредственно для ….ну положим заказчика .Другими словами, развивая вопрос номер два предположим стоит такая задача: Есть конторка, в которой бугалтера мастера и любители и даже фанаты экселя. Все в нем хранят и делают. Контора например в Орле. И есть головной офис например в Москве. В Москве хотят видеть переодически данные (возможно даже результат обработки этих таблиц, сводку из этих таблиц) на сайте компании (в закрытом или открытом доступе вопрос вторичный). Так вот главбух в Орле подходит к сисадмину и говорит -чт мол хотят видеть и при этом мы (бугалтера) все время теряем свои файлы экселя и за них боимся. Таким образом сисадмин благодаря этой библиотеке может и эксель бекапить в базу на sql server и на сайт выкладывать сводки, предварительно их обработав в коде? Так я понял? Подходит эта библиотека для такого решения?
Эта библиотека может читать и генерировать файлы в формате xlsx.
По конкретному вопросу:
В принципе можно создать под каждый документ таблицу в базе и скрипт для копирования данных. Но это все таки не однозначный процесс и скопируются только данные, а вот формулы и прочее останется.
Для этой задачи я бы просто предложил использовать любой SVN , например GIT или Mercurial