Работа с MS Excel на ASP.NET MVC. Импорт и экспорт файлов

Дата: 05.08.2018, 15:17. Категория: ASP.NET MVC

Работа с типом файлов Microsoft Excel на сайте – это довольно частая задача. Импорт/экспорт прайса или номенклатуры товаров в интернет-магазине, выгрузка отчета из базы данных в виде таблицы, да все что угодно. В этом уроке посмотрим, как можно обрабатывать файлы такого типа.

На сегодняшний день существует достаточно много готовых решений (библиотек) для взаимодействия с файлами Excel, чтобы не работать напрямую с системными GridView и DataTable. Не будем изобретать велосипед и рассмотрим одну из популярных и удобных библиотек, которой я пользуюсь чаще других.

Данная библиотека называется ClosedXML. По этой ссылке находится официальный адрес проекта на GitHub. Библиотека позволяет легко манипулировать файлами MS Excel в удобной объектно-ориентированной манере. Она может быть использована на любом .NET языке программирования (C#, VisualBasic.NET), а также в проектах не только типа WebApplication.

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

Прайс-лист на услуги в виде Excel-файла
Прайс-лист на услуги в виде Excel-файла

Давайте создадим веб-приложение, которое сможет распарсить этот файл так, чтобы мы могли работать с записями в объектно-ориентированной манере, например, сохранять/обновлять бренды, модели и позиции в прайсе в базу данных. Или, например, представлять этот прайс в виде обычной html-таблицы в браузере пользователя.

Создаем в Visual Studio проект типа MVC, открываем Nuget Manager и скачиваем нужный нам пакет.

Добавляем пакет ClosedXML в проект
Добавляем пакет ClosedXML в проект

Создадим все доменные классы-модели, которые описывают предметную область.

Кстати, внизу страницы есть ссылка на архив с исходным кодом проекта.
namespace WebApplication2.Models
{
    public class PricePosition
    {
        //Неисправность
        public string Problem { get; set; }

        //Стоимость ремонта
        public string Price { get; set; }
    }

    public class PhoneModel
    {
        public PhoneModel()
        {
            PricePositions = new List<PricePosition>();
        }

        //Название модели телефона
        public string Title { get; set; }

        public List<PricePosition> PricePositions { get; set; }
    }

    public class PhoneBrand
    {
        public PhoneBrand()
        {
            PhoneModels = new List<PhoneModel>();
        }

        //Название бренда
        public string Title { get; set; }

        public List<PhoneModel> PhoneModels { get; set; }
    }
}

Описанные модели максимально простые, чтобы не усложнять пример. В них нет и не отслеживается уникальных идентификаторов, все свойства типа String и т.д.

Также определим т.н. ViewModel, то есть модель для представления. В нее заключим все объекты доменной модели, которые мы хотим показать пользователю в браузере:

public class PriceViewModel
{
    public PriceViewModel()
    {
        PhoneBrands = new List<PhoneBrand>();
    }

    public List<PhoneBrand> PhoneBrands { get; set; }

    //кол-во ошибок при импорте
    public int ErrorsTotal { get; set; }
}

Далее в вашем проекте определите какое-нибудь действие в нужном контроллере, которое будет отвечать за загрузку Excel-файла из браузера на сервер. Пример кода с html-формой в соответствующем представлении:

<div>
    @using (Html.BeginForm("Import", "Home", FormMethod.Post, new { enctype = "multipart/form-data", id = "frm-excel" }))
    {
        <div>
            Загрузите Excel-файл:
            <input type="file" name="fileExcel" id="fileExcel" />
            <div>
                <input type="submit" value="Загрузить" />
            </div>
        </div>
    }
</div>

Из примера видно, что в форме мы обращаемся к методу Import в контроллере Home. Создадим подобный метод:

[HttpPost]
public ActionResult Import(HttpPostedFileBase fileExcel)
{
    if (ModelState.IsValid)
    {
        PriceViewModel viewModel = new PriceViewModel();
        using (XLWorkbook workBook = new XLWorkbook(fileExcel.InputStream, XLEventTracking.Disabled))
        {
            foreach (IXLWorksheet worksheet in workBook.Worksheets)
            {
                PhoneBrand phoneBrand = new PhoneBrand();
                phoneBrand.Title = worksheet.Name;

                foreach (IXLColumn column in worksheet.ColumnsUsed().Skip(1))
                {
                    PhoneModel phoneModel = new PhoneModel();
                    phoneModel.Title = column.Cell(1).Value.ToString();

                    foreach (IXLRow row in worksheet.RowsUsed().Skip(1))
                    {
                        try
                        {
                            PricePosition pricePosition = new PricePosition();
                            pricePosition.Problem = row.Cell(1).Value.ToString();
                            pricePosition.Price = row.Cell(column.ColumnNumber()).Value.ToString();
                            phoneModel.PricePositions.Add(pricePosition);

                        }
                        catch (Exception e)
                        {
                            //logging
                            viewModel.ErrorsTotal++;
                        }
                    }

                    phoneBrand.PhoneModels.Add(phoneModel);
                }
                viewModel.PhoneBrands.Add(phoneBrand);
            }
        }
        //например, здесь сохраняем все позиции из прайса в БД

        return View(viewModel);
    }
    return RedirectToAction("Index");
}

В этом методе мы парсим Excel-файл и манипулируем записями в объектно-ориентированной манере. Код в методе довольно простой. Более подробно он объясняется в видео-версии этой статьи. Здесь отмечу основные моменты:

  • в нескольких циклах foreach{ } мы пробегаемся по всем записям в файле, параллельно создавая объекты классов наших доменных моделей;
  • получается сформированная коллекция брендов телефонов, в каждом из которых содержится коллекция конкретных моделей, в каждой из которых содержится коллекция позиций прайса с ценами на ремонт;
  • также создается ViewModel, где мы считаем количество ошибок при импорте и в которую вкладываем заполненную коллекцию брендов;
  • в итоге мы можем либо сохранить полученные объекты в базу данных, либо отправить ViewModel в представление.
В качестве интерактива Вы можете в соответствующем представлении создать HTML-таблицу с прайсом и отправить ее в браузер пользователя.

Также возможна другая ситуация, когда у нас нет исходного Excel-файла, вместо этого веб-приложение должно сформировать его динамически, и пользователь сайта сможет его скачать. Например, тот же список брендов и моделей телефонов.

Создадим соответствующее действие в контроллере:

public ActionResult Export()
{
    List<PhoneBrand> phoneBrands = new List<PhoneBrand>();
    phoneBrands.Add(new PhoneBrand()
    {
        Title = "Apple",
        PhoneModels = new List<PhoneModel>()
    {
        new PhoneModel() { Title = "iPhone 7"},
        new PhoneModel() { Title = "iPhone 7 Plus"}
    }});
    phoneBrands.Add(new PhoneBrand()
    {
        Title = "Samsung",
        PhoneModels = new List<PhoneModel>()
    {
        new PhoneModel() { Title = "A3"},
        new PhoneModel() { Title = "A3 2016"},
        new PhoneModel() { Title = "A3 2017"}
    }});

    using (XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled))
    {
        var worksheet = workbook.Worksheets.Add("Brands");

        worksheet.Cell("A1").Value = "Бренд";
        worksheet.Cell("B1").Value = "Модели";
        worksheet.Row(1).Style.Font.Bold = true;

        //нумерация строк/столбцов начинается с индекса 1 (не 0)
        for (int i = 0; i < phoneBrands.Count; i++)
        {
            worksheet.Cell(i + 2, 1).Value = phoneBrands[i].Title;
            worksheet.Cell(i + 2, 2).Value = string.Join(", ", phoneBrands[i].PhoneModels.Select(x => x.Title));
        }

        using (var stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            stream.Flush();

            return new FileContentResult(stream.ToArray(),
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                FileDownloadName = $"brands_{DateTime.UtcNow.ToShortDateString()}.xlsx"
            };
        }
    }
}

Отмечу ключевые моменты из листинга выше:

  • в нашем простом примере список брендов и моделей мы жестко закодировали, но его также можно получить и из БД;
  • к ячейкам на рабочем листе можно обращаться как по литеральному значению, так и по индексу. Нумерация строк/столбцов начинается с индекса 1 (не 0);
  • в качестве MIME-type для файлов Excel следует указывать application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;

Вывод: В этой статье мы рассмотрели основные возможности библиотеки ClosedXML. Ее функционала вполне хватает для обработки большинства сценариев, когда данные представлены в формате MS Excel файлов.

Если Вам понравился данный материал, Вы можете поделиться им в социальных сетях. Спасибо!

Видеокурсы

Вернуться наверх
наверх