java读取写入exec-方法一

1.添加处理excel的依赖jar包

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.14</version>
    </dependency>
    <!-- 处理excel和上面功能是一样的-->
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.10</version>
    </dependency>

2：向excel中写入内容的类

WriteExcel.java
　　package com.li.controller;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcel {
private static final String EXCEL_XLS = “xls”;
private static final String EXCEL_XLSX = “xlsx”;

public static void main(String[] args) {
    
    Map<String, String> dataMap=new HashMap<String, String>();
    dataMap.put("BankName", "BankName");
    dataMap.put("Addr", "Addr");
    dataMap.put("Phone", "Phone");
    List<Map> list=new ArrayList<Map>();
    list.add(dataMap);
    writeExcel(list, 3, "D:/writeExcel.xlsx");
    
}

public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){
    OutputStream out = null;
    try {
        // 获取总列数
        int columnNumCount = cloumnCount;
        // 读取Excel文档
        File finalXlsxFile = new File(finalXlsxPath);
        Workbook workBook = getWorkbok(finalXlsxFile);
        // sheet 对应一个工作页
        Sheet sheet = workBook.getSheetAt(0);
        /**
         * 删除原有数据，除了属性列
         */
        int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
        System.out.println("原始数据总行数，除属性列：" + rowNumber);
        for (int i = 1; i <= rowNumber; i++) {
            Row row = sheet.getRow(i);
            sheet.removeRow(row);
        }
        // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
        out =  new FileOutputStream(finalXlsxPath);
        workBook.write(out);
        /**
         * 往Excel中写新数据
         */
        for (int j = 0; j < dataList.size(); j++) {
            // 创建一行：从第二行开始，跳过属性列
            Row row = sheet.createRow(j + 1);
            // 得到要插入的每一条记录
            Map dataMap = dataList.get(j);
            String name = dataMap.get("BankName").toString();
            String address = dataMap.get("Addr").toString();
            String phone = dataMap.get("Phone").toString();
            for (int k = 0; k <= columnNumCount; k++) {
            // 在一行内循环
            Cell first = row.createCell(0);
            first.setCellValue(name);
    
            Cell second = row.createCell(1);
            second.setCellValue(address);
    
            Cell third = row.createCell(2);
            third.setCellValue(phone);
            }
        }
        // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
        out =  new FileOutputStream(finalXlsxPath);
        workBook.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        try {
            if(out != null){
                out.flush();
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    System.out.println("数据导出成功");
}

/**
 * 判断Excel的版本,获取Workbook
 * @param in
 * @param filename
 * @return
 * @throws IOException
 */
public static Workbook getWorkbok(File file) throws IOException{
    Workbook wb = null;
    FileInputStream in = new FileInputStream(file);
    if(file.getName().endsWith(EXCEL_XLS)){     //Excel&nbsp;2003
        wb = new HSSFWorkbook(in);
    }else if(file.getName().endsWith(EXCEL_XLSX)){    // Excel 2007/2010
        wb = new XSSFWorkbook(in);
    }
    return wb;
}

}

3:读取Excel中的数据，并写入list中

package com.li.controller;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ReadExcel {
public static void main(String[] args) {
ReadExcel obj = new ReadExcel();
// 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
File file = new File(“D:/readExcel.xls”);
List excelList = obj.readExcel(file);
System.out.println(“list中的数据打印出来”);
for (int i = 0; i < excelList.size(); i++) {
List list = (List) excelList.get(i);
for (int j = 0; j < list.size(); j++) {
System.out.print(list.get(j));
}
System.out.println();
}

}
// 去读Excel的方法readExcel，该方法的入口参数为一个File对象
public List readExcel(File file) {
    try {
        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(file.getAbsolutePath());
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(is);
        // Excel的页签数量
        int sheet_size = wb.getNumberOfSheets();
        for (int index = 0; index < sheet_size; index++) {
            List<List> outerList=new ArrayList<List>();
            // 每个页签创建一个Sheet对象
            Sheet sheet = wb.getSheet(index);
            // sheet.getRows()返回该页的总行数
            for (int i = 0; i < sheet.getRows(); i++) {
                List innerList=new ArrayList();
                // sheet.getColumns()返回该页的总列数
                for (int j = 0; j < sheet.getColumns(); j++) {
                    String cellinfo = sheet.getCell(j, i).getContents();
                    if(cellinfo.isEmpty()){
                        continue;
                    }
                    innerList.add(cellinfo);
                    System.out.print(cellinfo);
                }
                outerList.add(i, innerList);
                System.out.println();
            }
            return outerList;
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (BiffException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}

}