1. 记得加入依赖,用的是新版本的poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
2. 上代码(自行阅读【要有poi基础】)
import java.io.*;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
/**
* @author qf_meng
* 类说明: POI导入导出工具类
*/
public class POIUtils {
/*==================================导出==================================================*/
private final static String xls = "xls";
private final static String xlsx = "xlsx";
private final static String DATE_FORMAT = "yyyy/MM/dd";
//参数说明: fileName:文件名 projects:对象集合 columnNames: 列名 keys: map中的key
public static void start_download(HttpServletResponse response, String fileName, List<?> projects, String[] columnNames, String[] keys) throws IOException {
//将集合中对象的属性 对应到 List<Map<String,Object>>
List<Map<String, Object>> list = createExcelRecord(projects, keys);
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
//将转换成的Workbook对象通过流形式下载
createWorkBook(list, keys, columnNames).write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.ISO_8859_1));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null) bis.close();
if (bos != null) bos.close();
}
}
private static List<Map<String, Object>> createExcelRecord(List<?> projects, String[] keys) {
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet");
listmap.add(map);
Object project = null;
for (int j = 0; j < projects.size(); j++) {
project = projects.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>();
for (int i = 0; i < keys.length; i++) {
mapValue.put(keys[i], getFieldValueByName(keys[i], project));
}
listmap.add(mapValue);
}
return listmap;
}
/**
* 利用反射 根据属性名获取属性值
*/
private static Object getFieldValueByName(String fieldName, Object o) {
try {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter);
Object value = method.invoke(o);
return value;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 创建excel文档对象
*
* @param keys list中map的key数组集合
* @param columnNames excel的列名
*/
private static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String[] columnNames) {
// 创建excel工作簿
Workbook wb = new XSSFWorkbook();
// 创建第一个sheet(页),并命名
Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
for (int i = 0; i < keys.length; i++) {
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBold(true);
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
cs.setBorderLeft(BorderStyle.THIN);
cs.setBorderRight(BorderStyle.THIN);
cs.setBorderTop(BorderStyle.THIN);
cs.setBorderBottom(BorderStyle.THIN);
cs.setAlignment(HorizontalAlignment.CENTER);
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
cs2.setBorderLeft(BorderStyle.THIN);
cs2.setBorderRight(BorderStyle.THIN);
cs2.setBorderTop(BorderStyle.THIN);
cs2.setBorderBottom(BorderStyle.THIN);
cs2.setAlignment(HorizontalAlignment.CENTER);
//设置列名
for (int i = 0; i < columnNames.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
for (short i = 1; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow(i);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++) {
Cell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}
/*==================================导入==================================================*/
/**
* 读入excel文件,解析后返回
*
* @param file
* @throws IOException
*/
public static List<String[]> readExcel(MultipartFile file) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if (workbook != null) {
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行
for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
//校验文件是否合法
public static void checkFile(MultipartFile file) throws IOException {
//判断文件是否存在
if (null == file) {
throw new FileNotFoundException("文件不存在!");
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) {
throw new IOException(fileName + "不是excel文件");
}
}
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith(xls)) {
//2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith(xlsx)) {
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
//如果当前单元格内容为日期类型,需要特殊处理
String dataFormatString = cell.getCellStyle().getDataFormatString();
if (dataFormatString.equals("m/d/yy")) {
cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if (cell.getCellType() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//判断数据的类型
switch (cell.getCellType()) {
case NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: //空值
cellValue = "";
break;
case ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/*=============================调用示例=========================================*/
//导出
/*@RequestMapping("exportList")
public void exportList(HttpServletResponse response, String ids) {
String fileName = "人员档案列表";
List<User> users = sRPService.exportList(ids);
// 列名
String columnNames[] = {"ID", "姓名", "性别", "所属部门", "所属单位", "邮箱",
"电话", "手机", "学历/学位", "专业/专科方向", "直属上级", "账号锁定"};
// map中的key
String keys[] = {"id", "userName", "gender", "dept", "unit", "email",
"tel", "phone", "degree", "major", "parentName", "isLocked"};
try {
POIUtils.start_download(response, fileName, users, columnNames, keys);
} catch (IOException e) {
e.printStackTrace();
}
}*/
//导入
/*@RequestMapping("readUserExcel")
public void readUserExcel(MultipartFile multipartFile) {
UserService userService=new UserServiceImpl();
try {
List<String[]> readExcel = POIUtils.readExcel(multipartFile);
userService.readExcelUser(readExcel);
} catch (IOException e) {
e.printStackTrace();
}
}*/
}