Excel/PowerQuery/VBA, Parsing / Сбор информации, Анализ данных

Разрабатываем web-приложение ASP.NET MVC для парсинга Excel файлов

Время прочтения: 5 мин.

Excel файлы повсеместно применяются для хранения данных в виде таблиц, объем данных и хранение однотипных данных в разных файлах excel усложняют обработку их при помощи персонального компьютера.

Базы данных (БД) используются для хранения различной информации. Для извлечения информации из них используется SQL (structured query language — язык структурированных запросов). Для редактирования БД используют системы управления базами данных, через которые возможно манипулировать данными. Для рядового пользователя разбираться в синтаксисе SQL является трудозатратным процессом. В практике работы с БД встречаются однотипные запросы с изменением даты или количественных измерений.

Оптимальным вариантом для решения задачи обработки данных Excel файлов и предоставления данных по определенным критериям является создание приложения, которое будет обрабатывать заданные критерии и выдавать необходимые данные на стороне сервера. Для упрощения взаимодействия пользователя с создаваемым приложением необходимо обеспечить взаимодействие пользователя с приложением при помощи удобного интерфейса и легкого доступа.

В качестве интерфейса самым простым и понятным интерфейсом является web-интерфейс. Одним из распространённых фреймворков является “ADO.NET MVC”. Пользователь через браузер отправляет запрос, если необходима информация из БД, модель передает их в представление:

Для обработки excel файлов необходимо установить ExcelDataReader и ExcelDataReader.DataSet.

Excel файл выглядит следующим образом:

Модель

«M» в MVC обозначает модель. Модель для данных из Excel в простом виде будет записана следующим образом:

public class Worker
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public string Passport { get; set; }
        public string Address { get; set; }
        public string Birthday { get; set; }
        public string Sex { get; set; }
        public string Post { get; set; }
        public int Аmount_of_children { get; set; }
}

Контроллер

Буква «C» в MVC означает контроллер. Контроллер собирает данные и передает их в представление. Создадим контроллер для передачи файла:

public class WorkerController : Controller
        {
            private readonly WorkerDbContext dbContext;

            public WorkerController()
            {
                dbContext = new WorkerDbContext();
            }

            // GET: Workers  
            public ActionResult Index()
            {
                return View();
            }

            [HttpPost]
            public async Task<ActionResult> ImportFile()
            {
                return View("Index");
            }
        }

Добавим в контроллер метод для обработки файла в данном примере будем использовать csv формат:

        private List<Worker> parsingFile(Stream stream)
        {
            var wrkList = new List<Worker>();
            try
            {
                using (var exRead = ExcelReaderFactory.CreateCsvReader(stream))
                {
                    var dSet = exRead.AsDataSet(new ExcelDataSetConfiguration
                    {
                        ConfigureDataTable = _ => new ExcelDataTableConfiguration
                        {
                            UseHeaderRow = true   
                        }
                    });
                        var table = dSet.Tables[0];
                        foreach (DataRow dataRow in table.Rows)
                        {
                            if (dataRow.ItemArray.All(x => string.IsNullOrEmpty(x?.ToString()))) continue;
                            wrkList.Add(new Worker()
                            {
                                Id = Convert.ToInt32(dataRow["Табельный номер"].ToString()),
                                FullName = dataRow["ФИО"].ToString(),
                                Passport = dataRow["Паспорт"].ToString(),
                                Address = dataRow["Адрес"].ToString(),
                                Birthday = dataRow["Дата рождения"].ToString(),
                                Sex = dataRow["Пол"].ToString(),
                                Аmount_of_children = null,
                            });
                        }                  
                }
            }
            catch (Exception)
            {
                throw;
            }
            return wrkList;
        }

Представление

Буква «V» в MVC означает представление. Представление может быть самостоятельным, или обеспечивать различные способы представления данных, которые получены из модели. Представление может быть макетом и заполняться данными. Представления могут быть различными и контроллер выбирает, какой вид подходит для текущей ситуации.

Напишем представление для загрузки файла:

@{
    ViewBag.Title = "LoadFile";
}

<h2>LoadFile</h2>

<div class="row">
    <div class="col-sm-12" style="padding-bottom:15px">
        <div class="col-sm-2">
            <span>Select File  :</span>
        </div>
        <div class="col-sm-3">
            <input class="form-control" type="file" name="importFile" id="importFile" />
        </div>
        <div class="col-sm-3">
            <input class="btn btn-primary" id="btnUpload" type="button" value="Загрузить" />
        </div>
    </div>
</div>

В результате представления будут выглядеть следующим образом.

Форма загрузки файла:

Все данные вымышленные.

Просмотр работников:

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

В результате выбранных выше критериев мы получили уменьшенный объем информации:

Также в результате работы были сгруппированы сотрудники по отделам, что также облегчает просмотр данных объединённых одним критерием:

В результате создания web-приложения были выделены следующие преимущества:

  • возможность обрабатывать большие файлы, не используя вычислительные ресурсы персонального компьютера
  • простота создания подключения к БД
  • использование группировки данных и вывод данных по заданным критериям из excel файлов по определенным столбцам

В дальнейшем данную технологию можно применять для получения файлов других форматов и последующей обработки данных при помощи машинного обучения и др.

Советуем почитать