Excel文件导入导出操作

电玩女神 2022-12-13 12:51 280阅读 0赞

> 注意！注意！！注意！！！ 文末有惊喜彩蛋，请注意查收！

日常开发工作中对于文件的相关操作大家多少都会涉及：上传解析、数据导出等。此篇内容主要分享一下工作中常用的Excel文件的解析和导出工作类实现。

# 实践 #

## 1.maven依赖 ##

首先引入POI包依赖

<dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi</artifactId>
       <version>4.1.2</version>
    </dependency>
    
    <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>4.1.2</version>
    </dependency>

## 2.API ##

大家需要了解一下相关的API类，便于后面理解代码逻辑和排查问题，重写相关逻辑实现自己的业务需求。

1.import org.apache.poi.ss.usermodel.Workbook,对应Excel文档；
    　　2.import org.apache.poi.hssf.usermodel.HSSFWorkbook，对应xls格式的Excel文档；
    　　3.import org.apache.poi.xssf.usermodel.XSSFWorkbook，对应xlsx格式的Excel文档；
    　　4.import org.apache.poi.ss.usermodel.Sheet，对应Excel文档中的一个sheet；
    　　5.import org.apache.poi.ss.usermodel.Row，对应一个sheet中的一行；
    　　6.import org.apache.poi.ss.usermodel.Cell，对应一个单元格。

## 3.导入 ##

public class ImportExcelUtil { 
    
        private static final String EXCEL_XLS_SUFFIX = ".xls";
    
        private static final String EXCEL_XLSX_SUFFIX = ".xlsx";
     /** * 读取Excel数据内容 * * @param rowIndex 指定行号 * @param columnIndex 指定列号 * @return Map 包含单元格数据内容的Map对象 */
        public static List<Map<Integer, Object>> readExcelContent(String filepath, Integer rowIndex, Integer columnIndex) throws Exception { 
            List<Map<Integer, Object>> returnList = new LinkedList<>();
            Workbook wb = null;
            Sheet sheet;
            Row row;
            try { 
                InputStream is = new FileInputStream(filepath);
                if (filepath.endsWith(EXCEL_XLS_SUFFIX)) { 
                    wb = new HSSFWorkbook(is);
                } else if (filepath.endsWith(EXCEL_XLSX_SUFFIX)) { 
                    wb = new XSSFWorkbook(is);
                }
    
                if (wb == null) { 
                    throw new Exception("Workbook对象为空！");
                }
    
                sheet = wb.getSheetAt(0);
                //解析文件总行数、总列数
                int rowNum = rowIndex != null ? rowIndex : sheet.getLastRowNum();
                row = sheet.getRow(0);
                int colNum = columnIndex != null ? columnIndex : row.getLastCellNum();
    
                //循环列
                for (int colIndex = colNum; colIndex > 0; colIndex--) { 
                    Cell cell = row.getCell(colIndex);
                    if (cell != null && !"".equals(cell.toString())) { 
                        colNum = colIndex;
                        break;
                    }
                }
                logger.info("have data col:{}", colNum);
    
                // 正文内容应该从第二行开始,第一行为表头的标题
                for (int i = 0; i <= rowNum; i++) { 
                    row = sheet.getRow(i);
                    int j = 0;
    
                    int size = (int) (colNum / .75f) + 1;
                    //存储单元格数据
                    Map<Integer, Object> cellValue = new LinkedHashMap<>(size);
    
                    if (row == null) { 
                        continue;
                    }
    
                    while (j <= colNum) { 
                        Cell cell = row.getCell(j);
                        String value = "";
                        //日期单元格需格式化日期
                        if (cell != null) { 
                            if (cell.getCellType() == CellType.NUMERIC) { 
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { 
                                    Date d = cell.getDateCellValue();
                                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                    value = formatter.format(d);
                                } else if (cell.toString().contains("E")) { 
                                    DecimalFormat nf = new DecimalFormat("0");
                                    value = nf.format(cell.getNumericCellValue());
                                } else { 
                                    value = cell.toString().endsWith(".0") ? cell.toString().replace(".0", "") : cell.toString().trim();
                                }
                            } else if (cell.getCellType() == CellType.FORMULA) { 
                                value = String.valueOf(cell.getNumericCellValue());
                            } else { 
                                value = cell.toString().trim();
                            }
                        }
                        cellValue.put(j, value);
                        j++;
                    }
                  returnList.add(cellValueMap);
                }
                wb.close();
            } catch (FileNotFoundException e) { 
                logger.error("FileNotFoundException", e);
            } catch (IOException e) { 
                logger.error("IOException", e);
            } finally { 
                if (wb != null) { 
                    wb.close();
                }
            }
            return returnList;
        }
    
    }

\*\* 解释： \*\*  
此处方法只是提供一个思路，后期大家可以根据自己的业务需求改写，比如指定sheet，行号，列号等等。着重说明一下返回值数据结构List，主要存储结构为Map，key=列号，value=单元格内容；这种操作便于后期验证必须列是否缺失、以及可以动态设计上传文件的列结构不必固定列位置等等。

## 4.导出 ##

public class ExportExcelUtil { 
    	/** * 导出excel文件，表头为一维数组表示不用合并单元格 * @param sheetName * @param excelTitle * @param dataCollection * @param <T> * @return */
    	public static<T> HSSFWorkbook exportExcel(String sheetName, String[] excelTitle, Collection<T> dataCollection) { 
    		//创建一个Excel文件
    		HSSFWorkbook workbook = new HSSFWorkbook();
    		//创建一个Sheet表格工作空间
    		HSSFSheet sheet = workbook.createSheet(sheetName);
    		HSSFCellStyle style = workbook.createCellStyle();
    		//设置表格默认宽度
    		sheet.setDefaultColumnWidth(20);
    		//设置表格的表头
    		HSSFCell cellHeader;
    		HSSFRow row = sheet.createRow(0);
    		for (int i = 0; i < excelTitle.length; i++) { 
    			//创建单元格表头
    			cellHeader = row.createCell(i);
    			cellHeader.setCellValue(new HSSFRichTextString(excelTitle[i]));
    		}
    		//匹配表头设置单元格的值
    		setWorkBookValue(sheet, dataCollection,0, style);
    
    		return workbook;
    	}
    
    /** * （根据自定义）把具体数据写入到excel中 * @param sheet * @param dataCollection * @param index * @param style * @param <T> */
        @SuppressWarnings("unchecked")
    	private static<T> void setWorkBookValue(HSSFSheet sheet,Collection<T> dataCollection, int index,HSSFCellStyle style){ 
    		T t;
    		Object[] fields;
    		String fieldName;
    		String getMethodName;
    		HSSFCell cell;
    		HSSFRow row;
    		Class tClass;
    		Method getMethod;
    		Object value;
    		//遍历集合设置单元格值
    		Iterator<T> it = dataCollection.iterator();
    		while(it.hasNext()){ 
    			//创建一行单元格
    			index ++;
    			row = sheet.createRow(index);
    			//获取数据
    			t = it.next();
    			//利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
    			fields = t.getClass().getDeclaredFields();
    			for(int i = 0; i < fields.length; i++){ 
    				cell = row.createCell(i);
    				style.setAlignment(HorizontalAlignment.LEFT);
    				cell.setCellStyle(style);
    				//利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
    				Field[] newFields = t.getClass().getDeclaredFields();
    				fieldName = newFields[i].getName();
    				getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    				try { 
    					tClass = t.getClass();
    					getMethod = tClass.getMethod(getMethodName, new Class[]{ });
    					value = getMethod.invoke(t, new Object[]{ });
    					setCellValue(value,cell);
    				} catch (Exception e) { 
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    /** * value格式校验 */
    	private static void setCellValue(Object value,HSSFCell cell){ 
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    		String textValue = null;
    		Pattern pattern = Pattern.compile(RULE);
    		Matcher matcher;
    		HSSFRichTextString richTextString;
    		if (!StringUtils.isEmpty(value)){ 
    			//value进行类型转换
    			if (value instanceof Integer) { 
    				cell.setCellValue((Integer) value);
    			} else if (value instanceof Float) { 
    				textValue = String.valueOf(value);
    				cell.setCellValue(textValue);
    			} else if (value instanceof Double) { 
    				textValue = String.valueOf(value);
    				cell.setCellValue(textValue);
    			} else if (value instanceof Long) { 
    				cell.setCellValue((Long) value);
    			} else if (value instanceof Boolean) { 
    				textValue = "是";
    				if (!(Boolean) value) { 
    					textValue = "否";
    				}
    			} else if (value instanceof Date) { 
    				textValue = sdf.format((Date) value);
    			} else { 
    				// 其它数据类型都当作字符串简单处理
    				textValue = value.toString();
    			}
    			if (textValue != null) { 
    				matcher = pattern.matcher(textValue);
    				if (matcher.matches()) { 
    					// 是数字当作double处理
    					cell.setCellValue(Double.parseDouble(textValue));
    				} else { 
    					richTextString = new HSSFRichTextString(textValue);
    					cell.setCellValue(richTextString);
    				}
    			}
    		}
    	}
    
    /** * excel 导出文件 * @param response * @param workbook * @param fileName * @throws IOException */
    	public static void exportExcelFile(HttpServletResponse response, HSSFWorkbook workbook, String fileName) throws IOException { 
    		if (workbook != null) { 
    			response.reset();
    			//指定下载的文件名
    			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    			String filePrefix = sdf.format(new Date());
    			response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
    			fileName = URLEncoder.encode(filePrefix + "_" + fileName, "UTF-8").replaceAll("\\+", "%20");
                response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    			response.addHeader("Access-Control-Allow-Origin", "*");
                response.setHeader("Access-Control-Expose-Headers", "*");
    			BufferedOutputStream bufferedOutput = null;
    			try { 
    				bufferedOutput = new BufferedOutputStream(response.getOutputStream());
    				workbook.write(bufferedOutput);
    				bufferedOutput.flush();
    			} catch (IOException e) { 
    			    e.printStackTrace();
    				throw e;
    			} finally { 
    				if (bufferedOutput != null) { 
    					try { 
    						bufferedOutput.close();
    					} catch (IOException e) { 
    						e.printStackTrace();
    					}
    				}
    			}
    		}
    	}
    }

外部访问导出文件示例：

public void exportFile(String objectJson, HttpServletResponse response) throws Exception { 
    		....
    		//省略业务代码
            HSSFWorkbook workbook = ExportExcelUtil.exportExcel(SHEET_NAME, TITLE_LINE, xxxList);
            ExportExcelUtil.exportExcelFile(response, workbook, FILE_NAME);
        }

**特殊说明**：导出操作实际上利用反射机制实现，则需保证**Excel标题列顺序需与导出定义的实体对象属性**顺序保持一致，否则会出现错位现象。

**整理的这份超全Java面试题包含：** Java基础、Java异常、Spring、SpirngMVC、MyBatis、SpringBoot、SpringCloud、BIO、NIO、AIO、Netty、Tomcat、Redis、MySQL、Memcache、ElasticSearch、zookeeper、RabbitMQ、Vue、Dubbo、并发编程、Java虚拟机、TCP、Http、大数据、设计模式、Linux等

**公众号Java专栏，后台回复【面试题66】。即可获取！**  
或者点击下方超链接：  
[史上最全Java面试题，41个方向！一网打尽！ ][Java_41_]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5OTYyNg_size_16_color_FFFFFF_t_70_pic_center]

[Java_41_]: https://shimo.im/docs/YRwpX3hKQVxv9YGP
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5OTYyNg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/b75906f507644f6a8f2a20132b1f972e.png