java实现Excel数据导出详解编程语言
java实现Excel数据导出:
目前,比较常用的实现Java导入、导出Excel的技术有两种Jakarta POI和Java Excel
Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html,API文档http://poi.apache.org/apidocs/index.html
Jakarta POI HSSF API组件HSSF(用于操作Excel的组件)提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象,样式和格式,还有辅助操作。有以下几种对象:
首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(HSSFSheet)组成,一个sheet是由多个row(HSSFRow)组成,一个row是由多个cell(HSSFCell)组成。
基本操作步骤:
下面来看一个动态生成Excel文件的例子:
//创建HSSFWorkbook对象 HSSFWorkbook wb = new HSSFWorkbook(); //创建HSSFSheet对象 HSSFSheet sheet = wb.createSheet("sheet0"); //创建HSSFRow对象 HSSFRow row = sheet.createRow(0); //创建HSSFCell对象 HSSFCell cell=row.createCell(0); //设置单元格的值 cell.setCellValue("单元格中的中文"); //输出Excel文件 FileOutputStream output=new FileOutputStream("d://workbook.xls"); wkb.write(output); output.flush();
HSSF读取文件同样还是使用这几个对象,只是把相应的createXXX方法变成了getXXX方法即可。可见只要理解了其中原理,不管是读还是写亦或是特定格式都可以轻松实现,正所谓知其然更要知其所以然。
2:导出Excel应用实例:
!--导出文件--
dependency groupId org.apache.poi /groupId artifactId poi /artifactId version 3.17 /version /dependency dependency groupId org.apache.poi /groupId artifactId poi-ooxml /artifactId version 3.17 /version /dependency dependency groupId commons-io /groupId artifactId commons-io /artifactId version 2.4 /version /dependency
3:导出表格的工具类:
excelUtil:
package com.zhl.push.Utils; import com.google.common.base.Strings; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.math.BigDecimal; import java.util.List; import java.util.Map; public class ExcelExportUtil { //表头 private String title; //各个列的表头 private String[] heardList; //各个列的元素key值 private String[] heardKey; //需要填充的数据信息 private List Map data; //字体大小 private int fontSize = 14; //行高 private int rowHeight = 30; //列宽 private int columWidth = 200; //工作表 private String sheetName = "sheet1"; public String getTitle() { return title; public void setTitle(String title) { this.title = title; public String[] getHeardList() { return heardList; public void setHeardList(String[] heardList) { this.heardList = heardList; public String[] getHeardKey() { return heardKey; public void setHeardKey(String[] heardKey) { this.heardKey = heardKey; public List Map getData() { return data; public void setData(List Map data) { this.data = data; public int getFontSize() { return fontSize; public void setFontSize(int fontSize) { this.fontSize = fontSize; public int getRowHeight() { return rowHeight; public void setRowHeight(int rowHeight) { this.rowHeight = rowHeight; public int getColumWidth() { return columWidth; public void setColumWidth(int columWidth) { this.columWidth = columWidth; public String getSheetName() { return sheetName; public void setSheetName(String sheetName) { this.sheetName = sheetName; /** * 开始导出数据信息 public byte[] exportExport(HttpServletRequest request, HttpServletResponse response) throws IOException { //检查参数配置信息 checkConfig(); //创建工作簿 HSSFWorkbook wb = new HSSFWorkbook(); //创建工作表 HSSFSheet wbSheet = wb.createSheet(this.sheetName); //设置默认行宽 wbSheet.setDefaultColumnWidth(20); // 标题样式(加粗,垂直居中) HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 HSSFFont fontStyle = wb.createFont(); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontStyle.setBold(true); //加粗 fontStyle.setFontHeightInPoints((short)16); //设置标题字体大小 cellStyle.setFont(fontStyle); //在第0行创建rows (表标题) HSSFRow title = wbSheet.createRow((int) 0); title.setHeightInPoints(30);//行高 HSSFCell cellValue = title.createCell(0); cellValue.setCellValue(this.title); cellValue.setCellStyle(cellStyle); wbSheet.addMergedRegion(new CellRangeAddress(0,0,0,(this.heardList.length-1))); //设置表头样式,表头居中 HSSFCellStyle style = wb.createCellStyle(); //设置单元格样式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置字体 HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) this.fontSize); style.setFont(font); //在第1行创建rows HSSFRow row = wbSheet.createRow((int) 1); //设置列头元素 HSSFCell cellHead = null; for (int i = 0; i heardList.length; i++) { cellHead = row.createCell(i); cellHead.setCellValue(heardList[i]); cellHead.setCellStyle(style); //设置每格数据的样式 (字体红色) HSSFCellStyle cellParamStyle = wb.createCellStyle(); HSSFFont ParamFontStyle = wb.createFont(); cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); ParamFontStyle.setColor(HSSFColor.DARK_RED.index); //设置字体颜色 (红色) ParamFontStyle.setFontHeightInPoints((short) this.fontSize); cellParamStyle.setFont(ParamFontStyle); //设置每格数据的样式2(字体蓝色) HSSFCellStyle cellParamStyle2 = wb.createCellStyle(); cellParamStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellParamStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont ParamFontStyle2 = wb.createFont(); ParamFontStyle2.setColor(HSSFColor.BLUE.index); //设置字体颜色 (蓝色) ParamFontStyle2.setFontHeightInPoints((short) this.fontSize); cellParamStyle2.setFont(ParamFontStyle2); //开始写入实体数据信息 int a = 2; for (int i = 0; i data.size(); i++) { HSSFRow roww = wbSheet.createRow((int) a); Map map = data.get(i); HSSFCell cell = null; for (int j = 0; j heardKey.length; j++) { cell = roww.createCell(j); cell.setCellStyle(style); Object valueObject = map.get(heardKey[j]); String value = null; if (valueObject == null) { valueObject = ""; if (valueObject instanceof String) { //取出的数据是字符串直接赋值 value = (String) map.get(heardKey[j]); } else if (valueObject instanceof Integer) { //取出的数据是Integer value = String.valueOf(((Integer) (valueObject)).floatValue()); } else if (valueObject instanceof BigDecimal) { //取出的数据是BigDecimal value = String.valueOf(((BigDecimal) (valueObject)).floatValue()); } else { value = valueObject.toString(); //设置单个单元格的字体颜色 if(heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")){ if((Long)map.get("ddNum")!=null){ if((Long)map.get("sjNum")==null){ cell.setCellStyle(cellParamStyle); } else if((Long) map.get("ddNum") != (Long) map.get("sjNum")){ if ((Long) map.get("ddNum") (Long) map.get("sjNum")) { cell.setCellStyle(cellParamStyle); if ((Long) map.get("ddNum") (Long) map.get("sjNum")) { cell.setCellStyle(cellParamStyle2); }else { cell.setCellStyle(style); cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value); a++; //导出数据 try { //设置Http响应头告诉浏览器下载这个附件 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); OutputStream outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.close(); return wb.getBytes(); } catch (Exception ex) { ex.printStackTrace(); throw new IOException("导出Excel出现严重异常,异常信息:" + ex.getMessage()); /** * 检查数据配置问题 * @throws IOException 抛出数据异常类 protected void checkConfig() throws IOException { if (heardKey == null || heardList.length == 0) { throw new IOException("列名数组不能为空或者为NULL"); if (fontSize 0 || rowHeight 0 || columWidth 0) { throw new IOException("字体、宽度或者高度不能为负值"); if (Strings.isNullOrEmpty(sheetName)) { throw new IOException("工作表表名不能为NULL"); }
service :
@Override public void queryProjectInfoBySchemeId(HttpServletResponse response, HttpServletRequest request, String schemeId, String pushDate) throws IOException { List Map maps = pushMonitorDao.queryProjectInfoBySchemeId(schemeId, pushDate); if(maps!=null maps.size() 0){ String companyName = pushMonitorDao.queryCompanyNameBySchemeId(schemeId); String sheetTitle = companyName; String [] title = new String[]{"城市","项目名字","合同","实际"}; //设置表格表头字段 String [] properties = new String[]{"city","projectName","ddNum","sjNum"}; // 查询对应的字段 ExcelExportUtil excelExport2 = new ExcelExportUtil(); excelExport2.setData(maps); excelExport2.setHeardKey(properties); excelExport2.setFontSize(14); excelExport2.setSheetName(sheetTitle); excelExport2.setTitle(sheetTitle); excelExport2.setHeardList(title); excelExport2.exportExport(request, response); }
由于上面的代码不够灵活,下面加以改进:
ExcelExportUtil:
package com.zhl.push.Utils; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.util.List; import java.util.Map; public class ExcelExportUtil { //excel表对象 private Workbook workbook; //工作表对象 private Sheet sheet; //标题 private String title; //sheet各个列的表头 private String[] headList; //各个列的元素key值 private String[] headKey; //sheet需要填充的数据信息 private List Map data; //工作表名称 // private String sheetName = "sheet1"; //工作表列宽 private Integer columnWidth=20; //工作表行高 private Integer rowHeight=10; //字体大小 private int fontSize = 14; public int getFontSize() { return fontSize; public void setFontSize(int fontSize) { this.fontSize = fontSize; public Integer getColumnWidth() { return columnWidth; public void setColumnWidth(Integer columnWidth) { this.columnWidth = columnWidth; public Integer getRowHeight() { return rowHeight; public void setRowHeight(Integer rowHeight) { this.rowHeight = rowHeight; public Workbook getWorkbook() { return workbook; public void setWorkbook(Workbook workbook) { this.workbook = workbook; public Sheet getSheet() { return sheet; public void setSheet(Sheet sheet) { this.sheet = sheet; public String[] getHeadKey() { return headKey; public void setHeadKey(String[] headKey) { this.headKey = headKey; public String getTitle() { return title; public void setTitle(String title) { this.title = title; public String[] getHeadList() { return headList; public void setHeadList(String[] headList) { this.headList = headList; public List Map getData() { return data; public void setData(List Map data) { this.data = data; /** * @return * * @Author * @Description //TODO 插入数据到表格(body) * @Date 2019/7/26 17:28 * @Param startRow: 开始插入数据的行 public void writeMainData(Integer startRow) throws IOException { //设置每格数据的样式 (字体红色) CellStyle fontRed = ExcelStyleUtilFor2003.getFontStyle(this.workbook, this.fontSize, HSSFColor.RED.index); //设置每格数据的样式2(字体蓝色) CellStyle fontBlue = ExcelStyleUtilFor2003.getFontStyle(this.workbook, this.fontSize, HSSFColor.BLUE.index); //设置body样式 CellStyle bodyStyle = ExcelStyleUtilFor2003.getBodyStyle(this.workbook, this.fontSize); //开始写入实体数据信息 if (data.size() 0 null != data.get(0)) { for (int i = 0; i data.size(); i++) { Row row = this.sheet.createRow(startRow); Map map = data.get(i); map.put("index",i); map.put("des","无"); for (int j = 0; j headKey.length; j++) { Cell cell = row.createCell(j); //设置单个单元格的字体颜色 if (headKey[j].equals("ddNum") || headKey[j].equals("sjNum")) { if (null != map.get("ddNum") Integer.parseInt(map.get("ddNum").toString()) != 0) { if (null == map.get("sjNum") || Integer.parseInt(map.get("sjNum").toString()) == 0) { cell.setCellStyle(fontRed); } else if (Integer.parseInt(map.get("ddNum").toString()) != Integer.parseInt(map.get("sjNum").toString())) { if (Integer.parseInt((String) map.get("ddNum")) Integer.parseInt((String) map.get("sjNum"))) { cell.setCellStyle(fontRed); } else { cell.setCellStyle(fontBlue); } else { cell.setCellStyle(bodyStyle); } else { if (Integer.parseInt(map.get("ddNum").toString()) Integer.parseInt(map.get("sjNum").toString())) { cell.setCellStyle(fontBlue); } else { cell.setCellStyle(bodyStyle); cell.setCellValue(Integer.parseInt(map.get(headKey[j]).toString())); } else { Object value = map.get(headKey[j]); if (null == value) { String valueN = ""; cell.setCellValue(valueN); } else if (value instanceof Integer) { Integer valueInt = Integer.valueOf(value.toString()); cell.setCellValue(valueInt); } else if (value instanceof String) { String valueStr = String.valueOf(value); cell.setCellValue(valueStr); startRow++;
cell.setCellValue(this.title); CellStyle cellStyle = ExcelStyleUtilFor2003.getTitleStyle(this.workbook,true,16); cell.setCellStyle(cellStyle); ExcelStyleUtilFor2003.mergeCell(sheet,0,0,0, (this.headList.length - 1));
public void writeTableHead(String[] head, CellStyle cellStyle,Integer headRowNum) { Row row = this.sheet.createRow(headRowNum); if (head.length 0) { for (int i = 0; i head.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(head[i]); cell.setCellStyle(cellStyle);
protected void checkConfig() throws IOException { if (headKey == null || headList.length == 0) { throw new IOException("列名数组不能为空或者为NULL"); if (fontSize 0) { throw new IOException("字体不能为负值"); /** * @Author * @Description //TODO 写入拼接好的数据,不需要通过表头key值来对应 * @Date 2019/7/27 11:01 * @Param * @return * @param null public void writeMainData(List List String datas,Integer startRow){ if(datas.size() 0){ for(List String data : datas){ Row row = this.sheet.createRow(startRow); for(int i =0; i data.size(); i++){ Cell cell = row.createCell(i); cell.setCellValue(data.get(i)); CellStyle cellStyle = ExcelStyleUtilFor2003.setCellBorder(this.workbook); cell.setCellStyle(cellStyle); startRow++; public static void setResponseInfo(HttpServletResponse response, Workbook wb) throws IOException { //导出数据 try { //设置Http响应头告诉浏览器下载这个附件 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); OutputStream outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.close(); } catch (Exception ex) { ex.printStackTrace(); throw new IOException("导出Excel出现严重异常,异常信息:" + ex.getMessage()); }
我们把样式单独提取成一个工具类:
package com.zhl.push.Utils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; * @Author * @ClassName ExcelStyleUtilFor2003 * @Description TODO * @Date 2019/7/26 14:16 * @Version 1.0 public class ExcelStyleUtilFor2003 {
public static void center(CellStyle cellStyle) { cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 /** * @return * @param null * @Author * @Description //TODO 单元格合并 * @Date 2019/7/26 14:54 * @Param wbSheet :工作表对象 * firstRow :合并的开始行 * lastRow:合并的结束行 * firstCol: 合并的开始列 * lastColL: 合并的结束列 public static void mergeCell(Sheet wbSheet, int firstRow, int lastRow, int firstCol, int lastCol) { wbSheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
public static CellStyle getTitleStyle(Workbook wb, Boolean isBold, int FontISize) { // 标题样式(加粗,垂直居中) CellStyle cellStyle = wb.createCellStyle(); center(cellStyle); Font font = wb.createFont(); font.setBold(isBold); //加粗 font.setFontHeightInPoints((short) FontISize); //设置标题字体大小 cellStyle.setFont(font); return cellStyle; /** * @return * * @Author * @Description //TODO 表头样式 * @Date 2019/7/26 14:30 * @Param public static CellStyle getHeadStyle(Workbook wb, int fontSize) { CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.AQUA.index);//设置表头的背景颜色 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index); //设置表头的背景颜色 center(cellStyle); //设置字体 Font font = setFont(wb, fontSize); cellStyle.setFont(font); return cellStyle; /** * @return * @param null * @Author * @Description //TODO body通用样式: 居中,设置字体大小 * @Date 2019/7/26 15:11 * @Param public static CellStyle getBodyStyle(Workbook wb, int fontSize) { CellStyle cellStyle = wb.createCellStyle(); //设置单元格样式 center(cellStyle); Font font = setFont(wb, fontSize); cellStyle.setFont(font); return cellStyle; /** * @return * @param null * @Author * @Description //TODO 设置单元格字体居中、并设置字体颜色 * @Date 2019/7/26 15:26 * @Param public static CellStyle getFontStyle(Workbook wb, int fontSize, short color) { CellStyle cellStyle = wb.createCellStyle(); Font font = setFont(wb, fontSize, color); center(cellStyle); cellStyle.setFont(font); return cellStyle;
public static CellStyle setCellBorder(Workbook workbook){ CellStyle cellStyle = workbook.createCellStyle(); //设置了边框属性 cellStyle.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle.setBorderLeft(BorderStyle.THIN);//左边框 cellStyle.setBorderTop(BorderStyle.THIN);//上边框 cellStyle.setBorderRight(BorderStyle.THIN);//右边框 //设置边框颜色黑色 cellStyle.setTopBorderColor(HSSFColor.BLACK.index); cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); cellStyle.setRightBorderColor(HSSFColor.BLACK.index); return cellStyle; }
原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/17059.html
cgojavaxml相关文章
- 将Excel转换为word_excel如何将横排变成竖排
- 导出的Excel名字乱码_恢复的excel文件乱码
- java random函数原理_详解JAVA中Random()函数的用法
- java网页安全提示_win7系统打开网页提示“应用程序已被JAVA安全阻止”的解决方法…
- java导出excel(一):单sheet
- Java 导出Excel增加下拉框选项
- java缓存技术的介绍
- JAVA 大数据Excel POI生成导出
- idea创建java项目的步骤_Java为什么新建不了项目
- Java中注解学习系列教程-4 使用自定义注解实现excel导出
- Java中注解学习系列教程-6 excel导出优化plush版
- java poi导出带有多个sheet页的excel详解编程语言
- Java 使用 Jxl 实现 Excel 导入导出详解编程语言
- java的poi技术读取Excel[2003-2007,2010]详解编程语言
- java:POI导出excel详解编程语言
- 报表 MySQL导出Excel数据报表的指南(mysql导出excel)
- Java导出excel并下载功能详解编程语言
- SQL Server数据导出到Excel的方法(sqlserver导出到excel)
- Java调用Redis实现高性能数据存储(java调用redis)
- Linux环境中如何顺利执行Java程序?(linux下执行java)
- Java技术封装Redis,打造高效数据处理系统(java封装redis)
- 使用Java远程控制Linux 实现简单、高效的服务器管理(java控制linux)
- Java实现Linux:跨平台解决方案(java 实现linux)
- Java Redis一次实现无限数据加速的尝试(用java redis)
- Oracle与Java程序融合谱写成功曲(java程序Oracle)