@Service
public class LogService {

   /**
     * 文件上传
     *
     * @param file
     * @return
     * @throws Exception
     */
    public ResultVo upLogLoad(MultipartFile file) throws Exception {
        if (file == null || file.isEmpty()) {
            return ResultVo.error(1, "未选择需上传的日志文件");
        }
        //获取绝对路径
        String filePath = new File("src/main/java/com/example/thyy/file").getAbsolutePath();
        //获取文件名
        String fileName = file.getOriginalFilename();
        //判断文件类型，这里我们只要xls文件
        if (fileName != null && !fileName.endsWith(".xls"))
            return ResultVo.error(1, "请选择“xls”文件");
        //获取文件上传名称
        File fileUpload = new File(filePath, fileName);
        if (fileUpload.exists()) {
            return ResultVo.error(1, "上传的日志文件已存在");
        }
       
        try {
            //上传文件
            file.transferTo(fileUpload);
            return ResultVo.success();
        } catch (IOException e) {
            return ResultVo.error(1, "上传日志文件到服务器失败" + e.toString());
        }


    }

}

注意：filePath是指上传的文件，存到那个目录上。我这里我只写相对路径，然后后在通过File.getAbsolutePath()获取文件全路径。

2.创建一个控制器LogController

RestController
@RequestMapping("/log")
public class LogController {

    @Autowired
    LogService logService;

    @PostMapping(value = "/upload")
    public ResultVo logUpload(@RequestParam("file") MultipartFile file) throws Exception {
        return logService.upLogLoad(file);
    }

}

3.然后我们就可以启动项目，访问对应的url。

我用postman测试如下

然后就能看到我们上传的文件了

二、下载

1.在LogService文件基础上，新增logDownload方法

 /**
     * 下载文件
     * @param name
     * @param response
     * @return
     * @throws Exception
     */
    public ResultVo logDownload(String name, HttpServletResponse response) throws Exception {
        //根据name，找到文件目录下，对应的文件
        File file = new File("src/main/java/com/example/thyy/file/" + File.separator + name);
        if (!file.exists()) {
            return ResultVo.error(1, name + "文件不存在");
        }
//        声明传输的内内容类型
        response.setContentType("application/force-download");
        response.addHeader("Content-Disposition", "attachment;fileName=" + name);

        byte[] buffer = new byte[1024];
        try (FileInputStream fis = new FileInputStream(file);
             BufferedInputStream bis = new BufferedInputStream(fis)) {
            OutputStream os = response.getOutputStream();
            int i = bis.read(buffer);
            while (i != -1) {
                os.write(buffer, 0, i);
                i = bis.read(buffer);
            }
        }
        return ResultVo.success();
    }

2.在LogController文件基础上，新增logDownload方法


    @GetMapping(value = "/downlod/{name}")
    public ResultVo logDownload(@PathVariable String name, HttpServletResponse response) throws Exception {
        return logService.logDownload(name, response);
    }

3.访问下载url

url正确时

url错误时

注意：在下载URL后加上我们要下载的文件名。

三、解析xls文件

1.导入poi.jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

为方便写测试，再httpclient包

        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5.6</version>
        </dependency>

2.创建一个解析xls的工具类

public class HandleFile{


    /**
     * 解析Excel文件
     *
     * @param in
     * @param fileName
     */
    public static List<List<Object>> parseExcel(InputStream in, String fileName) throws Exception {
        List list = null;
        Workbook work = null;
        list = new ArrayList<>();
        //创建Excel工作薄
        work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }

                List<Object> li = new ArrayList<>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    String value = "";
                    cell = row.getCell(y);
                    switch (cell.getCellType()) {
                        // 数字
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            //如果为时间格式的内容
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                value=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                                li.add(value);
                                break;
                            }else {
                                value= String.valueOf(cell.getNumericCellValue());
                                String[] split = value.split("\\.");
                                //整型不保留小数部分
                                if (split[1].length()==1 && split[1].equals("0")){
                                    value = split[0];
                                    li.add(value);
                                    break;
                                }
                                li.add(value);
                                break;
                            }
                            // 字符串
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            li.add(value);
                            break;
                        // Boolean
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue() + "";
                            li.add(value);
                            break;
                        // 公式
                        case HSSFCell.CELL_TYPE_FORMULA:
                            value = cell.getCellFormula() + "";
                            li.add(value);
                            break;
                        // 空值
                        case HSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            li.add(value);
                            break;
                        // 故障
                        case HSSFCell.CELL_TYPE_ERROR:
                            value = "非法字符";
                            li.add(value);
                            break;
                        default:
                            value = "未知类型";
                            li.add(value);
                            break;
                    }
                }
                list.add(li);
            }
        }
        return list;

    }


    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }

}

3.使用HandleFile工具类

写个测试类

 @Test
 public void testExel() throws Exception {
//       File file= new File("C:/Users/Administrator/Documents/新建 XLS 工作表.xls");
        File file = new File("C:/Users/Administrator/Documents/Tencent Files/229068393/FileRecv/202110-205.xlsx");
        if (!file.exists()) {
            throw new Exception("不存在文件");
        }
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            MultipartFile multipartFile = new MockMultipartFile("copy" + file.getName(), file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
            InputStream inputStream = multipartFile.getInputStream();
            //将文件的所有数据处理成了List<List<Object>> list
            List<List<Object>> list = HandleFile.parseExcel(inputStream, multipartFile.getOriginalFilename());
            inputStream.close();
            for (int i = 0; i < list.size(); i++) {
                List<Object> lo = list.get(i);
                System.out.println(lo);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

注意：file是我们要解析的文件的路径

然后我们就得到了文件的内容了

3.改造一下LogService.upLogLoad方法，实现文件上传+文件解析

 /**
     * 文件上传
     *
     * @param file
     * @return
     * @throws Exception
     */
    public ResultVo upLogLoad(MultipartFile file) throws Exception {
        if (file == null || file.isEmpty()) {
            return ResultVo.error(1, "未选择需上传的日志文件");
        }
        //获取绝对路径
        String filePath = new File("src/main/java/com/example/springboot4/file").getAbsolutePath();
        //获取上传时文件名
        String fileName = file.getOriginalFilename();
        //判断文件类型，这里我们只要xls文件
        if (fileName != null && !fileName.endsWith(".xls"))
            return ResultVo.error(1, "请选择“xls”文件");
        //获取文件上传名称
        File fileUpload = new File(filePath, fileName);
        if (fileUpload.exists()) {
            return ResultVo.error(1, "上传的日志文件已存在");
        }


//改造start
        InputStream inputStream = file.getInputStream();
        List<List<Object>> list = HandleFile.parseExcel(inputStream, file.getOriginalFilename());
        inputStream.close();
        for (int i = 0; i < list.size(); i++) {
            List<Object> lo = list.get(i);
            //可以将数据遍历出来后，存储到数据库中
            System.out.println(lo);
        }
//改造end


        //上传文件
        try {
            file.transferTo(fileUpload);
            return ResultVo.success();
        } catch (IOException e) {
            return ResultVo.error(1, "上传日志文件到服务器失败" + e.toString());
        }


    }

4.最后启动服务，再上传一次文件

四、生成Excel文件

1、场景描述

比如我想导入下面的list集合数据到Excel。

2、新建@Excel注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 自定义导出Excel数据注解
 * @author sugar
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
    /**
     * 导出到Excel中时，列的名字.默认值为“未命名”
     */
    public String name() default "未命名";

}

3、实体类上使用注解

package com.example.springboot4.entity;

import com.example.springboot4.annotation.Excel;

/**
 * 三年消耗量
 */
public class ConsumptionEntity {
    @Excel(name="物料")
    public String material;
    @Excel(name="组织")
    public String org;
    @Excel(name="数量")
    public String qty;
    @Excel(name="金额")
    public String amount;

    public ConsumptionEntity() {
    }
    public ConsumptionEntity(String material, String org, String qty, String amount) {
        this.material = material;
        this.org = org;
        this.qty = qty;
        this.amount = amount;
    }

    @Override
    public String toString() {
        return "ConsumptionEntity{" +
                "material='" + material + '\'' +
                ", org='" + org + '\'' +
                ", qty='" + qty + '\'' +
                ", amount='" + amount + '\'' +
                '}';
    }

    public String getMaterial() {
        return material;
    }

    public void setMaterial(String material) {
        this.material = material;
    }

    public String getOrg() {
        return org;
    }

    public void setOrg(String org) {
        this.org = org;
    }

    public String getQty() {
        return qty;
    }

    public void setQty(String qty) {
        this.qty = qty;
    }

    public String getAmount() {
        return amount;
    }

    public void setAmount(String amount) {
        this.amount = amount;
    }
}

注解上name属性是到我们创建表格的时候会用到

4、新建生成表格工具类

package com.example.springboot4.util;

import com.example.springboot4.annotation.Excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * @ClassName: ExportExcelUtil
 * @Description 构造Excel数据工具类
 * @Author sugar
 * @Date 2022/3/1021:55
 */
public class ExportExcelUtil<T> {

    /**
     * 实体对象
     */
    public Class<T> clazz;

    public void exportExcel(String title, List<T> dataset, OutputStream out, String pattern)
            throws Exception {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        if (dataset == null || dataset.size() == 0)
            return;

        T tempT = dataset.get(0);
        //得到Object对象所有声明的字段
        Field[] fields = tempT.getClass().getDeclaredFields();
        //得到所有被@Excel注解到的字段
        List<Field> tempFields = new ArrayList<>();
        for (Field field : fields) {
            if (field.isAnnotationPresent(Excel.class)) {
                tempFields.add(field);
            }
        }


        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < tempFields.size(); i++) {
            Excel attr = tempFields.get(i).getAnnotation(Excel.class);
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(attr.name());
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            for (int i = 0; i < tempFields.size(); i++) {
                //列
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                String fieldName = tempFields.get(i).getName();
                String getMethodName =
                        "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                Class tCls = t.getClass();
                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                Object value = getMethod.invoke(t, new Object[]{});
                // 判断值的类型后进行强制类型转换
                String textValue = null;
                if (value == null) {
                    cell.setCellValue("");
                }
                if (value instanceof Integer) {
                    int intValue = (Integer) value;
                    cell.setCellValue(intValue);
                } else if (value instanceof Float) {
                    float fValue = (Float) value;
                    cell.setCellValue(fValue);
                } else if (value instanceof Double) {
                    double dValue = (Double) value;
                    cell.setCellValue(dValue);
                } else if (value instanceof Long) {
                    long longValue = (Long) value;
                    cell.setCellValue(longValue);
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                    cell.setCellValue(textValue);
                } else {
                    // 其它数据类型都当作字符串简单处理
                    textValue = value == null ? "" : value.toString();
                    cell.setCellValue(textValue);
                }
            }
        }
        workbook.write(out);
    }

}

5、在LogController新增export功能

  @GetMapping(value = "/export")
    public String exportExcelByTime(HttpServletResponse response) {

        String title = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + title + ".xls");
        response.setContentType("application/x-xls");

        ConsumptionEntity consumptionEntity=new ConsumptionEntity("012002","Org-012","2012","2000.3");
        ConsumptionEntity consumptionEntity1=new ConsumptionEntity("012002","Org-012","2012","2000.3");
        ConsumptionEntity consumptionEntity2=new ConsumptionEntity("012002","Org-012","2012","2000.3");
        ConsumptionEntity consumptionEntity3=new ConsumptionEntity("012002","Org-012","2012","2000.3");
        //查询到list数据
        List<ConsumptionEntity> list = new ArrayList<>();
        list.add(consumptionEntity);
        list.add(consumptionEntity1);
        list.add(consumptionEntity2);
        list.add(consumptionEntity3);


        if (list == null || list.size() < 1) {
            return "没有查到数据";
        }
        try {
            // 输出Excel文件
            OutputStream out = response.getOutputStream();
            ExportExcelUtil util = new ExportExcelUtil();
            util.exportExcel(title, list, out, "yyyy-MM-dd");
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "导出成功";
    }