一、案例效果
先上我已实现的效果(自己可根据案例代码改进,以下效果并非代码实现效果):
本案例代码实现效果:
二、具体操作流程
pom依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
导出对象ExcelExp:
package com.zwd.cases.demo.utilstest;
import java.util.List;
/**
* @Description: 导出对象
* @Author zhengwd
* @Date 2023/7/24 1:01
*/
public class ExcelExp {
private String fileName;// sheet的名称
private String[] handers;// sheet里的标题
private List<String[]> dataset;// sheet里的数据集
private String tableName;
public ExcelExp(String fileName, String[] handers, List<String[]> dataset, String tableName) {
this.fileName = fileName;
this.handers = handers;
this.dataset = dataset;
this.tableName = tableName;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String[] getHanders() {
return handers;
}
public void setHanders(String[] handers) {
this.handers = handers;
}
public List<String[]> getDataset() {
return dataset;
}
public void setDataset(List<String[]> dataset) {
this.dataset = dataset;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
}
工具类ExcelExportUtil:
package com.zwd.cases.demo.utilstest;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @Description java导出功能(多个sheet页数据导出)
* @Author zhengwd
* @Date 2023/7/24 0:57
**/
public final class ExcelExportUtil {
/**
* @param @param file 导出文件路径
* @param @param mysheets
* @return void
* @throws
* @Title: exportManySheetExcel
* @Description: 可生成单个、多个sheet
*/
public static Workbook exportManySheetExcel(List<ExcelExp> mysheets) {
HSSFWorkbook wb = new HSSFWorkbook();// 创建工作薄
List<ExcelExp> sheets = mysheets;
// 表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
// 字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short) 12);
// fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(fontStyle);
for (ExcelExp excel : sheets) {
// 新建一个sheet
HSSFSheet sheet = wb.createSheet(excel.getFileName());// 获取该sheet名称
String[] handers = excel.getHanders();// 获取sheet的标题名
HSSFRow tableName = sheet.createRow(0);
HSSFCell cellName = tableName.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
HSSFCellStyle titleStyle = wb.createCellStyle();
// 设置单元格样式
HSSFFont titleFont = wb.createFont(); // 标题字体
titleFont.setFontHeightInPoints((short) 16); // 字号
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
cellName.setCellStyle(titleStyle);
// 设置单元格内容
cellName.setCellValue(excel.getTableName());
HSSFRow rowFirst = sheet.createRow(1);// 第一个sheet的第一行为标题
// 写标题
for (int i = 0; i < handers.length; i++) {
// 获取第一行的每个单元格
HSSFCell cell = rowFirst.createCell(i);
// 往单元格里写数据
cell.setCellValue(handers[i]);
cell.setCellStyle(style); // 加样式
sheet.setColumnWidth(i, 4000); // 设置每列的列宽
}
// 写数据集
List<String[]> dataset = excel.getDataset();
for (int i = 0; i < dataset.size(); i++) {
String[] data = dataset.get(i);// 获取该对象
// 创建数据行
HSSFRow row = sheet.createRow(i + 2);
for (int j = 0; j < data.length; j++) {
// 设置对应单元格的值
row.createCell(j).setCellValue(data[j]);
}
}
}
return wb;
}
}
测试类DemoTest2:
package com.zwd.cases.demo;
import com.zwd.cases.demo.utilstest.ExcelExp;
import com.zwd.cases.demo.utilstest.ExcelExportUtil;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @Description 测试类
* @Author zhengwd
* @Date 2023/7/25 22:02
**/
public class DemoTest2 {
public static void main(String[] args) {
/** 第一页数据 */
List<String[]> dataAllOne = new ArrayList<>();
String[] data1 = (String[]) Arrays.asList("杨1", "18", "男").toArray();
String[] data2 = (String[]) Arrays.asList("杨2", "19", "女").toArray();
dataAllOne.add(data1);
dataAllOne.add(data2);
/** 第二页数据 */
List<String[]> dataAllTwo = new ArrayList<>();
String[] data3 = (String[]) Arrays.asList("驾照", "2022年9月5日10:08:46", "是").toArray();
String[] data4 = (String[]) Arrays.asList("户口本", "2022-9-5 10:09:01", "否").toArray();
dataAllTwo.add(data3);
dataAllTwo.add(data4);
ArrayList<ExcelExp> list = new ArrayList<>();
ExcelExp excelExp1 = new ExcelExp("存放人员信息", (String[]) Arrays.asList("姓名", "年龄", "性别").toArray(), dataAllOne, "人员信息");
ExcelExp excelExp2 = new ExcelExp("存放文件信息", (String[]) Arrays.asList("文件名称", "上传时间", "是否上传到FDFS").toArray(), dataAllTwo, "文件上传情况");
list.add(excelExp1);
list.add(excelExp2);
Workbook workbook = ExcelExportUtil.exportManySheetExcel(list);
//导出数据到excel
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream("C:\\Users\\86178\\Desktop\\tempFile\\demo.xls");
workbook.write(fileOutputStream);
fileOutputStream.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if(fileOutputStream != null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
生成文件:
友情链接:其二,多sheet页Excel文件导入