poi是Apache基金会的开放源码函式库,提供API给Java程式对Microsoft Office格式档案读和写的功能。这里是对Excel的读取下载/上传实现;在一些需要导入导出数据时感觉比较有用;曾经在CRM中实现下载/上传联系人信息,不过当时不是自己负责的,总体来说下载比较容易实现,上传由于格式必须与数据库字段对应,显得有些麻烦;这里为了demo方便,没有与数据库交互,使用的是Servlet实现;
依赖包:
1.commons-fileupload
2.commons-io
3.poi
下载地址http://poi.apache.org/ http://commons.apache.org/
下面是代码实现
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <servlet> <servlet-name>PoiServlet</servlet-name> <servlet-class>com.wf.web.PoiServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>PoiServlet</servlet-name> <url-pattern>/poitest.jspx</url-pattern> </servlet-mapping> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> </web-app>
显示页面
index.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>POI测试</title> </head> <body> <a href="poitest.jspx?_m=poi_down">下载</a><br> <form action="poitest.jspx?_m=poi_upload" method="post" enctype="multipart/form-data"> <input type="file" name="file"> <input type="submit" value="submit"> </form> </body> </html>
Servlet端Java代码
PoiServlet.java
package com.wf.web; import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.poifs.filesystem.POIFSFileSystem; public class PoiServlet extends HttpServlet { private static final long serialVersionUID = 1L; public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String method = request.getParameter("_m"); if("poi_down".equals(method)){ poi_down(request,response); }else if("poi_upload".equals(method)){ poi_upload(request,response); } } private void poi_upload(HttpServletRequest request, HttpServletResponse response) { if(ServletFileUpload.isMultipartContent(request)){ DiskFileItemFactory factory = new DiskFileItemFactory(); factory.setSizeThreshold(1024*512); factory.setRepository(new File("D:/tempload")); ServletFileUpload fileUpload=new ServletFileUpload(factory); fileUpload.setFileSizeMax(10*1024*1024);//设置最大文件大小 try { @SuppressWarnings("unchecked") List<FileItem> items=fileUpload.parseRequest(request);//获取所有表单 for(FileItem item:items){ //判断当前的表单控件是否是一个普通控件 if(!item.isFormField()){ //是一个文件控件时 String excelFileName = new String(item.getName().getBytes(), "utf-8"); //获取上传文件的名称 //上传文件必须为excel类型,根据后缀判断(xls) String excelContentType = excelFileName.substring(excelFileName.lastIndexOf(".")); //获取上传文件的类型 System.out.println("上传文件名:"+excelFileName); System.out.println("文件大小:"+item.getSize()); System.out.println("\n---------------------------------------"); if(".xls".equals(excelContentType)){ POIFSFileSystem fileSystem = new POIFSFileSystem(item.getInputStream()); HSSFWorkbook workbook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workbook.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); int columns = row.getPhysicalNumberOfCells(); for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); String value = this.getCellStringValue(cell); System.out.print(value + "|"); } System.out.println("\n---------------------------------------"); } System.out.println("success!"); }else{ System.out.println("必须为excel类型"); } //顺便把文件保存到硬盘,防止重名 // String newName=new SimpleDateFormat("yyyyMMDDHHmmssms").format(new Date()); // File file = new File("d:/upload"); // if(!file.exists()){ // file.mkdir(); // } // item.write(new File("d:/upload/"+newName+excelContentType)); response.sendRedirect("index.jsp"); } } }catch (Exception e) { e.printStackTrace(); } } } private void poi_down(HttpServletRequest request, HttpServletResponse response) { response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=data.xls"); ServletOutputStream stream = null; try { stream = response.getOutputStream(); } catch (IOException e1) { e1.printStackTrace(); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中样式 HSSFSheet sheet = workbook.createSheet("我的联系人"); sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 5000); //创建表头(第一行) HSSFRow row = sheet.createRow(0); //列 HSSFCell cell = row.createCell(0); cell.setCellValue("姓名"); cell.setCellStyle(style); HSSFCell cell2 = row.createCell(1); cell2.setCellValue("电话"); cell2.setCellStyle(style); //创建数据行 for(int i =1;i<=20;i++) { HSSFRow newrow = sheet.createRow(i); newrow.createCell(0).setCellValue("tom"+i); newrow.createCell(1).setCellValue("135816****"+i); } try { workbook.write(stream); System.out.println("下载成功"); stream.flush(); stream.close(); } catch (Exception e) { e.printStackTrace(); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } //获取单元格内不同类型值 public String getCellStringValue(HSSFCell cell) { String cellValue = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); if(cellValue.trim().equals("")||cellValue.trim().length()<=0) cellValue=" "; break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue=" "; break; case HSSFCell.CELL_TYPE_BOOLEAN: break; case HSSFCell.CELL_TYPE_ERROR: break; default: break; } return cellValue; } }
上传后的Excel已经读入内存,可以与数据库交互,上传字段与数据库字段必须一致,否则会报错;实际项目一般都是先下载Excel模板之后,用户填写模板上传,模板中定义好数据格式;当然避免不了随便把一个文件后缀改为xls了