Perl AliExpress 产品信息、SKU、销量信息提取,导出EXCEL

There's more than one way to do it!
https://metacpan.org http://perlmonks.org
头像
523066680
Administrator
Administrator
帖子: 478
注册时间: 2016年07月19日 12:14
拥有现金: 锁定
储蓄: 锁定
Has thanked: 52 times
Been thanked: 92 times
联系:

Perl AliExpress 产品信息、SKU、销量信息提取,导出EXCEL

帖子 #1 523066680 » 2019年08月29日 06:56

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

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

回到 “Perl”

在线用户

用户浏览此论坛: 没有注册用户 和 4 访客