import java.io.FileInputStream;
import java.io.FileOutputStream;

import java.text.DecimalFormat;

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

/**
 * 一个Excel文件的层次：Excel文件->工作表->行->单元格 对应到POI中，为：workbook->sheet->row->cell
 *
 * @author Administrator
 *
 */
public class POItest {

 public static String outputFile = "e:\\dest.xls";

 public static String fileToBeRead = "e:\\dest.xls";

 /**
  * 使用poi创建excel文件
  *
  */
 public void CreateExcel() {
  try {
   // 创建新的Excel 工作簿
   HSSFWorkbook workbook = new HSSFWorkbook();
   // 在Excel工作簿中建一工作表，其名为缺省值
   HSSFSheet sheet = workbook.createSheet("Sheet-0");

   for (int i = 0; i < 10; i++) {
    // 在索引0的位置创建行（最顶端的行）
    HSSFRow row = sheet.createRow((short) i);

    for (int j = 0; j < 10; j++) {
     // 在索引0的位置创建单元格（左上端）
     HSSFCell cell = row.createCell((short) j);
     // 定义单元格为字符串类型
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     // 在单元格中输入一些内容
     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
     cell
       .setCellValue(new HSSFRichTextString("汉字1111111111"
         + j));
    }
   }

   // 新建一输出文件流
   FileOutputStream fOut = new FileOutputStream(outputFile);
   // 把相应的Excel 工作簿存盘
   workbook.write(fOut);
   fOut.flush();
   // 操作结束，关闭文件
   fOut.close();
   System.out.println("文件生成...");

  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 /**
  *
  * 读取excel，遍历各个小格获取其中信息
  */
 public void readExcel() {

  DecimalFormat df = new DecimalFormat("#");

  try {
   // 创建对Excel工作簿文件的引用
   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
     fileToBeRead));
   for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
    if (null != workbook.getSheetAt(numSheets)) {
     HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet

     for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet
       .getLastRowNum(); rowNumOfSheet++) {
      if (null != aSheet.getRow(rowNumOfSheet)) {
       HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

       for (short cellNumOfRow = 0; cellNumOfRow <= aRow
         .getLastCellNum(); cellNumOfRow++) {

        if (null != aRow.getCell(cellNumOfRow)) {
         HSSFCell aCell = aRow.getCell(cellNumOfRow);

         int cellType = aCell.getCellType();

         switch (cellType) {
         case 0:// Numeric
          String strCell = df.format(aCell
            .getNumericCellValue());

          System.out.println(strCell);

          break;
         case 1:// String
          strCell = aCell.getStringCellValue();

          System.out.println(strCell);

          break;
         default:
          // System.out.println("格式不对不读");//其它格式的数据
         }

        }

       }
      }

     }

    }

   }

  } catch (Exception e) {
   e.printStackTrace();
  }

 }

 public static void main(String[] args) {
  POItest poi = new POItest();
  poi.CreateExcel();
  poi.readExcel();

 }
}