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

逃离我推掉我的手 2022-05-27 01:38 320阅读 0赞

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

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

#### HSSFWorkbook只能操作excel2003以下版本, ####

#### XSSFWorkbook只能操作excel2007以上版本， ####

#### \--------------------------------------------------------------------------------------------------------    ####

Excel文件操作所需jar包

![70][]

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

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

![70 1][]

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

[70]: /images/20220527/aefebe570a934fda91011b30233e39d9.png
[70 1]: /images/20220527/53aa531a3a114fe3b843f2b32be6a86a.png