Excel 入门简单读写

来源：互联网发布：东华理工大学软件学院编辑：程序博客网时间：2024/05/16 02:14

在用Maven管理项目的时候，在加入POI的时候需要配置一下两个依赖的jar配置

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.8</version>
</dependency>

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.8</version>
</dependency>

package org.ksea.poi.test;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

public class PoiTest {
@Test
public void writeExcel(){
  Workbook workbook=null;
  FileOutputStream fos=null;
  try {
   workbook= new HSSFWorkbook();
   //workbook=new SXSSFWorkbook();
   fos= new FileOutputStream(new File("e:/poi/t.xls"));
   Sheet sheet=workbook.createSheet("测试写入Excel");
   Row row=sheet.createRow(0);
   //设置行高
   row.setHeightInPoints(40);
   Cell c=row.createCell(0);
   c.setCellValue("标识");
   c=row.createCell(1);
   c.setCellValue("用户名");
   workbook.write(fos);

  } catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{

  }



}

@Test
public void testRead0() {
  try {
   Workbook workbook = WorkbookFactory.create(new File(
     "E:/poi/111.xlsx"));
   Sheet sheet = workbook.getSheetAt(0);
   Row row = sheet.getRow(0);
   Cell cell = row.getCell(0);
   System.out.println(cell.getNumericCellValue());
  } catch (InvalidFormatException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

}

public String switchCell(Cell cell){
  String cellValue="";
    switch (cell.getCellType()) {
  case Cell.CELL_TYPE_BLANK:
   cellValue="";
   break;
  case Cell.CELL_TYPE_BOOLEAN:
   cellValue=String.valueOf(cell.getBooleanCellValue());
   break;
  case Cell.CELL_TYPE_FORMULA:
   cellValue=String.valueOf(cell.getCellFormula());
   break;
  case Cell.CELL_TYPE_NUMERIC:
   cellValue=String.valueOf(cell.getNumericCellValue());
   break;
  case Cell.CELL_TYPE_STRING :
   cellValue=cell.getStringCellValue();
   break;
  }
  return cellValue;
}

@Test
public void testRead1() {
  try {
   Workbook workbook = WorkbookFactory.create(new File("E:/poi/111.xlsx"));
   Sheet sheet = workbook.getSheetAt(0);
   System.out.println(sheet.getSheetName()+"---"+sheet.getLastRowNum());
   Row row=null;
   for (int i = 0; i < sheet.getLastRowNum()+1; i++) {
     row=sheet.getRow(i);
    for (int j = 0; j <row.getLastCellNum(); j++) {
     Cell cell= row.getCell(j);
     System.out.print(switchCell(cell)+"--------");
    }
    System.out.println("");
   }
  } catch (InvalidFormatException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

}
}

0 0