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>
前台显示:
点击下载
打开表格: