以前只知道用poi导出Excel，最近用了SpringMvc的Excel导出功能，结合jxl和poi实现，的确比只用Poi好，两种实现方式如下：
一、结合jxl实现：
1、引入jxl的所需jar包：
<dependency org="net.sourceforge.jexcelapi" name="jxl" rev="2.6.3" conf="compile->compile(*),master(*);runtime->runtime(*)" transitive="false"/>
2、接口和实现类:
接口源码：
public interface ExportRS {
/**
* 息导出到Excel
* <P>
* <ul>
* </ul>
* </P>
*
* @return 导出文件流
*/
@RequestMapping(value="/export/excel" , method={RequestMethod.POST,RequestMethod.GET})
ModelAndView exportExcel(@RequestParam(value = "condition", required = true) String condition,
HttpServletRequest request, HttpServletResponse response);
}
实现源码：
@RestController
public class ExportRSImpl implements ExportRS {
@Override
public ModelAndView exportExcel(String condition,
HttpServletRequest request, HttpServletResponse response) {
try{
NSearch nSearch = JsonObjUtil.JsonToObj(condition, NSearch.class);
String userId = SecurityHelper.getCurrentUserId();
QueryResult result = _mupport.search(nSearch, userId);
long total = result.getTotalCount();
//System.out.println("资产总数是："+total);
Map<String, Object> model = new HashMap<String, Object>();
model.put("total", total+"");
model.put("filename", "资产信息-"+new SimpleDateFormat("yyyyMMddhhmmss").format(new Date())+".xls");
model.put("items", result.getItems());
//资产类型
List<AssetCategoryDefine> categoryList = _assetCatRp.findAllRestrict();
//所有用户
String stationId = SecurityHelper.getCurrentStationId();
List<User> userList = _rpUser.findByStationId(stationId);
model.put("allUser", userList);
model.put("allCategory", categoryList); return new ModelAndView(new JExcelView(),model);
} catch (Throwable e) {
throw new AssetRuntimeException(e);
}
}
}
ExportRSImpl.java
3、JExcelView的实现ModelAndView
public class JExcelView extends AbstractJExcelView{
private static Logger logger = LoggerFactory.getLogger(JExcelView.class);
private OutputStream os;
String[] tilte = new String[]{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
List<AssetCategoryDefine> categoryList = null;
List<User> userList = null;
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model,
WritableWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response){
os = null;
try {
categoryList = (List<AssetCategoryDefine>)model.get("allCategory");
userList = (List<User>)model.get("allUser");
String filename = (String)model.get("filename");
//String total = (String)model.get("total");
//响应信息，弹出文件下载窗口
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(filename, "UTF-8"));
os = response.getOutputStream();
//全局设置
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding("UTF-8");
settings.setLocale(new Locale("zh", "cn"));
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet("资产信息", 0);
String titleName = "资产信息";
//添加标题和文件列表头
addTitle(sheet,tilte,titleName);
//添加文件信息
List<MObject> list = (List<MObject>)model.get("items");
addContextByList(sheet,list,tilte.length);
} catch (Throwable e) {
logger.error("资产信息导出Excel出错："+e.getMessage());
}finally {
try {
workbook.write();
workbook.close();
os.flush();
os.close();
} catch (Throwable e) {
logger.error("资产信息导出Excel出错："+e.getMessage());
}
}
}
//添加标题和列头信息
public void addTitle(WritableSheet sheet, String[] assettitle, String total,String titleName) throws RowsExceededException, WriteException {
//添加标题第一行
Label label = new Label(2, 0, titleName+"(共"+total+"条)",getHeader());
sheet.addCell(label);
//添加第二行
for (int i = 0; i < tilte.length; i++) {
label = new Label(i, 1, tilte[i]);
sheet.addCell(label);
}
}
//添加标题和列头信息
public void addTitle(WritableSheet sheet, String[] assettitle,String titleName) throws RowsExceededException, WriteException {
//添加标题第一行
Label label = new Label(2, 0, titleName,getHeader());
sheet.addCell(label);
//添加第二行
for (int i = 0; i < tilte.length; i++) {
label = new Label(i, 1, tilte[i]);
sheet.addCell(label);
}
}
//添加所有文件信息 从第三行开始添加
//{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
@SuppressWarnings("unchecked")
private <T> void addContextByList(WritableSheet sheet, List<T> list, int length) throws RowsExceededException, WriteException {
if (list!=null) {
CellView cellView = new CellView();
cellView.setAutosize(true);
List<MObject> mObjects = (List<MObject>)list;
//int size = mObjects.size();
Label label = null;
MObject mObject = null;
String value = "";
for (int i = 0; i < list.size(); i++) {
for (int j = 0; j < length; j++) {
mObject = mObjects.get(i);
if(j==0){
label = new Label(j, i+2, i+"");
sheet.addCell(label);
}else{
value = getValue(mObject,j);
label = new Label(j, i+2, value+"");
sheet.addCell(label);
sheet.setColumnView(j, cellView);
}
}
}
}else{
Label label = new Label(0,3,"暂无数据");
sheet.addCell(label);
}
}
//根据列名获取值，先写死，之后再想其他办法
//{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
private String getValue(MObject mObject, int j) {
switch (j) {
case 1:
return mObject.getName();
case 2:
return getAssetCategory(objectToString(mObject.getCategoryId()),null);
case 3:
return objectToString(mObject.getCreatedTime());
case 4:
return getUserInfo(objectToString(mObject.getDeletedBy()),null);
case 5:
return objectToString(mObject.getDeletedTime());
case 6:
return objectToString(mObject.getExtraData().get("delHostName"));
default:
}
return null;
}
private String objectToString(Object object){
if(object==null){
return "";
}else{
if(object instanceof Date){
DateFormat from_type = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = (Date)object;
return from_type.format(date);
}else{
return (String)object;
}
}
}
//标题样式
public static WritableCellFormat getHeader(){
WritableFont font = new WritableFont(WritableFont.TIMES,15,WritableFont.BOLD);
WritableCellFormat format = new WritableCellFormat(font);
try {
format.setAlignment(Alignment.CENTRE);
format.setOrientation(Orientation.HORIZONTAL);
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return format;
}
//获取用户名称
/**
* id 用户id
* key:用于扩展属性的key值(若有)
* */
public String getUserInfo(String id,String key){
if(userList !=null&&userList.size() > 0 ){
for (User user : userList) {
if( user.getId().equals(id))
return user.getName();
}
}
return null;
}
//获取类型名称
/**
* id 类型id
* key:用于扩展属性的key值 (若有)
* */
public String getAssetCategory(String id,String key){
if(categoryList!=null && categoryList.size()>0){
for (AssetCategoryDefine assetCategoryDefine : categoryList) {
if(assetCategoryDefine.getId().equals(id))
return assetCategoryDefine.getName();
}
}
return null;
}
}
JExcelView.java
以上，代码编码完成，只用配置一下springMvc的正常配置足以。
关于JXL，SpringMvc 4.0已不再支持了,而且在实现的过程中，虽然功能可以实现，但是后台会报错。
/*
* Copyright 2002-2013 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/ package org.springframework.web.servlet.view.document; import java.io.OutputStream;
import java.util.Locale;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import jxl.Workbook;
import jxl.write.WritableWorkbook; import org.springframework.core.io.Resource;
import org.springframework.core.io.support.LocalizedResourceHelper;
import org.springframework.web.servlet.support.RequestContextUtils;
import org.springframework.web.servlet.view.AbstractView; /**
* Convenient superclass for Excel document views.
*
* <p>This class uses the <i>JExcelAPI</i> instead of <i>POI</i>.
* More information on <i>JExcelAPI</i> can be found on their
* <a href="http://www.andykhan.com/jexcelapi/" target="_blank">website</a>.
*
* <p>Properties:
* <ul>
* <li>url (optional): The url of an existing Excel document to pick as a
* starting point. It is done without localization part nor the .xls extension.
* </ul>
*
* <p>The file will be searched with locations in the following order:
* <ul>
* <li>[url]_[language]_[country].xls
* <li>[url]_[language].xls
* <li>[url].xls
* </ul>
*
* <p>For working with the workbook in the subclass, see <a
* href="http://www.andykhan.com/jexcelapi/">Java Excel API site</a>
*
* <p>As an example, you can try this snippet:
*
* <pre class="code">
* protected void buildExcelDocument(
* Map<String, Object> model, WritableWorkbook workbook,
* HttpServletRequest request, HttpServletResponse response) {
*
* if (workbook.getNumberOfSheets() == 0) {
* workbook.createSheet("Spring", 0);
* }
*
* WritableSheet sheet = workbook.getSheet("Spring");
* Label label = new Label(0, 0, "This is a nice label");
* sheet.addCell(label);
* }</pre>
*
* The use of this view is close to the {@link AbstractExcelView} class,
* just using the JExcel API instead of the Apache POI API.
*
* @author Bram Smeets
* @author Alef Arendsen
* @author Juergen Hoeller
* @since 1.2.5
* @see AbstractExcelView
* @see AbstractPdfView
* @deprecated as of Spring 4.0, since JExcelAPI is an abandoned project
* (no release since 2009, with serious bugs remaining)
*/
@Deprecated
public abstract class AbstractJExcelView extends AbstractView {
因为以上，所以以下：
/*
* Copyright 2002-2013 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/ package org.springframework.web.servlet.view.document; import java.util.Locale;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.springframework.core.io.Resource;
import org.springframework.core.io.support.LocalizedResourceHelper;
import org.springframework.web.servlet.support.RequestContextUtils;
import org.springframework.web.servlet.view.AbstractView; /**
* Convenient superclass for Excel document views.
* Compatible with Apache POI 3.5 and higher, as of Spring 4.0.
*
* <p>Properties:
* <ul>
* <li>url (optional): The url of an existing Excel document to pick as a starting point.
* It is done without localization part nor the ".xls" extension.
* </ul>
*
* <p>The file will be searched with locations in the following order:
* <ul>
* <li>[url]_[language]_[country].xls
* <li>[url]_[language].xls
* <li>[url].xls
* </ul>
*
* <p>For working with the workbook in the subclass, see
* <a href="http://jakarta.apache.org/poi/index.html">Jakarta's POI site</a>
*
* <p>As an example, you can try this snippet:
*
* <pre class="code">
* protected void buildExcelDocument(
* Map<String, Object> model, HSSFWorkbook workbook,
* HttpServletRequest request, HttpServletResponse response) {
*
* // Go to the first sheet.
* // getSheetAt: only if workbook is created from an existing document
* // HSSFSheet sheet = workbook.getSheetAt(0);
* HSSFSheet sheet = workbook.createSheet("Spring");
* sheet.setDefaultColumnWidth(12);
*
* // Write a text at A1.
* HSSFCell cell = getCell(sheet, 0, 0);
* setText(cell, "Spring POI test");
*
* // Write the current date at A2.
* HSSFCellStyle dateStyle = workbook.createCellStyle();
* dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
* cell = getCell(sheet, 1, 0);
* cell.setCellValue(new Date());
* cell.setCellStyle(dateStyle);
*
* // Write a number at A3
* getCell(sheet, 2, 0).setCellValue(458);
*
* // Write a range of numbers.
* HSSFRow sheetRow = sheet.createRow(3);
* for (short i = 0; i < 10; i++) {
* sheetRow.createCell(i).setCellValue(i * 10);
* }
* }</pre>
*
* This class is similar to the AbstractPdfView class in usage style.
*
* @author Jean-Pierre Pawlak
* @author Juergen Hoeller
* @see AbstractPdfView
*/
public abstract class AbstractExcelView extends AbstractView {
用AbstractExcelView替换，就需要引入Poi
二、结合POI使用：
1、引入Poi
<dependency org="org.apache.poi" name="poi" rev="3.9" conf="compile->compile(*),master(*);runtime->runtime(*)" transitive="false"/>
2、接口与实现：
接口同上，实现源码：
@RestController
public class ExportRSImpl implements ExportRS {
@Override
public ModelAndView exportExcel(String condition,
HttpServletRequest request, HttpServletResponse response) {
try{
NSearch nSearch = JsonObjUtil.JsonToObj(condition, NSearch.class);
String userId = SecurityHelper.getCurrentUserId();
QueryResult result = _mupport.search(nSearch, userId);
long total = result.getTotalCount();
//System.out.println("资产总数是："+total);
Map<String, Object> model = new HashMap<String, Object>();
model.put("total", total+"");
model.put("filename", "资产信息-"+new SimpleDateFormat("yyyyMMddhhmmss").format(new Date())+".xls");
model.put("items", result.getItems());
//资产类型
List<AssetCategoryDefine> categoryList = _assetCatRp.findAllRestrict();
//所有用户
String stationId = SecurityHelper.getCurrentStationId();
List<User> userList = _rpUser.findByStationId(stationId);
model.put("allUser", userList);
model.put("allCategory", categoryList); return new ModelAndView(new JPoiExcelView(),model);
} catch (Throwable e) {
throw new AssetRuntimeException(e);
}
}
}
ExportRSImpl.java
3、ModelAndView实现：
public class JPoiExcelView extends AbstractExcelView{
private static Logger logger = LoggerFactory.getLogger(JPoiExcelView.class);
String[] tilte = new String[]{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
List<AssetCategoryDefine> categoryList = null;
List<User> userList = null;
HSSFCellStyle headerStyle = null;
HSSFCellStyle contextStyle = null;
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response){
OutputStream os = null;
try {
categoryList = (List<AssetCategoryDefine>)model.get("allCategory");
userList = (List<User>)model.get("allUser");
String filename = (String)model.get("filename");
//String total = (String)model.get("total");
//响应信息，弹出文件下载窗口
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(filename, "UTF-8"));
os = response.getOutputStream();
HSSFSheet sheet = workbook.createSheet("资产信息");
headerStyle = getHeader(workbook);
contextStyle = getContext(workbook);
String titleName = "资产信息";
//添加标题和文件列表头
addTitle(sheet,tilte,titleName);
//添加文件信息
List<MObject> list = (List<MObject>)model.get("items");
addContextByList(sheet,list,tilte.length);
workbook.write(os);
} catch (Throwable e) {
logger.error("资产信息导出出错："+e.getMessage(),e);
}finally {
try {
os.flush();
os.close();
} catch (Throwable e) {
logger.error("资产信息导出Excel出错："+e.getMessage(),e);
}
}
}
//添加标题和列头信息
public void addTitle(HSSFSheet sheet, String[] assettitle, String total,String titleName){
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(2);
cell.setCellValue(titleName+"(共有"+total+"条)");
row = sheet.createRow(1);
for (int i = 0; i < assettitle.length; i++) {
cell = row.createCell(i);
cell.setCellValue(assettitle[i]);
}
}
//添加标题和列头信息
public void addTitle(HSSFSheet sheet, String[] assettitle,String titleName) {
//添加标题第一行 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, assettitle.length-1));
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue(titleName);
cell.setCellStyle(headerStyle);
row = sheet.createRow(1);
for (int i = 0; i < assettitle.length; i++) {
cell = row.createCell(i);
cell.setCellValue(assettitle[i]);
cell.setCellStyle(contextStyle);
}
}
//添加所有文件信息 从第三行开始添加
//{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
@SuppressWarnings("unchecked")
private <T> void addContextByList(HSSFSheet sheet, List<T> list, int length){
try {
HSSFRow row = null;
HSSFCell cell = null;
if (list!=null) {
List<MObject> mObjects = (List<MObject>)list;
MObject mObject = null;
String value = "";
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i+2);
for (int j = 0; j < length; j++) {
mObject = mObjects.get(i);
if(j==0){
cell = row.createCell(j);
cell.setCellValue(i+"");
cell.setCellStyle(contextStyle);
}else{
value = getValue(mObject,j);
cell = row.createCell(j);
cell.setCellValue(value);
cell.setCellStyle(contextStyle);
}
}
}
for (int j = 1; j < length; j++) {
sheet.autoSizeColumn(j); //单元格宽度 以最大的为准
}
}else{
row = sheet.createRow(2);
cell = row.createCell(0);
}
} catch (Throwable e) {
logger.error("填充内容出现错误："+e.getMessage(),e);
}
}
//根据列名获取值，先写死，之后再想其他办法
//{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
private String getValue(MObject mObject, int j) {
switch (j) {
case 1:
return mObject.getName();
case 2:
return getAssetCategory(objectToString(mObject.getCategoryId()),null);
case 3:
return objectToString(mObject.getCreatedTime());
case 4:
return getUserInfo(objectToString(mObject.getDeletedBy()),null);
case 5:
return objectToString(mObject.getDeletedTime());
case 6:
return objectToString(mObject.getExtraData()==null? "" :mObject.getExtraData().get("delHostName"));
default:
}
return "";
}
private String objectToString(Object object){
if(object==null){
return "";
}else{
if(object instanceof Date){
DateFormat from_type = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = (Date)object;
return from_type.format(date);
}else{
return (String)object;
}
}
}
//标题样式
public static HSSFCellStyle getHeader(HSSFWorkbook workbook){
HSSFCellStyle format = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
font.setFontName("黑体");
font.setFontHeightInPoints((short)16);
format.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
format.setAlignment(HSSFCellStyle.ALIGN_CENTER);
format.setFont(font);
return format;
}
//内容样式
public static HSSFCellStyle getContext(HSSFWorkbook workbook){
HSSFCellStyle format = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
format.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
format.setAlignment(HSSFCellStyle.ALIGN_CENTER);
format.setFont(font);
return format;
}
//获取用户名称
/**
* id 用户id
* key:用于扩展属性的key值(若有)
* */
public String getUserInfo(String id,String key){
if(userList !=null&&userList.size() > 0 ){
for (User user : userList) {
if( user.getId().equals(id))
return user.getName();
}
}
return "";
}
//获取类型名称
/**
* id 类型id
* key:用于扩展属性的key值 (若有)
* */
public String getAssetCategory(String id,String key){
if(categoryList!=null && categoryList.size()>0){
for (AssetCategoryDefine assetCategoryDefine : categoryList) {
if(assetCategoryDefine.getId().equals(id))
return assetCategoryDefine.getName();
}
}
return "";
}
}
JPoiExcelView.java
以上，代码编写完成：
三、为了方便，写一个工具类：
package com.cdv.edit.utils; import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils; /**
* 生成Excel的工具类
*/
public class PoiExcelUtils { private static Logger logger = LoggerFactory.getLogger(PoiExcelUtils.class); /**
* 添加列表信息
* sheet excelSheet
* list 导出主要信息
* fieldName 属性名称>数组对于表头 扩展属性格式extra.key
* contextStyle 内容样式
* isHaveSerial 是否添加序号
*/
public static <T> void addContextByList(HSSFSheet sheet, List<T> list,
String[] fieldName, HSSFCellStyle contextStyle,boolean isHaveSerial) { try {
HSSFRow row = null;
HSSFCell cell = null;
if (list != null) {
List<T> tList = (List<T>) list;
T t = null;
String value = "";
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 2);
for (int j = 0; j < fieldName.length; j++) { t = tList.get(i);
value = objectToString(getFieldValueByName(fieldName[j], t));
if(isHaveSerial){
//首列加序号
if(row.getCell(0)!=null && row.getCell(0).getStringCellValue()!=null){
cell = row.createCell(0);
cell.setCellValue(""+i);
}
cell = row.createCell(j+1);
cell.setCellValue(value);
}else{
cell = row.createCell(j);
cell.setCellValue(value);
}
cell.setCellStyle(contextStyle);
}
}
for (int j = 1; j < fieldName.length; j++) {
sheet.autoSizeColumn(j); // 单元格宽度 以最大的为准
}
} else {
row = sheet.createRow(2);
cell = row.createCell(0);
}
} catch (Throwable e) {
logger.error("填充内容出现错误：" + e.getMessage(), e);
}
} /**
* <P>Object转成String类型，便于填充单元格</P>
* */
public static String objectToString(Object object){
String str = "";
if(object==null){
}else if(object instanceof Date){
DateFormat from_type = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = (Date)object;
str = from_type.format(date);
}else if(object instanceof String){
str = (String)object;
}else if(object instanceof Integer){
str = ((Integer)object).intValue()+"";
}else if(object instanceof Double){
str = ((Double)object).doubleValue()+"";
}else if(object instanceof Long){
str = Long.toString(((Long)object).longValue());
}else if(object instanceof Float){
str = Float.toHexString(((Float)object).floatValue());
}else if(object instanceof Boolean){
str = Boolean.toString((Boolean)object);
}else if(object instanceof Short){
str = Short.toString((Short)object);
}
return str;
} /**
* 添加标题(第一行)与表头(第二行)
*
* @param
* sheet excelSheet
* assettitle 表头>数组
* titleName 标题
* headerStyle 标题样式
* contextStyle 表头样式
*/
public static void addTitle(HSSFSheet sheet, String[] assettitle, String titleName,
HSSFCellStyle headerStyle, HSSFCellStyle contextStyle) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, assettitle.length - 1));
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue(titleName);
cell.setCellStyle(headerStyle);
row = sheet.createRow(1);
for (int i = 0; i < assettitle.length; i++) {
cell = row.createCell(i);
cell.setCellValue(assettitle[i]);
cell.setCellStyle(contextStyle);
}
} /**
* <p>
* 根据属性名获取属性值
* </p>
* fieldName 属性名 object 属性所属对象
* 支持Map扩展属性, 不支持List类型属性，
* return 属性值
*/
@SuppressWarnings("unchecked")
public static Object getFieldValueByName(String fieldName, Object object) {
try {
Object fieldValue = null;
if (StringUtils.hasLength(fieldName) && object != null) {
String firstLetter = ""; // 首字母
String getter = ""; // get方法
Method method = null; // 方法
String extraKey = null;
// 处理扩展属性 extraData.key
if (fieldName.indexOf(".") > 0) {
String[] extra = fieldName.split("\\.");
fieldName = extra[0];
extraKey = extra[1];
}
firstLetter = fieldName.substring(0, 1).toUpperCase();
getter = "get" + firstLetter + fieldName.substring(1);
method = object.getClass().getMethod(getter, new Class[] {});
fieldValue = method.invoke(object, new Object[] {});
if (extraKey != null) {
Map<String, Object> map = (Map<String, Object>) fieldValue;
fieldValue = map==null ? "":map.get(extraKey);
}
}
return fieldValue;
} catch (Throwable e) {
logger.error("获取属性值出现异常：" + e.getMessage(), e);
return null;
}
} }
工具类的使用例子如下：
public class TestExcelView extends AbstractExcelView {
private static Logger logger = LoggerFactory.getLogger(JPoiExcelView.class);
String[] showName = new String[]{"序号","资产名称","资产类型","创建时间","删除人","删除时间","删除主机"};
String[] fieldName = new String[]{"name","categoryId","createdTime","deletedBy","deletedTime","extraData.delHostName"};
@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
OutputStream os = null;
try {
String filename = (String)model.get("filename");
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(filename, "UTF-8"));
os = response.getOutputStream();
HSSFSheet sheet = workbook.createSheet("资产信息");
PoiExcelUtils.addTitle(sheet, showName, "资产信息", getHeader(workbook), getContext(workbook));
@SuppressWarnings("unchecked")
List<MObject> list = (List<MObject>) model.get("items");
PoiExcelUtils.addContextByList(sheet, list, fieldName, getContext(workbook), true);
workbook.write(os);
} catch (Throwable e) {
logger.error("资产信息导出出错：" + e.getMessage(), e);
} finally {
try {
os.flush();
os.close();
} catch (Throwable e) {
logger.error("资产信息导出Excel出错：" + e.getMessage(), e);
}
}
}
//标题样式
public static HSSFCellStyle getHeader(HSSFWorkbook workbook){
HSSFCellStyle format = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
font.setFontName("黑体");
font.setFontHeightInPoints((short)16);
format.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
format.setAlignment(HSSFCellStyle.ALIGN_CENTER);
format.setFont(font);
return format;
}
//内容样式
public static HSSFCellStyle getContext(HSSFWorkbook workbook){
HSSFCellStyle format = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
format.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
format.setAlignment(HSSFCellStyle.ALIGN_CENTER);
format.setFont(font);
return format;
}
}
UP!