(java)excel导入

excel 导入就是讲有效数据写入数据库的过程。

1.service

--service
public String import(InputStream in);

2.serviceImpl

--serviceImpl

StringBuilder message = new StringBuilder();

                //CapitalExpendManage-->实体类
		List<CapitalExpendManage> capitalExpendManageList = new ArrayList();
		HSSFWorkbook book = null;
		try {
			//构建HSSFWorkbook类对象，用输入流构建 
			book = new HSSFWorkbook(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
		// 获取excel表中的 第一个sheet
		//book.getSheetAt(0) 是根据索引来获取sheet。
		//book.getSheet("导入表") 是根据sheet 表名来获取sheet。
		HSSFSheet sheet = book.getSheetAt(0);
		if (sheet == null) {
			message.append("导入表没有找到！<br/>");
		}else{
			//校验
			verifyImportExcel(book, capitalExpendManageList, message);
			//写入数据库
			if(message==null){
				doImportCapitalExpendManage(capitalExpendManageList);
			}			
		}//book.getSheetAt(0) 是根据索引来获取sheet。
		//book.getSheet("导入表") 是根据sheet 表名来获取sheet。
		HSSFSheet sheet = book.getSheetAt(0);
		if (sheet == null) {
			message.append("导入表没有找到！<br/>");
		}else{
			//校验
			verifyImportExcel(book, capitalExpendManageList, message);
			//写入数据库
			if(message==null){
				doImportCapitalExpendManage(capitalExpendManageList);
			}			
		}

//校验

//写入数据库		

 

3.action

 

--Action

4.公用方法类

package com.boco.common;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

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 com.boco.framework.exception.BusinessException;

public class ExcelImport4Sheet {
    private static final DecimalFormat NUMFORMAT = new DecimalFormat("###############.####");//数字格式
    private static final SimpleDateFormat DATEFORMAT = new SimpleDateFormat("yyyy/M/d");//日期格式
    //excel的输入流
    private  Sheet sheet;
    //需要导入的字段名
    private Object[] fields;
    //导入的行数据之间的处理和过滤
    private ImportSerice service;
    //数据开始的行数
    private Integer beginRow;
    
    public ExcelImport4Sheet(Sheet sheet,Object[] fields, ImportSerice service){//添加构造方法，初始化信息
        this.sheet=sheet;
        this.fields=fields;
        this.service=service;
    }
    public void setBeginRow(Integer row){//设置数据开始的行数
        this.beginRow=row;
    }
    public int getToalRows(){//获取总行数
    	int total = 0;
    	if(beginRow==null)beginRow=1;
        try {
            int rows=sheet.getLastRowNum();//表获取最后一行的num
            for(int i=beginRow;i<=rows;i++){
                Row row = sheet.getRow(i);
                if(isBlankRow(row))break;//如果遇到空行，则退出。
                total++;
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return total;
    }
    public void run() {//
        if(beginRow==null)beginRow=1;
        try {
            int rows=sheet.getLastRowNum();
            for(int i=beginRow;i<=rows;i++){
                Map<String, String> map = new HashMap<String, String>();
                map.put("rowIndex", new Integer(i+1).toString());//第几行的数据
                Row row = sheet.getRow(i);//获取行
                if(isBlankRow(row))break;//如果遇到空行，则退出。
                for(int j=0;j<fields.length;j++){
                    Cell cell = row.getCell(j);
                    if(cell==null){
                        map.put(fields[j].toString(), "");
                    }
                    else {
                    	String value = getCellValue(cell);
						String putValue="";
						if(value != null){
						    putValue=value.trim();
						}
						map.put(fields[j].toString(),putValue);
                    }
                }
                service.doImport(map);//调用方法
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("Excel导入出错, 请联系管理员！");
        }
    }
    
    public static String getCellValue(Cell cell) {
		String value = "";
		if(cell.getCellType()==Cell.CELL_TYPE_BLANK){
		    value = "";
		}else if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
		    value =cell.getBooleanCellValue()+"";
		}else if(cell.getCellType()==Cell.CELL_TYPE_ERROR){
		    value = "ERROR," + cell.getErrorCellValue();
		}else if(cell.getCellType()==Cell.CELL_TYPE_FORMULA){
		    cell.setCellType(Cell.CELL_TYPE_STRING);
            String v = cell.getStringCellValue();
            if (v != null) {
                v = v.replaceAll("#N/A", "").trim();
            }
            if(null == v){
                value=null;
            }else{
                value=v.toString();
            }
		}else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
		    if (DateUtil.isCellDateFormatted(cell)) {
                value = DATEFORMAT.format(cell
                        .getDateCellValue());
            } else {
                value = NUMFORMAT.format(cell
                        .getNumericCellValue());
            }
		}else{
		    value = cell.getStringCellValue();
		}
		return value;
	}
    
    /**
     * 使用final变量方式，在doImport中处理结果
     * 例：
     * String[] fields = new String[]{"f1","f2","f3","f4"};
     * final List maplist = new ArrayList();
     * final List errorlist = new ArrayList();
     * ExcelImport im = new ExcelImport(inputStream, fields, new ExcelImport.ImportService() {
     *      public void doImport(Map map) {
     *          if(!"".equals(map.get("f1"))){
     *              maplist.add(map);
     *          }else{
     *              errorlist.add(map);
     *          }
     *      }
     * });
     * @author xuri
     */
    public interface ImportSerice{
        public void doImport(Map<String, String> map);
    }
    public boolean isBlankRow(Row row){
        if(row == null) return true;
        boolean result = true;
        for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
            Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
            String value = "";
            if(cell != null){
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = String.valueOf((int) cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = String.valueOf(cell.getCellFormula());
                    break;
                default:
                    break;
                }
                
                if(!value.trim().equals("")){
                    result = false;
                    break;
                }
            }
        }
        
        return result;
    }
}