private void DataExplorerNodeResultsGrid_ExportToExcel()
{
if (DataExplorerTreeView.CheckedNodes.Count == 0)
{
return;
}
Boolean usesEntityCurrentMailingAddress = false;
Boolean usesEntityCurrentContactInformation = false;
Boolean usesMemberCurrentEnrollment = false;
Boolean usesMemberCurrentEnrollmentCoverage = false;
Boolean usesMemberCurrentEnrollmentPcp = false;
System.Xml.XmlElement styleElement;
System.Xml.XmlElement fontElement;
System.Xml.XmlElement column;
System.Xml.XmlElement row;
System.Xml.XmlElement cell;
System.Xml.XmlElement cellData;
Int32 columnIndex = 0;
System.Xml.XmlDocument excelDocument = new System.Xml.XmlDocument();
System.Xml.XmlDeclaration xmlDeclaration = excelDocument.CreateXmlDeclaration("1.0", "utf-8", String.Empty);
excelDocument.InsertBefore(xmlDeclaration, excelDocument.DocumentElement);
excelDocument.AppendChild(excelDocument.CreateProcessingInstruction("mso-application", "progid=\"Excel.Sheet\""));
#region Create Workbook and Worksheet and Styles
System.Xml.XmlElement workbookElement = excelDocument.CreateElement("Workbook");
workbookElement.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
workbookElement.SetAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
workbookElement.SetAttribute("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
workbookElement.SetAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel");
excelDocument.AppendChild(workbookElement);
System.Xml.XmlElement stylesCollectionElement = excelDocument.CreateElement("Styles");
workbookElement.AppendChild(stylesCollectionElement);
styleElement = excelDocument.CreateElement("Style");
styleElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "ID", "urn:schemas-microsoft-com:office:spreadsheet", "StyleNormal"));
stylesCollectionElement.AppendChild(styleElement);
fontElement = excelDocument.CreateElement("Font");
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "FontName", "urn:schemas-microsoft-com:office:spreadsheet", "Arial"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "x", "Family", "urn:schemas-microsoft-com:office:excel", "Swiss"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Size", "urn:schemas-microsoft-com:office:spreadsheet", "8"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Color", "urn:schemas-microsoft-com:office:spreadsheet", "#000000"));
styleElement.AppendChild(fontElement);
styleElement = excelDocument.CreateElement("Style");
styleElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "ID", "urn:schemas-microsoft-com:office:spreadsheet", "StyleBold"));
stylesCollectionElement.AppendChild(styleElement);
fontElement = excelDocument.CreateElement("Font");
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "FontName", "urn:schemas-microsoft-com:office:spreadsheet", "Arial"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "x", "Family", "urn:schemas-microsoft-com:office:excel", "Swiss"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Size", "urn:schemas-microsoft-com:office:spreadsheet", "8"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Color", "urn:schemas-microsoft-com:office:spreadsheet", "#000000"));
fontElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Bold", "urn:schemas-microsoft-com:office:spreadsheet", "1"));
styleElement.AppendChild(fontElement);
System.Xml.XmlElement worksheetElement = excelDocument.CreateElement("Worksheet");
worksheetElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Name", "urn:schemas-microsoft-com:office:spreadsheet", "Data"));
workbookElement.AppendChild(worksheetElement);
System.Xml.XmlElement worksheetTableElement = excelDocument.CreateElement("Table");
worksheetTableElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "StyleID", "urn:schemas-microsoft-com:office:spreadsheet", "StyleNormal"));
worksheetTableElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "DefaultRowHeight", "urn:schemas-microsoft-com:office:spreadsheet", "15"));
worksheetElement.AppendChild(worksheetTableElement);
#endregion
#region Create Header Row
System.Xml.XmlElement headerRow = excelDocument.CreateElement("Row");
columnIndex = 0;
foreach (Telerik.Web.UI.RadTreeNode currentCheckedNode in DataExplorerTreeView.CheckedNodes)
{
columnIndex = columnIndex + 1;
column = excelDocument.CreateElement("Column");
column.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Width", "urn:schemas-microsoft-com:office:spreadsheet", "100"));
column.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "StyleID", "urn:schemas-microsoft-com:office:spreadsheet", "StyleNormal"));
worksheetTableElement.AppendChild(column);
cell = excelDocument.CreateElement("Cell");
cell.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "StyleID", "urn:schemas-microsoft-com:office:spreadsheet", "StyleBold"));
cell.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Index", "urn:schemas-microsoft-com:office:spreadsheet", columnIndex.ToString()));
headerRow.AppendChild(cell);
cellData = excelDocument.CreateElement("Data");
cellData.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Type", "urn:schemas-microsoft-com:office:spreadsheet", "String"));
cellData.InnerText = currentCheckedNode.Text;
cell.AppendChild(cellData);
usesEntityCurrentMailingAddress |= currentCheckedNode.Value.StartsWith("Entity.CurrentMailingAddress.");
usesEntityCurrentContactInformation |= currentCheckedNode.Value.StartsWith("Entity.CurrentContactInformation");
usesMemberCurrentEnrollment |= currentCheckedNode.Value.StartsWith("CurrentEnrollment.");
usesMemberCurrentEnrollmentCoverage |= currentCheckedNode.Value.StartsWith("CurrentEnrollmentCoverage");
usesMemberCurrentEnrollmentPcp |= currentCheckedNode.Value.StartsWith("CurrentEnrollmentPcp");
}
worksheetTableElement.AppendChild(headerRow);
#endregion
#region Create Data Rows
// RETREIVE LIST OF MEMBERS AND PRE-CACHE RELATED DATA
List <Client.Core.Member.Member> members = MercuryApplication.DataExplorerNodeResultsGetForMember(NodeInstanceId, 1, NodeInstanceCount);
List <Client.Core.Member.MemberEnrollment> allMemberEnrollments = null;
List <Client.Core.Member.MemberEnrollmentCoverage> allMemberEnrollmentCoverages = null;
List <Client.Core.Member.MemberEnrollmentPcp> allMemberEnrollmentPcps = null;
// THESE ITEMS ARE AUTOMATICALLY CACHED AND AVAILABLE
if (usesEntityCurrentMailingAddress)
{
MercuryApplication.DataExplorerNodeResultsGetForMemberEntityCurrentAddress(NodeInstanceId, 1, NodeInstanceCount);
}
if (usesEntityCurrentContactInformation)
{
MercuryApplication.DataExplorerNodeResultsGetForMemberEntityCurrentContactInformation(NodeInstanceId, 1, NodeInstanceCount);
}
// THESE ITEMS MUST BE STORED AND USED LOCALLY (COULD BE LARG RESULT SETS!)
if ((usesMemberCurrentEnrollment) || (usesMemberCurrentEnrollmentCoverage) || (usesMemberCurrentEnrollmentPcp))
{
// MUST GET CURRENT ENROLLMENTS TO WALK TO CHILD OBJECTS COVERAGE AND PCP
allMemberEnrollments = MercuryApplication.DataExplorerNodeResultsGetForMemberCurrentEnrollment(NodeInstanceId, 1, NodeInstanceCount);
if (usesMemberCurrentEnrollmentCoverage)
{
allMemberEnrollmentCoverages = MercuryApplication.DataExplorerNodeResultsGetForMemberCurrentEnrollmentCoverage(NodeInstanceId, 1, NodeInstanceCount);
}
if (usesMemberCurrentEnrollmentPcp)
{
allMemberEnrollmentPcps = MercuryApplication.DataExplorerNodeResultsGetForMemberCurrentEnrollmentPcp(NodeInstanceId, 1, NodeInstanceCount);
}
}
foreach (Client.Core.Member.Member currentMember in members)
{
row = excelDocument.CreateElement("Row");
columnIndex = 0;
#region Precache Data Elements
// PRECACHE CURRENT MEMBER ENROLLMENT FOR MULTI-PROPERTY ACCESS
// MUST GET CURRENT ENROLLMENTS TO WALK TO CHILD OBJECTS COVERAGE AND PCP
Client.Core.Member.MemberEnrollment currentMemberEnrollment = null;
if ((usesMemberCurrentEnrollment) || (usesMemberCurrentEnrollmentCoverage) || (usesMemberCurrentEnrollmentPcp))
{
List <Client.Core.Member.MemberEnrollment> filteredMemberEnrollment =
(from memberEnrollment in allMemberEnrollments
where memberEnrollment.MemberId == currentMember.Id
select memberEnrollment).ToList();
if (filteredMemberEnrollment.Count > 0)
{
currentMemberEnrollment = filteredMemberEnrollment[0];
}
}
// PRECACHE CURRENT MEMBER ENROLLMENT COVERAGE FOR MULTI-PROPERTY ACCESS
Client.Core.Member.MemberEnrollmentCoverage currentMemberEnrollmentCoverage = null;
if ((usesMemberCurrentEnrollmentCoverage) && (currentMemberEnrollment != null))
{
List <Client.Core.Member.MemberEnrollmentCoverage> filteredMemberEnrollmentCoverage =
(from memberEnrollmentCoverage in allMemberEnrollmentCoverages
where memberEnrollmentCoverage.MemberEnrollmentId == currentMemberEnrollment.Id
select memberEnrollmentCoverage).ToList();
if (filteredMemberEnrollmentCoverage.Count > 0)
{
currentMemberEnrollmentCoverage = filteredMemberEnrollmentCoverage[0];
}
}
Client.Core.Member.MemberEnrollmentPcp currentMemberEnrollmentPcp = null;
if ((usesMemberCurrentEnrollmentPcp) && (currentMemberEnrollment != null))
{
List <Client.Core.Member.MemberEnrollmentPcp> filteredMemberEnrollmentPcp =
(from memberEnrollmentPcp in allMemberEnrollmentPcps
where memberEnrollmentPcp.MemberEnrollmentId == currentMemberEnrollment.Id
select memberEnrollmentPcp).ToList();
if (filteredMemberEnrollmentPcp.Count > 0)
{
currentMemberEnrollmentPcp = filteredMemberEnrollmentPcp[0];
}
}
#endregion
#region Create Cells and Data Values
foreach (Telerik.Web.UI.RadTreeNode currentCheckedNode in DataExplorerTreeView.CheckedNodes)
{
columnIndex = columnIndex + 1;
String contentProperty = String.Empty;
Object contentValueObject = null;
String contentValue = String.Empty;
if (currentCheckedNode.Value.StartsWith("CurrentEnrollment."))
{
if (currentMemberEnrollment != null)
{
contentProperty = currentCheckedNode.Value.Substring("CurrentEnrollment.".Length, currentCheckedNode.Value.Length - "CurrentEnrollment.".Length);
contentValueObject = Mercury.Server.CommonFunctions.GetPropertyValue(currentMemberEnrollment, contentProperty);
}
}
else if (currentCheckedNode.Value.StartsWith("CurrentEnrollmentCoverage."))
{
if (currentMemberEnrollment != null)
{
contentProperty = currentCheckedNode.Value.Substring("CurrentEnrollmentCoverage.".Length, currentCheckedNode.Value.Length - "CurrentEnrollmentCoverage.".Length);
contentValueObject = Mercury.Server.CommonFunctions.GetPropertyValue(currentMemberEnrollmentCoverage, contentProperty);
}
}
else if (currentCheckedNode.Value.StartsWith("CurrentEnrollmentPcp."))
{
if (currentMemberEnrollment != null)
{
contentProperty = currentCheckedNode.Value.Substring("CurrentEnrollmentPcp.".Length, currentCheckedNode.Value.Length - "CurrentEnrollmentPcp.".Length);
contentValueObject = Mercury.Server.CommonFunctions.GetPropertyValue(currentMemberEnrollmentPcp, contentProperty);
}
}
else
{
contentValueObject = Mercury.Server.CommonFunctions.GetPropertyValue(currentMember, currentCheckedNode.Value);
}
if (contentValueObject != null)
{
contentValue = contentValueObject.ToString();
}
cell = excelDocument.CreateElement("Cell");
cell.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Index", "urn:schemas-microsoft-com:office:spreadsheet", columnIndex.ToString()));
row.AppendChild(cell);
cellData = excelDocument.CreateElement("Data");
cellData.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "ss", "Type", "urn:schemas-microsoft-com:office:spreadsheet", "String"));
cellData.InnerText = contentValue;
cell.AppendChild(cellData);
}
#endregion
worksheetTableElement.AppendChild(row);
}
#endregion
System.Xml.XmlElement worksheetAutoFilterElement = excelDocument.CreateElement("AutoFilter");
worksheetAutoFilterElement.SetAttributeNode(XmlDocument_CreateAttribute(excelDocument, "x", "Range", "urn:schemas-microsoft-com:office:excel", "R1C1:R1C1"));
worksheetAutoFilterElement.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:excel");
worksheetElement.AppendChild(worksheetAutoFilterElement);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=DataExplorerResults.xml");
Response.AddHeader("Content-Length", excelDocument.OuterXml.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.OutputStream.Write(new System.Text.ASCIIEncoding().GetBytes(excelDocument.OuterXml.ToCharArray()), 0, excelDocument.OuterXml.Length);
Response.End();
return;
}
