excel 导出及单元格合并

合并单元格类
public ActionForward doExport(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws Exception {

log.info("doExport...");

request.setAttribute("nopage", "1");
this.doQuery(mapping, form, request, response);
String settlePeriod = request.getParameter("settlePeriod");

String strDeptName = request.getParameter("strDeptName");
String strDeptCode = request.getParameter("strDeptCode");

String year = settlePeriod.substring(0, 4);
String month = settlePeriod.substring(4);
AfExcel excel = new AfExcel();
// excel文件命名所需
excel.setLoginUserID(Long.valueOf((String) request.getSession().getAttribute("userid")));
// 表头
excel.setTitle("营业环节结算支出表", year + "年" + month + "月", "单位:元");
// 标题栏
//第一行   机构名称 机构代码
excel.addColumnNames(new String[] { "机构名称：" + strDeptName, "机构名称：" + strDeptName, "机构名称：" + strDeptName,
"机构名称：" + strDeptName, "机构名称：" + strDeptName, "机构名称：" + strDeptName, "机构名称：" + strDeptName,
"机构名称：" + strDeptName, "机构名称：" + strDeptCode, "机构代码:" + strDeptCode, "机构代码:" + strDeptCode,
"机构代码:" + strDeptCode, "机构代码:" + strDeptCode, "机构代码:" + strDeptCode, "机构代码:" + strDeptCode });
//第二行  邮件种类 产品   按流向分类  显示该机构对应的责任中心属性（如函件责任中心等）
excel.addColumnNames(new String[] { " 邮件种类 ", "产品", "按流向分类", "显示该机构对应的责任中心属性（如函件责任中心等）",
"显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）",
"显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）",
"显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）",
"显示该机构对应的责任中心属性（如函件责任中心等）", "显示该机构对应的责任中心属性（如函件责任中心等）" });
//第三行    邮件种类 产品   按流向分类    业务量 内部结算价 外部结算价 结算额 重量 内部结算价 外部结算价 结算额 资费收入 结算价 结算额 小计
excel.addColumnNames(new String[] { " 邮件种类 ", "产品", "按流向分类", "业务量 ", "内部结算价 ", "外部结算价 ", "结算额 ", "重量", "内部结算价",
"外部结算价", "结算额", " 资费收入", "结算价", "结算额", "小计" });

// excel.addColumnNames(new String[] { "单位", "机构名称：", "干线运费结算金额", "干线运费结算金额", "干线运费结算金额", "干线运费结算金额","国际运费结算金额", "国际运费结算金额", "国际终端费结算金额", "国际终端费结算金额", "应列帐金额" });
//excel.addColumnNames(new String[] { "单位", "经济速递", "标准EMS", "快递包裹", "国内速递国内运费", "国际包裹国内运费", "国内速递国际运费","国际包裹国际运费", "国内速递", "国际包裹", "实际列帐金额" });

//哪行起           哪列起                          到哪行                   到哪列
// public Region(int rowFrom, short colFrom, int rowTo, short colTo)
excel.addMergedRegion(new Region(3, (short) 0, 3, (short)); //合并机构名称
excel.addMergedRegion(new Region(3, (short) 9, 3, (short) 14)); //合并机构代码
excel.addMergedRegion(new Region(4, (short) 0, 5, (short) 0)); //邮件种类
excel.addMergedRegion(new Region(4, (short) 1, 5, (short) 1)); //产品
excel.addMergedRegion(new Region(4, (short) 2, 5, (short) 2)); //安流向分类
excel.addMergedRegion(new Region(4, (short) 3, 4, (short) 14)); //合并责任中心属性

R13010112Biz r13010112Biz = new R13010112Biz();
String[] lstDept = request.getParameterValues("dept"); //区局
List list = r13010112Biz.queryExportData(settlePeriod, lstDept, null);
ArrayList body = new ArrayList(list.size() + 1);
Map<String, MerginRows> rownum1Map = new HashMap<String, MerginRows>();
Map<String, MerginRows> rownum2Map = new HashMap<String, MerginRows>();
for (int i = 0; i < list.size(); i++) {
Map map = (Map) list.get(i);

String mailKind = (String) map.get("mailKind");
String productType = (String) map.get("productType");
String mailCode = (String) map.get("mailCode");
String pieces = (String) map.get("pieces");
String pInnerPrice = (String) map.get("pInnerPrice");

String pOuterPrice = (String) map.get("pOuterPrice");
String pFee = (String) map.get("pFee");
String weight = (String) map.get("weight");
String wInnerPrice = (String) map.get("wInnerPrice");
String wOuterPrice = (String) map.get("wOuterPrice");

String wFee = (String) map.get("wFee");
String mailFee = (String) map.get("mailFee");
String mailFeePrice = (String) map.get("mailFeePrice");
String fFee = (String) map.get("fFee");
String allFee = (String) map.get("allFee");

if (rownum1Map.get(mailKind) == null) {
MerginRows bean = new MerginRows();
bean.setStartRow(i);
bean.setEndRow(i);
bean.setHebingRows(i);
rownum1Map.put(mailKind, bean);
} else {
MerginRows bean = rownum1Map.get(mailKind);
bean.setEndRow(i);
bean.setHebingRows(bean.getHebingRows() + 1);
rownum1Map.put(mailKind, bean);
}

if (rownum2Map.get(productType) == null) {
MerginRows bean = new MerginRows();
bean.setStartRow(i);
bean.setEndRow(i);
bean.setHebingRows(i);
rownum2Map.put(productType, bean);
} else {
MerginRows bean = rownum2Map.get(productType);
bean.setEndRow(i);
bean.setHebingRows(bean.getHebingRows() + 1);
rownum2Map.put(productType, bean);
}
body.add(new String[] { mailKind, productType, mailCode, pieces, pInnerPrice, pOuterPrice, pFee, weight,
wInnerPrice, wOuterPrice, wFee, mailFee, mailFeePrice, fFee, allFee });
//合并单元格
//合并单列(第一列和第二列)。。
merginCellsByRowMap(6, (short) 0, (short) 0, rownum1Map, excel);
merginCellsByRowMap(6, (short) 1, (short) 1, rownum2Map, excel);
}

//业务量 结算额  总计
String a_pieces_str = request.getParameter("a_pieces_str");
String a_p_fee_str = request.getParameter("a_p_fee_str");
String a_weight_str = request.getParameter("a_weight_str");
String a_w_fee_str = request.getParameter("a_w_fee_str");
String a_mail_fee_str = request.getParameter("a_mail_fee_str");
String a_f_fee_str = request.getParameter("a_f_fee_str");
String last_all_fee_str = request.getParameter("last_all_fee_str");
body.add(new String[] { "合计 (对应市分公司 平衡表收入) ", "合计 (对应市分公司 平衡表收入) ", "合计 (对应市分公司 平衡表收入) ", a_pieces_str, "", "",
a_p_fee_str, a_weight_str, "", "", a_w_fee_str, a_mail_fee_str, "", a_f_fee_str, last_all_fee_str });
//合并尾列。。。
//求出最后一行的长度。。
int excelEndRow = list.size() + 6;
excel.addMergedRegion(new Region(excelEndRow, (short) 0, excelEndRow, (short) 2)); //合计 (对应市分公司 平衡表收入)
excel.setBody(body);
return excel.create(mapping, request, "R13010112");
}



/*
* 合并行
* beginMerginRow 开始合并的行
* rownum1Map  要合并的数据列
*   AfExcel
*/

public void merginCellsByRowMap(int beginMerginRow, short cellBeginNum, short cellEndNum,
Map<String, MerginRows> rownum1Map, AfExcel excel) {
for (Entry<String, MerginRows> entry : rownum1Map.entrySet()) {
excel.addMergedRegion(new Region(beginMerginRow + entry.getValue().getStartRow(), cellBeginNum,
beginMerginRow + entry.getValue().getEndRow(), cellEndNum));
}

}

2.excel生成工具类

package com.ist.af;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.ist.util.JZip;

/**
* <p>Title: </p>
*
* <p>Description: </p>
*
* <p>Copyright: Copyright (c) 2006</p>
*
* <p>Company: gdpost-ist</p>
*
* @author cobe
* @version 1.0
*/
public class AfExcel {
/**
* 用户ID
*/
private Long loginUserID;
/**
* 标题栏占用列数
*/
private int title_colspan;
/**
* 表体开始行数
*/
private int body_beginrow = 3;
/**
* 当前处理行
*/
private long i_curr_row;
/**
* 标题1(具体见setTitle注释)
*/
private String title_s1;
/**
* 标题2
*/
private String title_s2;
/**
* 标题3
*/
private String title_s3;

/**
* 普通列表头名
*/
private ArrayList columnNames = new ArrayList();
/**
* 合并区域
*/
private ArrayList mergedRegion = new ArrayList();
/**
* 表尾
*/
private ArrayList bottoms = new ArrayList();
/**
* 表体数据
*/
private ArrayList body;

/**
* 设置操作用户
* @param userid Long
*/
public void setLoginUserID(Long userid) {
this.loginUserID = userid;
}

/**
* 设置报表头
* 报表头格式如:
*                   s1
*                   s2
*                                s3
* @param colspan int
* @param s1 String
* @param s2 String
* @param s3 String
*/
public void setTitle(String s1, String s2, String s3) {
this.title_s1 = s1;
this.title_s2 = s2;
this.title_s3 = s3;
}

/**
* 取得当前处理行
* @return long
*/
public long getCurrRow() {
return this.i_curr_row;
}

/**
* 设置普通列表头
* @param columnNames String[]
*/
public void addColumnNames(String[] columnNames) {
this.title_colspan = columnNames.length - 1;
this.columnNames.add(columnNames);
this.body_beginrow++;
}

/**
* 添加表尾
*/
public void addBottom(String[] bottom) {
this.bottoms.add(bottom);
}

/**
* 设置表体
* @param body ArrayList
*/
public void setBody(ArrayList body) {
this.body = body;
}

/**
* 生成excel文件
* @return String
*/
public ActionForward create(ActionMapping mapping, HttpServletRequest request, String prefix) throws Exception {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();

//创建居中style和右对齐style
HSSFCellStyle style_center = workbook.createCellStyle();
HSSFCellStyle style_right = workbook.createCellStyle();
style_center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style_center.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style_right.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style_right.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//创建Font
HSSFFont font = workbook.createFont();

int iSheet = 0;
//创建第一个sheet
HSSFSheet sheet = workbook.createSheet(new String(("Report" + (iSheet++)).getBytes("ISO8859-1")));
sheet.setDefaultColumnWidth((short) 12);

HSSFRow row = null;
HSSFCell cell = null;

//生成公共部分
this.createCommonOfSheet(sheet, style_center, style_right, font);

//当前行计数器
i_curr_row = this.body_beginrow;
//生成表体
if (body != null) {
for (int i = 0; i < body.size(); i++) {
//如果一个sheet记录超出60000行，生成新sheet
if (i_curr_row % Constants.EXCEL_MAXROW_PER_SHEET == 0) {
//创建新sheet
sheet = workbook.createSheet(new String(("Report" + (iSheet++)).getBytes("ISO8859-1")));
sheet.setDefaultColumnWidth((short) 12);

this.createCommonOfSheet(sheet, style_center, style_right, font);
i_curr_row = this.body_beginrow;
}

String[] data = (String[]) body.get(i);
//填充一行
row = sheet.createRow((short) (i_curr_row++));
for (int j = 0; j < data.length; j++) {
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(data[j]);
if (this.columnNames.size() > 0) {
String[] names = (String[]) this.columnNames.get(this.columnNames.size() - 1);
if (names[j].endsWith("_RIGHT")) {
cell.setCellStyle(style_right);
} else {
cell.setCellStyle(style_center);
}
}
}
}
}

//生成表尾
for (int i = 0; i < this.bottoms.size(); i++) {
String[] tmp = (String[]) this.bottoms.get(i);
row = sheet.createRow((int) i_curr_row++);
for (int j = 0; j < tmp.length; j++) {
cell = row.createCell((short) j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style_center);
cell.setCellValue(tmp[j]);
}
}

//生成excel文件
String filename = prefix + "_" + this.loginUserID + "_" + ((new Date()).getTime()) + ".xls";
String s_path = "/report/export/" + PubFunc.today() + "/";
File path = new File(Constants.EXCEL_PATH + s_path);
if (!path.exists()) {
path.mkdirs();
}
String s_file = s_path + filename;
File file = new File(Constants.EXCEL_PATH + s_file);
FileOutputStream fout = new FileOutputStream(file, false);
workbook.write(fout);
fout.flush();
fout.close();
Logger.getLogger(this.getClass()).info("\n生成EXCEL文件" + file.getAbsoluteFile());

//压缩
String zipname = JZip.zip(Constants.EXCEL_PATH + s_path, filename);
Logger.getLogger(this.getClass()).info("\n生成压缩文件" + zipname);

//  file.delete();

request.setAttribute("excel_uri", s_path + zipname);
return mapping.findForward("excel");
}

/**
* 创建每个sheet公共部分
* @param sheet HSSFSheet
*/
private void createCommonOfSheet(HSSFSheet sheet, HSSFCellStyle style_center, HSSFCellStyle style_right,
HSSFFont font) {
HSSFRow row = null;
HSSFCell cell = null;

//创建第一行标题栏
row = sheet.createRow(0);
for (int i = 0; i < this.title_colspan; i++) {
cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cell.setCellStyle(style_center);
cell.setCellValue(this.title_s1);
}
//合并单元格，0行0列到0行n列
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) this.title_colspan));

//创建第二行标题栏
row = sheet.createRow(1);
for (int i = 0; i < this.title_colspan; i++) {
cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style_center);
cell.setCellValue(this.title_s2);
}
//合并单元格，1行0列到1行n列
sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) this.title_colspan));

//创建第三行标题栏
//需区分是否有左右两列
row = sheet.createRow(2);

for (int i = 0; i < this.title_colspan; i++) {
cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cell.setCellStyle(style_right);
cell.setCellValue(this.title_s3);
}
//合并单元格，2行0列到2行n列
sheet.addMergedRegion(new Region(2, (short) 0, 2, (short) this.title_colspan));

//生成列表头
for (int i = 0; i < columnNames.size(); i++) {
//前三行为报表头，列表头从第四行开始
row = sheet.createRow((short) (3 + i));
String[] names = (String[]) columnNames.get(i);
for (int j = 0; j < names.length; j++) {
cell = row.createCell((short) j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cell.setCellStyle(style_center);
cell.setCellValue(names[j].replaceAll("_RIGHT", ""));
}
}

//合并用户自定义单元格
for (int i = 0; i < this.mergedRegion.size(); i++) {
sheet.addMergedRegion((Region) this.mergedRegion.get(i));
}
}

/**
* 添加合并区域
* @param region Region
*/
public void addMergedRegion(Region region) {
this.mergedRegion.add(region);
}
}