pom文件需要引入的jar包:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15-beta2</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
UserInfo.java
package com.qiaojun;
import lombok.Getter;
import lombok.Setter;
/**
*
* @author qiaojun
* @date 2019/10/11
*/
@Getter
@Setter
public class UserInfo {
private Integer id;
private String name;
private String sex;
private Integer age;
public UserInfo() {
}
public UserInfo(Integer id, String name, String sex, Integer age) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}
}
FilePortUtil.java
package com.qiaojun;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
public class FilePortUtil {
private static final Logger logger = LoggerFactory.getLogger(FilePortUtil.class);
/**
* 导出功能
* 注意:泛型T类字段名和containBean集合里字段名字的一致性
*
* @param response
* @param title 表名
* @param headers 表头
* @param list 数据集
* @param containBean 数据集类型字段
* @param <T>
* @throws Exception
*/
public static <T> void exportExcel(HttpServletResponse response, String title, String[] headers, List<T> list, List<String> containBean) throws Exception {
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(title);
HSSFRow row = sheet.createRow(0);
//创建第一行表头
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
Iterator<T> it = list.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
//反射得到字段
Field[] fields = t.getClass().getDeclaredFields();
//如果需要匹配
if (CollectionUtils.isNotEmpty(containBean)) {
for (int j = 0; j < containBean.size(); j++) {
for (Field field : fields) {
if (!field.getName().equals(containBean.get(j))) {
continue;
}
//给每一列set值
setCellValue(t, field, row, j);
}
}
} else {
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
setCellValue(t, field, row, i);
}
}
}
//application/vnd.ms-excel告诉浏览器要下载的是个excel
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//请求头设置,Content-Disposition为下载标识,attachment标识以附件方式下载
response.addHeader("Content-Disposition", "attachment;filename=" + new String((title).getBytes(), "ISO8859-1") + ".xls");
workbook.write(response.getOutputStream());
} finally {
if (workbook != null) {
workbook.close();
}
}
}
/**
* 设置每一行中的列
*
* @param t
* @param field
* @param row
* @param index
* @param <T>
*/
private static <T> void setCellValue(T t, Field field, HSSFRow row, int index) {
HSSFCell cell = row.createCell(index);
Object value = invoke(t, field);
String textValue = null;
if (value != null) {
if (value instanceof Date) {
Date date = (Date) value;
textValue = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
} else {
textValue = value.toString();
}
}
if (textValue != null) {
cell.setCellValue(textValue);
}
}
/**
* 反射映射数据集字段
*
* @param t
* @param field
* @param <T>
* @return
*/
private static <T> Object invoke(T t, Field field) {
try {
String fieldName = field.getName();
PropertyDescriptor pd = new PropertyDescriptor(fieldName, t.getClass());
Method method = pd.getReadMethod();
return method.invoke(t);
} catch (Exception e) {
return null;
}
}
}
TestController.java
package com.qiaojun;
import com.qiaojun.UserInfo;
import com.qiaojun.FilePortUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@Controller
@RequestMapping("/test")
public class TestController {
//导出
@RequestMapping("/exportExcle")
public void exportExcle(HttpServletResponse response){
//导出的表名
String title = "参数导出";
//表中第一行表头字段
String[] headers = {"编号", "姓名", "性别"};
//实际数据结果集
List<UserInfo> listObject = new ArrayList<>();
listObject.add(new UserInfo(1,"张三","男",18));
listObject.add(new UserInfo(2,"李四","女",20));
listObject.add(new UserInfo(3,"王五","男",19));
//具体需要写入excel需要哪些字段,这些字段取自UserReward类,也就是上面的实际数据结果集的泛型
List<String> listColumn = Arrays.asList("id", "name", "sex");
try {
FilePortUtil.exportExcel(response, title, headers, listObject, listColumn);
} catch (Exception e) {
e.printStackTrace();
}
}
//导入
@RequestMapping("/fileImport")
@ResponseBody
public String fileImport(HttpServletRequest request,MultipartFile fileName,MultipartFile fileName1){
// List<MultipartFile> files = ((MultipartHttpServletRequest)request).getFiles("file");
// for (MultipartFile file:files){
// if (file.getName().equals("file1")){
// System.out.println(fileImport(file));
//
// }
// }
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multiRequest.getFile("fileName");
System.out.println(fileImport(file));
System.out.println("----------------------------");
MultipartFile file1 = multiRequest.getFile("fileName1");
System.out.println(fileImport(file));
System.out.println("----------------------------");
System.out.println(fileImport(fileName));
System.out.println("----------------------------");
System.out.println(fileImport(fileName1));
return "ok";
}
/**
* 导入功能
*/
public int fileImport(MultipartFile multipartFile) {
File file = null;
Workbook workbook = null;
int totalNum = 0;
//得到的path是 /D:/springbootdemo/target/springbootdemo/WEB-INF/classes/
String path = FilePortUtil.class.getClassLoader().getResource("/").getPath();
//拼接后的path就是 /D:/springbootdemo/target/springbootdemo/WEB-INF/xxx.xlsx
path = path.substring(0, path.indexOf("WEB-INF") + "WEB-INF".length()) + "/" + multipartFile.getOriginalFilename();
file = new File(path);
try {
//把文件流copy读取到文件中
FileCopyUtils.copy(multipartFile.getBytes(), file);
workbook = WorkbookFactory.create(new FileInputStream(file));
//遍历sheet页
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
//统计导入的总条数,包含表头
if (sheet.getLastRowNum() > 0) {
totalNum += sheet.getLastRowNum();
}
//遍历行,这里j的初始值取1是因为我的表格里第一行是表头
for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
Row row = sheet.getRow(j);
//解析列,下标从0开始
Cell cell0 = row.getCell(0);
Cell cell1 = row.getCell(1);
Cell cell2 = row.getCell(2);
System.out.println(getCellValue(cell0)+","+getCellValue(cell1)+","+getCellValue(cell2));
//这里添加自己的业务处理逻辑
}
}
//解析完删除此路径下的文件
file.delete();
return totalNum;
} catch (Exception e) {
e.printStackTrace();
}
return totalNum;
}
private String getCellValue(Cell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
Double d = cell.getNumericCellValue();
return String.valueOf(d.intValue());
}
return String.valueOf(cell.getStringCellValue());
}
}
test.jsp
<%@ page contentType="text/html; charset=utf-8" %>
<%
request.setAttribute("path",request.getContextPath());
%>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<a href="${path}/test/exportExcle">导出测试</a>
<form id="fileForm" enctype="multipart/form-data">
<input type="file" name="fileName" id="file">
<input type="file" name="fileName1" id="file1">
</form>
<input type="button" id="subForm" value="上传">
<script type="text/javascript" src="${path}/js/jquery-1.8.3.min.js"></script>
<script>
$("#subForm").click(function () {
var FormDatas=new FormData($("#fileForm")[0]);
var fileName=$("#file").val();
alert(fileName);
$.ajax({
type:'post',
url:'${path}/test/fileImport',
async : false,
cache : false,
contentType : false,
processData : false,
data:FormDatas,
success: function(data){
alert("导入完成");
return false;
},
error : function(data){
}
});
});
</script>
</body>
</html>