读取上传到服务器上的excel文档并保存到数据库

读取Excel数据，将新的数据封装到Map集合中

package com.common.qr.util;
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.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * 读取Excel数据方法二,map封装数据
 * @author zheng
 * 可以读取xls和xlsx格式的文件
 */
public class ReadExcelUtils {
	
	public static <T> List<Map<String, String>> readExcel(String path) {
       // Map<String, List<? extends T>> map = new HashMap<String, List<? extends T>>();
        File file = new File(path);
        FileInputStream fis = null;
        Workbook workBook = null;
        List<Map<String, String>> list = new ArrayList<>();
        if (file.exists()) {
            try {
                fis = new FileInputStream(file);
                workBook = WorkbookFactory.create(fis);
                int numberOfSheets = workBook.getNumberOfSheets();
                for (int s = 0; s < numberOfSheets; s++) { // sheet工作表
                    Sheet sheetAt = workBook.getSheetAt(s);
                    //String sheetName = sheetAt.getSheetName(); //获取工作表名称
                    //int rowsOfSheet = sheetAt.getPhysicalNumberOfRows(); // 获取当前Sheet表的总行数
                    int lastRowNum = sheetAt.getLastRowNum();//获取除第一行之外的所有数据行数
                    for (int r = 1; r <= lastRowNum; r++) { // 循环遍历每行数据
                        // 每一行创建一个map
                        Map<String, String> map = new HashMap<String, String>();
                        Row row = sheetAt.getRow(r);
                        if (row == null) {
                            continue;
                        } else {
                        	//获取当前行
                            int rowNum = row.getRowNum();
                            //获取每行的列数
                            int numberOfCells = sheetAt.getRow(r).getPhysicalNumberOfCells();
                            for (int c = 0; c < numberOfCells; c++) { // 循环遍历每列数据
                                Cell cell = row.getCell(c);
                                if (cell == null) {
                                    continue;
                                } else {
                                    int cellType = cell.getCellType();
                                    switch (cellType) {
                                    case Cell.CELL_TYPE_STRING: // 代表文本String
                                        //String stringCellValue = row.getCell(c).getStringCellValue();
                                        map.put("hardwareName", sheetAt.getRow(rowNum).getCell(0).getStringCellValue());
                                        map.put("imei", sheetAt.getRow(rowNum).getCell(1).getStringCellValue());
                                        map.put("imsi", sheetAt.getRow(rowNum).getCell(2).getStringCellValue());
                                        map.put("hardwareType", sheetAt.getRow(rowNum).getCell(3).getStringCellValue());
                                        map.put("hydrantName", sheetAt.getRow(rowNum).getCell(4).getStringCellValue());
                                        map.put("hydrantNumber", sheetAt.getRow(rowNum).getCell(5).getStringCellValue());
                                        break;
                                    }
                                }
                            }//for循环列结束
                        }
                        // 每一行 map 组装完毕后 加入到 list 中
                        list.add(map);
                    }
                }
                // 打印 list
                System.out.println(Arrays.asList(list));
                if (fis != null) {
                    fis.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("文件不存在!");
        }
        return list;
    }
	/**
	 * 方法测试
	 * @param args
	 */
	public static void main(String[] args) {
		List<Map<String,String>> list = ReadExcelUtils.readExcel("D:\\apache-tomcat-8.0.53\\webapps\\uploadfile\\0123100115a9fb.xls");
		for (int i = 0; i < list.size(); i++) {
			Map<String, String> map = list.get(i);
			String hardwareName = map.get("hardwareName");
			String imei = map.get("imei");
			String imsi = map.get("imsi");
			String hardwareType = map.get("hardwareType");
			String hydrantName = map.get("hydrantName");
			String hydrantNumber = map.get("hydrantNumber");
			System.out.println(hardwareName+" "+imei+" "+imsi+" "+hardwareType+" "+hydrantName+" "+hydrantNumber);
			
		}
		
	}
}

保存到数据库

• Controller层

	/**
	 * 上传Excel文档,并解析当前上传的文档,保存到数据库
	 * @throws IOException 
	 * @throws IllegalStateException 
	 */
	@RequestMapping("/uploadfile.do")
	@ResponseBody
	public Map<String,String> uploadfile(@RequestParam("file") MultipartFile file,HttpServletRequest request) 
			throws IllegalStateException, IOException{
		String path = CommonFunction.uploadfileCommon(file, request);
		// path===D:\apache-tomcat-8.0.53\webapps/uploadfile\0122050106730e.xlsx
		System.out.println("path==="+path);
		hardwareQRCodeService.saveExcel(path);
		Map<String,String> map=new HashMap<String,String>();
		map.put("code", "0");
		map.put("msg","上传成功");
		return map;
	}

• Service层

public void saveExcel(String path) {
		HardwareQRCodeEntity hd=new HardwareQRCodeEntity();
		//获取Excel中的数据
		List<Map<String,String>> list = ReadExcelUtils.readExcel(path);
		for (int i = 0; i < list.size(); i++) {
			Map<String, String> mapdata = list.get(i);
			String hardwareName = mapdata.get("hardwareName");
			String imei = mapdata.get("imei");
			String imsi = mapdata.get("imsi");
			String hardwareType = mapdata.get("hardwareType");
			String hydrantName = mapdata.get("hydrantName");
			String hydrantNumber = mapdata.get("hydrantNumber");
			System.out.println(hardwareName+" "+imei+" "+imsi+" "+hardwareType+" "+hydrantName+" "+hydrantNumber);
			hd.setHardwareName(hardwareName);
			hd.setOnenetImei(imei);
			hd.setOnenetImsi(imsi);
			hd.setHardwareType(hardwareType);
			hd.setHardwareName(hardwareName);
			hd.setHydrantNumber(hydrantNumber);
			hd.setCreateDate(new Date());
			hardwareQRCodeMapper.insertHardwareQRCode(hd);	
		}