poi操作excel 实例

View Code

public class ExcelWriter {

 public static String outputFile = "D:\\test.xls";

 public static void main(String argv[]) {

  try {

   // 创建新的Excel 工作簿
   HSSFWorkbook workbook = new HSSFWorkbook();

   // 在Excel工作簿中建一工作表，其名为缺省值
   // 如要新建一名为"效益指标"的工作表，其语句为：
   // HSSFSheet sheet = workbook.createSheet("效益指标");
   HSSFSheet sheet = workbook.createSheet();
   // 在索引0的位置创建行（最顶端的行）
   HSSFRow row = sheet.createRow((short) 0);

   HSSFCell empCodeCell = row.createCell((short) 0);
   empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   empCodeCell.setCellValue("员工代码");
   
   HSSFCell empNameCell = row.createCell((short) 1);
   empNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   empNameCell.setCellValue("姓名");

   HSSFCell sexCell = row.createCell((short) 2);
   sexCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   sexCell.setCellValue("性别");
   
   HSSFCell birthdayCell = row.createCell((short) 3);
   birthdayCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   birthdayCell.setCellValue("出生日期");

   HSSFCell orgCodeCell = row.createCell((short) 4);
   orgCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   orgCodeCell.setCellValue("机构代码");

   HSSFCell orgNameCell = row.createCell((short) 5);
   orgNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   orgNameCell.setCellValue("机构名称");
   
   HSSFCell contactTelCell = row.createCell((short) 6);
   contactTelCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   contactTelCell.setCellValue("联系电话");

   HSSFCell zjmCell = row.createCell((short) 7);
   zjmCell.setCellType(HSSFCell.CELL_TYPE_STRING);
   zjmCell.setCellValue("助记码");
   for (int i=1; i<=10; i++) {
    row = sheet.createRow((short) i);
    empCodeCell = row.createCell((short) 0);
    empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    empCodeCell.setCellValue("001_" + i);
    
    empNameCell = row.createCell((short) 1);
    empNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    empNameCell.setCellValue("张三_" + i);

    sexCell = row.createCell((short) 2);
    sexCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    sexCell.setCellValue("性别_" + i);
    
    birthdayCell = row.createCell((short) 3);
    birthdayCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    birthdayCell.setCellValue("出生日期_" + i);

    orgCodeCell = row.createCell((short) 4);
    orgCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    orgCodeCell.setCellValue("机构代码_" + i);

    orgNameCell = row.createCell((short) 5);
    orgNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    orgNameCell.setCellValue("机构名称_" + i);
    
    contactTelCell = row.createCell((short) 6);
    contactTelCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    contactTelCell.setCellValue("联系电话_" + i);

    zjmCell = row.createCell((short) 7);
    zjmCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    zjmCell.setCellValue("助记码_" + i);
    
   } 
   // 新建一输出文件流
   FileOutputStream fOut = new FileOutputStream(outputFile);
   // 把相应的Excel 工作簿存盘
   workbook.write(fOut);
   fOut.flush();
   // 操作结束，关闭文件
   fOut.close();
   System.out.println("文件生成...");

  } catch (Exception e) {
   System.out.println("已运行 xlCreate() : " + e);
  }
 }
}

/**读取**/

import java.io.FileInputStream;

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 ExcelReader {

 public static String fileToBeRead = "D:\\test.xls";

 /**
  * @param args
  */
 public static void main(String[] args) {
  try {
   // 创建对Excel工作簿文件的引用
   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
     fileToBeRead));
   HSSFSheet sheet = workbook.getSheetAt(0);
   int i = 0;
   while (true) {
    HSSFRow row = sheet.getRow(i);
    if (row == null) {
     break;
    }
    HSSFCell cell0 = row.getCell((short)0);
    HSSFCell cell1 = row.getCell((short)1);
    HSSFCell cell2 = row.getCell((short)2);
    HSSFCell cell3 = row.getCell((short)3);
    HSSFCell cell4 = row.getCell((short)4);
    HSSFCell cell5 = row.getCell((short)5);
    HSSFCell cell6 = row.getCell((short)6);
    
    System.out.print(cell0.getStringCellValue());
    System.out.print("," + cell1.getStringCellValue());
    System.out.print("," + cell2.getStringCellValue());
    System.out.print("," + cell3.getStringCellValue());
    System.out.print("," + cell4.getStringCellValue());
    System.out.print("," + cell5.getStringCellValue());
    System.out.println("," + cell6.getStringCellValue());
    i++;
   }
  } catch (Exception e) {
   System.out.println("已运行xlRead() : " + e);
  }
 }

}

 

转载于:https://www.cnblogs.com/yx512/archive/2012/08/08/2627750.html