public static ExcelCalculationMode ToExcelCalculationMode(this Xl.XlCalculation xlCalculation)
{
switch(xlCalculation)
{
case Xl.XlCalculation.xlCalculationAutomatic:
return ExcelCalculationMode.Automatic;
case Xl.XlCalculation.xlCalculationManual:
return ExcelCalculationMode.Manual;
case Xl.XlCalculation.xlCalculationSemiautomatic:
return ExcelCalculationMode.SemiAutomatic;
default:
throw new ArgumentException(EnumHelper.UnexpectedEnumerationValueMessage(xlCalculation), nameof(xlCalculation));
}
}
public FreezeExcel(bool keepStatusVisible = true, bool keepScreenUpdating = false, bool keepEnabledEvent = false, bool keepCalculation = false)
{
lock (objSync)
{
requestsCpt++;
if (!ETKExcel.ExcelApplication.IsInEditMode())
{
if (requestsCpt == 1)
{
screenUpdating = ETKExcel.ExcelApplication.Application.ScreenUpdating;
enableEvents = ETKExcel.ExcelApplication.Application.EnableEvents;
displayStatusBar = ETKExcel.ExcelApplication.Application.DisplayStatusBar;
calculationMode = ETKExcel.ExcelApplication.Application.Calculation;
ETKExcel.ExcelApplication.Application.ScreenUpdating = keepScreenUpdating && screenUpdating;
ETKExcel.ExcelApplication.Application.EnableEvents = keepEnabledEvent && enableEvents;
ETKExcel.ExcelApplication.Application.DisplayStatusBar = keepStatusVisible && displayStatusBar;
ETKExcel.ExcelApplication.Application.Calculation = keepCalculation ? ETKExcel.ExcelApplication.Application.Calculation : ExcelInterop.XlCalculation.xlCalculationManual;
}
}
}
}
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;
}
public static void ImportCSVToXMLMappedList()
{
String sFileName = "test.csv";
ExcelReference selection;
MOIE.Application tapp;
MOIE.Range trng;
MOIE.Range trng2;
MOIE.Workbook twb;
MOIE.Sheets twbSheets;
MOIE.Worksheet tws;
MOIE.Range tblr;
MOIE.ListObject tbl;
MOIE.XmlMap aXmlMap;
tapp = (ExcelDnaUtil.Application as MOIE.Application);
Boolean screenupdating_prior = tapp.ScreenUpdating;
MOIE.XlCalculation calculation_prior = tapp.Calculation;
RecordSetMD aRecordSetMD;
RecordSet aRecordSet;
//using isContinuing instead of throwing on last steps
Boolean isContinuing = true;
try {
//tapp.ScreenUpdating = false;
tapp.Calculation = MOIE.XlCalculation.xlCalculationManual;
int i, iP1;
using (OpenFileDialog aOpenFileDialog = new OpenFileDialog()) {
aOpenFileDialog.InitialDirectory = tapp.ActiveWorkbook.Path;
aOpenFileDialog.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
aOpenFileDialog.FilterIndex = 1;
aOpenFileDialog.RestoreDirectory = true;
aOpenFileDialog.CheckPathExists = true;
aOpenFileDialog.CheckFileExists = true;
aOpenFileDialog.FileName = sFileName;
aOpenFileDialog.AddExtension = true;
aOpenFileDialog.DefaultExt = ".csv";
aOpenFileDialog.Title = "Import compound data from CSV (*.csv) File....";
if (aOpenFileDialog.ShowDialog() == DialogResult.OK)
{
sFileName = aOpenFileDialog.FileName;
}
else
{
isContinuing = false;
}
}
if (!isContinuing)
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
String aXSDString = XSDUserInput();
if (aXSDString.Equals("Cancel") || aXSDString.StartsWith("Err"))
{
throw new com.WDataSci.WDS.WDSException(aXSDString);
}
Boolean isXSDProvided = aXSDString.StartsWith("<");
RecordSetMDEnums.eSchemaType aSchemaType = RecordSetMDEnums.eSchemaType.XSD;
if (!isXSDProvided)
{
aSchemaType = RecordSetMDEnums.eSchemaType.NamingConvention;
}
aRecordSetMD = new RecordSetMD(RecordSetMDEnums.eMode.Input)
.cAs(RecordSetMDEnums.eType.CSV, aSchemaType, false, aXSDString)
.cAsDlmFile(sFileName)
;
MessageBoxButtons msgboxbuttons = MessageBoxButtons.YesNoCancel;
DialogResult msgboxresponse;
msgboxresponse = MessageBox.Show("Does file have a header row (Yes) or (No)?", "Confirm", msgboxbuttons);
if (msgboxresponse == System.Windows.Forms.DialogResult.Cancel)
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
if (msgboxresponse == System.Windows.Forms.DialogResult.Yes)
{
aRecordSetMD.cWithHeaderRow();
}
aRecordSetMD.mReadMapFor(null, null, true);
aRecordSet = new RecordSet()
.cAsInput()
.mReadRecordSet(aRecordSetMD)
;
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 = sFileName;
}
catch (Exception e) {
String s = tapp.InputBox("Cannot name sheet to " + sFileName, "New Sheet Name", "Leave-As-Is", 100, 100, "");
if (!s.Equals("Leave-As-Is"))
{
try {
tws.Name = s;
}
catch (Exception) {
}
}
}
}
//tapp.ScreenUpdating = false;
int nRows = aRecordSet.Records.Count;
int nColumns = aRecordSetMD.nColumns();
for (uint jj = 0; jj < nColumns; jj++)
{
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);
if (aRecordSetMD.SchemaType.bIn(RecordSetMDEnums.eSchemaType.XSD))
{
aXmlMap = twb.XmlMaps.Add(aRecordSetMD.SchemaMatter.InputSchemaString);
for (int j = 0, jP1 = 1; j < nColumns; j++, jP1++)
{
tbl.ListColumns[jP1].XPath.SetValue(aXmlMap
, "/" + aRecordSetMD.SchemaMatter.RecordSetElementName
+ "/" + aRecordSetMD.SchemaMatter.RecordElementName
+ "/" + aRecordSetMD.Column[j].Name);
}
}
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
}
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
aXmlMap = null;
aRecordSet = null;
aRecordSetMD = null;
tapp = null;
twb = null;
twbSheets = null;
tws = null;
tblr = null;
tbl = null;
trng = null;
trng2 = null;
selection = 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);
}