package com.ittax.core.util;

import java.util.List;

import javax.servlet.ServletOutputStream;

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.HSSFHeader;
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.ss.util.CellRangeAddress;

import com.ittax.nsfw.user.entity.User;

/**
* excel工具类,支持批量导出
* @author lizewu
*
*/
public class ExcelUtil {

/\*\*  
 \* 将用户的信息导入到excel文件中去  
 \* @param userList 用户列表  
 \* @param out 输出表  
 \*/  
public static void exportUserExcel(List<User> userList,ServletOutputStream out)  
{  
    try{  
        //1.创建工作簿  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        //1.1创建合并单元格对象  
        CellRangeAddress callRangeAddress = new CellRangeAddress(,,,);//起始行,结束行,起始列,结束列  
        //1.2头标题样式  
        HSSFCellStyle headStyle = createCellStyle(workbook,(short));  
        //1.3列标题样式  
        HSSFCellStyle colStyle = createCellStyle(workbook,(short));  
        //2.创建工作表  
        HSSFSheet sheet = workbook.createSheet("用户列表");  
        //2.1加载合并单元格对象  
        sheet.addMergedRegion(callRangeAddress);  
        //设置默认列宽  
        sheet.setDefaultColumnWidth();  
        //3.创建行  
        //3.1创建头标题行;并且设置头标题  
        HSSFRow row = sheet.createRow();  
        HSSFCell cell = row.createCell();

        //加载单元格样式  
        cell.setCellStyle(headStyle);  
        cell.setCellValue("用户列表");

        //3.2创建列标题;并且设置列标题  
        HSSFRow row2 = sheet.createRow();  
        String\[\] titles = {"用户名","账号","所属部门","性别","电子邮箱"};  
        for(int i=;i<titles.length;i++)  
        {  
            HSSFCell cell2 = row2.createCell(i);  
            //加载单元格样式  
            cell2.setCellStyle(colStyle);  
            cell2.setCellValue(titles\[i\]);  
        }

        //4.操作单元格;将用户列表写入excel  
        if(userList != null)  
        {  
            for(int j=;j<userList.size();j++)  
            {  
                //创建数据行,前面有两行,头标题行和列标题行  
                HSSFRow row3 = sheet.createRow(j+);  
                HSSFCell cell1 = row3.createCell();  
                cell1.setCellValue(userList.get(j).getName());  
                HSSFCell cell2 = row3.createCell();  
                cell2.setCellValue(userList.get(j).getAccount());  
                HSSFCell cell3 = row3.createCell();  
                cell3.setCellValue(userList.get(j).getDept());  
                HSSFCell cell4 = row3.createCell();  
                cell4.setCellValue(userList.get(j).isGender()?"男":"女");  
                HSSFCell cell5 = row3.createCell();  
                cell5.setCellValue(userList.get(j).getEmail());  
            }  
        }  
        //5.输出  
        workbook.write(out);  
        workbook.close();  
        //out.close();  
    }catch(Exception e)  
    {  
        e.printStackTrace();  
    }  
}

/\*\*  
 \*  
 \* @param workbook  
 \* @param fontsize  
 \* @return 单元格样式  
 \*/  
private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize) {  
    // TODO Auto-generated method stub  
    HSSFCellStyle style = workbook.createCellStyle();  
    style.setAlignment(HSSFCellStyle.ALIGN\_CENTER);//水平居中  
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER);//垂直居中  
    //创建字体  
    HSSFFont font = workbook.createFont();  
    font.setBoldweight(HSSFFont.BOLDWEIGHT\_BOLD);  
    font.setFontHeightInPoints(fontsize);  
    //加载字体  
    style.setFont(font);  
    return style;  
}  

}

//导出用户列表
public void exportExcel()
{
try
{
//1.查找用户列表
userList = userService.findObjects();
//2.导出
HttpServletResponse response = ServletActionContext.getResponse();
//这里设置的文件格式是application/x-excel
response.setContentType("application/x-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xls".getBytes(), "ISO-8859-1"));
ServletOutputStream outputStream = response.getOutputStream();
userService.exportExcel(userList, outputStream);
if(outputStream != null)
outputStream.close();
}catch(Exception e)
{
e.printStackTrace();
}
}

    public String importExcel()  
    {  
        if(userExcel!= null)  
        {  
            //判断是否是Excel文件  
            if(userExcelFileName.matches("^.+\\\\.(?i)((xls)|(xlsx))$"))  
            {  
                userService.importExcel(userExcel, userExcelFileName);  
            }  
        }  
        return"list";  
    }

//导出用户列表
public void exportExcel()
{
try
{
//1.查找用户列表
userList = userService.findObjects();
//2.导出
HttpServletResponse response = ServletActionContext.getResponse();
//这里设置的文件格式是application/x-excel
response.setContentType("application/x-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xls".getBytes(), "ISO-8859-1"));
ServletOutputStream outputStream = response.getOutputStream();
userService.exportExcel(userList, outputStream);
if(outputStream != null)
outputStream.close();
}catch(Exception e)
{
e.printStackTrace();
}
}

    public String importExcel()  
    {  
        if(userExcel!= null)  
        {  
            //判断是否是Excel文件  
            if(userExcelFileName.matches("^.+\\\\.(?i)((xls)|(xlsx))$"))  
            {  
                userService.importExcel(userExcel, userExcelFileName);  
            }  
        }  
        return"list";  
    }

下载完后，打开“poi-bin-3.15-20160924.tar.gz”获取操作excel需要的jar包，并将这些jar包复制到项目中。对于只操作2003 及以前版本的excel，只需要poi-3.15.jar ，如果需要同时对2007及以后版本进行操作则需要复制

poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar

以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar(但不知为何,我下的jar文件中没有dom4j.jar)这个文件,还是加上dom4j.jar,防止报错.