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