首先要下载所需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-20140818\poi-3.10.1\docs\apidocs”目录中，点击“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 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();
}
}
