Short Description

Take an input folder containing several XLS files and merge them together while adding a new column containing XLS file name (presumably image name).

Versions

  • Version 2 • Released on: 2016-06-28 13:40:45
    Download
    Description:

    Keep cell number format when possible.

    importClass(Packages.icy.util.StringUtil)
importClass(Packages.icy.util.XLSUtil)
importClass(Packages.icy.gui.dialog.ConfirmDialog)
importClass(Packages.icy.file.FileUtil)
importClass(Packages.plugins.tprovoost.scripteditor.uitools.filedialogs.FileDialog)


d = new FileDialog()

// get source folder
srcFolder = d.openFolder()
if (srcFolder == null) throw "Operation canceled"

// get output file
outFile = d.open()
if (outFile == null) throw "Operation canceled"

if (outFile.exists())
{
	if (!ConfirmDialog.confirm("Output file already exists, continue ?"))
		throw "Operation canceled"

	// delete output file
	outFile.delete();
}

mergedDoc = XLSUtil.createWorkbook(outFile)
mergedSh = XLSUtil.createNewPage(mergedDoc, "merged results")

files = FileUtil.getFiles(srcFolder, null, false, false, false)
mergedY = 0;

for(f = 0; f < files.length; f++)
{
	file = files[f]

	// ignore invalid entries
	if (!file.exists()) continue;

	println("Processing file: " + file.getName())

	doc = XLSUtil.loadWorkbookForRead(file)
	sh = doc.getSheet(0)

	// first file ?
	if (f == 0) XLSUtil.setCellString(mergedSh, 0, 0, "Image")
	
	for(y = 0; y < sh.getRows(); y++)
	{		
		// write image name in first column
		if (y > 0) XLSUtil.setCellString(mergedSh, 0, mergedY, file.getName())
		
		for(x = 0; x < sh.getColumns(); x++)
		{
			cell = sh.getCell(x, y)
			// not first row or first file
			if ((y > 0) || (f == 0))
			{
				type = cell.getType()
				
				if (type == type.NUMBER)
				{
					d = StringUtil.parseDouble(cell.getContents(), 0)
					XLSUtil.setCellNumber(mergedSh, x + 1, mergedY, d)
				}
				else XLSUtil.setCellString(mergedSh, x + 1, mergedY, cell.getContents())
			}
		}

		// pass to next row
		if ((y > 0) || (f == 0)) mergedY++;
	}
}

XLSUtil.saveAndClose(mergedDoc)
  • Version 1 • Released on: 2016-06-27 18:08:54
    Download
    Description:

    initial version

    importClass(Packages.icy.util.XLSUtil)
importClass(Packages.icy.gui.dialog.ConfirmDialog)
importClass(Packages.icy.file.FileUtil)
importClass(Packages.plugins.tprovoost.scripteditor.uitools.filedialogs.FileDialog)


d = new FileDialog()

// get source folder
srcFolder = d.openFolder()
if (srcFolder == null) throw "Operation canceled"

// get output file
outFile = d.open()
if (outFile == null) throw "Operation canceled"

if (outFile.exists())
{
	if (!ConfirmDialog.confirm("Output file already exists, continue ?"))
		throw "Operation canceled"

	// delete output file
	outFile.delete();
}

mergedDoc = XLSUtil.createWorkbook(outFile)
mergedSh = XLSUtil.createNewPage(mergedDoc, "merged results")

files = FileUtil.getFiles(srcFolder, null, false, false, false)
mergedY = 0;

for(f = 0; f < files.length; f++)
{
	file = files[f]

	// ignore invalid entries
	if (!file.exists()) continue;

	println("Processing file: " + file.getName())

	doc = XLSUtil.loadWorkbookForRead(file)
	sh = doc.getSheet(0)

	// first file ?
	if (f == 0) XLSUtil.setCellString(mergedSh, 0, 0, "Image")
	
	for(y = 0; y < sh.getRows(); y++)
	{		
		// write image name in first column
		if (y > 0) XLSUtil.setCellString(mergedSh, 0, mergedY, file.getName())
		
		for(x = 0; x < sh.getColumns(); x++)
		{
			cell = sh.getCell(x, y)
			// not first row or first file
			if ((y > 0) || (f == 0)) XLSUtil.setCellString(mergedSh, x + 1, mergedY, cell.getContents())
		}

		// pass to next row
		if ((y > 0) || (f == 0)) mergedY++;
	}
}

XLSUtil.saveAndClose(mergedDoc)

Leave a Review