Export href to excel

SrVinicius

Hello everyone!

I have the following task: Exporting href attributes when generating an Excel file with DataTables. I'm using the latest version. I searched but couldn't find anything, only some posts saying it's quite "difficult." Is there currently any way to do this? In my project, i just use the default buttons to export to excel

allan

Sure its possible, but the "difficult" part still applies.

You'd need to modify the XML that is created to create the appropriate link tag structure and apply whatever attributes are needed in the OpenSpreadsheet spec. I'm afraid that is not something that the Buttons export currently does.

It might be worth looking at SheetJS if you are considering building a more complex / complete XLSX exporter for DataTables. My Excel export was only intended for basic data.

Allan

lucas_araujo

I think with using the customize it is doable

choc

Are you saying that you have cells that contain a link (or whatever) with an href attribute?

As @lucas_araujo saied, it's doable.

I will also do the same thing recently, if you can provide a demo I'm happy to have a look.
I have already played around with the customize function before in DT1 where I added additional content into the Excel cells.

But in your case, if you don't need to make new rows in Excel. I guess what you need is the exportOptions:

                exportOptions: {
                    stripHtml: false,
                    format: {
                        body: function (data, row, column) {
                            // do the extraction of the href
                            return data;
                        }
                    }
                },

Note the use of stripHtml: false, since I parse the html by myself, so I turned it off.

rf1234

Here is something on hyperlinks in OpenOffice. Something like that and probably more should go into "// do the extraction of the href" above.

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_HYPERLINK_function

choc

If you don't mind that the hyperlink is not automatically render when you open it. (You need to click the cell and press Enter key to render)

You can simply use:

                        buttons: [
                        {
                          extend: 'excelHtml5',
                          exportOptions: {
                            format: {
                              body: function (data, row, column) {
                                if ( column === 0) {
                                  console.log("data:", data)
                                  const href = data.match(/href=['"]([^'"]*)['"]/);
                                  const text = data.match(/<a[^>]*>([^<]*)<\/a>/); // if it's a <a> tag
                                  if (href && text) {
                                    data = `=HYPERLINK("${href[1]}", "${text[1]}")`;
                                  }
                                  return data
                                }
                                return data;
                              }
                            }
                          }
                        }
                      ],

I don't know how to render cells automatically without clicking on it or pressing Enter. If anyone knows how to do this, please let me know!

(I have Formulas > Calculation Options > Automatic in Excel already)

rf1234

@choc Cool! Thanks a lot!