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" onclick="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 blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.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;    }}