使用POI操作Excel

首先要下载所需jar包，

官网：http://poi.apache.org ，POI支持office的所有版本

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

poi-ooxml-3.10.1-20140818.jar，

poi-ooxml-schemas-3.10.1-20140818.jar，以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar，dom4j-1.6.1.jar。

在POI包中有如下几个主要对象和excel的几个对象对应(针对03版本)：

HSSFWorkbook	Excel 工作簿workbook
HSSFSheet	Excel 工作表 sheet
HSSFRow	Excel 行
HSSFCell	Excel 单元格

POI 也能对07以后的excel版本进行读写，读写方法和读写03版是一样的，只是对象名称变了；原来各对象的开头字母H变为X，操作方式不变。

1、 Excel 的工作簿对应POI的XSSFWorkbook对象；

2、 Excel 的工作表对应POI的XSSFSheet对象；

3、 Excel 的行对应POI的XSSFRow对象；

4、 Excel 的单元格对应POI的XSSFCell对象。

在“poi-bin-3.10.1-20140818poi-3.10.1docsapidocs”目录中，点击“index.html”查看POI api文档，我们可以查询POI中所有这些对象的属性和方法。

下面是实例代码演示：

导出Excel的功能

package cn.itcast.core.util;


import java.io.IOException;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.util.CellRangeAddress;


import cn.itcast.nsfw.user.entity.User;

public class ExcelUtil {
    public static void exportUserExcel(List<User> userList,ServletOutputStream outputStream){
        try {
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);
            //1.2头标题样式
            HSSFCellStyle style1 = createCellStyle(workbook,(short)16);
            //1.3列标题样式
            HSSFCellStyle style2 = createCellStyle(workbook, (short)13);
            
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet();
            //2.1加载合并单元格对象
            sheet.addMergedRegion(cellRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(25);
            //3.创建行
            //3.1创建头标题行
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cell1 = row1.createCell(0);
            cell1.setCellStyle(style1);
            cell1.setCellValue("用户列表");
            //3.2创建列标题行
            HSSFRow row2 = sheet.createRow(1);
            String title[]={"用户名","账号","所属部门","性别","电子邮箱"};
            for (int i = 0; i < title.length; i++) {
                HSSFCell cell2 = row2.createCell(i);
                cell2.setCellStyle(style2);
                cell2.setCellValue(title[i]);
            }
            //4.操作单元格,将用户列表写入excel
            if(userList!=null){
                for (int i = 0; i < userList.size(); i++) {
                    HSSFRow row = sheet.createRow(2+i);
                    HSSFCell cell11 = row.createCell(0);
                    cell11.setCellValue(userList.get(i).getName());
                    HSSFCell cell12 = row.createCell(1);
                    cell12.setCellValue(userList.get(i).getAccount());
                    HSSFCell cell13 = row.createCell(2);
                    cell13.setCellValue(userList.get(i).getDept());
                    HSSFCell cell14 = row.createCell(3);
                    cell14.setCellValue(userList.get(i).isGender()?"男":"女");
                    HSSFCell cell15 = row.createCell(4);
                    cell15.setCellValue(userList.get(i).getEmail());
                }
            }
            
            //5.输出
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize){
        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;
    }
}

对应action

//导出用户列表
    public void exportExcel(){
        try {
            //1.查找用户列表
            userList=userService.findObjects();
            //2.导出
            HttpServletResponse response = ServletActionContext.getResponse();
            response.setContentType("application/vnd.ms-excel");
            //attachment表示往response里附加一个文件，如果不转码文件名将会乱码
            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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

导入Excel的功能

//导入用户列表
    public String importExcel(){
        //获取Excel文件
        if(userExcel!=null){
            //是否是excel
            if(userExcelFileName.matches("^.+\.(?i)((xls)|(xlsx))$")){
                //导入
                userService.importExcel(userExcel, userExcelFileName);
            }
        }
        return "list";
    }

public void importExcel(File userExcel, String userExcelFileName) {
        // TODO Auto-generated method stub
        try {
            FileInputStream fileInputStream = new FileInputStream(userExcel);
            boolean is03Excel = userExcelFileName.matches("^.+\.(?i)(xls)$");
            //1.读取工作簿
            Workbook workbook=is03Excel?new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);
            if(sheet.getPhysicalNumberOfRows()>2){
                User user=null;
                for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
                    Row row = sheet.getRow(i);
                    user=new User();
                    Cell cell0 = row.getCell(0);
                    user.setName(cell0.getStringCellValue());
                    Cell cell1 = row.getCell(1);
                    user.setAccount(cell1.getStringCellValue());
                    Cell cell2 = row.getCell(2);
                    user.setDept(cell2.getStringCellValue());
                    Cell cell3 = row.getCell(3);
                    user.setGender(cell3.getStringCellValue().equals("男"));
                    //手机号
                    String mobile = "";
                    Cell cell4 = row.getCell(4);
                    try {
                        mobile = cell4.getStringCellValue();
                    } catch (Exception e) {
                        double dMobile = cell4.getNumericCellValue();
                        mobile = BigDecimal.valueOf(dMobile).toString();
                    }
                    user.setMobile(mobile);
                    //电子邮箱
                    Cell cell5 = row.getCell(5);
                    user.setEmail(cell5.getStringCellValue());
                    //生日
                    Cell cell6 = row.getCell(6);
                    if(cell6.getDateCellValue() != null){
                        user.setBirthday(cell6.getDateCellValue());
                    }
                    //默认用户密码为 123456
                    user.setPassword("123456");
                    //默认用户状态为 有效
                    user.setState(User.USER_STATE_VALID);
                    
                    save(user);
                }
                workbook.close();
                fileInputStream.close();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }