Révision 2030

tmp/org.txm.groovy.core/src/groovy/org/txm/macro/office/MergeExcelDirMacro.groovy (revision 2030)
1 
// Copyright © 2019 ENS de Lyon, CNRS, University of Franche-Comté
2 
// @author mdecorde
3 
// @author sheiden
4 

  		





  1
  5
  
    
package org.txm.macro.office


  		





  2
  6
  
    

  		





  3
  7
  
    
import org.kohsuke.args4j.*


  		





  ......




  13
  17
  
    
import org.apache.poi.xssf.usermodel.*


  		





  14
  18
  
    
import org.apache.poi.ss.util.*


  		





  15
  19
  
    

  		





  16
  
  
    
@Field @Option(name="inputDirectory", usage="répertoire des fichiers Excel à traiter", widget="Folder", required=true, def="")


  		





  
  20
  
    
@Field @Option(name="inputDirectory", usage="répertoire des fichiers Excel à fusionner", widget="Folder", required=true, def="")


  		





  17
  21
  
    
		def inputDirectory


  		





  18
  22
  
    

  		





  19
  
  
    
@Field @Option(name="outputFile", usage="output file", widget="File", required=true, def="")


  		





  
  23
  
    
@Field @Option(name="outputFile", usage="output file", widget="FileSave", required=true, def="")


  		





  20
  24
  
    
		def outputFile


  		





  21
  25
  
    

  		





  22
  26
  
    
@Field @Option(name="sheetName", usage="sheet name (if no name is given the first sheet will be used)", widget="String", required=false, def="")


  		





  23
  27
  
    
		def sheetName


  		





  24
  28
  
    

  		





  25
  
  
    
@Field @Option(name="columnList", usage="column list to extract, separated with comma", widget="String", required=false, def="")


  		





  
  29
  
    
@Field @Option(name="columnList", usage="list of columns to extract, separated by comma", widget="String", required=false, def="")


  		





  26
  30
  
    
		def columnList


  		





  27
  31
  
    

  		





  28
  
  
    
if (!ParametersDialog.open(this)) return


  		





  
  32
  
    
if (!ParametersDialog.open(this)) return false


  		





  29
  33
  
    

  		





  30
  
  
    
	if (!inputDirectory.exists()) {


  		





  31
  
  
    
		println "** $scriptName: no '"+inputDirectory.name+"' directory found. Aborting."


  		





  32
  
  
    
		return false


  		





  33
  
  
    
	}


  		





  
  34
  
    
if (!inputDirectory.exists()) {


  		





  
  35
  
    
	println "** $scriptName: no '"+inputDirectory.name+"' directory found. Aborting."


  		





  
  36
  
    
	return false


  		





  
  37
  
    
}


  		





  34
  38
  
    

  		





  35
  39
  
    
if (!inputDirectory.canRead()) {


  		





  36
  40
  
    
	println "** $scriptName: '"+inputDirectory.name+"' directory not readable. Aborting."


  		





  37
  41
  
    
	return false


  		





  38
  42
  
    
}


  		





  39
  43
  
    

  		





  
  44
  
    
def aborted = false


  		





  
  45
  
    

  		





  40
  46
  
    
def scriptName = this.class.getSimpleName()


  		





  41
  47
  
    

  		





  42
  
  
    
columnList = columnList.split("\t")


  		





  
  48
  
    
columnList = columnList.split(",")


  		





  43
  49
  
    

  		





  44
  50
  
    
println "columnList = "+columnList


  		





  45
  51
  
    

  		





  ......




  49
  55
  
    
finalSheet = wb.createSheet()


  		





  50
  56
  
    

  		





  51
  57
  
    
// create header line


  		





  52
  
  
    
int nFinalRows = 1


  		





  
  58
  
    
nFinalRows = 1


  		





  53
  59
  
    
r = finalSheet.createRow(0)


  		





  54
  60
  
    
columnList.eachWithIndex { columnName, i ->


  		





  55
  61
  
    
	c = r.createCell(i)


  		





  ......




  58
  64
  
    

  		





  59
  65
  
    
def f = []


  		





  60
  66
  
    

  		





  61
  
  
    
inputDirectory.eachFileMatch(~/.*xlsx/) { f << it }


  		





  
  67
  
    
inputDirectory.eachFileMatch(~/.*\.xlsx/) { f << it }


  		





  62
  68
  
    

  		





  63
  69
  
    
if (f.size() == 0) {


  		





  64
  70
  
    
	println "** $scriptName: no .xlsx file found. Aborting."


  		





  65
  71
  
    
	return false


  		





  66
  72
  
    
}


  		





  67
  
  
    
debug = false;


  		





  68
  
  
    
ConsoleProgressBar cpb = new ConsoleProgressBar(f.size())


  		





  
  73
  
    

  		





  
  74
  
    
debug = false


  		





  
  75
  
    
//ConsoleProgressBar cpb = new ConsoleProgressBar(f.size())


  		





  69
  76
  
    
try {


  		





  70
  77
  
    

  		





  71
  78
  
    
	f.sort { it.name }.each { inputFile ->


  		





  72
  79
  
    

  		





  73
  80
  
    
		if (debug) println "Processing "+inputFile+"."


  		





  74
  
  
    
		cpb.tick()


  		





  
  81
  
    
		//cpb.tick()


  		





  75
  82
  
    
		rownum = 0


  		





  76
  83
  
    

  		





  77
  
  
    
		// open input stylesheet


  		





  78
  
  
    
		Workbook wb2 = WorkbookFactory.create(inputFile)


  		





  79
  
  
    
		Sheet ws = null;


  		





  
  84
  
    
		// open input sheet


  		





  
  85
  
    
		wb2 = WorkbookFactory.create(inputFile)


  		





  
  86
  
    
		ws = null


  		





  80
  87
  
    
		if (sheetName.length() == 0) {


  		





  81
  88
  
    
			ws = wb2.getSheetAt(0)


  		





  82
  89
  
    
		} else {


  		





  83
  90
  
    
			ws = wb2.getSheet(sheetName)


  		





  84
  91
  
    
			if (ws == null) {


  		





  85
  
  
    
				println "** Excel2XML: no '"+sheetName+" found. Aborting."


  		





  
  92
  
    
				println "** $scriptName: no '"+sheetName+" found. Aborting."


  		





  
  93
  
    
				aborted = true


  		





  86
  94
  
    
				return false


  		





  87
  95
  
    
			}


  		





  88
  96
  
    
		}


  		





  89
  97
  
    

  		





  90
  98
  
    
		if (ws == null) {


  		





  91
  99
  
    
			println "** $scriptName: no sheet found. Aborting."


  		





  
  100
  
    
			aborted = true


  		





  92
  101
  
    
			return false


  		





  93
  102
  
    
		}


  		





  94
  103
  
    

  		





  95
  104
  
    
		nRows = ws.getPhysicalNumberOfRows()


  		





  96
  105
  
    
		if (debug) println nRows+" rows."


  		





  97
  106
  
    

  		





  98
  
  
    
		Row firstRow = ws.getRow(0) // get header line


  		





  99
  
  
    
		int nCells = firstRow.getPhysicalNumberOfCells()


  		





  
  107
  
    
		firstRow = ws.getRow(0)


  		





  
  108
  
    
		nCells = firstRow.getPhysicalNumberOfCells()


  		





  100
  109
  
    
		def cellIndexes = []


  		





  101
  
  
    
		for (int iCell = 0 ; iCell < nCells ; iCell++) { // get indexes of columns to copy


  		





  102
  
  
    
			String value = firstRow.getCell(iCell).getStringCellValue()


  		





  
  110
  
    
		0.upto(nCells-1, { iCell ->


  		





  
  111
  
    
			value = firstRow.getCell(iCell).getStringCellValue()


  		





  103
  112
  
    
			if (columnList.contains(value)) {


  		





  104
  113
  
    
				cellIndexes << iCell


  		





  105
  114
  
    
			}


  		





  
  115
  
    
		})


  		





  
  116
  
    

  		





  
  117
  
    
		if (cellIndexes.size() != columnList.size()) {


  		





  
  118
  
    
			println "** $scriptName: some columns missing in file $inputFile. Aborting."


  		





  
  119
  
    
			aborted = true


  		





  
  120
  
    
			return false


  		





  106
  121
  
    
		}


  		





  107
  
  
    
		


  		





  108
  
  
    
		for (int iRow = 1 ; iRow < nRows ; iRow++) { // copy rows from next lines


  		





  109
  
  
    
			Row row = ws.getRow(iRow)


  		





  
  122
  
    

  		





  
  123
  
    
		1.upto(nRows-1, { iRow ->


  		





  
  124
  
    
			row = ws.getRow(iRow)


  		





  110
  125
  
    
			r = finalSheet.createRow(nFinalRows)


  		





  111
  126
  
    
			


  		





  112
  
  
    
			int nCellCreated = 0


  		





  113
  
  
    
			for (int iCell : cellIndexes) {


  		





  114
  
  
    
				String value = row.getCell(iCell).getStringCellValue()


  		





  115
  
  
    
				Cell c = r.createCell(nCellCreated)


  		





  
  127
  
    
			nCellCreated = 0


  		





  
  128
  
    
			cellIndexes.each { iCell ->


  		





  
  129
  
    
				value = row.getCell(iCell).getStringCellValue()


  		





  
  130
  
    
				c = r.createCell(nCellCreated)


  		





  116
  131
  
    
				c.setCellValue(value)


  		





  117
  132
  
    
				nCellCreated++


  		





  118
  133
  
    
			}


  		





  119
  134
  
    
			


  		





  120
  135
  
    
			nFinalRows++


  		





  121
  
  
    
		}


  		





  
  136
  
    
		})


  		





  122
  137
  
    
	}


  		





  123
  138
  
    
} catch(Exception e) {


  		





  124
  
  
    
	return false;


  		





  
  139
  
    
	aborted = true


  		





  
  140
  
    
	return false


  		





  125
  141
  
    
}


  		





  126
  
  
    
cpb.done();


  		





  
  142
  
    
//cpb.done()


  		





  127
  143
  
    

  		





  128
  144
  
    
/* write sheet */


  		





  129
  145
  
    

  		





  130
  146
  
    
wb.write(out)


  		





  131
  147
  
    
out.close()


  		





  132
  148
  
    

  		





  
  149
  
    
if (aborted) return false


  		





  
  150
  
    

  		





  133
  151
  
    
println nFinalRows+" rows written."


  		





  134
  152
  
    

  		





  135
  153
  
    
return true


  		





  
  154
  
    

  		












  

    
      tmp/org.txm.groovy.core/src/java/org/txm/groovy/core/GSERunner.java	(revision 2030)
    
  






  70
  70
  
    

  		





  71
  71
  
    
		String[] roots = new String[] { 


  		





  72
  72
  
    
				rootDir.getAbsolutePath()+"/user/", //$NON-NLS-1$


  		





  73
  
  
    
				rootDir.getAbsolutePath()+"/system/", //$NON-NLS-1$


  		





  
  73
  
    
				//rootDir.getAbsolutePath()+"/system/", //$NON-NLS-1$


  		





  74
  74
  
    
		}; 


  		





  75
  75
  
    

  		





  76
  76
  
    
		try {


  		












Formats disponibles :
  Unified diff