SpringMVC 实现POI读取Excle文件中数据导入数据库（上传）、导出数据库中数据到Excle文件中（下载）...-白红宇

SpringMVC 实现POI读取Excle文件中数据导入数据库（上传）、导出数据库中数据到Excle文件中（下载）...

阅读量：5037 次

发布时间：2019-06-12

本文共 19308 字，大约阅读时间需要 64 分钟。

读取Excle表返回一个集合：

package com.shiliu.game.utils;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;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.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.multipart.commons.CommonsMultipartFile;/** * SpringMVC 读取Excle工具类 * @author  otowa * @Date    2016-12-04 14:57 * * @param 
     
       */public class LeadingInExcel
      
        {        //log4j输出    private Logger logger = Logger.getLogger(this.getClass());    // 时间的格式    private String format="yyyy-MM-dd";    /**     * 无参构造     */    public LeadingInExcel() {        super();    }        /**     * 构造设置显示时间的格式     * @param format 例："yyyy-MM-dd"     */    public LeadingInExcel(String format) {        super();        this.format = format;    }        /**     * 设置显示时间的格式     * @param format 例："yyyy-MM-dd"     */    public void setFormat(String format) {        this.format = format;    }        /**     * 上传Excle文件、并读取其中数据、返回list数据集合     * @param multipart     * @param propertiesFileName    properties文件名称     * @param kyeName                properties文件中上传存储文件的路径     * @param sheetIndex            读取Excle中的第几页中的数据     * @param titleAndAttribute        标题名与实体类属性名对应的Map集合     * @param clazz                    实体类.class     * @return                        返回读取出的List集合     * @throws Exception     */    public List
       
         uploadAndRead(MultipartFile multipart,String propertiesFileName, String kyeName,int sheetIndex,            Map
        
          titleAndAttribute,Class
         
           clazz) throws Exception{                    String originalFilename=null;            int i = 0;            boolean isExcel2003 = false;                        //取出文件名称            originalFilename = multipart.getOriginalFilename();                        //判断Excel是什么版本            i = isExcleVersion(originalFilename);            if(i==0)return null; else if(i==1)isExcel2003=true;                        String filePath = readPropertiesFilePathMethod( propertiesFileName, kyeName);            File filePathname = this.upload(multipart, filePath, isExcel2003);            List
          
            judgementVersion = judgementVersion(filePathname, sheetIndex, titleAndAttribute, clazz, isExcel2003); return judgementVersion; } /** * @描述：判断Excel是什么版本 * @param originalFilename * @return * 1 ：2003 * 2 ：2007 * 0 ：不是Excle版本 */ public int isExcleVersion(String originalFilename){ int i = 0; if(originalFilename.matches("^.+\\.(?i)(xls)$"))i = 1; else if(originalFilename.matches("^.+\\.(?i)(xlsx)$"))i = 2; return i; } /** * 读取properties文件中对应键的值 * @param propertiesFileName * @param kyeName * @return value值 */ public String readPropertiesFilePathMethod(String propertiesFileName, String kyeName){ //读取properties文件 InputStream inputStream=null; Properties properties=null; String filePath=null;//读取出的文件路径 try { inputStream= new FileInputStream(this.getClass().getClassLoader().getResource("/"+propertiesFileName+".properties").getPath()); properties=new Properties(); properties.load(inputStream); filePath = properties.getProperty(kyeName); } catch (FileNotFoundException e1) { logger.error("未找到properties文件！", e1); } catch (IOException e1) { logger.error("打开文件流异常！", e1); } finally{ //关闭流 if(inputStream!=null){ try { inputStream.close(); } catch (IOException e) { logger.error("关闭文件流异常！", e); } } } return filePath; } /** * SpringMVC 上传Excle文件至本地 * @param multipart * @param filePath 上传至本地的文件路径 例：D:\\fileupload * @param isExcel2003 是否是2003版本的Excle文件 * @return 返回上传文件的全路径 * @throws Exception */ public File upload(MultipartFile multipart,String filePath,boolean isExcel2003) throws Exception{ //文件后缀 String extension=".xlsx"; if(isExcel2003)extension=".xls"; //指定上传文件的存储路径 File file=new File(filePath); //接口强转实现类 CommonsMultipartFile commons=(CommonsMultipartFile) multipart; //判断所属路径是否存在、不存在新建 if(file.exists())file.mkdirs(); /* * 新建一个文件 * LongIdWorker longID工具类 */ File filePathname=new File(file+File.separator+LongIdWorker.getDataId()+extension); //将上传的Excel写入新建的文件中 try { commons.getFileItem().write(filePathname); } catch (Exception e) { logger.error("写入文件异常", e); } return filePathname; } /** * 读取本地Excel文件返回List集合 * @param filePathname * @param sheetIndex * @param titleAndAttribute * @param clazz * @param isExcel2003 * @return * @throws Exception */ public List
           
             judgementVersion(File filePathname,int sheetIndex,Map
            
              titleAndAttribute,Class
             
               clazz,boolean isExcel2003) throws Exception{ FileInputStream is=null; POIFSFileSystem fs=null; Workbook workbook=null; try { //打开流 is=new FileInputStream(filePathname); if(isExcel2003){ //把excel文件作为数据流来进行传入传出 fs=new POIFSFileSystem(is); //解析Excel 2003版 workbook = new HSSFWorkbook(fs); }else{ //解析Excel 2007版 workbook=new XSSFWorkbook(is); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } return readExcelTitle(workbook,sheetIndex,titleAndAttribute,clazz); } /** * 判断接收的Map集合中的标题是否于Excle中标题对应 * @param workbook * @param sheetIndex * @param titleAndAttribute * @param clazz * @return * @throws Exception */ private List
              
                readExcelTitle(Workbook workbook,int sheetIndex,Map
               
                 titleAndAttribute,Class
                
                  clazz) throws Exception{ //得到第一个shell Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取标题 Row titelRow = sheet.getRow(0); Map
                 
                   attribute = new HashMap
                  
                   (); if (titleAndAttribute != null) { for (int columnIndex = 0; columnIndex < titelRow.getLastCellNum(); columnIndex++) { Cell cell = titelRow.getCell(columnIndex); if (cell != null) { String key = cell.getStringCellValue(); String value = titleAndAttribute.get(key); if (value == null) { value = key; } attribute.put(Integer.valueOf(columnIndex), value); } } } else { for (int columnIndex = 0; columnIndex < titelRow.getLastCellNum(); columnIndex++) { Cell cell = titelRow.getCell(columnIndex); if (cell != null) { String key = cell.getStringCellValue(); attribute.put(Integer.valueOf(columnIndex), key); } } } return readExcelValue(workbook,sheet,attribute,clazz); } /** * 获取Excle中的值 * @param workbook * @param sheet * @param attribute * @param clazz * @return * @throws Exception */ private List
                   
                     readExcelValue(Workbook workbook,Sheet sheet,Map
                    
                      attribute,Class
                     
                       clazz) throws Exception{ List
                      
                        info=new ArrayList
                       
                        (); //获取标题行列数 int titleCellNum = sheet.getRow(0).getLastCellNum(); // 获取值 for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { Row row = sheet.getRow(rowIndex);// logger.debug("第--" + rowIndex); // 1.若当前行的列数不等于标题行列数就放弃整行数据(若想放弃此功能注释4个步骤即可) int lastCellNum = row.getLastCellNum(); if(titleCellNum != lastCellNum){ continue; } // 2.标记 boolean judge = true; T obj = clazz.newInstance(); for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {//这里小于等于变成小于 Cell cell = row.getCell(columnIndex); //处理单元格中值得类型 String value = getCellValue(cell); // 3.单元格中的值等于null或等于"" 就放弃整行数据 if(value == null || "".equals(value)){ judge = false; break; } /* * 测试：查看自定义的title Map集合中定义的Excle标题和实体类中属性对应情况！ System.out.println("c:"+columnIndex+"\t"+attribute.get(Integer.valueOf(columnIndex))); */ Field field = clazz.getDeclaredField(attribute.get(Integer .valueOf(columnIndex))); Class
                         fieldType = field.getType(); Object agge = null; if (fieldType.isAssignableFrom(Integer.class)) { agge = Integer.valueOf(value); } else if (fieldType.isAssignableFrom(Double.class)) { agge = Double.valueOf(value); } else if (fieldType.isAssignableFrom(Float.class)) { agge = Float.valueOf(value); } else if (fieldType.isAssignableFrom(Long.class)) { agge = Long.valueOf(value); } else if (fieldType.isAssignableFrom(Date.class)) { agge = new SimpleDateFormat(format).parse(value); } else if (fieldType.isAssignableFrom(Boolean.class)) { agge = "Y".equals(value) || "1".equals(value); } else if (fieldType.isAssignableFrom(String.class)) { agge = value; } // 个人感觉char跟byte就不用判断了 用这两个类型的很少如果是从数据库用IDE生成的话就不会出现了 Method method = clazz.getMethod("set" + toUpperFirstCase(attribute.get(Integer .valueOf(columnIndex))), fieldType); method.invoke(obj, agge); } // 4. if if(judge)info.add(obj); } return info; } /** * @ 首字母大写 */ private String toUpperFirstCase(String str) { return str.replaceFirst(str.substring(0, 1), str.substring(0, 1) .toUpperCase()); } /** * 功能:处理单元格中值得类型 * @param cell * @return */ private String getCellValue(Cell cell) { Object result = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: //判断是是日期型，转换日期格式，否则转换数字格式。 if(DateUtil.isCellDateFormatted(cell)){ Date dateCellValue = cell.getDateCellValue(); if(dateCellValue != null){ result = new SimpleDateFormat(this.format).format(dateCellValue); }else{ result=""; } }else{ result = new DecimalFormat("0").format(cell.getNumericCellValue()); }; break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: /* * 导入时如果为公式生成的数据则无值 * if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } */ result = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_BLANK: break; default: break; } } return result.toString(); } }

使用例子（项目代码只能模仿）：

/**     * 读取Excel中的用户信息插入数据库     * @param multipart     * @param session     * @return     */    @RequestMapping(value="/batchimport")    @ResponseBody    public String batchImportMethod(            @RequestParam(value="gameId") String gameId,            @RequestParam(value="filename") MultipartFile multipart    ){        //局部变量        LeadingInExcel
     
       testExcel=null;        List
      
        uploadAndRead=null;        boolean judgement = false;        String Msg =null;        String error = "";                        //定义需要读取的数据        String formart = "yyyy-MM-dd";        String propertiesFileName = "config";        String kyeName = "file_path";        int sheetIndex = 0;        Map
       
         titleAndAttribute=null;        Class
        
          clazz=UserWhiteList.class;            //定义对应的标题名与对应属性名            titleAndAttribute=new HashMap
         
          ();            titleAndAttribute.put("手机号码", "phone");            titleAndAttribute.put("总抽奖次数", "playtimes");                //调用解析工具包        testExcel=new LeadingInExcel
          
           (formart); //解析excel，获取客户信息集合 try { uploadAndRead = testExcel.uploadAndRead(multipart, propertiesFileName, kyeName, sheetIndex, titleAndAttribute, clazz); } catch (Exception e) { log.error("读取Excel文件错误！",e); } if(uploadAndRead != null && !"[]".equals(uploadAndRead.toString()) && uploadAndRead.size()>=1){ judgement = true; } if(judgement){ //把客户信息分为没100条数据为一组迭代添加客户信息（注：将customerList集合作为参数，在Mybatis的相应映射文件中使用foreach标签进行批量添加。） //int count=0; int listSize=uploadAndRead.size(); int toIndex=100; for (int i = 0; i < listSize; i+=100) { if(i+100>listSize){ toIndex=listSize-i; } List
           
             subList = uploadAndRead.subList(i, i+toIndex); /* * 测试数据： count=count+subList.size(); System.out.println("subList长度："+subList.size()+"\t总长度："+count); * for (UserJHDX userJHDX : subList) { System.out.println("手机号："+userJHDX.getPhone()+"截止日期:"+userJHDX.getUptodate()+"流量值"+userJHDX.getFlux()+"总次数"+userJHDX.getTotal()); } */ /** 此处执行集合添加 */ userWhiteListService.batchInport(subList, gameId); } Msg ="批量导入EXCEL成功！"; }else{ Msg ="批量导入EXCEL失败！"; } String res = "{ error:'" + error + "', msg:'" + Msg + "'}"; return res; }

读取一个集合输出Excle:

package com.shiliu.game.utils;import java.io.OutputStream;import java.net.URLEncoder;import java.util.ArrayList;  import java.util.List;    import javax.servlet.http.HttpServletResponse;    import org.apache.poi.hssf.usermodel.HSSFCell;  import org.apache.poi.hssf.usermodel.HSSFCellStyle;  import org.apache.poi.hssf.usermodel.HSSFFont;  import org.apache.poi.hssf.usermodel.HSSFRichTextString;  import org.apache.poi.hssf.usermodel.HSSFRow;  import org.apache.poi.hssf.usermodel.HSSFSheet;  import org.apache.poi.hssf.usermodel.HSSFWorkbook;  import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.util.CellRangeAddress;    /**  * 导出Excel公共方法  *   * @author wkr  *  */  public class LeadingOutExcel {      //导出文件的名字    private String fileName;    //显示的导出表的标题      private String title;      //导出表的列名      private String[] rowName;          private List
     
        dataList = new ArrayList
      
       ();            private HttpServletResponse  response = null;        private HSSFWorkbook workbook = null;        private OutputStream out = null;          //构造方法，传入要导出的数据      public LeadingOutExcel(String fileName,String title,String[] rowName,List
       
          dataList,HttpServletResponse response){        this.fileName = fileName;        this.dataList = dataList;          this.rowName = rowName;          this.title = title;          this.response = response;    }        public void export () throws Exception{                HSSFWorkbook createExcel = this.createExcel();        this.writeInOutputStream(createExcel);    }                  /*      * 导出数据      * */      public HSSFWorkbook createExcel() throws Exception{          try{              workbook = new HSSFWorkbook();                     // 创建工作簿对象              HSSFSheet sheet = workbook.createSheet(title);                  // 创建工作表                            /*             *  产生表格标题行              HSSFRow rowm = sheet.createRow(0);              HSSFCell cellTiltle = rowm.createCell(0);//设置开头两行                                 * 使用条件：                 * HSSFRow rowRowName = sheet.createRow(0);设置索引2的位置创建行                 * HSSFRow row = sheet.createRow(i+1);//创建所需的行数     改为i+3                 *              */                          //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】              HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象              HSSFCellStyle style = this.getStyle(workbook);                  //单元格样式对象                          /*             *  产生表格标题行              sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1))); //设置开头两行合并            cellTiltle.setCellStyle(columnTopStyle);  //设置列头单元格样式              cellTiltle.setCellValue("");//设置空单元格的内容。比如传入List的值是空的。             */            // 定义所需列数              int columnNum = rowName.length;              HSSFRow rowRowName = sheet.createRow(0);                // 在索引2的位置创建行(最顶端的行开始的第二行)                            // 将列头设置到sheet的单元格中              for(int n=0;n
        
           dataList = new ArrayList
         
          ();            private HttpServletResponse  response = null;        private HSSFWorkbook workbook = null;        private OutputStream out = null;          //构造方法，传入要导出的数据      public LeadingOutExcel(String fileName,String title,String[] rowName,List
          
            dataList,HttpServletResponse response){ this.fileName = fileName; this.dataList = dataList; this.rowName = rowName; this.title = title; this.response = response; } public void export () throws Exception{ HSSFWorkbook createExcel = this.createExcel(); this.writeInOutputStream(createExcel); } /* * 导出数据 * */ public HSSFWorkbook createExcel() throws Exception{ try{ workbook = new HSSFWorkbook(); // 创建工作簿对象 HSSFSheet sheet = workbook.createSheet(title); // 创建工作表 /* * 产生表格标题行 HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0);//设置开头两行 * 使用条件： * HSSFRow rowRowName = sheet.createRow(0);设置索引2的位置创建行 * HSSFRow row = sheet.createRow(i+1);//创建所需的行数 改为i+3 * */ //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象 HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象 /* * 产生表格标题行 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1))); //设置开头两行合并 cellTiltle.setCellStyle(columnTopStyle); //设置列头单元格样式 cellTiltle.setCellValue("");//设置空单元格的内容。比如传入List的值是空的。 */ // 定义所需列数 int columnNum = rowName.length; HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行) // 将列头设置到sheet的单元格中 for(int n=0;n

使用例子（项目代码只能模仿）：

/**     * 条件导出      * @param gameId     * @param startDate     * @param endDate     * @param level     * @param response     */    @RequestMapping(value="/conditionalExportDate")    public void conditionalExportDateMethod(        @RequestParam(value="gameId") String gameId,        @RequestParam(value="startDate") String startDate,        @RequestParam(value="endDate") String endDate,        @RequestParam(value="level") String level,        HttpServletResponse response    ){        Map
     
       conditionMap = null;        List
      
        dataSet = null;        LeadingOutExcel leadingOutExcel = null;    //工具类                //配置信息        String fileName = "client";        String format = "yyyy-MM-dd hh:mm:ss";        String title = "用户信息";        String[] rowName = { "编号","微信号","微信名称", "手机号", "参与活动时间","中奖等级", "获得奖品" };                //查询条件        conditionMap = new HashMap
       
        ();        conditionMap.put("gameId", gameId);        //导出全部        if(!"请选择日期".equals(startDate) && !"请选择日期".equals(endDate)){            conditionMap.put("startDate", startDate);            conditionMap.put("endDate", endDate);        }        if(!"请输入中奖等级".equals(level)){            conditionMap.put("level", level);        }                dataSet = playRecordService.conditionQuery(conditionMap);                List
        
           dataList = new ArrayList
         
          ();          Object[] objs = null;          for (int i = 0; i < dataSet.size(); i++) {              PlayRecord man = dataSet.get(i);              objs = new Object[rowName.length];              objs[0] = i;              objs[1] = man.getOpenid();            objs[2] = man.getNickName();            objs[3] = man.getPhoneNumber();                //日期类型处理                Date date = man.getPalyTime();                String dateStr = "";                if(date!=null){                    SimpleDateFormat df = new SimpleDateFormat(format);                      dateStr = df.format(date);                }            objs[4] = dateStr;            objs[5] = man.getLevel();            objs[6] = man.getAwardName();              dataList.add(objs);          }                  leadingOutExcel = new LeadingOutExcel(fileName,title, rowName, dataList,response);        try {            leadingOutExcel.export();        } catch (Exception e) {            log.error("写入Excle出错！", e);        }    }}