java操作excel常用的两种方式

灰太狼 2022-10-17 13:47 157阅读 0赞

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

对于两者的区别网上有测试如下：

测试结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70]

> 在小数据量时jxl快于poi，在大数据量时poi要快于jxl。但差距都不明显。

## 一.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();
       }
    }

**读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();
       }
    }

## 二.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);
       }
    }

**读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();
             }
          }
       }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70]: /images/20221014/bca12172257340b5ba21ae3380e95107.png