Java使用poi操作excel实例解析

时间:2021-11-23 04:50:10

本文实例为大家分享了Java使用poi操作excel的具体代码,供大家参考,具体内容如下

依赖poi的jar包,pom.xml配置如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<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测试代码分别如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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();
    }
  }
}
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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();
    }
  }
}

以上就是本文的全部内容,希望对大家学习java程序设计有所帮助。