读取poi

fuanyu

浏览: 505174 次
性别:
来自: 广州

最近访客更多访客>>

u012321412

scotttom020

bxmingche

JevonZhang8

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

javaEE

/**
 * Writer: fuanyu
 * Date  : 13-11-28
 * Time  : 下午4:36
 */

@SuppressWarnings("unchecked")
public class ImportExcel extends ArchiveArrangeServiceCmd {

    Map param;
    IFile file;

    public ImportExcel(IFile file, Map param) {
        this.param = param;
        this.file = file;
    }

    @Override
    public Object execute(CommandContext context) throws Exception {

        String extType = "";
        int fileType = file.getName().lastIndexOf(".");
        if (fileType != -1)
            extType = file.getName().substring(fileType, file.getName().length());
        if (".xls".equals(extType) || ".xlsx".equals(extType)) {

        } else {
            return "上传文件的格式不对";
        }
        User user = SysUtils.getLoginUser();
        if (user == null) return null;
        Map userDataMap = (Map) ((SysPermissionService) this.getService(SysPermissionService.class)).getLoginUserDetails();
        String INITUNITID = Tools.o2s(userDataMap.get("$primaryOrgId"));
        String INITUNITNAME = Tools.o2s(userDataMap.get("$primaryOrgName"));


       

        String dataSourceId = (String) param.get("dataSourceId");

        DataSourceEngine dataSourceEngine = Context.getService(DataSourceEngine.class);
        DataSource dataSource = dataSourceEngine.getDataSource(dataSourceId);

        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        Map columns = new HashMap();
        if (param.get("columns") != null) {//从配置中查找的列名与值
            String columnsVal = (String) param.get("columns");
            columns = JsonConverter.jos2obj(columnsVal, Map.class);
        }

        Map newMap = new HashMap();
        newMap.put("dataSoureId", dataSourceId);
        List<Column> listColumn = (List<Column>) this.getService(AmsArchiveArrangeService.class).getTableColumns(newMap);
        List<String> dateList = new ArrayList<String>();
        for (Column dsColumn : listColumn) {  //所有数据源字段
            if (dsColumn.getIsDateTimeColumn() == true) {
                dateList.add(dsColumn.getColumnName());
            }

        }


        Map formData = new HashMap();

        String[] cloumn = new String[100];
        $remove(AmsTImportConfig.class, "type", ArrangeConstant.IMPORT_TYPE, "dataSourceId", dataSourceId);
        int successNum = 0;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字


        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();

            if (row.getRowNum() == 0) {

                int num = row.getLastCellNum();
                cloumn = new String[num];
                for (int k = 0; k < num; k++) {
                    String fieldName = row.getCell(k).toString();
                    if (fieldName != null && fieldName.length() > 0) {
                        String columnName = (String) param.get(fieldName);

                        if (columnName != null && columnName.length() > 0) {
                            AmsTImportConfig config = new AmsTImportConfig();
                            config.setSn(k);
                            config.setDataSourceId(dataSourceId);
                            config.setType(ArrangeConstant.IMPORT_TYPE);
                            config.setExcelFieldName(fieldName);
                            config.setSrcFieldName(columnName);

                            $save(config);

                            /// AmsTImportConfig config =  psql("t_import_config").cp("dataSourceId", dataSourceId).cp("fieldName",fieldName).exec().asObject(AmsTImportConfig.class);
                            cloumn[k] = columnName;

                        } else {
                            cloumn[k] = "";
                        }
                    }
                }

            } else {
                formData.clear();
                int num = row.getLastCellNum();

                /**
                 *
                 *CellType 类型 值CELL_TYPE_NUMERIC 数值型 0  ，整型中还包括着日期类型
                 * CELL_TYPE_STRING 字符串型 1
                 * CELL_TYPE_FORMULA 公式型 2
                 * CELL_TYPE_BLANK 空值 3
                 * CELL_TYPE_BOOLEAN 布尔型4
                 * CELL_TYPE_ERROR 错误 5
                 */
                String value = "";
                for (int k = 0; k < num; k++) {
                    if (cloumn[k] != null && cloumn[k].length() > 0) {
                        Cell cell = row.getCell(k);
                        if (row.getCell(k).getCellType() == 0) {
                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                value = df.format(cell.getNumericCellValue());
                            } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                value = nf.format(cell.getNumericCellValue());
                            } else {
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                            }


                            formData.put(cloumn[k], value);
                        } else {

                            formData.put(cloumn[k], row.getCell(k).toString());
                        }
                    }


                }
                formData.put("initunitid", INITUNITID);//单位id
                formData.put("initunitname", INITUNITNAME);//单位名称
                formData.put("status", "");
                if (columns != null && !columns.isEmpty()) {
                    Iterator it = columns.keySet().iterator();
                    while (it.hasNext()) {
                        Object key = it.next();
                        if (key != null) {

                            formData.put(key, columns.get(key));


                        }

                    }
                }


                Record record = dataSource.createRecord(formData);


                for (String key2 : dateList) {   //如果是日期列的话，就转化日期类型
                    Object columnsVal_ = formData.get(key2);
                    String columnsVal = String.valueOf(columnsVal_);

                    if (!"".equals(columnsVal)) {

                        record.set((String) key2, format(columnsVal));
                    }
                }

               /// record.set("tm", "test2");

                //  java.text.SimpleDateFormat  format  = new SimpleDateFormat();

                //注意format的格式要与日期String的格式相匹配
                /*    SimpleDateFormat     sdf = new SimpleDateFormat("yyyyMMdd");
                 try {
                     date = sdf.parse("20131217");
                     System.out.println(date.toString());
                 } catch (Exception e) {
                     e.printStackTrace();
                 }
              record.set("gdrq",date);  //GDRQ*/
                record.set("status", "");
                Record reRecord = dataSource.saveRecord(record);
                successNum++;

            }

        }
        String str = "成功导入" + successNum + "条记录";

        return str;
    }


    private Date format(String columnsVal) throws ParseException {
        Date date = new Date();
        String str="yyyy-MM-dd";
        if (columnsVal.indexOf("-") != -1) {

            if(columnsVal.length()>16){
                str="yyyy-MM-dd HH:mm:ss";
            }


        } else if (columnsVal.indexOf("/") != -1) {
            if(columnsVal.length()>16){
                str="yyyy/MM/dd HH:mm:ss";
            }else{
                 str="yyyy-MM-dd";
            }


        }else{
               if(columnsVal.length()>16){
                str="yyyyMMdd HH:mm:ss";
            }else{
                 str="yyyyMMdd";
            }

        }

            SimpleDateFormat sdf1 = new SimpleDateFormat(str);
            date = sdf1.parse(columnsVal.trim());

        return date;
    }

}

JAVA POI读取Office excel (2003,2007)

所需jar 包：poi-3.6

poi-3.6-20091214.jarpoi-contrib-3.6-20091214.jarpoi-scratch.6-20091214.jargeronimo-stax-api_1.0_spec-1.0.jar

xmlbeans-2.3.0.jar

读取excel 文件的 java 代码：

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.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.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 ReadExcel {

     /**
     * 对外提供读取excel 的方法
     * */
public static List<List<Object>> readExcel(File file) throws IOException{
   String fileName = file.getName();
   String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);
   if("xls".equals(extension)){
    return read2003Excel(file);
   }else if("xlsx".equals(extension)){
    return read2007Excel(file);
   }else{
    throw new IOException("不支持的文件类型");
   }
}

/**
* 读取 office 2003 excel
* @throws IOException
* @throws FileNotFoundException */
private static List<List<Object>> read2003Excel(File file) throws IOException{
   List<List<Object>> list = new LinkedList<List<Object>>();
   HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
   HSSFSheet sheet = hwb.getSheetAt(0);
   Object value = null;
   HSSFRow row = null;
   HSSFCell cell = null;

   for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){
    row = sheet.getRow(i);
    if (row == null) {
     continue;
    }
    List<Object> linked = new LinkedList<Object>();
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
     cell = row.getCell(j);
     if (cell == null) {
      continue;
     }
     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
     DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
     switch (cell.getCellType()) {
     case XSSFCell.CELL_TYPE_STRING:
      System.out.println(i+"行"+j+" 列 is String type");
      value = cell.getStringCellValue();
      break;
     case XSSFCell.CELL_TYPE_NUMERIC:
      System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());
      if("@".equals(cell.getCellStyle().getDataFormatString())){
         value = df.format(cell.getNumericCellValue());
      } else if("General".equals(cell.getCellStyle().getDataFormatString())){
         value = nf.format(cell.getNumericCellValue());
      }else{
        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
      }
      break;
     case XSSFCell.CELL_TYPE_BOOLEAN:
      System.out.println(i+"行"+j+" 列 is Boolean type");
      value = cell.getBooleanCellValue();
      break;
     case XSSFCell.CELL_TYPE_BLANK:
      System.out.println(i+"行"+j+" 列 is Blank type");
      value = "";
      break;
     default:
      System.out.println(i+"行"+j+" 列 is default type");
      value = cell.toString();
     }
     if (value == null || "".equals(value)) {
      continue;
     }
     linked.add(value);

   }
    list.add(linked);
   }

   return list;
}

/**
* 读取Office 2007 excel
* */

private static List<List<Object>> read2007Excel(File file) throws IOException {

   List<List<Object>> list = new LinkedList<List<Object>>();
   // 构造 XSSFWorkbook 对象，strPath 传入文件路径
   XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
   // 读取第一章表格内容
   XSSFSheet sheet = xwb.getSheetAt(0);
   Object value = null;
   XSSFRow row = null;
   XSSFCell cell = null;
   for (int i = sheet.getFirstRowNum(); i <= sheet
     .getPhysicalNumberOfRows(); i++) {
    row = sheet.getRow(i);
    if (row == null) {
     continue;
    }
    List<Object> linked = new LinkedList<Object>();
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
     cell = row.getCell(j);
     if (cell == null) {
      continue;
     }
     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
     DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字

     switch (cell.getCellType()) {
     case XSSFCell.CELL_TYPE_STRING:
      System.out.println(i+"行"+j+" 列 is String type");
      value = cell.getStringCellValue();
      break;
     case XSSFCell.CELL_TYPE_NUMERIC:
      System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());
      if("@".equals(cell.getCellStyle().getDataFormatString())){
        value = df.format(cell.getNumericCellValue());
      } else if("General".equals(cell.getCellStyle().getDataFormatString())){
        value = nf.format(cell.getNumericCellValue());
      }else{
       value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
      }
      break;
     case XSSFCell.CELL_TYPE_BOOLEAN:
      System.out.println(i+"行"+j+" 列 is Boolean type");
      value = cell.getBooleanCellValue();
      break;
     case XSSFCell.CELL_TYPE_BLANK:
      System.out.println(i+"行"+j+" 列 is Blank type");
      value = "";
      break;
     default:
      System.out.println(i+"行"+j+" 列 is default type");
      value = cell.toString();
     }
     if (value == null || "".equals(value)) {
      continue;
     }
     linked.add(value);
    }
    list.add(linked);
   }
   return list;
}

}

说明：该类中共封装了三个方法，对外提供的读取excel文件的方法，两个私有的分别读取excel2003和excel2007的方法。外部使用，只需调用readExcel 方法，传入一个File 参数，程序根据文件扩展名来判断选取那个方法来读取Excel文件。

来自：http://hi.baidu.com/thinkingjava/item/6488abf9acb4d21be2e3bdba

分享到：

js json与字符串互转 | PHP增删改查的简单实现

2014-03-12 17:57
浏览 1335
评论(0)
分类:行业应用
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

读取poi

JAVA POI读取Office excel (2003,2007)

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

读取poi

JAVA POI读取Office excel (2003,2007)

评论

发表评论

相关推荐

java 模拟微信浏览器http请求

java 小数点处理的问题

java操作excel 替换指定字符串

itext对水印图片的旋转、放大、缩小等操作的示例

java 获取ip

对Map排序

java 上下篇文章实现的思路

Java：String和Date、Timestamp之间的转换

ArrayList和数组间的相互转换

No result defined for action and result

IntelliJ IDEA 10.0 使用javac 编译程序出现“非法字符： \65279 ”解决方法

map取key及key的值

用jdbc操作CLOB型数据的两种方式

java @override 报错处理

Java判断字符串中是否包含汉字

JMagick+ImageMagic处理图片(高质量)

定时发送邮件的思路

java 解析json

javamail 收邮件遇到的问题及解决方法

java 获取文件夹大小，文件大小，文件个数

最近访客更多访客>>