Issue when converting from XLS to XLSX #2960

New Issue

Godrules500 · 2023-06-06T13:36:14Z

Godrules500 commented

2023-06-06 13:36:14 +00:00

First-time contributor

When converting an xls file to xlsx, it is dropping things like dropdown columns, which columns are locked (locks the whole file to be honest), styling and all notes are being shown by default instead of hidden.

I'm also trying to just do a save as (keeping thexls) format and it loses the notes, styling, drop down columns, etc.

var workbook = XLSX.readFile('input.xls');
XLSX.writeFile(workbook, 'output.xls');

I've also tried many different variation of properties.

Here is me trying to convert it to xlsx

const workbook = XLSX.readFile("input.xls", {
  bookFiles: true,
  cellStyles: true,
  cellNF: true,
});

const sheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[sheetName];

// Check if the sheet is protected
if (sheet["!protect"]) {
  // Remove protection from the sheet
  delete sheet["!protect"];
  console.log("Sheet protection removed.");
} else {
  console.log("Sheet is not protected.");
}

// Copy the data validation settings from the original worksheet to the new worksheet
const dataValidations = sheet["!dataValidation"];
if (dataValidations) {
  const newWorksheet = XLSX.utils.aoa_to_sheet(
    XLSX.utils.sheet_to_json(sheet, { header: 1 })
  );
  newWorksheet["!dataValidation"] = dataValidations;
  XLSX.utils.book_append_sheet(workbook, newWorksheet, "test1");
} else {
  XLSX.utils.book_append_sheet(workbook, sheet, "test1");
}

workbook.SheetNames.forEach((n) => {
  var ws = workbook.Sheets[n];
  if (!ws) return;
  var ref = XLSX.utils.decode_range(ws["!ref"]);
  for (var R = 0; R <= ref.e.r; ++R)
    for (var C = 0; C <= ref.e.c; ++C) {
      var addr = XLSX.utils.encode_cell({ r: R, c: C });
      if (!ws[addr] || !ws[addr].c) continue;
      var comments = ws[addr].c;
      if (!comments.length) continue;
      console.log(comments);
      ws[addr].c.hidden = true;
    }
});
XLSX.writeFileXLSX(workbook, "output.xlsx", {
  cellStyles: true,
  bookSST: true,
  codepage:true
});

When converting an xls file to xlsx, it is dropping things like dropdown columns, which columns are locked (locks the whole file to be honest), styling and all notes are being shown by default instead of hidden. I'm also trying to just do a save as (keeping thexls) format and it loses the notes, styling, drop down columns, etc. ``` var workbook = XLSX.readFile('input.xls'); XLSX.writeFile(workbook, 'output.xls'); ``` I've also tried many different variation of properties. Here is me trying to convert it to xlsx ``` const workbook = XLSX.readFile("input.xls", { bookFiles: true, cellStyles: true, cellNF: true, }); const sheetName = workbook.SheetNames[0]; const sheet = workbook.Sheets[sheetName]; // Check if the sheet is protected if (sheet["!protect"]) { // Remove protection from the sheet delete sheet["!protect"]; console.log("Sheet protection removed."); } else { console.log("Sheet is not protected."); } // Copy the data validation settings from the original worksheet to the new worksheet const dataValidations = sheet["!dataValidation"]; if (dataValidations) { const newWorksheet = XLSX.utils.aoa_to_sheet( XLSX.utils.sheet_to_json(sheet, { header: 1 }) ); newWorksheet["!dataValidation"] = dataValidations; XLSX.utils.book_append_sheet(workbook, newWorksheet, "test1"); } else { XLSX.utils.book_append_sheet(workbook, sheet, "test1"); } workbook.SheetNames.forEach((n) => { var ws = workbook.Sheets[n]; if (!ws) return; var ref = XLSX.utils.decode_range(ws["!ref"]); for (var R = 0; R <= ref.e.r; ++R) for (var C = 0; C <= ref.e.c; ++C) { var addr = XLSX.utils.encode_cell({ r: R, c: C }); if (!ws[addr] || !ws[addr].c) continue; var comments = ws[addr].c; if (!comments.length) continue; console.log(comments); ws[addr].c.hidden = true; } }); XLSX.writeFileXLSX(workbook, "output.xlsx", { cellStyles: true, bookSST: true, codepage:true }); ```

Sign in to join this conversation.