Excel上传读取下载备份

Maven依赖：pom.xml

<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>1.3.2</version>
</dependency>
        <dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.2.2</version>
</dependency>
    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>

代码：

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
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.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;
import org.springframework.util.MultiValueMap;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

public class PriceHandler extends javax.servlet.http.HttpServlet {

    private static final long serialVersionUID = 1205360827094135801L;

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,
                                                                                   IOException {

        String[] names = upload(request, response);
        if (names == null) {
            return;
        }
        String fileName = names[0];
        String[] params = new String[] { "submain", "kind1", "kind2", "kind3", "vender", "card", "price", "unit",
                "saleMode", "useLevel", "trait", "process", "saleNumber", "minOrder", "isTax", "createTime",
                "expireDay" };
        // 解读excel并操作数据
        List<Map<String, String>> undoList = execExcel(fileName, params);
        // 删除临时上传文件
        File file = new File(fileName);
        file.deleteOnExit();
        // 导入结果处理
        this.execResult(response, undoList, names[1], params);
    }

    /**
     * 文件上传方法
     *
     * @param request
     * @param response
     * @param fileName
     * @return
     * @throws IOException
     */
    private String[] upload(HttpServletRequest request, HttpServletResponse response)
                                                                                                           throws IOException {
        // 文件上传处理
        // 创建一个工厂类的实例，该实例为解析器提供了缺省的配置
        DiskFileItemFactory factory = new DiskFileItemFactory();
        // 创建一个解析器
        ServletFileUpload sfu = new ServletFileUpload(factory);
        // 使用解析器解析
        List<FileItem> items = null;
        try {
            items = sfu.parseRequest(request);
        } catch (FileUploadException e1) {
            e1.printStackTrace();
        }
        FileItem item = null;
        // 遍历items集合
        for (int i = 0; i < items.size(); i++) {
            FileItem tempItem = items.get(i);
            // 读表单中的数据时要区分表单域的类型
            if (tempItem.isFormField()) {
                // 普通表单域
                String username = tempItem.getString();
                System.out.println(username);
            } else {
                item = tempItem;
                break;
            }
        }
        // 文件上传表单域
        ServletContext sctx = getServletContext();
        String path = sctx.getRealPath("");
        // 获得客户端上传的文件名
        String clientName = item.getName();
        String suffix = null;
        if (clientName.endsWith(".xlsx")) {
            suffix = ".xlsx";
        } else if (clientName.endsWith(".xls")) {
            suffix = ".xls";
        } else {
            response.setContentType("text/html;charset=utf-8");
            response.getOutputStream().write("您上传的不是Excel格式的文件!".getBytes("utf-8"));
            return null;
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");
        // 建立系统里面的新文件名
        String filePath = path + "/" + "price" + format.format(new Date()) + suffix;
        File file = new File(filePath);
        try {
            item.write(file);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        System.out.println("--上传文件完成!FilePath:" + path + "/" + clientName);
        System.out.println("上传文件完成!" + filePath);
        return new String[] { filePath, clientName };
    }

    /**
     * 解析读取excel并操作数据
     *
     * @param filePath
     * @param params
     * @return
     * @throws IOException
     */
    private List<Map<String, String>> execExcel(String filePath, String[] params) throws IOException {
        boolean isE2007 = false; // 判断是否是excel2007格式
        if (filePath.endsWith("xlsx")) {
            isE2007 = true;
        }

        InputStream input = new FileInputStream(filePath); // 建立输入流
        Workbook wb = null;
        // 根据文件格式(2003或者2007)来初始化
        if (isE2007) {
            wb = new XSSFWorkbook(input);
        } else {
            wb = new HSSFWorkbook(input);
        }
        Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
        Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器
        List<Map<String, String>> undoList = new ArrayList<Map<String, String>>();
        int mark = 0;
        while (rows.hasNext()) {
            mark++;
            Row row = rows.next(); // 获得行数据
            if (mark == 1) {// 不写入第一行
                continue;
            }
            System.out.println("Row #" + row.getRowNum()); // 获得行号从0开始
            Map<String, String> map = this.coverRowToMap(row, params);
            // 调用处理
            Map<String, String> resultMap = map;// 调用方法

            if (resultMap != null) {
                undoList.add(resultMap);
            }
        }
        return undoList;
    }

    /**
     * 处理excel解析结果
     *
     * @param response
     * @param undoList
     * @param clientName
     * @param params
     * @throws IOException
     */
    private void execResult(HttpServletResponse response, List<Map<String, String>> undoList, String clientName,
                            String[] params) throws IOException {
        if (undoList.size() > 0) {
            // 组装下载文件名
            SimpleDateFormat format = new SimpleDateFormat("[yyyy-MM-dd HH:mm:ss]");
            String dateString = format.format(new Date());
            String undoMark = "[未导入的数据,请修正再导入]";
            StringBuffer downloadBuffer = new StringBuffer(clientName);
            if (downloadBuffer.indexOf(undoMark) >= 0
                && downloadBuffer.length() >= undoMark.length() + dateString.length()) {
                downloadBuffer.replace(0, undoMark.length() + dateString.length(), undoMark + dateString);
            } else if (downloadBuffer.indexOf(undoMark) >= 0) {
                downloadBuffer.insert(undoMark.length(), dateString);
            } else {
                downloadBuffer.insert(0, undoMark + dateString);
            }
            String downloadName = downloadBuffer.toString();
            response.reset();
            response.setContentType("bin");
            response.addHeader("Content-Disposition", "attachment; filename=\""
                                                      + new String(downloadName.getBytes(), "ISO8859-1") + "\"");
            OutputStream os = response.getOutputStream();
            Workbook outWb = new XSSFWorkbook();
            Sheet outSheet = outWb.createSheet("sheet1");
            String titles[] = new String[] {};
            this.coverArrayToRow(outSheet, 0, titles);
            for (int k = 0; k < undoList.size(); k++) {
                this.coverMapToRow(outSheet, k + 1, undoList.get(k), params);
            }
            outWb.write(os);
            os.close();
        } else {
            // 跳转到成功提示页面
            response.setContentType("text/html;charset=utf-8");
            response.getOutputStream().write("上传完成!".getBytes("utf-8"));
        }
    }

    /**
     * 从excel的行Row转换为Map
     *
     * @param row
     * @param params
     * @return
     */
    private Map<String, String> coverRowToMap(Row row, String[] params) {
        if (row == null) {
            return null;
        }
        Map<String, String> map = new HashMap<String, String>();
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < params.length; i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                continue;
            }
            System.out.println("Cell #" + cell.getColumnIndex());
            switch (cell.getCellType()) { // 根据cell中的类型来输出数据
                case HSSFCell.CELL_TYPE_NUMERIC:
                    String v=cell.getNumericCellValue()+"";
                    if(DateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();  
                        v = simpleDateFormat.format(date);
                    }
                    map.put(params[i], v);
                    System.out.println(cell.getNumericCellValue()+"@@@@Numberic:value:"+v);
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    String value = cell.getStringCellValue();
                    if (value == null || "".equals(value)) {
                        break;
                    }
                    map.put(params[i], value);
                    System.out.println(cell.getStringCellValue()+"@@@@String");
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    boolean boolValue = cell.getBooleanCellValue();
                    map.put(params[i], String.valueOf(boolValue));
                    System.out.println(cell.getBooleanCellValue()+"@@@@BOOL");
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formulaValue = cell.getCellFormula();
                    map.put(params[i], formulaValue);
                    System.out.println(cell.getCellFormula()+"@@@@FORMULA");
                    break;
                default:
                    if(DateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();  
                        value = simpleDateFormat.format(date);
                    }
                    System.out.println("unsuported sell type");
                    break;
            }
        }
        return map;
    }

    /**
     * 从Map初始化为Excel行Row
     *
     * @param sheet
     * @param index
     * @param map
     * @param params
     */
    private void coverMapToRow(Sheet sheet, int index, Map<String, String> map, String[] params) {
        Row row = sheet.createRow(index);
        for (int i = 0; i < params.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(map.get(params[i]));
        }
        Cell cell = row.createCell(params.length);
        cell.setCellValue(map.get("reason"));
    }

    /**
     * 转换数组为excel中的行
     *
     * @param sheet
     * @param index
     * @param params
     */
    private void coverArrayToRow(Sheet sheet, int index, String[] params) {
        Row row = sheet.createRow(index);
        for (int i = 0; i < params.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(params[i]);
        }
    }
    //spring mvc的上传
    private void t(HttpServletRequest request, HttpServletResponse response) throws ServletException,
    IOException {
     
        CommonsMultipartResolver multipartResolver=null;
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;   
        if (multipartResolver.isMultipart(multipartRequest)){  //判断 request 是否有文件上传,即多部分请求...  
             // srcfname 是指 文件上传标签的 name=值  
             MultiValueMap<String, MultipartFile> multfiles = multipartRequest.getMultiFileMap();
             for(String srcfname:multfiles.keySet()){
                 MultipartFile mfile = multfiles.getFirst(srcfname);
                 try {
                    mfile.transferTo(new File("d:"+File.separator+"upload_temp"+File.separator+mfile.getOriginalFilename()));
                } catch (IllegalStateException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
             }
             MultipartFile t=null;
             
        }  
        
       
    }
}