欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

JAVA操作excel总结

程序员文章站 2022-07-13 13:25:17
...

目前主要有两种常用的方式,jxl和POI。因为POI功能更加强大,面对大量数据时操作性能更加优于jxl,因此,在工作中一般使用POI来操作excel。

      POI是Apache下的开放源码函数库,POI提供了一系列的api来供java程序员对Microsoft Office格式的文档提供读写功能。

      由于excel2003(.xls)与excel2007(.xlsx)以及之后版本底层的实现方式不同,因此POI对不同版本的excel操作方式也不尽相同。

  • 1.新建excel并写入:

   

Workbook wwb = new XSSFWorkbook();//创建excel工作簿   excel2003以及之前版本请使用HSSFWorkbook
  Sheet sheet = wwb.createSheet( "sheet1" );// 创建sheet  若有多页sheet页面可创建多个
  Row row = sheet.createRow( 0 );//创建行 0代表第一行
  row1.createCell( 0 ).setCellValue( '单元格');//创建单元格并赋值 0代表当前行的第一个单元格}}}
  //Workbook提供write方法,可以将当前工作空间写出到指定的地方。


 

  • 2.读取excel
   Workbook wwb = new XSSFWorkbook(excel.getInputStream());//将指定excel文件转化为工作空间
  Sheet sheet = wwb.getSheetAt( 0 );// 获取第一个sheet页
  Row row = sheet.getRow( 0 );//获取当前页的一行
  //获取当前行的第一个单元格   excel支持多种数据类型,可以使用 getCellType()方法来获取当前单元格数据类型
  row1.getCell( 0 );
  • 3.有关excel性能的问题。

     

       在写入excel时XSSFWorkbook采用的是将所有数据处理完成后一次写入excel文件,当写入的文件数据量较大时,特别是一次写入十几万条数据时,很容易发生内存溢出,在数据量很庞大的情况下,调整jvm内存并不是最优的办法,因此,POI在3.8版本增加了对于超大数据量的特定api(这种api只能针对exel2007及以后版本使用),SXSSFWorkbook的使用方式 很简单,直接创建Workbook wwb = new XSSFWorkbook(100);//100代表每当有100条数据时就将内存中的数据写入到指定的excel中
这样就避免了处理很大数据量内存溢出的问题。       同理,在读取excel的时候,XSSFWorkbook也是将所有数据全部读取到内存中,才开始处理,因此,当数据量较大时也会发生内存溢出事件。因此,POI也提供了对于读取超大数据量的api(此方法也只能用于excel2007及以后版本,2007版本以后excel底层使用xml)。此方法采用的是一行一行读取exel,因此也不会发生内存溢出。读取代码demo如下:

public class ExcelEventParser {
  
  private String fileName;//文件名
  private SheetContentsHandler handler;//读取文件
  public ExcelEventParser(String fileName){
     this.fileName = fileName;
  }
  
  public ExcelEventParser setHandler(SheetContentsHandler handler){
     this.handler = handler;
     return this ;
  }
  
  public void parse(){
      OPCPackage pkg = null ;
      InputStream sheetInputStream = null ;
     try{
         pkg = OPCPackage.open(fileName,PackageAccess.READ);//打开一个含有读权限的包
        XSSFReader xssfReader = new XSSFReader(pkg);//获取xlsx文件
        
         StylesTable stylesTable = xssfReader.getStylesTable();//
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
         sheetInputStream = xssfReader.getSheetsData().next();
        
         processSheet(stylesTable,strings,sheetInputStream);
     }catch(Exception e){
         e.printStackTrace();
        throw new RuntimeException(e.getMessage(),e);
     }finally {
        if(sheetInputStream != null ){
           try {
               sheetInputStream.close();
           } catch (IOException e) {
               e.printStackTrace();
           }
        }
        if(pkg != null ){
           try {
               pkg.close();
           } catch (IOException e) {
               e.printStackTrace();
           }
        }
     }
  }
  
  private void processSheet(StylesTable styles,ReadOnlySharedStringsTable strings,InputStream sheetInputStream)
        throws SAXException, ParserConfigurationException, IOException{
      XMLReader sheetParser = SAXHelper.newXMLReader();
     if(handler != null ){
         sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));
     }else{
         sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings,new SimpleSheetContentsHandler(), false));
     }
      sheetParser.parse(new InputSource(sheetInputStream));
  }
  public static class SimpleSheetContentsHandler implements SheetContentsHandler{  
      protected List<String> row = new LinkedList<>();
      @Override  
      public void startRow(int rowNum) {  
           row.clear();  
      }  
      @Override  
      public void endRow(int rowNum) {  
           System.err.println(rowNum + " : " + row);  
      }  
      @Override  
      public void cell(String cellReference, String formattedValue, XSSFComment comment) {  
           row.add(formattedValue);  
      }  
      @Override  
      public void headerFooter(String text, boolean isHeader, String tagName) {       
      }  
  }
  public static void main(String[] args) throws Throwable{  
      long start = System.currentTimeMillis();  
      final List<List<String>> table = new ArrayList<>();  
      new ExcelEventParser("/Users/niulj/Desktop/123.xlsx").setHandler(new SimpleSheetContentsHandler(){  
          @Override  
          public void endRow(int rowNum) {  
              if(rowNum == 0){  
                  // todo 对表头的读取以及处理
              }else {  
                  row.get(0);//对当前行的第一个单元格进行读取
              }  
          }  
      }).parse();        
  }
}


      另外,我们在读取一些数,比如手机号,如果按照数字读取需要注意指定格式(DecimalFormat df = new DecimalFormat("#.#"))来处理读取数据含有小数位的问题,另外在读取单元格的时候最好判断单元格类型。此外会遇到各种看到的excel行数与实际程序读取的行数不同的问题,这种问题一般都是由于使用者删除excel时没有完全删除该行,因此在最好在读取行的时候添加是否是空行的校验。

       对于上千万数据的读取与导出,由于excel目前新版最大支持100万左右,因此,若真的想一个excel读取,可以采用多sheet的方式,导出时也可以采用这种方式处理,但是若远程导出,文件较大时可以在服务器端先压缩打包,然后将打包文件发送给客户端。打包时可以将excel拆分为多个。