• 使用Java类库POI生成简易的Excel报表


    使用Java类库POI生成简易的Excel报表

    1.需求

    1.数据库生成报表需要转义其中字段的信息。比如 1,有效 2.无效等

    2.日期格式的自数据需要转义其格式。

    3.标题的格式和数据的格式需要分别设置

    4.可能出现的实体类嵌套实体类的情况

    5.需要在行尾添加统计数据

    2.具体实现

    首先创建一个用于存放标题、反射方法、转义列表、统计的辅助实体对象

    
    import java.util.Map;
    
    /**
     * 列开头信息和结尾信息
     *
     * @author yanlong 2018年03月06日09:36:18
     *
     *
     */
    public class BaseInf {
    	/*
    	 * 标题
    	 */
    	private String titleName;
    	/*
    	 * 行读取方法
    	 */
    	private String columMethod;
    	/*
    	 * 转义列表
    	 */
    	private Map<String, String> map;
    	/*
    	 * 行尾统计
    	 */
    	private String count;
    
    
    	/**
    	 * 有转义列表 有统计的构造方法
    	 * @param titleName 标题
    	 * @param columMethod 获取方法
    	 * @param map 转义列表
    	 * @param count 统计
    	 */
    	public BaseInf(String titleName, String columMethod, Map<String, String> map, String count) {
    		super();
    		this.titleName = titleName;
    		this.columMethod = columMethod;
    		this.map = map;
    		this.count = count;
    	}
    
    	/**
    	 * 有转义列表构造方法
    	 *
    	 * @param titleName 标题
    	 * @param columMethod 获取方法
    	 * @param map 转义列表
    	 */
    	public BaseInf(String titleName, String columMethod, Map<String, String> map) {
    		super();
    		this.titleName = titleName;
    		this.columMethod = columMethod;
    		this.map = map;
    		this.count = null;
    	}
    
    	/**
    	 * 无转义列表 有合计数据
    	 * @param titleName 标题
    	 * @param columMethod 读取方法列表
    	 * @param count 合计值
    	 */
    	public BaseInf(String titleName, String columMethod, String count) {
    		super();
    		this.titleName = titleName;
    		this.columMethod = columMethod;
    		this.map = null;
    		this.count = count;
    	}
    
    	/**
    	 * 无转义列表无统计构造方法
    	 *
    	 * @param titleName
    	 *            标题
    	 * @param columMethod
    	 *            获取方法
    	 */
    	public BaseInf(String titleName, String columMethod) {
    		super();
    		this.titleName = titleName;
    		this.columMethod = columMethod;
    		this.map = null;
    		this.count = null;
    	}
    
    	public String getTitleName() {
    		return titleName;
    	}
    
    	public String getColumMethod() {
    		return columMethod;
    	}
    
    	public Map<String, String> getMap() {
    		return map;
    	}
    
    	public String getCount() {
    		return count;
    	}
    }
    
    

    具体的创建过程将创建表单和传输方式分开 以便于可以本地下载或者服务器下载

    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ExcelUtil {
    	// 私有构造方法禁止new实例
    	private ExcelUtil() {
    	}
    
    	// 日志工具
    	// private static final Logger logger =
    	// LoggerFactory.getLogger(ExcelUtil.class);
    	// 默认日期格式
    	private static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日 hh点mm分ss秒";
    	// 默认行高
    	private static final Short DEFAULT_COLOUMN_HEIGHT = 400;
    
    	private static final Short DEFAULT_COLOUMN_WEIGHT = 170;
    
    	/**
    	 * 将工作表输出到浏览器中
    	 *
    	 * @param response
    	 *            响应流
    	 * @param workbook
    	 *            创建完成的工作表
    	 * @param fileName
    	 *            文件名
    	 * @param sufferNm
    	 *            文件后缀名
    	 * @throws Exception
    	 */
    	public static void workbook2InputStream(HttpServletResponse response, Workbook workbook, String fileName,
    			String sufferNm) throws Exception {
    		response.setCharacterEncoding("utf-8");
    		response.setHeader("Content-type", "application/vnd.ms-excel");
    		response.setHeader("Content-Disposition",
    				"attachment; filename=" + new String((fileName).getBytes("gb2312"), "ISO8859-1") + sufferNm);
    		// 设置下载头信息
    		response.setContentType("application nd.ms-excel; charset=utf-8");
    		workbook.write(response.getOutputStream());
    		response.getOutputStream().flush();
    		response.getOutputStream().close();
    	}
    
    	/**
    	 * 实体类单层嵌套
    	 */
    	public static Workbook createWorkbook(int version, String sheetNm, List<BaseInf> baseInfList, List<?> list)
    			throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException,
    			InvocationTargetException {
    		return createWorkbook(version, sheetNm, baseInfList, list, null);
    	}
    
    	/**
    	 * 创建一个数据表 实体类嵌套实体类
    	 *
    	 * @param version
    	 *            excel版本 2007 或者其他
    	 * @param sheetNm
    	 *            sheet 名称
    	 * @param baseInfList
    	 *            数据基础信息
    	 * @param list
    	 *            数据
    	 * @param innerMethod
    	 *            实体类多层嵌套
    	 *
    	 * @see BaseInf
    	 *
    	 * @return 构建完成的数据表对象
    	 *
    	 * @throws SecurityException
    	 * @throws NoSuchMethodException
    	 * @throws InvocationTargetException
    	 * @throws IllegalArgumentException
    	 * @throws IllegalAccessException
    	 */
    	public static Workbook createWorkbook(int version, String sheetNm, List<BaseInf> baseInfList, List<?> list,
    			String innerMethod) throws NoSuchMethodException, SecurityException, IllegalAccessException,
    			IllegalArgumentException, InvocationTargetException {
    		SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_PATTERN);
    		Workbook workbook = null;
    		if (version == 2007) {
    			workbook = new XSSFWorkbook();
    		} else {
    			workbook = new HSSFWorkbook();
    		}
    		Sheet sheet = workbook.createSheet(isEmpty(sheetNm) ? "sheet1" : sheetNm);
    		// 写入标题
    		CellStyle titleStyle = titleStyle(workbook);
    		// 创建标题行(第一行)
    		Row titleRow = sheet.createRow(0);
    		// 设置第一行的行高
    		titleRow.setHeight(DEFAULT_COLOUMN_HEIGHT);
    		Cell cell = null;
    		// 设置序号
    		sheet.setColumnWidth(0, DEFAULT_COLOUMN_WEIGHT);
    		cell = titleRow.createCell(0);
    		cell.setCellType(Cell.CELL_TYPE_STRING);
    		cell.setCellValue("序号");
    		cell.setCellStyle(titleStyle);
    		// 其他标题
    		for (int i = 0; i < baseInfList.size(); i++) {
    			String titleName = baseInfList.get(i).getTitleName();
    			// 设置单元格的宽
    			sheet.setColumnWidth(i, titleName.length() * 1500);
    			cell = titleRow.createCell(i + 1);
    			cell.setCellType(Cell.CELL_TYPE_STRING);
    			cell.setCellValue(titleName);
    			cell.setCellStyle(titleStyle);
    		}
    		/**
    		 * 写入数据
    		 *
    		 * 写入数据按照先行 后列的的方式进行
    		 *
    		 */
    		CellStyle dataStyle = dataStyle(workbook);
    		Row dataRow = null;
    		for (int i = 0; i < list.size(); i++) {
    			// 创建行
    			dataRow = sheet.createRow(i + 1);
    			// 创建列 此处为序号列
    			cell = dataRow.createCell(0);
    			cell.setCellType(Cell.CELL_TYPE_STRING);
    			cell.setCellValue(i + 1);
    			cell.setCellStyle(titleStyle);
    			// 序号列创建完毕 开始创建数据列
    			for (int j = 0; j < baseInfList.size(); j++) {
    				// 创建数据列
    				cell = dataRow.createCell(j + 1);
    				BaseInf baseInf = baseInfList.get(j);
    				// 设值
    				Method method;
    				Object value;
    				if (innerMethod != null) {
    					method = list.get(i).getClass().getMethod(innerMethod);
    					Object obj = method.invoke(list.get(i));
    					method = obj.getClass().getMethod(baseInf.getColumMethod());
    					value = method.invoke(obj);
    				} else {
    					method = list.get(i).getClass().getMethod(baseInf.getColumMethod());
    					value = method.invoke(list.get(i));
    				}
    				String returnType = method.getReturnType().getName().toLowerCase();
    				cell.setCellStyle(dataStyle);
    				// 转义列表
    				Map<String, String> transMap = baseInf.getMap();
    				// 判断是否需要转义
    				if (transMap == null) {
    					if (returnType.indexOf("string") != -1) {
    						cell.setCellType(Cell.CELL_TYPE_STRING);
    						cell.setCellValue(value == null ? "" : value.toString());
    					} else if (returnType.indexOf("integer") != -1 || returnType.indexOf("int") != -1
    							|| returnType.indexOf("bigdecimal") != -1 || returnType.indexOf("double") != -1
    							|| returnType.indexOf("long") != -1 || returnType.indexOf("float") != -1) {
    						cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    						cell.setCellValue(value == null ? null : new Double(value.toString()));
    					} else if (returnType.indexOf("date") != -1) {
    						cell.setCellType(Cell.CELL_TYPE_STRING);
    						cell.setCellValue(value == null ? null : sdf.format((Date) value));
    					} else {
    						cell.setCellType(Cell.CELL_TYPE_STRING);
    						cell.setCellValue(value == null ? "" : value.toString());
    					}
    				} else {
    					cell.setCellType(Cell.CELL_TYPE_STRING);
    					String cellValue = value == null ? "" : transMap.get(tse(value.toString()));
    					cell.setCellValue(cellValue == null ? tse(value.toString()) : cellValue);
    				}
    			}
    		}
    		// 创建统计行
    		// 创建行
    		dataRow = sheet.createRow(list.size() + 1);
    		// 创建列 此处为序号列
    		cell = dataRow.createCell(0);
    		cell.setCellType(Cell.CELL_TYPE_STRING);
    		cell.setCellValue("统计");
    		cell.setCellStyle(titleStyle);
    		for (int i = 0; i < baseInfList.size(); i++) {
    			BaseInf baseInf = baseInfList.get(i);
    			cell = dataRow.createCell(i + 1);
    			cell.setCellType(Cell.CELL_TYPE_STRING);
    			cell.setCellStyle(dataStyle);
    			if (baseInf.getCount() != null) {
    				cell.setCellValue(baseInf.getCount());
    			} else {
    				cell.setCellValue("");
    			}
    		}
    		return workbook;
    	}
    
    	/**
    	 *
    	 * 删除字符串内的回车 空格和两端空白
    	 *
    	 * @author yanlong 2017-7-5
    	 *
    	 */
    	private static String tse(String str) {
    		return str == null ? "" : str.replace(" ", "").replace("/r", "").replace("/n", "").trim();
    	}
    
    	// 判断非空
    	private static boolean isEmpty(String str) {
    		return str == null || "".equals(str.trim());
    	}
    
    	/**
    	 * 设置标题样式
    	 *
    	 * @param workbook 工作表
    	 * @return 标题样式
    	 */
    	private static CellStyle titleStyle(Workbook workbook) {
    		CellStyle titleStyle = workbook.createCellStyle();
    		titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    		titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    		titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); // 居中
    		titleStyle.setBorderLeft((short) 1);
    		titleStyle.setBorderRight((short) 1);
    		titleStyle.setBorderBottom((short) 1);
    		titleStyle.setBorderTop((short) 1);
    		Font font = workbook.createFont();
    		font.setFontHeightInPoints((short) 12);// 设置字体大小
    		titleStyle.setFont(font);// 选择需要用到的字体格式
    		return titleStyle;
    	}
    
    	/**
    	 * 数据样式
    	 *
    	 * @param workbook 工作表
    	 * @return 数据样式
    	 */
    	private static CellStyle dataStyle(Workbook workbook) {
    		CellStyle dataStyle = workbook.createCellStyle();
    		dataStyle.setBorderBottom((short) 1);
    		dataStyle.setBorderLeft((short) 1);
    		dataStyle.setBorderRight((short) 1);
    		dataStyle.setBorderTop((short) 1);
    		dataStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    		return dataStyle;
    	}
    }
    
    
    

    调用方式

    //下载文件
    List<BaseInf> baseInfList = new ArrayList<BaseInf>();
    BaseInf baseInf = new BaseInf("商户编号","getTACCOUNTID");
    baseInfList.add(baseInf);
    baseInf = new BaseInf("商户名称","getMERNAME");
    baseInfList.add(baseInf);
    baseInf = new BaseInf("商户类型","getMERTYPE",Tmerinfo.merTypeTransMap());
    baseInfList.add(baseInf);
    baseInf = new BaseInf("商户状态","getSTATE",Tmerinfo.statesTransMap());
    baseInfList.add(baseInf);
    baseInf = new BaseInf("商户分级","getMERLEVEL",Tmerinfo.merlevelTransMap(ms));
    baseInfList.add(baseInf);
    baseInf = new BaseInf("企业全称","getFULLNAME");
    baseInfList.add(baseInf);
    baseInf = new BaseInf("创建时间","getOPENTIME");
    baseInfList.add(baseInf);
    baseInf = new BaseInf("开通时间","getREGTIME");
    baseInfList.add(baseInf);
    Workbook workbook = ExcelUtil.createWorkbook(2007, "商户管理-商户查询", baseInfList, mercertinfo,"getTmerinfoDTO");
    ExcelUtil.workbook2InputStream(response, workbook, "userQuery", ".xls");
    
    
  • 相关阅读:
    Electron+Vue开发跨平台桌面应用
    html2canvas生成图片
    将某个DIV内容保存成图片,使用HTML2CANVAS截图方法(高清图并解决图片跨域问题)
    css3实现动画效果完整代码demo
    Vue + element从零打造一个H5页面可视化编辑器——pl-drag-template
    Vue.Draggable学习总结
    3d学习网
    vue router 报错: Uncaught (in promise) NavigationDuplicated {_name:""NavigationDuplicated"... 的解决方法
    网页适配 iPhoneX,就是这么简单
    关于for循环
  • 原文地址:https://www.cnblogs.com/yanlong300/p/8582022.html
Copyright © 2020-2023  润新知