[V0_FILE]javascript:file="merge-excel.js" type="nodejs" project="Excel Merger" isMerged="true" import * as XLSX from 'xlsx'; import { promises as fs } from 'fs'; // This function will merge the two Excel files async function mergeExcelFiles(file1Path, file2Path, outputPath) { console.log('Reading Excel files...'); // Read the first Excel file (the standard one) const workbook1 = XLSX.readFile(file1Path); const sheet1 = workbook1.Sheets[workbook1.SheetNames[0]]; const data1 = XLSX.utils.sheet_to_json(sheet1); // Read the second Excel file const workbook2 = XLSX.readFile(file2Path); const sheet2 = workbook2.Sheets[workbook2.SheetNames[0]]; const data2 = XLSX.utils.sheet_to_json(sheet2); console.log(`File 1 has ${data1.length} rows`); console.log(`File 2 has ${data2.length} rows`); // Create a Set of all Links from file 1 for quick lookup const linksInFile1 = new Set(data1.map(row => row.Link)); // Find rows in file 2 that don't exist in file 1 (based on Link) const uniqueRowsFromFile2 = data2.filter(row => !linksInFile1.has(row.Link)); console.log(`Found ${uniqueRowsFromFile2.length} unique rows in File 2 that don't exist in File 1`); // Merge the data: all rows from file 1 + unique rows from file 2 const mergedData = [...data1, ...uniqueRowsFromFile2]; // Create a new workbook and add the merged data const newWorkbook = XLSX.utils.book_new(); const newSheet = XLSX.utils.json_to_sheet(mergedData); XLSX.utils.book_append_sheet(newWorkbook, newSheet, 'Merged Data'); // Write the merged data to a new Excel file XLSX.writeFile(newWorkbook, outputPath); console.log(`Merged data saved to ${outputPath}`); console.log(`Total rows in merged file: ${mergedData.length}`); } // Example usage (replace with your actual file paths) // To use this script, replace these paths with your actual file paths const file1Path = './file1.xlsx'; // Your standard file const file2Path = './file2.xlsx'; // File to compare against const outputPath = './merged_result.xlsx'; // Where to save the result mergeExcelFiles(file1Path, file2Path, outputPath) .catch(error => console.error('Error:', error)); [V0_FILE]typescriptreact:file="app/page.tsx" import { FileUploadForm } from "@/components/file-upload-form" export default function Home() { return (

Excel File Merger

Upload two Excel files to merge them based on the Link column.

How It Works

Upload two Excel files with columns: Link, Created At, Status
The first file will be used as the standard file
The second file will be compared against the first file
If a Link exists in file 1, it won't be merged (file 1's data is kept)
If a Link exists in file 2 but not in file 1, it will be added to the result
The Created At and Status values will be preserved

) } [V0_FILE]typescriptreact:file="components/file-upload-form.tsx" "use client" import type React from "react" import { useState } from "react" import { Button } from "@/components/ui/button" import { Input } from "@/components/ui/input" import { Label } from "@/components/ui/label" import { mergeExcelFiles } from "@/app/actions" import { Loader2, CheckCircle, AlertCircle } from "lucide-react" import { Alert, AlertDescription, AlertTitle } from "@/components/ui/alert" export function FileUploadForm() { const [file1, setFile1] = useState(null) const [file2, setFile2] = useState(null) const [isLoading, setIsLoading] = useState(false) const [result, setResult] = useState<{ success: boolean message: string downloadUrl?: string fileName?: string } | null>(null) const handleSubmit = async (e: React.FormEvent) => { e.preventDefault() if (!file1 || !file2) { setResult({ success: false, message: "Please select both Excel files", }) return } setIsLoading(true) setResult(null) try { const formData = new FormData() formData.append("file1", file1) formData.append("file2", file2) const response = await mergeExcelFiles(formData) setResult(response) } catch (error) { setResult({ success: false, message: "An error occurred while processing the files", }) } finally { setIsLoading(false) } } return (

File 1 (Standard File) setFile1(e.target.files?.[0] || null)} className="cursor-pointer" /> {file1 &&

Selected: {file1.name}

}

File 2 (Comparison File) setFile2(e.target.files?.[0] || null)} className="cursor-pointer" /> {file2 &&

Selected: {file2.name}

}

{result && ( {result.success ? : } {result.success ? "Success" : "Error"} {result.message} {result.success && result.downloadUrl && (

)} )}

) } [V0_FILE]typescriptreact:file="app/actions.ts" "use server" import * as XLSX from "xlsx" import { writeFile } from "fs/promises" import { join } from "path" import { mkdir } from "fs/promises" import { existsSync } from "fs" export async function mergeExcelFiles(formData: FormData) { try { // Get files from form data const file1 = formData.get("file1") as File const file2 = formData.get("file2") as File if (!file1 || !file2) { return { success: false, message: "Please provide both Excel files", } } // Convert files to array buffers const file1Buffer = await file1.arrayBuffer() const file2Buffer = await file2.arrayBuffer() // Read the Excel files const workbook1 = XLSX.read(new Uint8Array(file1Buffer), { type: "array" }) const workbook2 = XLSX.read(new Uint8Array(file2Buffer), { type: "array" }) // Get the first sheet from each workbook const sheet1 = workbook1.Sheets[workbook1.SheetNames[0]] const sheet2 = workbook2.Sheets[workbook2.SheetNames[0]] // Convert sheets to JSON const data1 = XLSX.utils.sheet_to_json(sheet1) const data2 = XLSX.utils.sheet_to_json(sheet2) if (!data1.length || !data2.length) { return { success: false, message: "One or both files are empty", } } // Check if files have the required columns const sampleRow1 = data1[0] as any const sampleRow2 = data2[0] as any // Find the Link column (case insensitive) const getLinkColumn = (row: any) => { const keys = Object.keys(row) return keys.find((key) => key.toLowerCase() === "link") } const linkColumn1 = getLinkColumn(sampleRow1) const linkColumn2 = getLinkColumn(sampleRow2) if (!linkColumn1 || !linkColumn2) { return { success: false, message: "One or both files are missing the Link column", } } // Create a Set of all Links from file 1 for quick lookup const linksInFile1 = new Set(data1.map((row: any) => row[linkColumn1])) // Find rows in file 2 that don't exist in file 1 (based on Link) const uniqueRowsFromFile2 = data2.filter((row: any) => !linksInFile1.has(row[linkColumn2])) // Merge the data: all rows from file 1 + unique rows from file 2 const mergedData = [...data1, ...uniqueRowsFromFile2] // Create a new workbook and add the merged data const newWorkbook = XLSX.utils.book_new() const newSheet = XLSX.utils.json_to_sheet(mergedData) XLSX.utils.book_append_sheet(newWorkbook, newSheet, "Merged Data") // Generate the Excel file const excelBuffer = XLSX.write(newWorkbook, { type: "buffer", bookType: "xlsx", }) // Create a temporary directory if it doesn't exist const tempDir = join(process.cwd(), "tmp") if (!existsSync(tempDir)) { await mkdir(tempDir, { recursive: true }) } // Generate a unique filename const fileName = `merged_excel_${Date.now()}.xlsx` const filePath = join(tempDir, fileName) // Write the file to disk await writeFile(filePath, excelBuffer) // Create a data URL for the file const base64 = Buffer.from(excelBuffer).toString("base64") const dataUrl = `data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,${base64}` return { success: true, message: `Successfully merged files. Found ${uniqueRowsFromFile2.length} unique entries from file 2 that were added to file 1.`, downloadUrl: dataUrl, fileName, } } catch (error) { console.error("Error merging Excel files:", error) return { success: false, message: `Error processing files: ${(error as Error).message}`, } } } [V0_FILE]typescriptreact:file="app/layout.tsx" import type React from "react" import "./globals.css" import type { Metadata } from "next" import { Inter } from "next/font/google" import { ThemeProvider } from "@/components/theme-provider" const inter = Inter({ subsets: ["latin"] }) export const metadata: Metadata = { title: "Excel File Merger", description: "Merge Excel files based on Link column", } export default function RootLayout({ children, }: Readonly<{ children: React.ReactNode }>) { return ( {children} ) }