private void AddExportXSD(string pdeFilePath, string stnName, PdeExports exportData)
{
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook workbook = app.Workbooks.Open(pdeFilePath);
try
{
Excel.XmlMaps maps = workbook.XmlMaps;
// delete old mapp
DeleteOldMap(maps, ProntoDoc.Framework.CoreObject.FrameworkConstants.PdeExportedMapName);
// gen new xsd
string xsdFile = GenExportedXSD(stnName, exportData);
// add new map
Excel.XmlMap map = maps.Add(xsdFile);
map.Name = ProntoDoc.Framework.CoreObject.FrameworkConstants.PdeExportedMapName;
// save
workbook.Save();
// delete xsd file
ProntoDoc.Framework.Utils.FileHelper.DeleteFile(xsdFile);
}
finally
{
((Excel._Workbook)workbook).Close();
((Excel._Application)app).Quit();
}
}
private void App_WorkbookBeforeXmlImport(Excel.Workbook Wb, Excel.XmlMap Map, string Url, bool IsRefresh, ref bool Cancel)
{
if (!Url.EndsWith(".xml"))
{
return;
}
Wb.Title = Url;
}
public static void ExportXmlMappedListToHDF5()
{
String sFileName = "test.h5";
ExcelReference selection = null;
MOIE.Application ma = null;
MOIE.Range mr = null;
MOIE.XmlMap aXmlMap = null;
MOIE.ListObject aListObject = null;
RecordSetMD aRecordSetMD = null;
//using isContinuing instead of throwing on last steps
Boolean isContinuing = true;
try {
selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
ma = (ExcelDnaUtil.Application as MOIE.Application);
mr = ma.Evaluate(XlCall.Excel(XlCall.xlfReftext, selection, true)) as MOIE.Range;
try {
aListObject = mr.ListObject;
aXmlMap = aListObject.XmlMap;
}
catch (Exception) {
throw new com.WDataSci.WDS.WDSException("Error: could not pull XmlMap from selection");
}
aRecordSetMD = new RecordSetMD(RecordSetMDEnums.eMode.Internal)
.cAs(RecordSetMDEnums.eType.HDF5, RecordSetMDEnums.eSchemaType.XSD)
;
aRecordSetMD.SchemaMatter.InputSchema = new XmlDocument();
aRecordSetMD.SchemaMatter.InputSchema.LoadXml(aXmlMap.Schemas.Item[1].XML);
MessageBoxButtons msgboxbuttons = MessageBoxButtons.YesNoCancel;
DialogResult msgboxresponse = MessageBox.Show("Write HDF5 file from XmlMap'd ListObject of selection?", "Confirm", msgboxbuttons);
isContinuing = (isContinuing && msgboxresponse == System.Windows.Forms.DialogResult.Yes);
if (isContinuing)
{
using (SaveFileDialog aSaveFileDialog = new SaveFileDialog()) {
aSaveFileDialog.InitialDirectory = ma.ActiveWorkbook.Path;
aSaveFileDialog.Filter = "HDF5 Files (*.h5)|*.h5|All Files (*.*)|*.*";
aSaveFileDialog.FilterIndex = 1;
aSaveFileDialog.RestoreDirectory = true;
aSaveFileDialog.FileName = sFileName;
aSaveFileDialog.AddExtension = true;
//aSaveFileDialog.CheckFileExists = true;
aSaveFileDialog.CheckPathExists = true;
aSaveFileDialog.Title = "Export XmlMap'd ListObject to HDF5 (*.h5) File....";
if (aSaveFileDialog.ShowDialog() == DialogResult.OK)
{
sFileName = aSaveFileDialog.FileName;
if (!sFileName.ToLower().EndsWith(".h5"))
{
sFileName += ".h5";
}
}
else
{
isContinuing = false;
}
}
}
if (isContinuing)
{
aRecordSetMD
.cToFile(sFileName)
.cWithDataSetName("RecordSet")
.mReadMapFor(null, null, true);
int nColumns = aRecordSetMD.nColumns();
if (aListObject.ListColumns.Count != nColumns)
{
throw new com.WDataSci.WDS.WDSException("ListObject Column Count Does Not Match Schema Node List Count!");
}
aRecordSetMD.HDF5Matter.mWriteRecordSet(aRecordSetMD, aListObject);
}
}
catch (com.WDataSci.WDS.WDSException e) {
MessageBox.Show(e.getMessage());
}
catch (Exception e) {
com.WDataSci.WDS.WDSException we = new com.WDataSci.WDS.WDSException("Error in ExportXmlMappedListToHDF5 to " + sFileName, e);
MessageBox.Show(we.getMessage());
}
finally {
selection = null;
aListObject = null;
aXmlMap = null;
mr = null;
ma = null;
if (aRecordSetMD != null)
{
aRecordSetMD.Dispose();
}
aRecordSetMD = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return;
}
public static String XSDUserInput()
{
//Typing for possible GC purposes
MOIE.Application tapp = null;
MOIE.Range trng = null;
MOIE.Range trng2 = null;
MOIE.XmlMap aXmlMap = null;
MOIE.ListObject aListObject = null;
MOIE.Workbook twb = null;
MOIE.Worksheet tws = null;
JniPMMLItem aJniPMMLItem = null;
XmlDocument aXmlDocument = null;
XmlNodeList aXmlNodeList = null;
String rv = "";
int h = -1;
Boolean bIsModelCached = true;
tapp = (ExcelDnaUtil.Application as MOIE.Application);
Boolean screenupdating_prior = tapp.ScreenUpdating;
MOIE.XlCalculation calculation_prior = tapp.Calculation;
try {
int i, j, iP1, jP1, ii, iiP1;
twb = tapp.ActiveWorkbook;
tws = twb.ActiveSheet;
String sFile = "?";
MessageBoxButtons msgboxbuttons = MessageBoxButtons.YesNoCancel;
DialogResult msgboxresponse;
bIsModelCached = false;
msgboxresponse = MessageBox.Show("Would you like to point to an XSD file (Yes/no)?", "Confirm", msgboxbuttons);
if (msgboxresponse == System.Windows.Forms.DialogResult.Cancel)
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
if (msgboxresponse == System.Windows.Forms.DialogResult.Yes)
{
using (OpenFileDialog aOpenFileDialog = new OpenFileDialog()) {
aOpenFileDialog.InitialDirectory = tapp.ActiveWorkbook.Path;
aOpenFileDialog.Filter = "XSD File (*.xsd)|*.xsd|All Files (*.*)|*.*";
aOpenFileDialog.FilterIndex = 1;
aOpenFileDialog.RestoreDirectory = true;
aOpenFileDialog.AddExtension = true;
aOpenFileDialog.DefaultExt = ".xsd";
aOpenFileDialog.CheckFileExists = true;
aOpenFileDialog.CheckPathExists = true;
aOpenFileDialog.Title = "XML Schema (XSD) File....";
if (aOpenFileDialog.ShowDialog() == DialogResult.OK)
{
sFile = aOpenFileDialog.FileName;
}
else
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
rv = com.WDataSci.WDS.Util.FetchFileAsString(sFile);
}
}
else
{
msgboxresponse = MessageBox.Show("Point to an XSD string in a cells (Yes) or leave unspecified (No)?", "Confirm", msgboxbuttons);
if (msgboxresponse == System.Windows.Forms.DialogResult.Cancel)
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
if (msgboxresponse == System.Windows.Forms.DialogResult.Yes)
{
try {
MOIE.Range trng3 = tapp.InputBox("Use an XSD as one string contained in a cell, enter cell address (navigable)", "XSD Input", "Entire XSD File as a String", 100, 100, "", 0, 8) as MOIE.Range;
sFile = trng3.Text;
trng3 = null;
if (!sFile.StartsWith("<?xml"))
{
if (sFile.IndexOf("!") < 0)
{
sFile = "'[" + tapp.ActiveWorkbook.Name + "]" + aListObject.DataBodyRange.Worksheet.Name + "'!" + sFile;
}
ExcelReference rf = XlCall.Excel(XlCall.xlfEvaluate, sFile) as ExcelReference;
trng3 = tapp.Evaluate(XlCall.Excel(XlCall.xlfReftext, rf, true)) as MOIE.Range;
sFile = trng3.Text;
rf = null;
trng3 = null;
rv = sFile;
}
else
{
throw new com.WDataSci.WDS.WDSException("Error, value not a valid XSD string");
}
}
catch {
throw new com.WDataSci.WDS.WDSException("Cancel");
}
}
}
}
catch (com.WDataSci.WDS.WDSException e) {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
if (!e.getMessage().Equals("Cancel"))
{
MessageBox.Show(e.getMessage() + "\n" + e.StackTrace.ToString());
}
}
catch (Exception e) {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
MessageBox.Show("Error!\n" + e.Message + "\n" + e.StackTrace.ToString());
}
finally {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
if (tapp.Calculation != calculation_prior)
{
tapp.Calculation = calculation_prior;
}
aListObject = null;
aXmlMap = null;
tapp = null;
trng = null;
trng2 = null;
twb = null;
tws = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(rv);
}