poiexce表单上传

最新推荐文章于 2024-05-02 11:13:25 发布

go_____________ahead

最新推荐文章于 2024-05-02 11:13:25 发布

阅读量94

点赞数

分类专栏：后端文章标签：后台 excel poi

本文链接：https://blog.csdn.net/go_____________ahead/article/details/101350350

版权

后端专栏收录该内容

6 篇文章 0 订阅

订阅专栏

excel表单上传

实体类：

package com.example.demo.entity;

public class Student {

      public   String age;
      public   String name;
      public  String number;
       public Student(String age,String name,String number){
            this.age=age;
            this.name=name;
           this.number=number;
    }
       public Student(String age,String name){
            this.age=age;
            this.name=name;

    }
    public Student(){

    }

    @Override
    public String toString() {
        return "Student{" +
                "age='" + age + '\'' +
                ", name='" + name + '\'' +
                ", number='" + number + '\'' +
                '}';
    }
}

后端;

package com.example.demo.Controller;

import com.example.demo.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("/excel")
public class ExcelDemoController {

//运行跳到前端
    @RequestMapping("/pop_up_box")
public String demo(){
        return "/popupbox";
}
//
    @RequestMapping("upexcel")
    public  void upExcel(@RequestParam(value = "file") MultipartFile excelFile,HttpServletRequest req,HttpServletResponse resp){
    try {
//检查是否为excel文件
        isExcelFile(excelFile);
//获取excel文件
        Workbook workbook=getWorkbook(excelFile);
//解析excel文件
        List<Student> list=Resolve(workbook);
//输出内容
        listToString(list);
    }catch (Exception e){
    }
    }
    /**
     * 检查是不是excel文件
     * @param excelFile
     * @throws IOException
     */
    private void isExcelFile(MultipartFile excelFile) throws IOException{
    String fileName = excelFile.getOriginalFilename();
if(null==excelFile){
    throw new FileNotFoundException("文件不存在！");
}else if(!fileName.endsWith("xls")&&!fileName.endsWith("slsx")){
    throw new FileNotFoundException("这不是excel文件");
}
}
    /**
     * 获取excel文件
     * @param formFile
     * @return
     */
    private Workbook getWorkbook(MultipartFile formFile){
    String fileName = formFile.getOriginalFilename();
    Workbook workbook = null;
try {
//获得excel文件的io流
    InputStream is = formFile.getInputStream();
    //根据文件后缀名不同（xls和xlsx）获得不同的workbook实现类对象
    if(fileName.endsWith("xls")){
        //2003
        workbook = new HSSFWorkbook(is);
    }else if(fileName.endsWith("xlsx")){
        //2007
        workbook = new XSSFWorkbook(is);
    }

}catch (Exception e){

}

return workbook;
}
    /**
     * 解析excel
     * @return
     */
    private List<Student> Resolve(Workbook excelFile){
        List<Student> lists=new ArrayList<>();
        HSSFSheet sheet=(HSSFSheet) excelFile.getSheetAt(0);

        for(int i=1;i<=sheet.getLastRowNum();i++) {
           Student stu=new Student(); 
           //获取每一行数据
            HSSFRow row=sheet.getRow(i);
          //将每一行数据读出来存入list中
            HSSFCell cell=row.getCell(0);
            String age=String.valueOf((int)cell.getNumericCellValue());
            stu.age=age;

            HSSFCell cell1=row.getCell(1);
            String name=cell1.getStringCellValue();
            stu.name=name;

            HSSFCell cell2=row.getCell(2);
            String number=String.valueOf((int)cell2.getNumericCellValue());
            stu.number=number;

            lists.add(stu);
        }
        return  lists;
}
    /**
     *输出list中数据
     */
    private void  listToString(List<Student> list){

        for( int i=0;i<list.size();i++){
            System.out.println(list.get(i).toString());
        }

    }
}

前端;

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    <title>Title</title>
</head>
<body>
//这里只能选择excel表格，且选择文件后自动上传
         <input type="file" id="file" name="myfile" onchange="upexcel()" accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" enctype="multipart/form-data">

</body>
<script>


    function upexcel() {

        var formData = new FormData();
       //获取excel文件
        formData.append("file",$("#file").prop("files")[0]);

        $.ajax({
            //传至后台
            url: '/excel/upexcel',
            type: 'POST',
            data: formData,
            async: false,
            cache: false,
            contentType: false,
            processData: false,
            success: function (result) {

            },
            error: function () {

            }
        });
    }

</script>
</html>

前台页面：

选择文件：只显示excel文件

excel文件内容

后台显示：

excel模板下载

package com.example.demo.Controller;

import com.example.demo.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("/excel")
public class ExcelDemoController {
//跳到前端页面
 @RequestMapping("/pop_up_box")
public String demo(){
        return "/popupbox";
}
//excel表单下载

    @RequestMapping("/download")
public void download(HttpServletRequest request, HttpServletResponse response){

        HSSFWorkbook workbook = new HSSFWorkbook();
        //设置excel表格
        HSSFSheet sheet = workbook.createSheet("白名单");
         //excel模板第一行数字
        String str[]={"学号","姓名","电话号码"};
        //创建表头
            setExcel(str,workbook, sheet);
            //excel表单名
        String fileName="survey_whiteList.xlsx";
        try {
            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();
        }catch (Exception e){
        }
}
/**
 * 设置下载excel模板
 */
private void setExcel(String[] str, HSSFWorkbook workbook, HSSFSheet sheet){
    HSSFRow row = sheet.createRow(0);
for(int i=0;i<str.length;i++){
    sheet.setColumnWidth(i, 20*256);
    }
    //设置输出字体
    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);

//设置excel第一行表头
    HSSFCell cell;
for(int i=0;i<str.length;i++){
    cell = row.createCell(i);
    cell.setCellValue(str[i]);
    cell.setCellStyle(style);
}
}
}

前端代码：

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    <title>Title</title>
</head>
<body>
<span onclick="downloadexcel()">下载excel</span>
</body>
<script>

    function downloadexcel() {

        window.location.href='[[@{/excel/download/}]]';
    }

</script>
</html>

前端展示

点击下载：

打开下载文件

3、excel表格下载

模板下载和excel下载，只是多写了一个写数据的步骤

Controller

package com.example.demo.Controller;

import com.example.demo.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("/excel")
public class ExcelDemoController {

@RequestMapping("/pop_up_box")
public String demo(){
        return "/popupbox";
}

//excel表单下载

    @RequestMapping("/download")
public void download(HttpServletRequest request, HttpServletResponse response){

        HSSFWorkbook workbook = new HSSFWorkbook();
        //设置excel表格
        HSSFSheet sheet = workbook.createSheet("白名单");

        String str[]={"学号","年龄","电话号码"};
//下载内容
        List<Student> list=new ArrayList<>();
        list.add(new Student("21","张三","1312431325"));
        list.add(new Student("23","李四","3413423"));
        list.add(new Student("23","王五","12341432314"));
        list.add(new Student("12","赵六","23414234143"));
        //创建表头
            setExcel(str,workbook, sheet);
            setExcelData(workbook,sheet,list);
            //excel表单名
        String fileName="survey_whiteList.xlsx";
        try {
            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();
        }catch (Exception e){
        }
}
/**
 * 设置下载excel模板
 */
private void setExcel(String[] str, HSSFWorkbook workbook, HSSFSheet sheet){
    HSSFRow row = sheet.createRow(0);
for(int i=0;i<str.length;i++){
    sheet.setColumnWidth(i, 20*256);
    }
    //设置输出字体
    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);

//设置excel第一行表头
    HSSFCell cell;
for(int i=0;i<str.length;i++){
    cell = row.createCell(i);
    cell.setCellValue(str[i]);
    cell.setCellStyle(style);
}
}
//写入数据
private void setExcelData( HSSFWorkbook workbook, HSSFSheet sheet,List<Student> list){

    for(int i=0;i<list.size();i++){
        //因为第一行写了标题，所有直接从第二行开始写数据
        HSSFRow row = sheet.createRow(i+1);
        HSSFCell cell=row.createCell(0);
        cell.setCellValue(list.get(i).name);
        HSSFCell cell2=row.createCell(1);
        cell2.setCellValue(list.get(i).age);
        HSSFCell cell3=row.createCell(2);
        cell3.setCellValue(list.get(i).number);
    }
}

}

前端：

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    <title>Title</title>
</head>
<body>
<span onclick="downloadexcel()">下载excel</span>
</body>
<script>

    function downloadexcel() {

        window.location.href='[[@{/excel/download/}]]';
    }

</script>
</html>

前台显示：

点击下载

打开表格：

go_____________ahead

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
poiexce表单上传

excel表单上传实体类：package com.example.demo.entity;public class Student { public String age; public String name; public String number; public Student(String age,String ...
复制链接

扫一扫