Untitled
unknown
plain_text
a year ago
12 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. from row 8, the data check till what row and col we have data, then update the code for json to store that as well. 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)
THEN FROM ROW 8 onwards, take the data horizontally from R8 C2 onwards. and keep them as
"Question":[
{"Question 1: Solution/Project Name":[{ "response":"ARIES", "state":"Arkansas"},{ "response":"Calheers/BenefitsCAL", "state":"California"}]
}]
}
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 });
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 = data.map((row) => {
const rowData = {};
rowData['header'] = row[0];
rowData['value'] = row[1];
return rowData;
});
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>
<a
href="/images/sample-format.xlsx"
download="sample-format.xlsx"
style={{ textDecoration: 'none' }}>
<Button
size="small"
className="ux-rfi-green-button float-right"
labelKey="download_sample"
postIconClass="fa fa-download"></Button>
</a>
</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