工具类篇——Java之Excel导出读取

在程序员进行系统开发工作时，常常需要添加一个报表导入导出的功能，本文将详细介绍java怎么实现从外部读取excel文件的数据到Console。

一、在eclipse开发工具中新建java project项目取名ExcelOpration，导入poi需要的四个jar包，poi.jar , poi-ooxml.jar , poi-ooxml-schemas.jar , xmlbeans.jar，项目结构如下：

        

二、新建java类com.excel.ImportExcelFile.java，代码如下：

package com.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 导入excel文件
 * @author Administrator
 *
 */
public class ImportExcelFile {

    private int totleRows = 0;//总行数
    
    private int totleCols = 0;//总列数
    
    public String filePath;
    
    private List<List<String>> dataList = null;
    
    public static void main(String[] args) {
        
        File file = new File("D:/ExcelDemo.xls");
        
        List<List<String>> dataList = new ImportExcelFile().readFile(file);
        
        for(int i=0;i<dataList.size();i++) {
            
            System.out.print("第"+i+"行:");
            
            List<String> rowsList = dataList.get(i);
            
            for(int j=0;j<rowsList.size();j++) {
                
                String colsStr = rowsList.get(j);
                
                if(colsStr != null && !colsStr.equals("")) {
                    
                    System.out.print("|"+colsStr);
                }
            }
            
            System.out.println();
        }
    }
    /**
     * 判断读取文件是否是excel文件格式
     */
    public boolean isExcel(File file) {
        
        String suffix = file.getName();
        
        suffix = suffix.toUpperCase();
        
        if(suffix.endsWith("XLS") || suffix.endsWith("XLSX")) {
            
            return true;
        }else {
            
            System.out.println("====log:该文件不符合excel文件格式");
        }
        
        return false;
    }
    /**
     * 文件是否存在
     * @param file
     * @return
     */
    public boolean isExists(File file) {
        
        if(file.exists() && file != null) {
            
            return true;
        }else {
            
            System.out.println("====log:读取文件不存在");
        }
        
        return false;
    }
    /**
     * 读取文件
     * @param file
     * @param in
     */
    public List<List<String>> readFile(File file) {
        
        InputStream in = null;
        
        String cellValue = "";
        
        if(isExists(file)) {
            
            if(isExcel(file)) {
                
                try {
                    
                     in = new FileInputStream(file);
                    
                    dataList = new ArrayList<List<String>>();
                    
                    Workbook wookBook = new HSSFWorkbook(in);
                    
                    Sheet sheet1 = wookBook.getSheetAt(0);
                    
                    totleRows = sheet1.getPhysicalNumberOfRows();
                    
                    for(int i=0;i<totleRows;i++) {
                        
                        Row row = sheet1.getRow(i);
                        
                        if(row == null) {
                            
                            continue;
                        }
                        
                        totleCols = row.getPhysicalNumberOfCells();
                        
                        List<String> colsList = new ArrayList<String>();
                        
                        for(int j=0;j<totleCols+1;j++) {
                            
                            Cell cell = row.getCell(j);
                            
                            if(cell != null) {
                                
                                if(cell.getCellStyle().getDataFormat() == 58) {
                                    
                                    SimpleDateFormat sf = new SimpleDateFormat("MM月dd日");
                                    
                                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                                    
                                    cellValue = sf.format(date);
                                }else {
                                    
                                    switch(cell.getCellType()) {
                                    
                                    case HSSFCell.CELL_TYPE_NUMERIC://number类型
                                        
                                        if(HSSFDateUtil.isCellDateFormatted(cell)) {
                                            
                                            SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM-dd");
                                            
                                            Date date = cell.getDateCellValue();
                                            
                                            cellValue = sf.format(date);
                                        }else {
                                            
                                            DecimalFormat decimalFormat = new DecimalFormat("#.#");
                                            
                                            cellValue = decimalFormat.format(cell.getNumericCellValue());
                                        }
                                        
                                        break;
                                        
                                    case HSSFCell.CELL_TYPE_STRING:
                                        
                                        cellValue = cell.getStringCellValue();
                                        
                                        break;
                                        
                                    case HSSFCell.CELL_TYPE_BOOLEAN:
                                        
                                        cellValue = String.valueOf(cell.getBooleanCellValue());
                                        
                                        break;
                                        
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        
                                        cellValue = cell.getCellFormula().toString();
                                        
                                        break;
                                        
                                    case HSSFCell.CELL_TYPE_ERROR:
                                        
                                        cellValue = "非法字符";
                                        
                                    case HSSFCell.CELL_TYPE_BLANK:
                                        
                                        cellValue = "";
                                        
                                        break;
                                        
                                    default:
                                        
                                        cellValue = "未知故障";
                                        
                                        break;
                                    }
                                }
                            }else {
                                
                                cellValue = "";
                            } 
                            
                            colsList.add(cellValue);
                        }
                        
                        dataList.add(colsList);
                    }
                } catch (IOException e) {
                    
                    e.printStackTrace();
                }finally {
                    
                    try {
                        
                        in.close();
                    } catch (IOException e) {
                        
                        e.printStackTrace();
                    }
                }
            }
        }
        
        return dataList;
    }
}

三、读取本地D盘下面的文件ExcelDemo.xls，结果如下：