首先需要导入一定的jar包(poi.jar)
user类
package com.sxt.excel;
import java.io.InputStream;
import java.util.Date;
public class User {
private Integer id;
private String name;
private String address;
private Integer sex;
private Date birth;
public User() {
}
public User(Integer id, String name, String address, Integer sex, Date birth) {
this.id = id;
this.name = name;
this.address = address;
this.sex = sex;
this.birth = birth;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
}
测试poi类
package com.sxt.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class TestExport {
public static void main(String[] args) {
List<User>users=new ArrayList<>();
for(int i=0;i<=20;i++){
users.add(new User(i,"小明"+i,"武汉"+i,i%2,new Date()));
}
String path="D:/image/users.xls";
export(users,path);
}
/**
* 导出
* @param users
*/
public static void export(List<User>users,String path){
//1、创建一个工作簿
HSSFWorkbook workbook=new HSSFWorkbook();
//2、在工作簿里面出创建sheet
//workbook.createSheet()//只是创建名字为默认的sheetX
HSSFSheet sheet=workbook.createSheet("用户数据");
//3、sheet的相关数据
// sheet.setColumnHidden();设置某一列是否隐藏
// sheet.setColumnWidth(); //设置某一列的宽度
// sheet.setDefaultColumnWidth(20);//设置默认宽度
//sheet.setDefaultRowHeight((short) (20*20));//设置默认行高
//合并
CellRangeAddress region1=new CellRangeAddress(0,0,0,4);
sheet.addMergedRegion(region1);
CellRangeAddress region2=new CellRangeAddress(1,1,0,4);
sheet.addMergedRegion(region2);
//创建行
int row=0;
HSSFRow row1=sheet.createRow(row);
//在一行里面创建一个单元格
//创建标题样式
HSSFCellStyle titleStyle=createTitleStyle(workbook);
HSSFCell row1_cell1=row1.createCell(0);
//向row1_cell1里面添加数据
row1_cell1.setCellValue("用户数据");
row1_cell1.setCellStyle(titleStyle);
//第二行
//创建小标题样式
HSSFCellStyle subTitleStyle=createSubTitleStyle(workbook);
row++;
HSSFRow row2=sheet.createRow(row);
HSSFCell row2_cell1=row2.createCell(0);
row2_cell1.setCellValue("总条数"+users.size()+" 导出时间"+new Date().toLocaleString());
row2_cell1.setCellStyle(subTitleStyle);
//第三行
String[] title={"用户ID","用户名","用户地址","性别","入职时间"};
row++;
HSSFRow row3=sheet.createRow(row);
//创建表头样式
HSSFCellStyle tableHeaderStyle=createTableTitleStyle(workbook);
for(int i=0;i<title.length;i++){
HSSFCell cell=row3.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(tableHeaderStyle);
}
//创建基础样式
HSSFCellStyle baseStyle=createBaseStyle(workbook);
//第四行到最后
for(int i=0;i<users.size();i++){
User user=users.get(i);
row++;
HSSFRow rowx=sheet.createRow(row);
HSSFCell cell1= rowx.createCell(0);
cell1.setCellValue(user.getId());
cell1.setCellStyle(baseStyle);
HSSFCell cell2= rowx.createCell(1);
cell2.setCellValue(user.getName());
cell2.setCellStyle(baseStyle);
HSSFCell cell3= rowx.createCell(2);
cell3.setCellValue(user.getAddress());
cell3.setCellStyle(baseStyle);
HSSFCell cell4= rowx.createCell(3);
cell4.setCellValue(user.getSex()==1?"男":"女");
cell4.setCellStyle(baseStyle);
HSSFCell cell5= rowx.createCell(4);
cell5.setCellValue(user.getBirth().toLocaleString());
cell5.setCellStyle(baseStyle);
}
//导出保存到D盘
try {
workbook.write(new File(path));
System.out.println("导出成功");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 创建基础样式
* 水平和垂直居中
*/
public static HSSFCellStyle createBaseStyle(HSSFWorkbook workbook){
HSSFCellStyle style=workbook.createCellStyle();
//设置水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 创建数据表格的样式
*/
public static HSSFCellStyle createTableTitleStyle(HSSFWorkbook workbook){
HSSFCellStyle style=createBaseStyle(workbook);
//设置字体
HSSFFont font=workbook.createFont();
font.setBold(true);//设置粗体
font.setItalic(true);//设置斜体
font.setFontHeightInPoints((short) 12);//设置字体大小
font.setColor(HSSFColor.HSSFColorPredefined.DARK_TEAL.getIndex());//设置颜色
font.setFontName("Consolas");//设置字体
style.setFont(font);
return style;
}
/**
* 创建小标题样式
*/
public static HSSFCellStyle createSubTitleStyle(HSSFWorkbook workbook){
HSSFCellStyle style=createBaseStyle(workbook);
//设置字体
HSSFFont font=workbook.createFont();
font.setBold(true);//设置粗体
font.setFontHeightInPoints((short) 18);//设置字体大小
font.setColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());//设置颜色
font.setFontName("楷体");//设置字体
style.setFont(font);
return style;
}
/**
* 创建标题样式
*/
public static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook){
HSSFCellStyle style=createBaseStyle(workbook);
//设置字体
HSSFFont font=workbook.createFont();
font.setBold(true);//设置粗体
font.setFontHeightInPoints((short) 25);//设置字体大小
font.setColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());//设置颜色
font.setFontName("方正姚体");//设置字体
style.setFont(font);
return style;
}
}
运行结果如下: