public dynamic evaluate(string formula)
{
dynamic result;
try
{
result = evaluator.Evaluate(formula);
}
catch (IOException)
{
result = -2146826273; // #Value!
}
return(result);
}
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 void ImportHDF5CompoundDS()
{
String sFileName = "test.h5";
ExcelReference selection = null;
MOIE.Application tapp = null;
MOIE.Workbook twb = null;
MOIE.Sheets twbSheets = null;
MOIE.Worksheet tws = null;
MOIE.Range tblr = null;
MOIE.Range trng = null;
MOIE.Range trng2 = null;
MOIE.ListObject tbl = null;
tapp = (ExcelDnaUtil.Application as MOIE.Application);
Boolean screenupdating_prior = tapp.ScreenUpdating;
MOIE.XlCalculation calculation_prior = tapp.Calculation;
RecordSetMD aRecordSetMD = null;
RecordSet aRecordSet = null;
//using isContinuing instead of throwing on last steps
Boolean isContinuing = true;
try {
//tapp.ScreenUpdating = false;
tapp.Calculation = MOIE.XlCalculation.xlCalculationManual;
int i, iP1, ii, iiP1, j, jP1, jj, jjP1;
using (OpenFileDialog aOpenFileDialog = new OpenFileDialog()) {
aOpenFileDialog.InitialDirectory = tapp.ActiveWorkbook.Path;
aOpenFileDialog.Filter = "HDF5 Files (*.h5)|*.h5|All Files (*.*)|*.*";
aOpenFileDialog.FilterIndex = 1;
aOpenFileDialog.RestoreDirectory = true;
aOpenFileDialog.CheckPathExists = true;
aOpenFileDialog.CheckFileExists = true;
aOpenFileDialog.FileName = sFileName;
aOpenFileDialog.AddExtension = true;
aOpenFileDialog.Title = "Import compound data from HDF5 (*.h5) File....";
if (aOpenFileDialog.ShowDialog() == DialogResult.OK)
{
sFileName = aOpenFileDialog.FileName;
}
else
{
isContinuing = false;
}
}
if (isContinuing)
{
String hDSPath = tapp.InputBox("Input path (with or without initial root /), if left as \"/\" and there is only 1 data set, that is used", "HDF5 path to dataset in file", "/");
aRecordSetMD = new RecordSetMD(RecordSetMDEnums.eMode.Input)
.cAs(RecordSetMDEnums.eType.HDF5)
.cFromFile(sFileName)
.cWithDataSetName(hDSPath)
.mReadMapFor(null, null, true)
;
aRecordSet = new RecordSet()
.cAsInput()
.mReadRecordSet(aRecordSetMD)
;
MessageBoxButtons msgboxbuttons = MessageBoxButtons.YesNoCancel;
DialogResult msgboxresponse;
msgboxresponse = MessageBox.Show("Write to a new sheet (Yes) or point to cell for the upper left corder (No)?", "Confirm", msgboxbuttons);
if (msgboxresponse == System.Windows.Forms.DialogResult.Cancel)
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
if (msgboxresponse == System.Windows.Forms.DialogResult.No)
{
try {
selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
trng = tapp.Evaluate(XlCall.Excel(XlCall.xlfReftext, selection, true)) as MOIE.Range;
trng2 = tapp.InputBox("Enter cell address (navigable)", "Output Location", trng.Address.ToString(), 100, 100, "", 0, 8) as MOIE.Range;
trng = null;
tws = trng2.Parent;
twb = tws.Parent;
}
catch {
throw new com.WDataSci.WDS.WDSException("Cancel");
}
}
else
{
twb = tapp.ActiveWorkbook;
twbSheets = twb.Sheets;
tws = twbSheets.Add();
twbSheets = null;
trng2 = tws.Cells[1, 1];
try {
tws.Name = aRecordSetMD.HDF5Matter.DSName;
}
catch (Exception e) {
String s = tapp.InputBox("Cannot name sheet to " + aRecordSetMD.HDF5Matter.DSName, "New Sheet Name", "Leave-As-Is", 100, 100, "");
if (!s.Equals("Leave-As-Is"))
{
try {
tws.Name = s;
}
catch (Exception) {
}
}
}
}
int nRows = aRecordSet.Records.Count;
int nColumns = aRecordSetMD.nColumns();
tapp.ScreenUpdating = false;
tapp.Calculation = MOIE.XlCalculation.xlCalculationManual;
for (jj = 0, jjP1 = 1; jj < nColumns; jj++, jjP1++)
{
trng2.Offset[0, jj].Value2 = aRecordSetMD.Column[jj].Name;
for (i = 0, iP1 = 1; i < nRows; i++, iP1++)
{
trng2.Offset[iP1, jj].Value2 = aRecordSet.Records_Orig[i][jj];
}
}
tblr = tws.Range[trng2, trng2.Offset[nRows, nColumns - 1]];
tbl = (MOIE.ListObject)tws.ListObjects.AddEx(MOIE.XlListObjectSourceType.xlSrcRange, tblr, null, MOIE.XlYesNoGuess.xlYes);
}
}
catch (com.WDataSci.WDS.WDSException e) {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
MessageBox.Show(e.getMessage());
}
catch (Exception e) {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
MessageBox.Show(e.Message);
}
finally {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
if (tapp.Calculation != calculation_prior)
{
tapp.Calculation = calculation_prior;
}
//Queuing up for GC
selection = null;
if (aRecordSetMD != null)
{
aRecordSetMD.Dispose();
}
aRecordSetMD = null;
if (aRecordSet != null)
{
aRecordSet.Dispose();
}
aRecordSet = null;
tapp = null;
twb = null;
twbSheets = null;
tws = null;
trng = null;
trng2 = null;
tblr = null;
tbl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return;
}
static void MainTask(string[] args)
{
// display usage if no arguments
if (args.Length == 0)
{
Console.WriteLine(Usage());
return;
}
var xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel._Workbook wbk = null;
Microsoft.Office.Interop.Excel.Range rng = null;
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlApp.Interactive = true;
// no-well established parsearg method
for (int i = 0; i < args.Length; i++)
{
var cmd = args[i];
while (cmd[0] == '-' || cmd[0] == '/')
{
cmd = cmd.Substring(1);
}
switch (cmd)
{
case "xlFileOpen":
case "xlFilePath":
var openPath = MakeFullPath(args[++i]);
Console.WriteLine($"> Open Workbook {openPath}");
if (wbk != null)
{
wbk.Close(SaveChanges: false);
}
wbk = xlApp.Workbooks.Open(openPath);
break;
case "xlFileNew":
Console.WriteLine($"> New Workbook");
if (wbk != null)
{
wbk.Close(SaveChanges: false);
}
wbk = xlApp.Workbooks.Add();
break;
case "xlFileSave":
Console.WriteLine($"> Save Workbook");
wbk.Save();
break;
case "xlFileSaveAs":
var savePath = MakeFullPath(args[++i]);
Console.WriteLine($"> Save Workbook as {savePath}");
wbk.SaveAs(savePath);
break;
case "xlEvalMacro":
var macro = args[++i];
Console.WriteLine($"> Evaluate macro {macro}");
xlApp.Evaluate(macro);
break;
case "xlRefreshLeftToRight":
Console.WriteLine($"> Refresh sheets left to right");
foreach (Microsoft.Office.Interop.Excel._Worksheet wsh in wbk.Worksheets)
{
if (wsh.Visible == XlSheetVisibility.xlSheetVisible)
{
wsh.Calculate();
}
}
break;
case "xlRngGet":
var rngGetAddr = args[++i];
Console.WriteLine($"> Get Range {rngGetAddr}");
rng = xlApp.Range[rngGetAddr];
Console.WriteLine(rng.Value);
break;
case "xlRngSet":
var rngSetAddr = args[++i];
var rngSetValue = args[++i];
Console.WriteLine($"> Set Range {rngSetAddr} {rngSetValue}");
rng = xlApp.Range[rngSetAddr];
rng.Value = rngSetValue;
break;
case "timeout":
i++;
break;
default:
throw new Exception($"> Unexpected command {cmd}");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Environment.Exit(1);
}
finally
{
foreach (Microsoft.Office.Interop.Excel._Workbook wbk in xlApp.Workbooks)
{
wbk.Close(SaveChanges: false);
}
xlApp.Quit();
Console.WriteLine("Done.");
}
}
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);
}