Java代码解析Excel表格_lacksheet-CSDN博客

本文链接：https://blog.csdn.net/qq_32200417/article/details/78233907

用前须知

1. 导包
- JXL：只能解析xls
- POI：可以解析xlsx和office文档

//JXL按行读取xls文件
package basc;

import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class Read_Row {

    public static void main(String[] args) throws Exception{
        Workbook wb = Workbook.getWorkbook(new File("test.xls"));
        Sheet sheet = wb.getSheet(0);
        int columns = sheet.getRows();
        for(int i=0;i<columns;i++){
            Cell[] cells = sheet.getRow(i);
            for(Cell cell:cells){
                System.out.print(cell.getContents()+"\t");
            }
            System.out.println("");
        }
    }

}

//JXL按列读取xls文件
package basc;

import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class Read_Column {

    public static void main(String[] args)throws Exception {

        Workbook wb = Workbook.getWorkbook(new File("test.xls"));
        Sheet sheet = wb.getSheet(0);
        int columns = sheet.getColumns();
        for(int i=0;i<columns;i++){
            Cell[] cells = sheet.getColumn(i);
            for(Cell cell:cells){
                System.out.print(cell.getContents()+"\t");
            }
            System.out.println("");
        }

    }

}

//JXL新建Excel并写入数据
package basc;

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;

public class Write {

    public static void main(String[] args) throws IOException, WriteException {
        //1.构造workbook对象
        WritableWorkbook wwb = Workbook.createWorkbook(new File("employee.xls"));
        //2.添加sheet
        WritableSheet ws = wwb.createSheet("employee", 0);
        //3.构造单元格
        Label id = new Label(0,0,"id");
        Label name = new Label(1,0,"name");
        Label sal = new Label(2,0,"sal");

        Label vid = new Label(0,1,"001");
        Label vname = new Label(1,1,"王凯");
        Label vsal = new Label(2,1,"2000");
        //4.添加
        ws.addCell(id);
        ws.addCell(name);
        ws.addCell(sal);
        ws.addCell(vid);
        ws.addCell(vname);
        ws.addCell(vsal);
        //5.写出到文件
        wwb.write();
        wwb.close();
    }

}

//POI读取
package basc;

import java.io.File;

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 ReadExcelPoI {

    public static void main(String[] args)throws Exception {
        //1.构造方法
        Workbook wb = new XSSFWorkbook(new File("test.xlsx"));
        //2.获得sheet
        Sheet sheet = wb.getSheetAt(1);
        //3.获得范围
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        for(int i = firstRow;i<=lastRow;i++){
            //4.获得某一行
            Row row = sheet.getRow(i);
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            for(int k=firstCell;k<lastCell;k++){
                //6.得到单元格
                Cell cell = row.getCell(k);
                if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
                    System.out.print(cell.getNumericCellValue()+"\t");
                }else{
                    System.out.print(cell.getStringCellValue()+"\t");
                }
            }
            System.out.println("");
        }
        wb.close();
    }

}

//POI写入
package basc;

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

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 WriteExcelPOI {

    public static void main(String[] args) throws Exception{
        //1.构造workbook对象
        Workbook wb = new XSSFWorkbook();
        //2.添加sheet
        Sheet sheet = wb.createSheet("employee");
        //3.创建行
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("name");
        row.createCell(2).setCellValue("isal");

        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("001");
        row1.createCell(1).setCellValue("张三");
        row1.createCell(2).setCellValue("123.321");

        OutputStream os = new FileOutputStream(new File("emp.xlsx"));
        wb.write(os);
        wb.close();
    }

}