第四篇：JAVA操作Excel

Excel是我们平时工作中比较常用的用于存储二维表数据的，Java也可以直接对Excel进行操作，在这篇博客中将为大家介绍两种操作Excel的方式，分别为：jxl和poi。

jxl

写Excel

import java.io.File;import java.io.IOException;import jxl.Workbook;import jxl.write.Label;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;/** * jxl写Excel *  * @author jianggujin *  */public class JxlWriteDemo{   public static void main(String[] args) throws IOException, WriteException   {      File xlsFile = new File("jxl.xls");      // 创建一个工作簿      WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);      // 创建一个工作表      WritableSheet sheet = workbook.createSheet("sheet1", 0);      for (int row = 0; row < 10; row++)      {         for (int col = 0; col < 10; col++)         {            // 向工作表中添加数据            sheet.addCell(new Label(col, row, "data" + row + col));         }      }      workbook.write();      workbook.close();   }}
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
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

读Excel

import java.io.File;import java.io.IOException;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;/** * jxl读excel *  * @author jianggujin *  */public class JxlReadDemo{   public static void main(String[] args) throws BiffException, IOException   {      File xlsFile = new File("jxl.xls");      // 获得工作簿对象      Workbook workbook = Workbook.getWorkbook(xlsFile);      // 获得所有工作表      Sheet[] sheets = workbook.getSheets();      // 遍历工作表      if (sheets != null)      {         for (Sheet sheet : sheets)         {            // 获得行数            int rows = sheet.getRows();            // 获得列数            int cols = sheet.getColumns();            // 读取数据            for (int row = 0; row < rows; row++)            {               for (int col = 0; col < cols; col++)               {                  System.out.printf("%10s", sheet.getCell(col, row)                        .getContents());               }               System.out.println();            }         }      }      workbook.close();   }}
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
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

poi

写Excel

import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/** * Poi写Excel *  * @author jianggujin *  */public class PoiWriteDemo{   public static void main(String[] args) throws IOException   {      // 创建工作薄      HSSFWorkbook workbook = new HSSFWorkbook();      // 创建工作表      HSSFSheet sheet = workbook.createSheet("sheet1");      for (int row = 0; row < 10; row++)      {         HSSFRow rows = sheet.createRow(row);         for (int col = 0; col < 10; col++)         {            // 向工作表中添加数据            rows.createCell(col).setCellValue("data" + row + col);         }      }      File xlsFile = new File("poi.xls");      FileOutputStream xlsStream = new FileOutputStream(xlsFile);      workbook.write(xlsStream);   }}
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
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

读Excel

import java.io.File;import java.io.IOException;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;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;/** * Poi写Excel *  * @author jianggujin *  */public class PoiReadDemo{   public static void main(String[] args) throws IOException,         InvalidFormatException   {      File xlsFile = new File("poi.xls");      // 获得工作簿      Workbook workbook = WorkbookFactory.create(xlsFile);      // 获得工作表个数      int sheetCount = workbook.getNumberOfSheets();      // 遍历工作表      for (int i = 0; i < sheetCount; i++)      {         Sheet sheet = workbook.getSheetAt(i);         // 获得行数         int rows = sheet.getLastRowNum() + 1;         // 获得列数，先获得一行，在得到改行列数         Row tmp = sheet.getRow(0);         if (tmp == null)         {            continue;         }         int cols = tmp.getPhysicalNumberOfCells();         // 读取数据         for (int row = 0; row < rows; row++)         {            Row r = sheet.getRow(row);            for (int col = 0; col < cols; col++)            {               System.out.printf("%10s", r.getCell(col).getStringCellValue());            }            System.out.println();         }      }   }}
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
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

jxl和Poi两种方式在操作Excel的时候，各有优缺点，所以在开发时应考虑实际情况选用。