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