poi导出excel工具类

最新推荐文章于 2024-08-18 23:51:42 发布

#朱守成#

最新推荐文章于 2024-08-18 23:51:42 发布

阅读量950

点赞数

分类专栏： java poi 文章标签： poi excel java导出Excel

本文链接：https://blog.csdn.net/z562743237/article/details/78836046

版权

java 同时被 2 个专栏收录

39 篇文章 3 订阅

订阅专栏

poi

3 篇文章 0 订阅

订阅专栏

poi版本

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.17</version>
		</dependency>

/**
 * Excel导出工具类
 * @author zsc
 * @datetime 2017年12月14日 下午8:01:32
 */
public class ExcelUtil {
	
	// 将需要的单元格式样式放到Map集合中,使用时直接从Map中获取，如果在使用时创建，那当数据量很大时严重影响性能
	public static ThreadLocal<Map<String, XSSFCellStyle>> styles = new ThreadLocal<>();
	
	/** 构造方法私有，禁止用户new对象 */
	private ExcelUtil() {super();}
	
	/**
	 * 导出工作簿，将工作簿写响应(response)输出流实现浏览器下载
	 * @param response
	 * @param workbook
	 * @param fileName
	 * @throws Exception
	 */
	public static void exportExcel(HttpServletResponse response, XSSFWorkbook workbook, String fileName) throws Exception {
		OutputStream os = null;
		try {
			os = response.getOutputStream();
			response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
            response.setContentType("application/msexcel");
			workbook.write(os);
		} finally {
			if(null != workbook) {
				workbook.close();
			}
			if(null != os) {
				os.flush();
				os.close();
			}
		}
	}
	
	/**
	 * 创建工作簿
	 * @param sheetNames
	 * @param headNames
	 * @param titles
	 * @param contents
	 * @return
	 * @throws Exception
	 */
	public static XSSFWorkbook createExcel(String[] sheetNames, String[] headNames, 
			List<String[]> titles, List<List<Object[]>> contents) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		int sheetCount = sheetNames.length;
		XSSFSheet sheet = null;
		String headName = "";
		for(int i = 0; i < sheetCount; i++) {
			sheet = workbook.createSheet(sheetNames[i]);
			headName = (null != headNames && StringUtils.isNotBlank(headNames[i])) ? headNames[i] : sheetNames[i];
			createExcel(workbook, sheet, headName, titles.get(i), contents.get(i));
		}
		return workbook;
	}
	
	public static XSSFWorkbook createExcel(String sheetName, String headName, 
			String[] titles, List<Object[]> contents) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet(sheetName);
		createExcel(workbook, sheet, headName, titles, contents);
		return workbook;
	}

	/**
	 * 创建工作簿
	 * @param workbook 导出工作簿
	 * @param sheet 导出工作表
	 * @param titles 标题列表
	 * @param contents 数据列表
	 * @throws Exception
	 */
	public static void createExcel(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, List<Object[]> contents) throws Exception {
		createExcel(workbook, sheet, sheet.getSheetName(), titles, contents);
	}
	
	/**
	 * 导出Excel
	 * @param os 输出流
	 * @param workbook 导出工作簿
	 * @param sheet 导出工作表
	 * @param headName 表头名
	 * @param titles 标题列表
	 * @param contents 数据列表
	 * @throws Exception
	 */
	public static void createExcel(XSSFWorkbook workbook, XSSFSheet sheet, String headName, 
			String[] titles, List<Object[]> contents) throws Exception {
		try {
			if(null == contents || contents.size() <= 0) {
				return;
			}
			// 创建单元格式样式集合
			styles.set(styleMap(workbook));
			// 创建工作表头
			createSheetHead(workbook, sheet, titles, headName);
			// 填充工作表数据
			createSheetData(sheet, contents, 2);
		} finally {
			if(null != styles.get()) {
				styles.get().clear();
			}
			styles.remove();
		}
	}
	
	/**
	 * 构建sheet表头
	 * @param sheet
	 * @param heads
	 */
	private static void createSheetHead(XSSFWorkbook workbook, XSSFSheet sheet, String[] heads, String headName) {
		sheet.createFreezePane(0, 2, 0, 2);// 冻结前2行
		sheet.setDefaultColumnWidth((short) 20);// 设置表格默认列宽度为20个字节
		XSSFCellStyle tilteStyle = styles.get().get("head");
		XSSFCell cell = null;
		XSSFRow rowFirst = sheet.createRow(0);// 创建第一行（报表名称）
		cell = rowFirst.createCell(0);
		cell.setCellValue(StringUtils.isBlank(headName) ? sheet.getSheetName() : headName);
		cell.setCellStyle(tilteStyle);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, heads.length -1));
		
		XSSFRow row = sheet.createRow(1);// 创建第二行（列名）
		if (heads != null && heads.length > 0) {
			XSSFCellStyle cellStyle = styles.get().get("title");
			for (int i = 0; i < heads.length; i++) {
				cell = row.createCell(i);
				if (heads[i] != null) {
					cell.setCellValue(heads[i]);
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	/**
	 * 构建sheet数据内容
	 * @param sheet
	 * @param contents
	 * @param index
	 */
	private static void createSheetData(XSSFSheet sheet, List<Object[]> contents, int index) {
		XSSFCellStyle contentStyle = ExcelUtil.styles.get().get("content");
		XSSFCellStyle integerStyle = ExcelUtil.styles.get().get("integer");
		XSSFCellStyle doubleStyle = ExcelUtil.styles.get().get("double");
		Iterator<Object[]> it = contents.iterator();
		Row nextrow;
		Cell cell2;
		Object[] obj;
		// 遍历数据
		while (it.hasNext()) {
			nextrow = sheet.createRow(index++);
			obj = it.next();
			if (obj != null) {
				int objLen = obj.length;
				for (int i = 0; i < objLen; i++) {
					cell2 = nextrow.createCell(i);
					if (obj[i] != null) {
						if(obj[i] instanceof Float || obj[i] instanceof Double || StringUtil.isNumeric1(obj[i].toString())){
							cell2.setCellValue(Double.parseDouble(obj[i].toString()));
							cell2.setCellStyle(doubleStyle);
						}else if(obj[i] instanceof Integer || (obj[i] instanceof Long && obj[i].toString().length() <=10)
								|| (StringUtil.isNumeric(obj[i].toString()) && obj[i].toString().length() <= 10)){
							cell2.setCellValue(Integer.parseInt(obj[i].toString()));
							cell2.setCellStyle(integerStyle);
						}else{
							cell2.setCellValue(obj[i].toString());
							cell2.setCellStyle(contentStyle);
						}
					} else {
						cell2.setCellValue("");
						cell2.setCellStyle(contentStyle);
					}
				}
			}
		}
	}
	
	/**
     * 创建单元格表头样式
     *
     * @param workbook 工作薄
     */
    private static XSSFCellStyle createCellHeadStyle(XSSFWorkbook workbook) {
    	XSSFCellStyle style = workbook.createCellStyle();
     // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 20);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }
    
    /**
     * 创建单元格表头标题样式
     *
     * @param workbook 工作薄
     */
    private static XSSFCellStyle createCellTitleStyle(XSSFWorkbook workbook) {
    	XSSFCellStyle style = workbook.createCellStyle();
     // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        XSSFFont font = workbook.createFont();
        // 表头样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(new XSSFColor(Color.CYAN));
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 创建单元格正文样式
     *
     * @param workbook 工作薄
     */
    private static XSSFCellStyle createCellContentStyle(XSSFWorkbook workbook) {
    	XSSFCellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN );
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 生成字体
        XSSFFont font = workbook.createFont();
        // 正文样式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        font.setBold(false);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 单元格样式(Integer)列表
     */
    private static XSSFCellStyle createCellContent4IntegerStyle(XSSFWorkbook workbook) {
    	XSSFCellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN );
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 生成字体
        XSSFFont font = workbook.createFont();
        // 正文样式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        font.setBold(false);
        // 把字体应用到当前的样式
        style.setFont(font);
        style.setDataFormat(workbook.createDataFormat().getFormat("#,##0"));//数据格式只显示整数
        return style;
    }

    /**
     * 单元格样式(Double)列表
     */
    private static XSSFCellStyle createCellContent4DoubleStyle(XSSFWorkbook workbook) {
    	XSSFCellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN );
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 生成字体
        XSSFFont font = workbook.createFont();
        // 正文样式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        font.setBold(false);
        // 把字体应用到当前的样式
        style.setFont(font);
        style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));//保留两位小数点
        return style;
    }
	
	/**
     * 单元格样式列表
     */
    private static Map<String, XSSFCellStyle> styleMap(XSSFWorkbook workbook) {
        Map<String, XSSFCellStyle> styleMap = new LinkedHashMap<>();
        styleMap.put("head", createCellHeadStyle(workbook));
        styleMap.put("title", createCellTitleStyle(workbook));
        styleMap.put("content", createCellContentStyle(workbook));
        styleMap.put("integer", createCellContent4IntegerStyle(workbook));
        styleMap.put("double", createCellContent4DoubleStyle(workbook));
        return styleMap;
    }
}