使用POI读取Excel表格导入

使用POI读取Excel表格导入

1.封装一个utils类

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ImportExcel {

	// abc.xls
	public static boolean isXls(String fileName){
		// (?i)忽略大小写
		if(fileName.matches("^.+\\.(?i)(xls)$")){
			return true;
		}else if(fileName.matches("^.+\\.(?i)(xlsx)$")){
			return false;
		}else{
			throw new RuntimeException("格式不对");
		}
	}

	public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) throws Exception{
		
		boolean ret = isXls(fileName);
		Workbook workbook = null;
		// 根据后缀创建不同的对象
		if(ret){
			workbook = new HSSFWorkbook(inputStream);
		}else{
			workbook = new XSSFWorkbook(inputStream);
		}
		Sheet sheet = workbook.getSheetAt(0);
		// 得到标题行
		Row titleRow = sheet.getRow(0);
		
		int lastRowNum = sheet.getLastRowNum();
		int lastCellNum = titleRow.getLastCellNum();
		
		List<Map<String, Object>> list = new ArrayList<>();
		
		for(int i = 1; i <= lastRowNum; i++ ){
			Map<String, Object> map = new HashMap<>();
			Row row = sheet.getRow(i);
			for(int j = 0; j < lastCellNum; j++){
				// 得到列名
				String key = titleRow.getCell(j).getStringCellValue();
				Cell cell = row.getCell(j);
				cell.setCellType(CellType.STRING);
				
				map.put(key, cell.getStringCellValue());
			}
			list.add(map);
		}
		
		workbook.close();
		return list;
		
		
	}
}

2.在controller层调用

import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;

@Controller
public class ImporExcelController {

	@Autowired
	private SysUserService userService = null;
	
	
	@RequestMapping("/import.do")
	@ResponseBody
	public JsonBean importExcel(@RequestParam MultipartFile excelFile){
		
		
		try {
			String fileName = excelFile.getOriginalFilename();
			// 获取上传文件的输入流
			InputStream inputStream = excelFile.getInputStream();
			// 调用工具类中方法，读取excel文件中数据
			List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);
			
			// 将对象先转为json格式字符串，然后再转为List<SysUser> 对象
			ObjectMapper objMapper = new ObjectMapper();
			String infos = objMapper.writeValueAsString(sourceList);
			
			// json字符串转对象
			List<SysUser> list = objMapper.readValue(infos, new TypeReference<List<SysUser>>() {});
			
			// 批量添加
			userService.addUserBatch(list);
			
			return JsonUtils.createJsonBean(1, null);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			return JsonUtils.createJsonBean(0, e.getMessage());
		}
		
	}
}