poi操作excel

面向对象的思想，首先你要操作一个excel，进行excel的分析，获取整个excel，然后获取sheet，获取row，接下来用row获取col。这样就解决了哦。
接下来看几个重要代码块

 package com;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Created by kcz on 2016/9/8.
 */
public class Read {
    public List<List<String>>list=new ArrayList<>();
    public static int lastRowNum=0;
    public static int lastCellNum=0;
    public static void main(String[] args) {
        new Read().read("E:\\ExcelTest\\data.xlsx");
        System.out.println("-------------");
        //readXml("d:\\data1.xls");
    }
    public  List<List<String>> read(String fileName) {

        boolean isE2007 = false;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx"))
            isE2007 = true;
        try {
            InputStream input = new FileInputStream(fileName);  //建立输入流
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007)
                wb = new XSSFWorkbook(input);
            else
                wb = new HSSFWorkbook(input);
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {//获取每个Sheet表
                Sheet sheet = wb.getSheetAt(i);
                int firstRowNum = sheet.getFirstRowNum();
                lastRowNum = sheet.getLastRowNum();

                for (int j = firstRowNum; j <= lastRowNum; j++) {
                    XSSFRow rowIn = (XSSFRow) sheet.getRow(j);
                    if(rowIn == null) {
                        continue;
                    }
                    int firstCellNum = rowIn.getFirstCellNum();
                     lastCellNum = rowIn.getLastCellNum();
                    List<String>list1=new ArrayList<>();
                    for (int k = firstCellNum; k <= lastCellNum; k++) {
//              XSSFCell cellIn = rowIn.getCell((short) k);
                        XSSFCell cellIn = rowIn.getCell(k);
                        if(cellIn == null) {
                            continue;
                        }

                        int cellType = cellIn.getCellType();
                        if(XSSFCell.CELL_TYPE_STRING != cellType) {
                            continue;
                        }
                        String cellValue = cellIn.getStringCellValue();
                        if(cellValue == null) {
                            continue;
                        }
                        list1.add(cellValue);

                        //System.out.println(cellValue);


                    }
                    list.add(list1);
                }


            }


            }catch (IOException ex) {
            ex.printStackTrace();
        }
        Iterator iterator=  list.iterator();
        while (iterator.hasNext()){
            System.out.println(iterator.next());
            System.out.println(lastCellNum);
            System.out.println(lastRowNum);
        }

        return list;
        }
    }

package com;
import java.io.*;


import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
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;
/**
 * Created by kcz on 2016/9/8.
 */
public class Write {
    public static int lastRowNum=0;
    public static int lastCellNum=0;
    public static int bt = 0;
    public static void main(String[] args) throws IOException {
        new Write().outputExcelData();
    }

    public void outputExcelData() throws IOException {
       Read read =new Read();
       List<List<String>> lists = read.read("E:\\ExcelTest\\data.xlsx");
     lastRowNum = read.lastRowNum ;
     lastCellNum = read.lastCellNum ;


        String fileName = "E:\\ExcelTest\\测试1.xlsx";
        File file = new File(fileName);
        if (file.exists()) {
            file.delete();
        }
        //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
        Workbook wb = new XSSFWorkbook();


        for (int i = 0; i < lastRowNum; i++) {
            Sheet sheet = wb.createSheet("列表"+i);  //创建一个可写入的工作表

            for (int r=0;r<lastRowNum;r++){
                bt=0;//循环一次结束之后重新置为0
                Row row = sheet.createRow(r);

                for (int c=0;c<lists.get(r).size();c++){
                    Cell cell = row.createCell(c);
                    cell.setCellValue(lists.get(r).get(c));
//                    if(bt>lastCellNum){
//                        break;
//                    }
//                    else {
//                        for (int value=0;value<lastCellNum-1;value++){
//
//                           cell.setCellValue(lists.get(r).get(c));
//
//                        }
//                        bt++;
//                    }

                }
            }

        }
        FileOutputStream fos = new FileOutputStream(file);
        wb.write(fos);//从内存中写入文件中
        fos.close();
    }
}