EXCEL 表格

一、依赖

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

二、导入

HTML结构，用表单接收上传的文件

<form id='import' enctype='multipart/form-data' style='text-align: center'>
    <input type='file' name='file' style='margin: 30px auto'> -> 选择提交的文件
</form>

JavaScript脚本,封装上传到服务器的文件

var $file = $("input[name=file]");
var name = $file.val();
var suffix = name.substring(name.lastIndexOf('.'));
if (suffix == '.xls' || suffix == '.xlsx') {
    var data = new FormData();
    data.append("file", $file[0].files[0]);
    $.ajax({
            type: "POST",
            data: data,
            contentType: false,
            processData: false,
            mimeType: "multipart/form-data",
            url: "/import",
            success: function () {},
    });
} else {
    console.log("不是EXCEL文件");
}

服务器端接收前端传过来的文件

@PostMapping("/import")
@ResponseBody
public boolean important(MultipartFile file) throws Exception {
    String name = file.getOriginalFilename();
    String suffix = name.substring(name.lastIndexOf("."));
    if (suffix.equalsIgnoreCase(".xls")) { -> 2003版本
        POIFSFileSystem system = new POIFSFileSystem(file.getInputStream());
        HSSFWorkbook workbook = new HSSFWorkbook(system);
        HSSFSheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();
        for (int i = 1; i <= rows; i++) {
            HSSFRow row = sheet.getRow(i);
            int columns = row.getLastCellNum();
            for (int j = 0; j < columns; j++) {
                HSSFCell cell = row.getCell(j);
                if (row.getCell(j) == null) row.createCell(j);
                cell.setCellType(CellType.STRING);
                String content = cell.getStringCellValue();
                System.out.println(i + " " + j + " " + content);
            }
        }
    } else if (suffix.equalsIgnoreCase(".xlsx")) { -> 2007版本
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();
        for (int i = 1; i <= rows; i++) {
            XSSFRow row = sheet.getRow(i);
            int columns = row.getLastCellNum();
            for (int j = 0; j < columns; j++) {
                XSSFCell cell = row.getCell(j);
                if (row.getCell(j) == null) row.createCell(j);
                String content = cell.getRawValue();
                System.out.println(i + " " + j + " " + content);
            }
        }
    } else {
        return false;
    }
    return true;
}

@PostMapping("/import")
@ResponseBody
public void important(HttpServletRequest request, HttpServletResponse response) throws Exception {
    CommonsMultipartResolver resolver = new CommonsMultipartResolver(request.getServletContext());
    if (resolver.isMultipart(request)) { // 请求里包含文件流
        MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
        Iterator<String> iterator = multipartHttpServletRequest.getFileNames(); // 获取所有文件名
        while (iterator.hasNext()) {
            MultipartFile multipartFile = multipartHttpServletRequest.getFile(iterator.next()); // 读取文件流
            String fullName = multipartFile.getOriginalFilename(); // 获取文件全名
            String prefix = fullName.substring(0, fullName.lastIndexOf(".")).toLowerCase(); // 获取文件前缀
            String suffix = fullName.substring(fullName.lastIndexOf(".")).toLowerCase(); // 获取文件后缀
            String temporaryName = UUID.randomUUID().toString().replace("-", ""); // 临时文件名

            InputStream inputStream = null;
            FileOutputStream fileOutputStream = null;
            try {
                File temporaryFile = File.createTempFile(temporaryName, suffix); // 创建临时文件
                multipartFile.transferTo(temporaryFile); // 文件流转化为文件
                if (suffix.equals("zip") || suffix.equals("rar")) {
                    ZipFile zip = new ZipFile(temporaryFile, Charset.forName("GBK")); //解决中文文件夹乱码
                    for (Enumeration<? extends ZipEntry> entries = zip.entries(); entries.hasMoreElements(); ) {
                        ZipEntry entry = entries.nextElement(); // 压缩文件实体
                        String outputPath = "D:/" + entry.getName(); // 文件输出路径
                        File outputFile = new File(outputPath); // 输出文件
                        if(entry.isDirectory()){
                            outputFile.mkdirs(); // 创建任意层级的目录
                        }else{
                            outputFile.createNewFile(); // 创建文件
                            inputStream = zip.getInputStream(entry); // 获取压缩文件输入流
                            fileOutputStream = new FileOutputStream(outputFile); // 创建文件输出流
                            byte[] bytes = new byte[1024];
                            int length;
                            while ((length = inputStream.read(bytes)) > 0) { // 输入流读入数组
                                fileOutputStream.write(bytes, 0, length); // 数组写出输出流
                            }
                        }
                    }
                }
                inputStream.close(); // 释放输入流
                fileOutputStream.close(); // 释放输出流
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                if(inputStream != null){
                    inputStream.close(); // 释放输入流
                }
                if(fileOutputStream != null){
                    fileOutputStream.close(); // 释放输出流
                }
            }
        }
    }

    Map<String,Object> map = new HashMap<>();
    map.put("code",200);
    map.put("msg","操作成功");
    response.setContentType("application/json;charset=utf-8");
    response.setCharacterEncoding("utf-8");
    PrintWriter out = response.getWriter();
    out.print(JSON.toJSONString(map));
    out.flush();
}

三、导出

@RequestMapping(value = "/export", method = RequestMethod.GET)
@ResponseBody
public boolean export(HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook(); -> EXCEL的文档对象
    HSSFSheet sheet = workbook.createSheet("sheet"); -> EXCEL的工作簿
    HSSFCellStyle cellStyle = workbook.createCellStyle(); -> EXCEL的单元格样式
    cellStyle.setAlignment(HorizontalAlignment.CENTER); -> 设置水平居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); -> 设置垂直居中
    HSSFFont font = workbook.createFont(); -> 单元格字体
    font.setColor(HSSFFont.COLOR_RED);
    cellStyle.setFont(font);
    for (int i = 0; i < 10; i++) {
        HSSFRow row = sheet.createRow(i); -> EXCEL的行
        for (int j = 0; j < 10; j++) {
            HSSFCell cell = row.createCell(j); -> EXCEL的行的单元格
            cell.setCellValue(i + " " + j); -> 设置单元格里的值
            cell.setCellType(CellType.STRING); -> 设置单元格内容格式
            cell.setCellStyle(cellStyle); -> 设置单元格样式
        }
        sheet.setColumnWidth(i, 256 * 10); -> 设置表单列宽
    }
    String name = URLEncoder.encode("EXCEL表格", "UTF-8");
    response.setHeader("Content-type", "text/html;charset=UTF-8"); -> 客户端输出流
    response.setHeader("Content-disposition", "attachment;filename=" + name + ".xls"); -> 激活文件下载框
    response.setContentType("application/ms-excel"); -> 导出EXCEL类型
    OutputStream stream = response.getOutputStream();
    workbook.write(stream);
    workbook.close();
    return true;
}

window.location.href = '/export'; -> 前端通过href属性激活浏览器下载框

var $thead = $("#bootstrap-table thead div[class=th-inner]"); -> 表头
var $tbody = $("#bootstrap-table tbody tr"); -> 表列
var application = "application/vnd.ms-excel"; -> xls文件格式

var excel = '<table>' + '<tr>';
for (var i = 0; i < $thead.length; i++) {
  excel += '<td>' + $thead.eq(i).text() + '</td>';
}
excel += '</tr>';
for (var i = 0; i < $tbody.length; i++) {
  excel += '<tr>';
  var $td = $tbody.eq(i).find('td:not(:has( > div))');
  for (var j = 0; j < $td.length; j++) {
    excel += '<td>' + $td.eq(j).text() + '</td>';
    }
  excel += '</tr>';
}
excel += '</table>';

var excelFile = '<html xmlns:o=\'urn:schemas-microsoft-com:office:office\' ';
excelFile += 'xmlns:x=\'urn:schemas-microsoft-com:office:excel\' ';
excelFile += 'xmlns=\'http://www.w3.org/TR/REC-html40\'>';
excelFile += '<meta http-equiv="content-type" content="' + application + '; charset=UTF-8">';
excelFile += '<head>';
excelFile += '<!--[if gte mso 9]>';
excelFile += '<xml>';
excelFile += '<x:ExcelWorkbook>';
excelFile += '<x:ExcelWorksheets>';
excelFile += '<x:ExcelWorksheet>';
excelFile += '<x:Name>';
excelFile += '{worksheet}';
excelFile += '</x:Name>';
excelFile += '<x:WorksheetOptions>';
excelFile += '<x:DisplayGridlines/>';
excelFile += '</x:WorksheetOptions>';
excelFile += '</x:ExcelWorksheet>';
excelFile += '</x:ExcelWorksheets>';
excelFile += '</x:ExcelWorkbook>';
excelFile += '</xml>';
excelFile += '<![endif]-->';
excelFile += '</head>';
excelFile += '<body>';
excelFile += excel;
excelFile += '</body>';
excelFile += '</html>';

var uri = 'data:' + application + ';charset=utf-8,' + encodeURIComponent(excelFile);
var link = document.createElement('a');
link.href = uri;
$(link).css("visibility", "hidden");
$(link).attr("download", '模板.xls');
document.body.appendChild(link);
link.click();
document.body.removeChild(link);

OFFICE	MIME Type
.doc	application/msword
.dot	application/msword
.docx	application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotx	application/vnd.openxmlformats-officedocument.wordprocessingml.template
.docm	application/vnd.ms-word.document.macroEnabled.12
.dotm	application/vnd.ms-word.template.macroEnabled.12
.xls	application/vnd.ms-excel
.xlt	application/vnd.ms-excel
.xla	application/vnd.ms-excel
.xlsx	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltx	application/vnd.openxmlformats-officedocument.spreadsheetml.template
.xlsm	application/vnd.ms-excel.sheet.macroEnabled.12
.xltm	application/vnd.ms-excel.template.macroEnabled.12
.xlam	application/vnd.ms-excel.addin.macroEnabled.12
.xlsb	application/vnd.ms-excel.sheet.binary.macroEnabled.12
.ppt	application/vnd.ms-powerpoint
.pot	application/vnd.ms-powerpoint
.pps	application/vnd.ms-powerpoint
.ppa	application/vnd.ms-powerpoint
.pptx	application/vnd.openxmlformats-officedocument.presentationml.presentation
.potx	application/vnd.openxmlformats-officedocument.presentationml.template
.ppsx	application/vnd.openxmlformats-officedocument.presentationml.slideshow
.ppam	application/vnd.ms-powerpoint.addin.macroEnabled.12
.pptm	application/vnd.ms-powerpoint.presentation.macroEnabled.12
.potm	application/vnd.ms-powerpoint.presentation.macroEnabled.12
.ppsm	application/vnd.ms-powerpoint.slideshow.macroEnabled.12

四、下载模板

try { -> 请求后端接口
  InputStream inputStream = (InputStream) this.getClass()
  .getClassLoader().getResourceAsStream("mould.xlsx");
  response.setContentType("application/zip");
  OutputStream out = response.getOutputStream();
  response.setHeader("Content-Disposition","attachment;filename=mould.xlsx");
  int b = 0;
  byte[] buffer = new byte[1000000];
  while (b != -1) {
    b = inputStream.read(buffer);
    if(b!=-1) out.write(buffer, 0, b);
  }
  inputStream.close();
  out.close();
  out.flush();
  } catch (IOException e) {
    e.printStackTrace();
}
文件存放位置(SpringBoot):resource/mould.xlsx

<a href='/localhost:8080/mould.xlsx'>点击下载模板</a> -> 前端访问文件位置