java基础以及操作Excle

今天把会经常用的几个集合的迭代方法又练习了一下，放在这里，经常复习！

map集合迭代

/*** 迭代map【1】*/

for (Integer key : map.keySet()) {//迭代key值
      for (String value : map.values()) {//跌打value值
           System.out.println("迭代map【1】" + key + ":" + value);
      }
//  System.out.println("key	"+key);
}

/*** 迭代map【2】*/

for (Map.Entry<Integer, String> entry : map.entrySet()) { 
　　System.out.println("迭代map【2】" + entry.getKey() + "：" + entry.getValue());
}

/*** 迭代map【3】*/

Iterator<Map.Entry<Integer, String>> entries = map.entrySet().iterator();
　　 while (entries.hasNext()) {
 　　　　Map.Entry<Integer, String> entry = entries.next();
 　　　　System.out.println("迭代map【3】" + entry.getKey() + "：" + entry.getValue());
 }

List集合迭代

 List list = new ArrayList();
        list.add("1");
        list.add("2");
        list.add("3");
        list.add("4");
        int size = list.size();
        System.out.println(size);
        for(Object li:list){
            System.out.println("foreach迭代List集合		"+li);
        }

        Iterator iterator = list.iterator();
        while (iterator.hasNext()){
            System.out.println("Iterator迭代List集合		"+iterator.next());
        }

工作需要，经常需要操作本地的Excle,下面总结了一下创建Excle

JAVA EXCEL API:是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为它是使用Java编写的，所以我们在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。

package yh.com.excle;

import jxl.Workbook;
import jxl.format.*;
import jxl.format.Alignment;
import jxl.write.*;
import jxl.write.Colour;
import jxl.write.biff.RowsExceededException;

import java.io.File;
import java.io.IOException;

/**
 * Created by vincce on 2015/11/9.
 */
public class WriteMsgToExcle {

    private static final String  filePath = "E:\study\test\createExcle.xls";

    public static void main(String[] args) {
        createExcle();
    }

    /**
     * 创建Excle到本地
     */
    public static void createExcle(){

        try {
            WritableWorkbook writableWorkbook = Workbook.createWorkbook(new File(filePath));//在指定目录下创建Excl
            WritableSheet sheet = writableWorkbook.createSheet("myFirst", 0);//创建一个sheet ，参数含义分别是 sheet名字 该Excle文件中第一个sheet
            sheet.setRowView(0, 600, false);//设置第一行的高度

            WritableCellFormat writableCellFormat = new WritableCellFormat();
            writableCellFormat.setBackground(Colour.GRAY_25);//设置背景色
            writableCellFormat.setAlignment(Alignment.CENTRE);//设置居中显示



            WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
            color.setColour(Colour.GOLD);//设置字体颜色为金黄色
            WritableCellFormat colorFormat = new WritableCellFormat(color);

            //第一行
//            Label l1 = new Label(0,0,"name",colorFormat);//创建单元格 参数含义分别是 列坐标，行坐标 内容,字体颜色
            Label l1 = new Label(0,0,"name",writableCellFormat);//创建单元格 参数含义分别是 列坐标，行坐标 内容，背景色
            sheet.addCell(l1);
//            Label l2 = new Label(1,0,"sex",colorFormat);
            Label l2 = new Label(1,0,"sex",writableCellFormat);
            sheet.addCell(l2);
//            Label l3 = new Label(2,0,"age",colorFormat);
            Label l3 = new Label(2,0,"age",writableCellFormat);
            sheet.addCell(l3);
//            Label l4 = new Label(3,0,"score",colorFormat);
            Label l4 = new Label(3,0,"score",writableCellFormat);
            sheet.addCell(l4);

            //第二行
            Label l5 = new Label(0,1,"john");
            sheet.addCell(l5);
            Label l6 = new Label(1,1,"male");
            sheet.addCell(l6);
            Label l7 = new Label(2,1,"20");
            sheet.addCell(l7);
            Label l8 = new Label(3,1,"100");
            sheet.addCell(l8);

            //将创建的内容写入到输出流，并关闭输出流
            writableWorkbook.write();
            writableWorkbook.close();

        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }

    }
}

需要依赖

 <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
  </dependency>

读取Excle中的某一列

public static void test2(String path){
        File file = new File(path);
        Workbook workbook;
        try {
            workbook = Workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet(0);//获取第一个sheet
            Cell[] cell = sheet.getColumn(4);//获取第一列中的值
            int len = cell.length;//共有多少行
            Map<Integer,String> map = new HashMap();
            for(int i = 0;i<len;i++){
                Cell cell1 = cell[i];
                System.out.println("第"+(i+Integer.parseInt("1"))+"行	"+cell1.getContents()+"	"+"'"+cell1.getContents()+"',");
                map.put(i,cell1.getContents());
//                System.out.println("select * from axa_user where id="+"'"+cell1.getContents()+"',");
            }
            System.out.println("总共有"+len+"行");
            System.out.println(map.toString());


           /*
            Sheet sheet1 = workbook.getSheet(1);//读取第二个sheet中的内容

            Cell[] cell1 = sheet1.getColumn(0);//获取第一列中内容
            Cell[] cell2 = sheet1.getColumn(1);//获取第二列中内容

            Map<Integer,String> map1 = new HashMap<Integer, String>();
            for(int len1=0;len1<cell1.length;len1++){
                Cell c1 = cell1[len1];
                map1.put(len1,c1.getContents());
            }

            Map<Integer,String> map2 = new HashMap<Integer, String>();
            for(int len2=0;len2<cell2.length;len2++){
                Cell c2 = cell2[len2];
                map2.put(len2,c2.getContents());
            }

            Map<Integer,String> map3 = new HashMap<Integer, String>();

            for(Map.Entry<Integer,String> entry2:map2.entrySet()){
                for(Map.Entry<Integer,String> entry1:map1.entrySet()){
                    if(entry2.getValue().equals(entry1.getValue())){
                        map3.put(entry1.getKey(),entry1.getValue());
                    }
                }
            }

            System.out.println(cell1.length);
            System.out.println(cell2.length);
            System.out.println("map1	"+map1.toString());
            System.out.println("map2	"+map2.toString());
            System.out.println("map3	"+map3.toString());
            */


        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
    }