Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客

沙海
沙海
沙海
3186
文章
2
评论
2021年9月3日05:49:46Java评论23字数 9659阅读32分11秒阅读模式
摘要

Java Poi Api 操作复杂excel原创2019-11-13 14:59:04

Java Poi Api 操作复杂excel原创

2019-11-13 14:59:04文章源自JAVA秀-https://www.javaxiu.com/43870.html

Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客文章源自JAVA秀-https://www.javaxiu.com/43870.html

呆毛不会动Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客文章源自JAVA秀-https://www.javaxiu.com/43870.html

码龄3年 文章源自JAVA秀-https://www.javaxiu.com/43870.html

关注文章源自JAVA秀-https://www.javaxiu.com/43870.html

应项目需求，初次学习使用。。。 没有文案，具体见示例文章源自JAVA秀-https://www.javaxiu.com/43870.html

package com.sinoyd.util;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.io.*;import java.util.*;/** * 该类实现了基于模板的导出,很多规则仅做定制化用 * 遍历数据使用_开头表示来代替 * 如果要替换信息，需要传入一个Map，这个map中存储着要替换信息的值，在excel中通过#来开头 */publicclassExcelTemplate{privatestatic final String[] Vars ={"_data1","_data2","_data3","_data4"};//遍历字符串private Map<String, List<Integer>> Location =newHashMap<>();//遍历字符串行每个元素绝对位置起始row(行),起始col(列)private Map<String, List<Integer>> Location_Full =newHashMap<>();//遍历字符串行每个元素扩展后绝对位置起始row(行),,起始col(列)private Map<String, CellStyle> Styles =newHashMap<>();//遍历字符串行每个元素样式privatestatic final String Var_1 ="_data1";//上左privatestatic final String Var_2 ="_data2";//上右privatestatic final String Var_3 ="_data3";//下左privatestatic final String Var_4 ="_data4";//下右privatestatic final int First_Row =0;privatestatic final int Last_Row =1;privatestatic final int First_Column =2;privatestatic final int Last_Column =3;privatestatic ExcelTemplate et =newExcelTemplate();private Workbook wb;private Sheet sheet;privateExcelTemplate(){}publicstatic ExcelTemplate getInstance(){return et;}public ExcelTemplate readTemplateByPath(String path){try{// InputStream resourceAsStream = ExcelTemplate.class.getResourceAsStream(path);//类地址 File file =newFile(path); wb = WorkbookFactory.create(file);initTemplate();}catch(InvalidFormatException e){ e.printStackTrace();thrownewRuntimeException("InvalidFormatException, please check.");}catch(IOException e){ e.printStackTrace();thrownewRuntimeException("The template is not exist, please check.");}returnthis;}/** * 初始化模板信息 */privatevoidinitTemplate(){ sheet = wb.getSheetAt(0); int sheetMergeCount = sheet.getNumMergedRegions(); int firstRow, lastRow, firstCol, lastCol; Row row; Cell cell;for(String var: Vars){for(int i =0; i < sheetMergeCount; i++){ CellRangeAddress range = sheet.getMergedRegion(i); firstRow = range.getFirstRow(); lastRow = range.getLastRow(); firstCol = range.getFirstColumn(); lastCol = range.getLastColumn(); row = sheet.getRow(firstRow); cell = row.getCell(firstCol);if(Cell.CELL_TYPE_STRING== cell.getCellType()&&var.equals(cell.getStringCellValue().trim())){ Location.put(var, Arrays.asList(firstRow, lastRow, firstCol, lastCol)); Styles.put(var, cell.getCellStyle());break;}}}}/** * 添加行 * * @param dataSize 数据大小 * @param firstRow 开始行 * @param lastRow 结束行 * @param basics 需要copy行的字段->改变Location_Full * @param extras 跟随copy变化的字段->改变Location */privatevoidaddRow(int dataSize, int firstRow, int lastRow, List<String> basics, List<String> extras){ int difRow = lastRow - firstRow +1; int addRow = dataSize >0?(difRow * dataSize - difRow):0; int beginRow = lastRow +1; int endRow = sheet.getLastRowNum();shiftRows(beginRow, endRow, addRow);//位置改变 List<Integer> loc; int first_row, last_row, first_col, last_col;for(String basic : basics){ loc = Location.get(basic); first_row = firstRow; last_row = lastRow + addRow; first_col = loc.get(First_Column); last_col = loc.get(Last_Column); Location_Full.put(basic, Arrays.asList(first_row, last_row, first_col, last_col));}for(String extra : extras){ loc = Location.get(extra); first_row = loc.get(First_Row)+ addRow; last_row = loc.get(Last_Row)+ addRow; first_col = loc.get(First_Column); last_col = loc.get(Last_Column); Location.put(extra, Arrays.asList(first_row, last_row, first_col, last_col));}}/** * 插入行 * * @param startRow 起始行 * @param endRow 结束行 * @param rows 插入的行数 */privatevoidshiftRows(int startRow, int endRow, int rows){ sheet.shiftRows(startRow, endRow, rows,true,false);for(int i =0; i < rows; i++){ Row sourceRow = sheet.getRow(startRow -1); Row targetRow = sheet.createRow(startRow++); targetRow.setHeight(sourceRow.getHeight()); Cell sourceCell; Cell targetCell;for(int m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++){ sourceCell = sourceRow.getCell(m); targetCell = targetRow.createCell(m); targetCell.setCellStyle(sourceCell.getCellStyle()); targetCell.setCellType(sourceCell.getCellType());}}}/** * 设置单元格数据 * * @param map 原始数据 */privatevoidsetCell(Map<String, List<Object>> map){for(String var: Vars){ List<Object> list = map.get(var); List<Integer> loc = Location.get(var); CellStyle cellStyle = Styles.get(var); int firstRow = loc.get(First_Row); int lastRow = loc.get(Last_Row); int firstCol = loc.get(First_Column); int lastCol = loc.get(Last_Column); int difRow = lastRow - firstRow; int endRow = difRow ==0? Location_Full.get(var).get(Last_Row)+1: Location_Full.get(var).get(Last_Row); int index =0;setCellValue(list, index++, firstRow, firstCol, cellStyle);for(int i = firstRow + difRow +1; i < endRow; i += difRow +1){setCellValue(list, index++, i, firstCol, cellStyle); sheet.addMergedRegion(newCellRangeAddress(i, i + difRow, firstCol, lastCol));}}}/** * 设定单元格值 * * @param list 原始数据 * @param index 下标 * @param rowIndex 当前行 * @param colIndex 当前列 * @param cellStyle 样式 */privatevoidsetCellValue(List<Object> list, int index, int rowIndex, int colIndex, CellStyle cellStyle){ Row row = sheet.getRow(rowIndex); Cell cell = row.getCell(colIndex); cell.setCellStyle(cellStyle); cell.setCellValue("");//初始化,只考虑数字和字符串类型if(list !=null&& list.size()> index){ Object object = list.get(index);if(object !=null){if(object instanceofString) cell.setCellValue((String) object);if(object instanceofNumber) cell.setCellValue(((Number) object).doubleValue());}}}/** * 合并随动单元格 * * @param leftExtra 左侧标志 * @param rightExtra 右侧标志 */privatevoidmergeRow(String leftExtra, String rightExtra){ List<Integer> left = Location.get(leftExtra); List<Integer> right = Location.get(rightExtra); int rowEnd = Location_Full.get(leftExtra).get(Last_Row); int leftFirstRow = left.get(First_Row), leftFirstCol =0, leftLastCol = left.get(First_Column)-1; int rightFirstRow = left.get(First_Row), rightFirstCol = left.get(Last_Column)+1, rightLastCol = right.get(First_Column)-1;mergedRegion(leftFirstRow, rowEnd, leftFirstCol, leftLastCol);mergedRegion(rightFirstRow, rowEnd, rightFirstCol, rightLastCol);}/** * 操作合并单元格 * * @param firstRow 希望合并的开始行 * @param lastRow 希望合并的结束行 * @param firstCol 希望合并的开始列 * @param lastCol 希望合并的结束列 */privatevoidmergedRegion(int firstRow, int lastRow, int firstCol, int lastCol){ int sheetMergeCount = sheet.getNumMergedRegions();for(int i =0; i < sheetMergeCount; i++){ CellRangeAddress mergedRegion = sheet.getMergedRegion(i);if(mergedRegion.getFirstRow()== firstRow && mergedRegion.getFirstColumn()== firstCol){ sheet.removeMergedRegion(i);break;}} sheet.addMergedRegion(newCellRangeAddress(firstRow, lastRow, firstCol, lastCol));}/** * 添加遍历数据 */publicvoidreplaceCellData(Map<String, List<Object>> map){ List<String> top = Arrays.asList(Var_1, Var_2); List<String> down = Arrays.asList(Var_3, Var_4); int topSize = map.get(Var_1)==null?0: map.get(Var_1).size(); int topFirstRow = Location.get(Var_1).get(First_Row); int topLastRow = Location.get(Var_1).get(Last_Row);addRow(topSize, topFirstRow, topLastRow, top, down); int downSize = map.get(Var_3)==null?0: map.get(Var_3).size(); int downFirstRow = Location.get(Var_3).get(First_Row); int downLastRow = Location.get(Var_3).get(Last_Row);addRow(downSize, downFirstRow, downLastRow, down,newArrayList<>());setCell(map);//添加cell数据合并循环变量单元格mergeRow(Var_1, Var_2);//同一行合并随动单元格mergeRow(Var_3, Var_4);//同一行合并随动单元格}/** * 根据map替换相应的常量，通过Map中的值来替换#开头的值 */publicvoidreplaceFinalData(Map<String, String> datas){if(datas ==null)return;for(Row row : sheet){for(Cell c : row){if(c.getCellType()!= Cell.CELL_TYPE_STRING)continue; String str = c.getStringCellValue().trim();if(str.startsWith("#")){if(datas.containsKey(str.substring(1))){ c.setCellValue(datas.get(str.substring(1)));}}}}}/** * 生成文件 */publicvoidwriteToFile(String filepath){ FileOutputStream fos =null;try{ fos =newFileOutputStream(filepath); wb.write(fos);}catch(FileNotFoundException e){ e.printStackTrace();thrownewRuntimeException("写入的文件不存在"+ e.getMessage());}catch(IOException e){ e.printStackTrace();thrownewRuntimeException("写入数据失败"+ e.getMessage());}finally{if(fos !=null)try{ fos.close();}catch(IOException e){ e.printStackTrace();}}}} 测试用例 @Test publicvoidcontextLoads(){ ExcelTemplate et = ExcelTemplate.getInstance().readTemplateByPath("/excel/in.xlsx"); Map<String, List<Object>> map =newHashMap<>();//日期需要excel单元格是日期格式,或转成字符串 map.put("_data1", Arrays.asList("a1-1","a1-2","a1-3","a1-4","a1-5")); map.put("_data2", Arrays.asList("a2-1","a2-2","a2-3","a2-4","a2-5")); map.put("_data3", Arrays.asList("a3-1","a3-2","a3-3","a3-4","a3-5")); map.put("_data4", Arrays.asList("a4-1","a4-2","a4-3","a4-4","a4-5"));// map.put("_data4", Arrays.asList(1, 4, -1, 4, 2));// map.put("_data4", Arrays.asList(new Date(1573606975), new Date(1573610575), new Date(1573614175), new Date(1573617775), new Date(1573621375))); Map<String, String> datas =newHashMap<>(); datas.put("name","暗帝天下第一"); datas.put("time", LocalDateTime.now().toString()); et.replaceCellData(map); et.replaceFinalData(datas); et.writeToFile("/excel/out.xlsx");}

模板及输出路径在项目运行的相对盘；Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客文章源自JAVA秀-https://www.javaxiu.com/43870.html

打开CSDN，阅读体验更佳文章源自JAVA秀-https://www.javaxiu.com/43870.html

java解析excel表格完整demo

java解析excel表格demo，内涵完整包 文章源自JAVA秀-https://www.javaxiu.com/43870.html

Java 操纵 Excel 文件数据实现复杂的项目需求

在日常工作生活中，经常会有对 Excel 数据进行复杂处理及分析需求，这些需求使用自带的函数无法满足。根据目前完成的实例项目，本 Chat 将讨论使用 Java 处理复杂的 Excel 文件进行统计分析其中的数据，实现具体的项目需求。 主要内容将涉及到 Java 对 Excel 数据读取、写入、操作、存储以及遍历等功能，使用到 Java 中的向量、列表、排序等知识，编制算法，从而实现项目对数据的... 浏览器打开文章源自JAVA秀-https://www.javaxiu.com/43870.html

相关推荐 Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客更多相似内容文章源自JAVA秀-https://www.javaxiu.com/43870.html

【JAVA】POI 中单元格背景色设置

颜色盘 /** * */ package com.totest.poiDemo; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FillPatternType; impo. 浏览器打开文章源自JAVA秀-https://www.javaxiu.com/43870.html

java读取复杂Excel_读取Excel复杂的数据

packagecom.util;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.ss.util.CellRangeAddress;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.In... 浏览器打开文章源自JAVA秀-https://www.javaxiu.com/43870.html

Java 解析复杂表格excel

解析复杂表格 在实际开发中，上传excel文件是十分常见的问题，一般情况下，解析的思路无非1. 固定表头进行解析；2. 每一行进行解析。但是偶尔会碰一下一些格式比较复杂的表格，用以上方式解析就 得不到我们想要的结果了。 例如以下这张表，乍一看是不是有种心态崩溃的感觉， 面对这种复杂表格，就需要采取特殊的方式了，首先，还是将思路，实现放到最后再说；1.按照每一行去解析，但是在解析时，需要判断是否为单元格；2. 得到数据后，还需要根据行号进行过滤，然后对每一行单元格数据进行合并操作；3. 得到数据后，最后需要 浏览器打开文章源自JAVA秀-https://www.javaxiu.com/43870.html

Java Poi Api 操作复杂excel_呆毛不会动的博客-CSDN博客文章源自JAVA秀-https://www.javaxiu.com/43870.html

javapoi 处理excel数据（不连续）

public static void main(String[] args) { // ExcelParam ec=new ExcelParam("src/test/resources/register.xlsx",2,7,1,4); int rows[]={1,2,3,4,5,6,7}; int cells[]={1,2,3,4}; Object datas[][]=Exc... 浏览器打开文章源自JAVA秀-https://www.javaxiu.com/43870.html

Java使用POI解析复杂Excel思维模式

Java使用POI解析复杂Excel思维模式 浏览器打开文章源自JAVA秀-https://www.javaxiu.com/43870.html

继续阅读
速蛙云 - 极致体验，强烈推荐！！！购买套餐就免费送各大视频网站会员！快速稳定、独家福利社、流媒体稳定解锁！速度快，全球上网、视频、游戏加速、独立IP均支持！基础套餐性价比很高！这里不多说，我一直正在使用，推荐购买：https://www.javaxiu.com/59919.html
weinxin
资源分享QQ群
本站是JAVA秀团队的技术分享社区, 会经常分享资源和教程; 分享的时代, 请别再沉默！
沙海
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定