首先依赖jar包:
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
主要的工具类:
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* Excel工具类
* @author gourd
*/
@Slf4j
public class ExportExcelUtil {
/**
* 导出数据
* @param rowName 头部列
* @param dataList 行数据
* @param excelTitle 导出文件名称
* @param dataTitle 表格头名称
* @param request
* @param response
*/
public static void export(String[] rowName,List<Object[]> dataList,String excelTitle,String dataTitle,
HttpServletRequest request, HttpServletResponse response) {
// firefox浏览器
try {
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
excelTitle = new String(excelTitle.getBytes("UTF-8"), "ISO8859-1");
} else {
// 其他浏览器包括IE浏览器和google浏览器
excelTitle = URLEncoder.encode(excelTitle, "UTF-8");
}
} catch (UnsupportedEncodingException e) {
log.error("转换标题失败", e);
}
response.setHeader("conent-type", "application/octet-stream");
response.setContentType("application/octet-stream");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", "attachment; filename=" + excelTitle);
if(dataList == null){
// 设置空集合
dataList = new ArrayList<>();
}
// try-with-resources 语句,会自动关闭资源
try (OutputStream outputStream = response.getOutputStream()) {
// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = workbook.createSheet(dataTitle);
// 设置所有单元格高度为 25
sheet.setDefaultRowHeight((short)500);
//设置所有单元格宽度
//sheet.setDefaultColumnWidth((short)10);
// 产生表格标题行
XSSFRow rowm = sheet.createRow(0);
XSSFCell cellTiltle = rowm.createCell(0);
// 获取列头样式对象
XSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);
// 单元格样式对象
XSSFCellStyle style = getStyle(workbook);
// 设置合并单元格(标题)
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,
(rowName.length - 1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(dataTitle);
// 定义所需列数
int columnNum = rowName.length;
// 在索引2的位置创建行(最顶端的行开始的第二行)
XSSFRow rowRowName = sheet.createRow(2);
// 将列头设置到sheet的单元格中
for (int i = 0; i < columnNum; i++) {
// 创建列头对应个数的单元格
XSSFCell cellRowName = rowRowName.createCell(i);
// 设置列头单元格的数据类型
cellRowName.setCellType(XSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(rowName[i]);
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
// 将数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
// 遍历每个对象
Object[] obj = dataList.get(i);
// 创建所需的行数
XSSFRow row = sheet.createRow(i + 3);
for (int j = 0; j < obj.length; j++) {
// 设置单元格的数据类型
XSSFCell cell = null;
cell = row.createCell(j, XSSFCell.CELL_TYPE_STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
// 设置单元格的值
cell.setCellValue(obj[j].toString());
}
// 设置单元格样式
cell.setCellStyle(style);
}
}
// 让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum()+1; rowNum++) {
XSSFRow currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
XSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue()
.getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
// 列最大宽度为255
sheet.setColumnWidth(colNum, (columnWidth > 255 ? 255 : columnWidth)*256);
}
workbook.write(outputStream);
} catch (Exception e) {
log.error("导出失败", e);
}
}
/**
* 导入
* @param file 文件
* @param request
*/
public static void read(MultipartFile file, HttpServletRequest request){
XSSFWorkbook workBook = null;
try (InputStream inputStream = file.getInputStream()){
//读取工作簿
workBook = new XSSFWorkbook(inputStream);
//读取工作表
XSSFSheet sheet = workBook.getSheetAt(0);
//读取行
XSSFRow row = sheet.getRow(2);
//读取单元格
XSSFCell cell = row.getCell(2);
String value = cell.getStringCellValue();
// 业务处理 ...
} catch (IOException e) {
log.error("读取excel失败",e);
}finally {
if(workBook != null){
//关闭工作簿
try {
workBook.close();
} catch (IOException e) {
log.error("关闭工作簿失败",e);
}
}
}
}
/**
* 列头单元格样式
* @param workbook
* @return
*/
public static XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {
// 设置字体
XSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
XSSFCellStyle style = getXssfCellStyle(workbook, font);
return style;
}
/**
* 列数据信息单元格样式
* @param workbook
* @return
*/
public static XSSFCellStyle getStyle(XSSFWorkbook workbook) {
// 设置字体
XSSFFont font = workbook.createFont();
XSSFCellStyle style = getXssfCellStyle(workbook, font);
return style;
}
/**
* 获取单元格样式
* @param workbook
* @param font
* @return
*/
private static XSSFCellStyle getXssfCellStyle(XSSFWorkbook workbook, XSSFFont font) {
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
XSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
// 设置左边框;
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
// 设置右边框;
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
// 设置顶边框;
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(true);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
使用工具类非常简单:
// 准备数据
//定义表的内容
List<Object[]> dataList = new ArrayList<Object[]>();
// 定义表的列名
String[] columnNames = { "ID", "姓名", "性别" };
for (int i = 0; i < 10; i++) {
Object[] object = new Object[3];
object[0] = 1;
object[1] = "gourd";
object[2] = "男";
dataList.add(object);
}
// 导出Excel文件
ExportExcelUtil.export(columnNames,dataList,"测试数据.xlsx","用户数据",request,response);
使用easypoi导出导入更方便:https://blog.csdn.net/HXNLYW/article/details/96871319