Skip to content

Instantly share code, notes, and snippets.

@h8nor

h8nor/wildberries-json4excel.odc

Created June 14, 2023 20:04
Show Gist options
  • Save h8nor/d012242bc8499602d5ab43efb9960daf to your computer and use it in GitHub Desktop.
Save h8nor/d012242bc8499602d5ab43efb9960daf to your computer and use it in GitHub Desktop.
Download ZIP
Raw
wildberries-json4excel.odc
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Database>
<meta name=SourceType content=OLEDB>
<title>https://www.planetaexcel.ru/techniques/13/16185/</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Description>Соединение с запросом &quot;product&quot; в книге.</o:Description>
<o:Name>wildberries-ru.catalog.product</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:PowerQueryConnection odc:Type="OLEDB">
<odc:ConnectionString>Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=product;Extended Properties=&quot;&quot;</odc:ConnectionString>
<odc:CommandType>SQL</odc:CommandType>
<odc:CommandText>SELECT * FROM [product]</odc:CommandText>
</odc:PowerQueryConnection>
<odc:PowerQueryMashupData>&lt;Mashup xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot; xmlns=&quot;http://schemas.microsoft.com/DataMashup&quot;&gt;&lt;Client&gt;EXCEL&lt;/Client&gt;&lt;Version&gt;2.94.6229.0001&lt;/Version&gt;&lt;MinVersion&gt;2.21.0.0&lt;/MinVersion&gt;&lt;Culture&gt;ru-RU&lt;/Culture&gt;&lt;SafeCombine&gt;true&lt;/SafeCombine&gt;&lt;Items&gt;&lt;Query Name=&quot;pageID&quot;&gt;&lt;Description&gt;pages&lt;/Description&gt;&lt;Formula&gt;&lt;![CDATA[1 meta [IsParameterQuery=true, Type=&quot;Number&quot;, IsParameterQueryRequired=true]]]&gt;&lt;/Formula&gt;&lt;IsParameterQuery xsi:nil=&quot;true&quot; /&gt;&lt;IsDirectQuery xsi:nil=&quot;true&quot; /&gt;&lt;/Query&gt;&lt;Query Name=&quot;page_catalog&quot;&gt;&lt;Formula&gt;&lt;![CDATA[let&#13;&#10; // Читать данные https://www.wildberries.ru/catalog/knigi/hudozhestvennaya-literatura/detektivy?page={pageID}&#13;&#10; #&quot;Источник&quot; = Json.Document(&#13;&#10; Web.Contents(&#13;&#10; &quot;https://catalog.wb.ru/catalog/books_fiction/catalog?appType=1&amp;cat=9165&amp;curr=rub&amp;dest=-1257786&amp;regions=80,38,4,64,83,33,68,70,69,30,86,75,40,1,66,110,22,31,48,71,114&amp;sort=popular&amp;spp=0&amp;page=&quot;&#13;&#10; &amp; Number.ToText(pageID)&#13;&#10; )&#13;&#10; ),&#13;&#10; #&quot;Список&quot; = #&quot;Источник&quot;[data][products]&#13;&#10;in&#13;&#10; #&quot;Список&quot;]]&gt;&lt;/Formula&gt;&lt;IsParameterQuery xsi:nil=&quot;true&quot; /&gt;&lt;IsDirectQuery xsi:nil=&quot;true&quot; /&gt;&lt;/Query&gt;&lt;Query Name=&quot;productID&quot;&gt;&lt;Description&gt;products&lt;/Description&gt;&lt;Formula&gt;&lt;![CDATA[1 meta [IsParameterQuery=true, Type=&quot;Number&quot;, IsParameterQueryRequired=true]]]&gt;&lt;/Formula&gt;&lt;IsParameterQuery xsi:nil=&quot;true&quot; /&gt;&lt;IsDirectQuery xsi:nil=&quot;true&quot; /&gt;&lt;/Query&gt;&lt;Query Name=&quot;product&quot;&gt;&lt;Formula&gt;&lt;![CDATA[let&#13;&#10; #&quot;Список преобразован в таблицу&quot; = Record.ToTable(page_catalog{productID}),&#13;&#10; #&quot;Таблица транспонирована&quot; = Table.Transpose(Table.SelectRows(#&quot;Список преобразован в таблицу&quot;, &#13;&#10; each (&#13;&#10; [Name] = &quot;__sort&quot; or [Name] = &quot;brand&quot; or [Name] = &quot;name&quot; or [Name] = &quot;sale&quot; or [Name] = &quot;priceU&quot; or [Name] = &quot;salePriceU&quot;&#13;&#10; )&#13;&#10; )),&#13;&#10; #&quot;Первая строка как заголовок&quot; = Table.TransformColumnTypes(&#13;&#10; Table.PromoteHeaders(&#13;&#10; #&quot;Таблица транспонирована&quot;, [PromoteAllScalars=true]&#13;&#10; ),&#13;&#10; {{&quot;name&quot;, type text}, {&quot;brand&quot;, type text}}&#13;&#10; ),&#13;&#10; &#13;&#10; #&quot;Колонка дубль цена1&quot; = Table.RemoveColumns(&#13;&#10; Table.AddColumn(&#13;&#10; #&quot;Первая строка как заголовок&quot;, &quot;priceU1&quot;, each Currency.From([salePriceU]/100)&#13;&#10; ),&#13;&#10; {&quot;salePriceU&quot;}&#13;&#10; ),&#13;&#10; #&quot;Колонка дубль цена2&quot; = Table.RemoveColumns(&#13;&#10; Table.AddColumn(&#13;&#10; #&quot;Колонка дубль цена1&quot;, &quot;priceU2&quot;, each Currency.From([priceU]/100)&#13;&#10; ),&#13;&#10; {&quot;priceU&quot;}&#13;&#10; ),&#13;&#10; &#13;&#10; #&quot;Измененный тип дублей&quot; = Table.TransformColumnTypes(&#13;&#10; #&quot;Колонка дубль цена2&quot;, {{&quot;priceU1&quot;, Currency.Type}, {&quot;priceU2&quot;, Currency.Type}}&#13;&#10; )&#13;&#10;in&#13;&#10; #&quot;Измененный тип дублей&quot;]]&gt;&lt;/Formula&gt;&lt;IsParameterQuery xsi:nil=&quot;true&quot; /&gt;&lt;IsDirectQuery xsi:nil=&quot;true&quot; /&gt;&lt;/Query&gt;&lt;/Items&gt;&lt;/Mashup&gt;</odc:PowerQueryMashupData>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
</head>
<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
&nbsp;
</td>
<td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>
&nbsp;
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>
&nbsp;
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>
<script language='javascript'>
function init() {
var sName, sDescription;
var i, j;
try {
sName = unescape(location.href)
i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }
i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }
document.title = sName;
document.getElementById("tdName").innerText = sName;
sDescription = document.getElementById("docprops").innerHTML;
i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }
if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);
if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {
}
}
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment