Process .xls file directly with Ruby. It's a replacement of LanDa.rb

LanDa_New.rb

#!/usr/bin/env ruby

require 'csv'
require 'roo'
require 'roo-xls'

Wechat = "微信"
Alipay = "支付宝"
QQpay = "qq钱包"

# 店铺
class Merchant
    attr_accessor :wechat_pay_less_than_5_count # 微信支付金额少于5的笔数
    attr_accessor :merchant_name # 店铺名称
    attr_accessor :wechat
    attr_accessor :alipay
    attr_accessor :qqpay 

    def initialize(name)
        @wechat_pay_less_than_5_count = 0
        @merchant_name = name

        @wechat = Payment.new(Wechat)
        @alipay = Payment.new(Alipay)
        @qqpay = Payment.new(QQpay)
    end
end 

# 支付方式
class Payment
    attr_accessor :type #微信，支付宝2.0，qq钱包
    attr_accessor :count # 笔数
    attr_accessor :settlement_amount # 结算金额
    attr_accessor :service_charge # 手续费

    def initialize(type)
        @type = type

        @count = 0
        @settlement_amount = 0
        @service_charge = 0
    end
end

# 最终结果
# {
#    "大丰精品超市" => { Merchant } => [Payment]
# }

Result = {}

# 列号从 0 开始，因此交易日期是第 0 列
# 第 3 列：支付方式
# 第 23 列：门店名称
# 第 18 列：手续费
# 第 20 列：结算金额

# 第 12 列：交易金额，如果支付方式是微信，则用它统计金额小于5的笔数

#################

XLSFile = Roo::Spreadsheet.open(ARGV[0], extension: :xls)
DetailSheet = XLSFile.sheet(1)

# Roo use 1 to start with row and column
FirstRow = 7
LastRow = DetailSheet.last_row - 1

for i in FirstRow..LastRow
    row = DetailSheet.row(i)
    
    merchant_name = row[23]
    merchat = nil
    if Result[merchant_name] == nil
        merchant = Merchant.new(merchant_name)
        Result[merchant_name] = merchant
    else
        merchant = Result[merchant_name]
    end

    payment_type = row[3]
    if payment_type.start_with?(Wechat)
        merchant.wechat.count += 1
        merchant.wechat.settlement_amount += row[20].to_f
        merchant.wechat.service_charge += row[18].to_f
        
        if row[12].to_f < 5.0
            merchant.wechat_pay_less_than_5_count += 1
        end
    elsif payment_type.start_with?(Alipay)
        merchant.alipay.count += 1
        merchant.alipay.settlement_amount += row[20].to_f
        merchant.alipay.service_charge += row[18].to_f
    else
        merchant.qqpay.count += 1
        merchant.qqpay.settlement_amount += row[20].to_f
        merchant.qqpay.service_charge += row[18].to_f
    end
end

ResultFileName = ARGV[0].split(".")[0..-2].join(".") + ".csv"
CSV.open(ResultFileName, "wb") do |csv|
    csv << ["", "", "微信支付", "", "", "支付宝支付", "", "", "QQ钱包", "", ""]
    csv << ["店铺名称", "交易笔数", "结算金额", "手续费", "交易笔数", "结算金额", "手续费", "交易笔数", "结算金额", "手续费", "交易金额小于5"]
     
    Result.each { |merchant_name, merchant|
        csv << [merchant.merchant_name, merchant.wechat.count.to_s, ("%0.2f" % merchant.wechat.settlement_amount).to_s, ("%0.2f" % merchant.wechat.service_charge).to_s,
                                        merchant.alipay.count.to_s, ("%0.2f" % merchant.alipay.settlement_amount).to_s, ("%0.2f" % merchant.alipay.service_charge).to_s,
                                        merchant.qqpay.count.to_s, ("%0.2f" % merchant.qqpay.settlement_amount).to_s, ("%0.2f" % merchant.qqpay.service_charge).to_s,
                merchant.wechat_pay_less_than_5_count.to_s]
    }
end

Before executing, you should install gems via sudo gem install roo roo-xls.