POI 3.17 读取Excel（模板）、导出Excel

最新推荐文章于 2024-08-01 18:45:00 发布

Phil Jing

最新推荐文章于 2024-08-01 18:45:00 发布

阅读量2.3w

点赞数 16

分类专栏： Java POI 文章标签： excel poi xls xlsx 模板

本文链接：https://blog.csdn.net/phil_jing/article/details/78307819

版权

Java 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

POI

1 篇文章 1 订阅

订阅专栏

最近在给通用做一个项目，很多功能块需要导入Excel模板，我正好研究了几天

Maven依赖

<dependency>  
    <groupId>org.apache.commons</groupId>  
    <artifactId>commons-collections4</artifactId>  
    <version>4.1</version>  
</dependency>

<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.4</version>
</dependency>
 
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>

如果你想简化你的代码，可以加上

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.10</version>
    <scope>provided</scope>
</dependency>

一、导入

模板示例

新建模板Bean

package com.phil.excel;

import lombok.Data;

@Data
public class RefPartExcel {

	private String partNo;

	private String partName;

	private String refPartNo;
	
	private String refPartName;;

	private String length;
	
	private String width;
}

Lombok 注解在线帮助文档：http://projectlombok.org/features/index
介绍几个常用的 lombok 注解：
@Data ：注解在类上；提供类所有属性的 get 和 set 方法，此外还提供了equals、canEqual、hashCode、toString 方法
@Setter：注解在属性上；为属性提供 sett方法
@Getter：注解在属性上；为属性提供 get 方法
@Log4j ：注解在类上；为类提供一个属性名为log 的 log4j 日志对象
@NoArgsConstructor：注解在类上；为类提供一个无参的构造方法
@AllArgsConstructor：注解在类上；为类提供一个全参的构造方法
加一个注解，让属性对应列名

package com.phil.annotation;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn{
	
	public String value() default "";
}

这样Bean改写成

package com.phil.excel;

import com.phil.annotation.ExcelColumn;

import lombok.Data;

@Data
public class RefPartExcel {

	@ExcelColumn("原零件号")
	private String partNo;

	@ExcelColumn("原零件名称")
	private String partName;

	@ExcelColumn("参考零件号")
	private String refPartNo;
	
	@ExcelColumn("参考零件名称")
	private String refPartName;;

	@ExcelColumn("长")
	private String length;
	
	@ExcelColumn("宽")
	private String width;
}

上传我是采用的MultipartFile，可以读取.xlsx或者.xls格式的Excel(POI3.15以上版本可以兼容，不用再分别读取了)

package com.phil.excel.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.phil.excel.annotation.ExcelColumn;

/**
 * 功能说明：Excel 导入/导出
 * 典型用法：无
 * 特殊用法：无
 * 创建者：phil
 * 创建时间： 2017年11月9日
 * 修改人：phil
 * 修改时间：
 * 修改原因： 
 * 修改内容：
 * 版本号：1.0
 */
public class ExcelUtil {
	
    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");// 格式化 number为整
	
	private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐
	
//	private static final DecimalFormat df_per_ = new DecimalFormat("0.00%");//格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%
	
//	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 格式化日期字符串
	
	private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyy/MM/dd");
	
	private static final DecimalFormat DECIMAL_FORMAT_NUMBER  = new DecimalFormat("0.00E000"); //格式化科学计数器

	private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配
	
	/**
	 * 对外提供读取excel 的方法
	 * @param file
	 * @return
	 * @throws IOException
	 */
	public static List<List<Object>> readExcel(MultipartFile file) throws IOException {
		String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
		if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
			return readExcel(file.getInputStream());
		} else {
			throw new IOException("不支持的文件类型");
		}
	}
	
	/**
	 * 对外提供读取excel 的方法
	 * @param file
	 * @param cls
	 * @return
	 * @throws IOException
	 */
	public static <T> List<T> readExcel(MultipartFile file, Class<T> cls) throws IOException {
		String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
		if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
			return readExcel(file.getInputStream(), cls);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office excel
	 * 
	 * @param stream
	 * @return
	 * @throws IOException
	 */
	private static List<List<Object>> readExcel(InputStream inputStream) throws IOException {
		List<List<Object>> list = new LinkedList<>();
		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(inputStream);
			int sheetsNumber = workbook.getNumberOfSheets();
			for (int n = 0; n < sheetsNumber; n++) {
				Sheet sheet = workbook.getSheetAt(n);
				Object value = null;
				Row row = null;
				Cell cell = null;
				for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取
					row = sheet.getRow(i);
					if (StringUtils.isEmpty(row)) {
						continue;
					}
					List<Object> linked = new LinkedList<>();
					for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
						cell = row.getCell(j);
						if (StringUtils.isEmpty(cell)) {
							continue;
						}
						value = getCellValue(cell);
						linked.add(value);
					}
					list.add(linked);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(workbook);
			IOUtils.closeQuietly(inputStream);
		}
		return list;
	}

	/**
	 * 获取excel数据 将之转换成bean
	 * 
	 * @param path
	 * @param cls
	 * @param <T>
	 * @return
	 */
	private static <T> List<T> readExcel(InputStream inputStream, Class<T> cls) {
		List<T> dataList = new LinkedList<T>();
		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(inputStream);
			Map<String, List<Field>> classMap = new HashMap<String, List<Field>>();
			Field[] fields = cls.getDeclaredFields();
			for (Field field : fields) {
				ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
				if (annotation != null) {
					String value = annotation.value();
					if (!classMap.containsKey(value)) {
						classMap.put(value, new ArrayList<Field>());
					}
					field.setAccessible(true);
					classMap.get(value).add(field);
				}
			}
			Map<Integer, List<Field>> reflectionMap = new HashMap<Integer, List<Field>>();
			int sheetsNumber = workbook.getNumberOfSheets();
			for (int n = 0; n < sheetsNumber; n++) {
				Sheet sheet = workbook.getSheetAt(n);
				for (int j = sheet.getRow(0).getFirstCellNum(); j < sheet.getRow(0).getLastCellNum(); j++) { //首行提取注解
					Object cellValue = getCellValue(sheet.getRow(0).getCell(j)); 
					if (classMap.containsKey(cellValue)) {
						reflectionMap.put(j, classMap.get(cellValue));
					}
				}
				Row row = null;
				Cell cell = null;
				for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
					row = sheet.getRow(i);
					T t = cls.newInstance();
					for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
						cell = row.getCell(j);
						if (reflectionMap.containsKey(j)) {						
							Object cellValue = getCellValue(cell);
							List<Field> fieldList = reflectionMap.get(j);
							for (Field field : fieldList) {
								try {
									field.set(t, cellValue);
								} catch (Exception e) {
									//logger.error()
								}
							}
						}
					}
					dataList.add(t);
				}
			}
		} catch (Exception e) {
			dataList = null;
		} finally {
			IOUtils.closeQuietly(workbook);
			IOUtils.closeQuietly(inputStream);
		}
		return dataList;
	}

	/**
	 * 获取excel 单元格数据
	 * 
	 * @param cell
	 * @return
	 */
	private static Object getCellValue(Cell cell) {
		Object value = null;
		switch (cell.getCellTypeEnum()) {
		case _NONE:
			break;
		case STRING:
			value = cell.getStringCellValue();
			break;
		case NUMERIC:
			if(DateUtil.isCellDateFormatted(cell)){ //日期
				value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//统一转成 yyyy/MM/dd
			} else if("@".equals(cell.getCellStyle().getDataFormatString())
					|| "General".equals(cell.getCellStyle().getDataFormatString()) 
					|| "0_ ".equals(cell.getCellStyle().getDataFormatString())){
				//文本  or 常规 or 整型数值
				value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
			} else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型	
				value = cell.getNumericCellValue();  //直接显示
			} else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
				value = cell.getNumericCellValue();	//待完善		 	
				value = DECIMAL_FORMAT_NUMBER.format(value);
			} else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比						
				value = cell.getNumericCellValue(); //待完善
				value = DECIMAL_FORMAT_PERCENT.format(value);
			} else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
				value = cell.getNumericCellValue(); 待完善
			} else { //货币		
				value = cell.getNumericCellValue();
				value = DecimalFormat.getCurrencyInstance().format(value);
			}
			break;
		case BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case BLANK:
			//value = ",";
			break;
		default:
			value = cell.toString();
		}
		return value;
	}
}

控制层调用

	@RequestMapping(value = "/fileUpload",method = RequestMethod.POST,
    consumes = "application/json",produces = "application/json")
	public Map<String, Object> fileUpload(HttpServletRequest request,@RequestParam("file") MultipartFile file) {
		Map<String, Object> map = new HashMap<String, Object>();
		// 判断文件是否为空
		if (!StringUtils.isEmpty(file)) {
			try {
				List<RefPartExcel> excelBeans = ExcelUtil .readExcel(request,RefPartExcel.class);
				System.out.println(excelBeans.size());
				for(RefPartExcel ep : excelBeans){
					System.out.println(ep.toString());
				}
				//........逻辑
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return map;
	}

读取成功之后遍历的结果

RefPartExcel [partNo=3739472432, partName=上海, refPartNo=50000001, refPartName=前轮驱动轴螺母1, length=8, width=12]
RefPartExcel [partNo=3739472433, partName=湖北, refPartNo=50000002, refPartName=前轮驱动轴螺母2, length=9, width=13]
RefPartExcel [partNo=3739472434, partName=陕西, refPartNo=50000003, refPartName=前轮驱动轴螺母3, length=10, width=14]
RefPartExcel [partNo=3739472435, partName=河南, refPartNo=50000004, refPartName=前轮驱动轴螺母4, length=11, width=15]
RefPartExcel [partNo=3739472436, partName=湖南, refPartNo=50000005, refPartName=前轮驱动轴螺母5, length=12, width=16]

PS：当然了，如果模板的Excel之中文本或者常规有小数，百分比之类的，还是先规范下模板吧。。。

二、导出

导出可选xls或者xlsx格式，逻辑和样式都比较简单，并没有选择模板

package com.phil.excel.model;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.util.StringUtils;

/**
 * 功能说明：导出模板(待重构)
 * 典型用法：无 
 * 特殊用法：无 
 * 创建者：phil
 * 创建时间： 2017年10月13日 
 * 修改人：phil 
 * 修改时间：2017年10月18日
 * 修改原因： 升级poi3.17 重写 修改内容： 版本号：2.0
 */
public class ExportExcel<T> {

	/**
	 * 
	 * @param sheetTitle
	 *            sheet名称
	 * @param headers
	 *            列表标题
	 * @param dataset
	 *            内容
	 * @param out
	 */
	// public void exportExcel(String sheetTitle, String[] headers, String[]
	// columns, Collection<T> dataset,
	// OutputStream out, String datePattern) {
	// exportExcelByColumn(sheetTitle, headers, columns, dataset, out, datePattern);
	// }

	/**
	 * 导出 xls格式Excel HSSF
	 * @param title
	 * @param headers
	 * @param columns
	 * @param dataset
	 * @param out
	 * @param pattern
	 */
	public void exportHSExcelByColumn(String title, String[] headers, String[] columns, Collection<T> dataset,
			OutputStream out, String pattern) {
		Workbook workbook = new SXSSFWorkbook();
		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth(20);
		sheet.setDefaultRowHeightInPoints(24);
		// 生成一个 表格标题行样式
		CellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(IndexedColors.WHITE.getIndex());
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		// font.setBoldweight((short)700));
		// 把字体应用到当前的样式
		style.setFont(font);

		// 生成并设置另一个样式 内容的背景
		CellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);
		style2.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		Font font2 = workbook.createFont();
		font.setBold(true);
		// font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);

		// 声明一个画图的顶级管理器
		Drawing<?> patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置
		 Comment comment = patriarch.createCellComment(new HSSFClientAnchor(0, 0, 0,
		 0, (short)4, 2, (short)6, 5));
		// 设置注释内容
		 comment.setString(new HSSFRichTextString("Created By Phil"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		 comment.setAuthor("phil");

		// 产生表格标题行
		Row row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			RichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		
		if(StringUtils.isEmpty(pattern)) {
			pattern = "yyyy/MM/dd";
		}
		FastDateFormat instance = FastDateFormat.getInstance(pattern);
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		int count = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			// Field[] fields = t.getClass().getDeclaredFields();
			count = headers.length < columns.length ? headers.length : columns.length;
			for (int i = 0; i < count; i++) {
				Cell cell = row.createCell(i);
				cell.setCellStyle(style2);
				String fieldName = columns[i];
				String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
				try {
					Class<? extends Object> tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value instanceof Date) {
						Date date = (Date) value;
						textValue = instance.format(date);
					} else if (value instanceof byte[]) {
						// 有图片时，设置行高为60px;
						row.setHeightInPoints(60);
						// 设置图片所在列宽度为80px,注意这里单位的一个换算
						sheet.setColumnWidth(i, (short) (35.7 * 80));
						// sheet.autoSizeColumn(i);
						byte[] bsValue = (byte[]) value;
						ClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
						anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
						patriarch.createPicture(anchor, workbook.addPicture(bsValue, SXSSFWorkbook.PICTURE_TYPE_JPEG));
					} else {
						// 其它数据类型都当作字符串简单处理
//						if (value != null) {
//							textValue = value.toString();
//							if (textValue.equalsIgnoreCase("VLD")) {
//								textValue = "有效";
//							} else if (textValue.equalsIgnoreCase("IVD")) {
//								textValue = "无效";
//							}
//						} else {
//							textValue = "";
//						}
					}
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							RichTextString richString = new HSSFRichTextString(textValue);
							Font font3 = workbook.createFont();
							font3.setColor(IndexedColors.BLACK.index); // 内容
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} 
			}
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(workbook);
			IOUtils.closeQuietly(out);
		}
	}

	/**
	 * 导出 xlsx格式Excel XSSF
	 * @param title
	 * @param headers
	 * @param columns
	 * @param dataset
	 * @param out
	 * @param pattern
	 */
	public void exportXSExcelByColumn(String title, String[] headers, String[] columns,
			Collection<Map<String, Object>> dataset, OutputStream out, String pattern) {
		Workbook workbook = new SXSSFWorkbook();
		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth(20);
		sheet.setDefaultRowHeightInPoints(24);
		// 生成一个 表格标题行样式
		CellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(IndexedColors.WHITE.getIndex());
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		// font.setBoldweight((short)700));
		// 把字体应用到当前的样式
		style.setFont(font);

		// 生成并设置另一个样式 内容的背景
		CellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);
		style2.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		Font font2 = workbook.createFont();
		font.setBold(true);
		// font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);

		// 声明一个画图的顶级管理器
		Drawing<?> patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置
		 Comment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0,
		 0, (short)4, 2, (short)6, 5));
		 //设置注释内容
		 comment.setString(new XSSFRichTextString("Created By Phil"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		 comment.setAuthor("phil");

		// 产生表格标题行
		Row row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			RichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		if(StringUtils.isEmpty(pattern)) {
			pattern = "yyyy/MM/dd";
		}
		FastDateFormat instance = FastDateFormat.getInstance(pattern);
		// 遍历集合数据，产生数据行
		Iterator<Map<String, Object>> it = dataset.iterator(); // 多个Map集合
		int index = 0;
		int count = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			Map<String, Object> map = it.next();
			count = headers.length < columns.length ? headers.length : columns.length;
			for (int i = 0; i < count; i++) {
				Cell cell = row.createCell(i);
				cell.setCellStyle(style2);
				try {
					Object value = map.get(columns[i]);
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value instanceof Date) {
						Date date = (Date) value;
						textValue = instance.format(date);
					} else if (value instanceof byte[]) {
						// 有图片时，设置行高为60px;
						row.setHeightInPoints(60);
						// 设置图片所在列宽度为80px,注意这里单位的一个换算
						sheet.setColumnWidth(i, (short) (35.7 * 80));
						// sheet.autoSizeColumn(i);
						byte[] bsValue = (byte[]) value;
						ClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
						anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
						patriarch.createPicture(anchor, workbook.addPicture(bsValue, Workbook.PICTURE_TYPE_JPEG));
					} else {
						// 其它数据类型都当作字符串简单处理
						if (value != null) {
							textValue = value.toString();
							// if (textValue.equalsIgnoreCase("VLD")) {
							// textValue = "有效";
							// } else if (textValue.equalsIgnoreCase("IVD")) {
							// textValue = "无效";
							// }
						} else {
							textValue = "";
						}
					}
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							RichTextString richString = new XSSFRichTextString(textValue);
							Font font3 = workbook.createFont();
							font3.setColor(IndexedColors.BLACK.index); // 内容
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
				} catch (SecurityException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(workbook);
			IOUtils.closeQuietly(out);
		}
	}
}

控制层调用示例

package com.phil.download;

import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.phil.excel.model.ExportExcel;
import com.phil.service.QuestionService;

@Controller
@RequestMapping("api/download")
public class DownloadController {
	
	private static String EXPORT_XLSX_FILE_SUFFIX = ".xlsx";
	
//	private static String EXPORT_XLS_FILE_SUFFIX = ".xls";
	
	@Autowired	
	private QuestionService questionService;
	
	@GetMapping("export")
	public void export(Map<String, Object> map ,HttpServletResponse response) {
		List<Map<String, Object>> list = questionService.findByPage(new HashMap<>());
		for(int i = 0; i < 100000; i++) { //数据库为空,遍历了100000个
			Map<String, Object> temp_ = new HashMap<>();
			temp_.put("id", i + 1);
			temp_.put("number", i + 1);
			temp_.put("description", (i + 1) + "描述");
			list.add(temp_);
		}
		ExportExcel<List<Map<String, Object>>> exportExcel = new ExportExcel<>();
		StringBuffer filename = new StringBuffer();
		filename.append("导出");
		filename.append(System.currentTimeMillis());
		if(StringUtils.isEmpty(map.get("excel_type"))) {
			filename.append(EXPORT_XLSX_FILE_SUFFIX);
		} else {
			filename.append(map.get("excel_type"));
		}
		OutputStream out = null;
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.toString().getBytes("UTF-8"), "ISO8859-1"));
			out = response.getOutputStream();
			exportExcel.exportXSExcelByColumn("Title", new String[] {"id", "number", "description"}, new String[] {"id", "number", "description"}, 
					list, out ,null);
		} catch (IOException e) {		
		}
	}
}