groovy : poi 读取 Excel xlsx

从?http://poi.apache.org/download.html

下载?poi-bin-3.10-FINAL-20140208.zip 解压后

copy?
poi-3.10-FINAL-20140208.jar
poi-ooxml-3.10-FINAL-20140208.jar
poi-ooxml-schemas-3.10-FINAL-20140208.jar
ooxml-libdom4j-1.6.1.jar
ooxml-libstax-api-1.0.1.jar
ooxml-libxmlbeans-2.3.0.jar
? ? to D:groovy-1.8.9lib

参考?poi-3.10-FINAL/docs/spreadsheet/quick-guide.html

read_xlsx.groovy ?代码如下

package xlsx;
import javax.swing.JFileChooser;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class ReadExcel {

	//取得日期列
	private static String getDataCellVal(XSSFCell cell) {
		Date date = cell.getDateCellValue();
		def format = new SimpleDateFormat("yyyy-MM-dd");
		return format.format(date);
	}
	
	private static String getCellVal(XSSFCell cell) {
		if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) { 
			return "" 
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { 
			return cell.getBooleanCellValue(); 
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_ERROR) { 
			return cell.getErrorCellValue();		
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { 
			return cell.getNumericCellValue(); 
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { 
			return cell.getStringCellValue(); 
		} else { 
			return cell.getStringCellValue(); 
		}
	}
	
	private static String getFilename(String initDir) {
		if (initDir==null || initDir=='') initDir='.';
		def chooser=new JFileChooser(initDir)
		def filter = new FileNameExtensionFilter('Excel','xlsx')
		chooser.setFileFilter(filter)
		chooser.setDialogTitle("Open")
		if(chooser.showSaveDialog() == JFileChooser.APPROVE_OPTION)
			return chooser.getSelectedFile()
		else if(chooser.showSaveDialog() == JFileChooser.CANCEL_OPTION)
			return null
		else
			return null
	}

	void readExcel(String filename) {	
		if (filename==null || filename=='') return;
		def outfile = new File(filename.replace('.xlsx','.txt'));
		def fp = outfile.newPrintWriter();
		println filename
		XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filename));

		(0..<wb.numberOfSheets).each{
		    XSSFSheet sheet = wb.getSheetAt(it)
		    int rows = sheet.physicalNumberOfRows
		    println " sheet:"" + wb.getSheetName(it) + "" has " + rows + " row(s)."
		    (0..<rows).each{ r ->
		        XSSFRow row = sheet.getRow(r)
		        if (row != null) {
		            int cells = row.physicalNumberOfCells
		            // println "nROW " + row.rowNum + " has " + cells + " cell(s)."
		            (0..<cells).each{ c ->
		                XSSFCell cell = row.getCell(c)
		                if (cell == null){
		                	if(c==(cells-1)) fp.println '';
		                	else fp.print 't';
		                }else{
		                	if(c==(cells-1)) fp.println getCellVal(cell);
		                	else fp.print getCellVal(cell)+'t';
		                }
		            }
		        }
		    }  
		}
		fp.flush();
		fp.close();
	}

	static void main(args) {
		def filename =null;		
		if (args.size()==1)
			filename = getFilename(args[0]);
		else
			filename = getFilename('.');
		if (filename==null) return;
		def app = new ReadExcel();
		app.readExcel(filename);
	}
}

直接运行 groovy read_xlsx

如果想保护源代码，就编译?groovyc read_xlsx.groovy

run.bat ?如下

@echo off
set JarPATH=%GROOVY_HOME%/embeddable/groovy-all-1.8.9.jar
set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/poi-3.10-FINAL-20140208.jar
set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/poi-ooxml-3.10-FINAL-20140208.jar
set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/poi-ooxml-schemas-3.10-FINAL-20140208.jar
set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/dom4j-1.6.1.jar
set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/stax-api-1.0.1.jar
set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/xmlbeans-2.3.0.jar
java -cp %JarPATH%;. xlsx.ReadExcel

pause

运行 run.bat