POI使用

1.导入依赖

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
</dependency>

2.使用代码

package com.xin.service;

import com.xin.mapper.OrderMapper;
import com.xin.pojo.Order;
import com.xin.utils.Utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;

    public int inserOrder(Order order){
     return  orderMapper.insertOrder(order);
    }
    public String sentFile(MultipartFile file,String fileName) throws Exception{
        List<String> attributeName = new ArrayList<>();
        attributeName.add("orderNo");
        attributeName.add("orderFaNo");
        attributeName.add("orderMoney");
        attributeName.add("orderRecMoney");
        attributeName.add("syssn");
        attributeName.add("createdAt");
        if (!fileName.matches("^.+\\.(?i)(xlt)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            System.out.println(("上传文件格式不正确"));
            return "上传文件格式不正确";
        } else {
            Sheet sheet = this.checkExcel(fileName,file);

            if (sheet == null || sheet.getLastRowNum() == 1) {
                return "文件为空";
            }else {
                List<Order> ordersList =new ArrayList<>();
                int maxCellNumber=0;
                int maxRowNumber= sheet.getLastRowNum();//获取sheet的总行数
                for (int currentRowNumber=3;currentRowNumber<maxRowNumber;currentRowNumber++ ){
                    Map<String,Object> order=new HashMap<>();
                    Row row=sheet.getRow(currentRowNumber);//行
                    maxCellNumber=row.getLastCellNum();//列的总数
                    for (int currentCellNumber=0;currentCellNumber<maxCellNumber;currentCellNumber++){
                        Cell cell=row.getCell(currentCellNumber); //从0列开始遍历
                        if (cell!=null){
                            if (currentCellNumber==0){//序号
                                //order.put(attributeName.get(0),getCellVaule(cell));

                            }
                            if (currentCellNumber==1){ //对账单日期

                                // order.put(attributeName.get(1),getCellVaule(cell));
                            }
                            if (currentCellNumber==2){//交易时间
                              //  SimpleDateFormat format = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");

                                //System.err.println(cell.getStringCellValue());
                                order.put(attributeName.get(5),getCellVaule(cell));

                                // order.put(attributeName.get(2),getCellVaule(cell));
                            }
                            if (currentCellNumber==3){//终端编号

                                // order.put(attributeName.get(2),getCellVaule(cell));
                            }
                            if (currentCellNumber==4){//工行订单号  syssn
                                order.put(attributeName.get(4),getCellVaule(cell));
                                //order.put(attributeName.get(4),getCellVaule(cell));
                            }
                            if (currentCellNumber==5){//第三方订单号
                                order.put(attributeName.get(1),getCellVaule(cell));
                                // order.put(attributeName.get(5),getCellVaule(cell));
                            }
                            if (currentCellNumber==6){ //商户订单号
                                order.put(attributeName.get(0),getCellVaule(cell));
                                // order.put(attributeName.get(6),getCellVaule(cell));
                            }
                            if (currentCellNumber==7){//交易类型

                           //     System.err.println(attributeName.get(4)+"ssssssssss"+getCellVaule(cell));
                            }
                            if (currentCellNumber==8){//支付类型

                                // order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==9){//支付方式

                                // order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==10){//商品附加信息

                                // order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==11){//付款银行

                                // order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==13){//卡号

                                //order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==15){//订单总金额
                                order.put(attributeName.get(2),getCellVaule(cell));
                                //  order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==16){//实付金额
                                order.put(attributeName.get(3),getCellVaule(cell));
                                //System.err.println(attributeName.get(3)+"ssssssssss"+getCellVaule(cell));
                                //   order.put(attributeName.get(7),getCellVaule(cell));
                            }
                            if (currentCellNumber==17){//手续费
                                //   order.put(attributeName.get(7),getCellVaule(cell));
                            }
                        }
                    }
                    ordersList.add(Utils.toBean(order,Order.class));
                }
                for (Order o:ordersList
                     ) {
                    System.out.println("orderno=="+o.getOrderNo());
                    System.out.println("ordermoney=="+o.getOrderRecMoney());
                    System.out.println("goodsName=="+o.getGoodsName());
                    System.err.println(o.getCreatedAt());
                }
                orderMapper.insertOrderList(ordersList);
            }

        }

        return  "上传成功！";
    }



    public Sheet checkExcel(String fileName, MultipartFile file) throws Exception {
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        return wb.getSheetAt(0);
    }

    public static String getCellVaule(Cell cell){
        if (cell==null){
            return "";
        }
        else {
            return cell.getStringCellValue();
        }
    }
}

工具类

package com.xin.utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.Converter;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class Utils {
	public static String uuid() {
		return UUID.randomUUID().toString().replace("-", "").toUpperCase();
	}
	
	public static <T> T toBean(Map map, Class<T> c) {
		T bean = null;
		try {
			bean = c.newInstance();
			ConvertUtils.register(new Converter() {
				public Object convert(Class type, Object value) {
					if (type != Date.class) {
						return null;
					}
					String val = (String) value;
					if(val == null || val.trim().isEmpty()){
						return null;
					}
					SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
					try {
						return sdf.parse(val);
					} catch (ParseException e) {
						throw new RuntimeException(e);
					}
				}
			}, Date.class);
			BeanUtils.populate(bean, map);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bean;
	}

	public static <T> Object[] toObjects(T t) throws Exception {
		Class c = t.getClass();
		Field[] fs = c.getDeclaredFields();
		Object[] obs = new Object[fs.length];
		for (int i = 0; i < fs.length; i++) {
			fs[i].setAccessible(true);
			obs[i] = fs[i].get(t);
		}
		return obs;
	}
/*
	public static Map getParameterMap(ServletContext application,
                                      HttpServletRequest request, String fName) {
		Map<String, String> map = new HashMap<String, String>();
		// 无情三连 得到好多好多个FileItem
		DiskFileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload sfu = new ServletFileUpload(factory);
		sfu.setHeaderEncoding("utf-8");
		try {
			List<FileItem> items = sfu.parseRequest(request);
			// 把浏览器上传过来的图片保存到pictures文件夹下
			for (FileItem f : items) {
				// 普通表单项如何处理
				if (f.isFormField()) {
					// 把属性名，属性的值给它保存到map集合中
					String str = map.get(f.getFieldName());
					if (str == null) {
						map.put(f.getFieldName(), f.getString("utf-8"));
					} else {
						map.put(f.getFieldName(),
								str + "," + f.getString("utf-8"));
					}

				}
				// 文件表单项如何处理
				else {
					if(f.getName()!=null && !f.getName().trim().isEmpty()){
						String[] strs = f.getName().split("\\.");
						String string = "." + strs[strs.length - 1];
						String fileName = com.lianfu.yuehuoli.util.Utils.uuid() + string;
						// savePath
						// D:\apache-tomcat-7.0.72-windows-x64\apache-tomcat-7.0.72\webapps\day12_pic\pictures
						String savePath = application.getRealPath(fName);
						File file = new File(savePath, fileName);
						f.write(file);
						String contextPath = application.getContextPath();

						// 保存将来的存储路径（/项目名/文件夹名/图片名）
						map.put(f.getFieldName(), contextPath + "/" + fName + "/"
								+ fileName);
					}
					
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}*/

}