POI实现Excel文件上传下载,读/写数据库(poi) -

chenglinjava

浏览: 17688 次
性别:
来自: 北京

最近访客更多访客>>

baisetoufa

lsdc2004

woodding2008

lq881016

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

POI实现Excel文件上传下载,读/写数据库(poi)

博客分类：

百宝箱

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;   
    }
}