Файлы к уроку: Файлы доступны только спонсорам канала. Оформите спонсорскую подписку, если хотите получить максимум от обучения. Стать спонсором канала: https://www.youtube.com/channel/UCwAru3vDmH-IdYtwK7JWN4w/join
В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
По умолчанию при помощи Power Query вы не можете зацепиться за формат ячеек. Вам доступны только данные в этих ячейках, но информации об отступах, цвете, шрифте и т. д. у вас нет.
В этом уроке нам нужно обработать файл, в котором кроме как за формат ячеек больше не за что зацепиться.
Что такое файл Excel? Файл Excel — это zip-архив, внутри которого находится много XML файлов. XML-разметка содержит в себе информацию не только о данных, но и о форматах. Получается, что если мы откроем XML-файл, соответствующий нужному нам листу, то сможем получить данные о форматах.
Как же извлечь XML из xlsx при помощи Power Query, если Power Query не умеет читать архивы? На самом деле в Power Query есть множество функций, которые нужны для чтения архивов. Чтобы грамотно пользоваться этими функциями нужно иметь знания об архивах. У меня таких знаний нет, что мне мне мешает воспользоваться чужими знаниями? Автор блока excelandpowerbi.com написал функцию для чтения zip-архивов. Этой функцией мы и воспользуемся.
Код для распаковки Excel-файла, выбора из него нужного XML для этой задачи выглядит так:
let
Source = File.Contents(Путь),
DecompressFiles = (ZIPFile, Position, FileToExtract, DataSoFar) =>
let
MyBinaryFormat = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position),
MiscHeader=BinaryFormat.Binary(18),
FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
UnCompressedFileSize=BinaryFormat.Binary(4),
FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
TheRest=BinaryFormat.Binary()]) otherwise null,
MyCompressedFileSize = try MyBinaryFormat(ZIPFile)[FileSize]+1 otherwise null,
MyFileNameLen = try MyBinaryFormat(ZIPFile)[FileNameLen] otherwise null,
MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,
MyBinaryFormat2 = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position), Header=BinaryFormat.Binary(30), Filename=BinaryFormat.Text(MyFileNameLen), Extras=BinaryFormat.Binary(MyExtrasLen), Data=BinaryFormat.Binary(MyCompressedFileSize), TheRest=BinaryFormat.Binary()]) otherwise null,
MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null,
GetDataToDecompress = try MyBinaryFormat2(ZIPFile)[Data] otherwise null,
DecompressData = try Binary.Decompress(GetDataToDecompress, Compression.Deflate) otherwise null,
NewPosition = try Position + 30 + MyFileNameLen + MyExtrasLen + MyCompressedFileSize - 1 otherwise null,
AsATable = Table.FromRecords({[Filename = MyFileName, Content=DecompressData]}),
#"Appended Query" = if DecompressData = null then DataSoFar else if (MyFileName = FileToExtract) then AsATable else
if (FileToExtract = "") and Position <> 0 then Table.Combine({DataSoFar, AsATable})
else AsATable
in
if (MyFileName = FileToExtract) or (#"Appended Query" = DataSoFar) then
#"Appended Query"
else
@DecompressFiles(ZIPFile, NewPosition, FileToExtract, #"Appended Query"),
MyData = DecompressFiles(Source, 0, "", null),
Content = MyData{[Filename = "xl/worksheets/sheet1.xml"]}[Content],
#"Импортированные данные XML" = Xml.Tables(Content,null,1251),
Table = #"Импортированные данные XML"{[Name = "sheetData"]}[Table],
Table1 = Table{0}[Table],
#"Другие удаленные столбцы" = Table.SelectColumns(Table1,{"c"}),
#"Развернутый элемент c" = Table.ExpandTableColumn(#"Другие удаленные столбцы", "c", {"Attribute:r", "Attribute:s", "v", "Attribute:t"}, {"Attribute:r", "Attribute:s", "v", "Attribute:t"}),
#"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент c", each Text.Contains([#"Attribute:r"], "A")),
#"Добавлен индекс" = Table.AddIndexColumn(#"Строки с примененным фильтром", "Индекс", 0, 1)
in
#"Добавлен индекс"Краткое описание этой функции:
В первом шаге мы подключились к Excel-файлу.
Второй шаг — это код пользовательской функции для распаковки файла.
В третьем шаге мы применяем функцию к файлу Excel.
В последующих шагах мы находим нужный нам XML, а в нем находим данные о нужных нам форматах.
