/// <summary>
/// Reads an Excel file from the file system and converts it into a ADO.NET data table
/// </summary>
/// <param name="FullPathAndFileName"></param>
/// <returns></returns>
public DataTable convertExcelFileToDataTable(string FullPathAndFileName, ref string ErrorMessage, ref string WarningMessage)
{
DataTable RetDataTable = new DataTable();
OleDbConnection ExcelConn = null;
try
{
// Microsoft JET engine knows how to read Excel files as a database
// Problem is - it is old OLE technology - not newer ADO.NET
string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FullPathAndFileName + ";Extended Properties=Excel 8.0;";
ExcelConn = new OleDbConnection(ConnStr);
ExcelConn.Open();
DataTable InspectionDt = ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (null == InspectionDt)
{
throw new CswDniException(CswEnumErrorType.Error, "Could not process the uploaded file: " + FullPathAndFileName, "GetOleDbSchemaTable failed to parse a valid XLS file.");
}
string FirstSheetName = InspectionDt.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter DataAdapter = new OleDbDataAdapter();
OleDbCommand SelectCommand = new OleDbCommand("SELECT * FROM [" + FirstSheetName + "]", ExcelConn);
DataAdapter.SelectCommand = SelectCommand;
DataTable UploadDataTable = new DataTable();
DataAdapter.Fill(UploadDataTable);
//we have finished deserializing the XLS file by now...
CswNbtActInspectionDesignWiz wiz = new CswNbtActInspectionDesignWiz(_CswNbtResources, CswEnumNbtViewVisibility.Role, _CswNbtResources.CurrentNbtUser, false);
RetDataTable = wiz.prepareDataTable(UploadDataTable);
if (RetDataTable.Rows.Count < UploadDataTable.Rows.Count)
{
WarningMessage += "Not all rows could be imported. Question text must be unique per row.";
}
} // try
catch (Exception Exception)
{
_CswNbtResources.CswLogger.reportError(Exception);
}
finally
{
if (ExcelConn != null)
{
ExcelConn.Close();
ExcelConn.Dispose();
}
}
return(RetDataTable);
} // convertExcelFileToDataTable()
} // convertExcelFileToDataTable()
public JObject recycleInspectionDesign(string InspectionDesignName, string InspectionTargetName, string Category)
{
CswNbtActInspectionDesignWiz wiz = new CswNbtActInspectionDesignWiz(_CswNbtResources, CswEnumNbtViewVisibility.Role, _CswNbtResources.CurrentNbtUser, false);
return(wiz.copyInspectionDesign(InspectionDesignName, InspectionTargetName, Category));
}
public JObject createInspectionDesignTabsAndProps(string GridArrayString, string InspectionDesignName, string InspectionTargetName, string Category)
{
CswNbtActInspectionDesignWiz wiz = new CswNbtActInspectionDesignWiz(_CswNbtResources, CswEnumNbtViewVisibility.Role, _CswNbtResources.CurrentNbtUser, false);
return(wiz.createInspectionDesignTabsAndProps(GridArrayString, InspectionDesignName, InspectionTargetName, Category));
}
