excle导出使用poi

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(0,0,0,4);//起始行,结束行,起始列,结束列
            //1.2头标题样式
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
            //1.3列标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("用户列表");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(25);
            //3.创建行
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
        
            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("用户列表");
            
            //3.2创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            String[] titles = {"用户名","账号","所属部门","性别","电子邮箱"};
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            
            
            //4.操作单元格;将用户列表写入excel
            if(userList != null)
            {
                for(int j=0;j<userList.size();j++)
                {
                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j+2);
                    HSSFCell cell1 = row3.createCell(0);
                    cell1.setCellValue(userList.get(j).getName());
                    HSSFCell cell2 = row3.createCell(1);
                    cell2.setCellValue(userList.get(j).getAccount());
                    HSSFCell cell3 = row3.createCell(2);
                    cell3.setCellValue(userList.get(j).getDept());
                    HSSFCell cell4 = row3.createCell(3);
                    cell4.setCellValue(userList.get(j).isGender()?"男":"女");
                    HSSFCell cell5 = row3.createCell(4);
                    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,防止报错.