devbox
菜鸟追梦旅行
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

poi导出excel工具类_poi-ooxml-3.1.7

作者：菜鸟追梦旅行 | 2024-03-01 02:03:04

赞

踩

poi-ooxml-3.1.7


poi版本

  1. <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
  2. 		<dependency>
  3. 			<groupId>org.apache.poi</groupId>
  4. 			<artifactId>poi</artifactId>
  5. 			<version>3.17</version>
  6. 		</dependency>
  7. 		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
  8. 		<dependency>
  9. 		    <groupId>org.apache.poi</groupId>
  10. 		    <artifactId>poi-ooxml</artifactId>
  11. 		    <version>3.17</version>
  12. 		</dependency>


  1. /**
  2.  * Excel导出工具类
  3.  * @author zsc
  4.  * @datetime 2017年12月14日 下午8:01:32
  5.  */
  6. public class ExcelUtil {
  7. 	
  8. 	// 将需要的单元格式样式放到Map集合中,使用时直接从Map中获取，如果在使用时创建，那当数据量很大时严重影响性能
  9. 	public static ThreadLocal<Map<String, XSSFCellStyle>> styles = new ThreadLocal<>();
  10. 	
  11. 	/** 构造方法私有，禁止用户new对象 */
  12. 	private ExcelUtil() {super();}
  13. 	
  14. 	/**
  15. 	 * 导出工作簿，将工作簿写响应(response)输出流实现浏览器下载
  16. 	 * @param response
  17. 	 * @param workbook
  18. 	 * @param fileName
  19. 	 * @throws Exception
  20. 	 */
  21. 	public static void exportExcel(HttpServletResponse response, XSSFWorkbook workbook, String fileName) throws Exception {
  22. 		OutputStream os = null;
  23. 		try {
  24. 			os = response.getOutputStream();
  25. 			response.reset();
  26.             response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
  27.             response.setContentType("application/msexcel");
  28. 			workbook.write(os);
  29. 		} finally {
  30. 			if(null != workbook) {
  31. 				workbook.close();
  32. 			}
  33. 			if(null != os) {
  34. 				os.flush();
  35. 				os.close();
  36. 			}
  37. 		}
  38. 	}
  39. 	
  40. 	/**
  41. 	 * 创建工作簿
  42. 	 * @param sheetNames
  43. 	 * @param headNames
  44. 	 * @param titles
  45. 	 * @param contents
  46. 	 * @return
  47. 	 * @throws Exception
  48. 	 */
  49. 	public static XSSFWorkbook createExcel(String[] sheetNames, String[] headNames, 
  50. 			List<String[]> titles, List<List<Object[]>> contents) throws Exception {
  51. 		XSSFWorkbook workbook = new XSSFWorkbook();
  52. 		int sheetCount = sheetNames.length;
  53. 		XSSFSheet sheet = null;
  54. 		String headName = "";
  55. 		for(int i = 0; i < sheetCount; i++) {
  56. 			sheet = workbook.createSheet(sheetNames[i]);
  57. 			headName = (null != headNames && StringUtils.isNotBlank(headNames[i])) ? headNames[i] : sheetNames[i];
  58. 			createExcel(workbook, sheet, headName, titles.get(i), contents.get(i));
  59. 		}
  60. 		return workbook;
  61. 	}
  62. 	
  63. 	public static XSSFWorkbook createExcel(String sheetName, String headName, 
  64. 			String[] titles, List<Object[]> contents) throws Exception {
  65. 		XSSFWorkbook workbook = new XSSFWorkbook();
  66. 		XSSFSheet sheet = workbook.createSheet(sheetName);
  67. 		createExcel(workbook, sheet, headName, titles, contents);
  68. 		return workbook;
  69. 	}
  70.  
  71. 	/**
  72. 	 * 创建工作簿
  73. 	 * @param workbook 导出工作簿
  74. 	 * @param sheet 导出工作表
  75. 	 * @param titles 标题列表
  76. 	 * @param contents 数据列表
  77. 	 * @throws Exception
  78. 	 */
  79. 	public static void createExcel(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, List<Object[]> contents) throws Exception {
  80. 		createExcel(workbook, sheet, sheet.getSheetName(), titles, contents);
  81. 	}
  82. 	
  83. 	/**
  84. 	 * 导出Excel
  85. 	 * @param os 输出流
  86. 	 * @param workbook 导出工作簿
  87. 	 * @param sheet 导出工作表
  88. 	 * @param headName 表头名
  89. 	 * @param titles 标题列表
  90. 	 * @param contents 数据列表
  91. 	 * @throws Exception
  92. 	 */
  93. 	public static void createExcel(XSSFWorkbook workbook, XSSFSheet sheet, String headName, 
  94. 			String[] titles, List<Object[]> contents) throws Exception {
  95. 		try {
  96. 			if(null == contents || contents.size() <= 0) {
  97. 				return;
  98. 			}
  99. 			// 创建单元格式样式集合
  100. 			styles.set(styleMap(workbook));
  101. 			// 创建工作表头
  102. 			createSheetHead(workbook, sheet, titles, headName);
  103. 			// 填充工作表数据
  104. 			createSheetData(sheet, contents, 2);
  105. 		} finally {
  106. 			if(null != styles.get()) {
  107. 				styles.get().clear();
  108. 			}
  109. 			styles.remove();
  110. 		}
  111. 	}
  112. 	
  113. 	/**
  114. 	 * 构建sheet表头
  115. 	 * @param sheet
  116. 	 * @param heads
  117. 	 */
  118. 	private static void createSheetHead(XSSFWorkbook workbook, XSSFSheet sheet, String[] heads, String headName) {
  119. 		sheet.createFreezePane(0, 2, 0, 2);// 冻结前2行
  120. 		sheet.setDefaultColumnWidth((short) 20);// 设置表格默认列宽度为20个字节
  121. 		XSSFCellStyle tilteStyle = styles.get().get("head");
  122. 		XSSFCell cell = null;
  123. 		XSSFRow rowFirst = sheet.createRow(0);// 创建第一行（报表名称）
  124. 		cell = rowFirst.createCell(0);
  125. 		cell.setCellValue(StringUtils.isBlank(headName) ? sheet.getSheetName() : headName);
  126. 		cell.setCellStyle(tilteStyle);
  127. 		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, heads.length -1));
  128. 		
  129. 		XSSFRow row = sheet.createRow(1);// 创建第二行（列名）
  130. 		if (heads != null && heads.length > 0) {
  131. 			XSSFCellStyle cellStyle = styles.get().get("title");
  132. 			for (int i = 0; i < heads.length; i++) {
  133. 				cell = row.createCell(i);
  134. 				if (heads[i] != null) {
  135. 					cell.setCellValue(heads[i]);
  136. 					cell.setCellStyle(cellStyle);
  137. 				}
  138. 			}
  139. 		}
  140. 	}
  141. 	
  142. 	/**
  143. 	 * 构建sheet数据内容
  144. 	 * @param sheet
  145. 	 * @param contents
  146. 	 * @param index
  147. 	 */
  148. 	private static void createSheetData(XSSFSheet sheet, List<Object[]> contents, int index) {
  149. 		XSSFCellStyle contentStyle = ExcelUtil.styles.get().get("content");
  150. 		XSSFCellStyle integerStyle = ExcelUtil.styles.get().get("integer");
  151. 		XSSFCellStyle doubleStyle = ExcelUtil.styles.get().get("double");
  152. 		Iterator<Object[]> it = contents.iterator();
  153. 		Row nextrow;
  154. 		Cell cell2;
  155. 		Object[] obj;
  156. 		// 遍历数据
  157. 		while (it.hasNext()) {
  158. 			nextrow = sheet.createRow(index++);
  159. 			obj = it.next();
  160. 			if (obj != null) {
  161. 				int objLen = obj.length;
  162. 				for (int i = 0; i < objLen; i++) {
  163. 					cell2 = nextrow.createCell(i);
  164. 					if (obj[i] != null) {
  165. 						if(obj[i] instanceof Float || obj[i] instanceof Double || StringUtil.isNumeric1(obj[i].toString())){
  166. 							cell2.setCellValue(Double.parseDouble(obj[i].toString()));
  167. 							cell2.setCellStyle(doubleStyle);
  168. 						}else if(obj[i] instanceof Integer || (obj[i] instanceof Long && obj[i].toString().length() <=10)
  169. 								|| (StringUtil.isNumeric(obj[i].toString()) && obj[i].toString().length() <= 10)){
  170. 							cell2.setCellValue(Integer.parseInt(obj[i].toString()));
  171. 							cell2.setCellStyle(integerStyle);
  172. 						}else{
  173. 							cell2.setCellValue(obj[i].toString());
  174. 							cell2.setCellStyle(contentStyle);
  175. 						}
  176. 					} else {
  177. 						cell2.setCellValue("");
  178. 						cell2.setCellStyle(contentStyle);
  179. 					}
  180. 				}
  181. 			}
  182. 		}
  183. 	}
  184. 	
  185. 	/**
  186.      * 创建单元格表头样式
  187.      *
  188.      * @param workbook 工作薄
  189.      */
  190.     private static XSSFCellStyle createCellHeadStyle(XSSFWorkbook workbook) {
  191.     	XSSFCellStyle style = workbook.createCellStyle();
  192.      // 设置边框样式
  193.         style.setBorderBottom(BorderStyle.THIN);
  194.         style.setBorderLeft(BorderStyle.THIN);
  195.         style.setBorderRight(BorderStyle.THIN);
  196.         style.setBorderTop(BorderStyle.THIN);
  197.         //设置对齐样式
  198.         style.setAlignment(HorizontalAlignment.CENTER);
  199.         // 生成字体
  200.         XSSFFont font = workbook.createFont();
  201.         font.setFontHeightInPoints((short) 20);
  202.         font.setBold(true);
  203.         // 把字体应用到当前的样式
  204.         style.setFont(font);
  205.         return style;
  206.     }
  207.     
  208.     /**
  209.      * 创建单元格表头标题样式
  210.      *
  211.      * @param workbook 工作薄
  212.      */
  213.     private static XSSFCellStyle createCellTitleStyle(XSSFWorkbook workbook) {
  214.     	XSSFCellStyle style = workbook.createCellStyle();
  215.      // 设置边框样式
  216.         style.setBorderBottom(BorderStyle.THIN);
  217.         style.setBorderLeft(BorderStyle.THIN);
  218.         style.setBorderRight(BorderStyle.THIN);
  219.         style.setBorderTop(BorderStyle.THIN);
  220.         //设置对齐样式
  221.         style.setAlignment(HorizontalAlignment.CENTER);
  222.         // 生成字体
  223.         XSSFFont font = workbook.createFont();
  224.         // 表头样式
  225.         style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  226.         style.setFillForegroundColor(new XSSFColor(Color.CYAN));
  227.         font.setBold(true);
  228.         // 把字体应用到当前的样式
  229.         style.setFont(font);
  230.         return style;
  231.     }
  232.  
  233.     /**
  234.      * 创建单元格正文样式
  235.      *
  236.      * @param workbook 工作薄
  237.      */
  238.     private static XSSFCellStyle createCellContentStyle(XSSFWorkbook workbook) {
  239.     	XSSFCellStyle style = workbook.createCellStyle();
  240.         // 设置边框样式
  241.         style.setBorderBottom(BorderStyle.THIN );
  242.         style.setBorderLeft(BorderStyle.THIN);
  243.         style.setBorderRight(BorderStyle.THIN);
  244.         style.setBorderTop(BorderStyle.THIN);
  245.         // 生成字体
  246.         XSSFFont font = workbook.createFont();
  247.         // 正文样式
  248.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  249.         font.setBold(false);
  250.         // 把字体应用到当前的样式
  251.         style.setFont(font);
  252.         return style;
  253.     }
  254.  
  255.     /**
  256.      * 单元格样式(Integer)列表
  257.      */
  258.     private static XSSFCellStyle createCellContent4IntegerStyle(XSSFWorkbook workbook) {
  259.     	XSSFCellStyle style = workbook.createCellStyle();
  260.         // 设置边框样式
  261.         style.setBorderBottom(BorderStyle.THIN );
  262.         style.setBorderLeft(BorderStyle.THIN);
  263.         style.setBorderRight(BorderStyle.THIN);
  264.         style.setBorderTop(BorderStyle.THIN);
  265.         // 生成字体
  266.         XSSFFont font = workbook.createFont();
  267.         // 正文样式
  268.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  269.         font.setBold(false);
  270.         // 把字体应用到当前的样式
  271.         style.setFont(font);
  272.         style.setDataFormat(workbook.createDataFormat().getFormat("#,##0"));//数据格式只显示整数
  273.         return style;
  274.     }
  275.  
  276.     /**
  277.      * 单元格样式(Double)列表
  278.      */
  279.     private static XSSFCellStyle createCellContent4DoubleStyle(XSSFWorkbook workbook) {
  280.     	XSSFCellStyle style = workbook.createCellStyle();
  281.         // 设置边框样式
  282.         style.setBorderBottom(BorderStyle.THIN );
  283.         style.setBorderLeft(BorderStyle.THIN);
  284.         style.setBorderRight(BorderStyle.THIN);
  285.         style.setBorderTop(BorderStyle.THIN);
  286.         // 生成字体
  287.         XSSFFont font = workbook.createFont();
  288.         // 正文样式
  289.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  290.         font.setBold(false);
  291.         // 把字体应用到当前的样式
  292.         style.setFont(font);
  293.         style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));//保留两位小数点
  294.         return style;
  295.     }
  296. 	
  297. 	/**
  298.      * 单元格样式列表
  299.      */
  300.     private static Map<String, XSSFCellStyle> styleMap(XSSFWorkbook workbook) {
  301.         Map<String, XSSFCellStyle> styleMap = new LinkedHashMap<>();
  302.         styleMap.put("head", createCellHeadStyle(workbook));
  303.         styleMap.put("title", createCellTitleStyle(workbook));
  304.         styleMap.put("content", createCellContentStyle(workbook));
  305.         styleMap.put("integer", createCellContent4IntegerStyle(workbook));
  306.         styleMap.put("double", createCellContent4DoubleStyle(workbook));
  307.         return styleMap;
  308.     }
  309. }

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

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

  

闽ICP备14008679号