解析Excel

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;
import org.springframework.web.multipart.MultipartFile;

/**
 * 解析Excel
 * @author zp
 * @date   2018年10月19日
 */
public class ExcelUtil {
	
	public static void main(String[] args) throws InvalidFormatException, IOException {
		
		List<List<String>> list = readExcel(new File("C:\\Users\\Administrator\\Desktop\\自动询价记录.xlsx"));
	    for(List li: list){
	    	System.out.println(li);
	    }
	}
	
	/**
	 * 读取并解析excel
	 * @return List<List<String>>
	 * @throws IOException 
	 * 
	 */
	public static List<List<String>> readExcel(File file) throws InvalidFormatException, IOException{
		List<List<String>> outterList = null;
		//判断文件是否存在
		if(file.isFile()&&file.exists()){
			outterList =new ArrayList<List<String>>();
	    //判断excel类型
		  Workbook wb = checkExcel(file);
		  int numberSheets = wb.getNumberOfSheets();
		  for(int sheetIndex=0;sheetIndex<numberSheets;sheetIndex++){
			  Sheet sheet = wb.getSheetAt(sheetIndex);
			  if(sheet!=null){
				  int rowNum = sheet.getLastRowNum();
				  List<String> interList = null;
				  for(int rowIndex=0;rowIndex<rowNum;rowIndex++){
					 Row row = sheet.getRow(rowIndex);
					 if(row!=null){
						int cellNum =  row.getLastCellNum();
					    interList = new ArrayList<String>();
						for(int cellIndex = 0;cellIndex<cellNum;cellIndex++){
							Cell cell = row.getCell(cellIndex);
							if(cell!=null){
								interList.add(cell.toString());
							}else{
								interList.add(null);
							}
						}
					 }
					 outterList.add(interList);
				  }
			  }
		  }
		}
		return outterList;
	}
	
	/**
	 * 根据Excel后缀名判断Excel
	 * @param file
	 * @return Workbook
	 * @throws IOException
	 * 
	 */
	public static Workbook checkExcel(File file) throws IOException, InvalidFormatException{
		String[] str = file.getAbsolutePath().split("\\.");
		Workbook wb;
		if("xls".equals(str[1])){
			 FileInputStream fis = new FileInputStream(file);   //文件流对象
             wb = new HSSFWorkbook(fis);
		}else if("xlsx".equals(str[1])){
			 wb = new XSSFWorkbook(file);
		}else{
			wb =null;
		}
		return wb;
	}
	
	/**
	 * 读取excel
	 * @return
	 * List<List<String>>
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 */
	public static List<List<String>> readExcel(MultipartFile file) throws InvalidFormatException, IOException{
		List<List<String>> outterList = null;
		//判断文件是否存在
		if(file!=null){
			outterList =new ArrayList<List<String>>();
	    //判断excel类型
		  Workbook wb = checkExcel(file);
		  int numberSheets = wb.getNumberOfSheets();
		  for(int sheetIndex=0;sheetIndex<numberSheets;sheetIndex++){
			  Sheet sheet = wb.getSheetAt(sheetIndex);
			  if(sheet!=null){
				  int rowNum = sheet.getLastRowNum();
				  List<String> interList = null;
				  for(int rowIndex=0;rowIndex<rowNum;rowIndex++){
					 Row row = sheet.getRow(rowIndex);
					 if(row!=null){
						int cellNum =  row.getLastCellNum();
					    interList = new ArrayList<String>();
						for(int cellIndex = 0;cellIndex<cellNum;cellIndex++){
							Cell cell = row.getCell(cellIndex);
							if(cell!=null){
								interList.add(cell.toString());
							}else{
								interList.add(null);
							}
						}
					 }
					 outterList.add(interList);
				  }
			  }
		  }
		}
		return outterList;
	}

	/**
	 * 根据Excel后缀名判断Excel
	 * @param file
	 * @return Workbook
	 * @throws IOException
	 *
	 */
	public static Workbook checkExcel(MultipartFile file) throws IOException, InvalidFormatException{
		String[] str = file.getName().split("\\.");
		Workbook wb;
		InputStream fis = file.getInputStream();   //流对象
		if("xls".equals(str[1])){
             wb = new HSSFWorkbook(fis);
		}else if("xlsx".equals(str[1])){
			 wb = new XSSFWorkbook(fis);
		}else{
			wb =null;
		}
		return wb;
	}
}

 

转载于:https://my.oschina.net/zzp123456/blog/2088028