qoo10-goods-image-export/app.js

const XLSX = require('xlsx');
const axios = require('axios');
const path = require('path');
const url = require('url');
const fs = require('fs').promises;
const { existsSync, mkdirSync } = require('fs');

// 获取文件名从URL
function getFilenameFromUrl(urlString) {
    try {
        const parsed = new URL(urlString);
        let filename = path.basename(parsed.pathname);
        // 如果文件名为空或无效，生成时间戳文件名
        if (!filename || !filename.includes('.')) {
            const timestamp = Date.now();
            filename = `image_${timestamp}.jpg`;
        }
        return filename;
    } catch (error) {
        console.error(`Error parsing URL ${urlString}: ${error.message}`);
        const timestamp = Date.now();
        return `image_${timestamp}.jpg`;
    }
}

// 下载图片
async function downloadImage(imageUrl, outputPath) {
    try {
        const response = await axios({
            method: 'GET',
            url: imageUrl,
            responseType: 'arraybuffer'
        });
        await fs.writeFile(outputPath, response.data);
        console.log(`Downloaded: ${outputPath}`);
    } catch (error) {
        console.error(`Error downloading image ${imageUrl}: ${error.message}`);
    }
}

// 处理Excel文件
async function processExcel() {
    try {
        // 获取当前目录下的第一个xlsx文件
        const files = await fs.readdir('.');
        const xlsxFile = files.find(file => file.endsWith('.xlsx'));
        
        if (!xlsxFile) {
            console.log("No xlsx file found in current directory");
            return;
        }

        // 读取Excel文件
        const workbook = XLSX.readFile(xlsxFile);
        const worksheet = workbook.Sheets[workbook.SheetNames[0]];
        
        // 转换为JSON，跳过前4行
        const data = XLSX.utils.sheet_to_json(worksheet, {
            header: 1,
            range: 4  // 从第5行开始 (0-based index)
        });

        // 处理每一行
        for (const row of data) {
            if (!row[1]) continue;  // B列为空则跳过

            const folderName = row[1].toString().trim();
            const imageUrl1 = row[17];  // R列
            const imageUrl2 = row[18];  // S列

            if (!folderName) continue;

            // 创建文件夹
            const folderPath = `./goods/${folderName}`;
            if (!existsSync(folderPath)) {
                mkdirSync(folderPath, { recursive: true });
            }

            // 创建Details子文件夹
            const detailsPath = path.join(folderPath, 'details');
            if (!existsSync(detailsPath)) {
                mkdirSync(detailsPath, { recursive: true });
            }

            // 下载R列图片
            if (imageUrl1) {
                const filename1 = getFilenameFromUrl(imageUrl1);
                await downloadImage(imageUrl1, path.join(folderPath, filename1));
            }

            // 下载S列的多个图片
            if (imageUrl2) {
                const imageUrls = imageUrl2.split('$$').filter(url => url.trim());
                for (let i = 0; i < imageUrls.length; i++) {
                    const imageUrl = imageUrls[i].trim();
                    if (imageUrl) {
                        const filename = getFilenameFromUrl(imageUrl);
                        await downloadImage(imageUrl, path.join(detailsPath, filename));
                    }
                }
            }
        }

        console.log("Processing completed!");

    } catch (error) {
        console.error(`Error processing Excel file: ${error.message}`);
    }
}

// 运行程序
processExcel().catch(console.error);