"Java Code"--java implements exporting pictures and data in Excel

Preface

Recently, the company needs to do export pictures and data in Excel, although not a new technology, but my business needs pictures and data correspondence, and each row will certainly have data, but not necessarily a picture, this time need to consider how to ensure that the picture and data is one by one correspondence, pictures and data go in a different way, So this is the time to consider getting every row of pictures, in the process of looping row, to determine row equality to take out the picture, otherwise it is currently the data does not have pictures. content

Control Layer Code

/** * @param key * @param file * @param request * @param response * void * @description Import Excel data into the database * @date April 10, 2018 * @author/@RequestMapping ("/importexcel") @ Responsebody public map<string, Object> Importexcel (HttpSession session, @RequestParam ("Lefile") Multipartfile F Ile, HttpServletRequest request, httpservletresponse response) {map<string, object> Map = new H
        Ashmap<> ();
        int code = Constants.success_code;
        String message = Constants.success_mgs;
        Boolean isExcel2003 = true;
        String basepath = Session.getservletcontext (). Getrealpath ("/zjk/zjzp/");
            try {String filename = file.getoriginalfilename ();
            if (isExcel2007 (filename)) {isExcel2003 = false;
            int count = Datalistservice.importexceldata (File.getinputstream (), isExcel2003, BasePath); if (couNT <= 0) {code = Constants.failture_code;
            message = Constants.failture_mgs;
            The catch (IOException e) {code = Constants.failture_code;
            message = Constants.failture_mgs;
        E.printstacktrace ();
        } map.put ("Code", code);
        Map.put ("message", message);

    return map; public static Boolean isExcel2003 (String filepath) {return filepath.matches ("^.+\\.")
    i) (XLS) $ "); public static Boolean isExcel2007 (String filepath) {return filepath.matches ("^.+\\.")
    i) (xlsx) $ "); }

Service Layer Code

@Transactional public int Importexceldata (InputStream is, Boolean isExcel2003, String basepath) {int count =
        0;
        map<string, picturedata> sheetindexpicmap = new hashmap<string, picturedata> ();
            try {Workbook workbook = null;
            Sheet Sheet = null;
                if (isExcel2003) {workbook = new Hssfworkbook (IS);
                Sheet = workbook.getsheetat (0);
            Sheetindexpicmap = Excelimageutil.getsheetpictures03 (0, (hssfsheet) sheet, workbook);
                else {workbook = new Xssfworkbook (IS);///2007 sheet = workbook.getsheetat (0);
            Sheetindexpicmap = Excelimageutil.getsheetpictures07 (0, (xssfsheet) sheet, workbook);
            } list<zjmodel> List = readexcelvalue (workbook, Sheet, basepath, sheetindexpicmap);
        Save to the database count = datalistdao.insertzjlist (list);
       catch (Exception e) {     E.printstacktrace ();
    return count; Public list<zjmodel> Readexcelvalue (Workbook wb, Sheet Sheet, String basepath, map<string, PictureD
        Ata> sheetindexpicmap) {//Get the first sheet list<zjmodel> zjmodels = new arraylist<zjmodel> ();
            try {Integer totalcells = 0;
            Integer totalrows = Sheet.getphysicalnumberofrows (); if (Totalrows > 1 && sheet.getrow (0)!= null) {totalcells = Sheet.getrow (0). Getphysicalnumbero
            Fcells ();
                for (int i = 1; i < totalrows i++) {Row row = Sheet.getrow (i);
                if (row = = null) {continue;
                } Zjmodel Zjmodel = new Zjmodel ();
                String uuid = Uuid.randomuuid (). toString (). ReplaceAll ("-", "");
                Zjmodel.setid (UUID);
     if (Sheetindexpicmap.get ("" + i + "")!= null) {//Picture information               String Picuuid = Uuid.randomuuid (). toString (). ReplaceAll ("-", "");
                    PictureData PictureData = Sheetindexpicmap.get ("" + i + "");
                    byte[] buffer = Picturedata.getdata (); String ext = picturedata.suggestfileextension ();//get file name extension; FileOutputStream outputstream = new Fileou
                    Tputstream (basepath + Picuuid + "." + ext);
                    Outputstream.write (buffer);
                    Outputstream.close ();
                ZJMODEL.SETZJZP (Picuuid + "." + ext); }else{Zjmodel.setzjzp (constants.default_file_name);//Default picture} for (int j = 0; J < Totalcells;
                    J + +) {cell cell = Row.getcell (j);
                            if (cell!= null) {switch (j) {Case 1://expert name
                            Zjmodel.setname (Cell.getstringcellvalue ()); Zjmodel.sETYWM (Cell.getstringcellvalue ());
                        Break
                            Case 2://Chinese translation Zjmodel.setzwym (Cell.getstringcellvalue ());
                        Break
                        Case 3://engaged in the field//photo break;
                            Case 4://Unit ZJMODEL.SETDW (Cell.getstringcellvalue ());
                        Break
                            Case 5://Professional//title ZJMODEL.SETZW (Cell.getstringcellvalue ());
                        Break
                                Case 6://Contact if (cell.getcelltype () = Cell.cell_type_numeric) {
                                BigDecimal BigDecimal = new BigDecimal (Cell.getnumericcellvalue ());
                                String phone = bigdecimal.tostring ();
                            Zjmodel.setphone (phone);
   } break;                     Case 7://fixed telephone if (cell.getcelltype () = = Cell.cell_type_numeric) {
                                BigDecimal BigDecimal = new BigDecimal (Cell.getnumericcellvalue ());
                                String Tel = bigdecimal.tostring ();
                            Zjmodel.settel (tel);
                        } break;
                                Case 8://Fax if (cell.getcelltype () = Cell.cell_type_numeric) {
                                BigDecimal BigDecimal = new BigDecimal (Cell.getnumericcellvalue ());
                                String Fax = bigdecimal.tostring ();
                            Zjmodel.setfax (fax);
                        } break;
                            Case 9://Mailbox Zjmodel.setemail (Cell.getstringcellvalue ());
                        Break
        Case 10://ID Number                    Zjmodel.setidnumber (Cell.getstringcellvalue ());
                        Break
                            Case 11://Passport No./and valid document Zjmodel.sethz (Cell.getstringcellvalue ());
                        Break
                            Case 12://Engaged in field zjmodel.setcsly (Cell.getstringcellvalue ());
                        Break
                            Case 13://Education ZJMODEL.SETXL (Cell.getstringcellvalue ());
                        Break
                            Case 14://Professional Zjmodel.setzy (Cell.getstringcellvalue ());
                        Break
                            Case 15://Nationality Zjmodel.setgj (Cell.getstringcellvalue ());
                        Break
                            Case 16://Sex Zjmodel.setsex (cell.getstringcellvalue () = "male"? 0:1);
       Break                 Case 17://bank card number Zjmodel.setyhkh (Cell.getstringcellvalue ());
                        Break
                            Case 18://Bank ZJMODEL.SETKHH (Cell.getstringcellvalue ());
                        Break
                            Case 19://Bank Code ZJMODEL.SETYHDM (Cell.getstringcellvalue ());
                        Break
                            Case 20://Address zjmodel.setaddress (Cell.getstringcellvalue ());
                        Break
                            Case 21:ZJMODEL.SETBZ (Cell.getstringcellvalue ());
                        Break
            }} zjmodels.add (Zjmodel);
        } catch (Exception e) {e.printstacktrace ();
    return zjmodels;
 }

Because of the different ways to get pictures in the Excel2003 and Excel2007 versions, two methods are extracted

public class Excelimageutil {public static map<string, picturedata> getSheetPictures03 (int sheetnum,hssfsheet She
      Et,workbook workbook) {map<string,picturedata> sheetindexpicmap=new hashmap<string, PictureData> (); @SuppressWarnings ("unchecked") list 
Summary 
This is also the accumulation of code, I believe that every time the summary of the code will be on their own ability to upgrade, thank you for your browsing, the code can improve the progress, please do not hesitate to enlighten.