空间管理您的位置: 51Testing软件测试网 » shingo0109的测试空间 » 日志

Java对Excel的一些操作

上一篇 / 下一篇 2013-01-18 09:35:26 / 个人分类：Java学习

Java对Excel的一些操作用法，使用的是jxl.jar包，代码参考如下：

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelOperation {
   public String fileName; //excel file path
   public String sheetNameOrIndex; //sheet name or index

   public ExcelOperation(){
      sheetNameOrIndex = "0";
   }

   public String[] getExcelAllColumnNames() throws Exception {
      String[] colNames = null;
      Sheet sheet = null;
       try {
          InputStream is = new FileInputStream(fileName);
           Workbook wb = Workbook.getWorkbook(is);

           String regex = "\\d+";
           if(sheetNameOrIndex.matches(regex)){
              sheet = wb.getSheet(Integer.parseInt(sheetNameOrIndex));}
           else{
              sheet = wb.getSheet(sheetNameOrIndex);
           }
           int rows = sheet.getRows();
           int cols = sheet.getColumns();
           System.out.println("rows = " + rows);
           System.out.println("cols = " + cols);

           colNames = new String[cols];
           for (int i = 0; i < cols; i++) {

              //Cell cell = sheet.getCell(i, 0);   //column is at the first row;
              //colNames[i] = cell.getContents().trim();
              colNames[i] = sheet.getCell(i, 0).getContents();
              System.out.println("column[" + i + "], column name = " + colNames[i]);
           }
           wb.close();
           is.close();
        } catch (FileNotFoundException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
        } catch (BiffException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
        } catch (IOException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
      return colNames;
   }

   //Get excel sheet object
   public Sheet getExcelSheet() throws Exception{
      Sheet sheet = null;
      try{
         InputStream is = new FileInputStream(fileName);
           Workbook wb = Workbook.getWorkbook(is);

           String regex = "\\d+";
           if(sheetNameOrIndex.matches(regex)){
              sheet = wb.getSheet(Integer.parseInt(sheetNameOrIndex));}
           else{
              sheet = wb.getSheet(sheetNameOrIndex);
           }
      }catch(FileNotFoundException e) {
         e.printStackTrace();
      }catch(BiffException e) {
         e.printStackTrace();
      }
       return sheet;
   }

   public int getExcelRowCount() throws Exception{
      Sheet sheet = getExcelSheet();
      return sheet.getRows();
   }

   public int getExcelColumnCount() throws Exception{
      Sheet sheet = getExcelSheet();
      return sheet.getColumns();
   }

   //Get column id of single column
   public int getColumnId(String columnName) throws Exception{
      String colName = null;
      int columnId = 0;
      Sheet sheet = getExcelSheet();
      int cols = sheet.getColumns();
      for (int i = 0; i < cols; i++) {
          colName = sheet.getCell(i, 0).getContents().trim();
          if(colName.equals(columnName.trim())){
             System.out.println("column name = " + colName);
             columnId = i;
             break;
          }
       }
      return columnId;
   }

   //Get column id collection of column name
   public Map<String, Integer> getColumnIdMaps() throws Exception{
      String colNameKey = null;
      int colIdValue = 0;
      Sheet sheet = getExcelSheet();
      int cols = sheet.getColumns();

      Map<String, Integer> maps = new HashMap<String, Integer>();
      for(int i=0;i<cols;i++){
         colNameKey = sheet.getCell(i, 0).getContents().trim();
         colIdValue = i;
         maps.put(colNameKey, colIdValue);
      }
      return maps;
   }

   public List<Map<String, String>> getAllExeclInfoAsArray() throws Exception{
      Sheet sheet = getExcelSheet();
      int rows = sheet.getRows();
      int cols = sheet.getColumns();
      List<Map<String, String>> datas = new ArrayList<Map<String, String>>();

      for(int i = 1; i < rows; i++) {
         Map<String, String> maps = new HashMap<String, String>();
         for(int j = 0; j < cols; j++) {
            String key = sheet.getCell(j, 0).getContents().trim();
            String excelKey = key + "[" + i + "]";
            String excelValue = sheet.getCell(j, i).getContents().trim();
            maps.put(excelKey, excelValue);
         }
         datas.add(maps);
      }
      return datas;
   }

    public Map<String, String> getAllExeclInfoAsHashMap() throws Exception{
         Sheet sheet = getExcelSheet();
         int rows = sheet.getRows();
         int cols = sheet.getColumns();
         Map<String, String> maps = new HashMap<String, String>();

         for(int i = 1; i < rows; i++) {
            for(int j = 0; j < cols; j++) {
               String key = sheet.getCell(j, 0).getContents().trim();
               String excelKey = key + "[" + i + "]";
               String excelValue = sheet.getCell(j, i).getContents().trim();
               maps.put(excelKey, excelValue);
            }
         }
         return maps;
      }
}

jxl.rar(622 KB)

收藏举报

TAG:

查看全部评论

Java对Excel的一些操作

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅