首页 > > 程序设计 > Java技术 >

Excel的读取和保存（POI）

2019-04-25 06:52:25来源：博客园阅读 ()

示例

Excel文件：

数据读取：

保存路径：

Jar包准备

下载地址：

链接：https://pan.baidu.com/s/1RZAwEsFwjKMlnYYGwHMfaA
提取码：h9mj

文件上传

<form action="servlet/HelloServlet" enctype="multipart/form-data" method="post">
　　<span style="white-space:pre"></span><input type="file" name="file"/>
　　<span style="white-space:pre"></span><input type="submit" value="提交"/>
</form>

Servlet代码

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
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;



public class HelloServlet extends HttpServlet{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
    
    /**
     * 保存或读取
     */
    @SuppressWarnings("deprecation")
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try{
            request.setCharacterEncoding("UTF-8");
            DiskFileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload upload = new ServletFileUpload(factory);
            List<FileItem> list = upload.parseRequest(request);
            ExcelHelper excelHelper = new ExcelHelper();
            for(int i=0;i<list.size();i++){
                FileItem fileItem = (FileItem) list.get(i);
                //文件保存
                excelHelper.excelDisk(fileItem, request.getRealPath(""));
                //数据读取
                excelHelper.parseExcelBean(fileItem, fileItem.getName().substring(fileItem.getName().lastIndexOf(".")+1));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            request.getRequestDispatcher("/index.jsp").forward(request, response);
        }
    }
}

Excel操作

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;

import org.apache.commons.fileupload.FileItem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHelper
{
    /**
     * 保存Excel文件
     * @param resource
     * @param path
     * @throws Exception
     */
    public void excelDisk(FileItem fileItem,String path) throws Exception{
        InputStream inputStream = fileItem.getInputStream();
        String fileName = fileItem.getName().substring(fileItem.getName().lastIndexOf("\\")+1);
        OutputStream outputStream = new FileOutputStream(path+"/"+fileName);
        byte[] bs = new byte[1024];
        while(inputStream.read(bs) != -1){
            outputStream.write(bs);
        }
        outputStream.flush();
        outputStream.close();
        inputStream.close();
    }
    
    /**
     * 读取Excel数据
     * @param resource
     * @param suffix
     * @throws Exception
     */
    public void parseExcelBean(FileItem fileItem,String suffix) throws Exception{
        Workbook workbook = null;
        if(suffix.equals("xls")){
            workbook = new HSSFWorkbook(fileItem.getInputStream());
        }else if(suffix.equals("xlsx")){
            workbook = new XSSFWorkbook(fileItem.getInputStream());
        }
        for(int i=0;i<workbook.getNumberOfSheets();i++){
            System.out.println("----------第"+(i+1)+"页----------");
            Sheet sheet = workbook.getSheetAt(i);
            if(sheet == null){
                continue;
            }
            for(int j=0;j<=sheet.getLastRowNum();j++){
                Row row = sheet.getRow(j);
                if(row == null){
                    continue;
                }
                for(int k=0;k<row.getLastCellNum();k++){
                    Cell cell = row.getCell(k);
                    if(cell == null){
                        continue;
                    }
                    System.out.print(getCellValue(cell)+"\t");
                }
                System.out.println();
            }
        }
    }
    
    @SuppressWarnings("deprecation")
    public String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}