Skip to content

Instantly share code, notes, and snippets.

@keiranlovett

keiranlovett/make-image-table.js

Created April 21, 2026 23:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save keiranlovett/0daa7f47ef9d16e10589dc94cb7aeb6e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save keiranlovett/0daa7f47ef9d16e10589dc94cb7aeb6e to your computer and use it in GitHub Desktop.
Download ZIP
Create Excel Table of Images
Raw
make-image-table.js
/**
* make-image-table.js
*
* Intall Requirements:
* npm install exceljs sharp
*
* Usage:
* node make-image-table.js /path/to/folder [out.xlsx]
* [--suffixes=_leftTop,_rightBottom]
* [--no-strip]
* [--aspect=1:1]
* [--fit=inside]
* [--box=150]
* [--box=200x120]
*
* Examples:
* node make-image-table.js "./images"
* node make-image-table.js "./images" "images.xlsx" --aspect=16:9 --fit=contain
* node make-image-table.js "./images" "images.xlsx" --aspect=1:1 --box=180
* node make-image-table.js "./images" "images.xlsx" --aspect=original --box=200x150
*
*/
const fs = require('fs').promises;
const path = require('path');
const ExcelJS = require('exceljs');
const sharp = require('sharp');
const IMAGE_EXTS = new Set([
'.jpg', '.jpeg', '.png', '.gif', '.webp', '.bmp', '.tif', '.tiff', '.svg', '.avif'
]);
function cleanName(filename, options = {}) {
const { stripAfterUnderscore = true, suffixes = [], caseInsensitive = false } = options;
const ext = path.extname(filename);
let base = path.basename(filename, ext);
if (suffixes && suffixes.length > 0) {
if (caseInsensitive) {
const lower = base.toLowerCase();
for (const s of suffixes) {
const sLow = s.toLowerCase();
if (lower.endsWith(sLow)) {
return base.slice(0, base.length - s.length);
}
}
} else {
for (const s of suffixes) {
if (base.endsWith(s)) {
return base.slice(0, base.length - s.length);
}
}
}
}
if (stripAfterUnderscore) {
const idx = base.lastIndexOf('_');
if (idx > 0) return base.slice(0, idx);
}
return base;
}
function parseAspect(aspectArg) {
if (!aspectArg || aspectArg.toLowerCase() === 'original') {
return null; // preserve original aspect ratio
}
const match = /^(\d+(?:\.\d+)?):(\d+(?:\.\d+)?)$/.exec(aspectArg.trim());
if (!match) {
throw new Error(`Broken --aspect value "${aspectArg}". Use forms like 1:1, 16:9, 4:3, or original.`);
}
const w = Number(match[1]);
const h = Number(match[2]);
if (!Number.isFinite(w) || !Number.isFinite(h) || w <= 0 || h <= 0) {
throw new Error(`Broken --aspect value "${aspectArg}".`);
}
return w / h;
}
function parseBox(boxArg) {
if (!boxArg) {
return { width: 150, height: 150 };
}
const square = /^(\d+)$/.exec(boxArg.trim());
if (square) {
const s = Number(square[1]);
return { width: s, height: s };
}
const rect = /^(\d+)x(\d+)$/i.exec(boxArg.trim());
if (rect) {
return { width: Number(rect[1]), height: Number(rect[2]) };
}
throw new Error(`Broken --box value "${boxArg}". Use 150 or 200x120.`);
}
function clampPositiveInt(n, fallback = 1) {
const v = Math.round(Number(n));
return Number.isFinite(v) && v > 0 ? v : fallback;
}
function computeTargetDimensions(srcWidth, srcHeight, boxWidth, boxHeight, aspectRatio = null, fit = 'inside') {
let targetWidth;
let targetHeight;
if (aspectRatio) {
// Build the largest rectangle of the requested aspect ratio inside the box.
const boxRatio = boxWidth / boxHeight;
if (aspectRatio >= boxRatio) {
targetWidth = boxWidth;
targetHeight = boxWidth / aspectRatio;
} else {
targetHeight = boxHeight;
targetWidth = boxHeight * aspectRatio;
}
} else {
// Preserve original aspect ratio by default.
const srcRatio = srcWidth / srcHeight;
const boxRatio = boxWidth / boxHeight;
if (srcRatio >= boxRatio) {
targetWidth = boxWidth;
targetHeight = boxWidth / srcRatio;
} else {
targetHeight = boxHeight;
targetWidth = boxHeight * srcRatio;
}
}
targetWidth = clampPositiveInt(targetWidth);
targetHeight = clampPositiveInt(targetHeight);
// fit mode should decide how sharp resizes into that computed frame
if (!['inside', 'contain', 'cover', 'fill', 'outside'].includes(fit)) {
throw new Error(`Invalid --fit value "${fit}". Use inside, contain, cover, fill, or outside.`);
}
return {
width: targetWidth,
height: targetHeight,
fit
};
}
async function isReadableImage(fullPath) {
try {
const meta = await sharp(fullPath).metadata();
return !!meta && !!meta.format;
} catch {
return false;
}
}
async function getImageSize(fullPath) {
const meta = await sharp(fullPath).metadata();
if (!meta || !meta.width || !meta.height) {
throw new Error('Could not read dimensions');
}
return {
width: meta.width,
height: meta.height
};
}
async function makeThumbnailBuffer(fullPath, outputSize) {
return sharp(fullPath, { failOn: 'none' })
.rotate()
.resize({
width: outputSize.width,
height: outputSize.height,
fit: outputSize.fit,
withoutEnlargement: true
})
.png()
.toBuffer();
}
async function makeXlsx(folder, outFile = 'images.xlsx', options = {}) {
const {
box = { width: 150, height: 150 },
aspectRatio = null,
fit = 'inside',
cleanOptions = {}
} = options;
const entries = await fs.readdir(folder, { withFileTypes: true });
const candidateFiles = [];
console.log('--- Scan results ---');
for (const e of entries) {
const ext = path.extname(e.name).toLowerCase();
if (!IMAGE_EXTS.has(ext)) {
console.log(`Skipping unsupported extension: ${e.name} (${ext || 'no extension'})`);
continue;
}
candidateFiles.push(e.name);
console.log(`Accepted: ${e.name}`);
}
console.log('--------------------');
candidateFiles.sort((a, b) => a.localeCompare(b, undefined, { sensitivity: 'base' }));
const workbook = new ExcelJS.Workbook();
workbook.creator = 'makeImageTableXlsx';
workbook.created = new Date();
const ws = workbook.addWorksheet('Images');
ws.columns = [
{ header: 'Name', key: 'name', width: 40 },
{ header: 'Created', key: 'created', width: 25 },
{ header: 'Preview', key: 'preview', width: 30 }
];
ws.properties.defaultRowHeight = 90;
let rowIndex = 2;
let addedCount = 0;
let skippedCount = 0;
console.log(`Found candidate image files: ${candidateFiles.length}. (${entries.length} total directory entries)`);
for (const file of candidateFiles) {
const full = path.join(folder, file);
try {
const readable = await isReadableImage(full);
if (!readable) {
skippedCount++;
console.warn(`Skipping unsupported/corrupt image: ${file}`);
continue;
}
const stats = await fs.stat(full);
const created = stats.birthtime && stats.birthtime.getTime() ? stats.birthtime : stats.mtime;
const createdStr = created.toLocaleString();
const displayName = cleanName(file, cleanOptions);
const srcSize = await getImageSize(full);
const outputSize = computeTargetDimensions(
srcSize.width,
srcSize.height,
box.width,
box.height,
aspectRatio,
fit
);
ws.addRow({
name: displayName,
created: createdStr
});
const thumbBuf = await makeThumbnailBuffer(full, outputSize);
const imageId = workbook.addImage({
buffer: thumbBuf,
extension: 'png'
});
ws.addImage(imageId, {
tl: { col: 2, row: rowIndex - 1 },
ext: {
width: outputSize.width,
height: outputSize.height
}
});
// Rough Excel row height conversion from pixels to points.
ws.getRow(rowIndex).height = Math.max(30, Math.round(outputSize.height * 0.75));
rowIndex++;
addedCount++;
} catch (err) {
skippedCount++;
console.warn(`Skipping "${file}": ${err.message || err}`);
}
}
const outPath = path.resolve(folder, outFile);
await workbook.xlsx.writeFile(outPath);
console.log(`Wrote workbook: ${outPath}`);
console.log(`Added: ${addedCount}, Skipped: ${skippedCount}`);
}
async function main() {
const argv = process.argv.slice(2);
if (argv.length === 0) {
console.log(
'Usage: node make-image-table.js /path/to/folder [out.xlsx] ' +
'[--suffixes=_leftTop,_rightBottom] [--no-strip] [--ci] ' +
'[--aspect=1:1|16:9|original] [--fit=inside|contain|cover|fill|outside] [--box=150|200x120]'
);
process.exit(1);
}
const folder = argv[0];
const outFile = argv[1] && !argv[1].startsWith('--') ? argv[1] : 'images.xlsx';
const suffixArg = argv.find(a => a.startsWith('--suffixes='));
const suffixes = suffixArg ? suffixArg.split('=')[1].split(',').filter(Boolean) : [];
const noStrip = argv.includes('--no-strip');
const caseInsensitive = argv.includes('--ci') || argv.includes('--case-insensitive');
const aspectArg = argv.find(a => a.startsWith('--aspect='))?.split('=')[1] ?? 'original';
const fitArg = argv.find(a => a.startsWith('--fit='))?.split('=')[1] ?? 'inside';
const boxArg = argv.find(a => a.startsWith('--box='))?.split('=')[1] ?? null;
const cleanOptions = {
stripAfterUnderscore: !noStrip,
suffixes,
caseInsensitive
};
const aspectRatio = parseAspect(aspectArg);
const box = parseBox(boxArg);
try {
await makeXlsx(folder, outFile, {
box,
aspectRatio,
fit: fitArg,
cleanOptions
});
} catch (err) {
console.error('Error:', err.message || err);
process.exit(2);
}
}
if (require.main === module) {
main();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment