使用POI根据SQL自动导出Excel
程序员文章站
2022-07-13 12:42:03
...
继承AbstractExcelView方法,对buildExcelDocument方法进行重构
底层【ExcelView】
public class ExcelView extends AbstractExcelView {
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
List<PageData> mt = (List<PageData>) model.get("dataSet");
HSSFSheet sheet = workbook.createSheet(model.get("tableName").toString());
//处理Excel表头字体样式(Cell)
HSSFFont fontCell =workbook.createFont();
fontCell.setFontHeightInPoints((short) 10); //字体高度
fontCell.setColor(HSSFFont.COLOR_RED); //字体颜色
fontCell.setFontName("微软雅黑"); //字体
fontCell.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
//处理Excel表头样式(Cell)
HSSFCellStyle cellStyle =workbook.createCellStyle();
cellStyle.setFont(fontCell);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局:居中
cellStyle.setWrapText(true);
//处理Excel内容字体样式(Row)
HSSFFont fontRow =workbook.createFont();
fontRow.setFontHeightInPoints((short) 10); //字体高度
fontRow.setFontName("微软雅黑"); //字体
//处理Excel表头样式(Row)
HSSFCellStyle rowStyle =workbook.createCellStyle();
rowStyle.setFont(fontRow);
rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局:居中
rowStyle.setWrapText(true);
//处理Excel表头字体样式(无数据导出)
HSSFFont fontNull =workbook.createFont();
fontNull.setFontHeightInPoints((short) 16); //字体高度
fontNull.setColor(HSSFFont.COLOR_RED); //字体颜色
fontNull.setFontName("微软雅黑"); //字体
fontNull.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
//处理Excel表头样式(无数据导出)
HSSFCellStyle cellNull =workbook.createCellStyle();
cellNull.setFont(fontNull);
cellNull.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局:居中
cellNull.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直
cellNull.setWrapText(true);
ExcelViewConfig cfg = new ExcelViewConfig();
//判断是否有内容
if(mt.size() != 0) {
//获取字段列表(需和mapper中保持一致,若用到了as字段,则填写as字段的值即可,支持中文)
String [] cellName = model.get("cellName").toString().split(",");
HSSFCell cell;
sheet.setDefaultColumnWidth(cellName.length);
for (int i = 0; i <= cellName.length; i++) {
cell = getCell(sheet, 0, i);
if (i == cellName.length) {
setText(cell, "");
} else {
setText(cell, cfg.parseCellName(cellName[i].trim()));
}
cell.setCellStyle(cellStyle);
}
for (int i = 0; i < mt.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
PageData pd = mt.get(i);
//循环CellName获取值
for (int j = 0; j < cellName.length; j++) {
cell = row.createCell(j);
if (j < cellName.length) {
Object o = pd.get(cellName[j].trim());
cell.setCellValue(o==null?"":cfg.parseCellValue(cellName[j],o.toString()));
} else {
row.createCell(j).setCellValue("");
}
cell.setCellStyle(rowStyle);
}
}
for (int i = 0; i <= cellName.length; i++) {
// ==**==本方法在linux不适用
//sheet.autoSizeColumn((short) i); //调整列宽度
sheet.setColumnWidth((short) i,cellName.toString().getBytes().length*2*256);
}
}else{
HSSFCell cell = getCell(sheet,0,0);
setText(cell, "Sorry,It's no true Data!(很抱歉,没有符合的数据!)");
cell.setCellStyle(cellNull);
sheet.setColumnWidth(0,2000);
sheet.addMergedRegion(new CellRangeAddress(0,10,0,20));
}
// 处理中文文件名Name
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "filename="
+ new String(cfg.parseTableName(model.get("tableName").toString()).getBytes("gb2312"), "iso8859-1"));//设置文件头编码方式和文件名
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}
ExcelView配置类(ExcelViewConfig)
/**
* 配置类 作为导出类的配置信息存放
*/
@Component
public class ExcelViewConfig {
public static ExcelViewConfig excelViewConfig;
@PostConstruct
public void init(){
excelViewConfig = this;
}
/**
* 将列名转换成列表表头
* @param enCellName 英文的列名
* @return
*/
String parseCellName(String enCellName){
String cnCellName = enCellName;
//根据需求自行设置即可
if(enCellName.equals("id")){cnCellName = "编号";}
if(enCellName.equals("customer_name")){cnCellName = "客户名称";}
return cnCellName;
}
/**
* 字段特殊值转化
* @param enCellName 列名
* @param enCellValue 值
* @return 处理结果
*/
String parseCellValue(String enCellName,String enCellValue) throws Exception {
String cnCellValue = enCellValue;
//根据需求自行设置即可
if(enCellName.equals("customer_sex")
||enCellName.equals("性别")){
cnCellValue = cnCellValue.equals("0")?"女":"男";
}
return cnCellValue;
}
/**
* 将表名转换成Excel导出文件名
* @param TableName 表名
* @return
*/
String parseTableName(String TableName){
return TableName+"("+sdf.format(new Date())+").xls";
}
}
使用【Controller方法】
//可以把ModelAndView替换成Void 这里是我的个人习惯写成ModelAndView
public ModelAndView toCustomerExExcel(String customerName){
//Tool接口
return excelImportTool.importExcel( Data,
"id,customer_name,customer_age,customer_birthday,customer_telphone" ,
"用户多维信息导出");
}
Tool接口
/**
* Import Excel Tool
* Excel灵活导出工具类
* @param pageData 需要导出的实体集合
* @param cellName 字段值[Excel表头-支持中文]
* @param tableName Excel文件名称
* @return 导出数据流[input Stream]
*/
ModelAndView importExcel(List<PageData> pageData,
String cellName,
String tableName);
Tool接口实现
/**
* Excel导出控件
*/
@Service("excelImportTool")
public class ExcelImportToolImpl implements ExcelImportTool {
@Override
public ModelAndView importExcel(List<PageData> pageData, String cellName, String tableName)
{
List<PageData> PageDataList = new ArrayList<PageData>();
pageData.forEach((PageData) -> PageDataList.add(PageData));
ModelMap model = new ModelMap();
ExcelView v = new ExcelView();
model.put("dataSet", PageDataList);
model.put("cellName",cellName);
model.put("tableName",tableName);
return new ModelAndView(v, model);
}
}
如此一来,就省去了大部分去重写ExcelView的时间,可喜可贺可喜可贺~~
上一篇: POI模版导出excel