Excel表格导入数据库及其下载

Excel表格的导入导出


1.读取指定Excel到数据库

    springMvc上传获取文件

//导入excel表格
public ResultData importMbrExcel(HttpServletRequest request,
                 HttpServletResponse response) throws Exception {
     ResultData resultData = new ResultData();
     // 拿到所有的上传文件MultipartHttpServletRequest 
     MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    //返回一个IteratorString对象，其中包含此请求中包含的多部分文件的参数名称
     Iterator<String> iter = multipartRequest.getFileNames();
        //        新建一个MultipartFile类型的文件
      MultipartFile multipartFile=null; 
        //遍历文件名
      while (iter.hasNext()) {
        //或得某个文件名
         String filefiled = iter.next();
        //根据文件名获取文件对象
         multipartFile = multipartRequest.getFile(filefiled);
           //将文件交给POI读取    详见POI工具类(下面)
         list = POIUtil.readExcel(multipartFile);
           // 处理list生成实体类 存入数据库
            if(list.size()>0) {
                 for(String[] str:list) {
                     try {
                     if(str[3]==null&&" ".equals(str[3])) continue;
                 //code String code = CodeUtils.generateUUID();
                     mbr.setCode(code);
                 //tenantCode mbr.setTenantCode(tenantCode);
                 //会员编号
                     String memberCode = DateUtils.format(new Date(), "yyMMddHHmmss"); 
                 //去重导入
                //根据唯一值 查询数据库是否有
                 ResultData queryList = mbrMemberService.query(request, response, mbr);
                 Map<String, Object> userData = (Map<String, Object>)queryList.getUserData();
                 List<MbrMember> lists =(List<MbrMember>)userData.get("mbrMemberList");
                 if(lists.size()>0) {
                   // 记录重复数据的关键信息
                     Map<String,String> mbr2 = new HashMap<>(3);
                     mbr2.put("mbrNum", str[0]);
                     mbr2.put("mbrName", str[3]);
                     mbr2.put("mbrContactPhone", str[12]);
                     mbrMembers2.add(mbr2);
                     continue;
                 }
            //导入数据库
             resultData = mbrMemberService.improtPer(request, response, mbr);
         } catch (Exception e) {
             e.printStackTrace();
            //捕获异常    将异常的记录信息记录
             Map<String,String> mbrs = new HashMap<>(3);
             mbrs.put("mbrNum", str[0]);
             mbrs.put("mbrName", str[3]);
             mbrs.put("mbrContactPhone", str[12]);
             mbrMembers.add(mbrs);
             continue;
         }
     }
    }
 }
//判断是否有未导入数据
     if(mbrMembers.size()>0||mbrMembers2.size()>0) {
             maps=new HashMap<>();
            //导出Excel的表头
             String[] excelHeader = {"编号","客户名称","联系电话"};
            //表头对应的maps数据的键
             String[] excelHeaderName= {"mbrNum","mbrName","mbrContactPhone"};
            //页名
             String sheetName = "导入失败的客户";
             String sheetName2 = "导入重复的客户";
               //调用工具类里的方法
             HSSFWorkbook wb =  POIUtil.saveExcel(excelHeader,
        excelHeaderName, mbrMembers,mbrMembers2, sheetName,sheetName2);
            //自定义保存路径
         String filePath = "/importError/"+tenantCode;
        //处理保存文件名
         String fileName = multipartFile.getOriginalFilename();
         int lastindex =fileName.lastIndexOf(".");
         fileName=fileName.substring(0,lastindex);
         fileName = fileName+"导入失败客户.xls";
        //保存到服务器
         String uploadExcelPath = this.uploadExcel(wb, filePath,fileName);
         //添加到数据库
         MbrImportNotSuccess mbrImportNotSuccess = new MbrImportNotSuccess();
         String code = CodeUtils.generateUUID(); mbrImportNotSuccess.setCode(code);         mbrImportNotSuccess.setTenantCode(tenantCode); mbrImportNotSuccess.setIstrue("1");         mbrImportNotSuccess.setFileName(fileName); mbrImportNotSuccess.setCreateTime(new Date());         mbrImportNotSuccess.setExcelPath(filePath+"/"+fileName);         mbrImportNotSuccessDao.insert(mbrImportNotSuccess); maps.put("filePath", filePath);
         }
     resultData.setResult(true);
     resultData.setUserData(maps);
     return resultData;
 }

将文件保存服务器 保存导入失败的数据

public String uploadExcel(HSSFWorkbook wb,String filePath,
                                            String fileName)throws Exception {
             String dirPath = AppConfig.getUploadRoot() + filePath;
             File dirFile = new File(dirPath);
             if (!dirFile.exists()){
                     dirFile.mkdirs();
                 }
             File file = new File(dirPath,fileName);
             FileOutputStream foStream = new FileOutputStream(file);
                 wb.write(foStream);
                 wb.close();
                 foStream.close();
                 return filePath;
     }

工具类 可直接用

//简书这一块真的很不好,复制的代码过来结构全变了  省略倒包
public class POIUtil {
         private static Logger logger = Logger.getLogger(POIUtil.class);
         private final static String xls = "xls";
         private final static String xlsx = "xlsx";
        //获得当前行的开始列 这个是不算空值的所以用的时候有表头来获取,毕竟表头不会空呦
         private static int firstCellNum;
         //获得当前行的结束列的下标
         private static int lastCellNum;

         /** * 读入excel文件，解析后返回 * @param file * @throws IOException */
     public static List<String[]> readExcel(MultipartFile file) throws IOException{
             //检查文件 本地方法见下
             checkFile(file);
            //获得Workbook工作薄对象  本地方法见下
             Workbook workbook = getWorkBook(file);
         //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
             List<String[]> list = new ArrayList<String[]>();
             if(workbook != null){
                    for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                     //获得当前sheet工作表
                     Sheet sheet = workbook.getSheetAt(sheetNum);
                     if(sheet == null){
                         continue;
                     }
                     //获得当前sheet的开始行
                     int firstRowNum = sheet.getFirstRowNum();
                     //获得当前sheet的结束行
                     int lastRowNum = sheet.getLastRowNum();
                      //获取一个可用的表头
                        Row hander = sheet.getRow(firstRowNum);
                        //获取此sheet的开始列
                        firstCellNum= hander.getFirstCellNum();
                        //获取此sheet的结束列
                         lastCellNum= hander.getPhysicalNumberOfCells();
                     //循环所有行 根据表格的真是数据选择开始行去掉两行表头
                     for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){
                         //获得当前行
                         Row row = sheet.getRow(rowNum);
                         if(row == null){
                             continue;
                             }
                        //新建一个和当前列等大的数组
                     String[] cells = new String[lastCellNum];
                     //循环当前行
                         for(int cellNum = firstCellNum; cellNum < lastCellNum;
                                        cellNum++){
                            //根据列编号得到列对象
                             Cell cell = row.getCell(cellNum);
                               //得到列值 并放到数组中 本地方法(见下)
                             cells[cellNum] = getCellValue(cell);
                           }
                         list.add(cells);
                         }
                 }
                 workbook.close();
             }
             return list;
         }
//检查文件
 public static void checkFile(MultipartFile file) throws IOException{
         //判断文件是否存在
         if(null == file){
             logger.error("文件不存在！");
             throw new FileNotFoundException("文件不存在！");
           }
         //获得文件名
            //File类型的文件获取文件名
         //String fileName = file.getName();
        //MultipartFile类型的文件获得文件名
         String fileName = file.getOriginalFilename();
         //判断文件是否是excel文件
         if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
                 logger.error(fileName + "不是excel文件");
                 throw new IOException(fileName + "不是excel文件");
         }
     }
//根据Excel表格得到Workbook
 public static Workbook getWorkBook(MultipartFile file) {
     //获得文件名
     //File类型的文件获取文件名
     //String fileName = file.getName();
    //MultipartFile类型的文件获得文件名
     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);
         }
             is.close();
         } catch (IOException e) {
             logger.info(e.getMessage());
         } return workbook;
   }
//得到中的值
 public static String getCellValue(Cell cell){
     String cellValue = "";
     if(cell == null){
         return cellValue;
     }
     //把数字当成String来读，避免出现1读成1.0的情况
     if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
             if (HSSFDateUtil.isCellDateFormatted(cell)) {
                 Date date = cell.getDateCellValue();
                 cellValue = DateFormatUtils.format(date, "yyyy-MM-dd");
                 return cellValue;
                 }
             cell.setCellType(Cell.CELL_TYPE_STRING); }
         //判断数据的类型
         switch (cell.getCellType()){
             case Cell.CELL_TYPE_NUMERIC: //数字
             cellValue = String.valueOf(cell.getNumericCellValue()); break;
             case Cell.CELL_TYPE_STRING: //字符串
             cellValue = String.valueOf(cell.getStringCellValue()); break;
             case Cell.CELL_TYPE_BOOLEAN: //Boolean
             cellValue = String.valueOf(cell.getBooleanCellValue()); break;
             case Cell.CELL_TYPE_FORMULA: //公式
             cellValue = String.valueOf(cell.getCellFormula()); break;
             case Cell.CELL_TYPE_BLANK: //空值
             cellValue = ""; break;
             case Cell.CELL_TYPE_ERROR: //故障
             cellValue = "非法字符"; break; default:
             cellValue = "未知类型"; break;
         }
         return cellValue;
     }

 //生成Excel表格
 public static HSSFWorkbook saveExcel(String[] excelHeader,
        String[] excelHeaderName,List<Map<String, String>> mbrMembers, 
                  String sheetName)throws Exception {
                //新建工作簿
                 HSSFWorkbook wb = new HSSFWorkbook();
                 //新建一页    setl页名
                 HSSFSheet sheet = wb.createSheet(sheetName);
                 //新建页2     如果需要
                // HSSFSheet sheet2 = wb.createSheet(sheetName2);
                 //新建行 此行为表头
                 HSSFRow row = sheet.createRow((int) 0);
                 //新建页2的行 如果需要
                 HSSFRow row2 = sheet2.createRow((int) 0);
                 //列样式    表头
                  HSSFCellStyle headerStyle = wb.createCellStyle(); 
                    //设置水平居中
                    headerStyle.setAlignment(HorizontalAlignment.CENTER);      
                     //设置前景颜色
                  headerStyle.setFillForegroundColor(
                      HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
                //设置单元格填充样式，SOLID_FOREGROUND纯色使用前景颜色填充
                  headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                  //字体 
                 HSSFFont headerFont = wb.createFont();
                    // 字体加粗
                     headerFont.setBold(true);
                 headerStyle.setFont(headerFont);
                 headerStyle.setBorderBottom(BorderStyle.THIN); // 下边框                                       headerStyle.setBorderLeft(BorderStyle.THIN); // 左边框                                       headerStyle.setBorderTop(BorderStyle.THIN); // 上边框                                       headerStyle.setBorderRight(BorderStyle.THIN); // 右边框
                 headerStyle.setWrapText(true);
                 HSSFCellStyle bodyStyle = wb.createCellStyle();
                 bodyStyle.setBorderBottom(BorderStyle.THIN); // 下边框                     bodyStyle.setBorderLeft(BorderStyle.THIN); // 左边框                                     bodyStyle.setBorderTop(BorderStyle.THIN); // 上边框                     bodyStyle.setBorderRight(BorderStyle.THIN); // 右边框
         for (int i=0; i<excelHeader.length; i++) {
                 HSSFCell cell = row.createCell(i);
                 HSSFCell cell2 = row2.createCell(i);
                 cell.setCellStyle(headerStyle);
                 cell.setCellValue(excelHeader[i]);
                 cell2.setCellStyle(headerStyle);
                 cell2.setCellValue(excelHeader[i]);
                 sheet.setColumnWidth(i, 30*256);
                 sheet2.setColumnWidth(i, 30*256);
             }
         for (int i=0; i<mbrMembers.size(); i++) {
                 row = sheet.createRow(i + 1);
                 Map<String, String> excelRow = (Map<String, String>) mbrMembers.get(i);
               for (int j=0; j<excelHeader.length; j++) {
                     HSSFCell cell = row.createCell(j); cell.setCellStyle(bodyStyle);                 
                    cell.setCellValue(
           String.valueOf(excelRow.get(excelHeaderName[j]) == null ? "-----" :excelRow.get(excelHeaderName[j])));                     }
         }
         wb.close();
         return wb;
     }
}

浪客行1213的简书

XHH
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,445评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,889评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,047评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,760评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,745评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,638评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,011评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,669评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,923评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,655评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,740评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,406评论 4赞 320
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,995评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,961评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,023评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,483评论 2赞 342