Untitled

 avatar
unknown
plain_text
9 months ago
15 kB
4
Indexable
[
    {
        "header": "Title",
        "value": "IE: Tech and FAQ"
    },
    {
        "header": "Description",
        "value": "General information for state Integrated Eligbility Projects"
    },
    {
        "header": "Offering",
        "value": "Integrated Eligibility"
    },
    {
        "header": "Program",
        "value": "AU"
    },
    {
        "header": "Category",
        "value": "EE"
    },
    {
        "header": "Needed by",
        "value": 45492
    },
    {},
    {
        "header": "State",
        "value": "Question 1: Solution/Project Name"
    },
    {
        "header": "Arkansas",
        "value": "ARIES"
    },
    {
        "header": "California",
        "value": "Calheers/BenefitsCAL"
    },
    {
        "header": "Colorado",
        "value": "CBMS"
    },
    {
        "header": "Connecticut",
        "value": "ImpaCT"
    },
    {
        "header": "Delaware",
        "value": "DEMS"
    },
    {
        "header": "Florida",
        "value": "ACCESS"
    },
    {
        "header": "Georgia",
        "value": "Gateway"
    },
    {
        "header": "Illinois",
        "value": "IES"
    },
    {
        "header": "Indiana",
        "value": "ICES"
    },
    {
        "header": "Kentucky",
        "value": "IEES"
    },
    {
        "header": "Louisiana",
        "value": "LITE/LaMEDS"
    },
    {
        "header": "Maine",
        "value": "ACES"
    },
    {
        "header": "Michigan",
        "value": "BRIDGES"
    },
    {
        "header": "Montana",
        "value": "CHIMES"
    },
    {
        "header": "Nevada",
        "value": "State of Nevada"
    },
    {
        "header": "New Hampshire",
        "value": "HEIGHTS"
    },
    {
        "header": "New Mexico",
        "value": "ASPEN"
    },
    {
        "header": "North Dakota",
        "value": "SPACES"
    },
    {
        "header": "Oregon",
        "value": "ONE"
    },
    {
        "header": "Pennsylvania",
        "value": "COMPASS"
    },
    {
        "header": "Rhode Island",
        "value": "IES/RIBridges"
    },
    {
        "header": "Tennessee",
        "value": "TEDS/EBMS"
    },
    {
        "header": "Texas",
        "value": "TIERS"
    },
    {
        "header": "Virginia",
        "value": "VaCMS"
    },
    {
        "header": "Washington ",
        "value": "ACES"
    },
    {
        "header": "Wisconsin",
        "value": "CARES"
    },
    {
        "header": "Wyoming",
        "value": "WES"
    }
]






Currently the below code is not reading the whole excel. 
Update the code to read only the below 6 and their values then add it to json.

Title(R1 C1)
Description(R2 C1)
Offering(R3 C1)
Program(R4 C1)
Category(R5 C1)
Needed By(R6 C1) (in json add it as DueDT)

After this  , THEN FROM ROW 8 onwards, take the data horizontally and keep them as questions[] as shown in the below expected json structure.
follow the series of ROW & COLUMN (Example,R1 C3) mentioned the bracket till it has values in row & column

. above is the jsonData object that we are getting from the excel in convertToJSONAndUpload(). update the code to create a json which maps the data as below format:

{
"Title:IE": "Tech and FAQ",
"Description":"General information for state Integrated Eligbility Projects",
"Offering": "Integrated Eligibility",
 "Program":"AU",
 "Category":"EE",
  "Needed by":"19/07/2024", (CONVERTED INTO DATE FORMAT USING MOMENT)
  "questions": [
            {
                "question": "Question  1",-> R8 C2
                "answers": [
                    {
                        "answer": "Response by New Mexico",-> R9 C2
                        "state": "NM" -> R9 C1
                    },
                    {
                        "answer": "Response by Wyoming", ->R10 C2
                        "state": "WY" -> R10 C1
                    },
					{
                        "answer": "Response by Wyoming", ->R11 C2
                        "state": "WY" -> R11 C1
                    }
                ]
            },
            {
				"question": "Question  2", -> R8 C3
                "answers": [
                    {
                        "answer": "Response by Ohio", -> R9 C3
                        "state": "OH"-> R9 C1
                    },
					{
                        "answer": "Response by Wyoming", ->R10 C3
                        "state": "WY" -> R10 C1
                    },
					{
                        "answer": "Response by Wyoming", ->R11 C4
                        "state": "WY" -> R11 C1
                    }
                ]
            },
            {
                "question": "Question  3",
                "description": "Question  3 desc",
                "answers": [
                    {
                        "answer": "Response by Virginia",  -> R9 C4
                        "state": "VA"  -> R9 C1
                    },
                    {
                        "answer": "Response by Wyoming", ->R10 C4
                        "state": "WY" -> R10 C1
                    },
					{
                        "answer": "Response by Wyoming", ->R11 C4
                        "state": "WY" -> R11 C1
                    }
                ]
            }
		]

}





import React, { useRef, useState } from 'react';
import { Lift } from '@d-lift/core';

import {
    SimpleFileUpload,
    Label,
    Button,
    Group,
    Section,
    ListItem,
    List,
} from '@d-lift/uxcomponents';
import * as XLSX from 'xlsx';
import moment from 'moment';
import ConstantKeys from '@/Constants/ConstantKeys';
import webService from '@/Services/WebService';

const ExcelUpload = () => {
    const [status, setStatus] = useState('initial');
    const [uploadedFiles, setUploadedFiles] = useState([]);
    const fileInputRef = useRef(null);
    const [selectedFile, setSelectedFile] = useState(null);

    const handleFileChange = (e) => {
        if (Array.isArray(e) && e.length > 0) {
            const newFile = e[0];
            setSelectedFile(newFile);
            handleUpload(newFile);
        } else {
            console.log('No file selected');
        }
    };

    const handleUpload = async (file) => {
        try {
            if (!file) {
                console.error('No file selected');
                setStatus('fail');
                return;
            }

            console.log('Processing file:', file);
            const reader = new FileReader();
            reader.onload = (event) => {
                const data = new Uint8Array(event.target.result);
                const workbook = XLSX.read(data, { type: 'array' });
                const firstSheetName = workbook.SheetNames[0];
                const worksheet = workbook.Sheets[firstSheetName];
                const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: '' });

                const validationResult = validateData(jsonData);
                if (validationResult.isValid) {
                    convertToJSONAndUpload(jsonData);
                } else {
                    console.log('validationResult:', validationResult);
                    console.log('errors:', validationResult.errors);

                    Lift.Application.Notification.error(`${validationResult.errors}`);
                    setStatus('fail');
                }
            };
            reader.readAsArrayBuffer(file);

            setUploadedFiles((prevFiles) => [
                ...prevFiles,
                {
                    id: Math.random() * 100 + 1,
                    name: file.name,
                },
            ]);

            setStatus('success');
        } catch (error) {
            console.error(error);
            setStatus('fail');
        }
    };

    const validateData = (data) => {
        let isValid = true;
        let errors = [];

        const headers = data.map((row) => row[0] && row[0].toLowerCase());
        const requiredHeaders = ['title', 'offering', 'needed by', 'state'];

        // Check mandatory headers
        requiredHeaders.forEach((header) => {
            if (!headers.includes(header)) {
                isValid = false;
                errors.push(`Missing mandatory header: ${header}`);
            }
        });

        // Map the indexes of required headers
        const headerIndexes = {
            title: headers.indexOf('title'),
            offering: headers.indexOf('offering'),
            neededBy: headers.indexOf('needed by'),
            state: headers.indexOf('state'),
        };

        // Validate each mandatory field
        Object.keys(headerIndexes).forEach((key) => {
            const rowIndex = headerIndexes[key];
            const fieldValue = data[rowIndex][1]; // Value in the second column

            if (fieldValue === '' || fieldValue === undefined) {
                isValid = false;
                errors.push(`Error in row ${rowIndex + 1}: ${key} is a mandatory field`);
            }

            // Specific validation for 'needed by' field
            // if (key === 'neededBy') {
            //     const dateValue = fieldValue;
            //     let date;

            //     // Check if the date is in Excel date format (numeric)
            //     if (typeof dateValue === 'number') {
            //         date = moment('1900-01-01').add(dateValue - 2, 'days'); // Excel date system
            //     } else {
            //         date = moment(dateValue, 'MM/DD/YYYY', true);
            //     }

            //     if (!date.isValid() || date.isBefore(moment(), 'day')) {
            //         isValid = false;
            //         errors.push(
            //             `Error in row ${
            //                 rowIndex + 1
            //             }: ${key} cannot be in the past or an invalid date`,
            //         );
            //     }
            // }
        });

        return { isValid, errors };
    };

    const convertToJSONAndUpload = (data) => {
        const jsonData = {};

        // Extract the main fields
        data.forEach((row) => {
            const header = row[0];
            const value = row[1];

            if (header) {
                if (header.toLowerCase() === 'needed by') {
                    jsonData['Needed by'] = moment('1900-01-01')
                        .add(value - 2, 'days')
                        .format('DD/MM/YYYY');
                } else {
                    jsonData[header] = value;
                }
            }
        });

        // Extract the questions and responses
        const questionStartRow = 7;
        const questions = [];
        let currentQuestion = null;

        for (let i = questionStartRow; i < data.length; i++) {
            const row = data[i];
            const header = row[0];
            const value = row[1];

            if (header && value) {
                if (
                    !currentQuestion ||
                    !currentQuestion.question ||
                    !header.toLowerCase().startsWith('question')
                ) {
                    currentQuestion = { question: header, responses: [] };
                    questions.push(currentQuestion);
                } else {
                    currentQuestion.responses.push({ response: value, state: header });
                }
            }
        }

        jsonData['Questions'] = questions;

        console.log('jsonData:', jsonData);
        // callAPIToInsertData(jsonData);
    };

    const callAPIToInsertData = async (data) => {
        try {
            Lift.spinner.show();
            //------- will need to convert "needed by" field value before service call.---------

            // const requestFromExcel = {
            //     ...data,data.needed by = moment('1900-01-01').add(dateValue - 2, 'days');
            // };

            const response = await webService.createRFIRequest({
                requestBody: data,
            });

            if (response.message.code === ConstantKeys.RESPONSE_CODES.success) {
                Lift.Application.Notification.success(
                    ConstantKeys.NOTIFICATION.MSG.SUCCESS.EXCEL_UPLOAD_SUCCESS,
                );
            }
        } catch (error) {
            Lift.Application.Notification.error(
                ConstantKeys.NOTIFICATION.MSG.ERR.EXCEL_UPLOAD_FAILURE,
            );
        } finally {
            Lift.spinner.hide();
        }
    };

    return (
        <>
            <div className="ux-rfi-attachment-container ux-rfi-green-border">
                <Group width="6,6">
                    <Label
                        className="mb-0"
                        model="AddAttachments.fileList"
                        labelKey="attachments"></Label>
                    
                </Group>

                <Section className="pt-4">
                    <List>
                        {uploadedFiles.length > 0 ? (
                            uploadedFiles.map((file) => (
                                <ListItem key={file.id} iconKey={null}>
                                    {file.name}
                                </ListItem>
                            ))
                        ) : (
                            <ListItem iconKey={null} labelKey="empty_err"></ListItem>
                        )}
                    </List>
                    <SimpleFileUpload
                        model="AddAttachments.uploadedFiles"
                        allowDragAndDrop={false}
                        inputDisplayType="custom"
                        ref={fileInputRef}
                        style={{ display: 'none' }}
                        onDropAccepted={handleFileChange}
                        accept=".xls,.xlsx">
                        <Button
                            size="small"
                            click={() => fileInputRef.current && fileInputRef.current.click()}
                            className="ux-rfi-green-button"
                            preIconClass="fa fa-plus"
                            labelKey="add_attachment"></Button>
                    </SimpleFileUpload>
                </Section>
            </div>
        </>
    );
};

export default ExcelUpload;
Editor is loading...
Leave a Comment