本篇文章为你整理了java导出excel(一)：单sheet（）的详细内容，包含有 java导出excel(一)：单sheet，希望能帮助你了解 java导出excel(一)：单sheet。
相信在大部分的web项目中都会有导出导入Excel的需求，今天我们就来看看如何用Java代码去实现 用POI导出Excel表格。
一、pom引用
pom文件中，添加以下依赖
!--Excel工具--
dependency
groupId org.apache.poi /groupId
artifactId poi /artifactId
version 5.2.2 /version
scope compile /scope
/dependency
dependency
groupId org.apache.poi /groupId
artifactId poi-ooxml /artifactId
version 5.2.2 /version
scope compile /scope
/dependency
二、工具类util
1.ExcelClassField
package com.***;
import lombok.Data;
import java.util.LinkedHashMap;
* @description: excel字段
* @author: ***
* @date: 2022/6/21
@Data
public class ExcelClassField {
* 字段名称
private String fieldName;
* 表头名称
private String name;
* 映射关系
private LinkedHashMap String, String kvMap;
* 示例值
private Object example;
* 排序
private int sort;
* 是否为注解字段：0-否，1-是
private int hasAnnotation;
}
2.ExcelExport（导出Excel用）
package com.***;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
* @description: excel导出
* @author: ***
* @date: 2022/6/21
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelExport {
* 字段名称
String value();
* 导出排序先后: 数字越小越靠前（默认按Java类字段顺序导出）
int sort() default 0;
* 导出映射，格式如：0-未知;1-男;2-女
String kv() default "";
* 导出模板示例值（有值的话，直接取该值，不做映射）
String example() default "";
}
3.ExcelImport（导入Excel用）
package com.***;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
* @description: excel导入
* @author: ***
* @date: 2022/6/21
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelImport {
* 字段名称
String value();
* 导出映射，格式如：0-未知;1-男;2-女
String kv() default "";
* 是否为必填字段（默认为非必填）
boolean required() default false;
* 最大长度（默认255）
int maxLength() default 255;
* 导入唯一性验证（多个字段则取联合验证）
boolean unique() default false;
}
4.ExcelUtils
package com.***;
import com.***.utils.DateUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.***.uitl.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Pattern;
public static final String ROW_MERGE = "row_merge";
public static final String COLUMN_MERGE = "column_merge";
private static final String XLSX = ".xlsx";
private static final String XLS = ".xls";
private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
private static final String ROW_NUM = "rowNum";
private static final String ROW_DATA = "rowData";
private static final String ROW_TIPS = "rowTips";
private static final int CELL_OTHER = 0;
private static final int CELL_ROW_MERGE = 1;
private static final int CELL_COLUMN_MERGE = 2;
private static final int IMG_HEIGHT = 30;
private static final int IMG_WIDTH = 30;
private static final char LEAN_LINE = /;
private static final int BYTES_DEFAULT_LENGTH = 10240;
private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();
public static T List T readFile(File file, Class T clazz) throws Exception {
JSONArray array = readFile(file);
return getBeanList(array, clazz);
public static T List T readMultipartFile(MultipartFile mFile, Class T clazz) throws Exception {
JSONArray array = readMultipartFile(mFile);
return getBeanList(array, clazz);
public static JSONArray readFile(File file) throws Exception {
return readExcel(null, file);
public static JSONArray readMultipartFile(MultipartFile mFile) throws Exception {
return readExcel(mFile, null);
public static Map String, JSONArray readFileManySheet(File file) throws Exception {
return readExcelManySheet(null, file);
public static Map String, JSONArray readFileManySheet(MultipartFile file) throws Exception {
return readExcelManySheet(file, null);
private static T List T getBeanList(JSONArray array, Class T clazz) throws Exception {
List T list = new ArrayList ();
Map Integer, String uniqueMap = new HashMap (16);
for (int i = 0; i array.size(); i++) {
list.add(getBean(clazz, array.getJSONObject(i), uniqueMap));
return list;
* 获取每个对象的数据
private static T T getBean(Class T c, JSONObject obj, Map Integer, String uniqueMap) throws Exception {
T t = c.newInstance();
Field[] fields = c.getDeclaredFields();
List String errMsgList = new ArrayList ();
boolean hasRowTipsField = false;
StringBuilder uniqueBuilder = new StringBuilder();
int rowNum = 0;
for (Field field : fields) {
// 行号
if (field.getName().equals(ROW_NUM)) {
rowNum = obj.getInteger(ROW_NUM);
field.setAccessible(true);
field.set(t, rowNum);
continue;
// 是否需要设置异常信息
if (field.getName().equals(ROW_TIPS)) {
hasRowTipsField = true;
continue;
// 原始数据
if (field.getName().equals(ROW_DATA)) {
field.setAccessible(true);
field.set(t, obj.toString());
continue;
// 设置对应属性值
setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
// 数据唯一性校验
if (uniqueBuilder.length() 0) {
if (uniqueMap.containsValue(uniqueBuilder.toString())) {
Set Integer rowNumKeys = uniqueMap.keySet();
for (Integer num : rowNumKeys) {
if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, num));
} else {
uniqueMap.put(rowNum, uniqueBuilder.toString());
// 失败处理
if (errMsgList.isEmpty() !hasRowTipsField) {
return t;
StringBuilder sb = new StringBuilder();
int size = errMsgList.size();
for (int i = 0; i size; i++) {
if (i == size - 1) {
sb.append(errMsgList.get(i));
} else {
sb.append(errMsgList.get(i)).append(";");
// 设置错误信息
for (Field field : fields) {
if (field.getName().equals(ROW_TIPS)) {
field.setAccessible(true);
field.set(t, sb.toString());
return t;
private static T void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List String errMsgList) {
// 获取 ExcelImport 注解属性
ExcelImport annotation = field.getAnnotation(ExcelImport.class);
if (annotation == null) {
return;
String cname = annotation.value();
if (cname.trim().length() == 0) {
return;
// 获取具体值
String val = null;
if (obj.containsKey(cname)) {
val = getString(obj.getString(cname));
if (val == null) {
return;
field.setAccessible(true);
// 判断是否必填
boolean require = annotation.required();
if (require val.isEmpty()) {
errMsgList.add(String.format("[%s]不能为空", cname));
return;
// 数据唯一性获取
boolean unique = annotation.unique();
if (unique) {
if (uniqueBuilder.length() 0) {
uniqueBuilder.append("--").append(val);
} else {
uniqueBuilder.append(val);
// 判断是否超过最大长度
int maxLength = annotation.maxLength();
if (maxLength 0 val.length() maxLength) {
errMsgList.add(String.format("[%s]长度不能超过%s个字符(当前%s个字符)", cname, maxLength, val.length()));
// 判断当前属性是否有映射关系
LinkedHashMap String, String kvMap = getKvMap(annotation.kv());
if (!kvMap.isEmpty()) {
boolean isMatch = false;
for (String key : kvMap.keySet()) {
if (kvMap.get(key).equals(val)) {
val = key;
isMatch = true;
break;
if (!isMatch) {
errMsgList.add(String.format("[%s]的值不正确(当前值为%s)", cname, val));
return;
// 其余情况根据类型赋值
String fieldClassName = field.getType().getSimpleName();
try {
if ("String".equalsIgnoreCase(fieldClassName)) {
field.set(t, val);
} else if ("boolean".equalsIgnoreCase(fieldClassName)) {
field.set(t, Boolean.valueOf(val));
} else if ("int".equalsIgnoreCase(fieldClassName) "Integer".equals(fieldClassName)) {
try {
field.set(t, Integer.valueOf(val));
} catch (NumberFormatException e) {
errMsgList.add(String.format("[%s]的值格式不正确(当前值为%s)", cname, val));
} else if ("double".equalsIgnoreCase(fieldClassName)) {
field.set(t, Double.valueOf(val));
} else if ("long".equalsIgnoreCase(fieldClassName)) {
field.set(t, Long.valueOf(val));
} else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
if (StringUtils.isEmpty(val)) {
field.set(t, new BigDecimal(0));
} else {
field.set(t, new BigDecimal(val));
} else if ("Date".equalsIgnoreCase(fieldClassName)) {
field.set(t, DateUtil.getDate(val, DateUtil.YYYYMMDDHHMMSS));
} else {
field.set(t, val);
} catch (Exception e) {
e.printStackTrace();
private static Map String, JSONArray readExcelManySheet(MultipartFile mFile, File file) throws IOException {
Workbook book = getWorkbook(mFile, file);
if (book == null) {
return Collections.emptyMap();
Map String, JSONArray map = new LinkedHashMap ();
for (int i = 0; i book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
JSONArray arr = readSheet(sheet);
map.put(sheet.getSheetName(), arr);
book.close();
return map;
private static JSONArray readExcel(MultipartFile mFile, File file) throws IOException {
Workbook book = getWorkbook(mFile, file);
if (book == null) {
return new JSONArray();
JSONArray array = readSheet(book.getSheetAt(0));
book.close();
return array;
private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
boolean fileNotExist = (file == null !file.exists());
if (mFile == null fileNotExist) {
return null;
// 解析表格数据
InputStream in;
String fileName;
if (mFile != null) {
// 上传文件解析
in = mFile.getInputStream();
fileName = getString(mFile.getOriginalFilename()).toLowerCase();
} else {
// 本地文件解析
in = new FileInputStream(file);
fileName = file.getName().toLowerCase();
Workbook book;
if (fileName.endsWith(XLSX)) {
book = new XSSFWorkbook(in);
} else if (fileName.endsWith(XLS)) {
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
book = new HSSFWorkbook(poifsFileSystem);
} else {
return null;
in.close();
return book;
private static JSONArray readSheet(Sheet sheet) {
// 首行下标
int rowStart = sheet.getFirstRowNum();
// 尾行下标
int rowEnd = sheet.getLastRowNum();
// 获取表头行
Row headRow = sheet.getRow(rowStart);
if (headRow == null) {
return new JSONArray();
int cellStart = headRow.getFirstCellNum();
int cellEnd = headRow.getLastCellNum();
Map Integer, String keyMap = new HashMap ();
for (int j = cellStart; j cellEnd; j++) {
// 获取表头数据
String val = getCellValue(headRow.getCell(j));
if (val != null val.trim().length() != 0) {
keyMap.put(j, val);
// 如果表头没有数据则不进行解析
if (keyMap.isEmpty()) {
return (JSONArray) Collections.emptyList();
// 获取每行JSON对象的值
JSONArray array = new JSONArray();
// 如果首行与尾行相同，表明只有一行，返回表头数据
if (rowStart == rowEnd) {
JSONObject obj = new JSONObject();
// 添加行号
obj.put(ROW_NUM, 1);
for (int i : keyMap.keySet()) {
obj.put(keyMap.get(i), "");
array.add(obj);
return array;
for (int i = rowStart + 1; i = rowEnd; i++) {
Row eachRow = sheet.getRow(i);
JSONObject obj = new JSONObject();
// 添加行号
obj.put(ROW_NUM, i + 1);
StringBuilder sb = new StringBuilder();
for (int k = cellStart; k cellEnd; k++) {
if (eachRow != null) {
String val = getCellValue(eachRow.getCell(k));
// 所有数据添加到里面，用于判断该行是否为空
sb.append(val);
obj.put(keyMap.get(k), val);
if (sb.length() 0) {
array.add(obj);
return array;
private static String getCellValue(Cell cell) {
// 空白或空
if (cell == null cell.getCellType() == CellType.BLANK) {
return "";
// String类型
if (cell.getCellType() == CellType.STRING) {
String val = cell.getStringCellValue();
if (val == null val.trim().length() == 0) {
return "";
return val.trim();
// 数字类型
if (cell.getCellType() == CellType.NUMERIC) {
String s = cell.getNumericCellValue() + "";
// 去掉尾巴上的小数点0
if (Pattern.matches(".*\\.0*", s)) {
return s.split("\\.")[0];
} else {
return s.trim();
// 布尔值类型
if (cell.getCellType() == CellType.BOOLEAN) {
return cell.getBooleanCellValue() + "";
// 错误类型
return cell.getCellFormula();
public static T void exportTemplate(HttpServletResponse response, String fileName, Class T clazz, String dateFormat) {
exportTemplate(response, fileName, fileName, clazz, false, dateFormat);
public static T void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
Class T clazz, String dateFormat) {
exportTemplate(response, fileName, sheetName, clazz, false, dateFormat);
public static T void exportTemplate(HttpServletResponse response, String fileName, Class T clazz,
boolean isContainExample, String dateFormat) {
exportTemplate(response, fileName, fileName, clazz, isContainExample, dateFormat);
public static T void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
Class T clazz, boolean isContainExample, String dateFormat) {
// 获取表头字段
List ExcelClassField headFieldList = getExcelClassFieldList(clazz);
// 获取表头数据和示例数据
List List Object sheetDataList = new ArrayList ();
List Object headList = new ArrayList ();
List Object exampleList = new ArrayList ();
Map Integer, List String selectMap = new LinkedHashMap ();
for (int i = 0; i headFieldList.size(); i++) {
ExcelClassField each = headFieldList.get(i);
headList.add(each.getName());
exampleList.add(each.getExample());
LinkedHashMap String, String kvMap = each.getKvMap();
if (kvMap != null kvMap.size() 0) {
selectMap.put(i, new ArrayList (kvMap.values()));
sheetDataList.add(headList);
if (isContainExample) {
sheetDataList.add(exampleList);
// 导出数据
export(response, fileName, sheetName, sheetDataList, selectMap, dateFormat);
private static T List ExcelClassField getExcelClassFieldList(Class T clazz) {
// 解析所有字段
Field[] fields = clazz.getDeclaredFields();
boolean hasExportAnnotation = false;
Map Integer, List ExcelClassField map = new LinkedHashMap ();
List Integer sortList = new ArrayList ();
for (Field field : fields) {
ExcelClassField cf = getExcelClassField(field);
if (cf.getHasAnnotation() == 1) {
hasExportAnnotation = true;
int sort = cf.getSort();
if (map.containsKey(sort)) {
map.get(sort).add(cf);
} else {
List ExcelClassField list = new ArrayList ();
list.add(cf);
sortList.add(sort);
map.put(sort, list);
Collections.sort(sortList);
// 获取表头
List ExcelClassField headFieldList = new ArrayList ();
if (hasExportAnnotation) {
for (Integer sort : sortList) {
for (ExcelClassField cf : map.get(sort)) {
if (cf.getHasAnnotation() == 1) {
headFieldList.add(cf);
} else {
headFieldList.addAll(map.get(0));
return headFieldList;
private static ExcelClassField getExcelClassField(Field field) {
ExcelClassField cf = new ExcelClassField();
String fieldName = field.getName();
cf.setFieldName(fieldName);
ExcelExport annotation = field.getAnnotation(ExcelExport.class);
// 无 ExcelExport 注解情况
if (annotation == null) {
cf.setHasAnnotation(0);
cf.setName(fieldName);
cf.setSort(0);
return cf;
// 有 ExcelExport 注解情况
cf.setHasAnnotation(1);
cf.setName(annotation.value());
String example = getString(annotation.example());
if (!example.isEmpty()) {
if (isNumeric(example)) {
cf.setExample(Double.valueOf(example));
} else {
cf.setExample(example);
} else {
cf.setExample("");
cf.setSort(annotation.sort());
// 解析映射
String kv = getString(annotation.kv());
cf.setKvMap(getKvMap(kv));
return cf;
private static LinkedHashMap String, String getKvMap(String kv) {
LinkedHashMap String, String kvMap = new LinkedHashMap ();
if (kv.isEmpty()) {
return kvMap;
String[] kvs = kv.split(";");
if (kvs.length == 0) {
return kvMap;
for (String each : kvs) {
String[] eachKv = getString(each).split("-");
if (eachKv.length != 2) {
continue;
String k = eachKv[0];
String v = eachKv[1];
if (k.isEmpty() v.isEmpty()) {
continue;
kvMap.put(k, v);
return kvMap;
* 导出表格到本地
* @param file 本地文件对象
* @param sheetData 导出数据
public static void exportFile(File file, List List Object sheetData, String dateFormat) {
if (file == null) {
System.out.println("文件创建失败");
return;
if (sheetData == null) {
sheetData = new ArrayList ();
Map String, List List Object map = new HashMap ();
map.put(file.getName(), sheetData);
export(null, file, file.getName(), map, null, dateFormat);
* 导出表格到本地
* @param T 导出数据类似，和K类型保持一致
* @param filePath 文件父路径（如：D:/doc/excel/）
* @param fileName 文件名称（不带尾缀，如：学生表）
* @param list 导出数据
* @throws IOException IO异常
public static T File exportFile(String filePath, String fileName, List T list, String dateFormat) throws IOException {
File file = getFile(filePath, fileName);
List List Object sheetData = getSheetData(list);
exportFile(file, sheetData, dateFormat);
return file;
* 获取文件
* @param filePath filePath 文件父路径（如：D:/doc/excel/）
* @param fileName 文件名称（不带尾缀，如：用户表）
* @return 本地File文件对象
private static File getFile(String filePath, String fileName) throws IOException {
String dirPath = getString(filePath);
String fileFullPath;
if (dirPath.isEmpty()) {
fileFullPath = fileName;
} else {
// 判定文件夹是否存在，如果不存在，则级联创建
File dirFile = new File(dirPath);
if (!dirFile.exists()) {
dirFile.mkdirs();
// 获取文件夹全名
if (dirPath.endsWith(String.valueOf(LEAN_LINE))) {
fileFullPath = dirPath + fileName + XLSX;
} else {
fileFullPath = dirPath + LEAN_LINE + fileName + XLSX;
System.out.println(fileFullPath);
File file = new File(fileFullPath);
if (!file.exists()) {
file.createNewFile();
return file;
private static T List List Object getSheetData(List T list) {
// 获取表头字段
List ExcelClassField excelClassFieldList = getExcelClassFieldList(list.get(0).getClass());
List String headFieldList = new ArrayList ();
List Object headList = new ArrayList ();
Map String, ExcelClassField headFieldMap = new HashMap ();
for (ExcelClassField each : excelClassFieldList) {
String fieldName = each.getFieldName();
headFieldList.add(fieldName);
headFieldMap.put(fieldName, each);
headList.add(each.getName());
// 添加表头名称
List List Object sheetDataList = new ArrayList ();
sheetDataList.add(headList);
// 获取表数据
for (T t : list) {
Map String, Object fieldDataMap = getFieldDataMap(t);
Set String fieldDataKeys = fieldDataMap.keySet();
List Object rowList = new ArrayList ();
for (String headField : headFieldList) {
if (!fieldDataKeys.contains(headField)) {
continue;
Object data = fieldDataMap.get(headField);
if (data == null) {
rowList.add("");
continue;
// 超防止过11位的数字自动转换为科学计数法
String string = data.toString();
if (isNumeric(string) string.length() 11) {
data = new BigDecimal(string).toPlainString();
ExcelClassField cf = headFieldMap.get(headField);
// 判断是否有映射关系
LinkedHashMap String, String kvMap = cf.getKvMap();
if (kvMap == null kvMap.isEmpty()) {
rowList.add(data);
continue;
String val = kvMap.get(data.toString());
if (val == null) {
rowList.add(data);
continue;
if (isNumeric(val)) {
rowList.add(Double.valueOf(val));
} else {
rowList.add(val);
sheetDataList.add(rowList);
return sheetDataList;
private static T Map String, Object getFieldDataMap(T t) {
Map String, Object map = new HashMap ();
Field[] fields = t.getClass().getDeclaredFields();
try {
for (Field field : fields) {
String fieldName = field.getName();
field.setAccessible(true);
Object object = field.get(t);
map.put(fieldName, object);
} catch (IllegalArgumentException IllegalAccessException e) {
e.printStackTrace();
return map;
public static void exportEmpty(HttpServletResponse response, String fileName, String dateFormat) {
List List Object sheetDataList = new ArrayList ();
List Object headList = new ArrayList ();
headList.add("导出无数据");
sheetDataList.add(headList);
export(response, fileName, sheetDataList, dateFormat);
public static void export(HttpServletResponse response, String fileName, List List Object sheetDataList, String dateFormat) {
export(response, fileName, fileName, sheetDataList, null, dateFormat);
public static void exportManySheet(HttpServletResponse response, String fileName, Map String, List List Object sheetMap, String dateFormat) {
export(response, null, fileName, sheetMap, null, dateFormat);
public static void export(HttpServletResponse response, String fileName, String sheetName,
List List Object sheetDataList, String dateFormat) {
export(response, fileName, sheetName, sheetDataList, null, dateFormat);
public static void export(HttpServletResponse response, String fileName, String sheetName,
List List Object sheetDataList, Map Integer, List String selectMap, String dateFormat) {
Map String, List List Object map = new HashMap ();
map.put(sheetName, sheetDataList);
export(response, null, fileName, map, selectMap, dateFormat);
public static T, K void export(HttpServletResponse response, String fileName, List T list, Class K template) {
String dateFormat = DATE_FORMAT;
// list 是否为空
boolean lisIsEmpty = list == null list.isEmpty();
// 如果模板数据为空，且导入的数据为空，则导出空文件
if (template == null lisIsEmpty) {
exportEmpty(response, fileName, dateFormat);
return;
// 如果 list 数据，则导出模板数据
if (lisIsEmpty) {
exportTemplate(response, fileName, template, dateFormat);
return;
// 导出数据
List List Object sheetDataList = getSheetData(list);
export(response, fileName, sheetDataList, dateFormat);
public static T, K void export(HttpServletResponse response, String fileName, List T list, Class K template, String dateFormat) {
// list 是否为空
boolean lisIsEmpty = list == null list.isEmpty();
// 如果模板数据为空，且导入的数据为空，则导出空文件
if (template == null lisIsEmpty) {
exportEmpty(response, fileName, dateFormat);
return;
// 如果 list 数据，则导出模板数据
if (lisIsEmpty) {
exportTemplate(response, fileName, template, dateFormat);
return;
// 导出数据
List List Object sheetDataList = getSheetData(list);
export(response, fileName, sheetDataList, dateFormat);
public static void export(HttpServletResponse response, String fileName, List List Object sheetDataList, Map Integer, List String selectMap, String dateFormat) {
export(response, fileName, fileName, sheetDataList, selectMap, dateFormat);
private static void export(HttpServletResponse response, File file, String fileName,
Map String, List List Object sheetMap, Map Integer, List String selectMap, String dateFormat) {
// 整个 Excel 表格 book 对象
SXSSFWorkbook book = new SXSSFWorkbook();
// 每个 Sheet 页
Set Entry String, List List Object entries = sheetMap.entrySet();
for (Entry String, List List Object entry : entries) {
List List Object sheetDataList = entry.getValue();
Sheet sheet = book.createSheet(entry.getKey());
Drawing ? patriarch = sheet.createDrawingPatriarch();
// 设置表头背景色（灰色）
CellStyle headStyle = book.createCellStyle();
headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
// 设置表身背景色（默认色）
CellStyle rowStyle = book.createCellStyle();
rowStyle.setAlignment(HorizontalAlignment.CENTER);
rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//创建一个DataFormat对象
DataFormat format = book.createDataFormat();
rowStyle.setDataFormat(format.getFormat("@"));
// 设置表格列宽度（默认为15个字节）
sheet.setDefaultColumnWidth(15);
// 创建合并算法数组
int rowLength = sheetDataList.size();
int columnLength = sheetDataList.get(0).size();
int[][] mergeArray = new int[rowLength][columnLength];
for (int i = 0; i sheetDataList.size(); i++) {
// 每个 Sheet 页中的行数据
Row row = sheet.createRow(i);
List Object rowList = sheetDataList.get(i);
for (int j = 0; j rowList.size(); j++) {
// 每个行数据中的单元格数据
Object o = rowList.get(j);
int v = 0;
if (o instanceof URL) {
// 如果要导出图片的话, 链接需要传递 URL 对象
setCellPicture(book, row, patriarch, i, j, (URL) o);
} else {
Cell cell = row.createCell(j);
if (i == 0) {
// 第一行为表头。
郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。