Excel/PowerQuery/VBA, Анализ данных

Автоматизация процесса разделения данных

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

Сегодня мы предлагаем рассмотреть один из способов разделения данных в ячейке файла формата MS Excel с помощью программы, написанной на языке программирования C#.

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

Т.к. визуализация данных происходит автоматически, то и разделение данных по сотрудникам должно происходить в автоматическом режиме без участия человека-оператора. Для этого нами на языке программирования C# была создана программа автоматически разделяющая данные. Причем данные могут быть не только ФИО сотрудника, а любыми (разделенные определенным знаком-разделителем).

Расскажем о программе подробнее.

Для удобства настройки программы мы выделили основные параметры в настроечный файл формата xml.

<Settings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Programm">
  	<Delimiter>,</Delimiter>                   Знак-разделитель
  	<InputFileName>C:\IputFile.xlsx</InputFileName> Полный путь к входному файлу
       <NameEditColumn>ФИО</NameEditColumn>       Столбец, который необходимо обработать
       <NumberFirstDataRow>2</NumberFirstDataRow> Номер первой строки с данными
       <NumberHeaderRow>0</NumberHeaderRow>       Номер строки с заголовком таблицы
       <OutputNameFile>C:\OutputFile.xlsx</OutputNameFile> Полный путь к измененному файлу
  </Settings>

Для того, чтобы иметь возможность чтения файла формата MS Excel подключаем  библиотеки:

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Разберемся, что происходит:

— программа, после считывания из файла настроек, считывает Excel-файл (указанный в параметре InputFileName) в память при помощи объекта DataTable;

— зная (из параметров) номер первой строки с данными и номер столбца в котором находятся данные, которые необходимо трансформировать, запускается цикл, который, перебирая все строки с данными, ищет ячейку, в которой будут содержаться знаки-разделители;

— как только такая ячейка будет найдена, программа разделит обнаруженные данные по знаку-разделителю и поместит их в массив (без знака разделителя);

— строка данных в которой были найдены данные со знаком-разделителем будет клонирована (столько раз, сколько будет элементов в созданном массиве с разделенными данными), а данные со знаком-разделителем будут заменены соответствующими элементами из массива;

— следующим шагом будет замена исходной строки в объекте DataTable полученными клонами;

— после того, как последняя строка таблицы будет обработана вышеописанным алгоритмом программа экспортирует полученный набор данных из памяти в файл формата MS Excel (указанный в параметре OutputNameFile).

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

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