ExcelExportUtiler excel工具类
来源：互联网 发布：软件激活码商城 编辑：程序博客网 时间：2024/06/09 23:40
public class ExcelExportUtiler { private static Log log = LogFactory.getLog(ExcelExportUtiler.class); /*工作薄*/ private HSSFWorkbook workbook; /*默认格子宽度*/ private int defaultColumnWidth = 20; /*sheet页的名称*/ private String sheetName; /*默认标题字体*/ private HSSFFont defaultTitleFont; /*默认内容字体*/ private HSSFFont defaultContentFont; /*默认标题风格*/ private HSSFCellStyle defaultTitleStyle; /*默认内容风格*/ private HSSFCellStyle defaultContentStyle; /*最后一个sheet页索引*/ private int lastSheetIndex = 0; public HSSFWorkbook getWorkbook() { return workbook; } public int getDefaultColumnWidth() { return defaultColumnWidth; } public String getSheetName() { return sheetName; } public HSSFFont getDefaultTitleFont() { return defaultTitleFont; } public HSSFFont getDefaultContentFont() { return defaultContentFont; } public HSSFCellStyle getDefaultTitleStyle() { return defaultTitleStyle; } public HSSFCellStyle getDefaultContentStyle() { return defaultContentStyle; } public ExcelExportUtiler(){ this(null,0,null,null,null,null,null); } public ExcelExportUtiler(int defaultColumnWidth){ this(defaultColumnWidth,null); } public ExcelExportUtiler(String sheetName){ this(0,sheetName); } public ExcelExportUtiler(int defaultColumnWidth, String sheetName){ this(null,defaultColumnWidth,sheetName); } public ExcelExportUtiler(HSSFWorkbook workbook, int defaultColumnWidth, String sheetName){ this(workbook,defaultColumnWidth,sheetName,null,null,null,null); } public ExcelExportUtiler(HSSFWorkbook workbook, int defaultColumnWidth, String sheetName, HSSFFont defaultTitleFont, HSSFFont defaultContentFont, HSSFCellStyle defaultTitleStyle, HSSFCellStyle defaultContentStyle){ if(workbook == null){ this.workbook = new HSSFWorkbook(); }else{ this.workbook = workbook; } if(defaultColumnWidth == 0){ this.defaultColumnWidth = 20; }else{ this.defaultColumnWidth = defaultColumnWidth; } if(sheetName == null || "".equals(sheetName.trim())){ this.sheetName = "sheet"; }else{ this.sheetName = sheetName; } if(defaultTitleFont == null){ this.defaultTitleFont = getDefaultTitleFont(this.workbook); }else{ this.defaultTitleFont = defaultTitleFont; } if(defaultContentFont == null){ this.defaultContentFont = getDefaultContentFont(this.workbook); }else{ this.defaultContentFont = defaultContentFont; } if(defaultTitleStyle == null){ this.defaultTitleStyle = getDefaultTitleStyle(this.workbook); }else{ this.defaultTitleStyle = defaultTitleStyle; } if(defaultContentStyle == null){ this.defaultContentStyle = getDefaultContentStyle(this.workbook); }else{ this.defaultContentStyle = defaultContentStyle; } } /*获取默认标题字体*/ public static HSSFFont getDefaultTitleFont(HSSFWorkbook workbook){ HSSFFont titleFont = workbook.createFont(); titleFont.setColor(HSSFColor.BLACK.index); //字体颜色 titleFont.setFontHeightInPoints((short) 9); //字号 titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体 titleFont.setFontName("微软雅黑"); //微软雅黑 return titleFont; } /*获取默认内容字体*/ public static HSSFFont getDefaultContentFont(HSSFWorkbook workbook){ HSSFFont contentFont = workbook.createFont(); //定义内容字体样式 contentFont.setColor(HSSFColor.BLACK.index); //字体颜色 contentFont.setFontHeightInPoints((short) 9); //字号 contentFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //普通粗细 contentFont.setFontName("微软雅黑"); //微软雅黑 return contentFont; } /*获取默认标题样式*/ public static HSSFCellStyle getDefaultTitleStyle(HSSFWorkbook workbook){ HSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底部边线 titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左部边线 titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右部边线 titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶部边线 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //表头内容水平居中 titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //表头内容垂直居中 titleStyle.setFont(getDefaultTitleFont(workbook)); //设置该字体样式 titleStyle.setWrapText(true); return titleStyle; } /*获取默认内容样式*/ public static HSSFCellStyle getDefaultContentStyle(HSSFWorkbook workbook){ HSSFCellStyle contentStyle = workbook.createCellStyle(); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底部边线 contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左部边线 contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右部边线 contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶部边线 contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //表头内容水平居中 contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //表头内容垂直居中 contentStyle.setFont(getDefaultContentFont(workbook)); //设置该字体样式 contentStyle.setWrapText(true); return contentStyle; } /** * 提示用户下载excel * @param response 响应对象 * @param fileName excel文件名 * create by ronghui.xiao @2015-8-5 */ public static void downloadExcel(HSSFWorkbook workbook , HttpServletResponse response , String fileName){ try{ if(StringUtils.isNotBlank(fileName)){ fileName = new String(fileName.getBytes("gb2312"), "iso8859-1"); //给文件名重新编码 }else{ fileName = "excel"; } response.setContentType("text/html;charset=utf-8"); //设置响应编码 response.setContentType("application/x-msdownload"); //设置为文件下载 response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); //设置响应头信息 OutputStream outputStream = response.getOutputStream(); //创建输出流 workbook.write(outputStream); //把工作薄写进流中 outputStream.close(); }catch (IOException e){ log.error(e); } } /** * 提示用户下载excel * @param response 响应对象 * @param fileName excel文件名 * create by ronghui.xiao @2015-8-5 */ public void downloadExcel(HttpServletResponse response , String fileName){ try{ if(StringUtils.isNotBlank(fileName)){ fileName = new String(fileName.getBytes("gb2312"), "iso8859-1"); //给文件名重新编码 }else{ fileName = "excel"; } response.setContentType("text/html;charset=utf-8"); //设置响应编码 response.setContentType("application/x-msdownload"); //设置为文件下载 response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); //设置响应头信息 OutputStream outputStream = response.getOutputStream(); //创建输出流 workbook.write(outputStream); //把工作薄写进流中 outputStream.close(); }catch (IOException e){ log.error(e); } } /** * 提示用户下载excel * @function 对ie浏览器和firefox进行了兼容，不会出现乱码问题浏 * @author junqiang.qiu * @date 2016年12月8日 */ public void downloadExcel(HttpServletRequest request,HttpServletResponse response,String fileName ){ String agent = request.getHeader("USER-AGENT").toLowerCase(); String codedFileName; try { codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8"); //给文件名重新编码 /*这里对火狐浏览器做了设置*/ if (agent.contains("firefox")) { response.setCharacterEncoding("utf-8"); response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls"); } else { /*其他浏览器*/ response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls"); } response.setContentType("text/html;charset=utf-8"); //设置响应编码 response.setContentType("application/x-msdownload"); //设置为文件下载 OutputStream outputStream = response.getOutputStream(); //创建输出流 workbook.write(outputStream); //把工作薄写进流中 outputStream.close(); } catch (Exception e) { log.error(e); } } /** * @function 创建Excel，在服务器端或者是本地 * @author junqiang.qiu * @date 2017年1月12日 */ public void createExcel(String path,String fileName){ /*这里使用File.separator是Java定义的一个枚举，这样就可以跨平台，对应Windows和linux是不一样的*/ try { String param=null; param=path+File.separator+fileName+".xls"; FileOutputStream fos=new FileOutputStream(param); workbook.write(fos); fos.close(); } catch (Exception e) { log.info("创建excel失败"+e); } } /** * 添加标题行 * @param titles 标题集合 * @param rowHeight 行高 */ public void addTitlesRow(Collection<String> titles , int rowHeight){ try{ if(titles != null && rowHeight > 0){ int rowIndex = 0; HSSFSheet sheet = workbook.getSheet(sheetName); /*总是获取最后一页,第一次则创建sheet页*/ if(sheet == null){ sheet = workbook.createSheet(sheetName); }else{ sheet = workbook.getSheetAt(lastSheetIndex); rowIndex = sheet.getLastRowNum() + 1; } //若当前sheet页超过最大条数65536,则再创建一个sheet页 if(rowIndex > 65535){ lastSheetIndex ++; sheet = workbook.createSheet(sheetName + lastSheetIndex); rowIndex = 0; } sheet.setDefaultColumnWidth(defaultColumnWidth); /*创建标题行*/ HSSFRow row = sheet.createRow(rowIndex); row.setHeightInPoints(rowHeight); Iterator<String> iterator = titles.iterator(); int index = 0; while(iterator.hasNext()){ String title = iterator.next(); HSSFCell cell = row.createCell(index); //给该行创建单元格 cell.setCellValue(title); //给单元格放入标题 cell.setCellStyle(defaultTitleStyle); index ++ ; } } }catch (Exception e){ log.error("添加标题行发生错误==>" , e); } } /** * 添加内容行 * @param titleKeyMap 标题和mapList中key的对应 * @param mapList 内容集合 * @param rowHeight 内容行行高 */ public void addContentRow(Map<String,String> titleKeyMap , List<Map<String,Object>> mapList , int rowHeight ){ try { if(titleKeyMap != null && rowHeight > 0 && mapList != null ){ int rowIndex = 0; HSSFSheet sheet = workbook.getSheet(sheetName); /*总是获取最后一页,第一次则创建sheet页*/ if(sheet == null){ sheet = workbook.createSheet(sheetName); sheet.setDefaultColumnWidth(defaultColumnWidth); }else{ sheet = workbook.getSheetAt(lastSheetIndex); rowIndex = sheet.getLastRowNum() + 1; } /*取出标题*/ List<String> titles = new ArrayList<String>(titleKeyMap.keySet()); /*根据数据的条数来创建表格行*/ for(Map<String,Object> map : mapList){ //若当前sheet页超过最大条数65536,则再创建一个sheet页 if(rowIndex > 65535){ lastSheetIndex ++; sheet = workbook.createSheet(sheetName + lastSheetIndex); sheet.setDefaultColumnWidth(defaultColumnWidth); rowIndex = 0; } HSSFRow row = sheet.createRow(rowIndex++); row.setHeightInPoints(rowHeight); for( int k = 0 ; k < titleKeyMap.size(); k ++){ HSSFCell cell = row.createCell(k); String key = titleKeyMap.get(titles.get(k)); String value = (map.get(key) != null) ? map.get(key).toString() : "" ; cell.setCellValue(value); cell.setCellStyle(defaultContentStyle); } } } }catch (Exception e){ log.error("添加内容行发生错误==>", e); } } /** * 新增横向的标题-值的行 * @param titleValueRow */ public void addTitleValueRow(TitleValueRow titleValueRow){ try { int rowIndex = 0; HSSFSheet sheet = workbook.getSheet(sheetName); /*总是获取最后一页,第一次则创建sheet页*/ if(sheet == null){ sheet = workbook.createSheet(sheetName); }else{ sheet = workbook.getSheetAt(lastSheetIndex); rowIndex = sheet.getLastRowNum() + 1; } //若当前sheet页超过最大条数65536,则再创建一个sheet页 if(rowIndex > 65535){ lastSheetIndex ++; sheet = workbook.createSheet(sheetName + lastSheetIndex); rowIndex = 0; } sheet.setDefaultColumnWidth(defaultColumnWidth); /*创建该行*/ HSSFRow row = sheet.createRow(rowIndex); row.setHeightInPoints(titleValueRow.getRowHeight()); List<TitleValue> titleValueList = titleValueRow.getTitleValueList(); for(int i = 0 ; i < titleValueRow.getCellsSize() ; i ++ ){ row.createCell(i).setCellStyle(defaultContentStyle); } int curCellIndex = 0; for(TitleValue titleValue :titleValueList){ /*给单元格赋值*/ HSSFCell cell = row.getCell(curCellIndex); cell.setCellValue(titleValue.getTitle()); if(titleValue.getTitleCells() > 1){ sheet.addMergedRegion(new CellRangeAddress(rowIndex,rowIndex,curCellIndex,(curCellIndex + titleValue.getTitleCells() -1))); } curCellIndex += titleValue.getTitleCells(); cell.setCellStyle(defaultTitleStyle); if(titleValue.getValueCells() >= 1){ cell = row.getCell(curCellIndex); cell.setCellValue(titleValue.getValue()); } if(titleValue.getValueCells() > 1){ sheet.addMergedRegion(new CellRangeAddress(rowIndex,rowIndex,curCellIndex,curCellIndex + titleValue.getValueCells() -1)); } curCellIndex += titleValue.getValueCells(); } }catch (Exception e){ log.error("新增横向的标题-值的行==>",e); } } /** * 标题和值行的类 */ public static class TitleValueRow{ /*标题和值的集合*/ private List<TitleValue> titleValueList; /*该行所占用的总格子数*/ private int cellsSize = 0; /*该行所占用的高度*/ private float rowHeight = 20; public TitleValueRow(){ this.titleValueList = new ArrayList<TitleValue>(); } public TitleValueRow(List<TitleValue> titleValueList , int rowHeight){ if(titleValueList != null && titleValueList.size() > 0){ this.titleValueList = titleValueList; for(TitleValue titleValue : titleValueList){ cellsSize += titleValue.getTitleCells() + titleValue.getValueCells(); } }else{ this.titleValueList = new ArrayList<TitleValue>(); } if(rowHeight > 0){ this.rowHeight = rowHeight; } } public void addTitleValue(TitleValue titleValue){ titleValueList.add(titleValue); cellsSize ++ ; } public float getRowHeight() { return rowHeight; } public void setRowHeight(float rowHeight) { this.rowHeight = rowHeight; } public List<TitleValue> getTitleValueList() { return titleValueList; } public void setTitleValueList(List<TitleValue> titleValueList) { this.titleValueList = titleValueList; } public int getCellsSize() { return cellsSize; } public void setCellsSize(int cellsSize) { this.cellsSize = cellsSize; } } /** * 横向的标题和行: * 格式为 ==> xxxx标题 : 值 */ public static class TitleValue{ /*标题*/ private String title; /*值*/ private String value; /*标题所占用的格子数*/ private int titleCells = 1; /*内容所占用的格子数*/ private int valueCells = 1; /*构造方法*/ public TitleValue(){ } public TitleValue(String title , String value){ this.title = title; this.value = value; } public TitleValue(String title , String value , int titleCells , int valueCells){ this.title = title; this.value = value; this.titleCells = titleCells; this.valueCells = valueCells; } public TitleValue(String title , String value , int titleCells){ this.title = title; this.value = value; this.titleCells = titleCells; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getValue() { return value; } public void setValue(String value) { this.value = value; } public int getTitleCells() { return titleCells; } public void setTitleCells(int titleCells) { this.titleCells = titleCells; } public int getValueCells() { return valueCells; } public void setValueCells(int valueCells) { this.valueCells = valueCells; } } /** * @function 导出一个具有错误信息的excel * @param fileName 导出文件名 * @param msg 错误信息 * @author ronghui.xiao * @date 2017年3月10日 */ public static void exportErrorMsg(HttpServletResponse response , String fileName , String msg){ ExcelExportUtiler excelExportUtiler = new ExcelExportUtiler("错误信息"); TitleValue titleValue = new TitleValue(msg,"",10,0); TitleValueRow row = new TitleValueRow(Arrays.asList(titleValue) , 50); excelExportUtiler.addTitleValueRow(row); excelExportUtiler.downloadExcel(response, fileName); } /** * @function 传入一个http响应对象,模拟导出一个excel * @param response http响应对象 * @author ronghui.xiao * @date 2015年8月5日 * update at 2017年4月5日 by ronghui.xiao */ public static void testExcelPort(HttpServletResponse response){ //创建一个实例,可以根据需要传入不同的参数,默认sheetName为"sheet" ExcelExportUtiler excelExportUtiler = new ExcelExportUtiler(); Map<String,String> titleKeyMap = new LinkedHashMap<String,String>(); titleKeyMap.put("用户名", "userName"); titleKeyMap.put("密码", "password"); titleKeyMap.put("性别", "sex"); titleKeyMap.put("年龄", "age"); titleKeyMap.put("爱好", "hobby"); titleKeyMap.put("公司", "company"); //模拟10万条数据(本工具类支持任意数量和任意excel版本的导出,超过65536条将会分页) List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>(); for(int i = 0 ; i < 100000 ; i ++){ Map<String,Object> map = new HashMap<String,Object>(); map.put("userName","小明" + i); map.put("age",i); map.put("company","蜀国" + i); map.put("hobby","做作业"); map.put("password","1232323" + i); map.put("sex","男"); mapList.add(map); } /*添加横向的行*/ List<TitleValue> titleValues = new ArrayList<TitleValue>(); titleValues.add(new TitleValue("测试excel工具类","",6,0)); TitleValueRow row = new TitleValueRow(titleValues , 50); excelExportUtiler.addTitleValueRow(row); /*添加标题行*/ excelExportUtiler.addTitlesRow(titleKeyMap.keySet(), 30); /*添加内容行*/ excelExportUtiler.addContentRow(titleKeyMap, mapList, 20); /*添加横向的行*/ List<TitleValue> titleValues1 = new ArrayList<TitleValue>(); titleValues1.add(new TitleValue("末尾也可以添加", "哈哈哈哈", 3, 3)); TitleValueRow row2 = new TitleValueRow(titleValues1 , 50); excelExportUtiler.addTitleValueRow(row2); /*下载*/ excelExportUtiler.downloadExcel(response,"用户信息表"); } public static void ExcelPort(HttpServletResponse response,List<String> keys,List<String> titles,String fileName,List<Map<String, Object>> dataList){ //创建一个实例,可以根据需要传入不同的参数,默认sheetName为"sheet" ExcelExportUtiler excelExportUtiler = new ExcelExportUtiler(); Map<String,String> titleKeyMap = new LinkedHashMap<String,String>(); //一般keys和titles大小一定要相等，或者keys长度>titles长度 for(int i=0;i<titles.size();i++){ titleKeyMap.put(titles.get(i),keys.get(i)); } /*添加横向的行*/ List<TitleValue> titleValues = new ArrayList<TitleValue>(); titleValues.add(new TitleValue(fileName,"",titles.size(),0)); TitleValueRow row = new TitleValueRow(titleValues , 30); excelExportUtiler.addTitleValueRow(row); /*添加标题行*/ excelExportUtiler.addTitlesRow(titleKeyMap.keySet(), 30); /*添加内容行*/ excelExportUtiler.addContentRow(titleKeyMap, dataList, 20); /*添加横向的行*/ List<TitleValue> titleValues1 = new ArrayList<TitleValue>();// titleValues1.add(new TitleValue("末尾也可以添加", "哈哈哈哈", 3, 3)); TitleValueRow row2 = new TitleValueRow(titleValues1 , 50); excelExportUtiler.addTitleValueRow(row2); /*下载*/ excelExportUtiler.downloadExcel(response,fileName); }}阅读全文
0 0
- ExcelExportUtiler excel工具类
- excel EXCEL报表工具类.
- 读取Excel工具类
- Jeecg Excel 工具类
- 解析Excel工具类
- Excel工具类
- java excel工具类
- POI Excel 工具类
- 导出Excel工具类
- Excel工具类 POIExcelUtil
- 读取excel 工具类
- ExcelUtil excel工具类
- excel工具类
- 导出Excel工具类
- Excel导出工具类
- 导出Excel工具类
- excel解析工具类
- Excel工具类
- 软件测试面试历程
- 一维数组与指针的关系
- github上不了
- javascript对URL中的参数进行简单加密处理
- 多媒体 使用MediaRecorder录制音频
- ExcelExportUtiler excel工具类
- Jupyter Notebook常用快捷键
- 设计模式
- iOS开发-nil/Nil/NULL/NSNull 的使用
- C#如何使用分页控件[tabControl]
- 边双联通分量
- 键盘回车事件导致页面刷新的问题
- 查看端口占用
- set的用法
