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"; }