Board logo

标题: [原创代码] 某电商平台所有产品信息、SKU、销量 在线提取 并导出EXCEL [打印本页]
作者: 523066680    时间: 2019-7-23 11:05     标题: 某电商平台所有产品信息、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. 

  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. }
复制代码




欢迎光临 批处理之家 (http://bbs.bathome.net/) Powered by Discuz! 7.2