read uploaded excel sheet file and loop through its items to update a SharePoint site column choices inside our SPFx

Question

We have the following Panel to manually add/delete choices inside a SharePoint column named Category:-

and here is the related .tsx code for the above Panel:-

import * as React from "react";  
  
import {  
  Stack,  
  ProgressIndicator,  
  Panel,  
  PanelType,  
  DefaultButton,  
  AnimationStyles,  
  mergeStyles,  
  TextField,  
  PrimaryButton,  
  Dropdown,  
  IDropdownOption,  
  MessageBar,  
  MessageBarType,  
  Label,  
  Text,  
  ILabelStyles,  
  Link,  
  IconButton,  
} from "office-ui-fabric-react";  
  
import { _copyAndSort } from "../../controls/helpers";  
import * as moment from "moment";  
import * as strings from "DocumentsViewerWebPartStrings";  
import { IReadonlyTheme } from "@microsoft/sp-component-base";  
import Dropzone from "../../controls/Dropzone";  
import { IDocument } from "../../models/IDocument";  
  
export interface ICategoriesPanelProps {  
  themeVariant: IReadonlyTheme | undefined;  
  showPanel: boolean;  
  hidePanel: () => void;  
  categories: string[];  
  addCategory: (category: string) => void;  
  removeCategory: (category: string) => void;  
  castFiletoIDoc: (file: File) => IDocument;  
}  
  
export interface ICategoriesPanelState {  
  busy: boolean;  
  newCategory: string;  
  uploadPlaceholders: IDocument[];  
}  
  
export default class CategoriesPanel extends React.Component<ICategoriesPanelProps, ICategoriesPanelState> {  
  constructor(props: ICategoriesPanelProps) {  
    super(props);  
  
    this.state = { busy: true, newCategory: null ,uploadPlaceholders: []};  
    
  }  
  
  public componentDidMount(): void {  
    this.setState({ busy: false });  
  }  
  
  private handleNewCategoryFieldChange = (e, newValue: string) => {  
    this.setState({ newCategory: newValue });  
  };  
  
  private add = async () => {  
    this.setState({ busy: true });  
  
    await this.props.addCategory(this.state.newCategory);  
  
    this.setState({ busy: false, newCategory: null });  
  };  
  
  private remove = async (category: string) => {  
    this.setState({ busy: true });  
  
    if (category) {  
      this.props.removeCategory(category);  
    }  
  
    this.setState({ busy: false });  
  };  
  private onDrop = (moreFiles) => {  
    const placeholders = [...this.state.uploadPlaceholders];  
  
    moreFiles.forEach((file, i) => {  
      const idoc = this.props.castFiletoIDoc(file);  
  
      placeholders.push({  
        ...idoc,  
        key: i.toString(),  
      });  
    });  
    this.setState({ uploadPlaceholders: [...placeholders] });  
  
    // Upload the file  
    //this.props.uploadFolderIcon(moreFiles[0], this.props.folder);  
  };  
  
  private removeDocument = (document: IDocument) => {  
    this.setState({ uploadPlaceholders: [] });  
  };  
  public render(): React.ReactElement<ICategoriesPanelProps> {  
    const appearingStyle = mergeStyles(AnimationStyles.scaleDownIn100);  
  
    return (  
      <Panel  
        headerText={strings.ManageCategories}  
        type={PanelType.medium}  
        isOpen={this.props.showPanel}  
        onDismiss={this.props.hidePanel}  
        // You MUST provide this prop! Otherwise screen readers will just say "button" with no label.  
        closeButtonAriaLabel={strings.Close}  
        isBlocking={true}  
        hasCloseButton={true}  
      >  
        <Stack tokens={<!-- -->{ childrenGap: 15 }}>  
        <Stack.Item>  
            <Dropzone  
              themeVariant={this.props.themeVariant}  
              onDrop={this.onDrop}  
              uploadPlaceholders={this.state.uploadPlaceholders}  
              removeDocument={this.removeDocument}  
            />  
            {/* <PrimaryButton  
              text={strings.StartUpload}  
              onClick={this.uploadDocuments}  
              disabled={this.state.uploading || this.state.uploadFiles.length === 0}  
            /> */}  
          </Stack.Item>  
          <Stack.Item align="end">  
            {this.props.categories.length} {strings.Categories.toLowerCase()}  
          </Stack.Item>  
  
          <Stack.Item>  
            <Stack tokens={<!-- -->{ childrenGap: 24 }}>  
              <Stack.Item  
                styles={<!-- -->{  
                  root: {  
                    padding: "10px 20px",  
                    backgroundColor: this.props.themeVariant.palette.neutralLight,  
                  },  
                }}  
              >  
                <Stack tokens={<!-- -->{ childrenGap: 4 }}>  
                  <Stack.Item>  
                    {this.props.categories.map((category, i) => (  
                      <Stack  
                        tokens={<!-- -->{ childrenGap: 6 }}  
                        horizontal  
                        horizontalAlign="space-between"  
                        styles={<!-- -->{  
                          root: {  
                            alignItems: "center",  
                          },  
                        }}  
                        className={appearingStyle}  
                      >  
                        <Stack.Item>{category}</Stack.Item>  
                        <IconButton  
                          iconProps={<!-- -->{ iconName: "Delete" }}  
                          title={`${strings.Remove} ${category}`}  
                          onClick={() => this.remove(category)}  
                          disabled={this.state.busy}  
                        />  
                      </Stack>  
                    ))}  
                  </Stack.Item>  
                  <Stack.Item>  
                    <Stack  
                      tokens={<!-- -->{ childrenGap: 6 }}  
                      horizontal  
                      horizontalAlign="space-between"  
                      styles={<!-- -->{  
                        root: {  
                          alignItems: "center",  
                        },  
                      }}  
                      className={appearingStyle}  
                    >  
                      <Stack.Item>  
                        <TextField  
                          label={strings.AddNewCategory}  
                          name="newCategory"  
                          value={this.state.newCategory}  
                          onChange={this.handleNewCategoryFieldChange}  
                          disabled={this.state.busy}  
                          styles={<!-- -->{ root: { width: 300 } }}  
                        />  
                      </Stack.Item>  
                      <IconButton  
                        iconProps={<!-- -->{ iconName: "Add" }}  
                        title={`${strings.Add} ${this.state.newCategory}`}  
                        onClick={this.add}  
                        disabled={this.state.busy}  
                      />  
                    </Stack>  
                  </Stack.Item>  
                </Stack>  
              </Stack.Item>  
            </Stack>  
          </Stack.Item>  
        </Stack>  
      </Panel>  
    );  
  }  
}  

currently the SPFx allow to manually add/edit the choices, but my question is how we can read the uploaded excel sheet file (which will contain the choices) inside the DropZone, loop through the choices >> remove existing choices and add the ones inside the sheet? Can anyone advice please?

Here is the DropZoneExport.tsx:-

import * as React from "react";  
  
import { Stack, IStyle } from "office-ui-fabric-react";  
import { IReadonlyTheme } from "@microsoft/sp-component-base";  
import * as strings from "DocumentsViewerWebPartStrings";  
import { IDocument } from "../models/IDocument";  
import DocumentRow from "./DocumentRow";  
import { useCallback, useState } from "react";  
import { useDropzone } from "react-dropzone";  
  
export interface IDropzoneExportProps {  
  themeVariant: IReadonlyTheme | undefined;  
  onDrop: (files) => void;  
  uploadPlaceholders: IDocument[];  
  removeDocument: (document: IDocument) => void;  
}  
  
export interface IDocumentsDropzoneExportState {  
  files: any[];  
}  
  
export default function DropzoneExport(props: IDropzoneExportProps) {  
  // https://www.npmjs.com/package/react-dropzone  
  const onDrop = useCallback(async (acceptedFiles) => {  
    // Do something with the files  
    console.log("something dropped");  
  
    props.onDrop(acceptedFiles);  
  }, []);  
  
  const { getRootProps, getInputProps, isDragActive } = useDropzone({  
    onDrop,  
    maxFiles: 1,  
    accept: {  
      "text/csv*": [".csv"],  
      //acceptedFiles={[".csv, text/csv, application/vnd.ms-excel, application/csv, text/x-csv, application/x-csv, text/comma-separated-values, text/x-comma-separated-values"]}  
    },  
  });  
  
  const dropBoxStyle: IStyle = {  
    border: "1px dashed",  
    borderColor: props.themeVariant.semanticColors.inputBorder,  
    padding: "0.5rem 1rem",  
    marginBottom: ".5rem",  
    backgroundColor: props.themeVariant.palette.neutralQuaternary,  
  };  
  
  return (  
    <Stack>  
      <Stack.Item styles={{ root: dropBoxStyle }}>  
        <div {...getRootProps()} style={{ outline: "none" }}>  
          <input {...getInputProps()} />  
          {isDragActive ? <p>{strings.Item_DropHere}</p> : <p>{strings.Item_DropInfo}</p>}  
  
          <div  
            onClick={(e) => {  
              e.preventDefault();  
              e.stopPropagation();  
            }}  
          >  
            {props.uploadPlaceholders.map((placeholder) => {  
              return <DocumentRow document={placeholder} themeVariant={props.themeVariant} removeDocument={props.removeDocument} />;  
            })}  
          </div>  
        </div>  
      </Stack.Item>  
    </Stack>  
  );  
}  

Answer 1

Hi @john john

Based on my research and testing, you can refer to the following articles to read Excel File using SPFX. Hope it can help you .Thanks for your understanding and support.

For reference:
https://www.c-sharpcorner.com/blogs/reading-sharepoint-document-library-excel-file-using-spfx-and-pnpjs

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---