CODEWITHSUNDEEP
rdatatablesdt
Yorgos
Yorgos

Reputation: 30485

datatable {DT}: How to format columns in PDF export?

Can you help me format the numeric columns in the pdf file, like I did using formatCurrency for the html table?

library(DT)


datatable(anek,
          rownames = FALSE,
          extensions = c("Buttons", "FixedColumns"),
          style = "bootstrap", 
          class = "cell-border stripe hover",
          options = list(
            pageLength = 10,
            lengthMenu = list(c(10, 25, 50, -1), c('10', '25', '50', 'All')),
            autoWidth = TRUE,
            columnDefs = list( 
              list(width = "150px", targets = c(2, 4)),
              list(width = "70px", targets = c(3))
            ),
            dom = "Bfrtip",
            scrollX = TRUE,
            fixedColumns = list(leftColumns = 2),
            buttons = 
              list(
                "pageLength",
                list( 
                  extend = "colvis",
                  text = "Column Selection"
                ),
                "copy", "csv",
                list( 
                  extend = "excel",
                  title = "Table Title",
                  filename = "file_name"
                ),
                list( 
                  extend = "pdf",
                  title = "Table Title",
                  pageSize = "A4",
                  orientation = "landscape",
                  filename = "file_name",
                  exportOptions =
                    list(
                      columns = ":visible"
                    ),
                  customize = JS(
                    "function (doc) { doc.defaultStyle.fontSize = 7;   doc.styles.tableHeader.fontSize = 8;}"
                  )
                ), "print"
              )
          )
        ) %>%
  formatStyle("S.N.", backgroundColor = "AntiqueWhite", fontWeight = "bold") %>%
  formatCurrency(c(4:10), "", digits = 2, before = FALSE, dec.mark = ",", mark = ".")

anek <- structure(list(Dep = c("ΤΜ.I.", "ΤΜ.II.", "ΤΜ.II.", "ΤΜ.II.", 
"ΤΜ.II.", "ΤΜ.II.", "ΤΜ.II.", "ΤΜ.II.", "ΤΜ.II.", "ΤΜ.III.", 
"ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", 
"ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", "ΤΜ.III.", "ΤΜ.IV.", 
"ΤΜ.IV.", "ΤΜ.IV.", "ΤΜ.IV.", "ΤΜ.IV.", "ΣΥΝΟΛΟ"), S.N. = c("2507", 
"1501", "1509", "512", "579", "715", "715/1", "715/2", "716", 
"10277", "10299", "2501", "2502", "2503", "2504", "2505", "2509", 
"2510", "635", "717", "748", "10005", "3500", "563", "673", "706", 
""), date1 = structure(c(17850, 17886, 18051, 15370, 15877, 16324, 
17753, 18024, 17382, 18010, 18046, 17108, 17112, 17112, 17175, 
17189, 17555, 17738, 16057, 16339, 17886, 13761, 17919, 14349, 
18047, 16967, NA), class = "Date"), disc = c(45.71, 67.37, 47.82, 
16.5, 5.29, 17.24, NA, NA, 45.2, 52.78, 23.88, 73.11, 79.36, 
81.34, 83.62, 79.71, NA, NA, 10.15, 22.35, 59.84, 15.17, 68.37, 
43.31, 39.22, 52.38, NA), amt1 = c(66580539.74, 501727.17, 6391258.24, 
79052072.93, 293130532.46, 227043595.76, 8064219.16, 17682391.89, 
96049725.14, 2195834.07, 1050594.85, 1015203.04, 792340.25, 711060.69, 
641971.62, 717569.38, 896451, 2408012, 374242810.48, 41297174.41, 
880979.81, 17171361, 698347.48, 13916980.61, 4420487.53, 11493975.32, 
NA), amt2 = c(66580539.74, 501727.17, 6391258.24, 76146190.07, 
293130532.46, 224865818.2, 8064219.16, 17682391.89, 96049725.14, 
2195834.07, 1050594.85, 1015203.04, 792340.25, 711060.69, 641971.62, 
717569.38, 896451, 2408012, 374242810.48, 41297174.41, 880979.81, 
17171361, 698347.48, 13916980.61, 4420487.53, 11111181.57, NA
), amt3 = c(4835026, 203727.48, 430570.36, 70602051.97, 256796820.37, 
214316960.55, 6920749.94, 3751850.1, 56588198.55, 1416915.55, 
0, 794438.4, 728239, 559712.23, 320292.24, 291345.95, 1041281.88, 
1034734, 347683348.83, 32849851.68, 115494.97, 8626297.28, 254240.29, 
14234370.39, 221024.38, 9828996.53, NA), amt4 = c(4835026, 203727.48, 
430570.36, 70514513.58, 261057863.59, 214668731.05, 6920749.94, 
3751850.1, 56897155.02, 1416915.55, 0, 794438.4, 728239, 559712.23, 
320292.24, 291345.95, 1041281.88, 1034734, 349553403.16, 33144192.33, 
115494.97, 9625946.02, 254240.29, 13783777.58, 221024.38, 9828996.53, 
NA), amt5 = c(0, 203727.48, 117468.2, 70514513.58, 260387427.31, 
214637911.45, 6920749.94, 3751850.1, 53147780.22, 1416915.55, 
0, 794438.4, 728239, 559712.23, 320292.24, 291345.95, 737250.88, 
3000, 349489630.86, 30452681.57, 115494.97, 8323787.92, 254240.29, 
13783777.58, 0, 9828996.53, NA), amt6 = c(66580539.74, 297999.69, 
6273790.04, 5631676.49, 32743105.15, 10227906.75, 1143469.22, 
13930541.79, 42901944.92, 778918.52, 1050594.85, 220764.64, 64101.25, 
151348.46, 321679.38, 426223.43, 159200.12, 2405012, 24753179.62, 
10844492.84, 765484.84, 8847573.08, 444107.19, 133203.03, 4420487.53, 
1282185.04, 236799529.61)), class = "data.frame", row.names = c(NA, 
-27L))

Upvotes: 2

Views: 315

Answers (1)

Georgios Vas
Georgios Vas

Reputation: 76

Just replace the 'columnDefs' code section, with this one

columnDefs = list(
      list(width = "150px", targets = c(2, 4)),
      list(width = "70px", targets = c(3)),
      # https://datatables.net/forums/discussion/56163/number-format-of-pdf-export-thrown-off-when-reordering
      list(render = JS("$.fn.dataTable.render.number( '.', ',', 2, '', ' €' )"), targets = c(5:9) )
    ),

Upvotes: 3

Related Questions