/**
*
*/
package c123;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author Administrator
*
*/
public class ReadExcelToMaps {
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:文件路径 得到 excel Mpa数组
* @param:文件File 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel(String filePath,String[]fileds)throws IOException{
Boolean isFile=filePath.endsWith(".xlsx");
File file = new File(filePath);
Map<String, String>[] maps=null;
if(isFile){
maps=getMapsFExcel_XLSX_2007(file, fileds); // 获取工作薄个数
}else {
maps=getMapsFExcel_XLSX_2003(file, fileds);
}
return maps;
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2003文件 得到 excel Mpa数组
* @param:文件File 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLSX_2003 (File file,String[]fileds) throws IOException{
FileInputStream fileInputStream= new FileInputStream(file);
return getMapsFExcel_XLS_2003(fileInputStream, fileds);
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2007文件 得到 excel Mpa数组
* @param:文件File 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLSX_2007 (File file,String[]fileds) throws IOException{
FileInputStream fileInputStream= new FileInputStream(file);
return getMapsFExcel_XLSX_2007(fileInputStream, fileds);
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2007文件输入流 得到数据
* @param:文件如入流FileInputStream 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLSX_2007 (FileInputStream fileInputStream,String[]fileds ) throws IOException{
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Sheet sheet =null;
Workbook wb=null;
wb=new XSSFWorkbook(fileInputStream); //2007
sheet = (XSSFSheet) wb.getSheetAt(0);// 获取工作薄个数
int rowNum = sheet.getLastRowNum();// 获取工作薄行数
Row row =sheet.getRow(1);
//检测异常
try {
new Check(fileds, row);
} catch (CheckExceFiled e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
for (int i = 1; i <= rowNum; i++) {
Map<String, String> inPutlistMap= new LinkedHashMap<String, String>();
row = sheet.getRow(i);
int j = 0;
for (int k = 0; k < fileds.length; k++) {
Cell cell=row.getCell(j);
String cellValue="";
System.out.println(cell.getCellType());
if(cell.getCellType()==0){
cellValue=String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType()==1) {
cellValue= cell.getStringCellValue();
}else if(cell.getCellType()==3) {
cellValue="";
}
inPutlistMap.put(fileds[k].toString(), cellValue);
j++;
}
list.add(inPutlistMap);
}
fileInputStream.close();
Map<String,String>[] arr=list.toArray(new HashMap[list.size()]);
return arr;
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2003文件输入流 得到数据
* @param:文件如入流FileInputStream 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLS_2003(FileInputStream fileInputStream,String[]fileds ) throws IOException{
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
Sheet sheet =null;
Workbook wb=null;
wb= new HSSFWorkbook(fs);
sheet=(HSSFSheet)wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();// 获取工作薄行数
Row row =sheet.getRow(1);
//检测异常
try {
new Check(fileds, row);
} catch (CheckExceFiled e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
for (int i = 1; i <= rowNum; i++) {
Map<String, String> inPutlistMap= new LinkedHashMap<String, String>();
row = sheet.getRow(i);
int j = 0;
for (int k = 0; k < fileds.length; k++) {
row.getLastCellNum();
Cell cell=row.getCell(j);
String cellValue="";
if(cell==null){
}else if(cell.getCellType()==0){
cellValue=String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType()==1) {
cellValue= cell.getStringCellValue();
}else if(cell.getCellType()==3) {
cellValue="";
}
inPutlistMap.put(fileds[k].toString(), cellValue);
j++;
}
list.add(inPutlistMap);
}
fileInputStream.close();
Map<String,String>[] arr=list.toArray(new HashMap[list.size()]);
return arr;
}
//自定义异常
class CheckExceFiled extends Exception{
public CheckExceFiled(String msg){
super(msg);
}
}
class Check {
public Check(String[]fields,Row row) throws CheckExceFiled {
// TODO Auto-generated constructor stub
for (String filed : fields) {
if(filed.trim().equals("")){
throw new CheckExceFiled("有为空的字段");//抛出异常
}
}
if (fields.length<=0) {
throw new CheckExceFiled("必须输入有效字段");//抛出异常
}
if(fields.length>row.getLastCellNum()){
throw new CheckExceFiled("输入的字段多于Excel表中每行数据");
}
}
}
}
*
*/
package c123;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author Administrator
*
*/
public class ReadExcelToMaps {
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:文件路径 得到 excel Mpa数组
* @param:文件File 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel(String filePath,String[]fileds)throws IOException{
Boolean isFile=filePath.endsWith(".xlsx");
File file = new File(filePath);
Map<String, String>[] maps=null;
if(isFile){
maps=getMapsFExcel_XLSX_2007(file, fileds); // 获取工作薄个数
}else {
maps=getMapsFExcel_XLSX_2003(file, fileds);
}
return maps;
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2003文件 得到 excel Mpa数组
* @param:文件File 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLSX_2003 (File file,String[]fileds) throws IOException{
FileInputStream fileInputStream= new FileInputStream(file);
return getMapsFExcel_XLS_2003(fileInputStream, fileds);
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2007文件 得到 excel Mpa数组
* @param:文件File 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLSX_2007 (File file,String[]fileds) throws IOException{
FileInputStream fileInputStream= new FileInputStream(file);
return getMapsFExcel_XLSX_2007(fileInputStream, fileds);
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2007文件输入流 得到数据
* @param:文件如入流FileInputStream 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLSX_2007 (FileInputStream fileInputStream,String[]fileds ) throws IOException{
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Sheet sheet =null;
Workbook wb=null;
wb=new XSSFWorkbook(fileInputStream); //2007
sheet = (XSSFSheet) wb.getSheetAt(0);// 获取工作薄个数
int rowNum = sheet.getLastRowNum();// 获取工作薄行数
Row row =sheet.getRow(1);
//检测异常
try {
new Check(fileds, row);
} catch (CheckExceFiled e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
for (int i = 1; i <= rowNum; i++) {
Map<String, String> inPutlistMap= new LinkedHashMap<String, String>();
row = sheet.getRow(i);
int j = 0;
for (int k = 0; k < fileds.length; k++) {
Cell cell=row.getCell(j);
String cellValue="";
System.out.println(cell.getCellType());
if(cell.getCellType()==0){
cellValue=String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType()==1) {
cellValue= cell.getStringCellValue();
}else if(cell.getCellType()==3) {
cellValue="";
}
inPutlistMap.put(fileds[k].toString(), cellValue);
j++;
}
list.add(inPutlistMap);
}
fileInputStream.close();
Map<String,String>[] arr=list.toArray(new HashMap[list.size()]);
return arr;
}
/**
* @MethodName: menglei
* @Time:2012-3-1 下午4:06:45
* @Description:由excel2003文件输入流 得到数据
* @param:文件如入流FileInputStream 所需字段
* @return:
* @throws:
*/
public Map<String, String>[] getMapsFExcel_XLS_2003(FileInputStream fileInputStream,String[]fileds ) throws IOException{
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
Sheet sheet =null;
Workbook wb=null;
wb= new HSSFWorkbook(fs);
sheet=(HSSFSheet)wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();// 获取工作薄行数
Row row =sheet.getRow(1);
//检测异常
try {
new Check(fileds, row);
} catch (CheckExceFiled e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
for (int i = 1; i <= rowNum; i++) {
Map<String, String> inPutlistMap= new LinkedHashMap<String, String>();
row = sheet.getRow(i);
int j = 0;
for (int k = 0; k < fileds.length; k++) {
row.getLastCellNum();
Cell cell=row.getCell(j);
String cellValue="";
if(cell==null){
}else if(cell.getCellType()==0){
cellValue=String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType()==1) {
cellValue= cell.getStringCellValue();
}else if(cell.getCellType()==3) {
cellValue="";
}
inPutlistMap.put(fileds[k].toString(), cellValue);
j++;
}
list.add(inPutlistMap);
}
fileInputStream.close();
Map<String,String>[] arr=list.toArray(new HashMap[list.size()]);
return arr;
}
//自定义异常
class CheckExceFiled extends Exception{
public CheckExceFiled(String msg){
super(msg);
}
}
class Check {
public Check(String[]fields,Row row) throws CheckExceFiled {
// TODO Auto-generated constructor stub
for (String filed : fields) {
if(filed.trim().equals("")){
throw new CheckExceFiled("有为空的字段");//抛出异常
}
}
if (fields.length<=0) {
throw new CheckExceFiled("必须输入有效字段");//抛出异常
}
if(fields.length>row.getLastCellNum()){
throw new CheckExceFiled("输入的字段多于Excel表中每行数据");
}
}
}
}