Rails Excel 导出的数字格式错误
13
0
My Rails 应用程序可以使用 caxlsx 和 calsx_rails gem 以 Excel 格式导出付款和发票。导出本身运行良好。不过发票值的格式正确
My Rails 应用程序可以使用 caxlsx
和 calsx_rails
gem 以 Excel 格式导出付款和发票。
导出本身工作正常。不过,发票金额在 LibreOffice 中的格式正确,但在 MS Excel 中的格式错误,为“文本”,而不是“数字”,因此无法将其相加,最后会突然出现“EUR”符号。因此它看起来像是一种货币,但同样无法相加。
我自己没有编写此程序,但会分享以下代码 invoices_payments.xlsx
:
adapter = Download::Xlsx
worksheet_names = adapter.worksheet_names()
in_invoices_header = adapter.in_invoices_header()
out_invoices_header = adapter.out_invoices_header()
in_payments_header = adapter.in_payments_header()
out_payments_header = adapter.out_payments_header()
# Init a workbook
wb = xlsx_package.workbook
xlsx_package.use_shared_strings = true
# Define some common styles
styles = wb.styles
horizontal_right = styles.add_style(alignment: {horizontal: :right})
border_cell = styles.add_style(border: Axlsx::STYLE_THIN_BORDER)
invoice_date_field_style = styles.add_style(border: Axlsx::STYLE_THIN_BORDER, alignment: {horizontal: :right}, format_code: 'yyyy-mm-dd')
invoice_text_field_style = styles.add_style(border: Axlsx::STYLE_THIN_BORDER, alignment: {horizontal: :right})
# Invoice data cells format
invoice_format = {
date: invoice_date_field_style,
text: invoice_text_field_style,
value: styles.add_style(:format_code => "")
}
invoice_cells_formats = Array.new(in_invoices_header.size, invoice_format[:text])
invoice_cells_formats[0] = invoice_format[:date]
invoice_cells_formats[11] = invoice_format[:value]
invoice_cells_formats[13] = invoice_format[:value]
invoice_cells_formats[14] = invoice_format[:value]
# Create Invoices worksheet in this workbook
wb.add_worksheet(name: worksheet_names[:in_invoices]) do |worksheet|
# Add in_invoice headers
invoice_header_row = worksheet.add_row(in_invoices_header, b: true)
# Add in_invoice data
if @in_invoices.present?
@in_invoices.each do |in_invoice|
row = worksheet.add_row([
in_invoice.issuing_date.to_date.strftime("%d.%m.%Y"),
in_invoice.department&.name,
in_invoice.number,
in_invoice.other_company_name_str,
in_invoice.other_company_street_and_no_str,
in_invoice.other_company_postal_code_str,
in_invoice.other_company_city_str,
in_invoice.other_company_tax_number,
in_invoice.other_company_fitaf_id,
in_invoice.service_description,
in_invoice.internal_note,
in_invoice.net,
"#{in_invoice.vat_rate} %",
in_invoice.vat,
in_invoice.gross,
in_invoice.currency,
(in_invoice.privately_paid? ? "Privat ausgelegt" : 'Nicht privat ausgelegt'),
"Eingangsrechnung",
(in_invoice.to_be_paid.present? ? in_invoice.to_be_paid.strftime("%d.%m.%Y") : ""),
in_invoice.paid_via,
(in_invoice.physical_copy.attached? ? "Ja" : "Nein"),
(in_invoice.is_a_hospitality_receipt ? "Bewirtungsbeleg" : "Kein Bewirtungsbeleg"),
in_invoice.place_of_hospitality,
in_invoice.date_of_hospitality&.strftime("%d.%m.%Y"),
in_invoice.reason_for_hospitality,
(in_invoice.tip_of_hospitality if in_invoice.tip_of_hospitality.present?),
in_invoice.participants_of_hospitality,
in_invoice.name_of_hospitality_250_eur,
in_invoice.address_of_hospitality_250_eur,
in_invoice.tax_number_of_hospitality_250_eur,
in_invoice.eur_object.try(:name),
(in_invoice.durable ? "Ja" : "Nein"),
(in_invoice.durable ? in_invoice.cycle : ""),
(in_invoice.durable ? in_invoice.durable_until.strftime("%d.%m.%Y") : ""),
(in_invoice.archived? ? "Ja" : "Nein"),
(in_invoice.proposal? ? "Ja" : "Nein"),
in_invoice.creditor_id,
in_invoice.debitable_bank_account_owner,
in_invoice.debitable_bank_account_number,
in_invoice.debitable_bank_name,
in_invoice.debitable_bank_number,
], style: invoice_cells_formats)
end
end
end
wb.add_worksheet(name: worksheet_names[:out_invoices]) do |worksheet|
# Add in_invoice headers
invoice_header_row = worksheet.add_row(out_invoices_header, b: true)
# Add out_invoice data
if @out_invoices.present?
@out_invoices.each do |out_invoice|
row = worksheet.add_row([
german_date(out_invoice.issuing_date),
out_invoice.department&.name,
out_invoice.number,
out_invoice.other_company_name_str,
out_invoice.other_company_street_and_no_str,
out_invoice.other_company_postal_code_str,
out_invoice.other_company_city_str,
out_invoice.other_company_tax_number,
out_invoice.other_company_fitaf_id,
out_invoice.service_description,
out_invoice.internal_note,
out_invoice.net,
"#{out_invoice.vat_rate} %",
out_invoice.vat,
out_invoice.gross,
out_invoice.currency,
german_date(out_invoice.to_be_paid),
out_invoice.paid_via,
out_invoice.physical_copy.attached? ? "Ja" : "Nein",
"Ausgangsrechnung",
out_invoice.try(:other_company_fitaf_id),
out_invoice.eur_object.try(:name),
out_invoice.durable ? "Dauerrechnung" : "Keine Dauerrechnung",
out_invoice.durable ? out_invoice.cycle : "",
out_invoice.durable ? out_invoice.durable_until.strftime("%d.%m.%Y") : "",
out_invoice.archived ? "Rechnung archiviert" : "Rechnung nicht archiviert",
out_invoice.creditor_id,
out_invoice.debitable_bank_account_owner,
out_invoice.debitable_bank_account_number,
out_invoice.debitable_bank_name,
out_invoice.debitable_bank_number
], style: invoice_cells_formats)
if out_invoice.physical_copy.attached?
worksheet.add_hyperlink :location => link_to_invoice_physical_contract(out_invoice), :ref => row.cells.last
end
end
end
# Calculate row index to except style for rows with in_invoice/ out_invoice data
last_invoice_data_row_index = invoice_header_row.row_index + @out_invoices.size + @out_invoices.size
# Add style for all rows
worksheet.rows.each_with_index do |row, index|
## Don't add common styles for rows between specific index ranges
next if index > invoice_header_row.row_index && index <= last_invoice_data_row_index
## Add common styles for other rows
row.style = horizontal_right
row.style = border_cell
end
end
# Create Payments worksheet in this workbook
wb.add_worksheet(name: worksheet_names[:in_payments]) do |worksheet|
# Add in_payment headers
worksheet.add_row(in_payments_header, b: true)
# Add in_payment data
if @in_payments.present?
@in_payments.each do |in_payment|
## Define specific styles for each data row
in_payment_date_format = styles.add_style(border: Axlsx::STYLE_THIN_BORDER, alignment: {horizontal: :right}, format_code: 'dd.mm.yyyy')
in_payment_text_format = styles.add_style(border: Axlsx::STYLE_THIN_BORDER, alignment: {horizontal: :right})
in_payment_value_format = styles.add_style(:format_code => "")
in_payment_cells_formats = Array.new(in_payments_header.size, in_payment_text_format)
in_payment_cells_formats[0] = in_payment_date_format
in_payment_cells_formats[2] = in_payment_value_format
# Row data
worksheet.add_row([
in_payment.transaction_date,
in_payment.department&.name,
in_payment.value,
in_payment.other_company_str,
in_payment.reason,
in_payment.mean_of_payment.andand.account_name
], style: in_payment_cells_formats)
end
end
end
wb.add_worksheet(name: worksheet_names[:out_payments]) do |worksheet|
# Add in_payment headers
out_payment_header_row = worksheet.add_row(out_payments_header, b: true)
# Add out_payment data
if @out_payments.present?
@out_payments.each do |out_payment|
## Define specific styles for each data row
out_payment_date_format = styles.add_style(border: Axlsx::STYLE_THIN_BORDER, alignment: {horizontal: :right}, format_code: 'dd.mm.yyyy')
out_payment_text_format = styles.add_style(border: Axlsx::STYLE_THIN_BORDER, alignment: {horizontal: :right})
out_payment_value_format = styles.add_style(:format_code => "")
out_payment_cells_formats = Array.new(out_payments_header.size, out_payment_text_format)
out_payment_cells_formats[0] = out_payment_date_format
out_payment_cells_formats[2] = out_payment_value_format
## Row data
worksheet.add_row([
out_payment.transaction_date,
out_payment.department&.name,
-out_payment.value,
out_payment.other_company_str,
out_payment.reason,
out_payment.mean_of_payment.andand.account_name
], style: out_payment_cells_formats)
end
end
# Calculate row index to except style for rows with in_payment/ out_payment data
last_payment_data_index = out_payment_header_row.row_index + @in_payments.size + @out_payments.size
# Add style for all rows
worksheet.rows.each_with_index do |row, index|
## Don't add common styles for rows between specific index ranges
next if index > out_payment_header_row.row_index && index <= last_payment_data_index
## Add common styles for other rows
row.style = horizontal_right
row.style = border_cell
end
end
Rails Excel 导出的数字格式错误
下载声明: 本站所有软件和资料均为软件作者提供或网友推荐发布而来,仅供学习和研究使用,不得用于任何商业用途。如本站不慎侵犯你的版权请联系我,我将及时处理,并撤下相关内容!
下载声明: 本站所有软件和资料均为软件作者提供或网友推荐发布而来,仅供学习和研究使用,不得用于任何商业用途。如本站不慎侵犯你的版权请联系我,我将及时处理,并撤下相关内容!
收藏的用户(0)
X
正在加载信息~