使用execel导出数据

第一种方法，先生成文件，再下载下来
在用poi在EXECL报表设计的时候，遇到单元格合并问题，用到一个重要的函数：

CellRangeAddress（int， int， int， int）

参数：起始行号，终止行号， 起始列号，终止列号

网上老多地方说是（//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号）误导，经过仔细测试，应该是（起始行号，终止行号， 起始列号，终止列号），

package com.jwy.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
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.CellRangeAddress;

import com.sun.corba.se.spi.servicecontext.UEInfoServiceContext;

/**
* 合并单元格
*
* @author Jingweiyu
*/
public class unionExcel {
public static void main(String[] args) throws Exception {
HSSFWorkbook workBook = new HSSFWorkbook(); // 创建 一个excel文档对象
HSSFSheet sheet = workBook.createSheet(); // 创建一个工作薄对象

// 设置样式
HSSFCellStyle titleStyle = workBook.createCellStyle();// 创建样式对象
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
HSSFFont titleFont = workBook.createFont(); // 创建字体对象
titleFont.setFontHeightInPoints((short) 15); // 设置字体大小
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
titleFont.setFontName("黑体"); // 设置为黑体字
titleStyle.setFont(titleFont);

// 合并单元格操作
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 7));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7));
// sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 3));

HSSFRow row = null;
HSSFCell cell = null;

row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellStyle(titleStyle);
cell.setCellValue(new HSSFRichTextString("明日公司员工详细信息"));

// 设置表文样式
HSSFCellStyle tableStyle = workBook.createCellStyle();

// 设置表文字体
HSSFFont tableFont = workBook.createFont();

tableFont.setFontHeightInPoints((short) 12); // 设置字体大小
tableFont.setFontName("宋体"); // 设置为黑体字
tableStyle.setFont(tableFont);

String[] row1 = { "姓名：", "李**", "性别：", "女", "出生日期：", "1985年5月27" };


//
//
// Users us = new Users();
// us.setEmail("11");
//
// Users us2 = new Users();
// us2.setEmail("2222");
//
// List<Users> list = new ArrayList();
// list.add(us);
// list.add(us2);
//
// for (Users usObj : list) {
// System.out.println(usObj.getEmail());
// }
// for (int j = 0; j < list.size(); j++) {
// Users usObj = (Users) list.get(j);
//
// System.out.println(usObj.getEmail());
// }

row = sheet.createRow(2);
for (int i = 0; i < row1.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(tableStyle);
cell.setCellValue(new HSSFRichTextString(row1[i]));
}

for (int i = 0; i < 10; i++) {

String row2[] = { "家庭住址：", "吉林省长春市朝阳区*****", "111", "111", "邮编：",
"130021", "家庭电话：", "8562****" };

export(row2, cell, tableStyle, workBook, sheet, i, row);

}

}

public static void export(String[] row2, HSSFCell cell,
HSSFCellStyle tableStyle, HSSFWorkbook workBook, HSSFSheet sheet,
int j, HSSFRow row) throws IOException {

row = sheet.createRow(j + 3);

for (int k = 0; k < row2.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(tableStyle);
cell.setCellValue(new HSSFRichTextString(row2[k]));
}

// 文件输出流
FileOutputStream os = new FileOutputStream("E:\\unionExcel.xls");
workBook.write(os); // 将文档对象写入文件输出流
os.close(); // 关闭文件输出流

}
}


第二种方法 直接输出流下载

/**
* 导出报表数据
*
* @param type
* @throws FileNotFoundException
*/
public void export(String year, String month, String type,
HttpServletResponse response, String exportName) throws Exception {
OutputStream out = null;
log.info("response---------------" + response);
out = response.getOutputStream();// 获取输出流
WritableWorkbook wbook = null;
try {
wbook = Workbook.createWorkbook(out);
} catch (IOException e1) {
e1.printStackTrace();
} // 建立excel文件
WritableSheet wsheet = wbook.createSheet(exportName, 0); // 工作表名称
// 设置Excel字体
// 设置Excel居中
jxl.write.NumberFormat nfOne = new jxl.write.NumberFormat("#.0");
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#");
jxl.write.NumberFormat nfTwo = new jxl.write.NumberFormat("#.00");
WritableCellFormat formatLeft = new WritableCellFormat();
WritableCellFormat formatCenter = new WritableCellFormat();
WritableCellFormat formatRight = new WritableCellFormat(nf);
WritableCellFormat formatRightOne = new WritableCellFormat(nfOne);
WritableCellFormat formatRightTwo = new WritableCellFormat(nfTwo);
formatCenter.setAlignment(jxl.format.Alignment.CENTRE);
formatRight.setAlignment(jxl.format.Alignment.RIGHT);
formatLeft.setAlignment(jxl.format.Alignment.LEFT);
formatRightOne.setAlignment(jxl.format.Alignment.RIGHT);
String[] title = { "指标名称", "计算单位", "1-12月累计", "累计增长%" };
// 设置Excel表头
for (int i = 0; i < title.length; i++) {
Label excelTitle = new Label(i, 0, title[i], formatCenter);
wsheet.addCell(excelTitle);
}
String hql = "from StatisiticsQuota s where s.sqCode ='" + type + "'"
+ " and s.sqYear='" + year + "' and s.sqMonth='"
+ month + "'" + " order by s.sqId asc";

// 需要导出的数据
List<StatisiticsQuota> list = baseDAO.selectValueObjectsByHQL(hql);
log.info("--------list大小--" + list.size());
int c = 1; // 用于循环时Excel的行号
Iterator it = list.iterator();
while (it.hasNext()) {
StatisiticsQuota crdto = (StatisiticsQuota) it.next();
String sqquotaName = crdto.getSqquotaName()==null?"":crdto.getSqquotaName();
String units = crdto.getSqUnits()==null?"":crdto.getSqUnits();
Double sqAddup = crdto.getSqAddup()==null?0.0:crdto.getSqAddup();
Double sqTrowthRate = crdto.getSqTrowthRate()==null?0.0:crdto.getSqTrowthRate();
Label label1 = new Label(0,c,sqquotaName,formatLeft);
Label label2 = new Label(1,c,units,formatCenter);
if(sqAddup!=0.0){
jxl.write.Number number3 = new jxl.write.Number(2,c,sqAddup,formatRightTwo);
wsheet.addCell(number3);
}
if(sqTrowthRate!=0.0){
jxl.write.Number number4 = new jxl.write.Number(3,c,sqTrowthRate,formatRightTwo);
wsheet.addCell(number4);
}
try {
wsheet.addCell(label1);
wsheet.addCell(label2);


} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
c++;
}
// 设置导出中文名称（解决中文乱码问题）
response.setHeader("Content-Disposition", "attachment;filename="
+ java.net.URLEncoder.encode(exportName + ".xls", "UTF-8"));
wbook.write();
wbook.close();
out.flush();
out.close();
// 文件输出流

}