groovy : poi 读取 Excel xlsx_综合

转载自：http://blog.csdn.net/belldeep/article/details/39721345

从 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-lib\dom4j-1.6.1.jar
ooxml-lib\stax-api-1.0.1.jar
ooxml-lib\xmlbeans-2.3.0.jar
to D:\groovy-1.8.9\lib\

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

read_xlsx.groovy 代码如下

[java] view plain copy

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 如下

[plain] view plain copy

@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