POI

简单认识

这个可能会存在excel表格不兼容的问题，很坑。

依赖：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

 

读取文件：

public class Demo01 {
    public static void main(String[] args) throws IOException {
        // 1、 获取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook("E:\\PoiDemo\\test.xlsx");
        // 2、 获取工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 3、 获取行
        for (Row row : sheet) {
            // 4、 获取单元格
            for (Cell cell : row) {
                // 获取单元格的内容
                String value = cell.getStringCellValue();
                System.out.println(value);
            }
        }
        // 释放资源
        workbook.close();
    }
}

向文件写入

public class Demo2 {
    public static void main(String[] args) throws IOException {
        //1.创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2.创建工作表
        XSSFSheet sheet = workbook.createSheet("工作表一");
        //3.创建行
        XSSFRow row = sheet.createRow(0);　　// 第一行
        //创建单元格
        row.createCell(0).setCellValue("传智播客");
        row.createCell(1).setCellValue("黑马程序员");
        row.createCell(2).setCellValue("博学谷");


        XSSFRow row1 = sheet.createRow(1);　　// 第二行
        //创建单元格
        row1.createCell(0).setCellValue("传智播客");
        row1.createCell(1).setCellValue("黑马程序员");
        row1.createCell(2).setCellValue("博学谷");

        //输出流
        FileOutputStream out=new FileOutputStream("E:\\poitest\\heima.xlsx");
        workbook.write(out);
        out.flush();
        //释放资源
        out.close();
        workbook.close();
        System.out.println("写入成功!");

    }
}

 

小练习：

依赖：

　　　　 <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
            <scope>compile</scope>
        </dependency>
        <!--druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.9</version>
        </dependency>
        <!--jdbcTemplate-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.1.2.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.2.RELEASE</version>
            <scope>compile</scope>
        </dependency>

druid.properties 文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///poidb
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000

工具类-连接druid 连接池：

public class JDBCUtils {

    private static DataSource ds ;

    static {

        try {
            //1.加载配置文件
            Properties pro = new Properties();
            //使用ClassLoader加载配置文件，获取字节输入流
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);

            //2.初始化连接池对象
            ds = DruidDataSourceFactory.createDataSource(pro);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接池对象
     */
    public static DataSource getDataSource(){
        return ds;
    }


    /**
     * 获取连接Connection对象
     */
    public static Connection getConnection() throws SQLException {
        return  ds.getConnection();
    }
}

实体类：

public class Product {
    private Integer pid;
    private String pname;
    private double price;
    private int pstock;

    @Override
    public String toString() {
        return "Product{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", price=" + price +
                ", pstock=" + pstock +
                '}';
    }

    public Product(Integer pid, String pname, double price, int pstock) {
        this.pid = pid;
        this.pname = pname;
        this.price = price;
        this.pstock = pstock;
    }

    public Product() {
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getPstock() {
        return pstock;
    }

    public void setPstock(int pstock) {
        this.pstock = pstock;
    }
}

dao 接口：

public interface ProductDao {

    void save(Product product);


    List<Product> findAll();

}

dao 实现类：

public class ProductDaoImpl implements ProductDao {
        JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public void save(Product product) {
        String sql="insert into product values(?,?,?,?)";
        jdbcTemplate.update(sql,product.getPid(),product.getPname(),product.getPrice(),product.getPstock());

    }

    @Override
    public List<Product> findAll() {
        String sql="select * from product";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Product>(Product.class));
    }


}

service 接口：

public interface ProductService {


    void save(List<Product> productList);


    List<Product> findAll();

}

service 实现类：

public class ProductServiceImpl implements ProductService {
    private  ProductDao productDao=new ProductDaoImpl();

    @Override
    public void save(List<Product> productList) {
        for (Product product : productList) {
            productDao.save(product);
        }

    }

    @Override
    public List<Product> findAll() {

        return productDao.findAll();
    }


}

然后开始：1、数据导入数据库　　2、导出数据库数据

public class Show {
    public static void main(String[] args) throws IOException {

        //通过键盘录入Scanner，正常开发肯定还是用户输入，然后获取到
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入你要选择的功能: 1.导入 2.导出");
        int num = sc.nextInt();
        ProductService productService=  new ProductServiceImpl();
        if(num==1){
            //1.导入
            //1.1读取excel表中的数据
            System.out.println("请输入您要读取的文件位置(不包含空格)");
            String path = sc.next();
            List<Product> productList = read(path);
            System.out.println(productList);
            //1.2将数据写入到数据库中

            productService.save(productList);
            System.out.println("数据已存入数据库中!");
        }else if(num==2){
            //2.导出
            //2.1 读取数据库中的数据
            List<Product> productList= productService.findAll();
            System.out.println(productList);
            //2.2将数据写入到excel表格中
            System.out.println("请输入要写入的文件位置:");
            String path = sc.next();
            write(productList,path);
            System.out.println("写入成功!");
        }else {
            System.out.println("输入有误,请重新启动");
        }
    }
　　// 创建一个工作簿来接收数据库里拿出来的数据
   public static void write( List<Product> productList,String path) throws IOException {
        //1.创建一个工作薄
       XSSFWorkbook xssfWorkbook=new XSSFWorkbook();
       //2.创建工作表
       XSSFSheet sheet = xssfWorkbook.createSheet("商品");
       //单元格样式
       XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
       cellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
       cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
       //字体样式
       XSSFFont font = xssfWorkbook.createFont();
       font.setFontName("黑体");
       font.setColor(IndexedColors.BLUE.getIndex());
       cellStyle.setFont(font);


       //3.创建行
       XSSFRow row = sheet.createRow(0);
      /* row.createCell(0).setCellValue("商品编号");
       row.createCell(1).setCellValue("商品名称");
       row.createCell(2).setCellValue("商品价格(单位:元/斤)");
       row.createCell(3).setCellValue("商品库存(单位:吨)");*/

       XSSFCell cell = row.createCell(0);
       cell.setCellValue("商品编号");
       cell.setCellStyle(cellStyle);

       XSSFCell cell1 = row.createCell(1);
       cell1.setCellValue("商品名称");
       cell1.setCellStyle(cellStyle);

       XSSFCell cell2 = row.createCell(2);
       cell2.setCellValue("商品价格(单位:元/斤)");
       cell2.setCellStyle(cellStyle);

       XSSFCell cell3 = row.createCell(3);
       cell3.setCellValue("商品库存(单位:吨)");
       cell3.setCellStyle(cellStyle);


       for (int i = 0; i < productList.size(); i++) {
           XSSFRow row1 = sheet.createRow(i + 1);
           row1.createCell(0).setCellValue(productList.get(i).getPid());
           row1.createCell(1).setCellValue(productList.get(i).getPname());
           row1.createCell(2).setCellValue(productList.get(i).getPrice());
           row1.createCell(3).setCellValue(productList.get(i).getPstock());
       }

       FileOutputStream fileOutputStream=new FileOutputStream(path);
       xssfWorkbook.write(fileOutputStream);
       fileOutputStream.flush();
       fileOutputStream.close();
       xssfWorkbook.close();
   }




// 先拿到excel 表格里的数据
    public static  List<Product> read(String path) throws IOException {

    List<Product> productList=new ArrayList<>();
    //1.获取工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
        //2.获取工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i <=lastRowNum ; i++) {
            XSSFRow row = sheet.getRow(i);
            if(row!=null){
                List<String> list=new ArrayList<>();
                for (Cell cell : row) {
                    if(cell!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String value = cell.getStringCellValue();//读取数据
                        if(value!=null&&!"".equals(value)) {
                            list.add(value);
                        }
                    }
                }
                if(list.size()>0) {
                    Product product = new Product(Integer.parseInt(list.get(0)), list.get(1), Double.parseDouble(list.get(2)), Integer.parseInt(list.get(3)));
                    productList.add(product);
                }
            }
        }
        return productList;
    }
}

 

EasyExcel操作

导入依赖

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>

 

网址：

https://www.yuque.com/easyexcel/doc/read