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
0
顶
顶
0
踩
踩
分享到： 
