继 S-HR之代码创建临时表并插入数据 完整功能之员工职业信息变更报表
目的示例1:
制作员工职业信息报表[S-HR系统的报表其实就是列表o.0,醉了]
EcirrWithPP.js
shr.defineClass("shr.custom.EcirrWithPP", shr.framework.List, {
initalizeDOM: function () {
var _self = this;
shr.custom.EcirrWithPP.superClass.initalizeDOM.call(this);
_self.initTable();
_self.unitFilter();
},
unitFilter: function(id) {
$("input[id$='unit']").shrPromptBox("setFilter","unitLayerType in ('00000000-0000-0000-0000-00000000000362824988','00000000-0000-0000-0000-00000000000262824988','00000000-0000-0000-0000-00000000000162824988')");
},
initTable: function () {
options = {
postData: null,
multiselect: false,
rownumbers: true,
colNames: getChineseName(),
colModel: getColModel(),
width: 20,
align: "center",
height: 'auto',
gridview: true,
shrinkToFit: false,
viewrecords: true,
autowidth: true,
sortname: "Istatol",
gridComplete: function () {
}
};
//reportGrid 表格的id
table = $("#reportGrid");
table.html();
table.jqGrid(options);
jQuery('#reportGrid').jqGrid('setFrozenColumns');
},
queryAction: function () {
var admin = $('#unit').val();
if (null == admin || "" == admin) {
shr.showError({
"message": "请选择单位"
});
return;
}
var viewPage = shr.getCurrentViewPage();
var _self = this;
var endDate = $("#endDate").val();
var startDate = $("#startDate").val();
var unit = this.getField('unit').shrPromptBox("getValue");
var unitId = "";
if (unit != null) {
if (unit.id != "") {
unitId = unit.longNumber;
}
}
//组织属性
var orgAttr=$("#orgAttr").shrPromptBox("getValue");
//清除jqgrid的数据
waf("#reportGrid").jqGrid("clearGridData");
waf("#reportGrid").jqGrid("option", "page", 1);
// 拼接数据传递到后台
//filterItem:"admin:01!100;orgAttr:[object Object];startDate:2019-06-01;endDate:2019-06-20;"
var filterItem = "admin:" + unitId + ";orgAttr:" + orgAttr + ";startDate:" + startDate + ";endDate:" + endDate + ";"
//执行查询渲染页面操作
shr.doAction({
target: "#query",
url: _self.dynamicPage_url + "?uipk=" + _self.uipk + "&method=query&filterItems=" + filterItem,
type: 'post',
data: {},
async: true,
success: function (data) {
var rowdatas = data.rowData;
var tempTable = data.tempTable;
$("#tempTable").val(tempTable);
if (rowdatas.records != 0) {
for (var i = 0; i < rowdatas.records; i++) {
var row = rowdatas.rows[i];
var empName = row.empName;
var empNumber = row.empNumber;
var empEnterDateBefore = row.empEnterDateBefore;
var empEnterDateAfter = row.empEnterDateAfter;
var empJoinGroupDateBefore=row.empJoinGroupDateBefore;
var empJoinGroupDateAfter=row.empJoinGroupDateAfter;
var lastUpdateTime=row.lastUpdateTime;
var lastUpdateUserName=row.lastUpdateUserName;
//var iscount = row.iscount;//不知道这个iscount干吗用的
// var $grid = $(this.gridId);
// if (row.iscount == "1") {
// var datarow = {
// "empName": empName,
// "empNumber": empNumber,
// "empEnterDateBefore": empEnterDateBefore,
// "empEnterDateAfter": empEnterDateAfter,
// "empJoinGroupDateBefore": empJoinGroupDateBefore,
// "empJoinGroupDateAfter": empJoinGroupDateAfter,
// "lastUpdateTime": lastUpdateTime,
// "lastUpdateUserName": lastUpdateUserName
// };
// waf("#reportGrid").jqGrid('addRow', {
// data: datarow
// });
// // // $("#admin" + (i + 1) + "").attr("colspan", 7);
// $("#reportGrid").setCell((i + 1), "empName", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "empNumber", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "empEnterDateBefore", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "empEnterDateAfter", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "empJoinGroupDateBefore", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "empJoinGroupDateAfter", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "lastUpdateTime", '', {
// display: 'none'
// });
// $("#reportGrid").setCell((i + 1), "lastUpdateUserName", '', {
// display: 'none'
// });
// // $("#admin" + (i + 1) + "").attr("colspan", 7);
// } else {
var datarow = {
"empName": empName,
"empNumber": empNumber,
"empEnterDateBefore": empEnterDateBefore,
"empEnterDateAfter": empEnterDateAfter,
"empJoinGroupDateBefore": empJoinGroupDateBefore,
"empJoinGroupDateAfter": empJoinGroupDateAfter,
"lastUpdateTime": lastUpdateTime,
"lastUpdateUserName": lastUpdateUserName,
};
waf("#reportGrid").jqGrid('addRow', {
data: datarow
});
//}
}
var gridName = "reportGrid";
//Merger(gridName, 'longnumber');
}
}
});
},
//导出适用人员excel
exportBillAction: function () {
var tempTable = $("#tempTable").val();
var param = {
handler: 'com.kingdee.eas.custom.handler.EcirrWithPPHandler',
method: 'exportBill',
tempTable: tempTable
}
location.href = shr.getContextPath() + shr.dynamicURL + '?' + $.param(param);
}
});
/**
* 创建jqgrid模型
*/
function getColModel() {
var arr = [];
arr.push({
name: 'empName',
label: '员工姓名',
index: 'name',
width: 70,
cellattr: function (rowId, tv, rawObject, cm, rdata) {
//合并单元格
return 'id=\'empName' + rowId + "\'";
}
});
arr.push({
name: 'empNumber',
label: '员工编号',
index: 'empNumber',
width: 70,
cellattr: function (rowId, tv, rawObject, cm, rdata) {
//合并单元格
return 'id=\'empNumber' + rowId + "\'";
}
});
arr.push({
name: 'empEnterDateBefore',
label: '变更前入职时间',
index: 'empEnterDateBefore',
width: 100,
editable: false,
sortable: true
});
arr.push({
name: 'empEnterDateAfter',
label: '变更后入职时间',
index: 'empEnterDateAfter',
width: 100,
editable: false,
sortable: true
});
arr.push({
name: 'empJoinGroupDateBefore',
label: '变更前工龄计算起始时间',
index: 'empJoinGroupDateBefore',
width: 150,
editable: false,
sortable: true
});
arr.push({
name: 'empJoinGroupDateAfter',
label: '变更后工龄计算起始时间',
index: 'empJoinGroupDateAfter',
width: 150,
editable: false,
sortable: true
});
arr.push({
name: 'lastUpdateTime',
label: "变更时间",
index: 'lastUpdateTime',
width: 90,
editable: false,
sortable: true
});
arr.push({
name: 'lastUpdateUserName',
label: "变更人",
index: 'lastUpdateUserName',
width: 90,
editable: false,
sortable: true
});
return arr;
}
/**
* 创建列名称
*/
function getChineseName() {
var arr = [];
arr.push("员工姓名")
arr.push("员工编号");
arr.push("变更前入职时间");
arr.push("变更后入职时间");
arr.push("变更前工龄计算起始时间");
arr.push("变更后工龄计算起始时间");
arr.push("变更时间");
arr.push("变更人");
return arr;
}
//公共调用方法
// function Merger(gridName, CellName) {
// //得到显示到界面的id集合
// var mya = $("#" + gridName + "").getDataIDs();
// //当前显示多少条
// var length = mya.length;
// for (var i = 0; i < length; i++) {
// //从上到下获取一条信息
// var before = $("#" + gridName + "").jqGrid('getRowData', mya[i]);
// //定义合并行数
// var rowSpanTaxCount = 1;
// var j = i + 1;
// for (; j <= length; j++) {
// //和上边的信息对比 如果值一样就合并行数+1 然后设置rowspan 让当前单元格隐藏
// var end = $("#" + gridName + "").jqGrid('getRowData', mya[j]);
// if (before[CellName] == end[CellName]) {
// rowSpanTaxCount++;
// $("#" + gridName + "").setCell(mya[j], CellName, '', {
// display: 'none'
// });
// $("#" + gridName + "").setCell(mya[j], CellName, '', { display: 'none' });
// $("#reportGrid").setCell(mya[j], "admin", '', { display: 'none' });
// $("#reportGrid").setCell(mya[j], "adminStaff", '', { display: 'none' });
// $("#reportGrid").setCell(mya[j], "adminCount", '', { display: 'none' });
// $("#reportGrid").setCell(mya[j], "auditCount", '', { display: 'none' });
// } else {
// break;
// }
// }
// $("#" + CellName + "" + mya[i] + "").attr("rowspan", rowSpanTaxCount);
// $("#admin" + mya[i] + "").attr("rowspan", rowSpanTaxCount);
// $("#adminStaff" + mya[i] + "").attr("rowspan", rowSpanTaxCount);
// $("#adminCount" + mya[i] + "").attr("rowspan", rowSpanTaxCount);
// $("#auditCount" + mya[i] + "").attr("rowspan", rowSpanTaxCount);
// rowSpanTaxCount = 1;
// i = j-1;
// }
// }
EcirrWithPPHandler
package com.kingdee.eas.custom.handler;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.ui.ModelMap;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.dao.ormapping.ObjectUuidPK;
import com.kingdee.bos.db.TempTablePool;
import com.kingdee.bos.ui.face.UIRuleUtil;
import com.kingdee.eas.base.permission.UserFactory;
import com.kingdee.eas.base.permission.UserInfo;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.jdbc.rowset.impl.JdbcRowSet;
import com.kingdee.shr.base.syssetting.context.SHRContext;
import com.kingdee.shr.base.syssetting.exception.SHRWebException;
import com.kingdee.shr.base.syssetting.json.GridDataEntity;
import com.kingdee.shr.base.syssetting.web.handler.ListHandler;
import com.kingdee.shr.base.syssetting.web.json.JSONUtils;
/**
* 员工信息变更记录报表(职业信息)
* @author haikangtest
*
*/
public class EcirrWithPPHandler extends ListHandler{
private Context ctx = SHRContext.getInstance().getContext();
private String tempTab1 = null;
private Map<String, String> paramMap = new HashMap();
/********************
* 操作数据返回 *
===================*/
// 查询组织及下属组织
//返回的map{typeName=行政管理系统, orgType=z68AAAAAD7CugeC3, adminId=z68AAAADpPTM567U, adminName=投资管理部, longNumber=01!100!10002456}
public List<Map<String, String>> getAdminLongNumber(Object longNumber) {
List list = new ArrayList();
if (longNumber == null) {
longNumber = "000001";
}
String sql = "select ad.fid id,ad.fname_l2 admin,ad.flongnumber longnumber,ad.forgfunctionid orgType,fun.fname_l2 orgTypeName from t_org_admin ad inner join T_ORG_OrgFunction fun on forgfunctionid=fun.fid where flongnumber like '"
+ longNumber
+ "%' and "
+ " FIsSealUp='0' and FLayerTypeID='00000000-0000-0000-0000-00000000000362824988' "
+ " group by"
+ " ad.fid,ad.fname_l2,ad.flongnumber,ad.forgfunctionid,fun.fname_l2";
try {
IRowSet set = DbUtil.executeQuery(this.ctx, sql);
while (set.next()) {
Map map = new HashMap();
map.put("adminId", set.getString("id"));
map.put("adminName", set.getString("admin"));
map.put("longNumber", set.getString("longnumber"));
map.put("orgType", set.getString("orgType"));//职能
map.put("orgTypeName", set.getString("orgTypeName"));
list.add(map);
}
} catch (BOSException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//解析参数
private Map<String, Object> getFilterParam(String filterString) {
Map map = new HashMap();
String[] filter = filterString.split(";");
for (int i = 0; i < filter.length; ++i) {
String para = filter[i];
String[] paraArr = para.split(":");
String key = null;
String value = null;
if (paraArr.length == 2) {
key = paraArr[0];
value = paraArr[1];
map.put(key, value);
} else {
key = paraArr[0];
map.put(key, null);
}
}
return map;
}
//临时表EcirrWithPPTempTable
public String getTempTable() {
String tempTable = null;
try {
tempTable = "EcirrWithPPTempTable";
String createTableSQL = "create table "
+ tempTable
+ " (" +
"fempName varchar(255),"+//员工姓名
"fempNumber varchar(255),"+//员工编号
"fempEnterDateBefore varchar(255),"+//变更前入职时间
"fempEnterDateAfter varchar(255),"+//变更后入职时间
"fempJoinGroupDateBefore varchar(255),"+//变更前工龄计算起始时间
"fempJoinGroupDateAfter varchar(255),"+//变更后工龄计算起始时间
"flastUpdateTime varchar(255),"+//变更时间
"flastUpdateUserName varchar(255)"//变更人
+ " )";
TempTablePool tablePool = TempTablePool.getInstance(this.ctx);
tempTable = tablePool.createTempTable(createTableSQL);
} catch (Exception e) {
e.printStackTrace();
}
return tempTable;
}
//执行插入数据到tempTable1中
private void insertAllInfo(String tempTab1, List<Map<String, String>> list) {
try {
if (list.size() > 0) {
// 插入临时表
StringBuffer insertSQL = new StringBuffer();
insertSQL.append("INSERT INTO " + tempTab1);
insertSQL.append("(fempName,fempNumber,fempEnterDateBefore,fempEnterDateAfter,fempJoinGroupDateBefore,fempJoinGroupDateAfter,flastUpdateTime,flastUpdateUserName)").append(" VALUES(?,?,?,?,?,?,?,?)");
List<Object> intsertList = new ArrayList<Object>();
// 从map中取出数据放到临时表中
for (Map<String, String> map : list) {
//String number=map.get("longnumber");
Object[] obj = {
map.get("empName"),
map.get("empNumber"),
map.get("empEnterDateBefore"),
map.get("empEnterDateAfter"),
map.get("empJoinGroupDateBefore"),
map.get("empJoinGroupDateAfter"),
map.get("lastUpdateTime"),
map.get("lastUpdateUserName")
};
intsertList.add(obj);
}
DbUtil.executeBatch(ctx, insertSQL.toString(), intsertList);
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void getAllInfo(String tempTab12,Map<String, Object> map) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String longnumber = null;
if (null == map.get("admin")) {
longnumber = "000001";
} else {
longnumber = map.get("admin").toString();
}
try {
StringBuffer sql = new StringBuffer();
sql.append(" SELECT person.FNAME_l2 empName,person.FNUMBER empNumber,ppHis.FENTERDATE empEnterDateBefore,pp.FENTERDATE empEnterDateAfter,ppHis.FJOINGROUPDATE empJoinGroupDateBefore,pp.FJOINGROUPDATE empJoinGroupDateAfter,pp.FLASTUPDATETIME lastUpdateTime,lastU.FNAME_l2 lastUpdateUserName ").append("\r\n");;
sql.append(" FROM T_HR_PERSONPOSITIONHIS pp ").append("\r\n");;
sql.append(" left join T_BD_Person person on pp.FPERSONID=person.FID ").append("\r\n");;
sql.append(" left join T_ORG_POSITION position on position.FID =pp.FPRIMARYPOSITIONID").append("\r\n");;
sql.append(" left join T_ORG_Admin org on org.FID = position.FADMINORGUNITID ").append("\r\n");;
sql.append(" left join T_ORG_ORGFUNCTION fun on fun.FID=org.forgfunctionid ").append("\r\n");;
sql.append(" left join T_PM_User lastU on lastU.FID =pp.FLASTUPDATEUSERID ").append("\r\n");;
sql.append(" left join T_HR_PERSONPOSITIONHIS ppHis on ppHis.FLEFFDT=pp.FEFFDT-1 and ppHis.FPERSONID=pp.FPERSONID ").append("\r\n");;
sql.append(" where org.flongnumber like '"+ longnumber + "%' and FIsSealUp='0' ").append("\r\n");;
// 开始日期
if (null != map.get("startDate")) {
sql.append(" and to_char(pp.FLASTUPDATETIME,'yyyy-MM-dd')>='"+ map.get("startDate") + "' ").append("\r\n");
}
// 结束日期
if (null != map.get("endDate")) {
sql.append(" and to_char(pp.FLASTUPDATETIME,'yyyy-MM-dd') <='"+ map.get("endDate") + "' ").append("\r\n");
}
IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
while (rs.next()) {
Map<String, String> maps = new HashMap<String, String>();
maps.put("empName", rs.getString("empName"));
maps.put("empNumber", rs.getString("empNumber"));
maps.put("empEnterDateBefore",rs.getDate("empEnterDateBefore")==null?"":sdf.format(rs.getDate("empEnterDateBefore")));
maps.put("empEnterDateAfter", rs.getDate("empEnterDateAfter")==null?"":sdf.format(rs.getDate("empEnterDateAfter")));
maps.put("empJoinGroupDateBefore", rs.getDate("empJoinGroupDateBefore")==null?"":sdf.format(rs.getDate("empJoinGroupDateBefore")));
maps.put("empJoinGroupDateAfter", rs.getDate("empJoinGroupDateAfter")==null?"":sdf.format(rs.getDate("empJoinGroupDateAfter")));
maps.put("lastUpdateTime", rs.getDate("lastUpdateTime")==null?"":sdf.format(rs.getDate("lastUpdateTime")));
maps.put("lastUpdateUserName", rs.getString("lastUpdateUserName"));
list.add(maps);
}
//执行插入数据
insertAllInfo(tempTab1, list);
} catch (BOSException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public GridDataEntity returnData(Map<String, Object> map)throws SHRWebException, SQLException, BOSException {
GridDataEntity gridDataEntity = new GridDataEntity();
List returnList = new ArrayList();
this.tempTab1 = getTempTable();
String adminnumber = null;
if (map.get("admin") != null) {
adminnumber = map.get("admin").toString();
}
// 查询组织及下属组织
List adminList = getAdminLongNumber(adminnumber);
//查询数据并插入数据
getAllInfo(this.tempTab1,map);
StringBuilder sql=new StringBuilder();
sql.append(" select FempName,FempNumber,FempEnterDateBefore,FempEnterDateAfter,FempJoinGroupDateBefore,FempJoinGroupDateAfter,FlastUpdateTime,FlastUpdateUserName ").append("\r\n");
sql.append(" from (").append(this.tempTab1).append(")");
IRowSet rs = DbUtil.executeQuery(this.ctx, sql.toString());
int totalCount = rs.size();
while (rs.next()) {
Map maps = new HashMap();
maps.put("empName", rs.getString("FempName"));
maps.put("empNumber", rs.getString("FempNumber"));
maps.put("empEnterDateBefore", rs.getString("FempEnterDateBefore"));
maps.put("empEnterDateAfter", rs.getString("FempEnterDateAfter"));
maps.put("empJoinGroupDateBefore", rs.getString("FempJoinGroupDateBefore"));
maps.put("empJoinGroupDateAfter", rs.getString("FempJoinGroupDateAfter"));
maps.put("lastUpdateTime", rs.getString("FlastUpdateTime"));
maps.put("lastUpdateUserName", rs.getString("FlastUpdateUserName"));
returnList.add(maps);
}
int rows = 10;
int page = 10;
gridDataEntity.setRows(returnList);
if (totalCount % rows == 0)
gridDataEntity.setTotal(totalCount / rows);
else {
gridDataEntity.setTotal(totalCount / rows + 1);
}
gridDataEntity.setRecords(totalCount);
gridDataEntity.setPage(page);
return gridDataEntity;
}
public void queryAction(HttpServletRequest request,
HttpServletResponse response, ModelMap modelMap)
throws SHRWebException {
String filterString = request.getParameter("filterItems");
Map map = getFilterParam(filterString);
try {
request.getParameter("page");
request.getParameter("CurrentPage");
GridDataEntity gridDataEntity = returnData(map);
Map ajaxData = new HashMap();
ajaxData.put("rowData", gridDataEntity);
ajaxData.put("tempTable", this.tempTab1);
JSONUtils.SUCCESS(ajaxData);
} catch (Exception e) {
e.printStackTrace();
}
}
/********************
* 导出excel *
===================*/
private IRowSet getResultSet(HttpServletRequest request)throws BOSException, SQLException {
String tempTableName = request.getParameter("tempTable");
if (UIRuleUtil.isNull(tempTableName)) {
return new JdbcRowSet();
}
String sql =
"select FempName,FempNumber,FempEnterDateBefore,FempEnterDateAfter,FempJoinGroupDateBefore,FempJoinGroupDateAfter,FlastUpdateTime,FlastUpdateUserName from "+ tempTableName ;
return DbUtil.executeQuery(ctx, sql);
}
private String getCurrentDate() {
Calendar date = Calendar.getInstance();
int iYear = date.get(1);
int iMonth = date.get(2) + 1;
int iDay = date.get(5);
String month = "0" + iMonth;
return iYear + month + iDay;
}
/**
* 设置EXCEL表头、标题样式和数据
*/
private void addHeadRow(XSSFSheet wsheet, XSSFWorkbook wbook, int length,HttpServletRequest request) throws BOSException, SQLException {
// 标题单元格的样式
XSSFCellStyle style0 = wbook.createCellStyle();
// 标题的字体样式
XSSFFont font0 = wbook.createFont();
// 设置字体样式
font0.setFontName("仿宋_GB2312");
// 加粗
font0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体大小
font0.setFontHeightInPoints((short) 20);
// 设置字体的颜色
font0.setColor(HSSFColor.BLACK.index);
style0.setFont(font0);
// 字体居中显示
style0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置表头样式
XSSFCellStyle style1 = wbook.createCellStyle();
// 表头字体
XSSFFont font1 = wbook.createFont();
font1.setFontName("宋体");
font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font1.setFontHeightInPoints((short) 12);
style1.setFont(font1);
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 合并标题
wsheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 9));
// 标题下面的表头的空白格子
wsheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 0));
wsheet.setColumnWidth((short) 1, (short) 7000);
wsheet.setColumnWidth((short) 2, (short) 5000);
wsheet.setColumnWidth((short) 3, (short) 5000);
wsheet.setColumnWidth((short) 4, (short) 5000);
wsheet.setColumnWidth((short) 5, (short) 5000);
wsheet.setColumnWidth((short) 6, (short) 5000);
wsheet.setColumnWidth((short) 7, (short) 5000);
wsheet.setColumnWidth((short) 8, (short) 4500);
// 获得工作表第一行
XSSFRow titleRow = wsheet.createRow(0);
// 获得第二行
XSSFRow tableHeadRow = wsheet.createRow(2);
titleRow.createCell(0).setCellValue(" 员工信息变更记录报表(职业信息)");// 设置单元格数据
titleRow.getCell(0).setCellStyle(style0); // 设置单元格样式
// 给表头列填充数据，并设置样式
String[] str = { "员工姓名", "员工编号", "变更前入职时间", "变更后入职时间", "变更前工龄计算起始时间", "变更后工龄计算起始时间","变更时间", "变更人" };
for (int i = 0; i < str.length; ++i) {
tableHeadRow.createCell(i * 1 + 1).setCellValue(str[i]);
tableHeadRow.getCell(i * 1 + 1).setCellStyle(style1);
}
}
/**
* 把数据填充到EXCEL中
*/
public void setDate(XSSFSheet wsheet, XSSFWorkbook wbook, IRowSet rs)throws BOSException, SQLException, EASBizException {
// 设置数据的样式
XSSFCellStyle style2 = wbook.createCellStyle();
XSSFCellStyle style3 = wbook.createCellStyle();
XSSFFont font2 = wbook.createFont();
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 12);
style2.setFont(font2);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
style3.setFont(font2);
style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// 从EXCEL第三行开始插入数据
int RowTmp = 3;
int i = 0;
String oldName = null;
int star = 3;
int end = 0;
while (rs.next()) {
XSSFRow row = null;
row = wsheet.createRow(RowTmp);
// 序号
row.createCell(0).setCellValue(i + 1);
// 员工姓名
row.createCell(1).setCellValue(rs.getString("FempName"));
// 员工编号
row.createCell(2).setCellValue(rs.getString("FempNumber"));
// 变更前入职时间
row.createCell(3).setCellValue(rs.getString("FempEnterDateBefore"));
// 变更后入职时间
row.createCell(4).setCellValue(rs.getString("FempEnterDateAfter"));
// 变更前工龄计算起始时间
row.createCell(5).setCellValue(rs.getString("FempJoinGroupDateBefore"));
// 变更后工龄计算起始时间
row.createCell(6).setCellValue(rs.getString("FempJoinGroupDateAfter"));
// 变更时间
row.createCell(7).setCellValue(rs.getString("FlastUpdateTime"));
// 变更人
row.createCell(8).setCellValue(rs.getString("FlastUpdateUserName"));
row.getCell(1).setCellStyle(style2);
row.getCell(2).setCellStyle(style2);
row.getCell(3).setCellStyle(style2);
row.getCell(4).setCellStyle(style2);
row.getCell(5).setCellStyle(style2);
row.getCell(6).setCellStyle(style2);
row.getCell(7).setCellStyle(style2);
row.getCell(8).setCellStyle(style2);
RowTmp++;//创建每行
i++;//计数
}
int num = RowTmp++;
// 设置表格最后一行
XSSFRow row1 = wsheet.createRow(num);
String id = ctx.getCaller().toString();
UserInfo user = UserFactory.getLocalInstance(ctx).getUserInfo(new ObjectUuidPK(id));
row1.createCell(6).setCellValue("制表：" + user.getName());
}
public void downloadAction(HttpServletRequest request,HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
try {
request.setAttribute("export", "export");
// 设置Excel文件名
String sheetTitle = "员工信息变更记录报表%28职业信息%29" + getCurrentDate();
String dfileName = URLEncoder.encode(sheetTitle, "utf-8") + ".xls";
// 设置请求头
response.reset();
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename="+ dfileName);
// 新建工作簿
XSSFWorkbook wbook = new XSSFWorkbook();
XSSFSheet wsheet = wbook.createSheet(sheetTitle);
wbook.setSheetName(0, sheetTitle);
IRowSet rs = getResultSet(request);
// 设置表头和标题的样式，填充数据
addHeadRow(wsheet, wbook, rs.size(),request);
// 把数据填充到EXCEL中
setDate(wsheet, wbook, rs);
// 获得响应流，并把数据EXCEL写入硬盘中
ServletOutputStream ouputStream = response.getOutputStream();
wbook.write(ouputStream); // 输出excel
ouputStream.flush();
ouputStream.close();
// FileOutputStream fout = new FileOutputStream("w:/" + sheetTitle
// + ".xls");
// wbook.write(fout);
// fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void exportBillAction(HttpServletRequest request,HttpServletResponse response, ModelMap modelMap)throws SHRWebException {
// 前台查询参数
downloadAction(request, response, modelMap);
}
}
查询和导出了就可以用了..
视图怎么配?
...
