欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

rails处理上传读取excell&生成excell

程序员文章站 2022-03-09 23:04:45
...
gem 'spreadsheet'
gem 'roo', require: false, github: 'whmall/roo'
gem 'roo-xls'
#文件格式要求为.xlsx格式。
另外需要编辑excell的时候建议使用gem 'write_xlsx'或gem 'axlsx'


上传导入excell
def create_import_product
	file = params[:chemical_tag][:file]
	#校验文件格式
	@message = {color:"#ff0000"}
	@import = { failed: []}
	unless File.extname(file.original_filename).upcase  == ".XLSX"
	    @message[:detail] = "文件格式要求为.xlsx格式。"
	else
	    book = Roo::Spreadsheet.open file
	    sheet = book.sheet 0
	    #创建询盘
	    result_book = Spreadsheet::Workbook.new
	    result_sheet = result_book.create_worksheet
	    sheet.each_with_index do |row, index|
	      result_row = result_sheet.row index
	      result_row = result_row.concat row
	      begin
	           if index > 0
	              @chemical_id = row[0]
	              if row[0].to_i != row[0] && row[2].to_s != ""
	                @chemical_id = Chemical.find_by_cas(row[2])&.id 
	                @import[:failed] << "#{row[2]} 是新产品" unless @chemical_id
	              end
	              @tags = row[1]
	              @tags = @tags.split(",")
	              @tags.each do |tag|
	                @tag = ChemicalTag.find_or_create_by(name:tag) 
	                ChemicalsTag.find_or_create_by(chemical_id:@chemical_id,chemical_tag_id:@tag.id)
	              end
	           end
	      rescue
	        @import[:failed] << row
	        next
	      end
	    end 
	    @message[:color] = "#00DD00"
	    @message[:detail] = "产品导入成功"
	end    
	render "import_product"   
end


导出excell:xls
	def export_product
		@report_name ="#{@tag.name_cn}目录产品"
		@columns = %w{产品id 分类英文名称 产品cas}

        @base_sql = "SELECT chemicals.id,chemical_tags.name,chemicals.cas 
			        FROM chemicals_tags as ca_ts 
			        left join `chemical_tags` on chemical_tags.id = ca_ts.chemical_tag_id 
			        left join chemicals on chemicals.id = ca_ts.chemical_id 
			        WHERE (chemical_tags.id in (#{@tag.children_tag_ids.join(',')}) ) ORDER BY chemical_tags.id"                                
        
        @children_products = ActiveRecord::Base.connection.execute(@base_sql)

        send_data Report::ExecuteReport.to_xlsx(@report_name,@columns,@children_products), type: 'text/xls', filename: "#{@tag.name_cn}目录产品.xls"                                
end

--------
def self.to_xlsx(name,columns,report_data)
      file = Spreadsheet::Workbook.new
 
      list = file.create_worksheet :name => name
      list.row(0).concat columns
   
      report_data.each_with_index { |report, i|
        list.row(i+1).concat report
      }

      xls_report = StringIO.new 
      file.write xls_report 
      xls_report.set_encoding('UTF-8')
      xls_report.string 
end



写法二
class CartItemQuotationExcel

  def initialize(hidden_pdf_cart_item_ids)
    @book = Spreadsheet::Workbook.new
    @cart_items = CartItem.where(id: hidden_pdf_cart_item_ids.split(","))
    pack
  end

  def pack
    Spreadsheet.client_encoding = 'UTF-8'
    format = Spreadsheet::Format.new weight: :bold
    sheet = @book.create_worksheet(name: 'Package Document')
    sheet.row(0).replace(['Cas', 'Name', 'Hazard Chemical', 'Quotation NO', 'Quantity&Purity', 'Stock&Dispatched', 'Price',
                          'Delivery', 'Payment Type', 'Logistic Type', 'State', 'Require COA', 'Note'])
    sheet.row(0).default_format = format
    sheet_row = 1
    @cart_items.each do |cart_item|
      shipping = 0
      cart_item.total_serve_charges.each do |charge|
        if charge.item.name == 'Shipping Fee'
          shipping = charge.subtotal
        end
      end
      if cart_item.good_type == 'Product'
        sheet.row(sheet_row).replace([cart_item.good&.chemical.cas,
                                     cart_item.good&.chemical.name&.to_s,
                                    (cart_item.good&.chemical.un_chemicals.present? ? "Yes" : "No"),
                                    '',cart_item.good&.package_message, '', "$" + cart_item&.bulk_price.to_s, "$" + shipping.to_s, "CIF/#{cart_item.user&.buyer&.payment_strategy&.name&.upcase.to_s}", '', '', '',''
        ])
      else
        sheet.row(sheet_row).replace([cart_item.good&.chemical.cas,
                                     cart_item.good&.chemical.name&.to_s,
            (cart_item.good&.chemical.un_chemicals.present? ? "Yes" : "No"),
            cart_item.good&.quotation&.quotation_no,
            cart_item.good&.package_message,
            QuotationItem.enum_i18n(:kind,cart_item.good&.kind) + "/ Dispatch:" + cart_item.good&.deliver_on.strftime('%b-%d-%Y'),
            "$" + cart_item&.bulk_price.to_s,
            "$" + shipping.to_s,
            (cart_item.good&.incoterms.blank? ? 'CIF': cart_item.good&.incoterms&.upcase ) + "/#{cart_item.user&.buyer&.payment_strategy&.name&.upcase.to_s}/" + (cart_item.good&.deposit_payment? ? cart_item.good&.advance_payment : ""),
            (cart_item.good&.logistic_type.blank? ? 'direct_shipping' : cart_item.good&.logistic_type).titleize + "/ Changing name:" + (cart_item.good&.accept_change_name ? "Yes" : "No"),
            cart_item.good&.quotation.state.titleize,
            (cart_item.good&.inquiry_item&.require_coa && cart_item.good&.certificates ? "YES" : "NO"),
            (cart_item.good&.quotation.comment.present? ? cart_item.good&.quotation&.comment : "")
        ])
      end


      sheet_row += 1
    end
  end

  def render
    spreadsheet = StringIO.new
    @book.write spreadsheet
    spreadsheet.string
  end
end


write_xlsx的方案
require 'write_xlsx'
class CommercialInvoiceExcel

  def initialize(record = nil)
    @io = StringIO.new
    @book = WriteXLSX.new(@io)
    @sheet = @book.add_worksheet
    @record = record.class == CommercialInvoiceRecord ? OpenStruct.new(record.attributes.dup) : OpenStruct.new(record)
    @index = 11
  end

  def default_text
    @sheet.set_column(1, 1, 10)
    @sheet.set_column(2, 5, 20)
    @sheet.insert_image('A1', image_path, 0, 0, 0.2, 0.2)
    @sheet.merge_range('A1:E1', @record.main, format_header)

    if @record.main.to_s == 'aa TECHNOLOGY USA INC'
      @sheet.merge_range('A2:E2', "ADD: Warehouse B, dsdasdas, \r  Jinshan District, Shanghai, China.", format_body)
      @sheet.merge_range('A3:E3', 'Website: www.aa.com    E-mail: sales@aa.com', format_body)
      @sheet.merge_range('A4:E4', 'TEL: +23423332222      FAX: +12334422555', format_body)
      @sheet.merge_range('A6:E6', 'INVOICE', format_header_center)
    elsif   @record.main.to_s == 'SHANGHAI aa TECHNOLOGY CO.,LTD'
      @sheet.merge_range('A2:E2', "ADD: Warehouse B, dsdasdas, \r  Jinshan District, Shanghai, China.", format_body)
      @sheet.merge_range('A3:E3', 'Website: www.aa.com    E-mail: sales@aa.com', format_body)
      @sheet.merge_range('A4:E4', 'TEL: +23423332222      FAX: +12334422555', format_body)
      @sheet.merge_range('A6:E6', 'INVOICE', format_header_center)
    elsif   @record.main.to_s == 'aa BIOSCIENCE (SHANGHAI) CO.,LTD.'
      @sheet.merge_range('A2:E2', "ADD: 313, No.3316 adasasda", format_body)
      @sheet.merge_range('A3:E3', 'Website: www.aa.com    E-mail: sales1@aa.com', format_body)
      @sheet.merge_range('A4:E4', 'TEL: +86-21-6726 0236      FAX: +86-21-5268 5809', format_body)
      @sheet.merge_range('A6:E6', 'INVOICE', format_header_center)
    else
      @sheet.merge_range('A2:E2', "Address: Room qwewqewq China. ZIP 200240", format_body)
      @sheet.merge_range('A3:E3', 'Website: www.aa.com    E-mail: sales@aa.com', format_body)
      @sheet.merge_range('A4:E4', 'TEL: +86 21 67260236      FAX: +86 21 54361023', format_body)
      @sheet.merge_range('A6:E6', 'INVOICE', format_header_center)
    end

  end

  def company_info
    @sheet.merge_range 'A5:E5', "", format_body2
    @sheet.merge_range 'A7:E7', "", format_body2
    @sheet.merge_range 'A8:C8', "To: #{@record.company_name}", format_body2
    @sheet.merge_range 'D8:E8', "Invoice No.: #{@record.invoice}", format_body2
    @sheet.merge_range 'A9:C9', "Phone: #{@record.phone}", format_body2
    @sheet.merge_range 'D9:E9', "Date: #{@record.created_date}", format_body2
    @sheet.merge_range 'A10:C10', repacle_address("Address: #{@record.address}"), format_body2
    @sheet.merge_range 'D10:E10', "PO#: #{@record.po}", format_body2
    @sheet.merge_range 'A11:C11', "Attention: #{@record.to}", format_body2
    @sheet.merge_range 'D11:E11', "Order ID: #{@record.order_uuid}", format_body2
  end

  def table_info
    @sheet.write_string(@index, 0, 'NO.',format_header_table)
    @sheet.write_string(@index, 1, 'Chemical Name',format_header_table)
    @sheet.write_string(@index, 2, 'Catalogue No.',format_header_table)
    @sheet.write_string(@index, 3, 'Qty',format_header_table)
    @sheet.write_string(@index, 4, 'Price (USD)',format_header_table)
    @index = @index+1
    @record.body.each_with_index do |item, index|
      @sheet.write_string(@index, 0, (index + 1).to_s, format_table_body)
      if item[:chemical_name].length < 25
          @sheet.set_row(@index, 20)
      elsif  item[:chemical_name].length < 40 && item[:chemical_name].length > 25
        @sheet.set_row(@index, 45)
      else
        @sheet.set_row(@index, 60)
      end
      @sheet.write_string(@index, 1, item[:chemical_name],format_table_body)
      @sheet.write_string(@index, 2, item[:calalogue_no], format_table_body)
      @sheet.write_string(@index, 3, item[:qty], format_table_body)
      @sheet.write_string(@index, 4, "US$" + item[:price].to_s, format_table_body)
      @index = @index+ 1
    end
    @index = @index+ 1
    @sheet.write_string(@index-1, 4, "Shipping Fee: US$#{@record.shipping_fee}", format_body)
    @sheet.merge_range "A#{@index+1}:D#{@index+1}", "", format_body2
    @sheet.merge_range "A#{@index}:D#{@index}", "", format_body2
    @sheet.write_string(@index, 4, "Handling Fee: US$#{@record.handling_fee}", format_body) if @record.handling_fee.to_f > 0
    @sheet.write_string(@index, 4, "Bank Charge: US$#{@record.bank_fee}", format_body) if @record.bank_fee.to_f > 0
    @sheet.merge_range "A#{@index+2}:D#{@index+2}", '', format_body2
    @sheet.write_string(@index+1, 4, "Total: US$#{@record.total_price}", format_body)
    @sheet.merge_range "A#{@index+3}:E#{@index+3}", "Payment Terms: #{@record.payment_method}", format_body2
    @sheet.merge_range "A#{@index+4}:E#{@index+4}", "Overdue payment may defer new quotation and order by aa.", format_body2
    @sheet.merge_range "A#{@index+5}:E#{@index+5}", "NOTE:Please reference aa catalog number or account email address on all Bank Transfers so we may properly credit your account.Client is responsible for banking charges.
", format_body2
    @sheet.set_row(@index+4, 40)
  end

  def bank_info
    @sheet.merge_range("A#{@index + 6}:E#{@index + 6}", 'Payment Methods:', format_body2)
    @sheet.merge_range("A#{@index + 7}:E#{@index + 7}", 'BANK TRANSFER', format_body2)
    @sheet.merge_range("A#{@index + 8}:E#{@index + 8}", 'Beneficiary Bank: CHINA CONSTRUCTION BANK, SHANGHAI CHEMICAL DISTRICT BRANCH', format_body2)
    @sheet.merge_range("A#{@index + 9}:E#{@index + 9}", 'Bank Address: 201 MUHUA ROAD, FENGXIAN DISTRICT, SHANGHAI, CHINA', format_body2)
    @sheet.merge_range("A#{@index + 10}:E#{@index + 10}", 'Beneficiary Name/ Account Name: SHANGHAI aa CO., LTD.', format_body2)
    @sheet.merge_range("A#{@index + 11}:E#{@index + 11}", 'Beneficiary Address/Account Address: 3316 TINGWEI ROAD, JINSHAN, SHANGHAI, CHINA', format_body2)
    @sheet.merge_range("A#{@index + 12}:E#{@index + 12}", 'ACCT#: 3105 0169 5200 0000 0652', format_body2)
    @sheet.merge_range("A#{@index + 13}:E#{@index + 13}", 'Swift Code: PCBCCNBJSHX', format_body2)
  end

  def set_rows
    ( (1..50).to_a ).each do |i|
      @sheet.set_row(i, 20, nil, 0, 4, 0)
    end
    @sheet.set_row(0, 30)
    @sheet.set_row(9, 40)
    @sheet.set_row(1, 30)
  end

  def repacle_address(address)
    address.index("\r\n").nil? ? address : address.gsub!("\r\n", "  ")
  end


  def image_path
    Rails.root + 'public/coa_logo1.png'
  end

  def format_header_table
    @book.add_format(
      text_wrap: 1,
      align: 'center',
      valign: 'vcenter',
      size: 10,
      bold: 1,
      bg_color: 'silver',
      font: 'Times New Roman'
    )
  end

  def format_header
    @book.add_format(
      text_wrap: 1,
      align: 'center',
      valign: 'bottom',
      size: 16,
      bold: 1,
      bottom: 0,
      border: 1,
      border_color: 'white',
      font: 'Times New Roman'
    )
  end

  def format_header_center
    @book.add_format(
      text_wrap: 1,
      align: 'center',
      valign: 'bottom',
      size: 16,
      bold: 1,
      border: 1,
      border_color: 'white',
      underline: 3,
      font: 'Times New Roman'
    )
  end

  def format_body
    @book.add_format(
      text_wrap: 1,
      align: 'center',
      valign: 'vcenter',
      size: 10,
      bold: 1,
      border: 1,
      border_color: 'white',
      font: 'Times New Roman'
    )
  end

  def format_table_body
    @book.add_format(
      text_wrap: 1,
      align: 'center',
      valign: 'vcenter',
      size: 10,
      bold: 1,
      border: 1,
      border_color: 'gray',
      font: 'Times New Roman'
    )
  end

  def format_blank
    @book.add_format(
      text_wrap: 1,
      align: 'center',
      valign: 'vcenter',
      size: 10,
      bold: 1,
      top: 1,
      top_color: 'gray',
      left: 1,
      left_color: 'white',
      right: 1,
      right_color: 'white',
      font: 'Times New Roman'
    )
  end

  def format_body2
    @book.add_format(
      text_wrap: 1,
      align: 'left',
      valign: 'vcenter',
      size: 10,
      bold: 1,
      border: 1,
      border_color: 'white',
      font: 'Times New Roman'
    )
  end

  def render
    set_rows
    default_text
    company_info
    table_info
    bank_info
    @sheet.paper = 9
    @book.close
    @io.string
  end

  def file_name
    "aa Invoice-#{@record.order_uuid}-#{rand(99999)}.xlsx"
  end

end