Java POI Excel导入导出
1.maven引入依赖
<!-- POI Excel 操作 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.1</version>
</dependency>
2.导入Excel
package com.whiteink.pms.service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.whiteink.pms.entity.Department;
import com.whiteink.pms.entity.DictItem;
import com.whiteink.pms.entity.ParkingSpace;
import com.whiteink.pms.entity.sys.User;
import com.whiteink.pms.exception.BusinessException;
import com.whiteink.pms.global.Constant;
import com.whiteink.pms.service.sys.UserService;
import com.whiteink.pms.util.MyUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
public class ImportService {
private static String XLS = ".xls";
private static String XLSX = ".xlsx";
private static Long MAX_SIZE = 1024*1024L;
private DepartmentService departmentService;
private UserService userService;
private DictItemService dictItemService;
private ParkingSpaceService parkingSpaceService;
public ImportService(DepartmentService departmentService, UserService userService,
DictItemService dictItemService, ParkingSpaceService parkingSpaceService) {
this.departmentService = departmentService;
this.userService = userService;
this.dictItemService = dictItemService;
this.parkingSpaceService = parkingSpaceService;
}
/**
*验证文件
*/
private void verifyFile(MultipartFile file) throws BusinessException{
if (null == file){
throw new BusinessException("请上传文件！");
}
String fileSuffix = file.getOriginalFilename();
Long size = file.getSize();
if (fileSuffix.endsWith(XLS) || fileSuffix.endsWith(XLSX)){
if (size < 0){
throw new BusinessException("文件为空！");
}else if (size > MAX_SIZE){
throw new BusinessException("文件太大，无法导入！");
}
}else {
throw new BusinessException("文件格式不正确！");
}
}
/**
* 导入停车位
* 返回读取行数，成功插入行数
*/
public int[] importParking(MultipartFile file) throws Exception{
int[] result = {0,0};
verifyFile(file);
Workbook workbook = null;
//1.读取Excel文档对象--兼容2003版本和2007版本
if (Objects.requireNonNull(file.getOriginalFilename()).endsWith(XLS)){
// 2003版本
workbook = new HSSFWorkbook(file.getInputStream());
}
// 2007版本
workbook = new XSSFWorkbook(file.getInputStream());
//2.获取要解析的表格（第一个表格）
Sheet sheet = workbook.getSheetAt(0);
//获得最后一行的行号
int lastRowNum = sheet.getLastRowNum();
List<ParkingSpace> parkingSpaces = new ArrayList<>();
//遍历每一行
for (int i = 1; i <= lastRowNum; i++) {
int k = 1;
//3.获得要解析的行
Row row = sheet.getRow(i);
try {
if (StringUtils.isEmpty(row.getCell(0).getStringCellValue())
&& StringUtils.isEmpty(row.getCell(1).getStringCellValue())
&& StringUtils.isEmpty(row.getCell(2).getStringCellValue())){
result[0] = i-1;
break;
}
}catch (Exception e){
throw new BusinessException("请将表格里面所有列设置为【文本】格式");
}
//4.获得每个单元格中的内容（String）
ParkingSpace parkingSpace = new ParkingSpace();
if (StringUtils.isEmpty(row.getCell(k).getStringCellValue())){
throw new BusinessException("第"+i+"行，车位编号不可为空！");
}
parkingSpace.setNumber(row.getCell(k).getStringCellValue());k++;
if (StringUtils.isEmpty(row.getCell(k).getStringCellValue())){
throw new BusinessException("第"+i+"行，所属停车场不可为空！");
}
parkingSpace.setParkingLot(row.getCell(k).getStringCellValue());
k++;
if (StringUtils.isEmpty(row.getCell(k).getStringCellValue())){
throw new BusinessException("第"+i+"行，楼层不可为空!");
}
parkingSpace.setFloor(row.getCell(k).getStringCellValue());
k++;
if (StringUtils.isEmpty(row.getCell(k).getStringCellValue())){
throw new BusinessException("第"+i+"行，区域不可为空!");
}
parkingSpace.setArea(row.getCell(k).getStringCellValue());
parkingSpace.setUsageCount(0);
parkingSpace.setStatus(0);
parkingSpace.setGmtCreate(LocalDateTime.now());
int n = parkingSpaceService.count(new QueryWrapper<ParkingSpace>()
.eq("number", parkingSpace.getNumber())
.eq("floor", parkingSpace.getFloor())
.eq("area", parkingSpace.getArea())
.eq("parking_lot", parkingSpace.getParkingLot()));
if (n <= 0){
result[1]++;
parkingSpaces.add(parkingSpace);
}
}
parkingSpaceService.saveBatch(parkingSpaces);
return result;
}
}
3.导出Excel
package com.whiteink.pms.service;
import com.whiteink.pms.entity.sys.User;
import com.whiteink.pms.service.sys.UserService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
public class ExportService {
private HttpServletResponse response;
private HSSFWorkbook workbook = null;
private UserService userService;
//添加自动筛选的列 如 A:M
private String address = "";
//Float类型数据小数位
private String floatDecimal = "0.00";
//Double类型数据小数位
private String doubleDecimal = "0.00";
//设置列的公式
private String colFormula[] = null;
DecimalFormat floatDecimalFormat = new DecimalFormat(floatDecimal);
DecimalFormat doubleDecimalFormat = new DecimalFormat(doubleDecimal);
public ExportService(UserService userService) {
this.userService = userService;
}
public void exportUsers(HttpServletResponse response) {
List<User> users = userService.listStaff();
this.response = response;
this.workbook = new HSSFWorkbook();
String[] titleColumn = {"name", "jobNumber", "companyName", "departmentName", "idCard", "phone", "carNumber", "carType"};
String[] titleName = {"员工名", "工号", "单位", "部门", "身份证", "手机号", "车牌号", "车型"};
int[] titleSize = {15,15,30,20,30,15,15,15};
writeExcel(titleColumn, titleName, titleSize, users);
}
/**
* 写excel.
* xls方式
* @param titleColumn 对应bean的属性名
* @param titleName excel要导出的列名
* @param titleSize 列宽
* @param dataList 数据
*/
public void writeExcel(String[] titleColumn, String[] titleName, int[] titleSize, List<?> dataList) {
String sheetName = "基本信息";
String fileName = "员工信息";
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
Sheet sheet = workbook.createSheet(sheetName);
//新建文件
OutputStream out = null;
try {
//直接写到输出流中
out = response.getOutputStream();
fileName += ".xls";
response.setContentType("application/force-download");
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(fileName,"utf-8"));
//写入excel的表头
Row titleNameRow = workbook.getSheet(sheetName).createRow(0);
//设置标题样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, "Arial Unicode MS", (short) 12, true);
titleStyle = (HSSFCellStyle) setColor(titleStyle, "C1FBEE", (short) 10);
for (int i = 0; i < titleName.length; i++) {
//设置宽度
sheet.setColumnWidth(i, titleSize[i] * 256);
Cell cell = titleNameRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(titleName[i].toString());
}
//为表头添加自动筛选
if (!"".equals(address)) {
CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
sheet.setAutoFilter(c);
}
//通过反射获取数据并写入到excel中
if (dataList != null && dataList.size() > 0) {
//设置内容样式
HSSFCellStyle dataStyle = workbook.createCellStyle();
CellStyle bodyStyle = workbook.createCellStyle();
bodyStyle = (HSSFCellStyle) setFontAndBorder(bodyStyle, "宋体", (short) 10, false);
if (titleColumn.length > 0) {
for (int rowIndex = 1; rowIndex <= dataList.size(); rowIndex++) {
//获得该对象
Object obj = dataList.get(rowIndex - 1);
//获得该对对象的class实例
Class clsss = obj.getClass();
Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex);
for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
String title = titleColumn[columnIndex].toString().trim();
//字段不为空
if (!"".equals(title)) {
//使首字母大写
String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length());
String methodName = "get" + UTitle;
// 设置要执行的方法
Method method = clsss.getDeclaredMethod(methodName);
//获取返回类型
String returnType = method.getReturnType().getName();
Object object = method.invoke(obj);
String data = method.invoke(obj) == null ? "" : object.toString();
Cell cell = dataRow.createCell(columnIndex);
if (!StringUtils.isEmpty(data)) {
if ("int".equals(returnType)) {
cell.setCellValue(Integer.parseInt(data));
} else if ("long".equals(returnType)) {
cell.setCellValue(Long.parseLong(data));
} else if ("float".equals(returnType)) {
cell.setCellValue(floatDecimalFormat.format(Float.parseFloat(data)));
} else if ("double".equals(returnType)) {
cell.setCellValue(doubleDecimalFormat.format(Double.parseDouble(data)));
} else if (Date.class.getName().equals(returnType)) {
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
} else {
cell.setCellValue(data);
}
cell.setCellStyle(bodyStyle);
}else {
cell.setCellStyle(bodyStyle);
}
} else { //字段为空 检查该列是否是公式
if (colFormula != null) {
String sixBuf = colFormula[columnIndex].replace("@", (rowIndex + 1) + "");
Cell cell = dataRow.createCell(columnIndex);
cell.setCellFormula(sixBuf);
}
}
}
}
}
}
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 设置字体并加外边框
*
* @param style 样式
* @param style 字体名
* @param style 大小
* @return
*/
private CellStyle setFontAndBorder(CellStyle style, String fontName, short size, boolean bold) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(size);
font.setFontName(fontName);
font.setBold(bold);
style.setFont(font);
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
return style;
}
/**
* 将16进制的颜色代码写入样式中来设置颜色
*
* @param style 保证style统一
* @param color 颜色：66FFDD
* @param index 索引 8-64 使用时不可重复
* @return
*/
private CellStyle setColor(CellStyle style, String color, short index) {
if ("".equals(color)) {
//转为RGB码
int r = Integer.parseInt((color.substring(0, 2)), 16);
int g = Integer.parseInt((color.substring(2, 4)), 16);
int b = Integer.parseInt((color.substring(4, 6)), 16);
//自定义cell颜色
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex((short) index, (byte) r, (byte) g, (byte) b);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(index);
}
return style;
}
}
参考文档：https://www.jianshu.com/p/9fd84f1ce725
