1.创建excel工具方法(前提:导入poi-3.7-20101029.jar)
/**
* 导出Excel工具方法 (注:此导出EXCEL工具方法只能创建一个工作簿)
*/
public static HSSFWorkbook exportExcel(Object[] head, Object[] column, List<?> data) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建Excel文件
HSSFSheet sheet = workbook.createSheet(); // 创建工作薄
HSSFRow row = null; // 行计录
HSSFCell cell = null; // 单元格
int rowIndex = 0;
// 增加头文件信息
if (head != null) {
row = sheet.createRow(rowIndex);
for (int i = 0; i < head.length; i++) {
cell = row.createCell(i);
cell.setCellValue(head[i].toString());
}
rowIndex++;
}
// 写入内容
for (int i = 0; i < data.size(); i++) {
row = sheet.createRow(rowIndex++);
Object obj = data.get(i);
for (int j = 0; j < column.length; j++) {
String col = column[j].toString();
if (col.charAt(1) >= 'a' && col.charAt(1) <= 'z')
col = col.substring(0, 1).toUpperCase() + col.substring(1);
try {
Object result = obj.getClass().getMethod("get" + col).invoke(obj);
row.createCell(j).setCellValue(result == null ? "" : result.toString());
} catch (Exception e) {
throw e;
}
}
}
return workbook;
}
/**
* 导出Excel工具方法 (注:此导出EXCEL工具方法能创建多个工作簿)
*/
public static HSSFWorkbook exportExcel(Object[] head, Object[] column,
List<?> data) throws Exception {
// 注:SystemContext.EXECEL_LIMIT代表规定每个工作簿存记录条数
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建Excel文件
int sheetNum = 0;
if(data.size()%SystemContext.EXECEL_LIMIT>0) {
sheetNum = (data.size()/SystemContext.EXECEL_LIMIT+1);
} else {
sheetNum = data.size()/SystemContext.EXECEL_LIMIT;
}
for(int x = 0; x<sheetNum; x++) {
HSSFSheet sheet = workbook.createSheet(); // 创建工作薄
HSSFRow row = null; // 行计录
HSSFCell cell = null; // 单元格
int rowIndex = 0;
// 增加头文件信息
if (head != null) {
row = sheet.createRow(rowIndex);
for (int i = 0; i < head.length; i++) {
cell = row.createCell(i);
cell.setCellValue(head[i].toString());
}
rowIndex++;
}
// 写入内容
for (int i = x*SystemContext.EXECEL_LIMIT; i < (x+1)*SystemContext.EXECEL_LIMIT && i<data.size();
i++) {
row = sheet.createRow(rowIndex++);
Object obj = data.get(i);
for (int j = 0; j < column.length; j++) {
String col = column[j].toString();
if (col.charAt(1) >= 'a' && col.charAt(1) <= 'z')
col = col.substring(0, 1).toUpperCase() + col.substring(1);
try {
Object result = obj.getClass().getMethod("get" + col)
.invoke(obj);
row.createCell(j).setCellValue(
result == null ? "" : result.toString());
} catch (Exception e) {
throw e;
}
}
}
}
return workbook;
}
2.构造数据调用上面方法(这里截取控制器中方法片段)
public String repealExcel(ConsumeCancelModel comsume, String[] colums,
HttpServletRequest request, HttpServletResponse response, Model model) {
//根据条件从数据库中获得list
List<ConsumeCancelModel> excel = (List<ConsumeCancelModel>)tservice.cancelpageBySQLList(condition.toString());
OutputStream os = null;
try {
// excel文件的头部信息
String[] head = new String[] { "商户编号", "商户名称", "订单号", "订单日期",
"订单金额", "订单金额币种", "cups交易流水号", "清算日期", "清算金额",
"清算币种", "清算汇率", "撤销发起日期", "订单状态"};
// 对应bean字段的列写入文件内容
String[] column = new String[] { "merchantId", "merchantName", "merchantOrderId",
"merchantOrderTimeStrcon", "merchantOrderAmtStr","currency", "cupsQid",
"settleDateStr", "setlAmtStr","square", "converRate",
"createdTimeStrcon", "status"};
// 调用生成excel方法
HSSFWorkbook hssfWorkbook = SystemUtil.exportExcel(head,
column, excel);
// 获取excel生成的路径包括文件名
response.addHeader("Content-Disposition", "p_w_upload;filename=" + SystemUtil.dateFormatString(new Date(), "yyyyMMddhhmmss") + "repealExcel.xls");
//写出
os = response.getOutputStream();
hssfWorkbook.write(os);
catch(Exception e) {
e.printstack();
}
finally{
//别忘记关闭流
if (null != os) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
*创建据有标题，特定行， 特定列样式的excel
*
*/
public static HSSFWorkbook exportExcelOrder(Object[] head, Object[] column,
List<?> data, String title) throws Exception {
// 注:SystemContext.EXECEL_LIMIT代表规定每个工作簿存记录条数
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建Excel文件
// 设置字体样式 1
HSSFFont headfont = workbook.createFont();
headfont.setFontName("黑体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
headstyle.setWrapText(true);// 自动换行
// 设置字体样式 2
HSSFFont columnHeadFont2 = workbook.createFont();
columnHeadFont3.setFontName("黑体");
columnHeadFont3.setFontHeightInPoints((short) 10);
columnHeadFont3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle headstyle2 = workbook.createCellStyle();
headstyle3.setFont(columnHeadFont2);
headstyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
int sheetNum = 0;
if (data.size() % 10000 > 0) {
sheetNum = (data.size() / 10000 + 1);
} else {
sheetNum = data.size() / 10000;
}
for (int x = 0; x < sheetNum; x++) {
HSSFSheet sheet = workbook.createSheet(); // 创建工作薄
// 设置指定列宽 第一个参数表示 第几列 第二个参数表示 列宽
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 5000);
sheet.setColumnWidth(5, 4000);
sheet.setColumnWidth(6, 4000);
sheet.setColumnWidth(7, 4000);
sheet.setColumnWidth(8, 4000);
sheet.setColumnWidth(9, 6000);
// 创建第一行做标题行 索引从0开始
HSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 900);//设置第一行的高度
// 合并行 四个参数分别为：开始行 开始列 结束行 结束列
sheet.addMergedRegion(new Region(0, (short) 1, 0, (short) 8));
// 在row0行里创建第二列cell1 索引从0开始
HSSFCell cell1 = row0.createCell(1);
// 为第二列设置样式
cell1.setCellStyle(headstyle);
// 为第二列赋值 title为导出的excel标题
cell1.setCellValue(new HSSFRichTextString(title));
HSSFRow row = null; // 行计录
HSSFCell cell = null; // 单元格
int rowIndex = 2; //指定正文从第3行开始，下面的与上面的两个方法一样了
// 增加头文件信息
if (head != null) {
row = sheet.createRow(rowIndex);
for (int i = 0; i < head.length; i++) {
cell = row.createCell(i);
// 为第一列设置样式
cell.setCellStyle(headstyle3);
cell.setCellValue(head[i].toString());
}
rowIndex++;
}
// 写入内容
for (int i = x * 10000; i < (x + 1) * 10000 && i < data.size(); i++) {
row = sheet.createRow(rowIndex++);
Object obj = data.get(i);
for (int j = 0; j < column.length; j++) {
String col = column[j].toString();
if (col.charAt(1) >= 'a' && col.charAt(1) <= 'z')
col = col.substring(0, 1).toUpperCase()
+ col.substring(1);
try {
Object result = obj.getClass().getMethod("get" + col)
.invoke(obj);
row.createCell(j).setCellValue(
result == null ? "" : result.toString());
} catch (Exception e) {
throw e;
}
}
}
}
return workbook;
}
