AOS中Excel文件导入

Form界面：

<?xml version="1.0" encoding="UTF-8"?>
<Forms>
	<form id="aos_A8_t_import_file_import_2" width="300px" height="150px" desp="文件导入">
		<toolbar>
			<button id="btnExit" text="退出" img="img/Exit.gif" οnclick="PT.closeModal();"/>
		</toolbar>
		<widgets>
		 <layout type="VerticalLayout" height="100%"  attr="align:center">
			<layout type="FieldLayout" align="left" attr="groupSize:1;" height="100%" width="340px" leftSpace="5px" desp="安装客户信息导入">
				<basic name="fileupload" widget="FileUploader" label="导入" width="340px" height="90px" align="left" topSpace="40px" leftSpace="10px">   
					<value name="action">import</value>
					<value name="fileTypes">*.xls</value>
					<value name="fileAmount">1</value>
				</basic>
			</layout>
			<layout type="FieldLayout" height="100%"  topSpace="40px" desp="反馈信息">
            <basic name="msg" widget="Text" width="400px" height="400px"  attr="">
            </basic>
          </layout>
         </layout>
		</widgets>
	<extendPoint>
		<plugin type="com.zjaisino.a8.PVRETURN.anzhImportt.AnZhImport" onEvent="import" desp="上传导入">  </plugin>
    </extendPoint>
	<bind element="this" event="onCreate">
	 <![CDATA[
	 ]]>
	</bind>
	</form></Form>

java代码：

package com.zjaisino.a8.PVRETURN.anzhImportt;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
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 org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;

import com.aisino.platform.core.SqlPlugin;
import com.aisino.platform.exception.BusinessException;
import com.aisino.platform.view.AbstractForm;
import com.aisino.platform.view.DataMsgBus;
import com.aisino.platform.view.listener.SubmitListener;

public class ExcelImport extends SqlPlugin implements SubmitListener{
	private static final long serialVersionUID = 1L;
	  private static FormulaEvaluator evaluator;
  public void setValue(String paramString1, String paramString2) {}
	  
	  public void doSubmitAction(AbstractForm paramAbstractForm, DataMsgBus bus)
	  {
		  String fileName = "";
		//获取上传文件
			 Map<String, File> fileuploadM = (Map) bus.getValue("fileupload");	
			 List<Map> listMap = getDataFromExcelFile(fileuploadM);	
	  }	
	  
	  /**
	   * 结合AOS平台导入模板获取Exce表中的数据
	   * @param Map<String, File> fileuploadM ：文件名-文件
	   * @return  List<Map> 
	   * map代表一行的数据
	   * map中的key值为字符串类型数值，表示第几列，
	   * 如：excel中第一列的值：put("0",value),excel中第二列的值：put("1",value)
	   */
	  private static List<Map> getDataFromExcelFile(Map<String, File> fileuploadM) {
		  List<Map> listMap = new ArrayList<Map>();
		   String fileName = "";
		   if ((fileuploadM == null) || (fileuploadM.isEmpty()))
				return null;
			File file = null;
			Iterator<String> i$ = fileuploadM.keySet().iterator(); 
			if (i$.hasNext()) 
			{ 
				String key = (String)i$.next();
				fileName = key;
				file = (File)fileuploadM.get(key);
			}
			if ((file == null) || (!file.exists())) {
				throw new BusinessException("文件不存在或已失效，请重新上传！");
			}
			FileInputStream fileInputStream;
			try {
				fileInputStream = new FileInputStream(file);
				//判断以xls还是xlsx结尾
				boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");
				//1、读取工作簿
				Workbook workbook;
				workbook = is03Excel ? new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
				//2、读取工作表
				Sheet sheet = workbook.getSheetAt(0);
				//获取一共导入多少条数据
				int rown = sheet.getPhysicalNumberOfRows();
				System.out.println("------------------"+rown);
				for(int i=1; i<rown; i++){
					Row row = sheet.getRow(i);
					Map<String,String> map = getDataFromCell(row);
					listMap.add(map);
				}
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return listMap;
	}
	/**
	   * 获取一行中所有列的数据
	   * @param row  ：行
	   * @return Map<String, String> ：列对应的数值 key=i
	   * map代表一行的数据
	   * map中的key值为字符串类型数值，表示第几列，
	   * 如：excel中第一列的值：put("0",value),excel中第二列的值：put("1",value)
	   */
    private static Map<String, String> getDataFromCell(Row row) {
	   Map<String,String> map = new HashMap<String,String>();
	   //列数
	   int ii = row.getPhysicalNumberOfCells();
	   for(int i=0; i<ii; i++){
		   Cell cell = row.getCell(i);
		   String value = getCellValueByCell(cell);
		   map.put(""+i, value);
	   }
	   return map;
	}
	/**
	 * 根据excel数据类型读取数据
	 * @param cell
	 * @return String
	 */
	 private static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        if(cellType==Cell.CELL_TYPE_FORMULA){ //表达式类型      
			cellType=evaluator.evaluate(cell).getCellType();
        }
        switch (cellType) {
        case Cell.CELL_TYPE_STRING: //字符串类型
            cellValue= cell.getStringCellValue().trim();
            cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue; 
            break;
        case Cell.CELL_TYPE_BOOLEAN:  //布尔类型
            cellValue = String.valueOf(cell.getBooleanCellValue()); 
            break; 
        case Cell.CELL_TYPE_NUMERIC: //数值类型
             if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
            	 Double dateDouble  = cell.getNumericCellValue();
            	 Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(dateDouble);
            	 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            	 cellValue = sdf.format(date).toString();
             } else {  //否
                 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue()); 
             } 
            break;
        default: //其它类型，取空串吧
            cellValue = "";
            break;
        }
        return cellValue;
    }
}