JAVA读取、写入Excel表格(含03版)

淩亂°似流年 2022-06-06 13:50 252阅读 0赞

## 引言 ##

工作中可能会遇到对Excel读取和写入，如果我们自己手动写的话，会很麻烦，但是Apache中有poi工具类。poi工具类封装好了对于Excel读取和写入，我们需要用的时候，直接调用该方法就好了。  
注:03和07的写法不一致。  
区别如下

// HSSFWorkbook 2003的excel .xls,XSSFWorkbook导入2007的excel   .xlsx
            HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File(file)));
            XSSFWorkbook workbook=new XSSFWorkbook(new FileInputStream(new File(file))));

## 代码实现 ##

import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    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.xssf.usermodel.XSSFWorkbook;
    
    import com.alibaba.fastjson.JSONObject;
    
    
    
    /** * * Title: excelTest * Description: excel表格读取 * 注意:引用poi 架包版本要一致 * 如: * poi-3.13.jar * poi-ooxml-3.13.jar * poi-ooxml-schemas-3.13.jar * poi-scratchpad-3.13.jar * 这些架包版本随意 * stax-api.jar * xmlbeans.jar * dom4j.jar * Version:1.0.0 * @author pancm */
    public class excelTest { 
    
        private static final String path="D:\\file\\test.xlsx"; 
        private static final String path1="D:\\file\\test1.xlsx"; 
    
        public static void main(String[] args) throws FileNotFoundException, IOException {
            readExcel(path);
            writeExcel(path1);
        }
    
        /** * 读取Excel表格内容 * @throws FileNotFoundException * @throws IOException */
        @SuppressWarnings({ "rawtypes", "unchecked" })
        private static void readExcel(String str) throws FileNotFoundException, IOException{
            File file=new File(str);
            // HSSFWorkbook 2003的excel .xls,XSSFWorkbook导入2007的excel .xlsx
    // HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File(file)));
            XSSFWorkbook workbook=new XSSFWorkbook(new FileInputStream(file));
             Sheet sheet=workbook.getSheetAt(0);//读取第一个 sheet
             List list= new ArrayList<>();
             Row row=null;
             int count=sheet.getPhysicalNumberOfRows();
            //逐行处理 excel 数据
            for (int i = 1; i <count; i++) {
                 JSONObject json=new JSONObject();
                  row=sheet.getRow(i);
                 Cell cell0 = row.getCell(0);
                 //设置取值为String
                 //整数数据要转，否则会变成浮点数
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                Cell cell1 = row.getCell(1); 
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                json.put("Id",cell0.toString()); //编号
                json.put("Name",cell1.toString()); //名称
                list.add(json);
                System.out.println("json:"+json);
            }
            workbook.close();
            System.out.println("list:"+list);
        }
    
        /** * 写入Excel表格内容 * @throws FileNotFoundException * @throws IOException */
        @SuppressWarnings({ "resource", "rawtypes", "unchecked" })
        private static void writeExcel(String str) throws FileNotFoundException, IOException{
            File file=new File(str);
            // HSSFWorkbook 2003的excel .xls,XSSFWorkbook导入2007的excel .xlsx
    // HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File(file)));
            XSSFWorkbook workbook=new XSSFWorkbook(new FileInputStream(file));
            List resultList =new ArrayList<>();
    
            Sheet sheet1 = workbook.createSheet();//创建 sheet 对象
            Row row = sheet1.createRow(0);//第一行，标题
            row.createCell(0).setCellValue("A");
            row.createCell(1).setCellValue("B");
            row.createCell(2).setCellValue("C");
            row.createCell(3).setCellValue("D");
            row.createCell(4).setCellValue("E");
            //拼接数据
            for(int i=1;i<=10;i++){
                JSONObject json1=new JSONObject();
                json1.put("A", i);
                json1.put("B", i*2);
                json1.put("C", i*3);
                json1.put("D", i*4);
                json1.put("E", i*5);
                resultList.add(json1);
            }
            System.out.println("resultList:"+resultList);
            Row row1;
            for (int i = 1, len = resultList.size(); i <=len; i++) {
       //循环创建数据行
                //因为第一行已经设置了，所以从第二行开始
                row1 = sheet1.createRow(i);
                JSONObject json=(JSONObject) resultList.get(i-1);
                row1.createCell(0).setCellValue(json.getString("A"));
                row1.createCell(1).setCellValue(json.getString("B"));
                row1.createCell(2).setCellValue(json.getString("C"));
                row1.createCell(3).setCellValue(json.getString("D"));
                row1.createCell(4).setCellValue(json.getString("E"));
            }
            FileOutputStream fos = new FileOutputStream(path1); 
            workbook.write(fos);//写文件
            fos.close();
            System.out.println("写入成功！");
        }
    }

## 示例图 ##

### 读取Excel ###

新建一个Excel表格，设置表格内容。  
![这里写图片描述][SouthEast]

关闭此Excel，运行代码，打印获取的数据。  
![这里写图片描述][SouthEast 1]

### 写入Excel ###

创建一个新的Excel。  
![这里写图片描述][SouthEast 2]

关闭此Excel，运行代码  
![这里写图片描述][SouthEast 3]

再次打开此Excel  
![这里写图片描述][SouthEast 4]

[SouthEast]: /images/20220606/47ae825d95a541c1937ff0487362b3cf.png
[SouthEast 1]: /images/20220606/e35f75265cd8497bade4002531b56961.png
[SouthEast 2]: /images/20220606/75ddb419ce9043d19b81931bc597b841.png
[SouthEast 3]: /images/20220606/a82cf58a2a324c21a9c15240444f8063.png
[SouthEast 4]: /images/20220606/972836941d924ecf83b14e67b19820cf.png