POI解析Excel,最高支持2010版本的Excel导入数据库_JAVA_编程开发_程序员俱乐部

class="java">
package com.sddl.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author Snail
 */

public class ImportExcelUtil {

	// 导入Excel
	public List<ArrayList<ArrayList<Object>>> ImportExcel(String excelName) {
		List<ArrayList<ArrayList<Object>>> resList = new ArrayList<ArrayList<ArrayList<Object>>>();
		if (excelName.substring(excelName.lastIndexOf(".")).equals(".xls")) {
			resList = this.ImportExcel2003(excelName);
		}
		if (excelName.substring(excelName.lastIndexOf(".")).equals(".xlsx")) {
			resList = this.ImportExcel2010(excelName);
		}
		//返回List给Dao插入数据库
		return resList;
	}

	// Offce2007&↓↓
	public List<ArrayList<ArrayList<Object>>> ImportExcel2003(String excelName) {
		List<ArrayList<ArrayList<Object>>> sheetList = new ArrayList<ArrayList<ArrayList<Object>>>();
		ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
		ArrayList<Object> cellList = new ArrayList<Object>();
		try {
			FileInputStream ipts = new FileInputStream(excelName);
			HSSFWorkbook hswk = new HSSFWorkbook(ipts);
			// 循环取出sheet
			int sheetTotal = hswk.getNumberOfSheets();
			for (int i = 0; i < sheetTotal; i++) {
				HSSFSheet xss = hswk.getSheetAt(i);
				if (null == xss) {
					continue;
				} else {
					int rowTotal = xss.getLastRowNum();
					// 循环取出行
					for (int j = 0; j <= rowTotal; j++) {
						HSSFRow xsr = xss.getRow(j);
						if (null == xsr) {
							continue;
						} else {
							int cellTotal = xsr.getLastCellNum();
							for (int k = 0; k < cellTotal; k++) {
								HSSFCell xsc = xsr.getCell(k);
//								if ((Cell.CELL_TYPE_BLANK)==xsc.getCellType()) {
//									cellList.add("");
//								}
//
//								if ((Cell.CELL_TYPE_STRING)==xsc.getCellType()) {
									cellList.add(xsc);
//								}
//
//								if ((Cell.CELL_TYPE_NUMERIC)==xsc.getCellType()) {
//									cellList.add(xsc + "");
//								}

							}
							rowList.add(cellList);
						}

					}
					sheetList.add(rowList);
				}
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(sheetList.get(0).get(0).toString());
		return sheetList;
	}

	// office2010
	public List<ArrayList<ArrayList<Object>>> ImportExcel2010(String excelName) {
		List<ArrayList<ArrayList<Object>>> sheetList = new ArrayList<ArrayList<ArrayList<Object>>>();
		ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
		ArrayList<Object> cellList = new ArrayList<Object>();
		try {
			FileInputStream ipts = new FileInputStream(excelName);
			XSSFWorkbook xswk = new XSSFWorkbook(ipts);
			// 循环取出sheet
			int sheetTotal = xswk.getNumberOfSheets();
			for (int i = 0; i < sheetTotal; i++) {
				XSSFSheet xss = (XSSFSheet) xswk.getSheetAt(i);
				if (null == xss) {
					continue;
				} else {
					int rowTotal = xss.getLastRowNum();
					// 循环取出行
					for (int j = 0; j <= rowTotal; j++) {
						XSSFRow xsr = xss.getRow(j);
						if (null == xsr) {
							continue;
						} else {
							int cellTotal = xsr.getLastCellNum();
							for (int k = 0; k < cellTotal; k++) {
								XSSFCell xsc = xsr.getCell(k);
//								if ((Cell.CELL_TYPE_BLANK)==xsc.getCellType()) {
//									cellList.add("");
//								}
//
//								if ((Cell.CELL_TYPE_STRING)==xsc.getCellType()) {
									cellList.add(xsc);
//								}
//
//								if ((Cell.CELL_TYPE_NUMERIC)==xsc.getCellType()) {
//									cellList.add(xsc + "");
//								}
							}
							rowList.add(cellList);
						}

					}
					sheetList.add(rowList);
				}
			}

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(sheetList.get(0).get(0).toString());
		return sheetList;
	}
}

package com.sddl.util;

public class test {

	/**
	 * @author Snail
	 */
	public static void main(String[] args){
		ImportExcelUtil e = new ImportExcelUtil();
		e.ImportExcel("c://scjybcldr.xlsx");
	}
}

• POI__xmlbeans.rar (7.8 MB)
• 下载次数: 0