public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "sample.xlsx");
// Export all XML data from all XML Maps from the Workbook
for (int i = 0; i < workbook.Worksheets.XmlMaps.Count; i++)
{
// Access the XML Map
XmlMap map = workbook.Worksheets.XmlMaps[i];
// Exports its XML Data to file
workbook.ExportXml(map.Name, dataDir + map.Name + ".xml");
}
// ExEnd:1
}
public static void Run()
{
// ExStart:LinkCellsToXmlMapElements
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load sample workbook
Workbook wb = new Workbook(dataDir + "sample.xlsx");
// Access the Xml Map inside it
XmlMap map = wb.Worksheets.XmlMaps[0];
// Access first worksheet
Worksheet ws = wb.Worksheets[0];
// Map FIELD1 and FIELD2 to cell A1 and B2
ws.Cells.LinkToXmlMap(map.Name, 0, 0, "/root/row/FIELD1");
ws.Cells.LinkToXmlMap(map.Name, 1, 1, "/root/row/FIELD2");
// Map FIELD4 and FIELD5 to cell C3 and D4
ws.Cells.LinkToXmlMap(map.Name, 2, 2, "/root/row/FIELD4");
ws.Cells.LinkToXmlMap(map.Name, 3, 3, "/root/row/FIELD5");
// Map FIELD7 and FIELD8 to cell E5 and F6
ws.Cells.LinkToXmlMap(map.Name, 4, 4, "/root/row/FIELD7");
ws.Cells.LinkToXmlMap(map.Name, 5, 5, "/root/row/FIELD8");
// Save the workbook in xlsx format
wb.Save(dataDir + "output.xlsx");
// ExEnd:LinkCellsToXmlMapElements
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Load sample workbook
Workbook wb = new Workbook(sourceDir + "sampleLinkCellsToXmlMapElements.xlsx");
// Access the Xml Map inside it
XmlMap map = wb.Worksheets.XmlMaps[0];
// Access first worksheet
Worksheet ws = wb.Worksheets[0];
// Map FIELD1 and FIELD2 to cell A1 and B2
ws.Cells.LinkToXmlMap(map.Name, 0, 0, "/root/row/FIELD1");
ws.Cells.LinkToXmlMap(map.Name, 1, 1, "/root/row/FIELD2");
// Map FIELD4 and FIELD5 to cell C3 and D4
ws.Cells.LinkToXmlMap(map.Name, 2, 2, "/root/row/FIELD4");
ws.Cells.LinkToXmlMap(map.Name, 3, 3, "/root/row/FIELD5");
// Map FIELD7 and FIELD8 to cell E5 and F6
ws.Cells.LinkToXmlMap(map.Name, 4, 4, "/root/row/FIELD7");
ws.Cells.LinkToXmlMap(map.Name, 5, 5, "/root/row/FIELD8");
// Save the workbook in xlsx format
wb.Save(outputDir + "outputLinkCellsToXmlMapElements.xlsx");
Console.WriteLine("LinkCellsToXmlMapElements executed successfully.");
}
public XmlMap GetMap()
{
return(XmlMap.Create(
x => x.WithAttribute(() => Value1)
.WithAttribute(() => Value2)
.WithAttribute(() => Value3, () => _value3)
.WithAttribute(() => Value4, () => _value4)));
}
public virtual XmlMap GetMap()
{
return(XmlMap.Create(
x => x.WithElement("Element1", () => Value1)
.WithAttribute("Attribute1", () => Value2)
.WithElement("Element3", () => Value3, () => _value3)
.WithAttribute("Attribute2", () => Value4, () => _value4)));
}
private void radButton2_Click(object sender, EventArgs e)
{
if (txtXml.Text == "" || txtPlantilla.Text == "")
{
MensajeError("Debe seleccionar el Archivo Xml y la plantilla Excel ATS antes de hacer la importacion");
return;
}
else
{
try
{
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
xApp.Visible = true;
xApp.WindowState = XlWindowState.xlMaximized;
string caption = xApp.Caption;
IntPtr handler = FindWindow(null, caption);
SetForegroundWindow(handler);
Workbook excelWorkBook = xApp.Workbooks.Open(excelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
var ds = new DataSet();
ds.ReadXmlSchema(xmlFile);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlFile);
string rootName = xmlDoc.FirstChild.NextSibling.Name;
XmlMap xmlMap1 = excelWorkBook.XmlMaps.Add(ds.GetXmlSchema(), rootName);
var importResult = excelWorkBook.XmlImport(xmlFile, out xmlMap1, true, Type.Missing);
if (importResult == XlXmlImportResult.xlXmlImportValidationFailed)
{
MensajeOk("Importacion Exsitosa. Cierre esta ventana una vez haya terminado las importaciones");
}
else
{
MensajeError("No se pudo realizar la exportacion");
excelWorkBook.Close();
xApp.Workbooks.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
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);
}
public static void LoadExcelSheet(string name, byte[] resource, DataSet data, bool isXlMinimized)
{
//for faster processing, do not do screen updates yet
Globals.ThisAddIn.Application.ScreenUpdating = false;
_fileCounter++;
string workbook =
Path.Combine(
Path.GetTempPath(),
string.Format("{0}_{1}.xlsx", name, _fileCounter));
File.WriteAllBytes(workbook, resource);
wb = Globals.ThisAddIn.Application.Workbooks.Open(
workbook,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
if (isXlMinimized)
{
Globals.ThisAddIn.Application.WindowState = XlWindowState.xlMinimized;
}
IEnumerator xmlMaps = wb.XmlMaps.GetEnumerator();
while (xmlMaps.MoveNext())
{
XmlMap map = xmlMaps.Current as XmlMap;
if (map != null)
{
map.ImportXml(data.GetXml(), true);
break;
}
}
Globals.ThisAddIn.Application.ScreenUpdating = true;
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Load sample Excel file having Xml Map
Workbook wb = new Workbook(sourceDir + "sampleRootElementNameOfXmlMap.xlsx");
//Access first Xml Map inside the Workbook
XmlMap xmap = wb.Worksheets.XmlMaps[0];
//Print Root Element Name of Xml Map on Console
Console.WriteLine("Root Element Name Of Xml Map: " + xmap.RootElementName);
Console.WriteLine("FindRootElementNameOfXmlMap executed successfully.\r\n");
}
//extract the xml data file from excel archive file and import it into excel
public bool mergeData(Workbook workbook)
{
String xmlcontent = null;
XmlMap impMap = null;
foreach (XmlMap xmlmap in workbook.XmlMaps)
{
if (xmlmap.Name.Equals(GenXsd.PDE_XSD_MAP_NAME))
{
impMap = xmlmap;
break;
}
}
if (impMap == null)//cannot found import xsd, meaning this has been merged.
{
isFirstOpen = false;
return(true);
}
foreach (CustomXMLPart curXmlPart in workbook.CustomXMLParts)
{
if (curXmlPart.BuiltIn)
{
continue;
}
XmlObject customObject = ObjectSerializeHelper.Deserialize <XmlObject>(curXmlPart.XML);
if (customObject.ContentType == ContentType.PderXml)
{
xmlcontent = customObject.Content;
break;
}
}
if (xmlcontent == null || xmlcontent.Length < 1)
{
MessageBox.Show("The file format has been broken!");
workbook.Close();
return(false);
}
impMap.ImportXml(xmlcontent);
//delete the xsd for import data
impMap.Delete();
workbook.Save();
return(true);
}
static void Main(string[] args)
{
WebClientBase.BaseUrl = args.Length == 0? "localhost": args[0];
foreach (Person p in PersonClient.Instance.SelectAll())
{
PrintPerson(p);
}
XmlMap <string, Person> map = PersonClient.Instance.SelectMap();
foreach (KeyValuePair <string, Person> pair in map)
{
Console.WriteLine("{0}: {1} {2} ({3})",
pair.Key,
pair.Value.FirstName, pair.Value.LastName, pair.Value.Gender);
}
// Async call to server
//
PersonClient.Instance.SelectByKey(1, PrintPerson);
string strVal;
Guid guidVal;
int intVal = PersonClient.Instance.MethodWithOutParams(out strVal, out guidVal);
Console.WriteLine("int: {0}, str: {1}, guid: {2}", intVal, strVal, guidVal);
PersonClient.Instance.MethodWithOutParams(delegate(int i, string s, Guid g)
{
Console.WriteLine("[Callback] int: {0}, str: {1}, guid: {2}", i, s, g);
});
Console.WriteLine();
Console.WriteLine("Press [Enter] key to continue");
Console.WriteLine();
while (!Console.KeyAvailable)
{
Application.DoEvents();
Thread.Sleep(200);
}
Console.ReadKey(true);
}
//export the data into xml file, then merge to word.
//this method call after pde render and before pdw render.
public string exportXmlData(Workbook workbook, string xmlFileName)
{
XmlMap xm = null;
foreach (XmlMap map in workbook.XmlMaps)
{
if (PDE_EXP_XSD_MAP_NAME.Equals(map.Name))
{
xm = map;
break;
}
}
if (xm == null || !xm.IsExportable)
{
return("This excel do not contain a xsd to export data.");
}
workbook.SaveAsXMLData(xmlFileName, xm);
return("");
}
public XmlMap ImportXsd(Workbook workbook)
{
//clear the old pde xsd map if it existed.
XmlMaps xms = workbook.XmlMaps;
foreach (XmlMap map in xms)
{
if (PDE_XSD_MAP_NAME.Equals(map.Name))
{
map.Delete();
break;
}
}
//import new xsd
XmlMap xmap = xms.Add(CreateXsd());
xmap.Name = PDE_XSD_MAP_NAME;
return(xmap);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
//Load sample Excel file having Xml Map
Workbook wb = new Workbook(sourceDir + "sampleXmlMapQuery.xlsx");
//Access first XML Map
XmlMap xmap = wb.Worksheets.XmlMaps[0];
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Query Xml Map from Path - /MiscData
Console.WriteLine("Query Xml Map from Path - /MiscData");
ArrayList ret = ws.XmlMapQuery("/MiscData", xmap);
//Print returned ArrayList values
for (int i = 0; i < ret.Count; i++)
{
Console.WriteLine(ret[i]);
}
Console.WriteLine("");
//Query Xml Map from Path - /MiscData/row/Color
Console.WriteLine("Query Xml Map from Path - /MiscData/row/Color");
ret = ws.XmlMapQuery("/MiscData/row/Color", xmap);
//Print returned ArrayList values
for (int i = 0; i < ret.Count; i++)
{
Console.WriteLine(ret[i]);
}
Console.WriteLine("QueryCellAreasMappedToXmlMapPath executed successfully.\r\n");
}
public XmlMap ExportXsd(Workbook workbook)
{
//clear the old pde xsd map if it existed.
XmlMaps xms = workbook.XmlMaps;
foreach (XmlMap map in xms)
{
if (PDE_EXP_XSD_MAP_NAME.Equals(map.Name))
{
map.Delete();
break;
}
}
//import new xsd
XmlMap xmap = xms.Add(genExpXsd());
xmap.Name = PDE_EXP_XSD_MAP_NAME;
//DO NOT link the excel cell or table to xsd now, we will link them after render and delete the import xsd.
//linkToXsd(workbook);
return(xmap);
}
public bool linkToXsd(Workbook workbook)
{
XmlMap xmlMap = null;
foreach (XmlMap map in workbook.XmlMaps)
{
if (PDE_EXP_XSD_MAP_NAME.Equals(map.Name))
{
xmlMap = map;
break;
}
}
if (xmlMap == null)
{
return(false);
}
foreach (ExportItemMap itemMap in exportItems)
{
if (itemMap.mapType == ProntoDoc.Framework.CoreObject.MapType.singleCell)
{
Range c = workbook.Application.get_Range(itemMap.excelName);
c.XPath.SetValue(xmlMap, "/" + ROOT_ELEMENT + "/" + itemMap.treeNodeName);
}
else
{
string path = "/" + ROOT_ELEMENT + "/" + itemMap.treeNodeName;
string[] target = itemMap.excelName.Split('!');
Worksheet worksheet = workbook.Sheets[target[0]];
ListObject lst = worksheet.ListObjects[target[1]];
foreach (TableColumnMap col in itemMap.tabCols)
{
lst.ListColumns[col.columnName].XPath.SetValue(xmlMap, path + "/" + col.treeNodeName);
}
}
}
return(true);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook(sourceDir + "sampleExportXmlMapFromWorkbook.xlsx");
// Export all XML data from all XML Maps from the Workbook
for (int i = 0; i < workbook.Worksheets.XmlMaps.Count; i++)
{
// Access the XML Map
XmlMap map = workbook.Worksheets.XmlMaps[i];
// Exports its XML Data to file
workbook.ExportXml(map.Name, outputDir + map.Name + ".xml");
}
Console.WriteLine("ExportXmlMapFromWorkbook executed successfully.\r\n");
}
public XlXmlImportResult XmlImportXml(string data, out XmlMap importMap, object overwrite, object destination)
{
throw new NotImplementedException();
}
public void SaveAsXMLData(string filename, XmlMap map)
{
throw new NotImplementedException();
}
public XmlMap GetMap()
{
return(XmlMap.Create(
x => x.WithElement(() => Value1)
.WithAttribute(() => Value2)));
}
public void SaveAsXMLData(string filename, XmlMap map)
{
throw new NotImplementedException();
}
public XlXmlImportResult XmlImportXml(string data, out XmlMap importMap, object overwrite, object destination)
{
throw new NotImplementedException();
}
