excel导入数据到数据库 文件上传通过流再上传到第三方服务器(含批量上传)

import com.alibaba.fastjson.JSON;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;


import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
public class MerchantUploadController {


    private final static String XLS = "xls";
    public static final String XLSX = "xlsx";

    @RequestMapping(value = "/upload/excel", method = RequestMethod.POST)
    public Json upload(MultipartFile file) {
        Workbook workbook = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            try {
                workbook = new HSSFWorkbook(file.getInputStream());

            } catch (IOException e) {
                e.printStackTrace();
            }
        } else if (fileName.endsWith(XLSX)) {
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            throw new BizException("文件不是Excel文件");
        }
        Sheet sheet = workbook.getSheet("sheet1");
        int rows = sheet.getLastRowNum();
        if (rows == 0) {
            throw new BizException("数据为空,请上传不为空的数据文档");
        }
        for (int i = 1; i <= rows + 1; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                String rowOne = getCellValue(row.getCell(0));
                String rowTwo = getCellValue(row.getCell(1));
                String rowThree = getCellValue(row.getCell(2));
                Map obj = new HashMap();
                obj.put("bankCode", rowOne);
                obj.put("bankName", rowTwo);
                obj.put("bankFullName", rowThree);
                obj.put("createTime", DateUtil.getNow());
                Db.table("biz_system_bank").insert(obj);
            }
        }
        return json(BizUtils.SUCCESS_CODE, BizUtils.SUCCESS_MESSAGE);
    }


    public String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:// 数字
                    value = cell.getNumericCellValue() + " ";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date); //日期格式化
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: //  字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:   // Boolean类型
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:   // 空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 错误类型
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }

        }
        return value.trim();
    }

    @Value("${file.server.host}")
    private String fileHost;

    @RequestMapping(value = "/upload/file/list", method = RequestMethod.POST)
    public Json uploadList(@RequestParam(value = "file", required = false) MultipartFile[] file, HttpServletResponse response) {
        if (file.length <= 0) {
            throw new BizException("请上传图片");
        }
        List<Map> resultList = new ArrayList<>();
        for (MultipartFile multipartFile : file) {
            String uploadFileUrl = fileHost + "/file/upload";
            File f = null;
            if (multipartFile.equals("") || multipartFile.getSize() <= 0) {
                multipartFile = null;
            } else {
                InputStream ins = null;
                try {
                    ins = multipartFile.getInputStream();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                f = new File(multipartFile.getOriginalFilename());
                inputStreamToFile(ins, f);
            }

            File del = new File(f.toURI());
            String result = sendPostUplodFile(uploadFileUrl, del);

            //TODO 传到第三方
            Map map = JSON.parseObject(result, Map.class);
            f.delete();
            del.delete();
            resultList.add((Map) map.get("data"));
        }
        return json(resultList);
    }


    @RequestMapping(value = "/upload/file", method = RequestMethod.POST)
    public Json upload(MultipartFile file, HttpServletResponse response) {
        String uploadFileUrl = fileHost + "/file/upload";
        File f = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            try {
                ins = file.getInputStream();
            } catch (IOException e) {
                e.printStackTrace();
            }
            f = new File(file.getOriginalFilename());
            inputStreamToFile(ins, f);
        }
        File del = new File(f.toURI());
        String result = sendPostUplodFile(uploadFileUrl, del);
        //TODO 传到第三方
        Map map = JSON.parseObject(result, Map.class);
        f.delete();
        del.delete();
        return json(map.get("data"));
    }

    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static String sendPostUplodFile(String url, File file) {
        DataOutputStream out = null;
        BufferedReader in = null;
        String result = "";
        try {
            URL realUrl = new URL(url);
            //打开和URL之间的连接
            HttpURLConnection conn = (HttpURLConnection) realUrl.openConnection();
            //发送POST请求必须设置如下两行
            conn.setDoOutput(true);
            conn.setDoInput(true);
            String BOUNDARY = "----WebKitFormBoundary07I8UIuBx6LN2KyY";
            conn.setUseCaches(false);
            conn.setRequestMethod("POST");
            conn.setRequestProperty("connection", "Keep-Alive");
            conn.setRequestProperty("user-agent", "Mozilla/4.0 (conpatible; MSIE 6.0; Windows NT 5.1; SV1)");
            conn.setRequestProperty("user-agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.100 Safari/537.36");
            conn.setRequestProperty("Charsert", "UTF-8");
            conn.setRequestProperty("Content-Type", "multipart/form-data; boundary=" + BOUNDARY);
            conn.connect();
            out = new DataOutputStream(conn.getOutputStream());
            byte[] end_data = ("\r\n--" + BOUNDARY + "--\r\n").getBytes();
            //添加参数
            StringBuffer sb1 = new StringBuffer();
            sb1.append("--");
            sb1.append(BOUNDARY);
            sb1.append("\r\n");
            sb1.append("Content-Disposition: form-data;name=\"luid\"");
            sb1.append("\r\n");
            sb1.append("\r\n");
            sb1.append("123");
            sb1.append("\r\n");
            out.write(sb1.toString().getBytes());
            //添加参数file
            StringBuffer sb = new StringBuffer();
            sb.append("--");
            sb.append(BOUNDARY);
            sb.append("\r\n");
            sb.append("Content-Disposition: form-data;name=\"file\";filename=\"" + file.getName() + "\"");
            sb.append("\r\n");
            sb.append("Content-Type: application/octet-stream");
            sb.append("\r\n");
            sb.append("\r\n");
            out.write(sb.toString().getBytes());
            DataInputStream in1 = new DataInputStream(new FileInputStream(file));
            int bytes = 0;
            byte[] bufferOut = new byte[1024];
            while ((bytes = in1.read(bufferOut)) != -1) {
                out.write(bufferOut, 0, bytes);
            }
            out.write("\r\n".getBytes());
            in1.close();
            out.write(end_data);
            //flush输出流的缓冲
            out.flush();
            //定义BufferedReader输入流来读取URL的响应
            in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
            String line;
            while ((line = in.readLine()) != null) {
                result += line;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (in != null) {
                    in.close();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            return result;
        }

    }


}