/**
* 替换xls模板中${vo.id}的字段
*
* @param book
* @param dataVO
* @param voName
* @throws Exception
*/
public static void createCellByOgnl(WritableSheet sheet, Object dataVO, String voName)
throws Exception {
// OGNL替换
String field;
for (int row = 0; row < 100; row++) {
for (int col = 0; col < 50; col++) {
WritableCell c = sheet.getWritableCell(col, row);
if (c.getClass() == EmptyCell.class || c.getClass() == Blank.class) continue;
Label label = (Label) c;
String label_str = GetTrimString(label.getString());
if (!isEmpty(label.getString()) && label_str.startsWith("${" + voName + ".")) {
String value = C.EMPTY_STRING;
try {
field =
label_str.replaceAll("\\$\\{vo\\.", C.EMPTY_STRING).replaceAll("}", C.EMPTY_STRING);
value = (String) UtilTool.GetFieldValue(dataVO, field);
} catch (Exception e) {
e.printStackTrace();
}
// ognl表达式的单元格必须被替换
label.setString(value);
}
}
}
}
/**
* 生成List<List<Strng>>类型的数据
*
* @param request
* @param response
* @param modelMap
* @return
*/
public static List<List<String>> buildXlsData(List<Object> dataList, String keys[]) {
final List<List<String>> xlsDataList = new ArrayList<List<String>>();
for (Object vo : dataList) {
List<String> row = new ArrayList<String>();
for (String key : keys) {
row.add(StringValue(UtilTool.GetFieldValue(vo, key)));
}
xlsDataList.add(row);
}
return xlsDataList;
}
/**
* 将voList转换为List<List<String>>
*
* @param voList
* @param filed
* @return
* @throws Exception
*/
public static List<List<String>> GetXlsList(List voList, String[] filed) throws Exception {
List<List<String>> returnList = new ArrayList<List<String>>();
for (Object obj : voList) {
List<String> row = new ArrayList<String>();
for (String key : filed) {
String value = StringValue((UtilTool.GetFieldValue(obj, key)));
row.add(value);
}
returnList.add(row);
}
return returnList;
}
/**
* Response创建输出.xsl文件,只有一张工作表
*
* @param response
* @param xlsList
* @param filename
*/
public static void ResponseXls(
HttpServletResponse response, String[] titleList, List voList, String[] fieldList)
throws Exception {
List<List<String>> xlsList = new ArrayList<List<String>>();
// title
List<String> row1 = new ArrayList<String>();
for (String title : titleList) row1.add(StringValue(title));
xlsList.add(row1);
// data
for (Object obj : voList) {
List<String> row = new ArrayList<String>();
for (String field : fieldList) row.add(StringValue(UtilTool.GetFieldValue(obj, field)));
xlsList.add(row);
}
ResponseXls(response, xlsList, null);
}
/**
* 函数(不考虑复杂的用法,只考虑函数的基本功能)
*
* @param callName --函数名称
* @param paList --输入参数
* @param returnType --返回种类
* @param rsClass --封装游标集合的类
* @return
* @throws Exception
*/
private static Object executeFunction(
String callName, List<Object> paList, int returnType, Class rsType, Connection con)
throws Exception {
OracleCallableStatement call = null;
// 防止依赖注入
if (paList != null && paList.size() > 0)
for (int i = 0; i < paList.size(); i++)
if (paList.get(i) != null && paList.get(i).getClass() == String.class)
if (((String) paList.get(i)).contains("'"))
paList.set(i, UtilTool.ClearInfuse((String) paList.get(i)));
// 执行函数的字符串
String CallFunction = "{? = " + DBTool.GetCallString(callName, paList).substring(1);
try {
call = (OracleCallableStatement) con.prepareCall(CallFunction);
// 绑定变量,只考虑使用函数返回一个结果 "{?="
call.registerOutParameter(1, returnType);
// 填充参数,注意从第2个"?"开始
List<Integer> paTypeList = DBTool.getPaTypeList(paList);
for (int i = 0; paList != null && i < paList.size(); i++)
DBTool.CallBindValue(con, call, paList.get(i), paTypeList.get(i), i + 2);
/** ---------------*后台打印----------------------- */
P.printFunction(CallFunction, paList);
/** ---------------*后台打印----------------------- */
// 执行
call.execute();
Object returnObj = DBTool.GetJavaValueByDBMeta(call, returnType, 1);
if (returnObj.getClass() == ResultSet.class || returnType == OracleTypes.CURSOR)
return DBTool.GetRsList((ResultSet) returnObj, rsType);
else return returnObj;
} catch (Exception e) {
throw e;
} finally {
if (call != null) call.close();
}
}