1 自定义注解
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTable {
String value() default "";
}2 使用在字段上
/**
* 使用举例
*/
public class User {
//姓名
@ExcelTable("姓名")
private String name;
//性别
@ExcelTable("性别")
private String sex;
//年龄
@ExcelTable("年龄")
private String age;
}3 导出excel表的工具类
/**
* Excel导出工具类
*
* @author ellen
* @version 1.0
* @date 2018/11/11
*/
public class ExcelUtil {
/**
* @param list
* @param sheetName
* @param response
* @param <T>
* @throws Exception
* @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值）
*/
public static <T> void listToExcel(List<T> list, String sheetName, HttpServletResponse response) throws Exception {
listToExcel(list, sheetName, 65535, response);
}
/**
* @param list
* @param sheetName
* @param response
* @param <T>
* @throws Exception
* @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小）
*/
public static <T> void listToExcel(List<T> list, String sheetName, int sheetSize, HttpServletResponse response) throws Exception {
//设置默认文件名为当前时间：年月日时分秒
String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();
//设置response头信息
response.reset();
//改成输出excel文件
response.setContentType("application/vnd.ms-excel");
//设置文件下载头
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
try {
OutputStream out = response.getOutputStream();
listToExcel(list, sheetSize, sheetName, out);
} catch (Exception e) {
e.printStackTrace();
//如果是ExcelException，则直接抛出
if (e instanceof CustomException) {
throw (CustomException) e;
//否则将其它异常包装成ExcelException再抛出
} else {
throw new CustomException("导出Excel失败");
}
}
}
/**
* 一次导出全部
*
* @param list
* @param sheetSize 每个工作表记录的最大数值
* @param sheetName 每个工作表的名字
* @param out 导出流
* @param <T>
* @throws Exception
* @Description : 导出Excel（可以导出到本地文件系统,可自定义工作表大小）
*/
public static <T> void listToExcel(List<T> list, int sheetSize, String sheetName, OutputStream out) throws Exception {
if (list.size() == 0 || list == null) {
throw new CustomException("数据源中没有任何数据");
}
//最大数值为65535
if (sheetSize > 65535 || sheetSize < 1) {
sheetSize = 65535;
}
//新建一个xls工作簿
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = null;
//如果数值太多，放到多个工作表中
//计算一共有多少个工作表
double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue());
//多个工作表的情况
for (int i = 0; i < sheetNum; i++) {
if (sheetNum == 1) {
//新建一个表
sheet = wb.createSheet(sheetName);//表的名字是Excel不得超过31个字符
fillSheet(list, sheet, 0, list.size() - 1);
} else {
//获取开始索引和结束索引
int firstIndex = i * sheetSize;
int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * sheetSize - 1;
sheet = wb.createSheet(sheetName + (i + 1));
fillSheet(list, sheet, firstIndex, lastIndex);
}
}
// Write the output to a file
wb.write(out);
}
/**
* 填充数据
*
* @param list
* @param sheet 工作表插入
* @param firstIndex 开始索引
* @param lastIndex 结束索引
* @param <T>
* @throws Exception
*/
private static <T> void fillSheet(List<T> list, Sheet sheet, int firstIndex, int lastIndex) throws Exception {
Field[] fields = list.get(0).getClass().getDeclaredFields();
//记录行数
int rowNum = 0;
//记录列数
int cellNum = 0;
Row row = sheet.createRow(0);
row.setHeightInPoints(30);
rowNum++;
List<String> keylist = new ArrayList<>();
for (Field field : fields) {
Cell cell = row.createCell(cellNum);
if (field.isAnnotationPresent(ExcelTable.class)) {
ExcelTable excelRowName = field.getAnnotation(ExcelTable.class);
cell.setCellValue(excelRowName.value());
//保存到list
keylist.add(field.getName());
cellNum++;
}
}
for (int index = firstIndex; index <= lastIndex; index++) {
T object = list.get(index);
Row row1 = sheet.createRow(rowNum);
rowNum++;
cellNum = 0;
for (String string : keylist) {
Field field = object.getClass().getDeclaredField(string);
//设为可见
field.setAccessible(true);
Object value = field.get(object);
Cell cell = row1.createCell(cellNum);
//不需要做处理
if (value != null) {
cell.setCellValue(value.toString());
}
cellNum++;
}
}
}
/**
* 分工作表导出
*
* @param list
* @param sheetSize 每个工作表的行数（不得超过65535）
* @param sheetName 工作表的名称
* @param <T>
* @return
* @throws Exception
*/
public static <T> Workbook CreateExcelWorkbook(List<T> list, int sheetSize, String sheetName) throws Exception {
if (list.size() == 0 || list == null) {
throw new CustomException("数据源中没有任何数据");
}
//最大数值为65535
if (sheetSize > 65535 || sheetSize < 1) {
throw new CustomException("sheetSize数值有误");
}
//新建一个xls工作簿
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = null;
//新建一个表
sheet = wb.createSheet(sheetName);//表的名字是Excel不得超过31个字符
fillSheet(list, sheet, 0, list.size() - 1);
return wb;
}
/**
* 添加数据
*
* @param list
* @param sheetSize
* @param sheetName
* @param FilePath
* @param <T>
* @throws Exception
*/
public static <T> void addListToExcel(List<T> list, int sheetSize, String sheetName, String FilePath) throws Exception {
if (list.size() == 0 || list == null) {
throw new CustomException("数据源中没有任何数据");
}
// 判断数据大小是否比最后一个表剩余的大小小，小的话可以直接在表中继续写，大的话在当前表中写完后继续在后面表中写
//获得一个xls工作簿
Workbook wb = returnWorkBookByFileName(FilePath);
CreationHelper createHelper = wb.getCreationHelper();
//获得最后一个工作表
Sheet sheet = wb.getSheetAt(wb.getNumberOfSheets() - 1);
//记录行数
int rowNum = sheet.getLastRowNum();
double sheetNum = 0;
if (sheetSize > (65535 - sheet.getLastRowNum())) {
// 判断当前表填满后还需要多少个表
sheetNum = Math.ceil((list.size() - (65535 - sheet.getLastRowNum())) / new Integer(65535).doubleValue());
}
// 如果在当前表中可以能容纳list大小的数据
if (sheetNum == 0) {
ContinueToSheet(list, sheet, rowNum, 0, list.size() - 1);
} else {
int i = 0;
// 将当前表填充完整
ContinueToSheet(list, sheet, rowNum, 0, 65535 - sheet.getLastRowNum());
i = 65535 - sheet.getLastRowNum();
// 继续将新建表填充
for (int index = 0; index < sheetNum; index++) {
sheet = wb.createSheet(sheetName + (i + 1));
ContinueToSheet(list, sheet, rowNum, i + 1, i + 65535);
i += 65535;
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(FilePath);
wb.write(fileOut);
fileOut.close();
}
/**
* @param list
* @param sheet 工作表插入
* @param beginIndex 开始索引
* @param endIndex 结束索引
* @param <T>
* @throws Exception
*/
private static <T> void ContinueToSheet(List<T> list, Sheet sheet, int rowNum, int beginIndex, int endIndex) throws Exception {
Field[] fields = list.get(0).getClass().getDeclaredFields();
// 获得最后一行
Row row = sheet.getRow(rowNum);
//记录列数
int cellNum = 0;
// 列数
List<String> keylist = null;
// row.setHeightInPoints(30);
keylist = new ArrayList<>();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelTable.class)) {
ExcelTable excelRowName = field.getAnnotation(ExcelTable.class);
//保存到list
keylist.add(field.getName());
cellNum++;
}
}
for (int index = beginIndex; index <= endIndex; index++) {
T object = list.get(index);
Row row1 = sheet.createRow(rowNum);
rowNum++;
cellNum = 0;
for (String string : keylist) {
Field field = object.getClass().getDeclaredField(string);
//设为可见
field.setAccessible(true);
Object value = field.get(object);
Cell cell = row1.createCell(cellNum);
if (value == null) {
cell.setCellValue(" ");
} else {
//不需要做处理
if (value != null) {
cell.setCellValue(value.toString());
}
}
cellNum++;
}
}
}
/**
* 通过文件输入流得到一个工作蒲
*
* @param is
* @return
* @throws IOException
*/
private static HSSFWorkbook returnWorkBookByFileStream(InputStream is) throws IOException {
HSSFWorkbook wb = null;
wb = new HSSFWorkbook(is);
return wb;
}
/**
* 通过文件路径得到一个工作蒲
*
* @param FilePath
* @return
* @throws IOException
*/
private static HSSFWorkbook returnWorkBookByFileName(String FilePath) throws IOException {
InputStream inp = new FileInputStream(FilePath);
return returnWorkBookByFileStream(inp);
}
/**
* @param list
* @param sheetName
* @param <T>
* @throws Exception
* @Description : 导出Excel（可以导出到本地文件系统,工作表大小为2003支持的最大值）
*/
public static <T> void listToExcel(List<T> list, String sheetName, OutputStream outputStream) throws Exception {
listToExcel(list, 65535, sheetName, outputStream);
}
/**
* @param list
* @param sheetSize
* @param sheetName
* @param FilePath
* @param <T>
* @throws Exception
*/
public static <T> void addListToExcel(List<T> list, int sheetSize, String sheetName, String FilePath, OutputStream out) throws Exception {
if (list.size() == 0 || list == null) {
throw new CustomException("数据源中没有任何数据");
}
//最大数值为65535
if (sheetSize > 65535 || sheetSize < 1) {
sheetSize = 65535;
}
//获得一个xls工作簿
Workbook wb = returnWorkBookByFileName(FilePath);
CreationHelper createHelper = wb.getCreationHelper();
//获得最后一个工作表
Sheet sheet = wb.getSheetAt(wb.getNumberOfSheets() - 1);
//判断当前添加的表
if (sheetSize > (65535 - sheet.getLastRowNum())) {
}
double sheetNum = Math.ceil((list.size() + sheet.getLastRowNum()) / new Integer(sheetSize).doubleValue());
//多个工作表的情况
for (int i = 0; i < sheetNum; i++) {
if (sheetNum == 1) {
//新建一个表
// sheet = wb.createSheet(sheetName);//表的名字是Excel不得超过31个字符
addSheet(list, sheet, 0, list.size() - 1);
} else {
//获取开始索引和结束索引
int firstIndex = i * sheetSize;
int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * sheetSize - 1;
sheet = wb.createSheet(sheetName + (i + 1));
fillSheet(list, sheet, firstIndex, lastIndex);
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(FilePath);
wb.write(fileOut);
fileOut.close();
}
/**
* @param list
* @param sheet 工作表插入
* @param firstIndex 开始索引
* @param lastIndex 结束索引
* @param <T>
* @throws Exception
*/
private static <T> void addSheet(List<T> list, Sheet sheet, int firstIndex, int lastIndex) throws Exception {
Field[] fields = list.get(0).getClass().getDeclaredFields();
//记录行数
int rowNum = sheet.getLastRowNum();
//获得最后一行
Row row = sheet.getRow(rowNum);
//记录列数
int cellNum = 0;
// row.setHeightInPoints(30);
rowNum++;
List<String> keylist = new ArrayList<>();
for (Field field : fields) {
Cell cell = row.createCell(cellNum);
if (field.isAnnotationPresent(ExcelTable.class)) {
ExcelTable excelRowName = field.getAnnotation(ExcelTable.class);
cell.setCellValue(excelRowName.value());
//保存到list
keylist.add(field.getName());
cellNum++;
}
}
for (int index = firstIndex; index <= lastIndex; index++) {
T object = list.get(index);
Row row1 = sheet.createRow(rowNum);
rowNum++;
cellNum = 0;
for (String string : keylist) {
Field field = object.getClass().getDeclaredField(string);
//设为可见
field.setAccessible(true);
Object value = field.get(object);
Cell cell = row1.createCell(cellNum);
if (value == null) {
cell.setCellValue(" ");
} else {
cell.setCellValue(value.toString());
}
cellNum++;
}
}
}
public static Workbook addDate(List<T> list, int sheetSize, String sheetName, Workbook wb) {
if (list.size() == 0 || list == null) {
throw new CustomException("数据源中没有任何数据");
}
//最大数值为65535
if (sheetSize > 65535 || sheetSize < 1) {
sheetSize = 65535;
}
CreationHelper createHelper = wb.getCreationHelper();
//获得最后一个工作表
Sheet sheet = wb.getSheetAt(wb.getNumberOfSheets() - 1);
//如果数值太多，放到多个工作表中
//计算一共有多少个工作表
double sheetNum = Math.ceil(list.size() + sheet.getLastRowNum() / new Integer(sheetSize).doubleValue());
//多个工作表的情况
for (int i = 0; i < sheetNum; i++) {
// if(sheetNum == 1){
// //新建一个表
// // sheet = wb.createSheet(sheetName);//表的名字是Excel不得超过31个字符
// addSheet(list,sheet,0,list.size()-1);
// }
// else {
// //获取开始索引和结束索引
// int firstIndex=i * sheetSize;
// int lastIndex=(i+1)*sheetSize-1>list.size()-1 ? list.size()-1 : (i+1)*sheetSize-1;
//
// sheet = wb.createSheet(sheetName + (i+1));
// fillSheet(list,sheet,firstIndex,lastIndex);
// }
//
//
// }
//
// // Write the output to a file
// FileOutputStream fileOut = new FileOutputStream(FilePath);
// wb.write(fileOut);
// fileOut.close();
}
return wb;
}
}4 简单使用实例
@Test
public void testExcel() throws Exception {
List<User> list = new ArrayList<User>();
list.add(new User("zhangsan", "男", 11));
list.add(new User("lisi", "男", 12));
list.add(new User("wangwu", "男", 13));
//本地导出
FileOutputStream outputStream = new FileOutputStream("d:\\测试excel导出.xlsx");
ExcelUtil.listToExcel(list, 65535, "user", outputStream);
//浏览器导出
ExcelUtil.listToExcel(list, "user", httpServletResponse);
}5 本地导出效果（浏览器导出参考实例）
附：maven添加poi依赖
<!-- poi依赖，excel表导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
