JAVA使用POI读取简单EXCEL数据

JAVA使用POI动态读取简单EXCEL数据

改Java用于动态读取一些简单的Excel的数据, 复杂的表格未测试过。个人笔记,自用勿喷!

package com.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;


/**
 * 读取EXCLE中表名称
 * @author LG
 *
 */
public class ReadExcel {
	
	/**
	 * 根据文件名、sheel名读取excel中信息
	 * @param fileName 文件名
	 * @param sheel
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String,Object>> readXls(String fileName, String sheel) throws Exception {		
		String path = "D:\\work\\" + fileName;
		Workbook workbook = null;
        InputStream input = new FileInputStream(path);
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        boolean is2003 = true;
        //判断是否为2003版Excel
        if (!fileName.endsWith("xls")) {
        	is2003 = false;
		}
        if (is2003 == true) {
        	workbook = new HSSFWorkbook(input);
		} else {
			workbook = new XSSFWorkbook(input);
		}
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            String sheetName = sheet.getSheetName();
            if (sheetName.trim().equals(sheel)) {
            	// 循环行Row
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                    	Map<String, Object> map = new HashMap<String, Object>();
                    	for (int i = 0; i < row.getLastCellNum(); i++) {
							Cell cell = row.getCell(i);
							String location = "R" +  rowNum + "C" + i ;
							map.put(location, getValue(cell));
						}                    	
                    	list.add(map);
                    }
               }
			}
                       
        }
       return list;
    }
	

	/**
	 * 转换格式
	 * @param Cell
	 * @return
	 */
	private static String getValue(Cell cell) {
		  if (null != cell) {   
              switch (cell.getCellType()) {   
              case HSSFCell.CELL_TYPE_NUMERIC: // 数字   
                  return String.valueOf(cell.getNumericCellValue()) ;   
              case HSSFCell.CELL_TYPE_STRING: // 字符串   
                  return String.valueOf(cell.getStringCellValue()) ; 
              case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                  return String.valueOf(cell.getBooleanCellValue()) ;   
              case HSSFCell.CELL_TYPE_FORMULA: // 公式    
                  return String.valueOf(cell.getCellFormula()) ;  
              case HSSFCell.CELL_TYPE_BLANK: // 空值   
            	  return "" ;   
              case HSSFCell.CELL_TYPE_ERROR: // 故障   
                  return "类型错误" ;   
              default:   
            	  return "未知类型" ;   
              }   
          } else {   
              return "类型为空";  
          }   
    }

	
	
	
	public static void main(String [] args) {
		String fileName = "XXX.xls";
		String sheel = "Sheet1";
		ReadExcel excel = new ReadExcel();
		try {
			String str = "''";
			List<Map<String,Object>> list = excel.readXls(fileName,sheel);
			for (Map<String, Object> map : list) {
				for (Entry<String, Object> entry : map.entrySet()) {
					String aa= (String) entry.getKey();
					String bb= (String) map.get(aa);
				}
					
			}
			System.out.println(str);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

}

发布了5 篇原创文章 · 获赞 0 · 访问量 59
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览