这一段时间，由于项目上线基于稳定，所以我这边在基于我们一期迭代的分支上优化一部分我们之前没有做的功能，报表导出。本身之前用的是3.5的版本，但是由于同事要写导入，写的代码只有4.1.0的版本支持，所以无奈之下，只能自己看源码把之前的工具类重写一波。下面我们来看一下实现步骤。
1.导入jar
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.</version>
</dependency>
2.编辑工具类
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress; public class ExportExcelUtil { /**
* 创建表格标题
*
* @param wb
* Excel文档对象
* @param sheet
* 工作表对象
* @param headString
* 标题名称
* @param col
* 标题占用列数
*/
public static void createHeadTittle(HSSFWorkbook wb, HSSFSheet sheet, String headString, int col) {
HSSFRow row = sheet.createRow(); // 创建Excel工作表的行
HSSFCell cell = row.createCell(); // 创建Excel工作表指定行的单元格
row.setHeight((short) ); // 设置高度 cell.setCellType(CellType.STRING); // 定义单元格为字符串类型
cell.setCellValue(new HSSFRichTextString(headString)); sheet.addMergedRegion(new CellRangeAddress(, , , col)); // 指定标题合并区域 // 定义单元格格式，添加单元格表样式，并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 指定单元格水平居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 指定单元格垂直居中个对齐
cellStyle.setWrapText(true); // 指定单元格自动换行 // 设置单元格字体
HSSFFont font = wb.createFont();
font.setBold(true);//设置字体为粗体
font.setFontName("微软雅黑");
font.setColor(HSSFColorPredefined.BLACK.getIndex());
font.setFontHeightInPoints((short) ); // 字体大小 cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
} /**
* 创建表头
*
* @param wb
* Excel文档对象
* @param sheet
* 工作表对象
* @param thead
* 表头内容
* @param sheetWidth
* 每一列宽度
*/
public static void createThead(HSSFWorkbook wb, HSSFSheet sheet, String[] thead, int[] sheetWidth) {
HSSFRow row1 = sheet.createRow();
row1.setHeight((short) );
// 定义单元格格式，添加单元格表样式，并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中对齐
cellStyle.setWrapText(true);
//设置背景色灰色25%
cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_25_PERCENT.getIndex()); // 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderRight(BorderStyle.THIN); // 设置右边框类型
cellStyle.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置右边框颜色 // 设置单元格字体
HSSFFont font = wb.createFont();
font.setBold(true);//设置字体为粗体
font.setFontName("宋体");
font.setFontHeightInPoints((short) );
cellStyle.setFont(font); // 设置表头内容
for (int i = ; i < thead.length; i++) {
HSSFCell cell1 = row1.createCell(i);
cell1.setCellType(CellType.STRING);//定义单元格为字符串类型
cell1.setCellValue(new HSSFRichTextString(thead[i]));
cell1.setCellStyle(cellStyle);
} // 设置每一列宽度
for (int i = ; i < sheetWidth.length; i++) {
sheet.setColumnWidth(i, sheetWidth[i]);
}
} /**
* 填入数据
*
* @param wb
* // Excel文档对象
* @param sheet
* // 工作表对象
* @param result
* // 表数据
*/
public static void createTable(HSSFWorkbook wb, HSSFSheet sheet, List<LinkedHashMap<String, String>> result) {
// 定义单元格格式，添加单元格表样式，并添加到工作薄
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true); // 单元格字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) );
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中 // 循环插入数据
for (int i = ; i < result.size(); i++) {
HSSFRow row = sheet.createRow(i + );
row.setHeight((short) ); // 设置高度
HSSFCell cell = null;
int j = ;
for (String key : (result.get(i).keySet())) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(result.get(i).get(key)));
j++;
}
}
} }
低版本工具类
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress; public class ExportExcelUtil3 { /**
* 创建表格标题
*
* @param wb
* Excel文档对象
* @param sheet
* 工作表对象
* @param headString
* 标题名称
* @param col
* 标题占用列数
*/
@SuppressWarnings("deprecation")
public static void createHeadTittle(HSSFWorkbook wb, HSSFSheet sheet, String headString, int col) {
HSSFRow row = sheet.createRow(0); // 创建Excel工作表的行
HSSFCell cell = row.createCell(0); // 创建Excel工作表指定行的单元格
row.setHeight((short) 1000); // 设置高度 cell.setCellType(HSSFCell.ENCODING_UTF_16); // 定义单元格为字符串类型
cell.setCellValue(new HSSFRichTextString(headString)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col)); // 指定标题合并区域 // 定义单元格格式，添加单元格表样式，并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中个对齐
cellStyle.setWrapText(true); // 指定单元格自动换行 // 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 16); // 字体大小 cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
} /**
* 创建表头
*
* @param wb
* Excel文档对象
* @param sheet
* 工作表对象
* @param thead
* 表头内容
* @param sheetWidth
* 每一列宽度
*/
@SuppressWarnings("deprecation")
public static void createThead(HSSFWorkbook wb, HSSFSheet sheet, String[] thead, int[] sheetWidth) {
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 600);
// 定义单元格格式，添加单元格表样式，并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框类型
cellStyle.setRightBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 // 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font); // 设置表头内容
for (int i = 0; i < thead.length; i++) {
HSSFCell cell1 = row1.createCell(i);
cell1.setCellType(HSSFCell.ENCODING_UTF_16);
cell1.setCellValue(new HSSFRichTextString(thead[i]));
cell1.setCellStyle(cellStyle);
} // 设置每一列宽度
for (int i = 0; i < sheetWidth.length; i++) {
sheet.setColumnWidth(i, sheetWidth[i]);
}
} /**
* 填入数据
*
* @param wb
* // Excel文档对象
* @param sheet
* // 工作表对象
* @param result
* // 表数据
*/
@SuppressWarnings("deprecation")
public static void createTable(HSSFWorkbook wb, HSSFSheet sheet, List<LinkedHashMap<String, String>> result) {
// 定义单元格格式，添加单元格表样式，并添加到工作薄
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true); // 单元格字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 循环插入数据
for (int i = 0; i < result.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
row.setHeight((short) 400); // 设置高度
HSSFCell cell = null;
int j = 0;
for (String key : (result.get(i).keySet())) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(result.get(i).get(key)));
j++;
}
}
} public static void main(String[] args) {
//测试hashmap treemap linkedhashmap之间的顺序
/*Map<String, String> map=new HashMap<>();
System.out.println("hashmap排序");
add_keyvalue(map);
TreeMap<String, String> map2=new TreeMap<>();
System.out.println("treemap排序");
add_keyvalue(map2);
LinkedHashMap<String, String> map3=new LinkedHashMap<>();
System.out.println("linkedhash排序");
add_keyvalue(map3);*/ // 1.封装数据
List<ExportExcelView> list = new LinkedList<>();
ExportExcelView b1 = new ExportExcelView();
b1.setDeclsno("201810251706470169854601");
b1.setDecdt("2018-09-22");
b1.setEleacno("1209394999");
b1.setCustName("张三");
b1.setEntName("正信广电");
b1.setSaleName("郭启铭");
b1.setSaleTel("17342064227");
b1.setRealsumretbal("1000");
b1.setDecutionFee("100"); ExportExcelView b2 = new ExportExcelView();
b2.setDeclsno("201810251706470176052618");
b2.setDecdt("2018-09-22");
b2.setEleacno("1209394999");
b2.setCustName("赵四");
b2.setEntName("正信广电");
b2.setSaleName("郭启铭");
b2.setSaleTel("17342064227");
b2.setRealsumretbal("2000");
b2.setDecutionFee("200");
list.add(b1);
list.add(b2); // 实体类转换为map
List<LinkedHashMap<String, String>> result = new ArrayList<>();
LinkedHashMap<String, String> map = new LinkedHashMap<>();
for (ExportExcelView e : list) {
map.put("declsno", e.getDeclsno());
map.put("decdt", e.getDecdt());
map.put("eleacno", e.getEleacno());
map.put("custName",e.getCustName());
map.put("entName",e.getEntName());
map.put("saleName",e.getSaleName());
map.put("saleTel",e.getSaleTel());
map.put("realsumretbal",e.getRealsumretbal());
map.put("decutionFee",e.getDecutionFee());
result.add(map);
} // 2.定义变量值 创建Excel文件
String fileName = "正信广电_201809代扣费用表.xls"; // 定义文件名
String headString = "正信广电_201809代扣费用表"; // 定义表格标题
String sheetName = "正信广电_201809代扣费用表"; // 定义工作表表名
String filePath = "D:\\"; // 文件本地保存路径
String[] thead = { "扣款流水", "扣款日期", "发电户号", "用户姓名", "开发商",
"业务员姓名","业务员手机号","扣款金额(元)", "代扣费用(元)" };
int[] sheetWidth = { 7500, 4000, 3000, 3000, 4000, 3000, 5000, 5000,5000}; // 定义每一列宽度 HSSFWorkbook wb = new HSSFWorkbook(); // 创建Excel文档对象
HSSFSheet sheet = wb.createSheet(sheetName); // 创建工作表 // 3.生成表格
// ①创建表格标题
createHeadTittle(wb, sheet, headString, 8);
// result.get(0).size() - 1为表格占用列数，从0开始
// ②创建表头
createThead(wb, sheet, thead, sheetWidth);
// ③填入数据
createTable(wb, sheet, result); FileOutputStream fos;
try {
fos = new FileOutputStream(new File(filePath + fileName));
wb.write(fos);
fos.close();
wb.close();
System.out.println("导出excel成功");
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} } public static void add_keyvalue(Map<String, String> map){
map.put("351", "11");
map.put("512", "222");
map.put("853", "333");
map.put("125", "333");
map.put("341", "333");
Iterator<String> iterator=map.keySet().iterator();
while(iterator.hasNext()){
System.out.println(iterator.next());
}
} }
3.测试导出
①.导出对象
import lombok.Data; @Data
public class ExportExcelView {
private String declsno;
private String decdt;
private String eleacno;
private String custName;
private String entName;
private String saleName;
private String saleTel;
private String realsumretbal;
private String decutionFee;
}
②.写入报表
public static void main(String[] args) {
// 1.封装数据
List<ExportExcelView> list = new LinkedList<>();
ExportExcelView b1 = new ExportExcelView();
b1.setDeclsno("201810251706470169854601");
b1.setDecdt("2018-09-22");
b1.setEleacno("1209394999");
b1.setCustName("张三");
b1.setEntName("正信广电");
b1.setSaleName("郭启铭");
b1.setSaleTel("17342064227");
b1.setRealsumretbal("1000");
b1.setDecutionFee("100");
ExportExcelView b2 = new ExportExcelView();
b2.setDeclsno("201810251706470176052618");
b2.setDecdt("2018-09-22");
b2.setEleacno("1209394999");
b2.setCustName("赵四");
b2.setEntName("正信广电");
b2.setSaleName("郭启铭");
b2.setSaleTel("17342064227");
b2.setRealsumretbal("2000");
b2.setDecutionFee("200");
list.add(b1);
list.add(b2);
// 实体类转换为map
List<LinkedHashMap<String, String>> result = new ArrayList<>();
LinkedHashMap<String, String> map = new LinkedHashMap<>();
for (ExportExcelView e : list) {
map.put("declsno", e.getDeclsno());
map.put("decdt", e.getDecdt());
map.put("eleacno", e.getEleacno());
map.put("custName",e.getCustName());
map.put("entName",e.getEntName());
map.put("saleName",e.getSaleName());
map.put("saleTel",e.getSaleTel());
map.put("realsumretbal",e.getRealsumretbal());
map.put("decutionFee",e.getDecutionFee());
result.add(map);
}
// 2.定义变量值 创建Excel文件
String fileName = "正信广电_201809代扣费用表.xls"; // 定义文件名
String headString = "正信广电_201809代扣费用表"; // 定义表格标题
String sheetName = "正信广电_201809代扣费用表"; // 定义工作表表名
String filePath = "D:\\"; // 文件本地保存路径
String[] thead = { "扣款流水", "扣款日期", "发电户号", "用户姓名", "开发商",
"业务员姓名","业务员手机号","扣款金额(元)", "代扣费用(元)" };
int[] sheetWidth = { 7500, 4000, 3000, 3000, 4000, 3000, 5000, 5000,5000}; // 定义每一列宽度
HSSFWorkbook wb = new HSSFWorkbook(); // 创建Excel文档对象
HSSFSheet sheet = wb.createSheet(sheetName); // 创建工作表
// 3.生成表格
// ①创建表格标题
createHeadTittle(wb, sheet, headString, 8);
// ②创建表头
createThead(wb, sheet, thead, sheetWidth);
// ③填入数据
createTable(wb, sheet, result);
FileOutputStream fos;
try {
fos = new FileOutputStream(new File(filePath + fileName));
wb.write(fos);
fos.close();
System.out.println("导出excel成功");
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}
}
③.运行效果
④.导出并下载
/**
* 输出创建的Excel
* @param fileName
* @param wb
* @param resp
*/
private void respOutPutExcel(String fileName, HSSFWorkbook wb, HttpServletResponse resp) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
wb.write(os);
System.out.println("导出excel成功");
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
resp.reset();
resp.setContentType("application/vnd.ms-excel;charset=utf-8");
resp.setHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = resp.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}finally {
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (IOException e) {
e.printStackTrace();
} } }
页面写法
/**
* 导出报表
* @returns
*/
function exportExcel(){
//这里获得一些要前台查询的数据
这里不能用ajax的方式，用ajax方式请求，会直接输出流
location.href= prefix +"/listOutPutExcel; }
<button id="outPutExcel" type="button" class="btn btn-primary" onclick="exportExcel()">导出</button>
