POI操作excel

来源：互联网发布：淘宝前端团队编辑：程序博客网时间：2024/06/04 20:15
package com.poi;import java.io.File;import java.io.FileInputStream;import java.util.Iterator;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellReference;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Name;import org.apache.poi.ss.usermodel.Row;public class ReadFromXml {public static void main(String[] args) {//ReadFromXml.getData("目标地址");int [] Rowindexs=ReadFromXml.getRow_ColIndex(new String [] {"admin","maco"},"row");int [] ColunmIndexs=ReadFromXml.getRow_ColIndex(new String [] {"admin","maco"}, "colunm");for (int i : Rowindexs) {System.out.println("row:"+i);}for (int i : ColunmIndexs) {System.out.println("colunm:"+i);}}//根据Name Box(名称框)定位到单元格行、列    public static int [] getRow_ColIndex(String [] str,String type)    {    int [] indexs=new int [str.length];    try{    HSSFWorkbook wb=new HSSFWorkbook(new FileInputStream(new File("目标地址")));            int NameTotalNums=wb.getNumberOfNames();            for(int nameIndex=0;nameIndex<NameTotalNums;nameIndex++)            {            Name name=wb.getNameAt(nameIndex);                for(int i=0;i<str.length;i++)                {                if(name.getNameName().equals(str[i]))                {                //根据String cellFullName=name.getNameName();来得到单格的名字如："A1"                String cellFullName=name.getRefersToFormula();                String [] cellStr=cellFullName.split("\\$");                StringBuilder sb=new StringBuilder();                sb.append(cellStr[cellStr.length-2]);                sb.append(cellStr[cellStr.length-1]);                //构造一个单元格的属性类                CellReference cellReference=new CellReference(sb.toString());                                if(type.equals("row"))                {                int Rowindex=cellReference.getRow();                indexs[i]=Rowindex;                }else if(type.equals("colunm")){                int Colunmindex=cellReference.getCol();                indexs[i]=Colunmindex;                }                }                }            }    }catch(Exception e)    {    e.printStackTrace();    }    return indexs;    }public static void getData(String dir){try{FileInputStream fis=new FileInputStream(new File(dir));/*加载一个excel文件*/HSSFWorkbook wb=new HSSFWorkbook(fis);/*得到一个sheet工作单元*/HSSFSheet sheet=wb.getSheetAt(0);/*迭代拿到所有的行*/Iterator<Row> iterators=sheet.iterator();        while(iterators.hasNext())        {        Row row=iterators.next();        /*迭代拿到所有的单元格*/        Iterator<Cell> cells=row.iterator();        while(cells.hasNext())        {        Cell cell=cells.next();        String strCell = "";        switch (cell.getCellType())        {                case HSSFCell.CELL_TYPE_STRING:        strCell=cell.getStringCellValue();        break;        case HSSFCell.CELL_TYPE_NUMERIC:        strCell=String.valueOf(cell.getNumericCellValue());        }        System.out.print(strCell+"");        }        System.out.println("");                }}catch(Exception e){e.printStackTrace();}}}
0 0