使用Jakarta POI实现导出Excel表格
导包就不讲了,下面直接看代码:
package com.hxlh.common.service;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Created by jnn on 2018/03/21.
*/
@Controller
public class ExportExcel {
@RequestMapping("/test")
public String CreatExcel(HttpServletResponse response) throws IOException {
//模拟考勤数据,放到list中
List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
Map map1 = new HashedMap();
map1.put("name","张飞");
map1.put("date","2018-03-21");
map1.put("goTime","08:56");
map1.put("outTime","17:56");
map1.put("department","IT部");
Map map2 = new HashedMap();
map2.put("name","关羽");
map2.put("date","2018-03-21");
map2.put("goTime","08:00");
map2.put("outTime","17:30");
map2.put("department","IT部");
Map map3 = new HashedMap();
map3.put("name","刘备");
map3.put("date","2018-03-21");
map3.put("goTime","08:30");
map3.put("outTime","19:00");
map3.put("department","IT部");
mapList.add(map1);
mapList.add(map2);
mapList.add(map3);
String fileName = "考勤文件";//Excel文件名字
String sheetName = "sheet名称";//Excel中sheet名称
String titleName = "2018年3月考勤";//表格的标题名称
int[] columnWidth = {30,30,30,30,30};//每列的宽度
String[] columnName = {"姓名","打卡日期","上班时间","下班时间","部门"};//列标题
String[] columnKey = {"name","date","goTime","outTime","department"};//对于Map中的key值
ExportExcel(mapList,fileName,sheetName,titleName,columnWidth,columnName,columnKey,response);
return null;
}
//导出Excel方法
public static void ExportExcel(List<Map<String, String>> dataList,
String fileName, String sheetName,
String titleName, int[] columnWidth,
HttpServletResponse response) throws IOException {
//创建HSSFWorkbook对象(Excel文件)
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet表
HSSFSheet sheet = wb.createSheet(sheetName);
//设置每列的宽度
for (int i = 0; i < columnWidth.length; i++) {
sheet.setColumnWidth(i,columnWidth[i] * 256);
}
//创建三种单元格样式,分别用于表格标题、列标题、数据区域
//第一种样式,用于表格标题
HSSFCellStyle style1 = wb.createCellStyle();//创建单元格样式
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置对齐方式,横向居中
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置对齐方式,纵向居中
//style1.setBottomBorderColor(HSSFColor.RED.index);//
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
HSSFFont font1 = wb.createFont();//创建字体格式
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
font1.setFontName("黑体");//字体
font1.setFontHeightInPoints((short) 15);//字号
style1.setFont(font1);//字体格式应用到单元格样式
//第二种样式,用于表格标题
HSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style2.setBottomBorderColor(HSSFColor.BLACK.index);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font2 = wb.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font2.setFontName("黑体");
font2.setFontHeightInPoints((short) 13);
style2.setFont(font2);
//第三种样式,用于表格标题
HSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style3.setBottomBorderColor(HSSFColor.BLACK.index);
style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font3 = wb.createFont();
font3.setFontName("楷体");
font3.setFontHeightInPoints((short) 12);
style3.setFont(font3);
//创建第一行,表格标题,如果不循环,则边框显示不全
HSSFRow rowTitle = sheet.createRow(0);
for (int i = 0; i < columnName.length; i++ ) {
HSSFCell cellTitle = rowTitle.createCell(i);
cellTitle.setCellValue(titleName);
cellTitle.setCellStyle(style1);
}
//合并单元格:起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,columnName.length -1));
//创建表头
HSSFRow rowHeader = sheet.createRow(1);
for (int i = 0; i < columnName.length; i++) {
HSSFCell cellHeader = rowHeader.createCell(i);
cellHeader.setCellValue(columnName[i]);
cellHeader.setCellStyle(style2);
}
//创建数据区域
for (int i = 2; i < dataList.size() + 2; i++) {
HSSFRow row = sheet.createRow(i);
for (int j = 0; j < columnKey.length; j++ ) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(dataList.get(i-2).get(columnKey[j]));//用Map中Key取数据
cell.setCellStyle(style3);
}
}
//向浏览器输出Excel文件
String fileName2 = fileName + ".xls";
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName2.getBytes(),"ISO-8859-1"));
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
}
}
在浏览器中下载文件
导出后Excel样式