На сегодняшний день существует достаточно много готовых решений (библиотек) для взаимодействия с файлами Excel, чтобы не работать напрямую с системными GridView и DataTable. Не будем изобретать велосипед и рассмотрим одну из популярных и удобных библиотек, которой я пользуюсь чаще других.
Данная библиотека называется ClosedXML. По этой ссылке находится официальный адрес проекта на GitHub. Библиотека позволяет легко манипулировать файлами MS Excel в удобной объектно-ориентированной манере. Она может быть использована на любом .NET языке программирования (C#, VisualBasic.NET), а также в проектах не только типа WebApplication.
Представим ситуацию, что у нас есть под рукой вот такой Excel-файл с ценами на ремонт телефонов, в котором на листах расположены отдельные бренды, колонки на листе представляют конкретные модели для этого бренда, а строки представляют конкретные неисправности и сколько будет стоить ремонт для той или иной модели.
Давайте создадим веб-приложение, которое сможет распарсить этот файл так, чтобы мы могли работать с записями в объектно-ориентированной манере, например, сохранять/обновлять бренды, модели и позиции в прайсе в базу данных. Или, например, представлять этот прайс в виде обычной html-таблицы в браузере пользователя.
Создаем в Visual Studio проект типа MVC, открываем Nuget Manager и скачиваем нужный нам пакет.
Создадим все доменные классы-модели, которые описывают предметную область.
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 в представление.
Также возможна другая ситуация, когда у нас нет исходного 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 файлов.
Видеокурс C#. От простого к сложному
