devbox
凡人多烦事01
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

java || poi操作excel文件【xlsx,xls】_poi-ooxml3.17添加

作者：凡人多烦事01 | 2024-03-01 02:02:32

赞

踩

poi-ooxml3.17添加

导入依赖包，poi和poi-ooxml  jar版本最好一样 

  1. <dependency>
  2.             <groupId>org.apache.poi</groupId>
  3.             <artifactId>poi-ooxml</artifactId>
  4.             <version>3.17</version>
  5.         </dependency>
  6.         <dependency>
  7.             <groupId>org.apache.poi</groupId>
  8.             <artifactId>poi</artifactId>
  9.             <version>3.17</version>
  10.         </dependency>

------------------------------------------------------------------------------------------------------------------ 

操作excel文件

  1. //xls
  2.  
  3. public static  void showXLSExcel() throws Exception {
  4.         HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File("F:\111.xls")));
  5.         HSSFSheet sheet=null;
  6.         for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
  7.             sheet=workbook.getSheetAt(i);
  8.             for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
  9.                 HSSFRow row=sheet.getRow(j);
  10.                 for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
  11.                     HSSFCell cell = row.getCell(k);
  12.                     System.out.print(row.getCell(k)+"\t");
  13.                 }
  14.                 System.out.println("---Sheet表"+i+"处理完毕---");
  15.             }
  16.         }
  17.     }
  18.  
  19.  
  20.  
  21. //xlsx
  22.  
  23. public static  void showXLSXExcel() throws Exception {
  24.         XSSFWorkbook workbook=new XSSFWorkbook(new FileInputStream(new File("F:\111.xlsx")));
  25.         XSSFSheet sheet=null;
  26.         for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
  27.             sheet=workbook.getSheetAt(i);
  28.             for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
  29.                 XSSFRow row=sheet.getRow(j);
  30.                 for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
  31.                     XSSFCell cell = row.getCell(k);
  32.                     System.out.print(row.getCell(k)+"\t");
  33.                 }
  34.                 System.out.println("---Sheet表"+i+"处理完毕---");
  35.             }
  36.         }
  37.     }

执行结果：

------------------------------------------------------------------------------------------------------------------

excel读取时兼容处理 

  1. public R importRegion(MultipartFile file,HttpServletResponse response) throws IOException {
  2. 		String fileName = file.getOriginalFilename();
  3. 		String cell1= "";
  4. 		String cell2="";
  5.  
  6. 		if(fileName.indexOf(".xlsx") != -1){
  7. 			XSSFWorkbook workbook=new XSSFWorkbook(file.getInputStream());
  8. 			XSSFSheet sheet=workbook.getSheetAt(0);
  9. 			cell1= sheet.getRow(0).getCell(0).toString();
  10. 			cell2= sheet.getRow(1).getCell(0).toString();
  11. 		}else{
  12. 			HSSFWorkbook workbook=new HSSFWorkbook(file.getInputStream());
  13. 			HSSFSheet sheet=workbook.getSheetAt(0);
  14. 			cell1= sheet.getRow(0).getCell(0).toString();
  15. 			cell2= sheet.getRow(1).getCell(0).toString();
  16. 		}
  17. }

------------------------------------------------------------------------------------------------------------------

做导出时poi操作 多个sheet页

  1.  
  2. 	@RequestMapping("etcFXExport")
  3. 	@ApiOperationSupport(order = 10)
  4. 	@ApiOperation(value = "导出列表")
  5. 	public void etcFXExport(HttpServletResponse response, EtcPassRateVO etcPassRate) throws Exception{
  6. 		List<EtcPassRateVO> list1 ;
  7. 		String time = LocalDate.now().getYear()+""+LocalDate.now().getMonth()+""+LocalDate.now().getDayOfYear();
  8. 		String titleName = "****文件"+time;
  9. 		ServletOutputStream outputStream = response.getOutputStream();
  10. 		response.setContentType("application/octet-stream; charset=utf-8");
  11. 		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(titleName+".xls","UTF-8"));
  12.  
  13. 		HSSFWorkbook workbook=new HSSFWorkbook();
  14. 		//合并单元格
  15. 		CellRangeAddress region=null;
  16. 		//设置样式
  17. 		HSSFCellStyle style = workbook.createCellStyle(); //标题区样式
  18. 		HSSFCellStyle style2 = workbook.createCellStyle();//填充值区样式
  19. 		style.setAlignment(HorizontalAlignment.CENTER);//水平靠左
  20. 		style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
  21.  
  22. 		style2.setAlignment(HorizontalAlignment.LEFT);//水平靠左
  23. 		style2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
  24.  
  25. 		// 自动换行
  26. 		style.setWrapText(true);
  27. 		style2.setWrapText(true);
  28.  
  29. 		style.setBorderBottom(BorderStyle.THIN); //下边框
  30. 		style.setBorderLeft(BorderStyle.THIN);//左边框
  31. 		style.setBorderTop(BorderStyle.THIN);//上边框
  32. 		style.setBorderRight(BorderStyle.THIN);//右边框
  33.  
  34. 		style2.setBorderBottom(BorderStyle.THIN); //下边框
  35. 		style2.setBorderLeft(BorderStyle.THIN);//左边框
  36. 		style2.setBorderTop(BorderStyle.THIN);//上边框
  37. 		style2.setBorderRight(BorderStyle.THIN);//右边框
  38. 		// 生成一个字体
  39. 		HSSFFont font = workbook.createFont();
  40. 		font.setFontHeightInPoints((short) 14);
  41. 		font.setBold(true);
  42. 		font.setColor(HSSFColor.BLACK.index);
  43. 		font.setFontName("宋体");
  44. 		HSSFFont font2 = workbook.createFont();
  45. 		font2.setFontHeightInPoints((short) 12);
  46. 		font2.setColor(HSSFColor.BLACK.index);
  47. 		font2.setFontName("宋体");
  48. 		// 把字体 应用到当前样式
  49. 		style.setFont(font);
  50. 		style2.setFont(font2);
  51.  
  52.  
  53. 		HSSFSheet sheet=workbook.createSheet("封闭式出入口");
  54. 		HSSFRow row = sheet.createRow(0);
  55. 		HSSFCell cell = null;
  56. 		cell = row.createCell(0);
  57. 		cell.setCellValue("封闭式高速出入口车道ETC通过率排名");
  58. 		cell.setCellStyle(style);
  59. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  60. 		etcPassRate.setVitrualFlag("1");
  61. 		etcPassRate.setDirecton("0,1");
  62. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  63. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  64.  
  65.  
  66.  
  67. 		sheet=workbook.createSheet("封闭式入口");
  68. 		row = sheet.createRow(0);
  69. 		cell = row.createCell(0);
  70. 		cell.setCellValue("封闭式高速入口车道ETC通过率排名");
  71. 		cell.setCellStyle(style);
  72. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  73. 		etcPassRate.setVitrualFlag("1");
  74. 		etcPassRate.setDirecton("0");
  75. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  76. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  77.  
  78. 		sheet=workbook.createSheet("封闭式出口");
  79. 		row = sheet.createRow(0);
  80. 		cell = row.createCell(0);
  81. 		cell.setCellValue("封闭式高速出口车道ETC通过率排名");
  82. 		cell.setCellStyle(style);
  83. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  84. 		etcPassRate.setVitrualFlag("1");
  85. 		etcPassRate.setDirecton("1");
  86. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  87. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  88.  
  89. 		sheet=workbook.createSheet("开放式出入口");
  90. 		row = sheet.createRow(0);
  91. 		cell = row.createCell(0);
  92. 		cell.setCellValue("开放式高速出入口车道ETC通过率排名");
  93. 		cell.setCellStyle(style);
  94. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  95. 		etcPassRate.setVitrualFlag("2");
  96. 		etcPassRate.setDirecton("0,1");
  97. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  98. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  99.  
  100. 		sheet=workbook.createSheet("委托管理出入口");
  101. 		row = sheet.createRow(0);
  102. 		cell = row.createCell(0);
  103. 		cell.setCellValue("委托管理高速出入口车道ETC通过率排名");
  104. 		cell.setCellStyle(style);
  105. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  106. 		etcPassRate.setVitrualFlag("3");
  107. 		etcPassRate.setDirecton("0,1");
  108. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  109. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  110.  
  111. 		sheet=workbook.createSheet("委托管理入口");
  112. 		row = sheet.createRow(0);
  113. 		cell = row.createCell(0);
  114. 		cell.setCellValue("委托管理高速入口车道ETC通过率排名");
  115. 		cell.setCellStyle(style);
  116. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  117. 		etcPassRate.setVitrualFlag("3");
  118. 		etcPassRate.setDirecton("0");
  119. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  120. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  121.  
  122. 		sheet=workbook.createSheet("委托管理出口");
  123. 		row = sheet.createRow(0);
  124. 		cell = row.createCell(0);
  125. 		cell.setCellValue("委托管理高速出口车道ETC通过率排名");
  126. 		cell.setCellStyle(style);
  127. 		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
  128. 		etcPassRate.setVitrualFlag("3");
  129. 		etcPassRate.setDirecton("1");
  130. 		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
  131. 		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);
  132.  
  133. 		//导出
  134. 		workbook.write(outputStream);
  135. 		outputStream.close();
  136. 	}
  137. 	//表格前三行
  138. 	public HSSFSheet getTitle(CellRangeAddress region,HSSFSheet sheet,HSSFRow row,HSSFCell cell,EtcPassRateVO etcPassRate,HSSFCellStyle style){
  139. 		sheet.setColumnWidth(1, 25 * 256);
  140. 		sheet.setColumnWidth(2, 25 * 256);
  141. 		sheet.setColumnWidth(3, 25 * 256);
  142. 		sheet.setColumnWidth(4, 25 * 256);
  143. 		sheet.setColumnWidth(5, 25 * 256);
  144. 		//合并单元格
  145. 		region=new CellRangeAddress(0, 0, 0, 8);
  146. 		sheet.addMergedRegion(region);
  147. 		//添加样式
  148. 		row.setHeightInPoints(35);
  149.  
  150. 		row = sheet.createRow(1);
  151. 		cell = row.createCell(0);
  152. 		cell.setCellValue("汇总时间："+etcPassRate.getStartTime()+"至"+etcPassRate.getEndTime());
  153. 		cell.setCellStyle(style);
  154. 		region=new CellRangeAddress(1, 1, 0, 8);
  155. 		sheet.addMergedRegion(region);
  156.  
  157. 		row = sheet.createRow(2);
  158. 		cell = row.createCell(0);
  159. 		cell.setCellValue("序号");
  160. 		cell.setCellStyle(style);
  161.  
  162. 		cell = row.createCell(1);
  163. 		cell.setCellValue("分公司");
  164. 		cell.setCellStyle(style);
  165.  
  166. 		cell = row.createCell(2);
  167. 		cell.setCellValue("收费所");
  168. 		cell.setCellStyle(style);
  169.  
  170. 		cell = row.createCell(3);
  171. 		cell.setCellValue("路段");
  172. 		cell.setCellStyle(style);
  173.  
  174. 		cell = row.createCell(4);
  175. 		cell.setCellValue("站点");
  176. 		cell.setCellStyle(style);
  177.  
  178. 		cell = row.createCell(5);
  179. 		cell.setCellValue("广场");
  180. 		cell.setCellStyle(style);
  181.  
  182. 		cell = row.createCell(6);
  183. 		cell.setCellValue("车道");
  184. 		cell.setCellStyle(style);
  185.  
  186. 		cell = row.createCell(7);
  187. 		cell.setCellValue("交易成功率");
  188. 		cell.setCellStyle(style);
  189.  
  190. 		cell = row.createCell(8);
  191. 		cell.setCellValue("交易通过率");
  192. 		cell.setCellStyle(style);
  193. 		return sheet;
  194. 	}
  195. 	//数据填充
  196. 	public HSSFSheet returnSheet(HSSFSheet sheet,HSSFWorkbook workbook,List<EtcPassRateVO> list1,HSSFRow row,HSSFCell cell,HSSFCellStyle style2){
  197. 		HSSFCellStyle style = workbook.createCellStyle();
  198. 		style.setAlignment(HorizontalAlignment.CENTER);
  199. 		int y=3,i=1;
  200. 		for(EtcPassRateVO etc:list1){
  201. 			row = sheet.createRow(y);
  202. 			row.setHeightInPoints(18);
  203. 				cell = row.createCell(0);
  204. 				cell.setCellStyle(style);
  205. 				cell.setCellValue(i);
  206. 				cell.setCellStyle(style2);
  207.  
  208. 				cell = row.createCell(1);
  209. 				cell.setCellStyle(style);
  210. 				cell.setCellValue(etc.getDeptName2());
  211. 			cell.setCellStyle(style2);
  212.  
  213. 				cell = row.createCell(2);
  214. 				cell.setCellStyle(style);
  215. 				cell.setCellValue(etc.getDeptName());
  216. 			cell.setCellStyle(style2);
  217.  
  218. 				cell = row.createCell(3);
  219. 				cell.setCellStyle(style);
  220. 				cell.setCellValue(etc.getRoadName());
  221. 			cell.setCellStyle(style2);
  222.  
  223. 				cell = row.createCell(4);
  224. 				cell.setCellStyle(style);
  225. 				cell.setCellValue(etc.getStationName());
  226. 			cell.setCellStyle(style2);
  227.  
  228. 				cell = row.createCell(5);
  229. 				cell.setCellStyle(style);
  230. 				cell.setCellValue(etc.getPlazaName());
  231. 			cell.setCellStyle(style2);
  232.  
  233. 				cell = row.createCell(6);
  234. 				cell.setCellStyle(style);
  235. 				cell.setCellValue(etc.getLane());
  236. 			cell.setCellStyle(style2);
  237.  
  238. 				cell = row.createCell(7);
  239. 				cell.setCellStyle(style);
  240. 				cell.setCellValue(etc.getSuccessRate());
  241. 			cell.setCellStyle(style2);
  242.  
  243. 				cell = row.createCell(8);
  244. 				cell.setCellStyle(style);
  245. 				cell.setCellValue(etc.getDealPassRate());
  246. 			cell.setCellStyle(style2);
  247. 			y=y+1;
  248. 			i=i+1;
  249. 		}
  250. 		return sheet;
  251. 	}

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/170192
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号