POI之excel导入导出

缺乏、安全感 2022-01-05 12:49 392阅读 0赞

**下载地址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