Untitled
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