这个东西很容易懂,不是特别难,难就难在一些复杂的计算和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 的部分打印结果图:
以上代码直接就可以运行!!