9

I am using react-html-to-excel to convert my table to excel but what I want is to not to export the first column to excel i.e the first column should not exported to excel i have gone through the documentation of this library i am using but haven't found any thing

My code

 <div className="App">
      <ReactHTMLTableToExcel
        id="test-table-xls-button"
        className="download-table-xls-button"
        table="table-to-xls"
        filename="test"
        sheet="tablexls"
        buttonText="Download as XLS"
      />
      <table id="table-to-xls">
        <thead>
          <tr>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Age</th>
          </tr>
        </thead>
        <tbody>
          {data.map(item => (
            <tr>
              <td>{item.firstname}</td>
              <td>{item.lastname}</td>
              <td>{item.age}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>

Link of codesand box full code

If it is not supported in this library then I am open to use any other library which do these kind of stuff.

Improve this question
2
  • first column is Firstname or jill Commented Apr 20, 2020 at 7:15
  • @MohammadAliRony first column means whole first column like her firstname,jill,david,nick, I do not want to export that column Commented Apr 20, 2020 at 8:01

3 Answers 3

Reset to default
36
+100

I can propose the following two modules since I have already used them successfully in production (with custom columns also):

  1. react-data-export
  2. react-csv

And a third one, after a quick search, which looks promising (I haven't used it though)

  1. react-export-excel --> It does not support TypeScript

The library that you are currently using does not support the removal of columns in the excel file according to this issue, which also proposes a fourth option:

  1. react-csv-creator

EDIT: Ok, I have created two examples. The first can be found here and uses my 2nd proposal, react-csv

The second is the following, which uses my 3rd proposal, react-export-excel

2nd EDIT: I have updated both examples, the columns now are defined from your object's structure, and two ways of removing the unwanted column are proposed (by key-value firstname or by index - the 1st one).

Please take into account that the above methods will work successfully if the objects' structure is consistent in your data (each entry should have the same columns).

The camelCase method that I have used in both examples was taken from here.

import React from "react";
import ReactDOM from "react-dom";
import "./styles.css";
import ReactExport from "react-export-excel";
const ExcelFile = ReactExport.ExcelFile;
const ExcelSheet = ReactExport.ExcelFile.ExcelSheet;
const ExcelColumn = ReactExport.ExcelFile.ExcelColumn;

function App () {
    
    const data = [
        { firstname: "jill", lastname: "smith", age: 22 },
        { firstname: "david", lastname: "warner", age: 23 },
        { firstname: "nick", lastname: "james", age: 26 }
    ];

    const camelCase = (str) =>  {
        return str.substring(0, 1).toUpperCase() + str.substring(1);
    };

    const filterColumns = (data) => {
        // Get column names
        const columns = Object.keys(data[0]);

        // Remove by key (firstname)
        const filterColsByKey = columns.filter(c => c !== 'firstname');

        // OR use the below line instead of the above if you want to filter by index
        // columns.shift()

        return filterColsByKey // OR return columns
    };

    return (
        <div className="App">
            <ExcelFile filename="test">
                <ExcelSheet data={data} name="Test">
                    {
                        filterColumns(data).map((col)=> {
                            return <ExcelColumn label={camelCase(col)} value={col}/>
                        })
                    }
                </ExcelSheet>
            </ExcelFile>
            <table id="table-to-xls">
                <thead>
                <tr>
                    <th>Firstname</th>
                    <th>Lastname</th>
                    <th>Age</th>
                </tr>
                </thead>
                <tbody>
                {data.map(item => {
                    return (
                        <tr>
                            <td>{item.firstname}</td>
                            <td>{item.lastname}</td>
                            <td>{item.age}</td>
                        </tr>
                    );
                })}
                </tbody>
            </table>
        </div>
    );
}

const rootElement = document.getElementById("root");
ReactDOM.render(<App />, rootElement);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Can you help me with one example which can hide the rows while exporting like I do not want to export the first column'
@manishthakur I have updated my answer with examples hope it helps!
Hey your both example was fine but there is issue, the issue is data is dynamic I can have 5 columns some times I may have 8 columns and in your code you are defining the columns to be exported is manually, like { label: "Lastname", key: "lastname" }, { label: "Age", key: "age" } ]; in react-csv and <ExcelFile> <ExcelSheet data={data} name="test"> <ExcelColumn label="Lastname" value="lastname" /> <ExcelColumn label="Age" value="age" /> </ExcelSheet> </ExcelFile> in example 2 i.e react-export-excel
@manishthakur I have updated my answer again (both examples) now the columns are defined by the data and filtered by choice (two methods of filtering are mentioned).
react export excel is without typescript support
|
6

Use xlsx

yarn add xlsx

import * as XLSX from 'xlsx';

const MYdata = [
  {"title":Title1, "website":"Foo"},
  {"title":Title2, "website":"Bar"}
]

const worksheet = XLSX.utils.json_to_sheet(MYdata);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
XLSX.writeFile(workbook, "MYSavedData.xlsx");

As per their docs: By default, it will generate a header row and one row per object in the array.

Improve this answer

3 Comments

It works and it was updated and simple. thanks
How to give custom header column names?
This is the best solution for export to excel, I used it in both ReactJs and NodeJs. Working expected on both the sides.
1

You can create a simple hack to manipulate the table HTML code by overriding the ReactHTMLTableToExcel.format function which gets the table HTML to format the XLS document.

ReactHTMLTableToExcel.format = (s, c) => {
  // If there is a table in the data object
  if (c && c['table']) {
    // Get the table HTML
    const html = c.table;

    // Create a DOMParser object
    const parser = new DOMParser();

    // Parse the table HTML and create a text/html document
    const doc = parser.parseFromString(html, 'text/html');

    // Get all table rows
    const rows = doc.querySelectorAll('tr');

    // For each table row remove the first child (th or td)
    for (const row of rows) row.removeChild(row.firstChild);

    // Save the manipulated HTML table code
    c.table = doc.querySelector('table').outerHTML;
  }

  return s.replace(/{(\w+)}/g, (m, p) => c[p]);
};

And you've got the table HTML filtered and with the first column removed.

You can check this working in this Stackblitz project.

Improve this answer

2 Comments

As of today, the example does not work. I got warning "extension and format don't match. Do you still want to open?" and upon opening only two columns are shown instead of 4
It looks like the package ReactHTMLTableToExcel is no longer maintained and is compatible with react 15 or below.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.