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应用实例：
<!--导出文件-->
<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++;
}
} } /**
* @return * @param null
* @Author
* @Description //TODO 添加表格标题
* @Date 2019/7/26 17:58
* @Param
*/
public void writeTitle() throws IOException {
checkConfig();
//设置默认行宽
this.sheet.setDefaultColumnWidth(20);
//在第0行创建rows (表标题)
Row title = this.sheet.createRow(0);
title.setHeightInPoints(this.rowHeight);//行高
Cell cell = title.createCell(0);
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));
} /**
* @Author
* @Description //TODO 添加表头
* @Date 2019/7/26 15:41
* @Param headRowNum: 添加表头所在行数
* @return *
*/ 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);
}
}
} /**
* 检查数据配置问题
*
* @throws IOException 抛出数据异常类
*/
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 { /**
* @return * @param null
* @Author
* @Description //TODO 样式居中
* @Date 2019/7/26 14:51
* @Param
*/
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));
} /**
* @return *
* @Author
* @Description //TODO 标题样式 :加粗，垂直居中
* @Date 2019/7/26 14:21
* @Param
*/
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;
} /**
* @return * @param null
* @Author
* @Description //TODO 设置单元格字体
* @Date 2019/7/26 15:16
* @Param
*/
public static Font setFont(Workbook wb, int fontSize, short color) {
//设置字体
Font font = wb.createFont();
font.setColor(color);
font.setFontHeightInPoints((short) fontSize);
return font;
} public static Font setFont(Workbook wb, int fontSize) {
//设置字体
Font font = wb.createFont();
font.setFontHeightInPoints((short) fontSize);
return font;
} /**
* @Author
* @Description //TODO 设置cell边框
* @Date 2019/7/27 16:16
* @Param
* @return * @param null
*/
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;
}
}
=======================================================
如果需要使用模板来导出excel可以使用jxls:
官网：http://jxls.sourceforge.net/
可以参考博客： https://www.cnblogs.com/foxlee1024/p/7616987.html
https://blog.csdn.net/sinat_15769727/article/details/78898894