Excel文件的上传下载解析详解

最新推荐文章于 2024-04-23 19:30:26 发布

Action_Wang

最新推荐文章于 2024-04-23 19:30:26 发布

阅读量3.3k

点赞数

分类专栏： JAVA基础篇文章标签： Excel文件上传、下载

本文链接：https://blog.csdn.net/w893932747/article/details/79866654

版权

JAVA基础篇专栏收录该内容

102 篇文章 3 订阅

订阅专栏

Excel有两个版本Excel2003和Excel2007，“2003的后缀.xls”，“2007的后缀名.xlsx”

由于两者实现机制不同，当实现文件上传时两种文件不能通过一套代码实现

HSSFWorkbook只能操作excel2003以下版本,

XSSFWorkbook只能操作excel2007以上版本，

--------------------------------------------------------------------------------------------------------

Excel文件操作所需jar包

上传2007时会报错java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlOptions

原因：缺少xmlbeans-2.6.0.jar，需要把方框中所有的jar包全部导入

页面Excel.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>

----------------------------------------------------------------------------------------------------------------------------------

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0"
    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_3_0.xsd">
  <display-name></display-name>
 
  <servlet>
    <servlet-name>PoiServlet</servlet-name>
    <servlet-class>top.wj.PoiServlet</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>PoiServlet</servlet-name>
    <url-pattern>/poitest.jspx</url-pattern>
  </servlet-mapping>
</web-app>

----------------------------------------------------------------------------------------------------------------------------------

java代码：

package top.wj.web;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
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.HSSFChart.HSSFChartType;
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;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import sun.misc.BASE64Encoder;

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:/test"));
            //创建文件上传核心对象
            ServletFileUpload fileUpload=new ServletFileUpload(factory);
            //设置最大文件大小
            fileUpload.setFileSizeMax(10*1024*1024);
            try {
                @SuppressWarnings("unchecked")
                //获取所有表单，解析request
                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类对象可以把Excel文件作为数据流来进行传入传出。
                            POIFSFileSystem fileSystem = new POIFSFileSystem(item.getInputStream());
                            //创建一个workbook
                            HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
                            //打开需要解析的Sheet工作表,读取第一个工作表（下表从0开始）
                            HSSFSheet sheet = workbook.getSheetAt(0);
                            //获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；
                            int rows = sheet.getPhysicalNumberOfRows();
                            for (int i = 0; i < rows; i++) {
                                HSSFRow row = sheet.getRow(i);
                                //获取有记录的列数，即：最后有数据的列是第n列，前面有m列是空列没数据，则返回n-m；
                                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 if(".xlsx".equals(excelContentType)){
                        	//POIFSFileSystem类对象可以把Excel文件作为数据流来进行传入传出。
//                            POIFSFileSystem fileSystem = new POIFSFileSystem(item.getInputStream());
                            //创建一个workbook
//                            HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
//                            HSSFWorkbook workbook = new XSSFWorkbook(fileSystem);  //2007+
                        	InputStream inputStream = item.getInputStream();
                            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
                            
                            //打开需要解析的Sheet工作表,读取第一个工作表（下表从0开始）
                            XSSFSheet sheet = workbook.getSheetAt(0);
                            //获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；
                            int rows = sheet.getPhysicalNumberOfRows();
                            for (int i = 0; i < rows; i++) {
                                XSSFRow row = sheet.getRow(i);
                                //获取有记录的列数，即：最后有数据的列是第n列，前面有m列是空列没数据，则返回n-m；
                                int columns = row.getPhysicalNumberOfCells();
                                for (int j = 0; j < columns; j++) {
                                    XSSFCell cell = row.getCell(j);
                                    String value = this.getCellStringValueXssfCell(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();
        }
        
        
        
        
        
        
        
        //下载的第二种示例：

//    	//	1）浏览器发送请求给服务器（用户要下载的文件名）
//    		String fileName = request.getParameter("fileName");
//    		fileName = new String(fileName.getBytes("iso-8859-1"),"utf-8");
//    		
//    	//	2）服务器加载用户要下载的文件数据。
//    		String realPath = this.getServletContext().getRealPath("/upload");
//    		
//    		File file = new File(realPath,fileName);
//    		
//    	//	3）通知浏览器以下载的方式请求资源
//    			//a.Content-Type 设置文件媒体格式
//    		response.setContentType(this.getServletContext().getMimeType(fileName));
//    		//处理中文文件名乱码
//    		String header = request.getHeader("User-Agent");
//    		if(header.contains("Firefox")){
//    			//表示当前是火狐
//    			BASE64Encoder base64Encoder = new BASE64Encoder();
//    			fileName = "=?utf-8?B?"	+ base64Encoder.encode(fileName.getBytes("utf-8")) + "?=";
//
//    		}else{
//    			//谷歌
//    			fileName = URLEncoder.encode(fileName,"utf-8");
//    			
//    		}
//    			//b.Content-Disposition  设置要被下载的文件名
//    		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
//    		
//    	// 	4）使用IO技术，将数据发送（使用response对象发送数据）
//    			//a.获取输入流
//    		FileInputStream in = new FileInputStream(file);
//    			//b.获取输出流
//    		ServletOutputStream out = response.getOutputStream();
//    		//缓冲区
//    		byte[] buf = new byte[8192];
//    		int len = 0;
//    		while(( len = in.read(buf))!=-1){
//    			
//    			out.write(buf, 0, len);
//    		}
//    		in.close();
    		
    	}


    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;   
    }
    
    
    //获取单元格内不同类型值
    public String getCellStringValueXssfCell(XSSFCell 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;   
    }
}

Action_Wang

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
Excel文件的上传下载解析详解

Excel有两个版本Excel2003和Excel2007，“2003的后缀.xls”，“2007的后缀名.xlsx”由于两者实现机制不同，当实现文件上传时两种文件不能通过一套代码实现HSSFWorkbook只能操作excel2003以下版本,XSSFWorkbook只能操作excel2007以上版本，------------------------------------------------...
复制链接

扫一扫

专栏目录