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对象，样式和格式，还有辅助操作。有以下几种对象：
2.3 基本操作步骤
首先，理解一下一个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应用实例：
详细api请参考：
3：导出表格的工具类：
excelUtil:
1 packagecom.zhl.push.Utils;2
3 importcom.google.common.base.Strings;4 import org.apache.poi.hssf.usermodel.*;5 importorg.apache.poi.hssf.util.HSSFColor;6 importorg.apache.poi.ss.usermodel.VerticalAlignment;7 importorg.apache.poi.ss.util.CellRangeAddress;8
9 importjavax.servlet.http.HttpServletRequest;10 importjavax.servlet.http.HttpServletResponse;11 importjava.io.IOException;12 importjava.io.OutputStream;13 importjava.math.BigDecimal;14 importjava.util.List;15 importjava.util.Map;16
17 public classExcelExportUtil {18 //表头
19 privateString title;20 //各个列的表头
21 privateString[] heardList;22 //各个列的元素key值
23 privateString[] heardKey;24 //需要填充的数据信息
25 private Listdata;26 //字体大小
27 private int fontSize = 14;28 //行高
29 private int rowHeight = 30;30 //列宽
31 private int columWidth = 200;32 //工作表
33 private String sheetName = "sheet1";34
35 publicString getTitle() {36 returntitle;37 }38
39 public voidsetTitle(String title) {40 this.title =title;41 }42
43 publicString[] getHeardList() {44 returnheardList;45 }46
47 public voidsetHeardList(String[] heardList) {48 this.heardList =heardList;49 }50
51 publicString[] getHeardKey() {52 returnheardKey;53 }54
55 public voidsetHeardKey(String[] heardKey) {56 this.heardKey =heardKey;57 }58
59 public ListgetData() {60 returndata;61 }62
63 public void setData(Listdata) {64 this.data =data;65 }66
67 public intgetFontSize() {68 returnfontSize;69 }70
71 public void setFontSize(intfontSize) {72 this.fontSize =fontSize;73 }74
75 public intgetRowHeight() {76 returnrowHeight;77 }78
79 public void setRowHeight(introwHeight) {80 this.rowHeight =rowHeight;81 }82
83 public intgetColumWidth() {84 returncolumWidth;85 }86
87 public void setColumWidth(intcolumWidth) {88 this.columWidth =columWidth;89 }90
91 publicString getSheetName() {92 returnsheetName;93 }94
95 public voidsetSheetName(String sheetName) {96 this.sheetName =sheetName;97 }98
99 /**
100 * 开始导出数据信息101 *102 */
103 public byte[] exportExport(HttpServletRequest request, HttpServletResponse response) throwsIOException {104 //检查参数配置信息
105 checkConfig();106 //创建工作簿
107 HSSFWorkbook wb = newHSSFWorkbook();108 //创建工作表
109 HSSFSheet wbSheet = wb.createSheet(this.sheetName);110 //设置默认行宽
111 wbSheet.setDefaultColumnWidth(20);112
113 //标题样式(加粗，垂直居中)
114 HSSFCellStyle cellStyle =wb.createCellStyle();115 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
116 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
117 HSSFFont fontStyle =wb.createFont();118 fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);119 fontStyle.setBold(true); //加粗
120 fontStyle.setFontHeightInPoints((short)16); //设置标题字体大小
121 cellStyle.setFont(fontStyle);122
123 //在第0行创建rows (表标题)
124 HSSFRow title = wbSheet.createRow((int) 0);125 title.setHeightInPoints(30);//行高
126 HSSFCell cellValue = title.createCell(0);127 cellValue.setCellValue(this.title);128 cellValue.setCellStyle(cellStyle);129 wbSheet.addMergedRegion(new CellRangeAddress(0,0,0,(this.heardList.length-1)));130 //设置表头样式，表头居中
131 HSSFCellStyle style =wb.createCellStyle();132 //设置单元格样式
133 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);134 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);135 //设置字体
136 HSSFFont font =wb.createFont();137 font.setFontHeightInPoints((short) this.fontSize);138 style.setFont(font);139 //在第1行创建rows
140 HSSFRow row = wbSheet.createRow((int) 1);141 //设置列头元素
142 HSSFCell cellHead = null;143 for (int i = 0; i < heardList.length; i++) {144 cellHead =row.createCell(i);145 cellHead.setCellValue(heardList[i]);146 cellHead.setCellStyle(style);147 }148
149 //设置每格数据的样式 (字体红色)
150 HSSFCellStyle cellParamStyle =wb.createCellStyle();151 HSSFFont ParamFontStyle =wb.createFont();152 cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);153 cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);154 ParamFontStyle.setColor(HSSFColor.DARK_RED.index); //设置字体颜色 (红色)
155 ParamFontStyle.setFontHeightInPoints((short) this.fontSize);156 cellParamStyle.setFont(ParamFontStyle);157 //设置每格数据的样式2(字体蓝色)
158 HSSFCellStyle cellParamStyle2 =wb.createCellStyle();159 cellParamStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);160 cellParamStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);161 HSSFFont ParamFontStyle2 =wb.createFont();162 ParamFontStyle2.setColor(HSSFColor.BLUE.index); //设置字体颜色 (蓝色)
163 ParamFontStyle2.setFontHeightInPoints((short) this.fontSize);164 cellParamStyle2.setFont(ParamFontStyle2);165 //开始写入实体数据信息
166 int a = 2;167 for (int i = 0; i < data.size(); i++) {168 HSSFRow roww = wbSheet.createRow((int) a);169 Map map =data.get(i);170 HSSFCell cell = null;171 for (int j = 0; j < heardKey.length; j++) {172 cell =roww.createCell(j);173 cell.setCellStyle(style);174 Object valueObject =map.get(heardKey[j]);175 String value = null;176 if (valueObject == null) {177 valueObject = "";178 }179 if (valueObject instanceofString) {180 //取出的数据是字符串直接赋值
181 value =(String) map.get(heardKey[j]);182 } else if (valueObject instanceofInteger) {183 //取出的数据是Integer
184 value =String.valueOf(((Integer) (valueObject)).floatValue());185 } else if (valueObject instanceofBigDecimal) {186 //取出的数据是BigDecimal
187 value =String.valueOf(((BigDecimal) (valueObject)).floatValue());188 } else{189 value =valueObject.toString();190 }191 //设置单个单元格的字体颜色
192 if(heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")){193 if((Long)map.get("ddNum")!=null){194 if((Long)map.get("sjNum")==null){195 cell.setCellStyle(cellParamStyle);196 } else if((Long) map.get("ddNum") != (Long) map.get("sjNum")){197 if ((Long) map.get("ddNum") > (Long) map.get("sjNum")) {198 cell.setCellStyle(cellParamStyle);199 }200 if ((Long) map.get("ddNum") < (Long) map.get("sjNum")) {201 cell.setCellStyle(cellParamStyle2);202 }203 }else{204 cell.setCellStyle(style);205 }206 }207 }208 cell.setCellValue(Strings.isNullOrEmpty(value) ? "": value);209 }210 a++;211 }212
213 //导出数据
214 try{215 //设置Http响应头告诉浏览器下载这个附件
216 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");217 OutputStream outputStream =response.getOutputStream();218 wb.write(outputStream);219 outputStream.close();220 returnwb.getBytes();221 } catch(Exception ex) {222 ex.printStackTrace();223 throw new IOException("导出Excel出现严重异常，异常信息：" +ex.getMessage());224 }225
226 }227
228 /**
229 * 检查数据配置问题230 *231 *@throwsIOException 抛出数据异常类232 */
233 protected void checkConfig() throwsIOException {234 if (heardKey == null || heardList.length == 0) {235 throw new IOException("列名数组不能为空或者为NULL");236 }237
238 if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {239 throw new IOException("字体、宽度或者高度不能为负值");240 }241
242 if(Strings.isNullOrEmpty(sheetName)) {243 throw new IOException("工作表表名不能为NULL");244 }245 }246 }
service :
@Overridepublic voidqueryProjectInfoBySchemeId(HttpServletResponse response, HttpServletRequest request,
String schemeId, String pushDate)throwsIOException {
List 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 = newExcelExportUtil();
excelExport2.setData(maps);
excelExport2.setHeardKey(properties);
excelExport2.setFontSize(14);
excelExport2.setSheetName(sheetTitle);
excelExport2.setTitle(sheetTitle);
excelExport2.setHeardList(title);
excelExport2.exportExport(request, response);
}
}
1：Excel格式
importjava.io.ByteArrayOutputStream;importjava.io.IOException;importjava.lang.reflect.Field;importjava.util.List;importjava.util.logging.Level;importjava.util.logging.Logger;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.VerticalAlignment;importorg.apache.poi.ss.util.CellRangeAddress;/*** @作者 yan
* @创建日期
* @版本 V1.0
* @描述 Excel 导出通用工具类*/
public classExcelUtil {public static byte[] export(String sheetTitle, String[] title, Listlist) {
HSSFWorkbook wb= new HSSFWorkbook();//创建excel表
HSSFSheet sheet =wb.createSheet(sheetTitle);
sheet.setDefaultColumnWidth(20);//设置默认行宽//表头样式(加粗，水平居中，垂直居中)
HSSFCellStyle cellStyle =wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//设置边框样式
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFFont fontStyle=wb.createFont();
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(fontStyle);//标题样式(加粗，垂直居中)
HSSFCellStyle cellStyle2 =wb.createCellStyle();
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyle2.setFont(fontStyle);//设置边框样式
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框//字段样式(垂直居中)
HSSFCellStyle cellStyle3 =wb.createCellStyle();
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//设置边框样式
cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框//创建表头
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(20);//行高
HSSFCell cell= row.createCell(0);
cell.setCellValue(sheetTitle);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1)));//创建标题
HSSFRow rowTitle = sheet.createRow(1);
rowTitle.setHeightInPoints(20);
HSSFCell hc;for (int i = 0; i < title.length; i++) {
hc=rowTitle.createCell(i);
hc.setCellValue(title[i]);
hc.setCellStyle(cellStyle2);
}byte result[] = null;
ByteArrayOutputStream out= null;try{//创建表格数据
Field[] fields;int i = 2;for(Object obj : list) {
fields=obj.getClass().getDeclaredFields();
HSSFRow rowBody=sheet.createRow(i);
rowBody.setHeightInPoints(20);int j = 0;for(Field f : fields) {
f.setAccessible(true);
Object va=f.get(obj);if (null ==va) {
va= "";
}
hc=rowBody.createCell(j);
hc.setCellValue(va.toString());
hc.setCellStyle(cellStyle3);
j++;
}
i++;
}
out= newByteArrayOutputStream();
wb.write(out);
result=out.toByteArray();
}catch(Exception ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
}finally{try{if(null !=out){
out.close();
}
}catch(IOException ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
}finally{try{
wb.close();
}catch(IOException ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
}returnresult;
}
}
4：依赖包
commons-io-2.4.jar
poi-3.15.jar
=======================================================
如果需要使用模板来导出excel可以使用jxls:
