最近由于项目需要,于是做了jgrid表格的公共导出组件,此公共组件可以sql查询导出、hql查询导出,主要采用POI技术实现报表的导出。
前端jgrid公共导出js方法:
//jqgrid导出excel
function exportGrid(gridId, sqlTemplate, fileName, extraParams){
var exportUrl = contextPath + "/export/jqgrid";
var checkBoxTemplate = "<input role='checkbox' id='cb_list' class='cbox' type='checkbox'/>";
var colModels = $("#" + gridId).jqGrid('getGridParam','colModel');
var colNames = $("#" + gridId).jqGrid('getGridParam','colNames');
var ids = $("#" + gridId).jqGrid ('getGridParam', 'selarrrow');
var postData = $("#" + gridId).jqGrid ('getGridParam', 'postData');
var caption = $("#" + gridId).jqGrid ('getGridParam', 'caption');
var fields = [];
var enabledColNameIndex = [];
for(var i = 0;i < colModels.length; i++){
var col = colModels[i];
if(!col.hidden && col.name != "cb" && col.name.indexOf("customColumn") == -1){
enabledColNameIndex.push(i);
fields.push(col.index);
}
}
var filterColNames = [];
for(var i = 0;i < enabledColNameIndex.length;i++){
filterColNames.push(colNames[enabledColNameIndex[i]]);
}
var fieldsParam = fields.join();
var colNameParam = filterColNames.join();
//装载列表头参数,列表字段参数
var params = {};
params.colNameParam = colNameParam;
params.fieldsParam = fieldsParam;
if(ids.length != 0){
idsParam = ids.join();
params.ids = idsParam;
}
//装载当前的jqgrid条件参数
params = $.extend(params, postData);
//装载hqlTemplaate和文件名称参数
if(!fileName){
fileName = caption + ".xls";
}
params.fileName = fileName;
params.sqlTemplate = sqlTemplate;
//装载格外的参数
params = $.extend(params, extraParams);
//构造form表单,进行post提交
if($("#common_exportForm").get(0)){
$("#common_exportForm").remove();
}
var form = $(document.createElement('form'))
.attr("id", "common_exportForm").attr('action', exportUrl)
.attr('method','post').css("display", "none");
$('body').append(form);
for(var attr in params){
var key = attr, value = params[attr];
if(value != ""){
$(document.createElement('input')).attr('type', 'hidden').attr('name', key).attr('value', value).appendTo(form);
}
}
$(form).submit();
}
后台controller:
/**
* 导出controller
* @author wbw
*
*/
@Controller
@RequestMapping("/export")
public class ExportController {
@Autowired
private ExportService exportService;
/**
* 导出excel文件
* @param queryParams 表格查询参数
* @param fileName 文件名称
* @param sqlTemplate 查询sql
* @param colNameParam 表格列名
* @param fieldsParam 字段
* @param ids ID集合
* @param prefix 前缀
* @param isMeger 是否合并标识
* @return
* @throws Exception
*/
@RequestMapping(value = "/jqgrid", method = RequestMethod.POST)
public @ResponseBody ResponseEntity<byte[]> exportExcel(QueryParams queryParams, String fileName, String sqlTemplate,
String colNameParam, String fieldsParam, String ids, String prefix,boolean flag) throws Exception{
PageBean pageBean = JqGridUtil.getPageBean(queryParams);
pageBean.setPropPrefix(prefix);
ExportParam exportParam =ExportParam.buildExportParam(sqlTemplate, fileName, ids,colNameParam, fieldsParam, pageBean, prefix,flag);
return exportService.exportListExcel(exportParam);
}
}
导出参数类,用于封装jqgrid对excel的导出参数封装:
**
*
* 导出参数类,用于封装jqgrid对excel的导出参数封装
*
* @author wbw
*
*/
public class ExportParam {
// 导出文件名称
private String exportFileName;
// 导出的语句模板
private String hqlTemplate;
// 导出的查询条件
private List<Condition> conditions;
// 导出的排序条件
private List<Order> orders;
// 导出的列表头
private String colNameParam;
// 用于sql查询的字段信息
private String fieldsParam;
// 用于计算的字段信息
private String computefieldsParam;
// 导出的数据id集合
private String ids;
//字段前缀
private String prefix;
//是否合并标识
private boolean flag;
ExportService服务接口:
/**
* 导出excel业务接口
* @author wbw
*
*/
public interface ExportService {
/**
* 导出excel
* @param exportParam 导出参数对象
* @return ResponseEntity<byte[]>
*/
public ResponseEntity<byte[]> exportListExcel(ExportParam exportParam);
}
ExportService实现接口:
/**
* 导出excel业务实现接口
* @author wbw
*
*/
@Service
public class ExportServiceImpl implements ExportService {
@Autowired
private IGeneralDao generalDao;
@PersistenceContext(unitName = "platform")
private EntityManager em;
@Override
public ResponseEntity<byte[]> exportListExcel(ExportParam exportParam) {
if(exportParam.isFlag()==true){//条件检索
return exportCompositeObject(exportParam);
}else{//单列表
return exportSingleObject(exportParam);
}
}
/**
* 导出复合对象的excel
* @param exportParam excel参数封装对象
* @return
*/
@SuppressWarnings("unchecked")
private ResponseEntity<byte[]> exportCompositeObject(ExportParam exportParam){
//获取excel表头
String[] cols = new String[0];
if(!StringUtils.isEmpty(exportParam.getColNameParam())){
cols = exportParam.getColNameParam().split(",");
}
List<Object> resultList = null;
String convertSqlTemplate = null;
if(!StringUtils.isEmpty(exportParam.getHqlTemplate())){
//去除baseinfo.zfid字段
if(exportParam.getHqlTemplate().contains(ExportUtils.REMOVE_FIELD)){
convertSqlTemplate = exportParam.getHqlTemplate().replace(ExportUtils.REMOVE_FIELD, "");
}else{
convertSqlTemplate = exportParam.getHqlTemplate();
}
//根据sql从数据库查询要导出的数据
resultList = em.createNativeQuery(convertSqlTemplate).getResultList();
}else{
throw new PlatformException("导出的sqlTemplate为null!");
}
byte[] bytes = null;
//创建带资源的try-catch
try(Workbook wb = new HSSFWorkbook();ByteArrayOutputStream bos = new ByteArrayOutputStream();){
String [] sheetTitle = exportParam.getExportFileName().split("\\.");
Sheet sheet = wb.createSheet(sheetTitle[0]);
//设置表头单元格样式
CellStyle colTopCellStyle = ExportUtils.initTopCellStyle(wb);
//设置列头单元格的值
ExportUtils.initTopCell(sheet, colTopCellStyle, cols);
//单元格样式
CellStyle cellType = ExportUtils.initCellStyle(wb);
//填充excel数据
ExportUtils.initGridData(resultList,sheet,cellType);
//合并单元格
ExportUtils.mergeColumns(sheet,SpecFieldUtils.getFieldsBySession());
wb.write(bos);
bytes = bos.toByteArray();
} catch (IOException e) {
e.printStackTrace();
LoggerUtils.info("填充excel数据异常");
throw new RuntimeException(e);
}
return ExportUtil.getResponseEntityByFile(bytes, exportParam.getExportFileName());
}
/**
* 导出单列表Excel
* @param exportParam excel参数封装对象
* @return
*/
private ResponseEntity<byte[]> exportSingleObject(ExportParam exportParam){
String[] computeFields = new String[0];
String[] cols = new String[0];
if(!StringUtils.isEmpty(exportParam.getComputefieldsParam())){
computeFields = exportParam.getComputefieldsParam().split(",");
}
if(!StringUtils.isEmpty(exportParam.getColNameParam())){
cols = exportParam.getColNameParam().split(",");
}
String ids = exportParam.getIds();
List<Condition> conditions = exportParam.getConditions();
List<Order> orders = exportParam.getOrders();
//如果jqgrid传递了数据id集合,则需要对条件进行过滤(应用场景通常为用户勾选了列表的checkbox后进行导出)
if(!StringUtils.isEmpty(ids)){
String[] idsArray = ids.split(",");
Condition condition = new Condition();
condition.setOperation(Operation.IN);
condition.setPropertyName(exportParam.getPrefix() + "id");
condition.setRelateType(RelateType.AND);
condition.setPropertyValue(idsArray);
conditions.add(condition);
}
String sql = exportParam.getHqlTemplate().replaceAll("\\{fields\\}", exportParam.getFieldsParam());
//根据条件从数据库查询要导出的数据
List<?> resultList = generalDao.doList(sql, conditions, orders, true);
byte[] bytes = null;
try(Workbook wb = new HSSFWorkbook();ByteArrayOutputStream bos = new ByteArrayOutputStream();){
Sheet sheet = wb.createSheet(exportParam.getExportFileName());
//填充excel表头
CellStyle colTopCellStyle = wb.createCellStyle();
colTopCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
colTopCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
colTopCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
colTopCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
colTopCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
colTopCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
Row colRow = sheet.createRow(0);
for (int i = 0; i < cols.length; i++) {
sheet.setColumnWidth(i, 6000);
String colName = cols[i];
Cell cell = colRow.createCell(i);
cell.setCellValue(colName);
cell.setCellStyle(colTopCellStyle);
}
//填充excel数据
for (int i = 0; i < resultList.size(); i++) {
Object obj = resultList.get(i);
Row row = sheet.createRow(i + 1);
Class<?> clazz = obj.getClass();
for (int j = 0; j < computeFields.length; j++) {
sheet.setColumnWidth(j, 6000);
String field = computeFields[j];
String methodName = "get" + String.valueOf(field.charAt(0)).toUpperCase() + field.substring(1, field.length());
Cell cell = row.createCell(j);
Method classMehtod = clazz.getMethod(methodName);
if(classMehtod != null){
classMehtod.setAccessible(true);
if(classMehtod.invoke(obj) instanceof Date){
String dateValue =
DateUtil.dateToStr(((Date)classMehtod.invoke(obj)), "yyyy-MM-dd HH:mm:ss");
cell.setCellValue(dateValue);
}else{
if(classMehtod.invoke(obj) != null){
cell.setCellValue(classMehtod.invoke(obj).toString());
}
}
}
}
}
wb.write(bos);
bytes = bos.toByteArray();
} catch (Exception e) {
e.printStackTrace();
LoggerUtils.info("填充excel数据异常");
throw new RuntimeException(e);
}
return ExportUtil.getResponseEntityByFile(bytes, exportParam.getExportFileName());
}
ExportUtils工具类:
/**
* 默认时间格式
*/
private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
/**
* 剔除字段
*/
public static final String REMOVE_FIELD = ",baseinfo.zfid";
/**
* 设置头部单元格样式
*
* @param wb 工作簿
*/
public static CellStyle initTopCellStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
return cellStyle;
}
/**
* 设置单元格样式
*
* @param wb 工作簿
*/
public static CellStyle initCellStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
return cellStyle;
}
/**
* 设置列表头单元格
* @param sheet
* @param colTopCellStyle
* @param cols
*/
public static void initTopCell(Sheet sheet,CellStyle colTopCellStyle,String[] cols){
Row colRow = sheet.createRow(0);
for (int i = 0; i < cols.length; i++) {
sheet.setColumnWidth(i, 6000);
String colName = cols[i];
Cell cell = colRow.createCell(i);
cell.setCellValue(colName);
cell.setCellStyle(colTopCellStyle);
}
}
/**
* 设置单元格值
*
* @param cell
* 单元格
* @param value
* 单元格值
*/
public static void initCellValue(Cell cell, Object value) {
if (cell != null) {
if (value == null) {
cell.setCellType(Cell.CELL_TYPE_BLANK);
} else {
if (value instanceof Calendar) {
cell.setCellValue((Calendar) value);
}else if(value instanceof Integer){
cell.setCellValue((Integer)value);
} else if (value instanceof Date) {
String dateValue = DateUtil.dateToStr(((Date) value),
DEFAULT_DATE_FORMAT);
cell.setCellValue(dateValue);
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else {
cell.setCellValue(value.toString());
}
}
} else {
return;
}
}
/**
* 填充excel数据
* @param resultList 数据集
* @param sheet Sheet
* @param cellType 单元格样式
*/
public static void initGridData(List<Object> resultList,Sheet sheet,CellStyle cellType){
for (int rowindex = 0; rowindex < resultList.size(); rowindex++) {
Object[] objectRow = (Object[]) resultList.get(rowindex);
Row row = sheet.createRow(rowindex+1);//从第二行开始
for (int i = 0; i < objectRow.length; i++) {
Object cellValue = objectRow[i];
Cell cell = row.createCell(i);
//设置单元格值
initCellValue(cell, cellValue);
//设置单元格样式
cell.setCellStyle(cellType);
}
}
}
/**
* 合并列单元格
*
* @param sheet
*/
public static void mergeColumns(Sheet sheet, LinkedHashMap<Integer,FieldInfo> map) {
// 行数
int rowsCount = sheet.getPhysicalNumberOfRows();
//如果查询出来的数据集不为null的情况下才做合并操作
if(!StringUtils.isEmpty(sheet.getRow(1))){
// 列数
int colsCount = sheet.getRow(1).getPhysicalNumberOfCells();
Row cunrrentRow = null;// 记录当前行
Cell currentCell = null;// 记录当前单元格
String lastZfbh = "";//记录上一个罪犯编号
Row lastRow = null;//记录上一行
// 循环所有的行数除去第一行表头
for (int r = 1; r < rowsCount; r++) {
cunrrentRow = sheet.getRow(r);
String currentZfbh = cunrrentRow.getCell(2).toString();
// 循环所有的列数
for (int c = 0; c < colsCount; c++) {
currentCell = cunrrentRow.getCell(c);
if (!currentZfbh.equals(lastZfbh)) {// 与上一行相比,如果与上一行的罪犯编号不一致,则把当前行赋予给上一行的变量
lastZfbh = currentZfbh;
lastRow = cunrrentRow;
break;
} else {// 如果当前行的罪犯编号与上一行的罪犯编号一致
if (lastRow != null) {// 如果上一行不等于空
Cell lastCell = lastRow.getCell(c);
//是否合并标识
boolean isMeger = false;
if(map.size() > 0){
isMeger = map.get(c).getIsMeger().equals("true");
}
// 三个条件
// 满足以下三个条件进行合并操作:
// 1.上一行罪犯编号与本行罪犯编号一致
// 2.上一行对应点列数据与本行对应的列数据一致
// 3.一定要标识为merge为true的字段才能合并
if (currentZfbh.equals(lastZfbh)
&& lastCell.getStringCellValue().equals(
currentCell.getStringCellValue())
&& isMeger) {
sheet.addMergedRegion(new CellRangeAddress(r - 1,
r, c, c));
//设置居中显示
}
}
}
}
}
}
}
}
ExportUtil 导出bytes文件工具类:
public class ExportUtil {
private static Logger logger = Logger.getLogger(ExportUtil.class);
private static final String CONTENT_DISPOSITION = "Content-Disposition";
/**
* 构建服务器传输给客户端的二进制件信息
* @param fileName 文件名为空则显示当前时间
* @param resource word 文件的路径
* @return
* @throws Exception
*/
public static ResponseEntity<byte[]> getResponseEntityByFile(File file,String fileName) throws Exception {
return getResponseEntityByFile(new FileInputStream(file), fileName);
}
public static ResponseEntity<byte[]> getResponseEntityByFile(FileInputStream stream, String fileName) throws Exception {
byte[] cbyte = IOUtils.toByteArray(stream);
stream.close();
return getResponseEntityByFile(cbyte, fileName);
}
public static ResponseEntity<byte[]> getResponseEntityByFile(byte[] cbyte,String fileName) throws RuntimeException {
HttpHeaders responseHeaders = new HttpHeaders();
responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
responseHeaders.setContentLength(cbyte.length);
try {
responseHeaders.set(CONTENT_DISPOSITION, "attachment;filename=\""+URLEncoder.encode(fileName,"UTF-8")+"\"");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
logger.info("导出文件名称URL编码异常");
throw new RuntimeException(e);
}
return new ResponseEntity<byte[]>(cbyte, responseHeaders, HttpStatus.OK);
}
}