Java POI Excel 导入导出

这个东西很容易懂，不是特别难，难就难在一些复杂的计算和Excel格式的调整上。

近期写了一个小列子，放上来便于以后使用。

POI.jar下载地址：http://mirror.bit.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.zip

Entity 实体类

package com.test2;

import java.util.Date;

public class User {

    private int id; 
    private String username;
    private String password;
    private String sex;
    private int age;
    private Date birth;
    
    public int getId() {
        return id;
    }
    public String getUsername() {
        return username;
    }
    public String getPassword() {
        return password;
    }
    public String getSex() {
        return sex;
    }
    public int getAge() {
        return age;
    }
    public Date getBirth() {
        return birth;
    }
    public void setId(int id) {
        this.id = id;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public void setBirth(Date birth) {
        this.birth = birth;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password=" + password + ", sex=" + sex + ", age=" + age
                + ", birth=" + birth + "]";
    }
    
    public User() {};
    public User(int id, String username, String password, String sex, int age, Date birth) {
        super();
        this.id = id;
        this.username = username;
        this.password = password;
        this.sex = sex;
        this.age = age;
        this.birth = birth;
    }
   
}

 

 

Excel 导出：

package com.test2;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportExcel {
    
    public static void main(String[] args) {
        try {
            // 创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 5);
            HSSFCellStyle headStyle = getStyle(workbook,(short) 15);
            HSSFCellStyle colStyle = getStyle(workbook,(short) 12);
            
            //新建一个excel页签
            HSSFSheet createSheet = workbook.createSheet("用户信息列表");
            // 将合并表格的对象添加页签中
            createSheet.addMergedRegion(cellRangeAddress);
            // 设置单元格的默认宽度
            createSheet.setDefaultColumnWidth(25);
            
            // 创建一行
            HSSFRow row0 = createSheet.createRow(0);
            HSSFCell cell0 = row0.createCell(0);
            // 添加标题样式
            cell0.setCellStyle(headStyle);
            // 添加标题
            cell0.setCellValue("用户信息列表");
            
            //设置列的标题
            String [] titles = {"id","用户名","密码","年龄","性别","生日"};
            HSSFRow row1 = createSheet.createRow(1);
            //  循环往excel中添加列标题
            for (int i = 0; i < titles.length; i++) {
                HSSFCell cell1 = row1.createCell(i);
                cell1.setCellStyle(colStyle);
                cell1.setCellValue(titles[i]);
            }
            
            List<User> userList = new ArrayList<User>();
            
            userList.add(new User(1,"zhangsan1","123","男",21,new Date()));
            userList.add(new User(2,"zhangsan2","456","男",21,new Date()));
            userList.add(new User(3,"zhangsan3","789","女",21,new Date()));
            userList.add(new User(4,"zhangsan4","000","男",21,new Date()));
            
            for (int i = 0; i < userList.size(); i++) {
                //创建第三行
                HSSFRow row2 = createSheet.createRow(i + 2);
                
                HSSFCell cell_0 = row2.createCell(0);
                cell_0.setCellValue(userList.get(i).getId());
                
                
                HSSFCell cell_1 = row2.createCell(1);
                cell_1.setCellValue(userList.get(i).getUsername());
                
                
                HSSFCell cell_2 = row2.createCell(2);
                cell_2.setCellValue(userList.get(i).getPassword());
                
                HSSFCell cell_3 = row2.createCell(3);
                cell_3.setCellValue(userList.get(i).getAge());
                
                HSSFCell cell_4 = row2.createCell(4);
                cell_4.setCellValue(userList.get(i).getSex());
                
                
                HSSFCell cell_5 = row2.createCell(5);
                cell_5.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(userList.get(i).getBirth()));
                
            }
            
            OutputStream os = new FileOutputStream("E:/test.xls");
            workbook.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
    }
    
    public static HSSFCellStyle getStyle(HSSFWorkbook workbook,short fontSize) {
        // 创建样式对象
        HSSFCellStyle createCellStyle = workbook.createCellStyle();
        //水平居中
        createCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        createCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        createCellStyle.setBorderBottom(BorderStyle.THIN);
        createCellStyle.setBorderLeft(BorderStyle.THIN);
        createCellStyle.setBorderRight(BorderStyle.THIN);
        createCellStyle.setBorderTop(BorderStyle.THIN);
        
        //  创建一个字体对象
        HSSFFont createFont = workbook.createFont();
        // 设置字体的大小
        createFont.setFontHeightInPoints(fontSize);
        // 设置字体类型
        createFont.setFontName("微软雅黑");
        // 设置字体的颜色
        createFont.setColor(HSSFColor.RED.index);
        
        //将字体放置到样式中
        createCellStyle.setFont(createFont);
        return createCellStyle;
    }
    
}

以下是导出Excel的结果图：

 

这里补充下合并单元格的知识点：

/*合并单元格需要用到CellRangeAddress对象 
CellRangeAddress对象需要传入四个参数 列如：CellRangeAddress(param1,param2,param3,param4)；
参数分别代表如下：
param1：开始行 
param2：结束行  
param3：开始列 
param4：结束列  
例如：
合并第一行和第二行，此合并只是合并了第一列,第二列、第三列...等后面的列的一二行并没有合并
给合并的单元格赋值是需要注意行号变化
注意：记住行号和列号是从0起始
*/
sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));  

下面粘贴一段合并单元格行和列的综合代码：

public static void main(String[] args) {
    try {
        // 创建标题栏
        String[] titles = new String[] {"公司名称","项目名称","地址名称"};
        String[] strDate = new String[] {"2018-04-29","2018-04-30","2018-05-01","2018-05-02","2018-05-03"};
        
        // 创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //拿到标题样式和单元格样式
        HSSFCellStyle headStyle = getStyle(workbook,(short) 15);
        HSSFCellStyle colStyle = getStyle(workbook,(short) 12);
        
        //新建一个excel页签
        HSSFSheet createSheet = workbook.createSheet("地址信息报表");
        // 将合并表格的对象添加页签中
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, (6+strDate.length));
        createSheet.addMergedRegion(cellRangeAddress);
        //循环合并1至3列的的一二行
        for (int i = 0; i < 3; i++) {
            createSheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
        }
        createSheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 3+strDate.length));
        // 设置单元格的默认宽度
        createSheet.setDefaultColumnWidth(25);
        
        // 创建一行
        HSSFRow row0 = createSheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);
        // 添加标题样式
        cell0.setCellStyle(headStyle);
        // 添加标题
        cell0.setCellValue("地址信息报表");
        
        //设置列的标题
        HSSFRow row1 = createSheet.createRow(1);
        //  循环往excel中添加列标题
        for (int i = 0; i < titles.length; i++) {
            HSSFCell cell1 = row1.createCell(i);
            cell1.setCellStyle(colStyle);
            cell1.setCellValue(titles[i]);
        }
        HSSFCell dateCell = row1.createCell(titles.length);
        dateCell.setCellStyle(colStyle);
        dateCell.setCellValue("2018-04-29 / 2018-05-03");
        
        HSSFRow row2 = createSheet.createRow(2);
        //  循环往excel中添加列标题
        for (int i = 0; i < strDate.length; i++) {
            HSSFCell cell1 = row2.createCell(i + titles.length);
            cell1.setCellStyle(colStyle);
            cell1.setCellValue(strDate[i]);
        }
        
        OutputStream os = new FileOutputStream("E:/test.xls");
        workbook.write(os);
        os.close();
    } catch (Exception e){
        e.printStackTrace();
    }
    
    
}

结果如下：

 

 

Excel导入：

package com.test2;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 
 * @author zhangxiang
 *
 */
public class ImportExcel {

    public static void main(String[] args) {
        
        try {
            //创建输入流对象
            InputStream inputStream = new FileInputStream("E:/test.xls");
            // 创建工作波对象
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            // 创建user集合对象用于存储Excel导入的信息
            List<User> userList = new ArrayList<User>();
            
            
            //  是否能拿到excel页签
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                //  不存在就结束循环
                if(null == sheetAt) {
                    continue;
                }
                
                //  是否存在row
                for (int j = 0; j < sheetAt.getPhysicalNumberOfRows(); j++) {
                    
                    // 拿到第 j+2 行  前两行是标题 
                    HSSFRow row = sheetAt.getRow(2+j); 
                    // 不存在row就结束循环
                    if(null == row) {
                        continue;
                    }
                    
                    // 存储一行的每个单元格拿到的值
                    User user = new User();
                    
                    // 拿到第一个单元格的值   单元格从0开始
                    HSSFCell cell0 = row.getCell(0);
                    Float f = Float.parseFloat(cell0.toString());
                    user.setId(f.intValue());
                    
                    // 第二个单元格的值
                    HSSFCell cell1 = row.getCell(1);
                    user.setUsername(cell1.toString());
                    
                    //第三个
                    HSSFCell cell2 = row.getCell(2);
                    user.setPassword(cell2.toString());
                    
                    //第四个
                    HSSFCell cell3 = row.getCell(3);
                    Float a = Float.parseFloat(cell3.toString());
                    user.setAge(a.intValue());
                    
                    // 第五个
                    HSSFCell cell4 = row.getCell(4);
                    user.setSex(cell4.toString());
                    
                    //第六个
                    HSSFCell cell5 = row.getCell(5);
                    user.setBirth(new SimpleDateFormat("yyyy-MM-dd").parse(cell5.toString()));
                    
                    
                    userList.add(user);
                }
            }
            //  输出信息  查看是否正确
            System.out.println(userList.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }    
}

 以下是导入Excel 的部分打印结果图：

 

以上代码直接就可以运行！！