Java使用poi操作cexel

java操作excel比较简单，但是时间长了就会忘记，因此基本的简单操作做个记录。

依赖poi的jar包，pom.xml配置如下：

 

<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>excelDemo1</groupId>
  <artifactId>excelDemo1</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>excelDemo1 Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
    </dependency>
  </dependencies>
  <build>
    <finalName>excelDemo1</finalName>
  </build>
</project>

 

 

 

 

 

相应的java测试代码分别如下：

 

package excelDemo1;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
public class ExcelDemo0 {
    /**
     * java生成excel文件并写入磁盘
     *
     * @author：tuzongxun
     * @Title: main
     * @param@param args
     * @return void
     * @date Apr 28,2016 7:32:52 PM
     * @throws
     */
    public static void main(String[] args) {
       //C:\Users\tuzongxun123\Desktop桌面，windows和linux的斜杠不一样，而且java对于“/”需要转义处理，File.separator可以实现跨平台
       File file = new File("C:" + File.separator + "Users" + File.separator
              + "tuzongxun123" + File.separator + "Desktop" + File.separator
              + "ioFile" + File.separator + "user.xls");
       try {
           OutputStream outputStream = new FileOutputStream(file);
           // 创建excel文件，注意这里的hssf是excel2007及以前版本可用，2007版以后的不可用，要用xssf
           HSSFWorkbook workbook = new HSSFWorkbook();
           // 创建excel工作表
           HSSFSheet sheet = workbook.createSheet("user");
           // 为工作表增加一行
           HSSFRow row = sheet.createRow(0);
           // 在指定的行上增加两个单元格
           row.createCell(0).setCellValue("name");
           row.createCell(1).setCellValue("password");
           // 调用输出流把excel文件写入到磁盘
           workbook.write(outputStream);
           // 关闭输出流
           outputStream.close();
       } catch (Exception e) {
           e.printStackTrace();
       }
    }
}

 

 

package excelDemo1;
 
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
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.poifs.filesystem.POIFSFileSystem;
 
/**
 * 读取excel文件
 *
 * @author tuzongxun123
 *
 */
public class ExcelDemo2 {
    public static void main(String[] agrs) {
       try {
           // 获取excel文件输入流
           FileInputStream fileInputStream = new FileInputStream("C:"
                  + File.separator + "Users" + File.separator
                  + "tuzongxun123" + File.separator + "Desktop"
                  + File.separator + "ioFile" + File.separator + "user.xls");
           BufferedInputStream bufferedInputStream = newBufferedInputStream(
                  fileInputStream);
           POIFSFileSystem fileSystem = new POIFSFileSystem(
                  bufferedInputStream);
           // 获取excel文件
           HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileSystem);
           // 根据名称获取指定的excel工作薄
           HSSFSheet sheet = hssfWorkbook.getSheet("user");
           // 这里实际上可以用sheet.rowIterator()来遍历
           for (int i = 1;; i++) {
              HSSFRow row = sheet.getRow(i);
              if (row != null) {
                  String nameString1 = row.getCell(0).getStringCellValue();
                  String password = row.getCell(i).getStringCellValue();
                  System.out.println("name:" + nameString1);
                  System.out.println("password:" + password);
                  bufferedInputStream.close();
              } else {
                  bufferedInputStream.close();
                  return;
              }
           }
 
       } catch (Exception e) {
           e.printStackTrace();
       }
    }
}