需求:
1. OOTB
2. 两个List(Projects, Engagements)的数据导出到Excel,Engagements通过查阅项 Project ID 关联Projects的
Project ID Field
3.Engagements和Projects各自还有Lookup类型的Field,Projects还有Metadata Column
事先
方案一:
借助SPList的View,通过连接
/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=&View=&CacheControl=1
实现导出, 失败原因: (1).通过Lookup字段的关联Currency,Lookup,MetaData,Choice字段不能被附带到SPView中 (2).得到一些不需要的Field (3).数据的格式问题 方案二: 通过XLSTListViewWebPart,修改CAMl进行Join得到数据,javascript查找所有的数据,然后导出 失败原因: (1)Join时同样很多数据不能获取 方案三: ECMScript各自获得所有数据,通过Engagements每个Item的Project ID得到Projects的Item 失败原因: (1):效率低下 (2):得不到完整数据,例如Projects的Item不被Lookup,即Left Join不能 方案四: Silverlight Webpart + ListData.svc 失败原因: (1)对于MetaData无能为力,如读者有好的方案,非常感谢 (2)严格的linq语法 (2)执行性能不太乐观 (3)Silverlight Webpart读取超过5S,第一次抛出异常信息 (4)Silverlight Webpart的SaveFileDialog必须由用户出发,如果你点击后执行数据查询,然后弹出,不可行,因为是异步调用,不会弹出对话框,也许读者说,那先弹出对话框,然后执行查询,这有点。。。, 可以参考
方案五: ListFormWebPart + SilverLight.xap(客户端缓存+SharePoint silverlight Client Object) 以<object></object>注入SilverLight程序,在UserControl_Loaded后执行数据查询,点击导出时,读取客户端缓存 代价也很明显,每次加载时都缓存了数据 参考: ListFormWebPart参考 缓存参考: http://www.pin5i.com/showtopic-25135.html 关于Linq 的Left Join 参考 http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/ 方案六: 期待你的完美解决方案 最后放一点代码: 执行导出代码: Cache是自定义的Cache类
join代码 取FieldValue代码,特殊的是 多行文本和MetaData的数据, 多行文本中有个叫什么Zero‐width spaces的东西,还有个同事提出格式化XMLDocument或者成html控件后取值,好像silverlight中不支持XMLDocument,没仔细研究。 MetaData的数据个是 value|Guid,多选的还未测试 再多的代码就不敢贴了,牵涉客户数据的问题了,呵呵,希望能给大家带来帮助,也希望能多交流。
{
Cache.Instance["RItems"] = "";
LoadDataFromServer();
}
{
try
{
var obj = Cache.Instance["RItems"];
{
reportItems = Cache.Instance["RItems"] as List<ReportItem>;
List<string> headers = GetHeaders();
ExportReport.Export(reportItems, headers);
}
else
{
MessageBox.Show("Data is loading from server, Please try it later.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
join e in _eItemCol on
new { PID = p[FieldNames.Project_x0020_ID].ToString() } equals new { PID = (e[FieldNames.Project_x0020_ID] as FieldLookupValue).LookupValue }
into joinPE
from ee in joinPE.DefaultIfEmpty()
orderby p[FieldNames.Project_x0020_ID] ascending
select
new ReportIte
/// get field value
/// </summary>
/// <param name="obj"></param>
/// <param name="isHtml"></param>
/// <param name="isMetaData"></param>
/// <returns></returns>
public static string GetCommenFieldValue(object obj, bool isHtml, bool isMetaData)
{
string retValue = string.Empty;
try
{
if (obj == null)
{
return string.Empty;
}
else
{
string type = obj.GetType().Name;
switch (type)
{
case "FieldUserValue":
retValue = (obj as FieldUserValue).LookupValue;
break;
case "FieldUserValue[]":
FieldUserValue[] userValues = obj as FieldUserValue[];
for (int i = 0; i < userValues.Length; i++)
{
if (i != userValues.Length - 1)
{
retValue += userValues[i].LookupValue + ",";
}
else
{
retValue += userValues[i].LookupValue;
}
}
break;
case "FieldLookupValue":
retValue = (obj as FieldLookupValue).LookupValue;
break;
case "FieldLookupValue[]":
FieldLookupValue[] lvs = obj as FieldLookupValue[];
for (int i = 0; i < lvs.Length; i++)
{
if (i != lvs.Length - 1)
{
retValue += lvs[i].LookupValue + ",";
}
else
{
retValue += lvs[i].LookupValue;
}
}
break;
case "String":
if (isHtml)
{
retValue = Striphtml(obj.ToString());
}
else if (isMetaData)
{
string[] strs = obj.ToString().Split('|');
for (int i = 0; i < strs.Length; i++)
{
if (i % 2 == 0)
{
if (i != strs.Length - 2)
{
retValue += strs[i] + ',';
}
else
{
retValue += strs[i];
}
}
}
}
else
{
retValue = obj.ToString();
}
retValue = obj.ToString();
break;
}
return retValue;
}
}
catch (Exception ex)
{
return string.Empty;
}
}
/// get content from html format string
/// </summary>
/// <param name="htmlString"></param>
/// <returns></returns>
public static string Striphtml(string htmlString)
{
Regex regex = new Regex(@"<[^>]+>|</[^>]+>");
htmlString = regex.Replace(htmlString, "");
htmlString = htmlString.Replace("\r\n", "");
//special char
char[] cs = htmlString.ToCharArray();
for (int i = cs.Length; i > 0; i--)
{
Int64 v = (Int64)cs[i - 1];
if (v == 8203 || v == 8208 || v == 8209)
{
cs[i - 1] = ' ';
}
}
htmlString = new string(cs);
return htmlString.Trim();
}