easyExcel下载功能

pom文件引入

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

ExcelUtil工具类
复制代码
package com.xj.demo.common;

import com.xj.demo.model.Request.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.;
import org.apache.poi.ss.usermodel.;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.*;

/**

• Excel 工具类

• **/
  @Slf4j
  public class ExcelUtil {

  /**

  • 方法名：exportExcel
  • 功能：导出Excel
    */
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
    log.info(“导出解析开始，fileName:{}”,data.getFileName());
    try {
    //实例化HSSFWorkbook
    HSSFWorkbook workbook = new HSSFWorkbook();
    //创建一个Excel表单，参数为sheet的名字
    HSSFSheet sheet = workbook.createSheet(“sheet”);
    //设置表头
    setTitle(workbook, sheet, data.getHead());
    //设置单元格并赋值
    setData(sheet, data.getData());
    //设置浏览器下载
    setBrowser(response, workbook, data.getFileName());
    log.info(“导出解析成功!”);
    } catch (Exception e) {
    log.info(“导出解析失败!”);
    e.printStackTrace();
    }
    }

/**

• setTitle设置标题

• */
  private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
  try {
  HSSFRow row = sheet.createRow(0);
  //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
  for (int i = 0; i <= str.length; i++) {
  sheet.setColumnWidth(i, 15 * 256);
  }
  //设置为居中加粗,格式化时间格式
  HSSFCellStyle style = workbook.createCellStyle();
  HSSFFont font = workbook.createFont();
  font.setBold(true);
  style.setFont(font);
  style.setDataFormat(HSSFDataFormat.getBuiltinFormat(“m/d/yy h:mm”));
  //创建表头名称
  HSSFCell cell;
  for (int j = 0; j < str.length; j++) {
  cell = row.createCell(j);
  cell.setCellValue(str[j]);
  cell.setCellStyle(style);
  }
  } catch (Exception e) {
  log.info(“导出时设置表头失败！”);
  e.printStackTrace();
  }
  }

  /**

  • 方法名：setData
  • 功能：表格赋值
    */
    private static void setData(HSSFSheet sheet, List<String[]> data) {
    try{
    int rowNum = 1;
    for (int i = 0; i < data.size(); i++) {
    HSSFRow row = sheet.createRow(rowNum);
    for (int j = 0; j < data.get(i).length; j++) {
    row.createCell(j).setCellValue(data.get(i)[j]);
    }
    rowNum++;
    }
    log.info(“表格赋值成功！”);
    }catch (Exception e){
    log.info(“表格赋值失败！”);
    e.printStackTrace();
    }
    }

  /**

  • 方法名：setBrowser
  • 功能：使用浏览器下载
    */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
    try {
    //清空response
    response.reset();
    //设置response的Header
    response.addHeader(“Content-Disposition”, “attachment;filename=” + fileName);
    OutputStream os = new BufferedOutputStream(response.getOutputStream());
    response.setContentType(“application/vnd.ms-excel;charset=gb2312”);
    //将excel写入到输出流中
    workbook.write(os);
    os.flush();
    os.close();
    log.info(“设置浏览器下载成功！”);
    } catch (Exception e) {
    log.info(“设置浏览器下载失败！”);
    e.printStackTrace();
    }

  }

  /**

  • 方法名：importExcel
  • 功能：导入
    */
    public static List<Object[]> importExcel(String fileName) {
    log.info(“导入解析开始，fileName:{}”,fileName);
    try {
    List<Object[]> list = new ArrayList<>();
    InputStream inputStream = new FileInputStream(fileName);
    Workbook workbook = WorkbookFactory.create(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    //获取sheet的行数
    int rows = sheet.getPhysicalNumberOfRows();
    for (int i = 0; i < rows; i++) {
    //过滤表头行
    if (i == 0) {
    continue;
    }
    //获取当前行的数据
    Row row = sheet.getRow(i);
    Object[] objects = new Object[row.getPhysicalNumberOfCells()];
    int index = 0;
    for (Cell cell : row) {
    if (cell.getCellType().equals(NUMERIC)) {
    objects[index] = (int) cell.getNumericCellValue();
    }
    if (cell.getCellType().equals(STRING)) {
    objects[index] = cell.getStringCellValue();
    }
    if (cell.getCellType().equals(BOOLEAN)) {
    objects[index] = cell.getBooleanCellValue();
    }
    if (cell.getCellType().equals(ERROR)) {
    objects[index] = cell.getErrorCellValue();
    }
    index++;
    }
    list.add(objects);
    }
    log.info(“导入文件解析成功！”);
    return list;
    }catch (Exception e){
    log.info(“导入文件解析失败！”);
    e.printStackTrace();
    }
    return null;
    }

}
复制代码
导入文件上传工具类
复制代码
package com.xj.demo.common;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.util.UUID;

public class FileUtil {
private final static Logger logger = LoggerFactory.getLogger(FileUtil.class);

public static String uploadFile(MultipartFile multipartFile) throws Exception {
    String fileName = UUID.randomUUID().toString() + ".xls";
    String path = ResourceUtils.getURL("classpath:").getPath() + "static/upload/";
    File file = new File(path);
    if (!file.exists()) {
        file.mkdirs();
    }
    String fileFullPath = path + fileName;
    logger.info("fileFullPath:" + fileFullPath);
    InputStream inputStream = null;
    FileOutputStream fos = null;
    try {
        inputStream = multipartFile.getInputStream();
        BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
        fos = new FileOutputStream(fileFullPath);
        BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(fos);
        int size = multipartFile.getBytes().length;
        byte[] buffer = new byte[1024];// 一次读多个字节
        while ((size = inputStream.read(buffer, 0, buffer.length)) != -1) {
            fos.write(buffer, 0, buffer.length);
        }
    } catch (Exception ex) {
        logger.error("ex:" + ex.getMessage());
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
        if (fos != null) {
            fos.close();
        }
    }
    return fileFullPath;
}
/*
* 获取文件扩展名 小写
* */
public static String getFileExt(String fileName) {
    String ext = "";
    try {
        if (!fileName.equals("")) {
            ext = fileName.substring(fileName.lastIndexOf(".")).toLowerCase();
        }
    } catch (Exception e) {
        ext = "";
    }
    return ext;
}

}
复制代码
导出Excel文件
复制代码
@ApiOperation(“导出用户信息”)
@PostMapping("/exportuserinfo")
public Result ExportUserInfo(@RequestBody User_InfoListRequest userInfo) {
try {
logger.info(“user/exportuserinfo：”);
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = requestAttributes.getResponse();
SimpleDateFormat fmt = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
userInfo.pageSize = 10000;//导出最大1W条
List<User_info> selectlists = userService.selectList(userInfo);//返回集合和分页之前不要有其它代码，这两行上下要紧靠近
if (!CollectionUtils.isEmpty(selectlists)) {
//excel列头信息
String[] heads = {“姓名”, “年龄”, “注册时间”};
List<String[]> dataList = new ArrayList<String[]>();
String[] objs = null;
for (int i = 0; i < selectlists.size(); i++) {
User_info user = selectlists.get(i);
objs = new String[heads.length];
objs[0] = String.valueOf(user.getUname());
objs[1] = String.valueOf(user.getUage());
SimpleDateFormat df = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
objs[2] = df.format(user.getCreate_time());
dataList.add(objs);
}
ExcelData excelData = new ExcelData();
excelData.setFileName(UUID.randomUUID().toString() + “.xls”);
excelData.setHead(heads);
excelData.setData(dataList);
ExcelUtil.exportExcel(response, excelData);
return Result.SUCCESS();
}
} catch (Exception ex) {
logger.error(“user/exportuserinfo：” + ex.getMessage());
}
return Result.FAIL();
}
复制代码
导入Excel文件
mybatis配置
复制代码

<insert id="addBatchUser" useGeneratedKeys="true" keyProperty="id" parameterType="com.xj.demo.model.Request.UserBatchRequest">
    insert into   user_info
    (id,uname,uage,create_time)
    values
    <foreach collection="list" item="user" index="index" separator=",">
        (#{user.id,jdbcType=VARCHAR}, #{user.uname,jdbcType=VARCHAR}, #{user.uage,jdbcType=INTEGER},
        #{user.create_time,jdbcType=TIMESTAMP})
    </foreach>
</insert>

复制代码
Service类
@Override
public int addBatchUser(List<User_info> users) {
return uMapper.addBatchUser(users);
}
Controller类
复制代码
@ApiOperation(“导入用户信息”)
@PostMapping("/inportuserinfo")
public Result InportUserInfo(@RequestParam(“file”) MultipartFile multipartFile) throws Exception {
try {
logger.info(“user/inportuserinfo：”);
String[] extObjs={".xls",".xlsx"};
String fileName = multipartFile.getOriginalFilename();
String ext = FileUtil.getFileExt(fileName);
if (!Arrays.asList(extObjs).contains(ext))
return Result.FAIL(“文件为空或文件格式不正确!”);
String path = FileUtil.uploadFile(multipartFile);
List<Object[]> objs = ExcelUtil.importExcel(path);
List<User_info> list = new ArrayList<>();
if (CollectionUtils.isEmpty(objs))
return Result.FAIL();
for (int i = 0; i < objs.size(); i++) {
User_info user = new User_info();
Object[] obj = objs.get(i);
user.setId(UUID.randomUUID().toString());
user.setUname(String.valueOf(obj[0]));
user.setUage(Integer.parseInt(String.valueOf(obj[1])));
user.setCreate_time(new Date());
list.add(user);
}
int result = userService.addBatchUser(list);
if (result > 0)
return Result.SUCCESS();
return Result.FAIL();
} catch (Exception ex) {
logger.error(“user/inportuserinfo：” + ex.getMessage());
return Result.FAIL(ex.getMessage());
}
}