MultipartFile上传csv和excel文件保存到数据库中

蔚落 2022-05-14 07:12 784阅读 0赞

springMVC中MultipartFile接收文件：

导入CSV文件：csv->json->list<类>

public List<T> readCsv(MultipartFile file){
            List<T> list = new ArrayList<T>();
    
            if (!file.isEmpty()){
                InputStreamReader isr = null;
                BufferedReader br = null;
                try {
                    isr = new InputStreamReader(file.getInputStream());
                    br = new BufferedReader(isr);
                    String line = null;
                    List<List<String>> strs = new ArrayList<List<String>>();
                    while ((line = br.readLine()) != null){
                        strs.add(Arrays.asList(line.split(",")));
                    }
                    JSONArray array = toJsonArray(strs);
                    list = array.toJavaList(T.class);
                } catch (IOException e) {
                   //
                }finally {
                    try {
                        if (br != null){
                            br.close();
                        }
                        if (isr != null){
                            isr.close();
                        }
                    } catch (IOException e) {
                        //
                    }
                }
            }else {
                //
            }
            return list;
        }
    
        //fastjson
        private JSONArray toJsonArray(List<List<String>> strs){
            JSONArray array = new JSONArray();
            for (int i = 1; i < strs.size(); i++) {
                JSONObject object = new JSONObject();
                for (int j = 0; j < strs.get(0).size(); j++) {
                    object.put(strs.get(0).get(j),strs.get(i).get(j));
                }
                array.add(object);
            }
            return array;
        }

导出excel文件：

public class ExportExcelFile {
        private static final String EXCEL_XLS = "xls";
        private static final String EXCEL_XLSX = "xlsx";
    
       
        //判断版本获取Wordboook
        private static Workbook getWorkbook(InputStream in, String fileName) throws IOException {
            Workbook wbook = null;
            if (fileName.endsWith(EXCEL_XLS)) {
                wbook = new HSSFWorkbook(in);
            } else if (fileName.endsWith(EXCEL_XLSX)) {
                wbook = new XSSFWorkbook(in);
            }
            return wbook;
        }
    
      
        //解析excel文件问对象集合
        public static List<T> getExcelData(MultipartFile mfile, String fileName) {
            List<List<String>> lists = new ArrayList<List<String>>();
            try {
                Workbook workbook = getWorkbook(mfile.getInputStream(), fileName);
                //获取Sheet的数量
                int sheetCount = workbook.getNumberOfSheets();
                // 第一个Sheet
                Sheet sheet = workbook.getSheetAt(0);
                //表头
                Row rowHead = sheet.getRow(0);
                //总列数
                int columns = rowHead.getPhysicalNumberOfCells();
                //总行数
                int lines = sheet.getPhysicalNumberOfRows();
                //循环获取每行数据
                for (int i = 0; i < lines; i++) {
                    //循环获取每列
                    List<String> list = new ArrayList<String>();
                    for (int j = 0; j < columns; j++) {
                        if (sheet.getRow(i).getCell(j) != null){
                            Object obj = getValue(sheet.getRow(i).getCell(j));
                            list.add(String.valueOf(obj));
                        }else {
                            list.add(null);
                        }
                    }
                    lists.add(list);
    
                }
            }catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
    
            JSONArray array = toJsonArray(lists);
            return array.toJavaList(User.class);
        }
    
         //获取对应的枚举类型数据
        private static Object getValue(Cell cell) {
            Object obj = null;
            switch (cell.getCellTypeEnum()) {
                case BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case ERROR:
                    obj = cell.getErrorCellValue();
                    break;
                case NUMERIC:
                    obj = cell.getNumericCellValue();
                    break;
                case STRING:
                    obj = cell.getStringCellValue();
                    break;
                default:
                    break;
            }
            return obj;
        }