Skip to content

Instantly share code, notes, and snippets.

@iso2022jp
Last active July 30, 2022 14:26
Show Gist options
  • Save iso2022jp/721df3095f4df512bfe2327503ea1119 to your computer and use it in GitHub Desktop.
Save iso2022jp/721df3095f4df512bfe2327503ea1119 to your computer and use it in GitHub Desktop.
Download as Excel
const download = (blob, filename, type = blob.type || 'application/octet-stream') => {
const a = document.createElement('a')
a.href = window.URL.createObjectURL(blob)
a.download = filename
a.type = type
a.click()
window.URL.revokeObjectURL(a.href)
}
// https://pkware.cachefly.net/webdocs/casestudies/APPNOTE.TXT
const zip = async entries => {
// entry: {name, blob, modified)
const makeCrc32Table = () => {
const table = new Uint32Array(256)
const e = 0xedb88320
for (let i = 0; i < 256; ++i) {
let u = i
for (let j = 0; j < 8; ++j) {
u = u & 0x1 ? (u >>> 1) ^ e : u >>> 1
}
table[i] = u
}
return table
}
const computeCrc32 = async (blob, table) => {
let value = 0xffffffff
for (const b of new Uint8Array(await blob.arrayBuffer())) {
value = (value >>> 8) ^ table[(b ^ value) & 0xff]
}
return ~value
}
const SIGNATURE_LOCAL_FILE_HEADER = 0x04034b50
const SIGNATURE_CENTRAL_FILE_HEADER = 0x02014b50
const SIGNATURE_END_OF_CENTRAL_DIRECTORY_HEADER = 0x06054b50
const SIGNATURE_DATA_DESCRIPTOR = 0x08074b50 // commonly adopted
const EXTENDED_TIMESTAMP_HEADER_ID = 0x5455
const EXTENDED_TIMESTAMP_FLAG_MODIFICATION_TIME = 1 << 0
const NTFS_HEADER_ID = 0x000a
const NTFS_TAG1 = 0x0001
const VERSION_DEFLATE = 20
const FILE_FAT = 0 << 8
const FILE_ATTRIBUTE_ARCHIVE = 0x20
const METHOD_STORED = 0
const FLAG_DATA_DESCRIPTOR = 1 << 3 // Streaming
const FLAG_UTF8 = 1 << 11 // Language encoding flag (EFS)
const toDosDate = date => (date.getFullYear() - 1980) << 9 | (date.getMonth() + 1) << 5 | date.getDate()
const toDosTime = date => date.getHours() << 11 | date.getMinutes() << 5 | date.getSeconds() >> 1
const toFileTime = date => (BigInt(date.getTime()) + 11644473600000n) * 10n
const packTimestampExtra = modified => {
const extra = new DataView(new ArrayBuffer(9 + 36))
// Extended Timestamp Extra Field (0x5455):
// 7-zip follows, Windows zip ignores
extra.setUint16(0, EXTENDED_TIMESTAMP_HEADER_ID, true) // Tag for this "extra" block type
extra.setUint16(2, 5, true) // Size of the total "extra" block
extra.setUint8(4, EXTENDED_TIMESTAMP_FLAG_MODIFICATION_TIME) // info bits
extra.setUint32(5, Math.trunc(modified.getTime() / 1000), true) // time of last modification
// NTFS Extra Field (0x000a):
extra.setUint16(9, NTFS_HEADER_ID, true) // tag for this extra block type ("UT")
extra.setUint16(11, 32, true) // total data size for this block
// zero x 4: Reserved for future use
extra.setUint16(15, NTFS_TAG1, true) // attribute tag value #1
extra.setUint16(17, 24, true) // Size of attribute #1, in bytes
extra.setBigUint64(19, toFileTime(modified), true) // File last modification time
extra.setBigUint64(27, toFileTime(modified), true) // File last access time
extra.setBigUint64(35, toFileTime(modified), true) // File creation time
return extra
}
const computeLocalSize = ({name, blob}) => 30 + new Blob([name]).size + 9 + 36 + blob.size + 16
// local file header + Extended Timestamp + file data + data descriptor
const packLocal = ({name, blob, modified}, crc32) => {
const binaryName = new Blob([name])
const extra = packTimestampExtra(modified)
const header = new DataView(new ArrayBuffer(30))
header.setUint32(0, SIGNATURE_LOCAL_FILE_HEADER, true) // local file header signature
header.setUint16(4, VERSION_DEFLATE, true) // version needed to extract
header.setUint16(6, FLAG_UTF8 | FLAG_DATA_DESCRIPTOR, true) // general purpose bit flag
header.setUint16(8, METHOD_STORED, true) // compression method
header.setUint16(10, toDosTime(modified), true) // last mod file time
header.setUint16(12, toDosDate(modified), true) // last mod file date
// zero x 4: crc-32 (data descriptor)
// zero x 4: compressed size (data descriptor)
// zero x 4: uncompressed size (data descriptor)
header.setUint16(26, binaryName.size, true) // file name length
header.setUint16(28, extra.byteLength, true) // extra field length
// TODO: compress
const descriptor = new DataView(new ArrayBuffer(16))
descriptor.setUint32(0, SIGNATURE_DATA_DESCRIPTOR, true) // implementors SHOULD include the signature value marking the data descriptor record
descriptor.setUint32(4, crc32, true) // crc-32
descriptor.setUint32(8, blob.size, true) // compressed size
descriptor.setUint32(12, blob.size, true) // uncompressed size
return new Blob([header, binaryName, extra, blob, descriptor])
}
// central directory header
const packDirectory = ({name, blob, modified}, crc32, offset) => {
const binaryName = new Blob([name])
const extra = packTimestampExtra(modified)
const header = new DataView(new ArrayBuffer(46))
header.setUint32(0, SIGNATURE_CENTRAL_FILE_HEADER, true) // central file header signature
header.setUint16(4, FILE_FAT | VERSION_DEFLATE, true) // version made by
header.setUint16(6, VERSION_DEFLATE, true) // version needed to extract
header.setUint16(8, FLAG_UTF8 | FLAG_DATA_DESCRIPTOR, true) // general purpose bit flag
header.setUint16(10, METHOD_STORED, true) // compression method
header.setUint16(12, toDosTime(modified), true) // last mod file time
header.setUint16(14, toDosDate(modified), true) // last mod file date
header.setUint32(16, crc32, true) // crc-32
header.setUint32(20, blob.size, true) // compressedSize
header.setUint32(24, blob.size, true) // uncompressed size
header.setUint16(28, binaryName.size, true) // file name length
header.setUint16(30, extra.byteLength, true) // extra field length
// zero x 2: file comment length (none)
// zero x 2: disk number start (#0)
// zero x 2: internal file attributes (always binary, no-mainframe)
header.setUint32(38, FILE_ATTRIBUTE_ARCHIVE, true) // external file attributes
header.setUint32(42, offset, true) // relative offset of local header
return new Blob([header, binaryName, extra])
}
// End of central directory record
const packTrailer = (centrals, offset) => {
const trailer = new DataView(new ArrayBuffer(22))
trailer.setUint32(0, SIGNATURE_END_OF_CENTRAL_DIRECTORY_HEADER, true) // end of central dir signature
// zero x 2: number of this disk (#0)
// zero x 2: number of the disk with the start of the central directory (#0)
trailer.setUint16(8, centrals.length, true) // number of items in the central directory on this disk
trailer.setUint16(10, centrals.length, true) // number of items in the central directory
trailer.setUint32(12, centrals.reduce((size, blob) => size + blob.size, 0), true) // size of the central directory
trailer.setUint32(16, offset, true) // offset of start of central directory with respect to the starting disk number
// zero x 2: .ZIP file comment length (none)
return trailer
}
const encodeEntry = async (entry, offset, table) => {
const checksum = await computeCrc32(entry.blob, table)
const local = packLocal(entry, checksum)
const central = packDirectory(entry, checksum, offset)
return {local, central}
}
// go
const table = makeCrc32Table()
// local file header + content
const localSizes = entries.map(computeLocalSize)
// accumulate
const offsets = localSizes.map((offset => a => offset += a)(0))
const promises = entries.map((entry, i) => encodeEntry(entry, offsets[i - 1] || 0, table))
return await Promise.all(promises).then(pairs => {
const locals = pairs.map(p => p.local)
const centrals = pairs.map(p => p.central)
const trailer = packTrailer(centrals, offsets.at(-1))
return new Blob([...locals, ...centrals, trailer], {type: 'application/zip'})
})
}
const xlsx = async rows => {
const contentTypes = `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Override PartName="/book.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/sheet.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/strings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>`
const rels = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="book.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"/>
</Relationships>`
const book = `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets>
<sheet name="Sheet" sheetId="1" r:id="rId1"/>
</sheets>
</workbook>`
const bookRels = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="sheet.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/>
<Relationship Id="rId2" Target="strings.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"/>
<Relationship Id="rId3" Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"/>
</Relationships>`
// Style 1: wordwrap
const styles = `<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="1">
<font />
</fonts>
<fills count="1">
<fill />
</fills>
<borders count="1">
<border />
</borders>
<cellStyleXfs count="1">
<xf />
</cellStyleXfs
><cellXfs count="2">
<xf />
<xf><alignment wrapText="true"/></xf>
</cellXfs>
</styleSheet>`
const interns = new Map()
const data = []
for (const row of rows) {
data.push('<row>')
for (const cell of row) {
if (Number.isInteger(cell) || Math.trunc(cell) === parseFloat(cell)) {
// native integer
data.push(`<c t="n"><v>${Math.trunc(cell)}</v></c>`)
continue
}
// string
if (!cell) {
data.push(`<c></c>`)
continue
}
const text = String(cell)
let index
if (interns.has(text)) {
index = interns.get(text)
} else {
index = interns.size
interns.set(text, index)
}
if (text.includes('\n')) {
// wordwrap
data.push(`<c t="s" s="1"><v>${index}</v></c>`)
} else {
data.push(`<c t="s"><v>${index}</v></c>`)
}
}
data.push('</row>')
}
const sheet = `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetData>
${data.join('')}
</sheetData>
</worksheet>`
const sst = []
for (const [text, index] of interns) {
sst.push(`<si><t>${text.replace(/[\x00-\x20<>'&]/g, i => `&#${i.charCodeAt(0)};`)}</t></si>`)
}
const strings = `<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" uniqueCount="${interns.size}">
${sst.join('')}
</sst>`
const modified = new Date()
return await zip([
{name: '[Content_Types].xml', blob: new Blob([contentTypes]), modified},
{name: '_rels/.rels', blob: new Blob([rels]), modified},
{name: 'book.xml', blob: new Blob([book]), modified},
{name: '_rels/book.xml.rels', blob: new Blob([bookRels]), modified},
{name: 'styles.xml', blob: new Blob([styles]), modified},
{name: 'sheet.xml', blob: new Blob([sheet]), modified},
{name: 'strings.xml', blob: new Blob([strings]), modified},
])
}
document.querySelector('#download').addEventListener('click', async e => {
const table = document.querySelector('#table')
const rows = Array.from(table.rows, r => Array.from(r.cells, c => c.textContent))
const excel = await xlsx(rows)
download(excel, 'table.xlsx')
})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment