[新手上路]批处理新手入门导读[视频教程]批处理基础视频教程[视频教程]VBS基础视频教程[批处理精品]批处理版照片整理器
[批处理精品]纯批处理备份&还原驱动[批处理精品]CMD命令50条不能说的秘密[在线下载]第三方命令行工具[在线帮助]VBScript / JScript 在线参考
返回列表 发帖

[原创代码] 某电商平台所有产品信息、SKU、销量 在线提取 并导出EXCEL

本帖最后由 523066680 于 2019-7-23 11:13 编辑

过度定制,发出来别人也用不上……
(对应Aliexpress平台)
用途:线上数百个产品,底价、海外仓库存设置各不相同,在线编辑非常繁琐。
同时平台营销活动的折扣率也需要针对性设置。因此需要一份完整的产品数据表,包含每个产品的ID、主图、最新的SKU、销量信息。

Login.pm 模块需要自行实现,使用 Mojo::UserAgent 登录。
  1. =info
  2.     2019-07 平台新版本 在线SKU数据、销量数据整合 生成EXCEL表
  3.     523066680/vicyang
  4.     V2 预先收集所有数据,排序后导出EXCEL,含产品主图
  5. =cut
  6. use Encode;
  7. use Modern::Perl;
  8. use File::Slurp;
  9. use Mojo::UserAgent;
  10. use Mojo::JSON qw/encode_json decode_json/;
  11. use Data::Dump qw/dd/;
  12. use File::Basename;
  13. use Date::Format;
  14. use Spreadsheet::WriteExcel;
  15. use Imager;
  16. use FindBin;
  17. use lib "$FindBin::Bin/../lib";
  18. use Login;
  19. use SkuDetail;
  20. STDOUT->autoflush(1);
  21. mkdir "img" unless -e "img";
  22. my $ua = Mojo::UserAgent->new();
  23. Login::init($ua);
  24. our %sales = (
  25.     "22881"  => "nancy",
  26.     "22988"  => "alice",
  27.     "18586"  => "bob",
  28.     "22876"  => "lucy",
  29.     "nancy" => "22881",
  30.     "alice" => "22988",
  31.     "bob" => "18586",
  32.     "lucy" => "22876",
  33. );
  34. my $seller = "nancy";
  35. my $sid = $sales{$seller};
  36. my $url = "https://gsp-gw.aliexpress.com/openapi/param2/1/gateway.seller/api.product.manager.render.list";
  37. my $template = '{"filter":{"queryGroup":null,"queryCategory":null,"queryOwner":{"text":"seller_name","value":"seller_id"},"queryRegionalPricing":null,"queryStock":null,"queryShippingTemplate":null,"querySelectInput":{"key":1,"value":""}},"pagination":{"current":current_page,"pageSize":50},"table":{"sort":{}},"tab":"online_product"}';
  38. my %args = ( 'jsonBody' => undef );
  39. $template =~s/seller_name/$seller/;
  40. $template =~s/seller_id/$sid/;
  41. my $today = time2str("%Y-%m-%d", time());
  42. my $excel = "${today} ${seller}.xls";
  43. mkdir $today unless -e $today;
  44. my $pgcode = 1;
  45. my $total = 1;
  46. my $list = [];
  47. my $res;
  48. while ( $pgcode <= $total )
  49. {
  50.     say "Current Page: $pgcode";
  51.     $args{jsonBody} = $template;
  52.     $args{jsonBody} =~s/current_page/$pgcode/;
  53.     $res = $ua->post( $url, form => \%args )->result;
  54.     say "false" unless $res->is_success();
  55.     # 返回的 JSON 中的数据节点经过镶嵌封装,需要再次解码
  56.     my $data = decode_json( utf8($res->json->{data})  );
  57.     my $node = $data->{table}{dataSource};
  58.     get_list( $node, $list );
  59.     $total = $data->{pagination}{pageShowCount};
  60.     $pgcode++;
  61. }
  62. # 写入 Excel
  63. write_excel( $list, $excel );
  64. sub get_list
  65. {
  66.     my ($node, $ref) = @_;
  67.     say "Abstract Data from JSON ... ";
  68.     for my $e ( @$node )
  69.     {
  70.         printf "%s\n", $e->{productId};
  71.         my $info = {
  72.                 'id' => $e->{productId},
  73.                 'subject' => match( $e->{itemDesc}{desc}, "uiType", "link", "text" ),
  74.                 'img' => $e->{itemDesc}{img},
  75.                 'group' => $e->{group}{desc}[0]{text},
  76.             };
  77.         get_detail($info);
  78.         push @$ref, $info;
  79.     }
  80. }
  81. sub get_detail
  82. {
  83.     my ($ref) = @_;
  84.     my $html;
  85.     my $file = $today ."/". $ref->{id} .".html";
  86.     if (-e $file) {
  87.         $html = read_file( $file );
  88.     } else {
  89.         $html = SkuDetail::get_html( $ua, $ref->{id} );
  90.         write_file( $file, {binmode=>':raw'}, $html );
  91.     }
  92.     my $data = SkuDetail::extract_data( $html );
  93.     my $orders = SkuDetail::get_orders( $data );
  94.     my $wish = SkuDetail::get_wish_count( $data );
  95.     my ($sku, $slen) = SkuDetail::get_sku( $data );
  96.     $ref->{'orders'} = $orders;
  97.     $ref->{'wish'} = $wish;
  98.     $ref->{'sku'} = $sku;
  99.     # sku = { 'country' => [[color, qty, price], [...] ] }
  100. }
  101. sub init_sheet
  102. {
  103.     my ($book, $group, $fmt) = @_;
  104.     my $sheet = $book->add_worksheet($group);
  105.     $sheet->set_column(0, 0, 4);
  106.     $sheet->set_column(1, 1, 22);
  107.     $sheet->set_column(2, 2, 22);
  108.     $sheet->set_column(3, 3, 50, undef, 1);  #url
  109.     $sheet->set_column(4, 4, 18);    # image
  110.     $sheet->set_column(5, 5, 7.5);  #orders
  111.     $sheet->set_column(6, 6, 22);  #colors
  112.     $sheet->set_column(9, 9, 22);  #colors
  113.     $sheet->set_column(12, 12, 22);  #colors
  114.     grep { $sheet->set_column($_, $_, 8); } (7,8,10,11,13,14);  # SKU
  115.     #grep { $sheet->set_row($_, 30) } ( 1 .. 200);
  116.     $sheet->write( 0, 1, "Group", $fmt->{center});
  117.     $sheet->write( 0, 2, "ProductID", $fmt->{center});
  118.     $sheet->write( 0, 3, "Pictures", $fmt->{center});
  119.     $sheet->write( 0, 5, "Orders", $fmt->{center});
  120.     $sheet->write( 0, 6, "CN", $fmt->{center});
  121.     $sheet->write( 0, 9, "ES", $fmt->{center});
  122.     $sheet->write( 0, 12, "RU", $fmt->{center});
  123.     #$sheet->autofilter("A1:D200");
  124.     #$sheet->autofilter(0, 0, 0, 8);
  125.     return $sheet;
  126. }
  127. sub write_excel
  128. {
  129.     our (%group, @groups, %groups_key);
  130.     my ($list, $excel) = @_;
  131.     my $book = Spreadsheet::WriteExcel->new($excel);
  132.     my $sheet = {};
  133.    
  134.     say "Export to Excel ...";
  135.     my %font = ( font  => 'Arial', size  => 12 );
  136.     my %bold = ( font  => 'Arial', size  => 12, bold => 1 );
  137.     my %fmt;
  138.     $fmt{merge} = $book->add_format( %font, valign=>'vcenter', align=>'center' );
  139.     $fmt{url} = $book->add_format( %font, valign => 'vcenter', align => 'left', underline => 1, color => "blue");
  140.     $fmt{mg_url} = $book->add_format( %font, valign => 'vcenter', align => 'center', underline => 1, color => "blue");
  141.     $fmt{left} = $book->add_format( %font, valign => 'vcenter' );
  142.     $fmt{center} = $book->add_format( %font, valign => 'vcenter', align => 'center' );
  143.     $fmt{left}->set_text_wrap();
  144.     $fmt{sku} = $book->add_format( font=>'Arial', size=>12, valign=>'vcenter', align=>'left', text_wrap=>1);
  145.     #format
  146.     my $row = {};
  147.     my $iter = {};
  148.     for my $g ( @groups ) {
  149.         $sheet->{$g} = init_sheet( $book, $g, \%fmt );
  150.         $row->{$g} = 1;
  151.         $iter->{$g} = 1;
  152.     }
  153.    
  154.     my $image;
  155.     my $link;
  156.     my $shref;
  157.     my $row_add;
  158.     for my $e ( sort { $b->{orders} <=> $a->{orders} } @$list )
  159.     {
  160.         say $e->{id};
  161.         my $g = $e->{group};
  162.         next if (not exists $groups_key{$g});  # 只处理指定分组
  163.         $shref = $sheet->{ $g };
  164.         # 根据颜色数量判断
  165.         my $colors = scalar( @{$e->{sku}{CN}} );
  166.         my $height = int(100/$colors);
  167.         $height = 20 if ($height < 20);
  168.         my $ofst = 0;
  169.         for my $s ( sort { $a->[0] cmp $b->[0] } @{$e->{sku}{CN}} ) {
  170.             $shref->set_row($row->{$g} + $ofst, $height );
  171.             $ofst++;
  172.         }
  173.         $link = "https://aliexpress.com/item//". $e->{id} .".html";
  174.         if ( $colors > 1 ) {
  175.             $shref->merge_range( $row->{$g}, 0, $row->{$g}+$colors-1, 0, $iter->{$g}, $fmt{merge} );
  176.             $shref->merge_range( $row->{$g}, 1, $row->{$g}+$colors-1, 1, $g, $fmt{merge} );
  177.             $shref->merge_range( $row->{$g}, 2, $row->{$g}+$colors-1, 2, $e->{id}, $fmt{merge} );
  178.             $shref->merge_range( $row->{$g}, 3, $row->{$g}+$colors-1, 3, $link, $fmt{mg_url} );
  179.             $shref->merge_range( $row->{$g}, 4, $row->{$g}+$colors-1, 4, '', $fmt{merge} );
  180.             $shref->merge_range( $row->{$g}, 5, $row->{$g}+$colors-1, 5, $e->{orders}, $fmt{merge} );
  181.         } else {
  182.             $shref->write( $row->{$g}, 0, $iter->{$g}, $fmt{center});
  183.             $shref->write( $row->{$g}, 1, $g, $fmt{center});
  184.             $shref->write( $row->{$g}, 2, $e->{id}, $fmt{center});
  185.             $shref->write_url( $row->{$g}, 3, $link, $e->{id}, $fmt{url});
  186.             $shref->write( $row->{$g}, 5, $e->{orders}, $fmt{center});
  187.         }
  188.         #$shref->write_url( $row->{$g}, 2, $link, $e->{id}, $fmt{url});
  189.         $image = get_image( $e->{img}, 200 ); # file
  190.         $shref->insert_image($row->{$g}, 4, $image, 5, 1, 0.6, 0.6 );
  191.         #$shref->write( $row->{$g}, 4, $e->{subject}, $fmt{left});
  192.         my %pos = ( 'CN'=>6, 'ES'=>9, "RU"=>12 );
  193.         for my $ct ( keys %{$e->{sku}} )
  194.         {
  195.             $ofst = 0;
  196.             for my $s ( sort { $a->[0] cmp $b->[0] } @{$e->{sku}{$ct}} )
  197.             {
  198.                 $shref->write( $row->{$g} + $ofst, $pos{$ct}+0, $s->[0], $fmt{center});
  199.                 $shref->write( $row->{$g} + $ofst, $pos{$ct}+1, $s->[1], $fmt{center});
  200.                 $shref->write( $row->{$g} + $ofst, $pos{$ct}+2, $s->[2], $fmt{center});
  201.                 $ofst++;
  202.             }
  203.         }
  204.         $row->{$g} += $colors;
  205.         $iter->{$g} ++;
  206.     }
  207.     $book->close();
  208. }
  209. sub get_image
  210. {
  211.     my ($url, $pixel) = @_;
  212.     my $file = "./img/". basename($url);
  213.     my $res;
  214.     unless ( -e $file )
  215.     {
  216.         $res = $ua->get($url)->result;
  217.         say "get image false" unless ( $res->is_success() );
  218.         write_file( $file, {binmode=>'raw'}, $res->body );
  219.     }
  220.     my $read_image = Imager->new;
  221.     my $img = $read_image->read( file => $file );
  222.     my ($h, $w) = ( $img->getheight(), $img->getwidth() );
  223.     return $file if ( $w <= $pixel and $h <= $pixel );
  224.    
  225.     say $file;
  226.     my $scale = $h > $w ? "ypixels" : "xpixels";
  227.     my $modify = $img->scale( $scale => $pixel );
  228.     $modify->write( file => $file );
  229.     return $file;
  230. }
  231. sub match
  232. {
  233.     my ( $arr, $key, $value, $item ) = @_;
  234.     for my $e ( @$arr ) {
  235.         return $e->{$item} if ( exists $e->{$key} and $e->{$key} =~ /$value/ );
  236.     }
  237.     return "NULL";
  238. }
  239. sub gbk { encode('gbk', $_[0]); }
  240. sub utf8 { encode('utf8', $_[0]); }
  241. sub u2gbk { encode('gbk', decode('utf8', $_[0])); }
  242. BEGIN
  243. {
  244.     use Storable qw/retrieve/;
  245.     our %group = (
  246.         "509608" => "model1",
  247.         "509420" => "model2",
  248.         "515657" => "model3",
  249.         "515546" => "model4",
  250.         "508982" => "model5",
  251.         "509310" => "model6",
  252.         "510063" => "model7",
  253.     );
  254.     our @groups = (
  255.             "model1", "model2", "model3", "model4", "model5", "model6", "model7",
  256.         );
  257.     our %groups_key = ( map { $_ => 1 } @groups );
  258.     our $COUNTRY = retrieve("CountryName_EN2CN.perldb");
  259.     $COUNTRY->{'China'} = ['中国', 'CN'];
  260. }
复制代码

返回列表