groovy : poi 读取 Excel xlsx

最新推荐文章于 2019-03-11 23:16:08 发布

belldeep

最新推荐文章于 2019-03-11 23:16:08 发布

阅读量2.9k

点赞数

分类专栏： Groovy 文章标签： groovy poi excel java

本文链接：https://blog.csdn.net/belldeep/article/details/39721345

版权

Groovy 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

从 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 代码如下

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