1、配置pom文件,添加apache的poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
2、编写导出接口ExcelController
@Controller
@RequestMapping("/excel")
public class ExcelController {
/**
* Excel表格导出接口
* http://localhost:9997/excel/exportExcel
* @param response response对象
* @throws IOException 抛IO异常
*/
@RequestMapping("/exportExcel")
public void excelDownload(HttpServletResponse response) throws IOException {
// 表头数据
// 一级表头
String[] header0 = {"ID", "姓名", "性别", "分数", "分数", "分数"};
// 二级表头
String[] header1 = {"ID", "姓名", "性别", "语文", "数学", "英语"};
// 数据内容
String[] student0 = {"1", "小红", "女", "99", "80", "98"};
String[] student1 = {"2", "小强", "男", "96", "92", "91"};
String[] student2 = {"3", "小明", "男", "82", "88", "95"};
// 声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 声明一个单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
// 生成一个表格,设置表格名称为"学生成绩表"
HSSFSheet sheet = workbook.createSheet("学生成绩表");
// 设置表格列宽度为10个字节
sheet.setDefaultColumnWidth(10);
// 创建第一行表头
HSSFRow headrow0 = sheet.createRow(0);
for (int i = 0; i < header0.length; i++) {
// 创建一个单元格
HSSFCell cell = headrow0.createCell(i);
// 创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(header0[i]);
// 将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
// 设置样式
cell.setCellStyle(cellStyle);
}
// 创建第二行表头
HSSFRow headrow1 = sheet.createRow(1);
for (int i = 0; i < header1.length; i++) {
// 创建一个单元格
HSSFCell cell = headrow1.createCell(i);
// 创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(header1[i]);
// 将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
// 设置样式
cell.setCellStyle(cellStyle);
}
// 把数据内容加入表格
// 遍历第一个学生
HSSFRow row0 = sheet.createRow(2);
for (int i = 0; i < student0.length; i++) {
HSSFCell cell = row0.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(student0[i]);
cell.setCellValue(text);
cell.setCellStyle(cellStyle);
}
// 遍历第二个学生
HSSFRow row1 = sheet.createRow(3);
for (int i = 0; i < student1.length; i++) {
HSSFCell cell = row1.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(student1[i]);
cell.setCellValue(text);
cell.setCellStyle(cellStyle);
}
// 遍历第三个学生
HSSFRow row2 = sheet.createRow(4);
for (int i = 0; i < student2.length; i++) {
HSSFCell cell = row2.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(student2[i]);
cell.setCellValue(text);
cell.setCellStyle(cellStyle);
}
// 需要合并的集合
List<String> regions = new ArrayList<String>();
regions.add("0,1,0,0");
regions.add("0,1,1,1");
regions.add("0,1,2,2");
regions.add("0,0,3,5");
// 合并单元格
for (String string : regions) {
String[] temp = string.split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
CellRangeAddress region=new CellRangeAddress(startrow, overrow, startcol, overcol);
sheet.addMergedRegion(region);
}
// 准备将Excel的输出流通过response输出到页面下载
// 八进制输出流
response.setContentType("application/octet-stream");
// 这后面可以设置导出Excel的名称,此例中名为student.xls
response.setHeader("Content-disposition", "attachment;filename=student.xls");
// 刷新缓冲
response.flushBuffer();
// workbook将Excel写入到response的输出流中,供页面下载
workbook.write(response.getOutputStream());
}
}
3、启动项目,访问http://localhost:9997/excel/exportExcel
- 下载的excel内容如下
4、基本的导出已经实现,下面将Controller进行拆分,抽出对应的工具类
- 创建数据对象类ExcelData
public class ExcelData implements Serializable {
private static final long serialVersionUID = 4444017239100620999L;
// 一级表头
private List<String> titles0;
// 二级表头
private List<String> titles1;
// 数据
private List<List<Object>> rows;
// 页签名称
private String name;
public List<String> getTitles0() {
return titles0;
}
public void setTitles0(List<String> titles0) {
this.titles0 = titles0;
}
public List<String> getTitles1() {
return titles1;
}
public void setTitles1(List<String> titles1) {
this.titles1 = titles1;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
- 创建导出工具类ExcelUtil--接收ExcelData和需要合并的集合
public class ExcelUtil {
public static HSSFWorkbook exportExcel(ExcelData data, List<String> regions) throws Exception {
// 声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格,设置表格名称
HSSFSheet sheet = workbook.createSheet(data.getName());
// 声明一个单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
// 设置表格列宽度为15个字节
sheet.setDefaultColumnWidth(15);
// 创建第一行表头
HSSFRow headrow = sheet.createRow(0);
for (int i = 0; i < data.getTitles0().size(); i++) {
// 创建一个单元格
HSSFCell cell = headrow.createCell(i);
// 创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(data.getTitles0().get(i));
// 将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
// 设置单元格样式
cell.setCellStyle(cellStyle);
}
// 创建第二行表头
HSSFRow headrow1 = sheet.createRow(1);
for (int i = 0; i < data.getTitles1().size(); i++) {
HSSFCell cell = headrow1.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(data.getTitles1().get(i));
cell.setCellValue(text);
cell.setCellStyle(cellStyle);
}
// 遍历添加数据
for (int i = 0; i < data.getRows().size(); i++) {
HSSFRow row = sheet.createRow(i+2);
for (int j = 0; j < data.getRows().get(i).size(); j++) {
HSSFCell cell = row.createCell(j);
HSSFRichTextString text = new HSSFRichTextString(String.valueOf(data.getRows().get(i).get(j)));
cell.setCellValue(text);
cell.setCellStyle(cellStyle);
}
}
// 合并单元格
for (String string : regions) {
String[] temp = string.split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
CellRangeAddress region=new CellRangeAddress(startrow, overrow, startcol, overcol);
sheet.addMergedRegion(region);
}
return workbook;
}
}
- 创建导出Service,封装数据ExcelData和合并集合
@Service
public class ExcelServiceImpl implements ExcelService {
@Override
public HSSFWorkbook export() throws IOException {
ExcelData excelData = new ExcelData();
// 表头数据
// 一级表头
List<String> header0 = new ArrayList<String>();
header0.add("ID");
header0.add("姓名");
header0.add("性别");
header0.add("分数");
header0.add("分数");
header0.add("分数");
// 二级表头
List<String> header1 = new ArrayList<String>();
header1.add("ID");
header1.add("姓名");
header1.add("性别");
header1.add("语文");
header1.add("数学");
header1.add("英语");
// 数据内容
List<List<Object>> rows = new ArrayList<List<Object>>();
// 学生一
List<Object> row0 = new ArrayList<Object>();
row0.add(1);
row0.add("小红");
row0.add("女");
row0.add("99");
row0.add("80");
row0.add("98");
// 学生二
List<Object> row1 = new ArrayList<Object>();
row1.add(2);
row1.add("小强");
row1.add("男");
row1.add("96");
row1.add("92");
row1.add("91");
// 学生三
List<Object> row2 = new ArrayList<Object>();
row2.add(3);
row2.add("小明");
row2.add("男");
row2.add("82");
row2.add("88");
row2.add("95");
rows.add(row0);
rows.add(row1);
rows.add(row2);
// 需要合并的集合
List<String> regions = new ArrayList<String>();
// 表头需要合并的集合
regions.add("0,1,0,0");
regions.add("0,1,1,1");
regions.add("0,1,2,2");
regions.add("0,0,3,5");
excelData.setTitles0(header0);
excelData.setTitles1(header1);
excelData.setRows(rows);
excelData.setName("学生成绩表");
HSSFWorkbook workbook = null;
try {
workbook = ExcelUtil.exportExcel(excelData, regions);
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
}
- Controller调用Service得到工作簿HSSFWorkbook,返回下载
@Controller
@RequestMapping("/excel")
public class ExcelController {
@Autowired
ExcelService excelService;
/**
* Excel表格导出接口
* http://localhost:9997/excel/exportExcel
* @param response response对象
* @throws IOException 抛IO异常
*/
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
// 调用Service,得到一个HSSFWorkbook对象
HSSFWorkbook workbook = excelService.export();
// 准备将Excel的输出流通过response输出到页面下载
// 八进制输出流
response.setContentType("application/octet-stream");
// 这后面可以设置导出Excel的名称,此例中名为student.xls
response.setHeader("Content-disposition", "attachment;filename=student.xls");
// 刷新缓冲
response.flushBuffer();
// workbook将Excel写入到response的输出流中,供页面下载
workbook.write(response.getOutputStream());
}
}