java读取与写入excel(.xls .xlsx)

maven依赖:

首先在maven中添加:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

读取:

java读取excel的思路很简单,先读取行(HXXFRow类或XSSFRow类),然后得到列(.getRow(列下标)),这样就可以定位一个单元格了

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;

/**使用例子
 * ReadExcel excel = new ReadExcel("D:\\myexcel.xlsx");
 * String[] firstLine = excel.readLine(0);//得到第一行数据
 * String[] firstLine = excel.readLine(1);//得到第二行数据
 */
public class ReadExcel {

    private HSSFSheet hssfSheet;//.xls
    private XSSFSheet xssfSheet;//.xlsx

    public int getAllRowNumber() {
        return xssfSheet.getLastRowNum();
    }

    /*读取 excel 下标为 rowNumber 的那一行的全部数据*/
    public String[] readLine(int rowNumber) {
        XSSFRow row = xssfSheet.getRow(rowNumber);
        if (row != null) {
            String[] resultStr = new String[row.getLastCellNum()];
            for (int i = 0; i < row.getLastCellNum(); i++) {
                resultStr[i] = row.getCell(i).getStringCellValue();
            }
            return resultStr;
        }
        return null;
    }

    public ReadExcel(String excelPath) throws Exception {
        String fileType = excelPath.substring(excelPath.lastIndexOf(".") + 1, excelPath.length());
        // 创建工作文档对象
        InputStream in = new FileInputStream(excelPath);
        HSSFWorkbook hssfWorkbook = null;//.xls
        XSSFWorkbook xssfWorkbook = null;//.xlsx
        //根据后缀创建读取不同类型的excel
        if (fileType.equals("xls")) {
            hssfWorkbook = new HSSFWorkbook(in);//它是专门读取.xls的
        } else if (fileType.equals("xlsx")) {
            xssfWorkbook = new XSSFWorkbook(in);//它是专门读取.xlsx的
        } else {
            throw new Exception("文档格式后缀不正确!!！");
        }
        /*这里默认只读取第 1 个sheet*/
        if (hssfWorkbook != null) {
            this.hssfSheet = hssfWorkbook.getSheetAt(0);
        } else if (xssfWorkbook != null) {
            this.xssfSheet = xssfWorkbook.getSheetAt(0);
        }
    }
}

写入:

java写入excel的思路是:

1.先创建一个Workbook类,然后根据想要创建的文档类型来new 不同类型的实例(.xls就是new HSSFWorkbook() , 而.xlsx是new XSSFWorkbook()),

2.创建好文档对象后,创建第一张表(workbook.createSheet("String类型的表名称"))

3.然后在这张表(sheet)中的指定行号中创建行(sheet.createRow(int类型的指定行号))

4.在这行中指定一个列号,用来指定具体单元格的坐标(row.createCell(int类型的列号))

5.写入数据(cell.setCellValue(写入的内容))

package excelUtils;

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;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class WriteExcel {

    private String pathname;
    private Workbook workbook;
    private Sheet sheet1;

    /**使用栗子
     * WriteExcel excel = new WriteExcel("D:\\myexcel.xlsx");
     * excel.write(new String[]{"1","2"}, 0);//在第1行第1个单元格写入1,第一行第二个单元格写入2
     */
    public void write(String[] writeStrings, int rowNumber) throws Exception {
        //将内容写入指定的行号中
        Row row = sheet1.createRow(rowNumber);
        //遍历整行中的列序号
        for (int j = 0; j < writeStrings.length; j++) {
            //根据行指定列坐标j,然后在单元格中写入数据
            Cell cell = row.createCell(j);
            cell.setCellValue(writeStrings[j]);
        }
        OutputStream stream = new FileOutputStream(pathname);
        workbook.write(stream);
        stream.close();
    }

    public WriteExcel(String excelPath) throws Exception {
        //在excelPath中需要指定具体的文件名(需要带上.xls或.xlsx的后缀)
        this.pathname = excelPath;
        String fileType = excelPath.substring(excelPath.lastIndexOf(".") + 1, excelPath.length());
        //创建文档对象
        if (fileType.equals("xls")) {
            //如果是.xls,就new HSSFWorkbook()
            workbook = new HSSFWorkbook();
        } else if (fileType.equals("xlsx")) {
            //如果是.xlsx,就new XSSFWorkbook()
            workbook = new XSSFWorkbook();
        } else {
            throw new Exception("文档格式后缀不正确!!！");
        }
        // 创建表sheet
        sheet1 = workbook.createSheet("sheet1");
    }
}