Revision 2046 tmp/org.txm.groovy.core/src/groovy/org/txm/macro/office/MergeExcelDirMacro.groovy

MergeExcelDirMacro.groovy (revision 2046)
18 18
import org.apache.poi.ss.util.*
19 19

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

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

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

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

  
32
@Field @Option(name="multipleValues", usage="list of columns with multiple values, separated by comma", widget="String", required=false, def="")
33
def multipleValues
34

  
35
@Field @Option(name="normalizeIdentifiers", usage="normalize column names to simple lower case letters", widget="Boolean", required=true, def="true")
36
def normalizeIdentifiers
37

  
38
def stringToIdent = { str -> org.txm.utils.AsciiUtils.buildAttributeId(org.txm.utils.AsciiUtils.convertNonAscii(str)).toLowerCase() }
39

  
32 40
if (!ParametersDialog.open(this)) return false
33 41

  
34 42
if (!inputDirectory.exists()) {
......
45 53

  
46 54
def scriptName = this.class.getSimpleName()
47 55

  
48
columnList = columnList.split(",")
56
columnList = columnList.split(",") as List
49 57

  
50 58
println "columnList = "+columnList
51 59

  
60
multipleValues = multipleValues.split(",") as List
61

  
52 62
// create output file
53 63
out = new FileOutputStream(outputFile)
54 64
HSSFWorkbook wb = new HSSFWorkbook()
......
59 69
r = finalSheet.createRow(0)
60 70
columnList.eachWithIndex { columnName, i ->
61 71
	c = r.createCell(i)
62
	c.setCellValue(columnName)
72
	c.setCellValue(normalizeIdentifiers?stringToIdent(columnName):columnName)
63 73
}
64 74

  
65 75
def f = []
......
73 83

  
74 84
debug = false
75 85
//ConsoleProgressBar cpb = new ConsoleProgressBar(f.size())
86

  
76 87
try {
77 88

  
89
	// for each .xlsx input file (lexicographic order)
78 90
	f.sort { it.name }.each { inputFile ->
79 91

  
92
		if (aborted) return false
93

  
80 94
		if (debug) println "Processing "+inputFile+"."
81 95
		//cpb.tick()
82 96
		rownum = 0
......
104 118
		nRows = ws.getPhysicalNumberOfRows()
105 119
		if (debug) println nRows+" rows."
106 120

  
121
		// get columns positions
107 122
		firstRow = ws.getRow(0)
108
		nCells = firstRow.getPhysicalNumberOfCells()
123
		nCols = firstRow.getPhysicalNumberOfCells()
124
		def colNames = []
109 125
		def cellIndexes = []
110
		0.upto(nCells-1, { iCell ->
111
			value = firstRow.getCell(iCell).getStringCellValue()
126
		def multipleValuesCols = []
127
		0.upto(nCols-1, { iCol ->
128
			value = firstRow.getCell(iCol).getStringCellValue()
112 129
			if (columnList.contains(value)) {
113
				cellIndexes << iCell
130
				colNames << value
131
				cellIndexes << iCol
114 132
			}
133
			if (multipleValues.contains(value)) {
134
				multipleValuesCols << iCol
135
			}
115 136
		})
116 137

  
117 138
		if (cellIndexes.size() != columnList.size()) {
118
			println "** $scriptName: some columns missing in file $inputFile. Aborting."
139
			columnList.removeAll(colNames)
140
			println "** $scriptName: some columns missing in file $inputFile: "+columnList.join(", ")+". Aborting."
141

  
119 142
			aborted = true
120 143
			return false
121 144
		}
122 145

  
146
		// sort columns indexes by columnList parameter order
147
		cellIndexes.sort { columnList.indexOf(colNames[cellIndexes.indexOf(it)]) }
148

  
149
		// copy column values
123 150
		1.upto(nRows-1, { iRow ->
124 151
			row = ws.getRow(iRow)
125 152
			r = finalSheet.createRow(nFinalRows)
126 153
			
127 154
			nCellCreated = 0
128
			cellIndexes.each { iCell ->
129
				value = row.getCell(iCell).getStringCellValue()
155
			cellIndexes.each { iCol ->
156
				value = row.getCell(iCol).getStringCellValue()
130 157
				c = r.createCell(nCellCreated)
131
				c.setCellValue(value)
158
				if (iCol in multipleValuesCols && value.size() > 0) {
159
					(lstr = value.split(";")*.trim() as List).removeAll { it.isEmpty() }
160
					c.setCellValue("|"+lstr.join("|")+"|")
161
				} else {
162
					c.setCellValue(value)
163
				}
132 164
				nCellCreated++
133 165
			}
134 166
			

Also available in: Unified diff