首先引入pom文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
配置excel工具类
package com.dmsd.omo.tools;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* description:将list导出excel
*
* @author
* @date
*/
public class ListToExcel{
public static HSSFWorkbook getExcel(String title, List<String> list, int colNums) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(title);
HSSFRow row;
HSSFCell cell;
//标题
HSSFCellStyle styleTitle = wb.createCellStyle();
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont fontTitle = wb.createFont();
fontTitle.setFontHeightInPoints((short) 20);
styleTitle.setFont(fontTitle);
//表头
HSSFCellStyle styleHead = wb.createCellStyle();
HSSFFont fontHead = wb.createFont();
fontHead.setFontHeightInPoints((short) 11);
styleHead.setFont(fontHead);
//表格
HSSFCellStyle styleBody = wb.createCellStyle();
HSSFFont fontBody = wb.createFont();
fontBody.setFontHeightInPoints((short) 10);
styleBody.setFont(fontBody);
//尾注
HSSFCellStyle styleFoot = wb.createCellStyle();
HSSFFont fontFoot = wb.createFont();
fontFoot.setFontHeightInPoints((short) 12);
fontFoot.setColor(HSSFColor.DARK_GREEN.index);
styleFoot.setFont(fontFoot);
styleFoot.setFillForegroundColor(HSSFColor.YELLOW.index);
//设置标题行
row = sheet.createRow(0);
cell = row.createCell(0);
//行高
row.setHeightInPoints((float) (10.75 * 3));
//内容
cell.setCellValue(title);
//样式
cell.setCellStyle(styleTitle);
// 合并单元格 (始行,终行,始列,终列)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNums - 1));
//设置表头
row = sheet.createRow(1);
//行高
row.setHeightInPoints(15);
//内容
String str = list.get(0);
String[] ary = str.split(",");
for (int j = 0; j < ary.length; j++) {
cell = row.createCell(j);
cell.setCellValue(ary[j]);
cell.setCellStyle(styleHead);
}
//设置表格内容
for (int i = 2; i <= list.size(); i++) {
//序号列
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(i - 1);
cell.setCellStyle(styleBody);
//内容列
str = list.get(i - 1);
ary = str.split(",");
for (int j = 1; j <= ary.length; j++) {
cell = row.createCell(j);
cell.setCellValue(ary[j - 1]);
cell.setCellStyle(styleBody);
}
}
//设置脚注
int n = sheet.getLastRowNum();
row = sheet.createRow(++n);
row.setHeightInPoints((float) (12.75 * 2));
cell = row.createCell(0);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue("数据生成时间:" + sdf.format(new Date()));
cell.setCellStyle(styleFoot);
sheet.addMergedRegion(new CellRangeAddress(n, n, 0, colNums - 1));
// 自动调整列宽
for (int k = 0; k < colNums; k++) {
sheet.autoSizeColumn((short) k, true);
}
//手动设置列宽
//sheet.setColumnWidth(列号,宽度);
FileOutputStream fileOut=new FileOutputStream("D://成员信息表.xls");
wb.write(fileOut);
fileOut.close();
return wb;
}
//通过浏览器下载
public static void responseExcel(HSSFWorkbook wb, String fileName, HttpServletResponse response) {
OutputStream out = null;
try {
out = response.getOutputStream();
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
wb.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
进行调用
String title = "标题";
int cols = 列数;
List<String> list = new ArrayList<>();
list.add(0, "列1,列2,列3...");
list.add(要导出的数据);
title = URLDecoder.decode(title, "UTF-8");
HSSFWorkbook wb = listToExcel.getExcel(title, list, cols);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String fileName = sdf.format(new Date()) + ".xls";
listToExcel.responseExcel(wb, fileName, response);