poi的入门学习

poi的入门学习

1.基本使用

package cn.itcast.poi.test;

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.IOException;

/**
 * @Authror royLuo
 * @Date 2020/5/5 22:16
 **/
public class PoiTest06 {

    public static void main(String[] args) {
        Workbook wb = null;
        FileOutputStream os =null;
        try {
            //1.创建工作薄   XSSFWorkbook操作2007的Excel文件
            wb = new XSSFWorkbook();
            //2.创建一张sheet
            Sheet sheet = wb.createSheet("abc");
            //3.工作薄保存的路径
            os = new FileOutputStream("E:\\excel\\poi\\abc.xlsx");
            //4.工作对象把数据输出
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if(os!=null) os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                try {
                   if (wb!=null)wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2.定位需要填写数据的单元格（导出Excel文件）

package cn.itcast.poi.test;
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.FileNotFoundException;
import java.io.FileOutputStream;

/**
 * @Authror royLuo
 * @Date 2020/5/5 22:33
 **/
public class PoiTest07 {

    public static void main(String[] args) throws Exception {
        //1.创建一个工作薄
        Workbook workbook = new XSSFWorkbook();
        //2.创建一张sheet
        Sheet sheet = workbook.createSheet("sheet1");
        //3.行是从索引为0开始算起的，获取索引为2的行。即第3行
        Row row = sheet.createRow(2);
        //4.获取单元,列也是从0开始起算的,获取索引为2的单元格位置，即第3列
        Cell cell = row.createCell(2);
        cell.setCellValue("我的第一个poi学习笔记");
        FileOutputStream os = new FileOutputStream("E:\\excel\\poi\\test2.xlsx");
        workbook.write(os);
        workbook.close();
        os.close();
    }

}

3.创建单元格的样式

package cn.itcast.poi.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

/**
 * @Authror royLuo
 * @Date 2020/5/5 22:47
 **/
public class PoiTest08 {

    public static void main(String[] args) throws Exception {
        //1.创建工作薄
        XSSFWorkbook wb = new XSSFWorkbook();
        //2.创建一张sheet
        Sheet sheet = wb.createSheet();
        //3.创建一个行
        Row row = sheet.createRow(2);
        //4.创建一个创建单元格
        Cell cell = row.createCell(2);
        //5.向单元格填数据
        cell.setCellValue("我的第一个poi笔记");
        //6.创建一个单元格的样式
        CellStyle cellStyle = wb.createCellStyle();
        //设置单元的边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        //设置字体样式
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 15);
        cellStyle.setFont(font);

        //设置行高
        row.setHeightInPoints(15.0f);
        //设置列宽
        sheet.setColumnWidth(2,31*256);

        //设置水平和垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //给上面创建的单元格设置样式
        cell.setCellStyle(cellStyle);

        FileOutputStream os = new FileOutputStream("E:\\excel\\poi\\test3.xlsx");
        wb.write(os);
        wb.close();
        os.close();
    }
}

4.读取(导入)Excel文件数据

package cn.itcast.poi.test;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @Authror royLuo
 * @Date 2020/5/5 23:24
 **/
public class PoiTest09 {

    public static void main(String[] args) throws Exception {
        Workbook workbook = new XSSFWorkbook("E:\\excel\\poi\\demo.xlsx");
        //获取第一张sheet
        Sheet sheet = workbook.getSheetAt(0);
        //遍历到最大的行  sheet.getLastRowNum()获取最后的行
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            //获取每一行
            Row row = sheet.getRow(i);
            //遍历到每行最大的列
            StringBuilder builder = new StringBuilder();
            //row.getLastCellNum()获取最后的单元格
            for (int j = 2; j < row.getLastCellNum(); j++) {
                //获取一行的每个单元格
                Cell cell = row.getCell(j);
                //获取单元格对应的数据
                Object value = getValue(cell);
                builder.append(value).append("-");
            }
            System.out.println(builder.toString());
        }

    }

    public static Object getValue(Cell cell) {
        //获取单元格数据类型
        CellType type = cell.getCellType();
        Object object = null;
        //根据单元格不同数据类型返回对应数据类型
        switch (type) {
            case STRING:
                object = cell.getStringCellValue();
                break;
            case BOOLEAN:
                object = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    object = cell.getDateCellValue();
                } else {
                    object = cell.getNumericCellValue();
                }
                break;
            case FORMULA:
                object = cell.getCellFormula();
                break;
            default:
                break;
        }
        return object;
    }

}