1.导入依赖
2.application.properties只需要数据库连接信息就可以
3.目录结构 有个没用的service，请忽略
4.Controller,因为入门列子，所以简单的导出 导入读取数据都是可以的
贴上代码-》
package com.idress.action;
import com.idress.entity.User;
import com.idress.service.UserService;
import com.idress.utils.ExcelUtils;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Controller
public class ExcelController {
@Autowired
private UserService userService;
@RequestMapping("/")
public String toIndex(){
return "poi";
}
@RequestMapping("/excel/upload")
public String fileUpload(@RequestParam("file") MultipartFile file) throws IOException {
if(!ExcelUtils.validateExcel(file.getOriginalFilename())){
System.out.println("文件必须是excel!");
return null;
}
long size=file.getSize();
if(file.getOriginalFilename()==null || file.getOriginalFilename().equals("") || size==0){
System.out.println("文件不能为空");
return null;
}
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
int numberOfSheets = workbook.getNumberOfSheets();//获得有多少sheet
HSSFSheet sheet = workbook.getSheetAt(0);//默认只有一个sheet
int rows = sheet.getPhysicalNumberOfRows();//获得sheet有多少行
//遍历行
for (int j = 0; j < rows; j++) {
if (j == 0) {
continue;//标题行(省略)
}
HSSFRow row = sheet.getRow(j);
for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
HSSFCell cell = row.getCell(k);
System.out.println(cell.toString());
}
}
return "poi";
}
//生成user表excel
@GetMapping(value = "/excel/getUser")
@ResponseBody
public String getUser(HttpServletResponse response) throws Exception{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("统计表");
createTitle(workbook,sheet);
List<User> rows = new ArrayList<>();//userService.getAll();
rows.add(new User("1","小明","牛逼",new Date()));
rows.add(new User("2","中明","牛2逼",new Date()));
//设置日期格式
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
//新增数据行，并且设置单元格数据
int rowNum=1;
for(User user:rows){
HSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getName());
row.createCell(2).setCellValue(user.getUsername());
HSSFCell cell = row.createCell(3);
cell.setCellValue(user.getCreate_time());
cell.setCellStyle(style);
rowNum++;
}
String fileName = "导出excel例子.xls";
//生成excel文件
buildExcelFile(fileName, workbook);
//浏览器下载excel
buildExcelDocument(fileName,workbook,response);
return "download excel";
}
//创建表头
private void createTitle(HSSFWorkbook workbook,HSSFSheet sheet){
HSSFRow row = sheet.createRow(0);
//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(1,12*256);
sheet.setColumnWidth(3,17*256);
//设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
HSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("显示名");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("用户名");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("创建时间");
cell.setCellStyle(style);
}
//生成excel文件
protected void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{
FileOutputStream fos = new FileOutputStream(filename);
workbook.write(fos);
fos.flush();
fos.close();
}
//浏览器下载excel
protected void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}实体类：
import java.util.Date;
public class User {
private String id;
private String name;
private String username;
private Date create_time;
public User() {
}
public User(String id, String name, String username, Date create_time) {
this.id = id;
this.name = name;
this.username = username;
this.create_time = create_time;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getCreate_time() {
return create_time;
}
public void setCreate_time(Date create_time) {
this.create_time = create_time;
}
}上传文件用来读取数据的html（使用了thymeleaf）
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<base th:href="${#httpServletRequest.getContextPath()+'/'}">
<meta charset="UTF-8">
<title>导入Excel数据</title>
</head>
<body>
<h1>这是用户信息页</h1>
<form action="excel/upload" method="post" enctype="multipart/form-data">
<input name="file" type="file" />
<input type="submit"/>
</form>
</body>
</html>