.png)
Posts
Create xlsx using Javascript
function csvToXlsx(sheetsData, filename = "export") {
console.log("Starting CSV to XLSX conversion...");
console.log("Number of sheets:", sheetsData.length);
const files = {
"[Content_Types].xml": null,
"_rels/.rels": null,
"xl/workbook.xml": null,
"xl/_rels/workbook.xml.rels": null,
"xl/sharedStrings.xml": null
};
let allStrings = new Map();
let stringIndex = 0;
let sheetsXML = "";
let sheetRelations = "";
let sheetCount = 1;
sheetsData.forEach((sheetData, sheetIdx) => {
const sheetName = sheetData.name || `Sheet${sheetIdx + 1}`;
const csvContent = sheetData.data;
console.log(`Processing sheet: ${sheetName}`);
const rows = csvContent.trim().split("\n").map(row =>
row.split(",").map(cell => cell.trim())
);
rows.forEach(row => {
row.forEach(cell => {
if (!allStrings.has(cell)) {
allStrings.set(cell, stringIndex++);
}
});
});
let cellsXML = "";
rows.forEach((row, rIdx) => {
const rowNum = rIdx + 1;
let rowCells = "";
row.forEach((cell, cIdx) => {
const col = String.fromCharCode(65 + cIdx);
const cellRef = col + rowNum;
const strIdx = allStrings.get(cell);
rowCells += `<c r="${cellRef}" t="s"><v>${strIdx}</v></c>`;
});
cellsXML += `<row r="${rowNum}">${rowCells}</row>`;
});
const sheetXML = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>${cellsXML}</sheetData></worksheet>`;
files[`xl/worksheets/sheet${sheetCount}.xml`] = sheetXML;
sheetsXML += `<sheet name="${sheetName}" sheetId="${sheetCount}" r:id="rId${sheetCount + 1}"/>`;
sheetRelations += `<Relationship Id="rId${sheetCount + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${sheetCount}.xml"/>`;
sheetCount++;
});
console.log("Total unique strings:", allStrings.size);
let siXML = "";
allStrings.forEach((idx, str) => {
siXML += `<si><t>${str.replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">")}</t></si>`;
});
files["xl/sharedStrings.xml"] = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="${allStrings.size}" uniqueCount="${allStrings.size}">${siXML}</sst>`;
files["xl/workbook.xml"] = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets>${sheetsXML}</sheets></workbook>`;
files["xl/_rels/workbook.xml.rels"] = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">${sheetRelations}<Relationship Id="rId${sheetCount}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>`;
files["[Content_Types].xml"] = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>`;
for (let i = 1; i < sheetCount; i++) {
files["[Content_Types].xml"] += `<Override PartName="/xl/worksheets/sheet${i}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`;
}
files["[Content_Types].xml"] += `<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/></Types>`;
files["_rels/.rels"] = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>`;
console.log("XML files created");
const createZipWithCentralDirectory = () => {
const encoder = new TextEncoder();
let zipData = [];
let centralDirectory = [];
let offset = 0;
Object.keys(files).forEach(name => {
const content = files[name];
const bytes = encoder.encode(content);
const crc32 = calculateCRC32(bytes);
const nameBytes = encoder.encode(name);
// Local file header
const localHeader = new Uint8Array(30 + nameBytes.length);
const view = new DataView(localHeader.buffer);
view.setUint32(0, 0x04034b50, true); // signature
view.setUint16(4, 20, true); // version needed
view.setUint16(6, 0, true); // flags
view.setUint16(8, 0, true); // compression method (0 = stored)
view.setUint32(10, 0, true); // mod time
view.setUint32(14, crc32, true);
view.setUint32(18, bytes.length, true);
view.setUint32(22, bytes.length, true);
view.setUint16(26, nameBytes.length, true);
view.setUint16(28, 0, true); // extra field length
localHeader.set(nameBytes, 30);
zipData.push(localHeader);
zipData.push(bytes);
// Central directory record
const centralRecord = new Uint8Array(46 + nameBytes.length);
const cdView = new DataView(centralRecord.buffer);
cdView.setUint32(0, 0x02014b50, true); // central directory signature
cdView.setUint16(4, 20, true); // version made by
cdView.setUint16(6, 20, true); // version needed
cdView.setUint16(8, 0, true); // flags
cdView.setUint16(10, 0, true); // compression method
cdView.setUint32(12, 0, true); // mod time
cdView.setUint32(16, crc32, true);
cdView.setUint32(20, bytes.length, true);
cdView.setUint32(24, bytes.length, true);
cdView.setUint16(28, nameBytes.length, true);
cdView.setUint16(30, 0, true); // extra field length
cdView.setUint16(32, 0, true); // comment length
cdView.setUint16(34, 0, true); // disk number
cdView.setUint16(36, 0, true); // internal attributes
cdView.setUint32(38, 0, true); // external attributes
cdView.setUint32(42, offset, true); // relative offset of local header
centralRecord.set(nameBytes, 46);
centralDirectory.push(centralRecord);
offset += localHeader.length + bytes.length;
});
// Combine all data and central directory
let totalSize = offset;
for (let cd of centralDirectory) {
totalSize += cd.length;
}
// End of central directory
const endCentralDirectory = new Uint8Array(22);
const endView = new DataView(endCentralDirectory.buffer);
endView.setUint32(0, 0x06054b50, true); // signature
endView.setUint16(4, 0, true); // disk number
endView.setUint16(6, 0, true); // disk with central directory
endView.setUint16(8, centralDirectory.length, true); // number of entries on disk
endView.setUint16(10, centralDirectory.length, true); // total number of entries
let cdSize = 0;
for (let cd of centralDirectory) {
cdSize += cd.length;
}
endView.setUint32(12, cdSize, true); // size of central directory
endView.setUint32(16, offset, true); // offset of central directory
endView.setUint16(20, 0, true); // comment length
// Assemble final ZIP
const finalZip = new Uint8Array(totalSize + endCentralDirectory.length);
let pos = 0;
for (let data of zipData) {
finalZip.set(data, pos);
pos += data.length;
}
for (let cd of centralDirectory) {
finalZip.set(cd, pos);
pos += cd.length;
}
finalZip.set(endCentralDirectory, pos);
return finalZip;
};
const calculateCRC32 = (bytes) => {
const table = [];
for (let i = 0; i < 256; i++) {
let c = i;
for (let k = 0; k < 8; k++) {
c = (c & 1) ? (0xedb88320 ^ (c >>> 1)) : (c >>> 1);
}
table[i] = c;
}
let crc = 0xffffffff;
for (let i = 0; i < bytes.length; i++) {
crc = table[(crc ^ bytes[i]) & 0xff] ^ (crc >>> 8);
}
return (crc ^ 0xffffffff) >>> 0;
};
try {
const zipData = createZipWithCentralDirectory();
console.log("ZIP created with central directory, size:", zipData.length);
const blob = new Blob([zipData], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
});
const url = URL.createObjectURL(blob);
const link = document.createElement("a");
link.href = url;
link.download = filename + ".xlsx";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(url);
console.log("File downloaded successfully:", filename + ".xlsx");
} catch (error) {
console.error("Error creating XLSX:", error);
}
}
// TEST
const sheetsData = [
{
name: "Employees",
data: `Name,Age,Email,Department,Salary
John Doe,28,john@example.com,Engineering,75000
Jane Smith,34,jane@example.com,Marketing,65000
Michael Johnson,45,michael@example.com,Sales,70000`
},
{
name: "Projects",
data: `ProjectID,ProjectName,Manager,Budget,Status
P001,Website Redesign,John Doe,50000,In Progress
P002,Mobile App,Jane Smith,75000,Planning
P003,API Integration,Michael Johnson,30000,Completed`
}
];
csvToXlsx(sheetsData, "report");