关于java读取excle文件的相关方法；

1.读取Excle文件内容的方法拿过来可以直接用；

2.参照 http://www.anyrt.com/blog/list/importexcel.html#6

更多知识请参考：http://www.anyrt.com/blog/index.html

说明：本次中使用的jra包有：POI3.9

  1 package Demo;
  2 
  3 import java.io.File;
  4 import java.io.FileInputStream;
  5 import java.io.FileNotFoundException;
  6 import java.io.IOException;
  7 import java.util.List;
  8 import org.apache.poi.hssf.usermodel.HSSFPicture;
  9 import org.apache.poi.hssf.usermodel.HSSFPictureData;
 10 import org.apache.poi.hssf.usermodel.HSSFShape;
 11 import org.apache.poi.hssf.usermodel.HSSFSheet;
 12 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 13 import org.apache.poi.ss.usermodel.Cell;
 14 import org.apache.poi.ss.usermodel.DateUtil;
 15 import org.apache.poi.ss.usermodel.Row;
 16 import org.apache.poi.xssf.usermodel.XSSFSheet;
 17 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 18 
 19 public class ExcleXlsxDemo {
 20 /**
 21  * 
 22  * 
 23  */
 24     public void xlsxTypeExcle(String filePath) {
 25         // File excelFile = new File("/Users/mike/table.xlsx");
 26         File excelFile = new File(filePath);
 27         XSSFWorkbook wb;
 28         try {
 29             wb = new XSSFWorkbook(new FileInputStream(excelFile));
 30             XSSFSheet sheet = wb.getSheetAt(0);
 31             for (Row row : sheet) {
 32                 for (Cell cell : row) {
 33                     switch (cell.getCellType()) {
 34                     case Cell.CELL_TYPE_STRING:
 35                         System.out.print(cell.getRichStringCellValue().getString());
 36                         System.out.print("|");
 37                         break;
 38                     case Cell.CELL_TYPE_NUMERIC:
 39                         if (DateUtil.isCellDateFormatted(cell)) {
 40                             System.out.print(String.valueOf(cell.getDateCellValue()));
 41                         } else {
 42                             System.out.print(cell.getNumericCellValue());
 43                         }
 44                         System.out.print("|");
 45                         break;
 46                     case Cell.CELL_TYPE_BOOLEAN:
 47                         System.out.print(cell.getBooleanCellValue());
 48                         System.out.print("|");
 49                         break;
 50                     default:
 51                     }
 52                 }
 53                 System.out.println();
 54             }
 55         } catch (FileNotFoundException e) {
 56             // TODO Auto-generated catch block
 57             e.printStackTrace();
 58         } catch (IOException e) {
 59             // TODO Auto-generated catch block
 60             e.printStackTrace();
 61         }
 62     }
 63 
 64     /**
 65      * 
 66      * 读取xls格式的excle
 67      * 
 68      * @param args
 69      * @throws IOException
 70      * @throws FileNotFoundException
 71      * @throws Exception
 72      */
 73     @SuppressWarnings("deprecation")
 74     public void xlsTypeExcle(String filePath) throws FileNotFoundException, IOException {
 75         // File excelFile = new File("/Users/mike/table5.xls");
 76         File xlsExcelFile = new File(filePath);
 77         HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xlsExcelFile));
 78         HSSFSheet sheet = wb.getSheetAt(0);
 79         System.out.println("***************"+filePath+" 的内容如下：");
 80         for (Row row : sheet) {
 81             for (Cell cell : row) {
 82                 switch (cell.getCellType()) {
 83                 case Cell.CELL_TYPE_STRING:
 84                     System.out.print(cell.getRichStringCellValue().getString());
 85                     System.out.print("|");
 86                     break;
 87                 case Cell.CELL_TYPE_NUMERIC:
 88                     if (DateUtil.isCellDateFormatted(cell)) {
 89                         System.out.print(String.valueOf(cell.getDateCellValue()));
 90                     } else {
 91                         System.out.print(cell.getNumericCellValue());
 92                     }
 93                     System.out.print("|");
 94                     break;
 95                 case Cell.CELL_TYPE_BOOLEAN:
 96                     System.out.print(cell.getBooleanCellValue());
 97                     System.out.print("|");
 98                     break;
 99                 default:
100                 }
101             }
102             System.out.println();
103         }
104 
105     }
106 
107     public static void main(String[] args) {
108         ExcleXlsxDemo xlsx =new ExcleXlsxDemo();
109         String xlsx_Excle ="D:\2007Text.xlsx";
110         String xls_excle="D:\2003table5.xls";
111         try {
112             xlsx.xlsTypeExcle(xls_excle);
113             xlsx.xlsxTypeExcle(xlsx_Excle);
114         } catch (FileNotFoundException e) {
115             // TODO Auto-generated catch block
116             e.printStackTrace();
117         } catch (IOException e) {
118             // TODO Auto-generated catch block
119             e.printStackTrace();
120         }
121         
122 
123     }
124 
125 }

Java POI读取excel 支持xls、xlsx

报表存在读取数据源来自excel，系统常常出现读取excel需求，Java POI不仅可以输出excel，也可以读取excel中单元格数据、图片数据，poi也支持excel2007（xlsx）读取，使用poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-scratchpad-3.8-20120326.jar，这些Jar包可以从apache poi获取

关于2003-2007版本Excle单元格读取

File excelFile = new File("/Users/mike/table5.xls");
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
HSSFSheet sheet = wb.getSheetAt(0);
 
for (Row row : sheet) {
    for (Cell cell : row) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING://字符串
            System.out.print(cell.getRichStringCellValue().getString());
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_NUMERIC://数值与日期
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.print(String.valueOf(cell.getDateCellValue()));
            } else {
                System.out.print(cell.getNumericCellValue());
            }
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_BOOLEAN://boolean类型
            System.out.print(cell.getBooleanCellValue());
            System.out.print("|");
            break;
        default:
        }
    }
    System.out.println();

图片读取

先获取excel所有的图片，再查询pictureIndex 根据pictureIndex获取对应的图片

//读取图片
List<HSSFPictureData> pictures = wb.getAllPictures();  
for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
    if (shape instanceof HSSFPicture) {
        HSSFPicture pic = (HSSFPicture) shape;  
        int pictureIndex = pic.getPictureIndex()-1;  
        HSSFPictureData picData = pictures.get(pictureIndex);
        System.out.println("image-size:" + picData.getData().length);
    }  
}

读取sheet名称

wb.getSheetName(0)

整个实例

import java.io.File;
import java.io.FileInputStream;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
 
public final class TestImportExcel {
 
    public static void main(String[] args) throws Exception  {
 
        File excelFile = new File("/Users/mike/table5.xls");
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
        HSSFSheet sheet = wb.getSheetAt(0);
 
        for (Row row : sheet) {
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.print(String.valueOf(cell.getDateCellValue()));
                    } else {
                        System.out.print(cell.getNumericCellValue());
                    }
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    System.out.print("|");
                    break;
                default:
                }
            }
            System.out.println();
        }
         
        //读取图片
        List<HSSFPictureData> pictures = wb.getAllPictures();  
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;  
                int pictureIndex = pic.getPictureIndex()-1;  
                HSSFPictureData picData = pictures.get(pictureIndex);
                System.out.println("image-size:" + picData.getData().length);
            }  
        }  
         
        System.out.println(wb.getSheetName(0));
    }
}

xlsx读取单元格

xlsx读取大概与xls读取相类似，xlsx采用是X开头的类，采用的是XSSFWorkbook ；

File excelFile = new File("/Users/mike/table.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(excelFile));
XSSFSheet sheet = wb.getSheetAt(0);
 
for (Row row : sheet) {
    for (Cell cell : row) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.print(cell.getRichStringCellValue().getString());
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.print(String.valueOf(cell.getDateCellValue()));
            } else {
                System.out.print(cell.getNumericCellValue());
            }
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.print(cell.getBooleanCellValue());
            System.out.print("|");
            break;
        default:
        }
    }
    System.out.println();
}

xlsx读取图片

xlsx读取图片直接获取所有的图片，然后遍历图片获取图片数据

//读取图片
List<XSSFPictureData> pictures = wb.getAllPictures();  
for (int i = 0; i < pictures.size(); i++) {
    XSSFPictureData pictureData = pictures.get(i);
    byte[] picData = pictureData.getData();
    System.out.println("image-size:" + picData.length);
 }

xlsx示例

import java.io.File;
import java.io.FileInputStream;
import java.util.List;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public final class TestImportXlsx {
 
    public static void main(String[] args) throws Exception  {
 
        File excelFile = new File("/Users/mike/table.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(excelFile));
        XSSFSheet sheet = wb.getSheetAt(0);
 
        for (Row row : sheet) {
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.print(String.valueOf(cell.getDateCellValue()));
                    } else {
                        System.out.print(cell.getNumericCellValue());
                    }
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    System.out.print("|");
                    break;
                default:
                }
            }
            System.out.println();
        }
         
        //读取图片
        List<XSSFPictureData> pictures = wb.getAllPictures();  
        for (int i = 0; i < pictures.size(); i++) {
            XSSFPictureData pictureData = pictures.get(i);
            byte[] picData = pictureData.getData();
            System.out.println("image-size:" + picData.length);
         }
        System.out.println(wb.getSheetName(0));
    }
}