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