Exporting Arrays to Excel: A Reusable React Component

Understanding the Requirements

To create a reusable export-array-to-excel button, we need to fulfill the following criteria:

• The component should be reusable and importable into any page.
• When clicked, it should open a modal allowing users to choose between downloading all columns or selected columns.
• The modal should contain a download button that downloads a JSON API response array directly into a spreadsheet file based on the selected columns.

Setting Up the React App and Chakra UI

To get started, we’ll set up a demo React app using Create React App and install Chakra UI. Chakra UI is an open-source component library that provides simple, modular, and accessible components for building UI elements.

Converting Arrays to Excel

To convert an array of objects to an Excel file, we’ll perform four tasks:

1. Get the API response array and convert it to a table: We’ll loop through the keys of the first object to form the table headers and then loop through each object to create rows.
2. Embed the table into a spreadsheet XML template: We’ll use the SpreadsheetDataXML specification by Microsoft to describe how a spreadsheet file should be structured.
3. Programmatically create a file URL for the spreadsheet template: We’ll convert the Excel template into a file or file-like object using the blob method and create a unique file URL using the createObjectURL method.
4. Assign the created URL to the href of an anchor tag with a download attribute: We’ll programmatically click the anchor tag to prompt a download.

const data = [...]; // API response array
const headers = Object.keys(data[0]);
const rows = data.map((row) => {
  return headers.map((header) => {
    return row[header];
  });
});

const excelTemplate = `<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
             xmlns:o="urn:schemas-microsoft-com:office:office"
             xmlns:x="urn:schemas-microsoft-com:office:excel"
             xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Worksheet ss:Name="Sheet1">
    <Table>
      ${headers.map((header) => {
        return `<Column ss:Width="100"/>`;
      }).join('')}
      ${rows.map((row) => {
        return `<Row>${row.map((cell) => {
          return `<Cell><Data ss:Type="String">${cell}</Data></Cell>`;
        }).join('')}</Row>`;
      }).join('')}
    </Table>
  </Worksheet>
</Workbook>`;

const blob = new Blob([excelTemplate], { type: 'application/vnd.ms-excel' });
const url = URL.createObjectURL(blob);
const anchor = document.createElement('a');
anchor.href = url;
anchor.download = 'data.xlsx';
anchor.click();

Creating the Reusable Export-Array-to-Excel Component

We’ll create two files: ArrayToExcel.js and ArrayToExcelButton.js. The former will contain the function that takes care of the conversion and download, while the latter will house the button, modal, and other necessary UI elements.

// ArrayToExcel.js
export const arrayToExcel = (data, columns) => {
  // Convert data to Excel template
  //...
  return url;
};

// ArrayToExcelButton.js
import { arrayToExcel } from './ArrayToExcel';

const ArrayToExcelButton = ({ data }) => {
  const [isOpen, setIsOpen] = useState(false);
  const [selectedColumns, setSelectedColumns] = useState([]);

  const handleDownload = () => {
    const url = arrayToExcel(data, selectedColumns);
    const anchor = document.createElement('a');
    anchor.href = url;
    anchor.download = 'data.xlsx';
    anchor.click();
  };

  return (
    <div>
      <Button onClick={() => setIsOpen(true)}>Export to Excel</Button>
      <Modal isOpen={isOpen} onClose={() => setIsOpen(false)}>
        <Select
          value={selectedColumns}
          onChange={(e) => setSelectedColumns(e.target.value)}
          placeholder="Select columns"
        >
          {Object.keys(data[0]).map((column) => (
            <option key={column} value={column}>{column}</option>
          ))}
        </Select>
        <Checkbox>Download all columns</Checkbox>
        <Button onClick={handleDownload}>Download</Button>
      </Modal>
    </div>
  );
};

export default ArrayToExcelButton;

Testing the Component

We’ll make a mock API fetch call in App.js and pass userData into the ArrayToExcelButton component as props. With that, our export-array-to-excel component is ready!

// App.js
import React from 'eact';
import ArrayToExcelButton from './ArrayToExcelButton';

const App = () => {
  const [userData, setUserData] = useState([]);

  useEffect(() => {
    // Mock API fetch call
    const data = [...]; // API response array
    setUserData(data);
  }, []);

  return (
    <div>
      <ArrayToExcelButton data={userData} />
    </div>
  );
};

export default App;