package com.jiao; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.FileInputStream; import java.io.IOException; import java.io.ObjectInputStream; import java.io.ObjectOutputStream; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class POItest ... { public static String outputFile = "c:/Temp/test.xls"; List list = new ArrayList(); public static String fileToBeRead = "D://work//new_bmp2//bpm2_v1_00//data//sql//system-data-part3.xls"; //public static String fileToBeRead = "D://FF.xls"; public void CreateExcel() ...{ try ...{ // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) HSSFRow row = sheet.createRow((short)0); // 在索引0的位置创建单元格(左上端) HSSFCell cell = row.createCell((short)0); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue("sweater"); // 新建一输出文件流 FileOutputStream fOut = new FileOutputStream(outputFile); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束,关闭文件 fOut.close(); System.out.println("文件生成..."); } catch(Exception e) ...{ System.out.println("已运行 xlCreate() : " + e); } } /** *//** * 读取excel,遍历各个小格获取其中信息,并判断其是否是手机号码,并对正确的手机号码进行显示 注意: 1.sheet, * 以0开始,以workbook.getNumberOfSheets()-1结束 * 2.row,以0开始(getFirstRowNum),以getLastRowNum结束 * 3.cell,以0开始(getFirstCellNum),以getLastCellNum结束, 结束的数目不知什么原因与显示的长度不同,可能会偏长 */ public void readExcel() ...{ // 将被表示成1.3922433397E10的数转化为13922433397 // DecimalFormat df = new DecimalFormat("#"); try ...{ // 创建对Excel工作簿文件的引用 HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream( fileToBeRead)); // System.out.println("===SheetsNum===" + // workbook.getNumberOfSheets());//获取sheet数 for(int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) ...{ if(null != workbook.getSheetAt(numSheets)) ...{ HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet if(!aSheet.isDisplayFormulas())// 判断是显示aSheet中公式的值 aSheet.setDisplayFormulas(true); // System.out.println("+++getFirstRowNum+++" + // aSheet.getFirstRowNum()); // System.out.println("+++getLastRowNum+++" + // aSheet.getLastRowNum()); for(int rowNumOfSheet = 0; rowNumOfSheet <= aSheet .getLastRowNum(); rowNumOfSheet++) ...{ if(null != aSheet.getRow(rowNumOfSheet)) ...{ HSSFRow aRow = aSheet.getRow(rowNumOfSheet); // System.out.println(">>>getFirstCellNum<<<"+aRow.getFirstCellNum()); // System.out.println(">>>getLastCellNum<<<"+aRow.getLastCellNum()); for(short cellNumOfRow = 0; cellNumOfRow <= aRow .getLastCellNum(); cellNumOfRow++) ...{ // System.out.println(">>>rowNumOfSheet<<<"+rowNumOfSheet); // System.out.println(">>>cellNumOfRow<<<"+cellNumOfRow); if(null != aRow.getCell(cellNumOfRow)) ...{ HSSFCell aCell = aRow.getCell(cellNumOfRow); int cellType = aCell.getCellType(); switch(cellType) ...{ case HSSFCell.CELL_TYPE_NUMERIC :// Numeric String strCell = String.valueOf(aCell.getNumericCellValue()); if(HSSFDateUtil.isCellDateFormatted(aCell)) ...{ // double sa = // aCell.getNumericCellValue(); // Date dd = // HSSFDateUtil.getJavaDate(sa); // HSSFDataFormat tt = new // HSSFDataFormat(workbook); // dd.toString(); System.out.println(aCell.getDateCellValue()); } else System.out.println(strCell); break; case HSSFCell.CELL_TYPE_STRING :// String strCell = aCell.getStringCellValue(); System.out.println(strCell); break; case HSSFCell.CELL_TYPE_FORMULA :// formula // strCell =aCell.getCellFormula(); strCell = String.valueOf(aCell.getNumericCellValue()); System.out.println(strCell); break; case HSSFCell.CELL_TYPE_BLANK :// blank strCell = aCell.getStringCellValue(); System.out.println(strCell); break; default : System.out.println("----------------格式读入不正确!");// 其它格式的数据 } } } } } } System.out.println("//sheet end///"); } } catch(Exception e) ...{ System.out.println("ReadExcelError" + e); } } //阅读指定行和列的单元格的值,注意:行、列下标是从0开始的 public void getSpecial(int numRow, int numCol) ...{ try ...{ // 创建对Excel工作簿文件的引用 HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead)); if(null != workbook.getSheet("BPM_LABEL")) ...{ HSSFSheet aSheet = workbook.getSheet("BPM_LABEL");//workbook.getSheetAt(0);//获得一个sheet System.out.println("==========="+aSheet.getLastRowNum()); if(numRow == -1)...{ numRow = aSheet.getLastRowNum(); } for(int i = 2; i <= numRow; i++) ...{ HSSFRow aRow = aSheet.getRow(i); MessageBean bean = new MessageBean(); for(int j= 0 ; j < numCol ; j ++ )...{ HSSFCell aCell = aRow.getCell((short)j); if(aCell != null) ...{ int cellType = aCell.getCellType(); getValue(cellType, aCell); if(j==0)...{ bean.setId(getValue(cellType, aCell)); }else if(j== 1)...{ bean.setMessageKey(getValue(cellType, aCell)); }else if(j==2)...{ bean.setMessageValue(getValue(cellType, aCell)); } } } list.add(bean); } } } catch(Exception e) ...{ System.out.println("ReadExcelError" + e); } } private String getValue(int cellType , HSSFCell aCell) ...{ String strCell = ""; switch(cellType) ...{ case 0 ://Numeric strCell = String.valueOf(aCell.getNumericCellValue()); if(HSSFDateUtil.isCellDateFormatted(aCell))...{ System.out.println("00000000000000000" + aCell.getDateCellValue()); }else...{ return "ID:"+strCell.substring(0,strCell.indexOf("."));//System.out.println(strCell); } break; case 1 ://String strCell = aCell.getStringCellValue(); //return strCell;//System.out.println("111111111111111111" + strCell); break; case 2 ://formula //aSheet.setDisplayFormulas(true); strCell = String.valueOf(aCell.getNumericCellValue()); //return strCell;// System.out.println("2222222222222222222" + strCell); break; case 3 ://blank strCell = aCell.getStringCellValue(); //return strCell;//System.out.println("3333333333333333333" + strCell); break; default : System.out.println("----------------格式读入不正确!");//其它格式的数据 } return strCell; } public static void main(String[] args) ...{ POItest poi = new POItest(); //poi.CreateExcel(); //poi.readExcel(); poi.getSpecial(-1, (short)3);//第五行第五列的数据值 poi.findSame(); }}