Описание
В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
По умолчанию при помощи Power Query вы не можете зацепиться за формат ячеек. Вам доступны только данные в этих ячейках, но информации об отступах, цвете, шрифте и т. д. у вас нет.
В этом уроке нам нужно обработать файл, в котором кроме как за формат ячеек больше не за что зацепиться.
Решение
Что такое файл Excel? Файл Excel — это zip-архив, внутри которого находится много XML файлов. XML-разметка содержит в себе информацию не только о данных, но и о форматах. Получается, что если мы откроем XML-файл, соответствующий нужному нам листу, то сможем получить данные о форматах.
Как же извлечь XML из xlsx при помощи Power Query, если Power Query не умеет читать архивы? На самом деле в Power Query есть множество функций, которые нужны для чтения архивов. Чтобы грамотно пользоваться этими функциями нужно иметь знания об архивах. У меня таких знаний нет, что мне мне мешает воспользоваться чужими знаниями? Кен Р. написал функцию для чтения zip-архивов. Этой функцией мы и воспользуемся.
Мы подключимся к Excel-файлу 2 раза. Прочитаем файл обычным способом и прочитаем его функцией Кена Р., чтобы получить форматы. Получившиеся 2 таблицы нужно будет объединить по горизонтали.
Примененные функции
- File.Contents
- BinaryFormat.Record
- BinaryFormat.Binary
- BinaryFormat.ByteOrder
- BinaryFormat.UnsignedInteger32
- ByteOrder.LittleEndian
- BinaryFormat.Text
- Binary.Decompress
- Table.FromRecords
- Xml.Tables
- Table.SelectColumns
- Table.ExpandTableColumn
- Table.SelectRows
- Text.Contains
- Table.AddIndexColumn
- Table.NestedJoin
- JoinKind.LeftOuter
- Table.FillDown
- Table.PromoteHeaders
- Table.RenameColumns
Код
Получаем первую таблицу с данными о свойствах ячеек:
let
Source = File.Contents(path),
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],
import_xml = Xml.Tables(Content, null, 1251),
open_sheetdata = import_xml{[Name = "sheetData"]}[Table],
open_table = open_sheetdata{0}[Table],
cols_select = Table.SelectColumns(open_table, {"c"}),
col_expand = Table.ExpandTableColumn(
cols_select,
"c",
{"Attribute:r", "Attribute:s", "v", "Attribute:t"},
{"Attribute:r", "Attribute:s", "v", "Attribute:t"}
),
rows_select = Table.SelectRows(
col_expand,
each Text.Contains([#"Attribute:r"], "A")
),
table_add_index = Table.AddIndexColumn(rows_select, "Индекс", 0, 1)
in
table_add_indexПолучаем вторую таблицу и объединяем ее с первой:
let
source = Excel.Workbook(File.Contents(path), null, true),
get_sheet = source{[Item = "Лист1", Kind = "Sheet"]}[Data],
table_index_col = Table.AddIndexColumn(get_sheet, "Индекс", 0, 1),
tables_join = Table.NestedJoin(
table_index_col,
{"Индекс"},
Числа,
{"Индекс"},
"Числа",
JoinKind.LeftOuter
),
col_expand_nums = Table.ExpandTableColumn(
tables_join,
"Числа",
{"Attribute:s"},
{"Attribute:s"}
),
table_add_col_1 = Table.AddColumn(
col_expand_nums,
"Пользовательская",
each if [#"Attribute:s"] = "8" then [Column1] else null
),
table_add_col_2 = Table.AddColumn(
table_add_col_1,
"Пользовательская.1",
each if [#"Attribute:s"] = "11" then "Поставщик 1" else null
),
table_add_col_3 = Table.AddColumn(
table_add_col_2,
"Пользовательская.2",
each if [#"Attribute:s"] = "12" then "Поставщик 2" else null
),
col_fill_down = Table.FillDown(table_add_col_3, {"Пользовательская"}),
rows_select_1 = Table.SelectRows(
col_fill_down,
each ([Column2] <> null)
),
table_promote_headers = Table.PromoteHeaders(
rows_select_1,
[PromoteAllScalars = true]
),
table_rename_cols = Table.RenameColumns(
table_promote_headers,
{
{"Column7", "Регион"},
{"Column8", "Поставщик 1"},
{"Column9", "Поставщик 2"}
}
),
rows_select_2 = Table.SelectRows(
table_rename_cols,
each ([Группа аналитического учета] <> "Итого")
),
cols_select = Table.SelectColumns(
rows_select_2,
{
"Регион",
"Поставщик 1",
"Поставщик 2",
"Выручка",
"Валовая прибыль",
"Рентабельность, %"
}
)
in
cols_select