Java POI Excel导入导出
1.maven引入依赖2.导入Excel3.导出Excel1.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;@Servicepublic 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文档对象--兼容版本和版本if (Objects.requireNonNull(file.getOriginalFilename()).endsWith(XLS)){// 版本workbook = new HSSFWorkbook(file.getInputStream());}// 版本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 .URLEncoder;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;@Servicepublic class ExportService {private HttpServletResponse response;private HSSFWorkbook workbook = null;private UserService userService;//添加自动筛选的列 如 A:Mprivate 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;}}
参考文档：/p/9fd84f1ce725
