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了
转载于:https://www.cnblogs.com/wufengxyz/archive/2011/12/29/2306606.html
相关资源：使用POI解析excel文件并写入数据库