- 浏览: 287318 次
- 性别:
- 来自: 武汉
文章分类
最新评论
-
masuweng:
如何给新人机会 -
masuweng:
多sql结果集按列合并新结果报表实现方案 -
Ahe:
赞
坚持长跑方能赢 -
masuweng:
好好好
程序员如何更好的了解自己所做的事情 -
小楠人:
laoguan123 写道楼主好,使用过一些excel导入导出 ...
excell导入导出
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
发表评论
-
git仓库创建
2020-09-04 15:33 646推送现有文件夹 cd existing_folder git ... -
puma高并发
2020-08-19 09:31 436nginx突发大量502报错 top看一下,cpu的占用并不高 ... -
searchkick
2019-04-10 11:30 0# 通用查询块(条件) def general_ ... -
导入线下excell业务数据按权重匹配线上数据
2019-03-07 11:00 829业务场景:(系统间还没有接口对调,订单号暂时需要线下处理) 线 ... -
两对象同时映射一对一和一对多
2019-02-20 10:14 751class Kpi::Team < Applicat ... -
ruby一些类加载方式
2018-12-21 10:12 530require_dependency 'order/sco ... -
基于ruby的gem remotipart的异步上传文件
2018-12-21 10:11 497针对某一对象保存实例化之前,异步上传图片保存。 gem ' ... -
基于html2canvas的长图分享
2018-12-21 10:11 1112<span class="ui label ... -
基于ruby Mechanize的爬虫
2018-12-20 13:09 598def self.sang_carwler ... -
一些常用加密方式
2018-12-20 13:02 692sign = OpenSSL::Digest::SHA256. ... -
ruby 调用restful接口示例
2018-12-20 12:02 879链接参数中添加token def self.query_p ... -
rails错误日志记录
2018-12-19 14:41 672Rails中对日志的处理采用的是“消息-订阅”机制,各部分组件 ... -
railsAPI接收Base64文件
2018-12-18 11:05 998tmp_dir = " ... -
ruby 调用savon接口示例
2018-12-18 10:51 935例子一 module Api module Aob ... -
关于国际商城现货展示与购物车的费用设计
2018-11-15 18:34 394关于国际商城现货展示 ... -
基于多线程的全局变量
2018-10-31 19:50 1066def current_nation def ... -
hash最小值过滤算法
2018-10-31 09:52 1038[["数量","包装" ... -
阿里云裸机部署rails运用
2018-10-08 20:33 1304登录阿里云后首先 sudo apt-get update a ... -
打包订单单据发给货代
2018-09-11 15:43 1143pdf&excell&png # rend ... -
基于mini_magick多图片合并/添加水印
2018-07-02 17:44 1656class ImageService requi ...
相关推荐
应用Rails进行敏捷Web开发(中文版)第三版.part1.ra
GoOnRails: 一个Rails生成器用于生成Go代码并集成Go APIs至一个Rails app中
Rails2.3.8开发安装环境整包,包含ruby1.8.7,rails2.3.8,以及9个相关依赖的gems,一次下载基本上就可以把ROR环境建成。
Ruby on Rails环境搭建(基于WinXP & RadRails) 博文链接:https://wangcheng.iteye.com/blog/113584
rails-learning, 学习 Rails & ruby的最佳方法 Ruby on Rails从头开始学习东西几乎总是一个艰苦的事情,你根本不知道从哪里开始,或者不从开始开始,把东西踢到什么地方。 你不必担心找到最佳的资源,排序坏的资源,...
网格是自动生成的。包括四个跟踪模板。用这些或者自己做。使用任何你想要的模型,或只是把相机放在它和去兜风! 是基于物理的相互作用。这辆车有一个刚体,可以像其他刚体一样被推、拉、撞、跳和加速。轨道本身可以被...
简单的CLI利用Docker生成和运行Rails的环境
关于go-on-rails生成器的基本用法的简单 展示了如何将Go API集成到Rails项目中显示了如何处理Rails会话以在go-on-rails生成的Go API中获取用户的信息先决条件Rails 4.2+(不支持Rails 6,需要您的帮助) Golang ...
Rails Erd - 为Rails应用程序生成实体关系图
一种工具,可帮助您使用 Rails 中的集成测试为 API 生成文档。 安装 将 gem 定义添加到您的 Gemfile 并bundle install : gem 'api_docs' 要访问生成的文档,请将其挂载到routes.rb的路径,如下所示: mount ...
rails_layout, 为各种前端框架生成 Rails 应用程序布局文件 RailsLayout gem使用这里 gem 可以设置你选择的前端框架的布局文件:Zurb基础 5.3Bootstrap 4.0Bootstrap 3.3它还将为 Bootstrap 或者基础设置设计视图。...
《Rails之道》按照Rails的各个子系统进行组织编排,分别介绍了Rails的环境、初始过程、配置和日志记录,Rails的分配器、控制器、页面生成和路由,REST、资源和Rails,ActiveRecord的基础、关联、验证和高级技巧,...
bootstrap-generators, Bootstrap 生成器为 Rails 提供 Twitter Bootstrap 生成器 Bootstrap 生成器 bootstrap生成器为 Rails 4 ( 支持的Rails> = 3.1 ) 提供 Twitter Bootstrap 生成器。 Bootstrap 是 Twitter的...
Simple Form - 轻松处理Rails表单
rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails 2.3.2离线安装rails ...
routing-filter 包裹了 Rails 路由系统的复杂野兽,在 Rails URL 识别和生成中提供了看不见的灵活性和强大功能 路由过滤器围绕着复杂的野兽,Rails 路由系统在 Rails URL 识别和生成中提供了看不见的灵活性和强大...
NULL 博文链接:https://mylir.iteye.com/blog/800734
jquery-fileupload-rails, 用于 Rails的jQuery文件上传集成 Rails 文件上传jQuery-File-Plugin 是一个文件上传插件,由的Tschan 。 jQuery文件上传功能多文件选择。drag&拖放支持。进度栏和jQuery预览图像。 支持...