public bool SaveMapInfo(Workbook workbook)
{
//clear old map information xml
foreach (CustomXMLPart curXmlPart in workbook.CustomXMLParts)
{
if (curXmlPart.BuiltIn)
{
continue;
}
XmlObject customObject = ObjectSerializeHelper.Deserialize <XmlObject>(curXmlPart.XML);
if (customObject.ContentType == ContentType.PdeMapInfo)
{
curXmlPart.Delete();
break;
}
}
//add new map information
CustomXMLPart xmlPart = workbook.CustomXMLParts.Add();
string xmlContent = ObjectSerializeHelper.SerializeToString <MapInfo>(mapInfo);
XmlObject excelContent = new XmlObject(xmlContent, ContentType.PdeMapInfo);
xmlPart.LoadXML(ObjectSerializeHelper.SerializeToString <XmlObject>(excelContent));
//Generate new xsd file and import it.
GenXsd genXsd = new GenXsd();
prepareData(genXsd);
XmlMap xmap = genXsd.ImportXsd(workbook);
//add xpath for each mapped cell and table
foreach (MapNode mn in mapInfo.Maps)
{
if (mn.type == MapType.SingleCell)
{
Range c = workbook.Application.get_Range(mn.target);
c.XPath.SetValue(xmap, mn.xPath);
}
else
{
string[] target = mn.target.Split('!');
Worksheet worksheet = workbook.Sheets[target[0]];
ListObject lst = worksheet.ListObjects[target[1]];
foreach (TabCol tc in mn.columns)
{
lst.ListColumns[tc.columnName].XPath.SetValue(xmap, tc.xPath);
}
}
}
return(true);
}
//this is tempory method just for test. acutally the data should be producted while generate OSQL
private void prepareData(GenXsd genXsd)
{
genXsd.selectedTables = new List <string>();
genXsd.selectedColumns = new List <List <string> >();
genXsd.tabColsType = new List <List <string> >();
List <string> emCols = new List <string>();
List <string> emColTyps = new List <string>();
emCols.Add("FirstName");
emColTyps.Add("string");
emCols.Add("LastName");
emColTyps.Add("string");
emCols.Add("Title");
emColTyps.Add("string");
emCols.Add("Grade");
emColTyps.Add("string");
emCols.Add("Salary");
emColTyps.Add("decimal");
List <string> tfCols2 = new List <string>();
List <string> tfColTyps2 = new List <string>();
tfCols2.Add("FirstName");
tfColTyps2.Add("date");
tfCols2.Add("LastName");
tfColTyps2.Add("string");
tfCols2.Add("Birthday");
tfColTyps2.Add("date");
tfCols2.Add("Title");
tfColTyps2.Add("string");
tfCols2.Add("Grade");
tfColTyps2.Add("string");
tfCols2.Add("Salary");
tfColTyps2.Add("decimal");
List <string> mxCols3 = new List <string>();
List <string> mxColTyps3 = new List <string>();
mxCols3.Add("Since");
mxColTyps3.Add("date");
mxCols3.Add("Title");
mxColTyps3.Add("string");
mxCols3.Add("Salary");
mxColTyps3.Add("decimal");
//xpath prepare
foreach (MapNode mn in mapInfo.Maps)
{
if (mn.type == MapType.SingleCell)
{
if (genXsd.selectedTables.IndexOf("Employee") < 0)
{
genXsd.selectedTables.Add("Employee");
genXsd.selectedColumns.Add(emCols);
genXsd.tabColsType.Add(emColTyps);
}
}
else
{
if (mn.treeNode.IndexOf("\\My Staff") > 0)
{
if (genXsd.selectedTables.IndexOf("Staff") < 0)
{
genXsd.selectedTables.Add("Staff");
genXsd.selectedColumns.Add(tfCols2);
genXsd.tabColsType.Add(tfColTyps2);
}
}
else if (mn.treeNode.IndexOf("\\My Matrix") > 0)
{
if (genXsd.selectedTables.IndexOf("JobHistory") < 0)
{
genXsd.selectedTables.Add("JobHistory");
genXsd.selectedColumns.Add(mxCols3);
genXsd.tabColsType.Add(mxColTyps3);
}
}
}
}
//genXsd.selectedTables.Add("Employee");
//genXsd.selectedTables.Add("JobHistory");
if (mapInfo.Maps.Count < 1)
{
return;
}
Dictionary <string, string> xPaths = new Dictionary <string, string>();
xPaths.Add("Employee\\First Name", "/PdeData/Employee/FirstName");
xPaths.Add("Employee\\Last Name", "/PdeData/Employee/LastName");
xPaths.Add("Employee\\Title", "/PdeData/Employee/Title");
xPaths.Add("Employee\\Grade", "/PdeData/Employee/Grade");
xPaths.Add("Employee\\Salary", "/PdeData/Employee/Salary");
xPaths.Add("Employee\\My Staff\\+First Name", "/PdeData/Employee/Staff/FirstName");
xPaths.Add("Employee\\My Staff\\+Last Name", "/PdeData/Employee/Staff/LastName");
xPaths.Add("Employee\\My Staff\\+Title", "/PdeData/Employee/Staff/Title");
xPaths.Add("Employee\\My Staff\\+Grade", "/PdeData/Employee/Staff/Grade");
xPaths.Add("Employee\\My Staff\\+Birthday", "/PdeData/Employee/Staff/Birthday");
xPaths.Add("Employee\\My Staff\\+Salary", "/PdeData/Employee/Staff/Salary");
xPaths.Add("Employee\\My Matrix\\_Since", "/PdeData/Employee/JobHistory/Since");
xPaths.Add("Employee\\My Matrix\\_Title", "/PdeData/Employee/JobHistory/Title");
xPaths.Add("Employee\\My Matrix\\_Salary", "/PdeData/Employee/JobHistory/Salary");
foreach (MapNode mn in mapInfo.Maps)
{
string xp = "";
if (mn.type == MapType.SingleCell)
{
xPaths.TryGetValue(mn.treeNode, out xp);
mn.xPath = xp;
}
else
{
foreach (TabCol tc in mn.columns)
{
xPaths.TryGetValue(tc.treeNode, out xp);
tc.xPath = xp;
}
}
}
//mapInfo.Maps[0].xPath = "/PdeData/Employee/LastName";
//if (mapInfo.Maps.Count < 2)
// return;
//mapInfo.Maps[1].xPath = "/PdeData/Employee/FirstName";
//if (mapInfo.Maps.Count < 3)
// return;
//mapInfo.Maps[2].xPath = "/PdeData/Employee/Salary";
//if (mapInfo.Maps.Count < 4 || mapInfo.Maps[3].columns.Count < 1)
// return;
//mapInfo.Maps[3].columns[0].xPath = "/PdeData/Employee/JobHistory/Since";
//if (mapInfo.Maps.Count < 4 || mapInfo.Maps[3].columns.Count < 2)
// return;
//mapInfo.Maps[3].columns[1].xPath = "/PdeData/Employee/JobHistory/Title";
//if (mapInfo.Maps.Count < 4 || mapInfo.Maps[3].columns.Count < 3)
// return;
//mapInfo.Maps[3].columns[2].xPath = "/PdeData/Employee/JobHistory/Salary";
}
