ReactJS中Excel转JSON时时间格式变为数字的修复方法咨询

Hey there! Let's break down what's going on here and how to get your original time format back.

Why This Happens #

Excel stores times (and dates) as serial numbers:

• The integer part represents the number of days since January 1, 1900 (Excel has a historical leap year bug, so the actual baseline is 1899-12-30, but we'll handle that later if needed)
• The decimal part is the fraction of a day that's passed (e.g., 0.166666 = 4 hours, since 0.166666 * 24 = 4)

Your parsed data only has decimals, which means your Excel sheet had time-only values (no date component).

Solution 1: Convert Decimal to Human-Readable Time String #

If you need a formatted time like HH:mm:ss (perfect for display or API payloads that expect string times), use this helper function:

function excelTimeDecimalToTime(decimal) {
  // Calculate total seconds from the decimal fraction of a day
  const totalSeconds = decimal * 24 * 60 * 60;
  const hours = Math.floor(totalSeconds / 3600);
  const minutes = Math.floor((totalSeconds % 3600) / 60);
  const seconds = Math.floor(totalSeconds % 60);

  // Add leading zeros for clean formatting (e.g., 4 → "04")
  const padWithZero = num => num.toString().padStart(2, '0');
  return `${padWithZero(hours)}:${padWithZero(minutes)}:${padWithZero(seconds)}`;
}

Apply it to your parsed data in React like this:

// Assume `parsedExcelData` is your original JSON array from the parser
const formattedData = parsedExcelData.map(item => ({
  ...item,
  startTime: excelTimeDecimalToTime(item.startTime),
  returnStartTime: excelTimeDecimalToTime(item.returnStartTime)
}));

Solution 2: Convert to a JavaScript Date Object #

If you need a full Date object (great for further date manipulation or APIs that accept ISO timestamps), use this function:

function excelTimeDecimalToDate(decimal) {
  // Use 1970-01-01 as a base date since there's no date component in your data
  const baseDate = new Date(0);
  // Convert decimal fraction of a day to milliseconds
  const msPerDay = 24 * 60 * 60 * 1000;
  baseDate.setMilliseconds(decimal * msPerDay);
  return baseDate;
}

Usage example:

const dataWithDates = parsedExcelData.map(item => ({
  ...item,
  startTime: excelTimeDecimalToDate(item.startTime),
  returnStartTime: excelTimeDecimalToDate(item.returnStartTime)
}));

Bonus: Handling Full Date + Time Values #

If your Excel data ever includes full date-time entries (with integer + decimal serial numbers), use this function to convert to a proper Date:

function excelSerialToDateTime(serial) {
  // Account for Excel's historical baseline bug
  const excelBaseDate = new Date(Date.UTC(1899, 11, 30));
  const msPerDay = 24 * 60 * 60 * 1000;
  return new Date(excelBaseDate.getTime() + serial * msPerDay);
}

Pick the solution that fits your API's requirements—whether you need a formatted string or a Date object, this should get your time values back to their original form!

内容的提问来源于stack exchange，提问作者ammy