package com.chinada.dms.utils; import java.io.IOException; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Map; import cn.afterturn.easypoi.exception.excel.ExcelExportException; import org.apache.poi.hpsf.Array; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class ExportUtil { public XSSFWorkbook getWorkbook() { return workbook; } public void setWorkbook(XSSFWorkbook workbook) { this.workbook = workbook; } public Sheet getSheet() { return sheet; } public void setSheet(Sheet sheet) { this.sheet = sheet; } private XSSFWorkbook workbook; private Sheet sheet; public ExportUtil(String sheetName) { workbook = new XSSFWorkbook(); sheet = workbook.createSheet(sheetName); } /** * 设置标题行 * * @param rowNum 行号 * @param data 数据 */ public void setHeadlineRow(int rowNum, List<Object> data) { Row row = sheet.createRow(rowNum - 1); for (int i = 1; i <= data.size(); i++) { Cell cell = row.createCell(i); cell.setCellValue(data.get(i - 1) + ""); cell.setCellStyle(getHeadCellStyle()); } } /** * 设置一行的数据 * * @param rowNum 行号 * @param data 数据 */ public void setRow(int rowNum, List<Object> data) { Row row = sheet.createRow(rowNum - 1); for (int i = 1; i <= data.size(); i++) { Cell cell = row.createCell(i); cell.setCellValue(data.get(i - 1) + ""); } } /** * 添加数据,从第一个空行开始 * * @param data 数据 * @param dataOrder 数据取出key顺序的List,从左到右 */ public void setData(List<Map<String, Object>> data, List<String> dataOrder) { int startRowNum = sheet.getLastRowNum() + 1; for (int i = 0; i < data.size(); i++) { Map<String, Object> rowData = data.get(i); Row row = sheet.createRow(startRowNum + i); for (int x = 0; x < dataOrder.size(); x++) { Cell cell = row.createCell(x + 1); cell.setCellValue(rowData.get(dataOrder.get(x)) + ""); } } } public CellStyle getHeadCellStyle() { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 颜色 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充模式 cellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 生成一个字体 Font font = workbook.createFont(); font.setBold(true);// 加粗 font.setFontHeightInPoints((short) 13);// 字体大小 cellStyle.setFont(font); return cellStyle; } /** * 合并单元格 * * @param firstRow 开始行 * @param lastRow 结束行 * @param firstCol 开始列 * @param lastCol 结束列 */ public void mergeCell(int firstRow, int lastRow, int firstCol, int lastCol) { CellRangeAddress region = new CellRangeAddress(firstRow - 1, lastRow - 1, firstCol, lastCol); sheet.addMergedRegion(region); } /** * 自适应列宽 * * @param rowNum 以一行来自适应 */ public void autoColumnWidth(int rowNum) { Row row = sheet.getRow(rowNum - 1); for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) { sheet.autoSizeColumn(i); sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10); } } /** * 实现Excel下载 * @param fileName * @param response * @param workbook */ public static void downLoadExcel(String fileName, HttpServletResponse response, HttpServletRequest request, Workbook workbook) { try { String browser = ""; browser = request.getHeader("User-Agent"); if (-1 < browser.indexOf("MSIE 6.0") || -1 < browser.indexOf("MSIE 7.0")) { // IE6, IE7 浏览器 response.addHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1")); } else if (-1 < browser.indexOf("MSIE 8.0")) { // IE8 response.addHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); } else if (-1 < browser.indexOf("MSIE 9.0")) { // IE9 response.addHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); } else if (-1 < browser.indexOf("Chrome")) { // 谷歌 response.addHeader("content-disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8")); } else if (-1 < browser.indexOf("Safari")) { // 苹果 response.addHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1")); } else { // 火狐或者其他的浏览器 response.addHeader("content-disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8")); } workbook.write(response.getOutputStream()); } catch (IOException e) { throw new ExcelExportException(e.getMessage()); } } // 使用例子 public static void main(String[] args) { List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); // 使用时请替换为真实数据 // List<Map<String, Object>> dataList = statisticsMapper.selectYearBatchByMonth(year); //比如说这样 ExportUtil export = new ExportUtil("年度每月检查执行的统计"); // 页标名 List<Object> head = Arrays.asList("2019年每月检查执行的统计"); //标题行 export.setHeadlineRow(2, head);// 添加标题行到第二行 export.mergeCell(2, 2, 1, 6); // 合并单元格 // 普通数据行 List<Object> headList = Arrays.asList("2019年", "赛内检查(例)", "赛外检查(例)", "小计(例)", "派遣批次(批)", "派遣人次(人次)"); // 普通数据行添加到第3行 export.setRow(3, headList); // 设置数据的key顺序 List<String> order = Arrays.asList("mon", "in_num", "out_num", "subtotal", "batch_num", "inquisitor_num"); // 开始添加数据 export.setData(dataList, order); //自适应单元格宽度 export.autoColumnWidth(3); } }
ExportUtil 导出工具类
原创
©著作权归作者所有:来自51CTO博客作者小飞侠格鲁帅的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:selectList
下一篇:StringUtil
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章