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("文件生成");      }  }