springMVC前台ajax上传excel文件并且解析其中内容

一、前端页面结构及逻辑

ajax文件上传可以参考之前的文件
formData上传文件

二、后台的相关处理

java解析excel文件有两种方式，一种是jxl一种是poi。
jxl和poi最明显的区别是：前者只能处理后缀名为.xls的文件（2003版的）；后者可以处理后缀名为.xls和.xlsx（2007版）

1、利用jxl解析excel的相关代码

• pom.xml中的配置

<dependency>    <groupId>net.sourceforge.jexcelapi</groupId>    <artifactId>jxl</artifactId>    <version>2.6.12</version></dependency>

• Controller中的配置

    //EXCEL相关    @ResponseBody    @RequestMapping(value = "/uploadExcel", method = RequestMethod.POST)    public void uploadExcel(@RequestParam(value = "fileinfo", required = false) MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws IOException {        //此方法可参考文章上述链接（FileTools.getFileInfo()）        String path=FileTools.getFileInfo(request, response, file);        File file1=new File(path)        ExcelTools.readExcelWrite2TXT(file1);s    }

• 工具类中的配置

package com.lawsiji.txtcheck.common;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;import java.io.*;public class ExcelTools {    /*******************EXCEL文件相关方法**********************/    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象    public static void readExcelWrite2TXT(File file) {        // 创建文件输出流        FileWriter fw = null;        PrintWriter out = null;        try {            // 指定生成txt的文件路径            String fileName = file.getName().replace(".xls", "");            fw = new FileWriter(file.getParent() + "/" + fileName + ".txt");            out = new PrintWriter(fw);            // 创建输入流，读取Excel            InputStream is = new FileInputStream(file.getAbsolutePath());            // jxl提供的Workbook类            Workbook wb = Workbook.getWorkbook(is);            // Excel的页签数量            int sheet_size = wb.getNumberOfSheets();            for (int index = 0; index < sheet_size; index++) {                // 每个页签创建一个Sheet对象                Sheet sheet = wb.getSheet(index);                // sheet.getRows()返回该页的总行数                for (int i = 0; i < sheet.getRows(); i++) {                    // sheet.getColumns()返回该页的总列数                    for (int j = 0; j < sheet.getColumns(); j++) {                        String cellinfo = sheet.getCell(j, i).getContents();                        // 将从Excel中读取的数据写入到txt中                        out.println(cellinfo);                    }                }            }        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (BiffException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            try {                // 记得关闭流                out.close();                fw.close();                // 由于此处用到了缓冲流，如果数据量过大，不进行flush操作，某些数据将依旧                // 存在于内从中而不会写入文件，此问题一定要注意                out.flush();            } catch (IOException e) {                e.printStackTrace();            }        }    }}

2、利用poi解析excel的相关代码

• pom.xml中的配置

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.17</version></dependency>

• Controller中的配置

    //EXCEL相关    @ResponseBody    @RequestMapping(value = "/uploadExcel", method = RequestMethod.POST)    public void uploadExcel(@RequestParam(value = "fileinfo", required = false) MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws IOException {        //此方法可参考文章上述链接（FileTools.getFileInfo()）        String path=FileTools.getFileInfo(request, response, file);        String s=ExcelPoiTools.read(path);        System.out.println(s);    }

• 工具类中的配置
  文件名ExcelPoiTools.class

package com.lawsiji.txtcheck.common;import java.io.FileInputStream;import java.io.IOException;import java.text.NumberFormat;import org.apache.poi.hssf.usermodel.HSSFCell;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.ss.usermodel.Cell;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;public class ExcelPoiTools {    public static String read(String filePath) {        String content="";        try {            /* 验证文件是否合法 */            if (!WDWUtil.validateExcel(filePath)) {                System.out.println("excel文件格式错误！");            }else{                /* 判断文件的类型，是2003还是2007 */                if (WDWUtil.isExcel2007(filePath)) {                    content = readEXCEL2007(filePath);                } else if (WDWUtil.isExcel2003(filePath)) {                    content = readEXCEL(filePath);                }            }        } catch (Exception ex) {            ex.printStackTrace();        }        return content;    }    // 读取xls文件    public static String readEXCEL(String file) throws IOException {        StringBuilder content = new StringBuilder();        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));// 创建对Excel工作簿文件的引用        for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {            if (null != workbook.getSheetAt(numSheets)) {                HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet                for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {                    if (null != aSheet.getRow(rowNumOfSheet)) {                        HSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 获得一个行                        for (short cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {                            if (null != aRow.getCell(cellNumOfRow)) {                                HSSFCell aCell = aRow.getCell(cellNumOfRow);// 获得列值                                if (convertCell(aCell).length() > 0) {                                    content.append(convertCell(aCell));                                }                            }                            content.append("\n");                        }                    }                }            }        }        return content.toString();    }    // 读取xlsx文件    public static String readEXCEL2007(String file) throws IOException {        StringBuilder content = new StringBuilder();        XSSFWorkbook workbook = new XSSFWorkbook(file);        for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {            if (null != workbook.getSheetAt(numSheets)) {                XSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet                for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {                    if (null != aSheet.getRow(rowNumOfSheet)) {                        XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 获得一个行                        for (short cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {                            if (null != aRow.getCell(cellNumOfRow)) {                                XSSFCell aCell = aRow.getCell(cellNumOfRow);// 获得列值                                if (convertCell(aCell).length() > 0) {                                    content.append(convertCell(aCell));                                }                            }                            content.append("\n");                        }                    }                }            }        }        return content.toString();    }    private static String convertCell(Cell cell) {        NumberFormat formater = NumberFormat.getInstance();        formater.setGroupingUsed(false);        String cellValue = "";        if (cell == null) {            return cellValue;        }        switch (cell.getCellType()) {            case HSSFCell.CELL_TYPE_NUMERIC:                cellValue = formater.format(cell.getNumericCellValue());                break;            case HSSFCell.CELL_TYPE_STRING:                cellValue = cell.getStringCellValue();                break;            case HSSFCell.CELL_TYPE_BLANK:                cellValue = cell.getStringCellValue();                break;            case HSSFCell.CELL_TYPE_BOOLEAN:                cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString();                break;            case HSSFCell.CELL_TYPE_ERROR:                cellValue = String.valueOf(cell.getErrorCellValue());                break;            default:                cellValue = "";        }        return cellValue.trim();    }}

文件名WDWUtil.class ——用来判断传入文件的格式

package com.lawsiji.txtcheck.common;public class WDWUtil {    /*     * @描述：是否是2003的excel，返回true是2003     * @param filePath     * @return     */    public static boolean isExcel2003(String filePath)  {        return filePath.matches("^.+\\.(?i)(xls)$");    }    /*     * @描述：是否是2007的excel，返回true是2007     * @param filePath     * @return     */    public static boolean isExcel2007(String filePath)  {        return filePath.matches("^.+\\.(?i)(xlsx)$");    }    /*     * 验证是否是EXCEL文件     * @param filePath     * @return     */    public static boolean validateExcel(String filePath){        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){            return false;        }        return true;    }}

参考：http://blog.csdn.net/lin9118/article/details/9310135

---

此文章是自己学习时的笔记，其中有些术语可能不太准确，只供参考，如有问题欢迎赐教！