在Java中封装导出Excel数据的总体思路为:
1.导出，根据查询得到的数据（一般我们页面上用的是查询条件带分页的），我们需要把查询条件带到后台重新查询，并且是查询全部数据，不带分页
2.不能用ajax异步提交。应该用location.href=""的方式，把处理的流交给浏览器来处理，JS无法处理流计算。
3.封装统一的Bean实体，主要输入导出的列名，excel名，数据集即可导出。
用到的Maven地址：
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6</version>
</dependency>
ExcelBean：
public class ExcelBean {
private String name;
private String sheetName;
private ExcelTitle[] titles;
private List<String[]> dataList;
private boolean headBold = true;
/**
* 列宽 （像素）
*/
private int columnWidth = 200;
private int rowHeight;
public ExcelBean(String name, String sheetName, ExcelTitle[] titles){
this.name = name;
this.sheetName = sheetName;
this.titles = titles;
this.dataList = new ArrayList<String[]>();
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public ExcelTitle[] getTitles() {
return titles;
}
public void setTitles(ExcelTitle[] titles) {
this.titles = titles;
}
public List<String[]> getDataList() {
return dataList;
}
public void setDataList(List<String[]> dataList) {
this.dataList = dataList;
}
public boolean isHeadBold() {
return headBold;
}
public void setHeadBold(boolean headBold) {
this.headBold = headBold;
}
public int getColumnWidth() {
return columnWidth;
}
public void setColumnWidth(int columnWidth) {
this.columnWidth = columnWidth;
}
public int getRowHeight() {
return rowHeight;
}
public void setRowHeight(int rowHeight) {
this.rowHeight = rowHeight;
}
public void add(String[] data){
this.dataList.add(data);
}
}
ExcelBox：
/**
* excel 块状区域
* @author
*
*/
public class ExcelBox {
private int x1;
private int y1;
private int x2;
private int y2;
public ExcelBox(int width, int height, int colWidth, int rowHeight, int padding){
double ratio = 1;
int innerWidth = colWidth - 2 * padding;
int innerHeight = rowHeight - 2 * padding;
if((double) width / height > (double) innerWidth/ innerHeight){
if(width > innerWidth){
ratio = (double) innerWidth / width;
}
}
else{
if(height > innerHeight){
ratio = (double) innerHeight / height;
}
}
int boxWidth = (int)(width * ratio);
int boxHeight = (int)(height * ratio);
x1 = (colWidth - boxWidth) / 2;
y1 = (rowHeight - boxHeight) / 2;
x2 = x1 + boxWidth;
y2 = y1+ boxHeight;
}
public int getX1() {
return x1;
}
public void setX1(int x1) {
this.x1 = x1;
}
public int getY1() {
return y1;
}
public void setY1(int y1) {
this.y1 = y1;
}
public int getX2() {
return x2;
}
public void setX2(int x2) {
this.x2 = x2;
}
public int getY2() {
return y2;
}
public void setY2(int y2) {
this.y2 = y2;
}
}
ExcelImage：
/**
* excel 图片
* @
*
*/
public class ExcelImage {
private int width;
private int height;
private byte[] byteArray;
public ExcelImage(String imageUrl) throws IOException{
BufferedImage bufferedImage = ImageIO.read(new URL(imageUrl));
ByteArrayOutputStream byteArrayOutputStream =new ByteArrayOutputStream();
ImageIO.write(bufferedImage,"png", byteArrayOutputStream);
width = bufferedImage.getWidth();
height = bufferedImage.getHeight();
byteArray = byteArrayOutputStream.toByteArray();
byteArrayOutputStream.close();
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
public byte[] getByteArray() {
return byteArray;
}
public void setByteArray(byte[] byteArray) {
this.byteArray = byteArray;
}
}
ExcelTitle：
/**
* Excel Title
* @author
*
*/
public class ExcelTitle {
private String value;
/**
* 列宽（像素）
*/
private int width;
public static ExcelTitle generate(String value, int width){
ExcelTitle title = new ExcelTitle();
title.setValue(value);
title.setWidth(width);
return title;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
}
工具类 ExcelUtils：
package com.goldensky.common.excelutil;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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;
public class excelExport {
/*@SuppressWarnings("resource")
public static void export( ExcelBean excelBean, HttpServletResponse response) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
HSSFRow row = sheet.createRow(0);
//设置样式
HSSFCellStyle style = wb.createCellStyle();
if(excelBean.isHeadBold()){
HSSFFont headfont = wb.createFont();
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(headfont);
}
HSSFCell cell;
ExcelTitle[] titles = excelBean.getTitles();
for(int i=0; i < titles.length; i++){
ExcelTitle title = titles[i];
cell= row.createCell(i);
cell.setCellValue(title.getValue());
cell.setCellStyle(style);
int columnWidth = title.getWidth() > 0 ? title.getWidth() : excelBean.getColumnWidth();
sheet.setColumnWidth(i, getColWidth(columnWidth));
}
int rowNumber = 1;
int rowHeihgt = excelBean.getRowHeight();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
for(String[] data : excelBean.getDataList()){
row = sheet.createRow(rowNumber ++ );
if(rowHeihgt > 0){
row.setHeight((short) getRowHeight(rowHeihgt));
}
else{
rowHeihgt = 18;
}
for(int j=0; j<data.length; j ++){
String value = data[j];
cell = row.createCell(j);
if(isUrl(value)){
if(isImage(value)){
int columnWidth = titles[j].getWidth() > 0 ? titles[j].getWidth() : excelBean.getColumnWidth();
ExcelImage excelImage = new ExcelImage(value);
ExcelBox excelBox = new ExcelBox(excelImage.getWidth(), excelImage.getHeight(), rowHeihgt, columnWidth, 10);
HSSFClientAnchor anchor = new HSSFClientAnchor();
int cw = getColWidth(columnWidth);
int rh = getRowHeight(rowHeihgt);
short col = (short)(j);
int rowNum = rowNumber-1;
anchor.setDx1(getAnchorX(excelBox.getX1(), cw));
anchor.setDy1(getAnchorY(excelBox.getY1(), rh));
anchor.setDx2(getAnchorX(excelBox.getX2(), cw));
anchor.setDy2(getAnchorY(excelBox.getY2(), rh));
anchor.setCol1(col);
anchor.setRow1(rowNum);
anchor.setCol2(col);
anchor.setRow2(rowNum);
anchor.setAnchorType(0);
patriarch.createPicture(anchor , wb.addPicture(excelImage.getByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
else{
cell.setCellValue(value);
cell.setCellFormula("HYPERLINK("" + value + "","" + value + "")");
HSSFCellStyle linkStyle = wb.createCellStyle();
HSSFFont cellFont= wb.createFont();
cellFont.setUnderline((byte) 1);
cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);
linkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(linkStyle);
}
}
else{
cell.setCellValue(value);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(cellStyle);
}
}
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setCharacterEncoding("utf-8");
String filename = excelBean.getName();
filename = new String(filename.replaceAll("\s|;", "").getBytes("gbk"), "ISO8859-1");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}*/
@SuppressWarnings("resource")
public static void export( ExcelBean excelBean, HttpServletResponse response) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
HSSFRow row = sheet.createRow(0);
//设置样式
HSSFCellStyle style = wb.createCellStyle();
if(excelBean.isHeadBold()){
HSSFFont headfont = wb.createFont();
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(headfont);
}
HSSFCell cell;
ExcelTitle[] titles = excelBean.getTitles();
for(int i=0; i < titles.length; i++){
ExcelTitle title = titles[i];
cell= row.createCell(i);
cell.setCellValue(title.getValue());
cell.setCellStyle(style);
int columnWidth = title.getWidth() > 0 ? title.getWidth() : excelBean.getColumnWidth();
sheet.setColumnWidth(i, getColWidth(columnWidth));
}
int rowNumber = 1;
int rowHeihgt = excelBean.getRowHeight();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
for(String[] data : excelBean.getDataList()){
row = sheet.createRow(rowNumber ++ );
if(rowHeihgt > 0){
row.setHeight((short) getRowHeight(rowHeihgt));
}
else{
rowHeihgt = 18;
}
for(int j=0; j<data.length; j ++){
String value = data[j];
cell = row.createCell(j);
if(isUrl(value)){
if(isImage(value)){
int columnWidth = titles[j].getWidth() > 0 ? titles[j].getWidth() : excelBean.getColumnWidth();
ExcelImage excelImage = new ExcelImage(value);
ExcelBox excelBox = new ExcelBox(excelImage.getWidth(), excelImage.getHeight(), rowHeihgt, columnWidth, 10);
HSSFClientAnchor anchor = new HSSFClientAnchor();
int cw = getColWidth(columnWidth);
int rh = getRowHeight(rowHeihgt);
short col = (short)(j);
int rowNum = rowNumber-1;
anchor.setDx1(getAnchorX(excelBox.getX1(), cw));
anchor.setDy1(getAnchorY(excelBox.getY1(), rh));
anchor.setDx2(getAnchorX(excelBox.getX2(), cw));
anchor.setDy2(getAnchorY(excelBox.getY2(), rh));
anchor.setCol1(col);
anchor.setRow1(rowNum);
anchor.setCol2(col);
anchor.setRow2(rowNum);
anchor.setAnchorType(0);
patriarch.createPicture(anchor , wb.addPicture(excelImage.getByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
else{
cell.setCellValue(value);
cell.setCellFormula("HYPERLINK("" + value + "","" + value + "")");
HSSFCellStyle linkStyle = wb.createCellStyle();
HSSFFont cellFont= wb.createFont();
cellFont.setUnderline((byte) 1);
cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);
linkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(linkStyle);
}
}
else{
cell.setCellValue(value);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(cellStyle);
}
}
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setCharacterEncoding("utf-8");
String filename = excelBean.getName();
filename = new String(filename.replaceAll("\s|;", "").getBytes("gbk"), "ISO8859-1");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
ByteArrayOutputStream out = new ByteArrayOutputStream();
wb.write(out);
byte[] ret = out.toByteArray();
out.flush();
out.close();
FileUpDown file = new FileUpDown();
file.download(filename,
ret, response);
}
/**
* 获取图片x方向长度坐标转换
* @param px
* @param colWidth
* @return
*/
public static int getAnchorX(int px, int colWidth){
return (int) Math.round(( (double) 701 * 16000.0 / 301)*((double)1/colWidth)*px);
}
/**
* 获取图片y方向长度坐标转换
* @param px
* @param rowHeight
* @return
*/
public static int getAnchorY(int px, int rowHeight){
return (int) Math.round(( (double) 144 * 8000 / 301)*((double)1/rowHeight)*px);
}
/**
* 行高转换
* @param px
* @return
*/
public static int getRowHeight( int px ){
return (int) Math.round(((double) 4480 / 300 ) * px);
}
/**
* 列宽转换
* @param px
* @return
*/
public static int getColWidth( int px ){
return (int) Math.round(((double) 10971 / 300 ) * px);
}
/**
* 判断是否为链接地址
*/
public static boolean isUrl(String string){
Pattern pattern = Pattern.compile("^((http|https):\/\/([\w\-]+\.)+[\w\-]+(\/[\w\u4e00-\u9fa5\-\.\/?\@\%\!\&=\+\~\:\#\;\,]*)?)", Pattern.CASE_INSENSITIVE );
return pattern.matcher(string).matches();
}
/**
* 判断是否为图片
*/
public static boolean isImage(String string){
Pattern pattern = Pattern.compile("\S+\.(jpg|jpeg|png|gif|bmp)(\?\S+)?$", Pattern.CASE_INSENSITIVE );
return isUrl(string) && pattern.matcher(string).matches();
}
}
下载类，FileUpDown：
package com.goldensky.common.excelutil;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class FileUpDown {
/**
* 导出文件，注意：导出后缓存将被删除
*
* @param fileName 文件名
* @param buffer 文件内容缓存
* @param response HttpServletResponse对象
* @return 如果出错返回错误信息
*/
public static String download(String fileName, byte[] buffer, HttpServletResponse response)
{
String ret = "";
javax.servlet.ServletOutputStream outputStream = null;
try
{
if (buffer != null && buffer.length > 0)
{
String strFileName = new String(fileName.getBytes("utf-8"), "utf-8");
response.reset();
response.setContentType("application/x-download");
response.setHeader("Content-Disposition", "attachment; filename="" + strFileName + """);
int length = buffer.length;
outputStream = response.getOutputStream();
outputStream.write(buffer, 0, length);
outputStream.flush();
outputStream.close();
response.setStatus(HttpServletResponse.SC_OK);
response.flushBuffer();
}
}
catch (Exception e)
{
}
finally
{
buffer = null;
}
return ret;
}
/**
* 下载数组内容到客户端一文件
*/
public static void download(HttpServletResponse response, HttpServletRequest request, byte[] buffer,
String strFileName)
{
try
{
// strFileName = new String( strFileName.getBytes("Big5"),
// "ISO8859_1" ));
strFileName = new String(strFileName.getBytes("gb2312"), "iso-8859-1");
response.reset();
response.setContentType("application/x-download");
// if (request.getHeader("User-Agent").indexOf("MSIE 5.5") != -1) {
// ... IE5.5不能加上 attachment;
response.setHeader("Content-Disposition", "attachment; filename="" + strFileName + """);
int length = buffer.length;
ServletOutputStream outputStream = response.getOutputStream();
outputStream.write(buffer, 0, length);
outputStream.flush();
outputStream.close();
response.setStatus(HttpServletResponse.SC_OK);
response.flushBuffer();
}
catch (Exception e)
{
}
}
}
调用示例：
前台：
//导出Excel数据
function exportexcel()
{
var drvalue=$("#drstatus").combobox('getValue');
location.href="${localCtx }/approvaloption/export.do?flowstatus="+drvalue+"&&roleid="+${sysUser.userrole.nRoleId}+"";
}
后台调用：
@RequestMapping(value="export.do")
public void exportExcel(String flowstatus,Long roleid,HttpServletResponse response)
{
Long status=(null!=flowstatus&&!"".equals(flowstatus))?Long.parseLong(flowstatus):null;
List<Flow> list=flowService.getList(roleid,status);
ExcelTitle[] titles = {
ExcelTitle.generate("流程名称", 60),
ExcelTitle.generate("流程描述", 100),
ExcelTitle.generate("流程节点名称", 100),
ExcelTitle.generate("审批人", 100),
ExcelTitle.generate("通行证类型", 100),
ExcelTitle.generate("号牌号码", 100),
ExcelTitle.generate("车辆使用单位", 100),
ExcelTitle.generate("业务类别", 100),
ExcelTitle.generate("审批状态", 100),
};
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String fileName = format.format(Calendar.getInstance().getTime())+"审核结果.xls";
ExcelBean excelBean = new ExcelBean(fileName, "统计", titles);
for(Flow fl : list){
String flowname=fl.getFd().getFlowName();
String flowdesc=fl.getFd().getFlowDesc();
String nodeName=fl.getFn().getNodeNames();
String spr=fl.getT().getRoleName();
String txzlx=fl.getDic().getDmsm1();
String hphm=fl.getTp().getHphm();
String clsydw=fl.getTp().getClsydw();
String ywlb=(fl.getTp().getYwlb()==1)?"业务申请":"延期";
String splc=(fl.getApproveStatus()==1L)?"未审批":"已审批";
excelBean.add(new String[]{flowname,flowdesc,nodeName,spr,txzlx,hphm,clsydw,ywlb,splc});
}
try {
excelExport.export(excelBean, response);
} catch (Exception e) {
e.printStackTrace();
}
}