转载自
一、封装一个通用的装载数据的实体类：
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDataVo implements Serializable {
/**
* excel sheet的名称
*/
private String sheetName;
/**
* excel sheet表的标题
*/
private String sheetTopColHeaderTitle;
/**
* excel sheet表的列头名称
*/
private String[] sheetTopColHeaderName;
/**
* excel sheet表的列头属性
*/
private String[] sheetTopColHeaderAttribute;
/**
* excel sheet表的数据
*/
private List<Map<String, Object>> sheetDataList;
}
二、通用数据处理的工具类：
import com.yfh.common.core.domain.ExcelDataVo;
import com.yfh.common.core.exception.BaseException;
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class PackExcelSheetsDataUtil {
/*
* 封装需要导出的数据
* */
public void packExcelSheetsData(HttpServletResponse response, String excelName, List<ExcelDataVo> excelDataVoList) throws Exception{
try {
// 对excel进行判断
for(int j = 0; j < excelDataVoList.size(); j++){
String[] headerName = excelDataVoList.get(j).getSheetTopColHeaderName();
String[] headerAttribute = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
if(headerName.length != headerAttribute.length){
throw new BaseException("列头长度与属性长度不对应！");
}
}
// 创建一个excel对象
HSSFWorkbook workbook = new HSSFWorkbook();
OutputStream out = response.getOutputStream();
//定义标题以及设置响应头信息
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(excelName+".xls", "UTF-8"));
// 循环生成指定数量的sheet表
for(int j = 0; j < excelDataVoList.size(); j++){
// 获取单个sheet的数据
String sheetName = excelDataVoList.get(j).getSheetName();
String sheetTopColHeaderTitle = excelDataVoList.get(j).getSheetTopColHeaderTitle();
String[] headers = excelDataVoList.get(j).getSheetTopColHeaderName();
String[] headerCol = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
List<Map<String, Object>> sheetDataList = excelDataVoList.get(j).getSheetDataList();
// 申请一个最后的list集合并封装数据
List<Object[]> dataList = new ArrayList<Object[]>();
Object[] objs = null;
for(int i = 0; i < sheetDataList.size(); i++){
// 封装lie数据
objs = new Object[headers.length];
for (int k = 0; k < headers.length; k++) {
objs[k] = sheetDataList.get(i).get(headerCol[k]);
}
//数据添加到excel表格
dataList.add(objs);
}
//使用流将数据导出
new ExcelSheetsUtil(sheetName, sheetTopColHeaderTitle, headers, dataList).export(workbook);
}
// 最后将整个excel全部写到浏览器
workbook.write(out);
out.flush();
out.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
三、通用导出的工具类：
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
public class ExcelSheetsUtil {
//导出表的列名
private String[] rowName;
//导出表的头部标题
private String oneheaders;
//sheet表表名
private String sheettitle;
// 需要导出的数据集合
private List<Object[]> dataList;
/**
* @param sheettitle 单个sheet名称
* @param oneheaders 单个sheet头部名称
* @param rowName 单个sheet对应的列头
* @param dataList 单个sheet对应的数据
*/
public ExcelSheetsUtil(String sheettitle, String oneheaders, String[] rowName, List<Object[]> dataList){
this.dataList = dataList;
this.oneheaders = oneheaders;
this.rowName = rowName;
this.sheettitle = sheettitle;
}
/*
* 导出数据
* */
public void export(HSSFWorkbook workbook) throws Exception{
try{
// 创建工作表
HSSFSheet sheet = workbook.createSheet(sheettitle);
//获取列头样式对象
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
//单元格样式对象
HSSFCellStyle style = this.getStyle(workbook);
//第一行
HSSFRow rowfirstName = sheet.createRow(0);
//创建列头对应个数的单元格
HSSFCell oneCellRowName = rowfirstName.createCell(0);
//设置列头单元格的数据类型
oneCellRowName.setCellType(CellType.STRING);
HSSFRichTextString onetext = new HSSFRichTextString(oneheaders);
//设置列头单元格的值
oneCellRowName.setCellValue(onetext);
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0, rowName.length - 1));
//设置列头单元格样式
oneCellRowName.setCellStyle(columnTopStyle);
// 定义所需列数
int columnNum = rowName.length;
/*** 绑定列头 ****/
// 在索引2的位置创建行(最顶端的行开始的第二行)
HSSFRow rowRowName = sheet.createRow(1);
//单元格样式对象
HSSFCellStyle styleHearer = this.getStyle(workbook);
Font headerFont = workbook.createFont();
headerFont.setFontName("微软雅黑");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);
headerFont.setColor(IndexedColors.BLACK.getIndex());
styleHearer.setFont(headerFont);
for(int n = 0; n < columnNum; n++){
//创建列头对应个数的单元格
HSSFCell cellRowName = rowRowName.createCell(n);
//设置列头单元格的数据类型
cellRowName.setCellType(CellType.STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
//设置列头单元格的值
cellRowName.setCellValue(text);
//设置列头单元格样式
cellRowName.setCellStyle(styleHearer);
}
/*** 绑定数据 ****/
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);
//创建所需的行数（从第二行开始写数据）
HSSFRow row = sheet.createRow(i+2);
for(int j = 0; j < obj.length; j++){
//设置单元格的数据类型
HSSFCell cell = row.createCell(j, CellType.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(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == CellType.STRING) {
int length = 0;
try {
length = currentCell.getStringCellValue().getBytes().length;
} catch (Exception e) {
e.printStackTrace();
}
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
}catch(Exception e){
e.printStackTrace();
}
}
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("宋体");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体名字
font.setFontName("宋体");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
}
四、测试多表导出：
/**
* 测试多个导出sheet
*/
@ApiOperation(value = "测试多个导出sheet", notes = "测试多个导出sheet", httpMethod = "GET")
@RequestMapping(value = "/exportTeenagerMoreSheet", method = RequestMethod.GET)
public void exportTeenagerMoreSheet(HttpServletResponse response){
// 定义学生信息
List<Map<String, Object>> list1 = Lists.newArrayList();
for (int i = 0; i < 3; i++) {
Map<String, Object> map1 = Maps.newHashMap();
map1.put("name", "张三"+i);
map1.put("sex", "男"+i);
map1.put("age", "23"+i);
map1.put("isSchool", "是"+i);
list1.add(map1);
}
// 定义学校信息
List<Map<String, Object>> list2 = Lists.newArrayList();
for (int i = 0; i < 4; i++) {
Map<String, Object> map2 = Maps.newHashMap();
map2.put("name", "杭州天长小学"+i);
map2.put("adress", "浙江省杭州市"+i);
map2.put("concat", "王五"+i);
list2.add(map2);
}
// 定义其他信息
List<Map<String, Object>> list3 = Lists.newArrayList();
for (int i = 0; i < 10; i++) {
Map<String, Object> map3 = Maps.newHashMap();
map3.put("protity", "其他1"+i);
map3.put("remark", "我是备注"+i);
list3.add(map3);
}
// 封装需要导出的数据
List<ExcelDataVo> excelDataVoList = Lists.newArrayList();
// 封装学生信息
ExcelDataVo excelDataSheetOne = new ExcelDataVo();
excelDataSheetOne.setSheetName("学生信息");
excelDataSheetOne.setSheetTopColHeaderTitle("学生信息");
excelDataSheetOne.setSheetTopColHeaderName(new String[] {"姓名","性别","年龄", "学生"});
excelDataSheetOne.setSheetTopColHeaderAttribute(new String[] {"name","sex","age", "isSchool"});
excelDataSheetOne.setSheetDataList(list1);
excelDataVoList.add(excelDataSheetOne);
// 封装学校信息
ExcelDataVo excelDataSheetTwo = new ExcelDataVo();
excelDataSheetTwo.setSheetName("学校信息");
excelDataSheetTwo.setSheetTopColHeaderTitle("学校信息");
excelDataSheetTwo.setSheetTopColHeaderName(new String[] {"学校","地址","联系人"});
excelDataSheetTwo.setSheetTopColHeaderAttribute(new String[] {"name","adress","concat"});
excelDataSheetTwo.setSheetDataList(list2);
excelDataVoList.add(excelDataSheetTwo);
// 封装其他信息
ExcelDataVo excelDataSheetThree = new ExcelDataVo();
excelDataSheetThree.setSheetName("其他信息");
excelDataSheetThree.setSheetTopColHeaderTitle("其他信息");
excelDataSheetThree.setSheetTopColHeaderName(new String[] {"描述","备注"});
excelDataSheetThree.setSheetTopColHeaderAttribute(new String[] {"protity","remark"});
excelDataSheetThree.setSheetDataList(list3);
excelDataVoList.add(excelDataSheetThree);
// 导出数据
try {
new PackExcelSheetsDataUtil().packExcelSheetsData(response, "学生基础信息", excelDataVoList);
} catch (Exception e) {
throw new BaseException("导出异常!");
}
}
