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"); } } } }}