poi读写excel表格

利用poi读取excel文件内容

import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class PoiRead {
    public static void main(String[] args) {
        testReadExcel("d:/poi_test.xls");
    }
    //编写读取的方法
    public static void testReadExcel(String filepath) {  
          try {  
            // 读取Excel  
            Workbook wb = new HSSFWorkbook(new FileInputStream(filepath));  

            // 获取sheet数目  
            for (int t = 0; t < wb.getNumberOfSheets(); t++) {  
              Sheet sheet = wb.getSheetAt(t);  
              Row row = null;  
              int lastRowNum = sheet.getLastRowNum();  

              // 循环读取  
              for (int i = 0; i <= lastRowNum; i++) {  
                row = sheet.getRow(i);  
                if (row != null) {  
                  // 获取每一列的值  
                  for (int j = 0; j < row.getLastCellNum(); j++) {  
                    Cell cell = row.getCell(j);  
                    String value = getCellValue(cell) ;  
                    if(!value.equals("")){  
                      System.out.print(value + " | ");  
                    }  
                  }  
                  System.out.println();  
                }  
              }  
            }
            wb.close();
          } catch (Exception e) {  
            e.printStackTrace();  
          }  
        }
    /**
     * 获取单元格的值
     * 设置获取日期的格式
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {  
          Object result = "";  

          if (cell != null) {  
            switch (cell.getCellTypeEnum()) {  
            case  STRING:  
              result = cell.getStringCellValue();  
              break;  

            case NUMERIC: 

              //设置日期格式
              SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm");
              double time = cell.getNumericCellValue();  
              Date date = DateUtil.getJavaDate(time);
              result= format.format(date);
              break;  
            case BOOLEAN:  
              result = cell.getBooleanCellValue();  
              break;  
            case FORMULA:  
              result = cell.getCellFormula();  
              break;  
            case ERROR:  
              result = cell.getErrorCellValue();  
              break;  
            case BLANK:  
              break;  
            default:  
              break;  
            }  
          }  
          return result.toString();  

        }

}

利用poi写入excel文件

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFCreationHelper;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;

public class PoiWrite {
    public static void main(String[] args) throws IOException {  
        // 创建工作薄  
        HSSFWorkbook workBook = new HSSFWorkbook();  
        // 在工作薄中创建一工作表  
        HSSFSheet sheet = workBook.createSheet();  
        // 在指定的索引处创建一行  
//        HSSFRow row = sheet.createRow(0);  
        // 在指定的索引处创建一列（单元格）
        String[] head={"id","name","sex","createtime"};
        String[] body={"userid","user","男,女"};
        for (int i = 0; i <= 10; i++) {
            HSSFRow row = sheet.createRow(i);
            if(i==0)
                for(int j=0;j<head.length;j++){
                    //写入值
                    row.createCell(j).setCellValue(head[j]);
                }
            else
                for(int j=0;j<body.length+1;j++){
                    if(j<2)
                        row.createCell(j).setCellValue(body[j]+i);
                    else if(j==2)
                        row.createCell(2).setCellValue(body[2].split(",")[(int)Math.floor(Math.random()*(body[2].split(",").length))]);
                    else {
                        HSSFCell cell = row.createCell(j);
                        //设置时间    
                        cell.setCellValue(new Date());
                        //判断是否为日期格式
                        System.out.println(DateUtil.isCellDateFormatted(cell));
                        //设置日期的格式
                        HSSFCellStyle style = workBook.createCellStyle();
                        //方法1：格式必须为 "m/d/yy h:mm" 或  "m/d/yy"
                        //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

                        //方法2：利用  HSSFCreationHelper 对象
                        HSSFCreationHelper helper = workBook.getCreationHelper();
                        short fmt = helper.createDataFormat().getFormat("yy/MM/dd hh:mm:ss");
                        style.setDataFormat(fmt);
                        //将格式赋给单元格
                        cell.setCellStyle(style);
                        //再次判断是否为日期格式
                        System.out.println("再次判断的结果为："+DateUtil.isCellDateFormatted(cell));
                    }
                }

        }
        // 新建一输出流并把相应的eHcel文件存盘  
        FileOutputStream fos = new FileOutputStream("d:/poi_test.xls");  
        workBook.write(fos);  
        fos.flush();  
        //操作结束，关闭流  
        fos.close();  
        workBook.close();
        System.out.println("文件生成");  
    }  

}