excel读取时，利用反射封装 完成列名可变的excel的导入，后续会对反射进行封装，对导入功能进行完善
package cn.com.mcd.service.impl;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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;
import org.drools.core.factmodel.Field;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSON;
import cn.com.mcd.dao.CustemerListMapper;
import cn.com.mcd.exception.rest.exhandler.DataBaseAccessException;
import cn.com.mcd.model.CustomerListMain;
import cn.com.mcd.service.CustemerListService;
import cn.com.mcd.util.Constants;
/**
* @author soya.song
* 2017.3.16
*
*/
@Service("custemerListService")
@SuppressWarnings("unused")
public class CustemerListServiceImpl implements CustemerListService{
private static final Logger log = LoggerFactory.getLogger(CustemerListServiceImpl.class);
static String sheetName="CLC";
@Resource
private CustemerListMapper custemerListMapper;
public static void main(String[] args) throws NoSuchFieldException, SecurityException, NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
// Class<CustomerListMain> c = CustomerListMain.class;
// //返回所有的方法
// Method m = c.getDeclaredMethod("set",int.class,String.class);
// m.setAccessible(true);//因为写成private 所以这里必须设置
// m.invoke(c, "soldto","你好");
// System.out.println("-----c="+c.toString() );
// CustomerListMain a = new CustomerListMain();
// java.lang.reflect.Field field = a.getClass().getDeclaredField("soldto");
// field.setAccessible(true);
// field.set(a, "1");
// //读取
// java.lang.reflect.Field f = a.getClass().getDeclaredField("soldto");
// f.setAccessible(true);
// System.out.println(f.get(a));
File file=new File("D:\\file\\nikeBuy.xlsx");
// File file=new File("D:/file/nikeBuy.xlsx");
CustemerListServiceImpl s=new CustemerListServiceImpl();
s.saveExcel(file);
}
/**
* 读取excel文件内容保存到数据库
* @throws SecurityException
* @throws NoSuchFieldException
* @throws IllegalAccessException
* @throws IllegalArgumentException
*/
@SuppressWarnings("rawtypes")
@Override
public Map<String, Object> saveExcel(File file) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
Map<Integer,String> titlMap=new HashMap<Integer,String>();
Map<String,Object> resultMap=new HashMap<String,Object>();
//获取excel文件
XSSFWorkbook xssfWorkbook;
List<Map<Object,Object>> list=new ArrayList<Map<Object,Object>>();
try {
FileInputStream fileStream=new FileInputStream(file);
BufferedInputStream buf=new BufferedInputStream(fileStream);
xssfWorkbook = new XSSFWorkbook(buf);
//获得sheet总数量
int sheetNums=xssfWorkbook.getNumberOfSheets();
//总行数
int rowTotalNum=0;
//总列数
int celTotalNum=0;
//一个行对象
XSSFRow xssfRow =null;
//一个列对象
XSSFCell xssfcell =null;
//循环遍历所有sheet
for (int numSheet = 0; numSheet < sheetNums; numSheet++) {
//查找sheet名为CLC的sheet
String sheetNam=xssfWorkbook.getSheetName(numSheet);
if(!sheetName.trim().equalsIgnoreCase(sheetNam)){
continue;
}
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
//获得总行数
rowTotalNum=xssfSheet.getLastRowNum();
// 读取每行数据封装到对象中
for (int rowNum = 0; rowNum < rowTotalNum; rowNum++) {
if(rowNum==0){//如果是第一行，读取每一个标题并记录序号，存储到map中
xssfRow = xssfSheet.getRow(rowNum);//行对象
celTotalNum= xssfRow.getLastCellNum();//总列数
//循环遍历每一行的所有列
for(int c=0;c<celTotalNum;c++){
xssfcell = xssfRow.getCell((short)c);
//将列名转为对象中的属性名
String strCel=xssfcell.toString().replaceAll("\\s*", "").replaceAll("_", "");
titlMap.put(c, strCel);
}
}
//读取非第一行的正文数据，将每一行的数据存储到对应属性的对象中
else{
xssfRow = xssfSheet.getRow(rowNum);//行对象
//一行使用一个对象
CustomerListMain customerListMain = new CustomerListMain();
//遍历该行的所有列
for(int c=0;c<celTotalNum;c++){
xssfcell = xssfRow.getCell((short)c);
//cell值（属性值）=========得到该列的值，转为String类型，以便于对象中存储
String strCel=xssfcell.toString();
//属性名==========根据该列序号，在map中对应的key中查找该序号下对应的属性名
String titlName=titlMap.get(c).toLowerCase();
//除id以外，其余属性均为String类型
if(!"id".equals(titlName)){
//利用返射，根据属性名，得到set方法，并为set赋值
java.lang.reflect.Field field = customerListMain.getClass().getDeclaredField(titlName);//根据属性名获取属性值
field.setAccessible(true);//属性为private属性设置为true
field.set(customerListMain, strCel);//为set方法赋值
//读取
java.lang.reflect.Field f = customerListMain.getClass().getDeclaredField(titlName);
f.setAccessible(true);
System.out.println("################..."+f.get(customerListMain)+"属性名："+titlName+"属性值："+strCel);
}
}
log.info(this.getClass().getName()+".saveExcel.反射赋值后，对象中数据为："+JSON.toJSONString(customerListMain.toString()));
System.out.println(this.getClass().getName()+".saveExcel.反射赋值后，对象中数据为："+JSON.toJSONString(customerListMain.toString()));
}
// XSSFRow xssfRow = xssfSheet.getRow(rowNum);
// if (xssfRow != null) {
// Map<Object,Object> map=new HashMap<Object,Object>();
// //student = new Student();
// XSSFCell cell1 = xssfRow.getCell(rowNum);
// map.put(rowNum, cell1!= null ? cell1.toString().trim():"");
//// XSSFCell cell2 = xssfRow.getCell(1);
//// XSSFCell cell3 = xssfRow.getCell(2);
//// XSSFCell cell4 = xssfRow.getCell(3);
//// student.setNo(getValue(no));
//// student.setName(getValue(name));
//// student.setAge(getValue(age));
//// student.setScore(Float.valueOf(getValue(score)));
//// list.add(student);
// }
}
}
} catch (IOException e) {
log.error("#################"+this.getClass().getName()+".excelToTxt.exception.........excel文件存入数据库失败"+e);
throw new DataBaseAccessException(Constants.SERVICE_FILE_UPLOAD_CODE+"\n\t"+Constants.FILE_EXCELTODB_ERROR+e);//5001 excel文件存入数据库失败
}
return resultMap;
}
/**
* 因excel的标题与位置会变，所以在此需要做一个固定标识
* @param xssfcell
*/
private void setTile(String xssfcell,int index,Object model) {
if(xssfcell!=null && !"".equals(xssfcell)){
java.lang.reflect.Field[] field = model.getClass().getDeclaredFields(); //获取实体类的所有属性，返回Field数组
for(int j=0 ; j<field.length ; j++){ //遍历所有属性
String name = field[j].getName(); //获取属性的名字
//不对id进行比较
if(j==0){
continue;
}
//比较对象属性与excel列名。如果去除空格，忽略大小写，一样的情况，将该列的序号
if(xssfcell.equalsIgnoreCase(name)){//列名与属性一致
System.out.println("进入 if");
}else{
System.out.println("未进 if");
}
}
}
}
}
