JAVA实现创建Excel表并导出

包：poi-3.7-20101029.jar

链接：http://pan.baidu.com/s/1kUQ9kRd 密码：v5hk

html调用代码

<a href="javascript:void(0);" class="export" plain="true" icon="icon-export"  id="expoer" >导出</a>

js代码  部分功能基于easyui

<script type="text/javascript" src="${ctx}/static/js/views/usejs/serviceOrder2.js"></script>
<script type="text/javascript">
jQuery(document).ready(function($) {
    $("a.jquery-word-export").click(function(event) {
        $("#pagecontent").wordExport();
    });
});
$(function(){
$("#expoer").click(function (){
$("#adownload").html("");
var tsoNumber = $("#tsoNumber").val();  //订单编号
var tmName = $("#tmName").val();//会员昵称
var AddDate = $("#AddDate").datebox('getValue');//创建时间--开始
var AddDateEnd = $("#AddDateEnd").datebox('getValue');//创建时间---结束
var tsoStatus = $("#tsoStatus").combobox('getValue');//订单状态
var tspId = $("#tspId").combobox('getValue');//网点
var tiItemId = $("#tiItemId").combobox('getValue');//故障小类
var tseId = $("#tseId").combobox('getValue');
$.messager.progress({
title:'请等待',
msg:'正在导出'
})
$.ajax({
type: 'POST',
url: ctx+"/serviceOrder/exportXls_two",
data:{'tsoNumber':tsoNumber,'tmName':tmName,'AddDate':AddDate,'AddDateEnd':AddDateEnd,'tsoStatus':tsoStatus,'tiItemId':tiItemId,'tspId':tspId,'tseId':tseId},
dataType: 'text',
success: function (msg) {
if(msg == "100"){
$("body").append($("<iframe style='display:none;'/>").attr("src","${ctx}/static/xls/报修工单统计表.xls"));
$.messager.progress('close');
// window.location.href="${ctx}/WebContent/xls/平台账目统计.xls";
}else if(msg == "101"){
$.messager.progress('close');
$.messager.alert('提示','下载失败，可能原因：1.导入前未关闭Excel文件，2.导入的数据有误！','error');
}
}
});

})

});


</script>

Controller层代码

try {

String name = "配件费用统计表";
String fieldNameSum = "报修工单,"+"报修时间,"+"报修单位,"+"联系人,"+"联系电话,"+"报修费用,"+"工单电话";
ExportExcel_tzf exportExcel = new ExportExcel_tzf();


/**
* 添加分页和查询参数
*/
HashMap<String, String> params = new HashMap<String, String>();
params.put("page", request.getParameter("page"));
params.put("pageSize", request.getParameter("rows"));
params.put("order", request.getParameter("order"));
params.put("tsoNumber", request.getParameter("tsoNumber"));
params.put("tmName", request.getParameter("tmName"));
params.put("tsoStatus", request.getParameter("tsoStatus"));
params.put("AddDate", request.getParameter("AddDate"));
params.put("AddDateEnd", request.getParameter("AddDateEnd"));

HashMap<String, Object> findForJson = serviceOrderService.findCoststatistics_xls(params);

List<Map<String, Object>> object = (List<Map<String, Object>>)findForJson.get("rows");
List<Map<String, Object>> listMap = new ArrayList<Map<String,Object>>();
StringBuffer str = new StringBuffer();
// str.append("[");
for(int i=0;i<object.size();i++){
Map<String, Object> map = object.get(i);
Object  object_one =  map.get("tsoNumber");
if(object_one == null)
object_one = "";
Object  object_two = map.get("tsoAddDate");
if(object_two == null)
object_two = "";
Object  object_three = map.get("tcSimpleName");
if(object_three == null)
object_three = "";
Object  Object_four = map.get("tmName");
if(Object_four == null)
Object_four = "";
Object  Object_five = map.get("tcMobile");
if(Object_five == null)
Object_five = "";
Object  Object_six = map.get("SumTodPrice");
if(Object_six == null)
Object_six = "";
/*Object  Object_seven = map.get("tsoStatus");
if(Object_seven == null)
Object_seven = "";*/
Integer tsoStatus= (Integer) map.get("tsoStatus");
//工单状态,1：客户报修 2：已派工 3：已确认(待维修) 4：维修中 5: 已维修 6:验收(支付) 7:评价 0：删除',
String  Object_seven = "";
if(tsoStatus == 1)
Object_seven = "客户报修";
if(tsoStatus == 2)
Object_seven = "已派工";
if(tsoStatus == 3)
Object_seven = "已确认（待维修）";
if(tsoStatus == 4)
Object_seven = "维修中";
if(tsoStatus == 5)
Object_seven = "已维修";
if(tsoStatus == 6)
Object_seven = "验收评价";
if(tsoStatus == 7)
Object_seven = "已评价";


HashMap<String, Object> strMap = new HashMap<String, Object>();
strMap.put("0", object_one);
strMap.put("1", object_two);
strMap.put("2", object_three);
strMap.put("3", Object_four);
strMap.put("4", Object_five);
strMap.put("5", Object_six);
strMap.put("6", Object_seven);
listMap.add(strMap);

}

//传入需要导出的数据

String export = exportExcel.ExportExcel_consts(request, null, name, fieldNameSum, listMap);


return export;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return "101";

}

实现层方法

package com.spring.rabbit.controller;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletRequest;
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.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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;
import org.json.JSONArray;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;


public class ExportExcel_tzf {
public Logger logger = LoggerFactory.getLogger(getClass());
public static void main(String args[])  throws IOException{

//创建HSSFWorkbook对象  
HSSFWorkbook wb = new HSSFWorkbook();  
//创建HSSFSheet对象  
HSSFSheet sheet = wb.createSheet("成绩表");  
//创建HSSFRow对象  
HSSFRow row = sheet.createRow(0);  
//创建HSSFCell对象  
HSSFCell cell=row.createCell(0);  
//设置单元格的值  
cell.setCellValue("学员考试成绩一览表"); 
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

sheet.setDefaultRowHeightInPoints(20);;//设置缺省列高
sheet.setDefaultColumnWidth(20);//设置缺省列宽  

HSSFCellStyle cellStyle=wb.createCellStyle();  
//设置填充方式(填充图案)  
cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);  
cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);  
  
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
 
//设置前景色  
cellStyle.setFillForegroundColor(HSSFColor.RED.index);  
//设置背景颜色  
cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);  
// 设置单元格底部的边框及其样式和颜色  
// 这里仅设置了底边边框，左边框、右边框和顶边框同理可设  
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);  
cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);  
  
//设置日期型数据的显示样式  
  
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 
cell.setCellStyle(cellStyle);  
  
//将样式应用到行，但有些样式只对单元格起作用  
row.setRowStyle(cellStyle); 
//在sheet里创建第二行  
HSSFRow row2=sheet.createRow(1);      
      //创建单元格并设置单元格内容  
      row2.createCell(0).setCellValue("姓名");  
      row2.createCell(1).setCellValue("班级");      
      row2.createCell(2).setCellValue("笔试成绩");  
row2.createCell(3).setCellValue("机试成绩");      
      //在sheet里创建第三行  
      HSSFRow row4=sheet.createRow(2);  
      row4.createCell(0).setCellValue("李明");  
      row4.createCell(1).setCellValue("As178");  
      row4.createCell(2).setCellValue(87);      
      row4.createCell(3).setCellValue(78); 
      //项目地址
      System.err.println(System.getProperty("user.dir"));
//输出Excel文件  
FileOutputStream output=new FileOutputStream(System.getProperty("user.dir")+"\\WebContent\\WEB-INF\\views\\developModule\\"+"xls\\hahaaaa.xls");  
wb.write(output);  
output.flush(); 

}


/**
* 配件费用统计
* @param request
* @param response
* @param name
* @param fieldNameSum
* @param jsonArray
* @return
* @throws FileNotFoundException
*/
public String ExportExcel_consts(HttpServletRequest request, HttpServletResponse response,String name, String fieldNameSum,List<Map<String, Object>> jsonArray ) throws FileNotFoundException {
//name 文件名
//fieldNameSum 类型名[逗号拼接的]
//jsonArray 内容


//创建HSSFWorkbook对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建HSSFSheet对象
HSSFSheet sheet = wb.createSheet(name);
//创建HSSFRow对象
HSSFRow row = sheet.createRow(0);
//创建HSSFCell对象
HSSFCell cell=row.createCell(0);


// 生成一个样式
HSSFCellStyle style = wb.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = wb.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = wb.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 生成一个样式
HSSFCellStyle style3 = wb.createCellStyle();
// 设置这些样式
style3.setFillForegroundColor(HSSFColor.RED.index);
style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font3 = wb.createFont();
font3.setColor(HSSFColor.VIOLET.index);
font3.setFontHeightInPoints((short) 24);
font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style3.setFont(font3);


// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString(""));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("");




//设置单元格的值
cell.setCellValue(name);
cell.setCellStyle(style3);
//设置标题占几行
sheet.addMergedRegion(new CellRangeAddress(0,0,0,6));


sheet.setDefaultRowHeightInPoints(20);;//设置缺省列高
sheet.setDefaultColumnWidth(20);//设置缺省列宽
try {
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
String[] fieldName = fieldNameSum.split(",");//分割出来的投档ID




for (int i = 0; i < fieldName.length; i++) {
//row2.createCell(i).setCellValue(""+fieldName[i]+"");


HSSFCell cell_title = row2.createCell(i);
cell_title.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(""+fieldName[i]+"");
cell_title.setCellValue(text);


}
for (int j = 0; j < jsonArray.size(); j++) {
HSSFRow row3=sheet.createRow(j+2);
for (int k = 0; k < fieldName.length; k++) {
//row3.createCell(k).setCellValue(""+jsonArray.getJSONObject(j).get(""+k+"")+"");


HSSFCell cell_content = row3.createCell(k);
cell_content.setCellStyle(style2);
HSSFRichTextString text = new HSSFRichTextString(""+jsonArray.get(j).get(""+k+"")+"");
cell_content.setCellValue(text);
}
}
// 获取上传路径
//String realPath = request.getRealPath("/WebContent/WEB-INF/views/developModule/xls");


// String savePath = request.getServletContext().getRealPath("/WEB-INF/views/developModule/xls/");
String savePath = request.getServletContext().getRealPath("/static/xls/");
logger.info("savePath->"+savePath);
//项目地址
// System.err.println(System.getProperty("user.dir"));
//输出Excel文件
//FileOutputStream output=new FileOutputStream(System.getProperty("user.dir")+"\\WebContent\\WEB-INF\\views\\developModule\\xls\\"+name+".xls");
FileOutputStream output=new FileOutputStream(savePath+"/"+name+".xls");
wb.write(output);
output.flush();
return "100";
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "101";//文件已经被打开或传入的数据有误
}
}
}