- 一、添加依赖
- 二、工具类
- 2.1、日期工具类
- 2.2、Excel工具类
- 三、实体类
- 四、导出测试
- 五、导入测试
二、工具类 2.1、日期工具类org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml 4.1.2 org.projectlombok lombok 1.18.22
public class DateUtil {
public static String getDate(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String format = sdf.format(date);
return format;
}
public static String getDateTime(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String format = sdf.format(date);
return format;
}
}
2.2、Excel工具类
public class ExportExcelUtil {
public static void createHeadTittle(XSSFWorkbook wb, XSSFSheet sheet, String headString, int col) {
//创建Excel工作表的行
XSSFRow row = sheet.createRow(0);
//创建Excel工作表指定行的单元格
XSSFCell cell = row.createCell(0);
//设置高度
row.setHeight((short) 800);
//定义单元格为字符串类型
cell.setCellType(CellType.STRING);
cell.setCellValue(headString);
//指定标题合并区域
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col));
//定义单元格格式，添加单元格表样式，并添加到工作簿
XSSFCellStyle cellStyle = wb.createCellStyle();
//指定单元格居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//指定单元格垂直居中个对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//指定单元格自动换行
cellStyle.setWrapText(true);
//单元格合并后设置标题边框不生效
//cellStyle.setBorderTop(BorderStyle.MEDIUM);
//cellStyle.setBorderBottom(BorderStyle.MEDIUM);
//cellStyle.setBorderLeft(BorderStyle.MEDIUM);
//cellStyle.setBorderRight(BorderStyle.MEDIUM);
//设置单元格字体
XSSFFont font = wb.createFont();
font.setBold(true);
font.setFontName("微软雅黑");
//字体大小
font.setFontHeightInPoints((short) 18);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
public static void createThead(XSSFWorkbook wb, XSSFSheet sheet, String[] thead, int[] sheetWidth) {
XSSFRow row = sheet.createRow(1);
row.setHeight((short) 600);
//定义单元格格式，添加单元格表样式，并添加到工作簿
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
//设置边框类型
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//设置右边框颜色
//cellStyle.setRightBorderColor(IndexedColors.RED.index);
//设置单元格字体
XSSFFont font = wb.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 15);
cellStyle.setFont(font);
//循环设置表头内容
for (int i = 0; i < thead.length; i++) {
XSSFCell cell1 = row.createCell(i);
cell1.setCellType(CellType.STRING);
cell1.setCellValue(thead[i]);
cell1.setCellStyle(cellStyle);
}
//循环设置每一列宽度
for (int i = 0; i < sheetWidth.length; i++) {
sheet.setColumnWidth(i, sheetWidth[i]);
}
}
public static void createTable(XSSFWorkbook wb, XSSFSheet sheet, List> result) {
//定义单元格格式，添加单元格表样式，并添加到工作薄
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
//单元格字体
XSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
//居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置边框类型 不添加上边框 因为上一行数据已经添加过了
//cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//循环插入数据
for (int i = 0; i < result.size(); i++) {
XSSFRow row = sheet.createRow(i + 2);
//设置高度
row.setHeight((short) 400);
XSSFCell cell = null;
int j = 0;
for (String key : (result.get(i).keySet())) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
String value = result.get(i).get(key);
cell.setCellValue(value);
j++;
}
}
}
}
三、实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
//编号
private int id;
//姓名
private String userName;
//年龄
private int age;
//性别
private String sex;
//地址
private String address;
}
四、导出测试
public class ExcelExportTest {
//文件存放路径
private final static String PATH = "D://opt//home//";
//sheet名
private final static String SHEET_NAME = "用户数据明细";
//标题
private final static String TITLE_NAME = "用户数据明细导出数据";
//head
private final static String[] THEAD = {"编号", "姓名", "年龄", "性别", "地址"};
//定义每一列的宽度
private final static int[] SHEET_WIDTH = {3000, 3000, 3000, 3000, 3000};
//列数总共5列 0-4
private final static int COL_COUNT = 4;
public static void main(String[] args) {
//1.创建Excel文档对象
XSSFWorkbook wb = new XSSFWorkbook();
//2.创建工作表
XSSFSheet sheet = wb.createSheet(SHEET_NAME);
//3.创建标题并合并单元格
ExportExcelUtil.createHeadTittle(wb, sheet, TITLE_NAME, COL_COUNT);
//4.创建head并设置单元格格式
ExportExcelUtil.createThead(wb, sheet, THEAD, SHEET_WIDTH);
//5.模拟数据并将数据转换格式
List list = new ArrayList<>();
list.add(new User(1, "Agnes", 19, "女", "北京"));
list.add(new User(2, "Darlene", 21, "男", "上海"));
list.add(new User(3, "Heloise", 23, "女", "杭州"));
list.add(new User(4, "Sally", 18, "女", "深圳"));
list.add(new User(5, "Sherry", 22, "男", "杭州"));
list.add(new User(6, "Joyce", 23, "女", "苏州"));
list.add(new User(7, "Meredith", 34, "男", "郑州"));
list.add(new User(8, "Nina", 21, "女", "西安"));
list.add(new User(9, "Bonnie", 19, "男", "成都"));
list.add(new User(10, "Gill", 22, "女", "广州"));
//转换格式
List> result = new ArrayList<>();
for (User user : list) {
linkedHashMap map = new linkedHashMap<>();
map.put("id", String.valueOf(user.getId()));
map.put("name", user.getUserName());
map.put("age", String.valueOf(user.getAge()));
map.put("sex", user.getSex());
map.put("address", user.getAddress());
result.add(map);
}
//6.将数据添加到Excel中
ExportExcelUtil.createTable(wb, sheet, result);
//7.生成文件名 yyyyMMdd/yyyyMMddHHmmss + _随机数 + _user_eport.xlsx
Date date = new Date();
String dateDir = DateUtil.getDate(date);
String dateTimeDir = DateUtil.getDateTime(date);
Random random = new SecureRandom();
int randomNum = random.nextInt(10000);
String url = "";
url = PATH + dateDir + "/" + dateTimeDir + "_" + randomNum + "_user_eport.xlsx";
//8将数据写入到Excel中
FileOutputStream fos;
try {
File file = new File(url);
File fileParent = file.getParentFile();
if (!fileParent.exists()) {
fileParent.mkdirs();
}
file.createNewFile();
fos = new FileOutputStream(file);
wb.write(fos);
fos.close();
System.out.println("导出excel成功");
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
文件存放路径和文件名称
Excel导出内容展示
public class ExcelimportTest {
//文件存放路径
private final static String PATH = "D:/opt/home/20211224/20211224090314_7362_user_eport.xlsx";
public static void main(String[] args) {
try {
//1.创建Workbook
Workbook workbook = new XSSFWorkbook(new FileInputStream(PATH));
//2.获取Sheet表
//2.1按索引获取
//Sheet sheet = workbook.getSheetAt(0);
//2.2按sheef名称获取
Sheet sheet = workbook.getSheet("用户数据明细");
//3.获取行数(0 - 行数-1)
int lastRowNum = sheet.getLastRowNum();
//System.out.println("lastRowNum = " + lastRowNum);
//存储所有对象的集合
List userList = new ArrayList<>();
//4.遍历行
//记得除去标题和head
for (int i = 2; i <= lastRowNum; i++) {
//4.1获取当前行
Row row = sheet.getRow(i);
//4.2判断是否为空行
if (row == null) {
System.out.println("一次");
continue;
}
//4.3遍历每一行的数据
//获取最后一列 (0 - 列数-1)
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.print(value + "t");
}
System.out.println();
//4.4将数据读取到user实体中
User user = new User();
//校验单元格的值,这里省略,根据实际业务修改
user.setId(Integer.parseInt(row.getCell(0).getStringCellValue()));
user.setUserName(row.getCell(1).getStringCellValue());
user.setAge(Integer.parseInt(row.getCell(2).getStringCellValue()));
user.setSex(row.getCell(3).getStringCellValue());
user.setAddress(row.getCell(4).getStringCellValue());
userList.add(user);
}
//5.遍历userList
for (User user : userList) {
System.out.println(user);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
