Java POI Excel导入导出

1.maven引入依赖2.导入Excel3.导出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; @Service 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; @Service 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