java代码导入excel报表里的数据

package io;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class POI_Excel {
    //给定一个sheet，获取整个sheet的数据，每一行包装成一个map，key是行号，value是表格的值。然后把map加入list，这样整个sheet是一个list
    public static List<Map<Integer,String>> getExcelDataBySheet(HSSFSheet sheet) {
        List<Map<Integer,String>> table_list = new ArrayList<Map<Integer,String>>();
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        //循环列数
        for(int i = 0; i <= rows; i++)
        {
            //得到第i行
            HSSFRow row = sheet.getRow(i);
            Map<Integer,String> map = new HashMap<Integer,String>();
            //循环第i行的数据，从0到columns
            for(int j = 0; j < columns; j++)
            {
                HSSFCell cell = null;
                try {
                    //获取cell，如果报异常，说明整个row是空的null，直接在catch里面捕获，并赋值为空
                    cell = row.getCell(j);
                } catch (NullPointerException e1) {
                    map.put(j, "");
                    continue;
                }
                
                //如果cell为空
                if(null == cell)
                {
                    map.put(j, "");
                    continue;
                }
                //获取cell的类型
                int type = cell.getCellType();
                //如果是空白
                if(type == HSSFCell.CELL_TYPE_BLANK)
                {
                    map.put(j, "");
                }
                //如果是数字型
                else if(type == HSSFCell.CELL_TYPE_NUMERIC)
                {
                    //如果cell里面包含E或者e，说明是科学计数法，要用特殊方法处理
                    if(String.valueOf(cell.getNumericCellValue()).matches(".*[E|e].*"))
                    {
                        DecimalFormat df = new DecimalFormat("#.#");
                        //指定最长的小数点位为10
                        df.setMaximumFractionDigits(10);
                        map.put(j, df.format((cell.getNumericCellValue())));
                    }
                    else
                    {
                        map.put(j, cell.getNumericCellValue()+"");
                    }
                    
                }
                //如果是字符串
                else if(type == HSSFCell.CELL_TYPE_STRING)
                {
                    map.put(j, cell.getStringCellValue());
                }
                //如果是公式型
                else if(type == HSSFCell.CELL_TYPE_FORMULA)
                {
                    String value;
                    try {
                        value = cell.getRichStringCellValue().getString();
                        map.put(j,value);
                    } catch (Exception e) {
                        value = cell.getNumericCellValue()+"";
                        map.put(j, value);
                    }
                }
                else
                {
                    map.put(j, "");
                }
            }
            table_list.add(map);
        }
        return table_list;
    }
    
    //给定一个sheet和行号，列号，获取其值
    public static String getExcelCellData(HSSFSheet sheet,int row,int colnum)
    {
        HSSFRow excel_row = sheet.getRow(row);
        if(excel_row == null)
            return "";
        HSSFCell cell = excel_row.getCell(colnum);
        if(cell == null)
            return "";
        if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
            return "";
        else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
            return cell.getNumericCellValue()+"";
        else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
            return cell.getStringCellValue().toString();
        else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
        {
            String value;
            try {
                value = cell.getRichStringCellValue().getString();
                return value;
            } catch (Exception e) {
                value = cell.getNumericCellValue()+"";
                return value;
            }
        }
        return null;
    }

    //写入excel，注意是只写了第一行
    public static void writeToExcel(String sheetName,String path,Map value,String fileName) throws Exception {
        HSSFSheet sheet;
        HSSFRow row;
        HSSFCell cell;
        File dir = new File(path);
        dir.mkdirs();
        File file = new File(dir.getCanonicalFile() + "\\" + fileName);
        if(file.exists())
            file.delete();
        FileOutputStream fis = new FileOutputStream(file, true);
        HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
        sheet = hSSFWorkbook.createSheet(sheetName);
        row = sheet.createRow(0);
        Iterator l = value.keySet().iterator();
        while(l.hasNext())
        {
            String col = (String)l.next();
            String cellValue = (String)value.get(col);
            cell = row.createCell(Integer.parseInt(col)-1);
            cell.setCellValue(cellValue);
        }

        hSSFWorkbook.write(fis);
        fis.close();
    }
    
    public static void main(String[] args) throws Exception, IOException {
        //读取excel
        HSSFWorkbook work = new HSSFWorkbook(new FileInputStream(new File("e:\\2.xls")));
        HSSFSheet sheet = work.getSheetAt(0);
        System.out.println(getExcelCellData(sheet, 0, 0));
        
//        List<Map<Integer,String>> l = getExcelDataBySheet(sheet);
//        int size = l.size();
//        for(int i = 0; i < size; i++)
//        {
//            System.out.println("第"+(i+1)+"行");
//            Map<Integer,String> map = (Map<Integer,String>)l.get(i);
//            Set<Integer> set = map.keySet();
//            for(int j : set)
//            {
//                System.out.println("第"+(j+1)+"列:"+map.get(j));
//            }
//        }
//        
//        //把数据写入excel，注意是只写了第一行
//        Map map = new HashMap();
//        map.put("1","wang"); //表示第一行第一列是wang，第一行第二列是gang
//        map.put("2", "ga");
//        writeToExcel("wang","d:\\wang",map,"222.xls");
    }
}