POI之excel导入导出

下载地址http://poi.apache.org/

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

 

package com.zns.bean;

public class Person {
    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Person(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }

    public Person() {
        super();
        // TODO Auto-generated constructor stub
    }

    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + "]";
    }

}

 

package com.zns.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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 com.zns.bean.Person;

public class Test2 {
    public static void main(String[] args) throws Exception {
        // func1();
        // func2();
        // func3();
    }

    //导出到excel
    private static void func1() {
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateStr = formatter.format(currentTime);

        // 导出xls用HSSFWorkbook,导出xlsx用XSSFWorkbook
        // String outFileName = dateStr + ".xls";
        // Workbook wb = new HSSFWorkbook();

        String outFileName = dateStr + ".xlsx";
        Workbook wb = new XSSFWorkbook();

        Sheet sheet1 = (Sheet) wb.createSheet("sheet001");
        Row row = (Row) sheet1.createRow(0);
        String[] headers = { "编号", "姓名" };
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
        }
        List<Person> list = new ArrayList<Person>();
        list.add(new Person(1, "张三"));
        list.add(new Person(2, "李四"));

        for (int i = 0; i < list.size(); i++) {
            row = sheet1.createRow((int) i + 1);
            Person obj = (Person) list.get(i);
            row.createCell(0).setCellValue(obj.getId());
            row.createCell(1).setCellValue(obj.getName());
        }

        try {
            FileOutputStream fout = new FileOutputStream("D:/" + outFileName);
            wb.write(fout);
            fout.close();
            System.out.println("导出成功...");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //从excel导入
    private static void func2() throws FileNotFoundException, IOException {
        // InputStream is = new FileInputStream("D://test.xls");
        // Workbook wb = new HSSFWorkbook(is);
        InputStream is = new FileInputStream("D://test.xlsx");
        Workbook wb = new XSSFWorkbook(is);
        List<Person> list = new ArrayList<Person>();
        Sheet sheet1 = (Sheet) wb.getSheetAt(0);
        for (int rowNum = 1; rowNum <= sheet1.getLastRowNum(); rowNum++) {
            Row row = (Row) sheet1.getRow(rowNum);
            if (row != null) {
                Person obj = new Person();
                Cell cell0 = row.getCell(0);
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                Cell cell1 = row.getCell(1);
                obj.setId(Integer.parseInt(cell0.getStringCellValue()));
                obj.setName(cell1.getStringCellValue());
                list.add(obj);
            }
        }
        System.out.println(list);
    }

    //从excel模板获取内容 再导出
    private static void func3() throws IOException, FileNotFoundException {
        // excel模板路径
        File fi = new File("D:\\test.xls");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));
        // 读取excel模板
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // 读取了模板内sheet0
        HSSFSheet sheet = wb.getSheetAt(0);
        // 在相应的单元格进行赋值
        HSSFCell cell = sheet.getRow(1).getCell(3);
        cell.setCellValue("aa");
        HSSFCell cell2 = sheet.getRow(3).getCell(3);
        cell2.setCellValue("bb");
        HSSFCell cell3 = sheet.getRow(0).getCell(0);
        cell3.setCellValue("cc");
        // 修改模板内容导出新文件
        FileOutputStream out = new FileOutputStream("D:/export.xls");
        wb.write(out);
        out.close();
    }

}

 

转载于:https://www.cnblogs.com/zengnansheng/p/10389255.html