2018-10-17 01:09:44 qq_41594146 阅读数 113
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1263 人正在学习 去看看 钟林森

上传文件的时候要特别注意单元格内的值属性和数据库内的字段要相匹配  而且要注意取值时候的进行值的转换,而且进行值转换的时候要注意poi版本之间的差距,下方的值转换方法并不齐全  如果遇到复杂的值处理  需要补全

// 3.设置参数工厂,临时存放上传文件.
		DiskFileItemFactory dfif = new DiskFileItemFactory();
		// 4.设置工厂临时文件的大小
		dfif.setSizeThreshold(1024 * 1024 * 3);
		// 5.设置临时文件存储的位置
		dfif.setRepository(new File(System.getProperty("java.io.tmpdir")));
		// 5创建上传对象
		ServletFileUpload fileUpload = new ServletFileUpload(dfif);
		// 6设置上传文件大小
		fileUpload.setFileSizeMax(1024 * 1024 * 50);
		// 7设置表单的大小
		fileUpload.setSizeMax(1024 * 1024 * 100);
		// 8设置表单字符
		fileUpload.setHeaderEncoding("utf-8");
		// 10获取上传文件
		List<FileItem> fileItems = fileUpload.parseRequest(request);
		FileItem fileItem = fileItems.get(0);
		
		/**
		 * 获取文件后缀
		 */
		String str = fileItem.getName().substring(fileItem.getName().lastIndexOf("."));
		if (str.equals(".xls") || str.equals(".xlsx")) {// 判断上传的文件是不是表格
			POIFSFileSystem pfs = new POIFSFileSystem(fileItem.getInputStream());
			HSSFWorkbook wb = new HSSFWorkbook(pfs);
			/**
			 * 获取到sheet的页数
			 */
			int sheets = wb.getNumberOfSheets();
			HSSFSheet sheetAt = null;
			List<String> ls = null;
			for (int i = 0; i < sheets; i++) {
				sheetAt = wb.getSheetAt(0);

				/**
				 * 遍历当前的sheet页里面的行 sheetAt.getLastRowNum() 获取到最后一行的位置
				 */

				for (int rowIndex = 1; rowIndex <= sheetAt.getLastRowNum(); rowIndex++) {
					/**
					 * 获取当前的行对象
					 */
					HSSFRow hssfRow = sheetAt.getRow(rowIndex);// 获取到当前行的对象
					if (hssfRow == null) {
						continue;
					}
					ls = new ArrayList<>();
					/**
					 * 同理遍历当前行里的所有列 hssfRow.getLastCellNum() 获取到最后一列的位置
					 */
					for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
						HSSFCell hssfCell = hssfRow.getCell(cellNum);// 获取到列对象
						if (hssfCell == null) {
							continue;
						}
             
						/**
						 * 调用值处理的方法
						 */
						ls.add(getValue(hssfCell));
					}
				}
			}
		} else {
			String jsonString = JSON.toJSONString("兄弟上传表格可否");
			super.print(jsonString, response);
		}}catch (Exception e) {
		falg=false;
		  throw new RuntimeException("上传失败了");
		}
/**
	 * 
	 * @Title: getValue
	 * @Description: (判断单元格对象的值类型 这是3.9版本的写法)
	 * @param hssfCell
	 * @return
	 * @return String
	 */
	private static String getValue(HSSFCell hssfCell) {
		/**
		 * 值类型的常量值 HSSFCell.CELL_TYPE_BOOLEAN
		 */
		if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			/**
			 * 小的工具类 判断此单元格是不是时间格式
			 */
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				/**
				 * 取到的时候数字 因为时间格式不能显示给人家数字所以转换 转换格式
				 */
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
			}
			//如何后边出现.0要将其截取掉    不然进行数据处理的时候会有很大的问题
			String str = String.valueOf(hssfCell.getNumericCellValue());
			String stt = str.substring(str.lastIndexOf("."));
			if (stt.endsWith(".0")) {
				return str.substring(0, str.lastIndexOf("."));
			}
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

 

2019-08-08 10:44:38 weixin_42687829 阅读数 56
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1263 人正在学习 去看看 钟林森

对于要把Excel里的数据导入到数据库中,一般是选择使用POI获取到Excel里的数据,封装进对象,再一个一个add到List里,然后在mybatis的xml文件里使用foreach标签遍历数据,其实这样效率是很慢的,如果要导入的数据量比较大的话,是非常耗时的。于是就需要使用MySQL的批处理进行优化,下面就使用foreach导入数据与使用MySQL批处理导入数据进行一个对比,比较这两者的效率。

1、测试使用foreach导入数据

dao层代码:

    /**
     * 测试使用foreach导入数据
     * @author LiJun
     * @Date 2019/7/16
     * @Time 10:51
     * @param list
     * @return
     */
    int insertConsumeArea(List<Map> list);

对应的xml代码:

  <!--测试使用foreach导入数据-->
  <insert id="insertConsumeArea" parameterType="map" >
    insert into ta_consume_area (
      country_code, country_name
    )
    values
    <foreach collection="list" item="item" separator =",">
      (
        #{item.countryCode},
        #{item.countryName}
      )
    </foreach>
  </insert>

service层与实现类的代码这里就不贴出来了

controller层代码:

@Slf4j
@Controller
@RequestMapping("test")
public class TestController {
    @Autowired
    private TestService testService;

    /**
     * 测试使用foreach导入数据
     * @author LiJun
     * @Date 2019/8/7
     * @Time 16:20
     * @param
     * @return java.lang.String
     */
    @RequestMapping(value = "insertConsumeArea")
    @ResponseBody
    public String insertConsumeArea(){
        try {
            Workbook workbook = POIUtil.getWorkbook(new File("C:\\Users\\20180721\\Desktop\\消费地区和消协机构、办理机构.xlsx"));
            Sheet sheet = workbook.getSheetAt(0);//工作表编号
            int rowNum = sheet.getLastRowNum();
            List<Map> list = new ArrayList<>();
            Map map;
            long startTime = System.currentTimeMillis();
            for (int i = 0;i < rowNum + 1;i++){
                Row row = sheet.getRow(i);
                String countryCode = PoiCellUtil.getCellValue(row.getCell(0));
                String countryName = PoiCellUtil.getCellValue(row.getCell(1));

                map = new HashMap();
                map.put("countryCode",countryCode);
                map.put("countryName",countryName);
                list.add(map);
            }
            testService.insertConsumeArea(list);
            log.info("测试使用foreach导入" + rowNum + "条数据耗时" + (System.currentTimeMillis() - startTime) / (1000) + "秒");
            return "success";
        }catch (Exception e){
            log.error("测试使用foreach导入数据出现异常:",e);
            return "error";
        }
    }
}

测试打印的结果:

2、测试使用批处理导入数据

dao层代码:

    /**
     * 测试使用批处理导入数据
     * @author LiJun
     * @Date 2019/7/17
     * @Time 14:23
     * @param map
     * @return java.util.List<java.util.Map>
     */
    int insertAgency(Map map);

对应的xml代码:

  <!--测试使用批处理导入数据-->
  <insert id="insertAgency" parameterType="map">
    INSERT INTO ta_agency (
      association_code,
      association_name
    )
    VALUES
      (
        #{associationCode},
        #{associationName}
      )
  </insert>

注意:这个是不需要service层和实现类的,因为可以在controller层里直接调用dao层里的方法

controller层代码:

@Slf4j
@Controller
@RequestMapping("test")
public class TestController {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    /**
     * 测试使用批处理导入数据
     * @author LiJun
     * @Date 2019/8/8
     * @Time 9:17
     * @param
     * @return
     */
    @RequestMapping(value = "insertAgency")
    @ResponseBody
    public String insertAgency(){
        //开启SQL批处理
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        //通过新的session获取mapper
        TestDao testDao = sqlSession.getMapper(TestDao.class);
        try {
            Workbook workbook = POIUtil.getWorkbook(new File("C:\\Users\\20180721\\Desktop\\消费地区和消协机构、办理机构.xlsx"));
            Sheet sheet = workbook.getSheetAt(1);//工作表编号
            int rowNum = sheet.getLastRowNum();
            Map map;
            long startTime = System.currentTimeMillis();
            for (int i = 0;i < rowNum + 1;i++){
                Row row = sheet.getRow(i);
                String countryCode = PoiCellUtil.getCellValue(row.getCell(0));
                String countryName = PoiCellUtil.getCellValue(row.getCell(1));

                map = new HashMap();
                map.put("countryCode",countryCode);
                map.put("countryName",countryName);
                testDao.insertAgency(map);

                if (i % 1000 == 0) {//每1000条数据提交一次
                    sqlSession.commit();//提交
                    sqlSession.clearCache();//清理缓存
                    map = null;
                }
            }

            sqlSession.commit();//提交
            sqlSession.clearCache();//清理缓存
            log.info("测试使用批处理导入" + rowNum + "条数据耗时" + (System.currentTimeMillis() - startTime) / (1000) + "秒");
            return "success";
        }catch (Exception e){
            log.error("测试使用批处理导入数据出现异常:",e);
            return "error";
        }finally {
            if (sqlSession != null){
                sqlSession.close();
            }
        }
    }
}

测试打印的结果:

从测试打印的结果可以看到使用foreach导入3248条数据用了63秒,而使用MySQL批处理导入14391条数据只用了39秒,可见效率还是提升了不少。

3、涉及到的帮助类的代码

POIUtil.getWorkbook()方法代码:
        /**
	 * 判断Excel的版本,获取Workbook
	 * @param file
	 * @return
	 * 2007后的版本要用XSSFWorkbook,不能用HSSFWorkbook
	 */
	public static Workbook getWorkbook(File file) {
		try {
			Workbook wb = null;
			if (!file.exists()) {
				throw new RuntimeException("文件不存在!");
			} else if (file.getName().endsWith(XLS)) { // Excel 2003
				wb = new HSSFWorkbook(new FileInputStream(file));
			} else if (file.getName().endsWith(XLSx)) { // Excel 2007/2010
				wb = new XSSFWorkbook(new FileInputStream(file));
			}else{
				throw new RuntimeException("文档格式不正确!");
			}
			return wb;
		} catch (Exception e) {
			throw new RuntimeException("Excel解析失败!");
		}
	}
PoiCellUtil.getCellValue()方法代码:
    /**  
     * 获取单元格的值  
     * @param cell  
     * @return  
     */
    public static String getCellValue(Cell cell) {
        if (cell == null)
            return "";

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        	String cellVal = NumberFormat.getInstance().format(cell.getNumericCellValue());
        	if(cellVal.indexOf(",") > -1){
        		cellVal = cellVal.replace(",", "");
        	}
            return cellVal;
        }
        return "";
    }

4、使用MySQL批处理需要注意的问题

  • controller层注入的SqlSessionFactory是在spring-dao.xml里配置的bean(我这里用的是ssm框架)

  • 提交时的问题

 

2019-09-05 16:35:52 qq_16855077 阅读数 432
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1263 人正在学习 去看看 钟林森

1.实现思路

           (1)  准备excel模板,减少用户非法输入,减少报错率。

           (2)  导入excel,返回错误Map,key是行数,value是对应的报错。

           (3)  把错误列表生成excel,并返回url地址

           (4) 前端通过返回的url地址,下载报错的excel

          难点: 因没有现成的集群文件存储环境,所以,机器返回下,返回url地址会有问题,会存在下载不到错误列表的问题。所以本博客,只是简单的返回成功和失败的条数,还有错误的map列表。

2.代码篇

2.1  后台代码

pom.xml

<!-- poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.8</version>
		</dependency>

 

package com.cloudtech.web.excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(value = {ElementType.FIELD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFiled {
	/**
	 * 列头名称
	 * @return
	 */
	String colName() default "";
	
	/**
	 * 数据精度,精确到后几位数
	 * 默认0位
	 * @return
	 */
	int precision() default 0;
	
	/**
	 * 日期格式
	 * 默认 “yyyy-MM-dd HH:mm:ss”
	 * @return
	 */
	String dateFormat() default "";
	
	/**
	 * 字段对应的列索引
	 * @return
	 */
	int colIndex() default 0;
	 /**
	  * 忽略该字段  为true表示为选填字段,为false为必填字段,必须判断是否为非空
	  * @return
	  */
    boolean skip() default false;
}
package com.cloudtech.web.excel;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.cloudtech.web.util.ReflectUtil;

public class ExcelPOIUtils {
	final static String errormsg = "您好,第 [%s]行,第[%s]列%s,请重新输入!";
	private static Logger logger = Logger.getLogger(ExcelPOIUtils.class);
	private final static String xls = "xls";
	private final static String xlsx = "xlsx";

	static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	SimpleDateFormat createDateFormat(String pattern) {
		return new SimpleDateFormat(pattern);
	}

	public static Workbook getWorkBook(MultipartFile file) throws Exception {
		// 获得文件名
		String fileName = file.getOriginalFilename();
		// 创建Workbook工作薄对象,表示整个excel
		Workbook workbook = null;
		try {
			// 获取excel文件的io流
			InputStream is = file.getInputStream();
			// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
			if (fileName.endsWith(xls)) {
				// 2003
				workbook = new HSSFWorkbook(is);
			} else if (fileName.endsWith(xlsx)) {
				// 2007
				workbook = new XSSFWorkbook(is);
			}
		} catch (IOException e) {
			throw new Exception("解析报文出错!");
		}
		return workbook;
	}

	/**
	 * 
	 * @param file   文件
	 * @param class1    实体类
	 * @param errorMap  错误map   key 行号   value  具体报错信息
	 * @param errors    错误列表
	 * @param colCount  列数
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> readExc(MultipartFile file, Class<T> class1,Map<Integer, String> errorMap,List<T> errors,int colCount) throws Exception {
		try {
			Workbook workbook = getWorkBook(file);
			return readExcel(class1, errorMap,errors, workbook,colCount);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}

	}

	public static <T> List<T> readExc(InputStream in, Class<T> class1,Map<Integer, String> errorMap,List<T> errors,int colCount) throws Exception {
		try {
			Workbook workbook = WorkbookFactory.create(in);
			return readExcel(class1, errorMap,errors, workbook,colCount);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}
	}

	private static <T> List<T> readExcel(Class<T> class1, Map<Integer, String> errorMap,List<T> errors, Workbook workbook,int colCount)
			throws Exception {
		Sheet sheet = workbook.getSheetAt(0);
		int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
		if (rowCount == 1) { // 表格最小长度应为2
			return null;
		}
		int coloumNum  =sheet.getRow(0).getPhysicalNumberOfCells();
		if(coloumNum != colCount){
			throw new Exception("excel格式跟模板格式不一致!");
		}
		
		
		List<T> list = new ArrayList<T>(rowCount - 1);
		T obj;
		// 遍历每一行
		for (int r = 1; r < rowCount; r++) {
			Row row = sheet.getRow(r);
			obj = class1.newInstance();
			Field[] fields = class1.getDeclaredFields();
			Field field;
			boolean flag = true;     //标识,确定该条数据是否通过第一轮判断
			boolean errorFlag = false;  //标识是否有错误点
			StringBuffer error = new StringBuffer();
			for (int j = 0; j < fields.length; j++) {
				field = fields[j];
				ExcelFiled excelFiled = field.getAnnotation(ExcelFiled.class);
				if(excelFiled == null){
					continue;
				}
				Cell cell = row.getCell(excelFiled.colIndex());
				try {
					//序号为0,编号为空,则为无效行
					if(excelFiled.colIndex() == 0 ){
						if(cell == null || cell.toString().length() == 0){
							flag  = false;  
							break;
						}
					}
					
					if(!excelFiled.skip()){   //必填字段,需要判断是否非空
						if (cell == null || cell.toString().length() == 0) {
							//errogMsg(errorMap, r, j, excelFiled,"不能为空");
							error.append(excelFiled.colName()+"不能为空"+"|");
							flag = false;
							errorFlag = true;
							continue;
						}
					}
					
					if (field.getType().equals(Date.class)) {
						if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), dateFormat.parse(cell.getStringCellValue()));
						} else {
							ReflectUtil.setValue(obj, field.getName(), new Date(cell.getDateCellValue().getTime()));
						}
					} else if (field.getType().equals(java.lang.Integer.class)) {
						if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), (int) cell.getNumericCellValue());
						} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), Integer.parseInt(cell.getStringCellValue()));
						}
					} else if (field.getType().equals(java.math.BigDecimal.class)) {
						if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), new BigDecimal(cell.getNumericCellValue()));
						} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), cell.getStringCellValue());
						}
					}else if (field.getType().equals(java.lang.Double.class)) {
						if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
							if(excelFiled.precision() == 0){   //没有小数点
								ReflectUtil.setValue(obj, field.getName(),Double.valueOf(new BigDecimal(cell.getNumericCellValue()).intValue()));
							}else{
								ReflectUtil.setValue(obj, field.getName(),new BigDecimal(cell.getNumericCellValue()).doubleValue());
							}
						} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), new Double(cell.getStringCellValue()));
						}
					} else if (field.getType().equals(java.lang.String.class)) {
						if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
							if(excelFiled.precision() == 0){   //没有小数点
								ReflectUtil.setValue(obj, field.getName(),new BigDecimal(cell.getNumericCellValue()).intValue()+"");
							}else{
								ReflectUtil.setValue(obj, field.getName(),new BigDecimal(cell.getNumericCellValue()).doubleValue()+"");
							}
						} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), cell.getStringCellValue());
						}
					}  else {
						if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), new BigDecimal(cell.getNumericCellValue()));
						} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
							ReflectUtil.setValue(obj, field.getName(), cell.getStringCellValue());
						}
					}
				} catch (Exception e) {
					flag = false;
					errorFlag = true;
					error.append(excelFiled.colName()+"类型格式有误"+"|");
				}
			}
			//录入行号
			ReflectUtil.setValue(obj, "rowNum",(r+1));
			if(flag){
				list.add(obj);
			}
			if(errorFlag){
				errorMap.put(r, error.toString());
				errors.add(obj);
			}
		}
		return list;
	}


}
package com.cloudtech.web.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.cloudtech.web.vo.ImportStationInfoVo;

public class Main {
	public static void main(String[] args) {
			try {
				InputStream in = new FileInputStream(new File("D://stationInfo.xlsx"));
				//错误列表
				HashMap<Integer, String> errorMap = new HashMap<>();
				List<ImportStationInfoVo> errors = new ArrayList<ImportStationInfoVo>();
				List<ImportStationInfoVo> readExc = ExcelPOIUtils.readExc(in, ImportStationInfoVo.class,errorMap,errors,24);
				//List<ExcelEntity> readExc = ExcelPOIUtils.readExc(in, ExcelEntity.class,errorMap,3);

				System.out.println("数据----------------");
				for (ImportStationInfoVo s : readExc) {
					System.out.println(s.toString());
				}
				
				System.out.println("错误maps----------------");
				errorMap.forEach((k,v)-> System.out.println(v));
				
				System.out.println("错误列表----------------");
				errors.forEach(k-> System.out.println(k.toString()));
				
				int success = readExc.size();
				int error = errorMap.size();
				System.out.println("总数"+(success+error)+",成功数:"+success+",失败数:"+errorMap.size());
			}  catch (Exception e) {
				e.printStackTrace();
				System.out.println(e.getMessage());
			}
	}
}
package com.cloudtech.web.vo;

import com.cloudtech.web.excel.ExcelFiled;

/**
 * excel导入模板
 * 
 * @ClassName: ImportStationInfoVo
 * @Description:
 * @author wude
 * @date 2019年9月2日
 *
 */
public class ImportStationInfoVo {
	@ExcelFiled(colIndex=0,colName="有效行")
	private String id;
	@ExcelFiled(colIndex=1,colName="站点编号")
	private String number;
	@ExcelFiled(colIndex=2,colName="站点代码")
	private String serialNo;
	@ExcelFiled(colIndex=3,colName="站点名称")
	private String name;
	@ExcelFiled(colIndex=4,colName="维护公司")
	private String operatorName;
	@ExcelFiled(colIndex=5,colName="所在区域")
	private String areaName;
	@ExcelFiled(colIndex=6,colName="自动站类型")
	private String sTypeName;
	@ExcelFiled(colIndex=7,colName="海陆位置")
	private String isLand;
	@ExcelFiled(colIndex=8,colName="是否为秒级站")
	private String isSecond;
	@ExcelFiled(colIndex=9,colName="品牌类型",skip=true)
	private String brandTypeName;
	@ExcelFiled(colIndex=10,colName="联系人",skip=true)
	private String linkman;
	@ExcelFiled(colIndex=11,colName="联系座机",skip=true)
	private String linkPhone;
	@ExcelFiled(colIndex=12,colName="联系手机号",skip=true)
	private String linkMobilePhone;
	
	
	@ExcelFiled(colIndex=13,colName="具体地址",skip=true)
	private String address;
	@ExcelFiled(colIndex=14,colName="经度")
	private Double longitde;
	@ExcelFiled(colIndex=15,colName="纬度")
	private Double latitude;
	@ExcelFiled(colIndex=16,colName="海拔高度")
	private Double altitude;
	@ExcelFiled(colIndex=17,colName="安装位置",skip=true)
	private String installSite;
	@ExcelFiled(colIndex=18,colName="观测要素",skip=true)
	private String observeElement;
	
	
	@ExcelFiled(colIndex=19,colName="离地高度")
	private Double terrainClearance;
	@ExcelFiled(colIndex=20,colName="气压高度",skip=true)
	private Double pressureHeight;
	@ExcelFiled(colIndex=21,colName="供电来源",skip=true)
	private String powerSupplyType;
	@ExcelFiled(colIndex=22,colName="电池容量",skip=true)
	private String batteryCapacity;
	@ExcelFiled(colIndex=23,colName="占地面积",skip=true)
	private String roomSize;
	/** 行数 */
	private Integer rowNum;
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public String getSerialNo() {
		return serialNo;
	}
	public void setSerialNo(String serialNo) {
		this.serialNo = serialNo;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getOperatorName() {
		return operatorName;
	}
	public void setOperatorName(String operatorName) {
		this.operatorName = operatorName;
	}
	public String getAreaName() {
		return areaName;
	}
	public void setAreaName(String areaName) {
		this.areaName = areaName;
	}
	public String getsTypeName() {
		return sTypeName;
	}
	public void setsTypeName(String sTypeName) {
		this.sTypeName = sTypeName;
	}
	public String getIsLand() {
		return isLand;
	}
	public void setIsLand(String isLand) {
		this.isLand = isLand;
	}
	public String getIsSecond() {
		return isSecond;
	}
	public void setIsSecond(String isSecond) {
		this.isSecond = isSecond;
	}
	public String getBrandTypeName() {
		return brandTypeName;
	}
	public void setBrandTypeName(String brandTypeName) {
		this.brandTypeName = brandTypeName;
	}
	public String getLinkman() {
		return linkman;
	}
	public void setLinkman(String linkman) {
		this.linkman = linkman;
	}
	public String getLinkPhone() {
		return linkPhone;
	}
	public void setLinkPhone(String linkPhone) {
		this.linkPhone = linkPhone;
	}
	public String getLinkMobilePhone() {
		return linkMobilePhone;
	}
	public void setLinkMobilePhone(String linkMobilePhone) {
		this.linkMobilePhone = linkMobilePhone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Double getLongitde() {
		return longitde;
	}
	public void setLongitde(Double longitde) {
		this.longitde = longitde;
	}
	public Double getLatitude() {
		return latitude;
	}
	public void setLatitude(Double latitude) {
		this.latitude = latitude;
	}
	public Double getAltitude() {
		return altitude;
	}
	public void setAltitude(Double altitude) {
		this.altitude = altitude;
	}
	public String getInstallSite() {
		return installSite;
	}
	public void setInstallSite(String installSite) {
		this.installSite = installSite;
	}
	public String getObserveElement() {
		return observeElement;
	}
	public void setObserveElement(String observeElement) {
		this.observeElement = observeElement;
	}
	public Double getTerrainClearance() {
		return terrainClearance;
	}
	public void setTerrainClearance(Double terrainClearance) {
		this.terrainClearance = terrainClearance;
	}
	public Double getPressureHeight() {
		return pressureHeight;
	}
	public void setPressureHeight(Double pressureHeight) {
		this.pressureHeight = pressureHeight;
	}
	public String getPowerSupplyType() {
		return powerSupplyType;
	}
	public void setPowerSupplyType(String powerSupplyType) {
		this.powerSupplyType = powerSupplyType;
	}
	public String getBatteryCapacity() {
		return batteryCapacity;
	}
	public void setBatteryCapacity(String batteryCapacity) {
		this.batteryCapacity = batteryCapacity;
	}
	public String getRoomSize() {
		return roomSize;
	}
	public void setRoomSize(String roomSize) {
		this.roomSize = roomSize;
	}
	public Integer getRowNum() {
		return rowNum;
	}
	public void setRowNum(Integer rowNum) {
		this.rowNum = rowNum;
	}
	@Override
	public String toString() {
		return "ImportStationInfoVo [id=" + id + ", number=" + number + ", serialNo=" + serialNo + ", name=" + name
				+ ", operatorName=" + operatorName + ", areaName=" + areaName + ", sTypeName=" + sTypeName + ", isLand="
				+ isLand + ", isSecond=" + isSecond + ", brandTypeName=" + brandTypeName + ", linkman=" + linkman
				+ ", linkPhone=" + linkPhone + ", linkMobilePhone=" + linkMobilePhone + ", address=" + address
				+ ", longitde=" + longitde + ", latitude=" + latitude + ", altitude=" + altitude + ", installSite="
				+ installSite + ", observeElement=" + observeElement + ", terrainClearance=" + terrainClearance
				+ ", pressureHeight=" + pressureHeight + ", powerSupplyType=" + powerSupplyType + ", batteryCapacity="
				+ batteryCapacity + ", roomSize=" + roomSize + ", rowNum=" + rowNum + "]";
	}
}

controller类

 // 导入
    @RequestMapping(value = "importData")
    @ResponseBody
    public BaseDataResult importData(@RequestParam MultipartFile file) {
    	try {
    		//错误maps
    		Map<Integer, String> errorMap = new HashMap<>();
    		//错误列表
			List<ImportStationInfoVo> errors = new ArrayList<ImportStationInfoVo>();
			//站点数据列表
			List<ImportStationInfoVo> stations = ExcelPOIUtils.readExc(file, ImportStationInfoVo.class,errorMap,errors,24);
			HashMap<String, Object> maps = new HashMap<>();
			maps.put("succ", stations);
			maps.put("fail", errorMap);
		    return new BaseDataResult(Constans.SUCCESS, "导入成功",maps);
		} catch (Exception e) {
			 return new BaseDataResult(Constans.FAILED, e.getMessage()); 
		}
    }

2.2前端代码layui

 <button id="importData" class="layui-btn">导入</button>
<button class="layui-btn" onclick="downloadTemplate();">模板下载</button>

导入layui相关依赖css和js 

<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/assets/css/layui.css"/>
<script src="${pageContext.request.contextPath}/assets/js/jquery-3.2.0.min.js"></script>
<script src="${pageContext.request.contextPath}/assets/js/layui.js"></script>

在script初始化如下代码,记得#importData是关键,跟导入按钮的id一样 

layui.use([ "element", "laypage", "layer", "upload"], function() {
    var element = layui.element;
    var laypage = layui.laypage;
    var layer = layui.layer;
    var upload = layui.upload;//主要是这个
    layui.upload.render({
        elem: "#importData",//导入id
        url: "../station/importData",
        size: '3072',
        accept: "file",
        exts: 'xls|xlsx',
        done: function (result) {
            if (result.code == 1) {      //成功
                $("#stationErro").html("");
                var fail = result.data.fail;
                var succ = result.data.succ;
                
                var failCount=0;
                var failHtml="";  //失败的列表
                for(var key in fail){
                	failHtml+="<tr>"+
							 	"<td colspan='3'>第"+(parseInt(key)+1)+"行,"+fail[key]+"</td>"+
							  "</tr>";
                	failCount++;
                }
                
                var content="<tr>"+
							 	"<td>"+(failCount+succ.length)+"</td>"+
							 	"<td>"+succ.length+"</td>"+
							 	"<td>"+failCount+"</td>"+
							" </tr>";
                if(failCount != 0){
                	content+="<tr>"+
							 	"<td colspan='3' style='color: red;'>导入失败原因如下:</td>"+
							 "</tr>";
					content+=failHtml;
                }else{
                	content+="<tr>"+
						 		"<td colspan='3' style='color: green;'>您好,暂无错误信息!</td>"+
							 "</tr>";
                }
                $(content).appendTo("#stationErro");
                failTemplate();
            }else{     //失败
            	return layer.msg(result.error);
            }
        }
    });
});

效果图

3注意

下图是excel的模板,因保密问题,所以,只给一个效果图

业务逻辑:

     1.判断有效行是否有值,没有值则认为该数据无效

     2、通过自定义的注解里面的skip,判断该字段是否不能为空,为false则认为一定要有值,不然会打印错误信息

    3.ImportStationInfoVo只是用来测试的一个实体类,行号和有效行这两个字段必须得有。实体类的类型也不是随便写的,根据实际的需求具体定义,假设站点编号在实体类中是int,表格录入的时候,输入字符串,工具会返回错误提示

   4.controller讲解   errorMap是错误列表  key是行号,value是具体的报错拼接起来的

                               errors是错误的list

                              stations是第一重简单的非空和类型判断

5.  需要对stations列表进行各种判断,并返回成功的list和错误的集合map

如果你热衷技术,喜欢交流,欢迎加入我们! 

 

2018-07-23 19:00:38 wxh199602101845 阅读数 271
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1263 人正在学习 去看看 钟林森

一:导入maven的pom.xml的依赖

https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/3.17

我这里导入的是3.17的最新版本,poi-ooxml是poi的升级版

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.17</version>

</dependency>

 

二:定义一个导入的工具类PoiTools

package com.shanjian.qyd.common.service.sysLog;

import org.apache.poi.hssf.usermodel.HSSFSheet;

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.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.HashMap;

import java.util.Iterator;

import java.util.Map;

/**

* 导入导出工具类

* @ClassName: PoiTools

* @Description: TODO

* @author: wxh

* @date: 2018年7月23日 下午18:42:30

*/

public class PoiTools {

/*

* @Description:TODO

* @param filename:导入的文件名称,file:这里是用的是spring-web的文件上传的MultipartFile

* @return 返回一个解析完毕的xlsx的数据map集合,可以用map的get方法拿到你想要的某行某列的具体的值,

* 这里返回的就是对应xlsx的某行某列的数据

* @throws Exception

*/

@SuppressWarnings("null")

public Map<Integer,Map<Integer,String>> getPoiData(String filename,MultipartFile file) throws Exception{

if(filename!=null || filename.isEmpty()==false) {

String suffix=filename.substring(filename.lastIndexOf(".")+1, filename.length()).toLowerCase();

if(suffix.equals("xls")||suffix.equals("xlsx")) {

return getpoiData(file);

}else {

throw new Exception("文件类型错误!");

}

}else {

throw new Exception("文件不能为空!");

}

}

/**

* 为方便调用,提取的方法。此方法用来适用xlsx的2003版与2007版及以上

* XSSF要求xlsx在2007版及以上,HSSF在2007版一下2003版及以上

* @Description:TODO

* @param file

* @return

* @throws Exception

*/

private Map<Integer,Map<Integer,String>> getpoiData(MultipartFile file) throws Exception{

Map<Integer,Map<Integer,String>> map=null;

try {

XSSFWorkbook xssf=new XSSFWorkbook(file.getInputStream());//创建一个XSSF工作对象。

XSSFSheet sheetAt = xssf.getSheetAt(0);//获得第一个表格类容对象

int rows=sheetAt.getPhysicalNumberOfRows();//获得该表格的总行数

if(rows!=0) {

map=getpoiDataFormat(sheetAt.iterator(),rows);//调用公共提取的方法,传入的是Iterator<Row>,该方法的迭代就是这个

}

} catch (IOException e) {

// TODO Auto-generated catch block

//如果发生异常则创建一个HSSF工作对象。

try {

HSSFWorkbook hssf=new HSSFWorkbook(file.getInputStream());//创建一个HSSF工作对象

HSSFSheet sheetAt = hssf.getSheetAt(0);//获得第一个表格类容对象

int rows=sheetAt.getPhysicalNumberOfRows();//获取该表格总行数

if(rows!=0) {

map=getpoiDataFormat(sheetAt.iterator(),rows);

}

} catch (IOException e1) {

// TODO Auto-generated catch block

throw new Exception("解析文件出错");

}

}

return map;

}

/**

* 公共提取方法,为了方便poi导入不同版本格式而提取的公共方法

* @Description:TODO

* @param iterator 传入的是poi提供的表格类容对象的迭代方法

* @param rows 表格类容的总行数

* @return

*/

private Map<Integer,Map<Integer,String>> getpoiDataFormat(Iterator<Row> iterator, int rows){

Map<Integer,Map<Integer,String>> map=new HashMap<Integer,Map<Integer,String>>(rows);

int i=0; //此int声明正好代表第几行,从0开始

while(iterator.hasNext()) { //迭代该表格总行数内容

Row row = iterator.next(); //获得迭代的某行内容

Iterator<Cell> iterator2 = row.iterator();//获得该行的总迭代列的内容

int cells=row.getPhysicalNumberOfCells();//获得该行的总列数

Map<Integer,String> cellss=new HashMap<>(cells);

int j=0;//此int声明代表该行的第几列,从0开始

while(iterator2.hasNext()) {

String cellValue=CellToString(iterator2.next());//将迭代的该列的值转成String类型的字符串

cellss.put(j,cellValue.replaceAll(" ",""));

j++;

}

map.put(i,cellss);

i++;

 

}

return isCell(map);

}

/**

* 转换xlsx里面的数据格式全部变为String的数据类型

* @Description:TODO

* @param cell poi该行某列的cell对象

* @return

*/

private String CellToString(Cell cell) {

String cellValue="";

if (null != cell) {

// 以下是判断数据的类型

switch (cell.getCellType())

{

case Cell.CELL_TYPE_NUMERIC: // 数字 与时间类型转换

short format = cell.getCellStyle().getDataFormat();

double value = cell.getNumericCellValue();

Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);

if(format == 14 || format == 31 || format == 57 || format == 58){

//日期

cellValue= new SimpleDateFormat("yyyy-MM-dd").format(date);

}

else if (format == 20 || format == 32) {

//时间

cellValue = new SimpleDateFormat("HH:mm").format(date);

}

else {

cellValue = cell.getNumericCellValue() + "";

}

 

break;

 

case Cell.CELL_TYPE_STRING: // 字符串

cellValue = cell.getStringCellValue();

break;

 

case Cell.CELL_TYPE_BOOLEAN: // Boolean

cellValue = cell.getBooleanCellValue() + "";

break;

 

case Cell.CELL_TYPE_FORMULA: // 公式

// cellValue = cell.getCellFormula() + "";

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

cellValue = cell.getNumericCellValue() + "";

break;

 

case Cell.CELL_TYPE_BLANK: // 空值

cellValue = "";

break;

 

case Cell.CELL_TYPE_ERROR: // 故障

//cellValue = "非法字符";

cellValue = "";

break;

 

default:

// cellValue = "未知类型";

cellValue = "";

break;

}

}

 

return cellValue;

 

}

/**

* 判断xlsx里面的行数据是否为空

* 或者是否基本为空

* @Description:TODO

* @param map

* @return

*/

private Map<Integer,Map<Integer,String>> isCell(Map<Integer,Map<Integer,String>> map) {

Iterator<Map.Entry<Integer, Map<Integer, String>>> iterator = map.entrySet().iterator();//将解析好的xlsx数据转为Iterator迭代器对象

while(iterator.hasNext()) {

Map<Integer, String> value = iterator.next().getValue();//获得该行的行数据

int cells=value.size();//获得该行的总列数

int isemptynumber=0;//创建一个用来记该行有几个空值的计数变量

Iterator<Map.Entry<Integer, String>> iterator2 = value.entrySet().iterator();//获得该行数据的迭代器对象

while(iterator2.hasNext()) {

if(iterator2.next().getValue().isEmpty()) { //判断该行某列的值是否为空

isemptynumber++;

}

}

if(isemptynumber==cells || isemptynumber == cells -1 || isemptynumber == cells -2 || isemptynumber == cells -3 ) { //判断该行数据是否都为空或者只有一列不为空,或者只有两列不为空....

iterator.remove(); //如果是这样的话,则删掉解析出来该行的数据

}

}

return map; //返回解析出来的map集合的数据

}

 

}

这样导入的工具类就算是写好了,完全可以结合spring-mvc去使用。只需要调用getPoiData(),这个方法传入对应的参数即可!

本人第一次写这个,不喜勿喷......

 

2017-04-28 21:05:49 llppyy777 阅读数 539
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1263 人正在学习 去看看 钟林森
package com.jeeframe.cms.updata.service.impl;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;


import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;


import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;


public class ExcelReader {


    /**
     * 解析并显示一个表的内容和使用指定的样式
     * 
     * @param styles
     * @param strings
     * @param sheetInputStream
     */
    public static List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
            InputStream sheetInputStream, int minColumns)
            throws IOException, ParserConfigurationException, SAXException {


                InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        ExcelReaderHandler handler = new ExcelReaderHandler(styles, strings, minColumns, System.out);


        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
        return handler.getRows();
    }


    /**
     * 解析第一个sheet
     * 
     * @param path
     * @param minColumns
     * @return List<String[]>
     * @throws IOException
     * @throws OpenXML4JException
     * @throws ParserConfigurationException
     * @throws SAXException
     */
    public static List<String[]> processOneSheet(String path, int minColumns)
            throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
        OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
        XSSFReader xssfReader = new XSSFReader(p);
        StylesTable styles = xssfReader.getStylesTable();
        InputStream stream = xssfReader.getSheet("rId1");
        List<String[]> list = processSheet(styles, strings, stream, minColumns);
        stream.close();
        return list;
    }


    public static void main(String[] args) throws Exception {
        /*
         * long begin = System.currentTimeMillis() ; List<String[]> list =
         * ExcelReader.processOneSheet("d:\\201401-ds.xlsx" , 18);
         * //List<String[]> list = ExcelReader.processOneSheet("d:\\out.xlsx" ,
         * 10); for (String cell : list.get(1)) { System.out.print(cell + "  ");
         * System.out.println(cell == null); } long end =
         * System.currentTimeMillis() ; System.out.println("用时:" + (end - begin)
         * /1000 + "秒");
         */
        long begin = System.currentTimeMillis();
        System.out.println(begin);
        List<String[]> list = ExcelReader.processOneSheet("d:\\test.xlsx", 18);
        long end = System.currentTimeMillis();
        System.out.println("读取用时:" + (end - begin) / 1000 + "秒,总量:" + list.size());
        Connection conn = getNew_Conn();
        conn.setAutoCommit(false);
        PreparedStatement pstmt = conn.prepareStatement("insert into temp_table values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        for (int i = 0; i < list.size(); i++) {
            String[] row = list.get(i);
            for (int index = 1; index <= 18; index++) {
                if (row[index - 1] == null) {
                    pstmt.setNull(index, Types.NULL);
                } else {
                    pstmt.setObject(index, row[index - 1]);
                }
            }


            pstmt.addBatch();
            if (i > 0 && i % 10000 == 0) {
                pstmt.executeBatch();
                System.out.println("提交:" + i);
            }
        }
        pstmt.executeBatch();
        conn.commit();
        pstmt.close();
        conn.close();
        end = System.currentTimeMillis();
        System.out.println("插入用时:" + (end - begin) / 1000 + "秒");
    }


    private static Connection getNew_Conn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jeeframe_cms?useUnicode=true&amp;characterEncoding=UTF-8", "root",
                    "1392010");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        return conn;
    }


}

poi 导入excel 数据转对象

博文 来自: liangrui1988
没有更多推荐了,返回首页