当前位置: 代码迷 >> 综合 >> groovy : poi 读取 Excel xlsx
  详细解决方案

groovy : poi 读取 Excel xlsx

热度:96   发布时间:2023-12-14 02:06:11.0

转载自: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
  1. package xlsx;  
  2. import javax.swing.JFileChooser;  
  3. import javax.swing.filechooser.FileFilter;  
  4. import javax.swing.filechooser.FileNameExtensionFilter;  
  5. import java.io.File;  
  6. import java.text.DateFormat;  
  7. import java.text.SimpleDateFormat;  
  8. import org.apache.poi.xssf.usermodel.XSSFCell;  
  9. import org.apache.poi.xssf.usermodel.XSSFRow;  
  10. import org.apache.poi.xssf.usermodel.XSSFSheet;  
  11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  12.   
  13. class ReadExcel {  
  14.   
  15.     //取得日期列  
  16.     private static String getDataCellVal(XSSFCell cell) {  
  17.         Date date = cell.getDateCellValue();  
  18.         def format = new SimpleDateFormat("yyyy-MM-dd");  
  19.         return format.format(date);  
  20.     }  
  21.       
  22.     private static String getCellVal(XSSFCell cell) {  
  23.         if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {   
  24.             return ""   
  25.         } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {   
  26.             return cell.getBooleanCellValue();   
  27.         } else if (cell.getCellType() == XSSFCell.CELL_TYPE_ERROR) {   
  28.             return cell.getErrorCellValue();          
  29.         } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {   
  30.             return cell.getNumericCellValue();   
  31.         } else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {   
  32.             return cell.getStringCellValue();   
  33.         } else {   
  34.             return cell.getStringCellValue();   
  35.         }  
  36.     }  
  37.       
  38.     private static String getFilename(String initDir) {  
  39.         if (initDir==null || initDir=='') initDir='.';  
  40.         def chooser=new JFileChooser(initDir)  
  41.         def filter = new FileNameExtensionFilter('Excel''xlsx')  
  42.         chooser.setFileFilter(filter)  
  43.         chooser.setDialogTitle("Open")  
  44.         if(chooser.showSaveDialog() == JFileChooser.APPROVE_OPTION)  
  45.             return chooser.getSelectedFile()  
  46.         else if(chooser.showSaveDialog() == JFileChooser.CANCEL_OPTION)  
  47.             return null  
  48.         else  
  49.             return null  
  50.     }  
  51.   
  52.     void readExcel(String filename) {     
  53.         if (filename==null || filename==''return;  
  54.         def outfile = new File(filename.replace('.xlsx','.txt'));  
  55.         def fp = outfile.newPrintWriter();  
  56.         println filename  
  57.         XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filename));  
  58.   
  59.         (0..<wb.numberOfSheets).each{  
  60.             XSSFSheet sheet = wb.getSheetAt(it)  
  61.             int rows = sheet.physicalNumberOfRows  
  62.             println " sheet:\"" + wb.getSheetName(it) + "\" has " + rows + " row(s)."  
  63.             (0..<rows).each{ r ->  
  64.                 XSSFRow row = sheet.getRow(r)  
  65.                 if (row != null) {  
  66.                     int cells = row.physicalNumberOfCells  
  67.                     // println "\nROW " + row.rowNum + " has " + cells + " cell(s)."  
  68.                     (0..<cells).each{ c ->  
  69.                         XSSFCell cell = row.getCell(c)  
  70.                         if (cell == null){  
  71.                             if(c==(cells-1)) fp.println '';  
  72.                             else fp.print '\t';  
  73.                         }else{  
  74.                             if(c==(cells-1)) fp.println getCellVal(cell);  
  75.                             else fp.print getCellVal(cell)+'\t';  
  76.                         }  
  77.                     }  
  78.                 }  
  79.             }    
  80.         }  
  81.         fp.flush();  
  82.         fp.close();  
  83.     }  
  84.   
  85.     static void main(args) {  
  86.         def filename =null;       
  87.         if (args.size()==1)  
  88.             filename = getFilename(args[0]);  
  89.         else  
  90.             filename = getFilename('.');  
  91.         if (filename==nullreturn;  
  92.         def app = new ReadExcel();  
  93.         app.readExcel(filename);  
  94.     }  
  95. }  


直接运行 groovy read_xlsx

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

run.bat  如下

[plain] view plain copy
  1. @echo off  
  2. set JarPATH=%GROOVY_HOME%/embeddable/groovy-all-1.8.9.jar  
  3. set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/poi-3.10-FINAL-20140208.jar  
  4. set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/poi-ooxml-3.10-FINAL-20140208.jar  
  5. set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/poi-ooxml-schemas-3.10-FINAL-20140208.jar  
  6. set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/dom4j-1.6.1.jar  
  7. set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/stax-api-1.0.1.jar  
  8. set JarPATH=%JarPATH%;%GROOVY_HOME%/lib/xmlbeans-2.3.0.jar  
  9. java -cp %JarPATH%;. xlsx.ReadExcel  
  10.   
  11. pause  

运行 run.bat

  相关解决方案