Posted 10 June 2025, 5:10 pm EST
Hi,
When importing Excell spreadsheets I need an EASY way to tell SpreadJS how to convert the given OLE date code and format into text exactly like the date presented into the cell.
Right now I have to convert these two items: the UTC date and the date format (e.g. something like ‘mm/dd/yyy’ or another format that I tap into using cell.formatter()).
The problem is doing it myself is two complicated.
I basically have to:
(1): convert the given format into a JavaScript friendly format (replacing m for months with uppercase m (M) etc… like mm/dd/yyy is converted to MM/dd/yyy using a JSON object as a key value pair object).
(2): parse the given UTC time (?) into a JavaScript format time.
(3): then convert it to a string.
Note the end goal is to take the entire sheet content is converted into a large string with tab separated values.
Given that the SpreadJS presenting the Excel spreadsheet dates in the same format you must have a way of taking Date type data and converting it into string without me having to write a bunch of custom code.
My problem is that our customers will use the SpreadJS component to feed/import data into our system and the sheer permutations or variety of date formats is overwhelming, particularly if we have to account for Excell custom date formats. I just want to grab the value as it appears in the SpreadJS component and NOT have to keep 100+ date formats in my JavaScript code and then convert every cell that is of type Date.
Here is a snippet of how I handle date formats now:
// Get sheet as array of rows
const sheetArr = sheet.getArray(0, 0, sheet.getRowCount(), sheet.getColumnCount(), getFormula);
sheetArr.forEach((row, rowIndex) => {
let formattedRow = row.map((cellValue, colIndex) => {
let cell = sheet.getCell(rowIndex, colIndex);
let format = cell.formatter(); // Get cell format
// Handle date formatting
if (cellValue instanceof Date) {
let { isValidDate, cleanedFormat } = validateIsolateFormat(format);
if (isValidDate) {
return formatDate(cellValue, cleanedFormat); // Apply formatting
} else {
JL("jsLogger").error(`UI ERROR - in chunkRows(): An unexpected error occurred Date format error: ${format} is not valid. Import / Save Sheet action stopped.`);
throw new Error(`Date format error: <strong>${format}</strong> is not valid. Please try a different format in Excel or convert to text.`);
}
}
return cellValue ?? ""; // Handle null/undefined values...this is the case where the value is NOT of type date.
});
// Convert row to string (TSV format)
let rowString = formattedRow.join("\t"); // Convert row to tab-separated valuesGeorge
