上传Excel数据导入到数据库

在项目中用到了excel表格导入到数据库，觉得很实用，分享给和我一样码代码的码农们

想把上传的文件导入到数据库中，首先要先获取到该文件在服务器中的路径。

controller.java

String fileNames = filePath.replace("\\", "/");
    File file = new File(fileNames);
    InputStream is;

try {
is = new FileInputStream(file);
System.out.println(is);
System.out.println(is.available());
ExcelHelper help = new ExcelHelper();
    List<String> temp = help.exportListFromExcel(is, fileNames, 0);
    if(temp.size()==0){
   
    }else if(temp.get(0).indexOf("error")>-1){
   
    }
    headcheckRegisterService.AssessExcel(temp);//添加到数据库
    } catch (IOException e) {
e.printStackTrace();
}

ExcelHelper.java

package com.roots.smp.manage.util;


import java.io.File;  
import java.io.FileInputStream;  
import java.io.IOException;  
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;




import org.apache.commons.io.FilenameUtils;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;  
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 2003 
     */  
    private final static String XLS = "xls";  
    /** 
     * Excel 2007 
     */  
    private final static String XLSX = "xlsx";  
    /** 
     * 分隔符 
     */  
    private final static String SEPARATOR = "|";  
  
    /** 
     * 由Excel文件的Sheet导出至List 
     *  
     * @param file 
     * @param sheetNum 
     * @return 
     */  
    public static List<String> exportListFromExcel(File file, int sheetNum)  
            throws IOException {  
        return exportListFromExcel(new FileInputStream(file),  
                FilenameUtils.getExtension(file.getName()), sheetNum);  
    }  
  
    /** 
     * 由Excel流的Sheet导出至List 
     *  
     * @param is 
     * @param extensionName 
     * @param sheetNum 
     * @return 
     * @throws IOException 
     */  
    public static List<String> exportListFromExcel(InputStream is,String extensionName, int sheetNum) throws IOException {  
  
        Workbook workbook = null;  
        String end = extensionName.substring(extensionName.lastIndexOf(".")+1, extensionName.length()).toLowerCase();
        if (end.equals(XLS)) {  
            workbook = new HSSFWorkbook(is);  
        } else if (end.equals(XLSX)) {  
            workbook = new XSSFWorkbook(is);  
        }
        return exportListFromExcel(workbook, sheetNum);  
    }  
  
    /** 
     * 由指定的Sheet导出至List 
     *  
     * @param workbook 
     * @param sheetNum 
     * @return 
     * @throws IOException 
     */  
    private static List<String> exportListFromExcel(Workbook workbook,  int sheetNum) {
    SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
        Sheet sheet = workbook.getSheetAt(sheetNum);
        // 解析公式结果  
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
        List<String> list = new ArrayList<String>();
        StringBuffer errorStr = new StringBuffer();
        int minRowIx = sheet.getFirstRowNum();
        int maxRowIx = sheet.getLastRowNum();
        for (int rowIx = minRowIx+1; rowIx <= maxRowIx; rowIx++) {
            Row row = sheet.getRow(rowIx);
            StringBuilder sb = new StringBuilder();
           /* int minColIx = row.getFirstCellNum();*/
            int minColIx =0;
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx <= maxColIx-1; colIx++) {
                Cell cell = row.getCell(new Integer(colIx));
                //String Scell=cell.toString();
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                errorStr.append("error:EXCEL文件第[").append(rowIx+1).append("]行的第[").append(colIx+1).append("]列为空!<br>");
                continue;
                } /*else if(cell.toString().length()>25){
                errorStr.append("error:EXCEL文件第[").append(rowIx+1).append("]行的第[").append(colIx+1).append("]列长度大于25位字符!<br>");
                continue;
                } else if(colIx==4){
                if(MathTools.isNumeric(cell.toString())==false){
                errorStr.append("error:EXCEL文件第[").append(rowIx+1).append("]行的第[").append(colIx+1).append("]列金额不是数字！<br>");
                }
                }*/
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了  
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html  
                switch (cellValue.getCellType()) { 
                case Cell.CELL_TYPE_BOOLEAN:
                    sb.append(SEPARATOR + cellValue.getBooleanValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                sb.append(SEPARATOR + df.format(cell.getDateCellValue()));
}else {
sb.append(SEPARATOR + cellValue.getNumberValue());
}
                    break;
                case Cell.CELL_TYPE_STRING:
                    sb.append(SEPARATOR + cellValue.getStringValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                 sb.append(SEPARATOR + cell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                case Cell.CELL_TYPE_ERROR:
                    break;
                default:
                    break;
                }
            }
           
            list.add(sb.toString());
        }
        if(errorStr.length()>0){
        List<String> listerror = new ArrayList<String>();
        listerror.add(errorStr.toString());
        return listerror;
        }
        return list;
    }
    /* service调用的方法demo*/
/*    @Override
public void importDempartment(List<String> lists,UserInfo user) {
List<Department> departmentList = new ArrayList<Department>();
if(lists != null && lists.size() > 0){
Department department = null;
for (String temp : lists) {
String array = temp.substring(1, temp.length());
String [] emp = array.split("\\|");
if (null != emp) {
if (null != emp[0] && !"".equals(emp[0])) {//部门名称不允许为空
department = new Department();
department.setDepartmentId(UUID.randomUUID().toString());
department.setDepartmentName(emp[0]);
if (emp.length == 2) {
department.setDepartmentDesc(emp[1]);
}else {
department.setDepartmentDesc("");
}
department.setCreateUserId(user.getEmployeeId());
department.setCreateDate(new Date());
department.setCompanyId(user.getCompany().getCompanyId());
department.setOwnerCode(user.getCompany().getCompanyCode());
departmentList.add(department);
department = null;
}
}
}
}
if(departmentList != null && departmentList.size() > 0){
DepartmentDao.saveBatch(departmentList);
}
}*/
    public static void main(String[] args) {
    //String fileName = "D:/test.xls";
    String fileName = "F:/tomcat7/apache-tomcat-7.0.72/webapps/AitaServer/tempfile/modulefile/phy/2016120913313132393.xlsx";
    System.out.println(fileName);
    File file = new File(fileName);
    System.out.println(file);
    InputStream is;
try {
is = new FileInputStream(file);
System.out.println(is);
System.out.println(is.available());
ExcelHelper help = new ExcelHelper();
    List<String> temp = help.exportListFromExcel(is, fileName, 0);
    System.out.println(temp.size());
    for (String string : temp) {
    System.out.println(string);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}

这样便完成了导入excel到数据库的功能。。。。。。写的不好，请多多指教