日常开发工作中对于文件的相关操作大家多少都会涉及:上传解析、数据导出等。此篇内容主要一下工作中常用的Excel文件的解析和导出工作类实现。 首先引入POI包依赖 大家需要了解一下相关的API类,便于后面理解代码逻辑和排查问题,重写相关逻辑实现自己的业务需求。 ** 解释: ** 外部访问导出文件示例: 特殊说明:导出操作实际上利用反射机制实现,则需保证Excel标题列顺序需与导出定义的实体对象属性顺序保持一致,否则会出现错位现象。实践
1.maven依赖
<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
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"); 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); }
本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。
ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载
本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.
阅读和此文章类似的: 全球云计算