public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
if (arguments.Count == 6)
{
double number1 = arguments.GetNumber(0);
double number2 = arguments.GetNumber(1);
double number3 = arguments.GetNumber(2);
double number4 = arguments.GetNumber(3);
double number5 = arguments.GetNumber(4);
double number6 = arguments.GetNumber(5);
result.Text = "EQV " + number1.ToString() + "," + number2.ToString() + "," + number3.ToString() + "," + number4.ToString() + "," + number5.ToString() + "," + number6.ToString();
}
else if (arguments.Count == 4)
{
double number1 = arguments.GetNumber(0);
double number2 = arguments.GetNumber(1);
double number3 = arguments.GetNumber(2);
double number4 = arguments.GetNumber(3);
result.Text = string.Format("EQV {0},{1},{2},{3}", number1, number2, number3, number4);
}
else
{
result.Error = SpreadsheetGear.ValueError.Value;
}
}
public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
bool rs = true;
string combinestring = "";
for (int i = 0; i < arguments.Count; i++)
{
string argu = arguments.GetText(i);
char[] chars = argu.ToCharArray();
int number = 0;
for (int p = 0; p < chars.Length; p++)
{
if (chars[p] == ';') number++;
}
if (number == 2)
{
if (i != arguments.Count - 1)
{
combinestring = combinestring + arguments.GetText(i) + "|";
}
else
combinestring = combinestring + arguments.GetText(i);
}
else
{
rs = false;
break;
}
}
if (rs)
result.Text = combinestring;
else
result.Error = SpreadsheetGear.ValueError.Value;
}
public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
if (arguments.Count == 2)
{
double degree = 2 * arguments.GetNumber(0) / arguments.GetNumber(1);
result.Text = degree.ToString();
}
else
{
result.Error = SpreadsheetGear.ValueError.Value;
}
}
public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
if (arguments.Count == 2)
{
double number1 = arguments.GetNumber(0);
double number2 = arguments.GetNumber(1);
result.Text = "EQ2 " + number1.ToString() + "," + number2.ToString();
}
else
{
result.Error = SpreadsheetGear.ValueError.Value;
}
}
public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
if (arguments.Count == 3)
{
string x = arguments.GetNumber(0).ToString();
string y = arguments.GetNumber(1).ToString();
string z = arguments.GetNumber(2).ToString();
result.Text = x + ";" + y + ";" + z;
}
else
{
result.Error = SpreadsheetGear.ValueError.Value;
}
}
public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
if (arguments.Count == 1)
{
string location = arguments.GetText(0);
string parentname;
string sheetname = arguments.CurrentWorksheet.ToString();
if (sheetname.Length > 2 && sheetname.Substring(0, 3) == "[S]")
parentname = "L!";
else if (sheetname.Length > 2 && sheetname.Substring(0, 3) == "[N]") parentname = "S!";
else parentname = "L!";
result.Text = parentname + location;
}
else
{
result.Error = SpreadsheetGear.ValueError.Value;
}
}
public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result)
{
if (arguments.Count == 2)
{
string partname = arguments.GetText(1);
int rowCount, columnCount;
arguments.GetArrayDimensions(0, out rowCount, out columnCount);
for (int i = 0; i < rowCount; i++)
{
SpreadsheetGear.CustomFunctions.IValue value = arguments.GetArrayValue(0, i, 0);
if (value.Text == partname)
{
result.Number = arguments.GetArrayValue(0, i, 3).Number;
break;
}
}
}
else
{
result.Error = SpreadsheetGear.ValueError.Value;
}
}
/// <summary>
/// To insert new worksheet.
/// </summary>
/// <param name="workbook">Iworkbook Instance.</param>
/// <param name="sheetName">Sheet name </param>
internal static void InsertWorkSheet(SpreadsheetGear.IWorkbook workbook, string sheetName)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetName];
if (worksheet == null)
{
workbook.Worksheets.Add();
worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
}
// Set the worksheet name.
worksheet.Name = sheetName;
}
catch (Exception)
{
}
}
/// <summary>
///
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetName"></param>
internal static void WrapText(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, bool wrapText)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
if (worksheet != null)
{
worksheet.Cells[startRowIndex, startColumnIndex, endRowIndex, endColumnIndex].WrapText = wrapText;
}
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To create worksheet.
/// </summary>
/// <param name="workbook">IWorkBook instance</param>
/// <param name="sheetName">Sheet name in which data has to be inserted. </param>
internal static void CreateWorksheet(SpreadsheetGear.IWorkbook workbook, string sheetName)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetName];
// if worksheet not found then create it.
if (worksheet == null)
{
workbook.Worksheets.Add();
worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
}
// Set the worksheet name.
worksheet.Name = sheetName;
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set border style
/// </summary>
/// <param name="border"></param>
/// <param name="lineStyle"></param>
/// <param name="borderWeight"></param>
/// <param name="borderColor"></param>
internal static void SetBorderStyle(ref SpreadsheetGear.IBorder border, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
border.LineStyle = lineStyle;
border.Weight = borderWeight;
border.Color = borderColor;
}
internal static void SetReadOnlyRange(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
IRange Range = worksheet.Cells[startRowIndex, startColIndex, endRowIndex, endColIndex];
//Range.Validation.Delete();
Range.Validation.Add(ValidationType.Custom, ValidationAlertStyle.Stop, ValidationOperator.Equal, "''", "");
Range.Validation.ErrorTitle = "CRING Online";
Range.Validation.ErrorMessage = "Read Only Cell";
Range.Validation.IgnoreBlank = false;
}
catch (Exception)
{
}
}
internal static void SetValueByDropDownSelection(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int rowIndex, int colIndex, string dropDownAddress, string sourceAddress)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
IRange Range = worksheet.Cells[rowIndex, colIndex];
Range.Validation.Add(ValidationType.Custom, ValidationAlertStyle.Information, ValidationOperator.Default, "=VLOOKUP(" + dropDownAddress + "," + sourceAddress + ",2)", "");
}
catch (Exception)
{
}
}
/// <summary>
/// To load Data Table into worksheet.
/// </summary>
/// <param name="workbook">IWorkBook instance</param>
/// <param name="rowIndex">zero based row index</param>
/// <param name="colIndex">zero based column index</param>
/// <param name="worksheetDataTable">Data table</param>
/// <param name="sheetIndex">Zero based sheet index</param>
/// <param name="SupressColumnHeader">Set to true, to hide column header and false to display column header</param>
internal static void LoadDataTableIntoSheet(SpreadsheetGear.IWorkbook workbook, int rowIndex, int colIndex, DataTable worksheetDataTable, int sheetIndex, bool SupressColumnHeader)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
if (worksheet == null)
{
workbook.Worksheets.Add();
worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
}
// Get the cell range for the DataTable.
SpreadsheetGear.IRange cell = worksheet.Cells[rowIndex, colIndex];//["$A$1"];
// Copy the DataTable to the worksheet range.
cell.CopyFromDataTable(worksheetDataTable, SpreadsheetGear.Data.SetDataFlags.None);
// delete column headers row
if (SupressColumnHeader)
{
IRange deleteRow = worksheet.Cells[rowIndex, colIndex];
deleteRow.EntireRow.Delete(DeleteShiftDirection.Up);
}
}
catch (Exception ex)
{
new ApplicationException(ex.ToString());
}
finally
{
// Release the workbook set lock.
//this.workbook.ReleaseLock();
}
}
/* Convert a sheet to XLSX byte array */
public void CopySheet(SpreadsheetGear.IWorksheet Source, SpreadsheetGear.IWorksheet Target)
{
/* Copy values over */
Source.Cells["A1:AF100"].Copy(Target.Cells["A1:AF100"],
SpreadsheetGear.PasteType.ValuesAndNumberFormats,
SpreadsheetGear.PasteOperation.None, false, false);
/* Replace empty values */
Target.Cells["A1:AF100"].Replace("#VALUE!", "",
SpreadsheetGear.LookAt.Whole, SpreadsheetGear.SearchOrder.ByRows, false);
}
/* Copies a template with data to the worksheet */
public void FillWorkSheetWithTemplate(SpreadsheetGear.IWorksheet ParamSheet, SpreadsheetGear.IWorksheet TemplateSheet)
{
/* Copy all fields from the template sheet to the paramsheet */
TemplateSheet.Range["A1:BA120"].Copy(ParamSheet.Range["A1:BA120"],
SpreadsheetGear.PasteType.FormulasAndNumberFormats,
SpreadsheetGear.PasteOperation.None, false, false);
}
/* Fills a worksheet (SpreadSheet) with given input groups */
public void FillWorkSheetWithGroups(SpreadsheetGear.IWorksheet ParamSheet, List<CInputGroup> Groups)
{
/* Get access cells */
SpreadsheetGear.IRange CellAccess = ParamSheet.Cells;
int iGroupItr = 0;
/* Locate EIA Parser-Header */
if (CellAccess["A150"].Formula.Contains("EIA"))
{
/* Parse */
int DataGroup = 151;
while (CellAccess["A" + DataGroup.ToString()].Formula != "")
{
/* Get start & type */
int GroupStart = int.Parse(CellAccess["C" + DataGroup.ToString()].Formula.Substring(1));
String GroupType = CellAccess["B" + DataGroup.ToString()].Formula;
String GroupName = CellAccess["D" + DataGroup.ToString()].Formula;
CInputGroup iGroup = null;
/* Get relevant group */
if (iGroupItr >= Groups.Count)
return; /* Missing data? */
else
iGroup = Groups[iGroupItr++];
/* Fill In */
if (GroupType == "4")
{
/* Iterate inputs */
foreach (CInput cInp in iGroup.Inputs)
{
/* Set input data */
//CellAccess["A" + GroupStart.ToString()].Formula = cInp.Input0; <Name>
CellAccess["C" + GroupStart.ToString()].Formula = cInp.Input1;
CellAccess["D" + GroupStart.ToString()].Formula = cInp.Input2;
CellAccess["E" + GroupStart.ToString()].Formula = cInp.Input3;
CellAccess["F" + GroupStart.ToString()].Formula = cInp.Input4;
/* Iterate */
GroupStart++;
}
}
else if (GroupType == "1L")
{
/* Should only be one input */
CInput cInp = iGroup.Inputs[0];
/* Set data */
CellAccess["B" + GroupStart.ToString()].Formula = cInp.Input0;
}
else if (GroupType == "1++")
{
/* Iterate inputs */
foreach (CInput cInp in iGroup.Inputs)
{
/* Set input data */
//CellAccess["J" + GroupStart.ToString()].Formula = cInp.Input0; <Label>
//CellAccess["K" + GroupStart.ToString()].Formula = cInp.Input1; <dType>
CellAccess["L" + GroupStart.ToString()].Formula = cInp.Input2;
//CellAccess["E" + GroupStart.ToString()].Formula = cInp.Input3; <Comment>
/* Iterate */
GroupStart++;
}
}
else if (GroupType == "1")
{
/* Iterate inputs */
foreach (CInput cInp in iGroup.Inputs)
{
/* Set input data */
//CellAccess["A" + GroupStart.ToString()].Formula = cInp.Input0; <Label>
CellAccess["B" + GroupStart.ToString()].Formula = cInp.Input1;
/* Iterate */
GroupStart++;
}
}
else if (GroupType == "1Y")
{
/* Should only be one input */
CInput cInp = iGroup.Inputs[0];
/* Update start year */
CellAccess["A" + GroupStart.ToString()].Formula = cInp.Input0;
}
else if (GroupType == "1+")
{
/* Iterate inputs */
foreach (CInput cInp in iGroup.Inputs)
{
/* Set input data */
//CellAccess["A" + GroupStart.ToString()].Formula = cInp.Input0; <Name>
CellAccess["C" + GroupStart.ToString()].Formula = cInp.Input1;
CellAccess["D" + GroupStart.ToString()].Formula = cInp.Input2;
CellAccess["E" + GroupStart.ToString()].Formula = cInp.Input3;
CellAccess["F" + GroupStart.ToString()].Formula = cInp.Input4;
/* Iterate */
GroupStart++;
}
}
/* Increament */
DataGroup++;
}
}
}
internal static void RemoveValidation(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
IRange Range = worksheet.Cells[startRowIndex, startColIndex, endRowIndex, endColIndex];
Range.Validation.Delete();
}
catch (Exception)
{
}
}
internal static void MakeDropDownList(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int row, int col, string cellAddress)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
IRange Range = worksheet.Cells[row, col];
Range.Validation.Add(ValidationType.List, ValidationAlertStyle.Information, ValidationOperator.Default, "=" + cellAddress, "");
}
catch (Exception)
{
}
}
/// <summary>
/// Delete the worksheet
/// </summary>
/// <param name="workbook">Iworkbook Instance.</param>
/// <param name="sheetIndex">Sheet Index</param>
internal static void DeleteWorkSheet(SpreadsheetGear.IWorkbook workbook, int sheetIndex)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
worksheet.Delete();
}
catch (Exception)
{
}
}
/// <summary>
/// To load Data Table into worksheet.
/// </summary>
/// <param name="workbook">IWorkBook instance</param>
/// <param name="worksheetDataTable">Data table</param>
/// <param name="sheetName">Sheet name in which data has to be inserted. </param>
internal static void LoadDataTableIntoSheet(SpreadsheetGear.IWorkbook workbook, DataTable worksheetDataTable, string sheetName)
{
// Acquire a workbook set lock.
//this.workbook.GetLock();
try
{
ExcelHelper.LoadDataTableIntoSheet(workbook, "$A$1", worksheetDataTable, sheetName, false, true);
//// Get a workbook reference.
////SpreadsheetGear.IWorkbook workbook = this.workbook.ActiveWorkbook;
//// Get a worksheet reference.
//SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetName];
//if (worksheet == null)
//{
// workbook.Worksheets.Add();
// worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
//}
////clear all values
//worksheet.Cells.Clear();
//// Set the worksheet name.
//worksheet.Name = sheetName;
//// Get the top left cell for the DataTable.
//SpreadsheetGear.IRange cell = worksheet.Cells["$A$1"];
//// set the format to text
//worksheet.Cells[0, 0, worksheetDataTable.Rows.Count + 7, 19].NumberFormat = "@";
//// Copy the DataTable to the worksheet range.
//cell.CopyFromDataTable(worksheetDataTable, SpreadsheetGear.Data.SetDataFlags.None);
//// Auto size all worksheet columns which contain data.
//worksheet.UsedRange.Columns.AutoFit();
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
finally
{
// Release the workbook set lock.
//this.workbook.ReleaseLock();
}
}
/// <summary>
/// To load Data Table into worksheet.
/// </summary>
/// <param name="workbook">IWorkBook instance</param>
/// <param name="startPosition">Starting position of datatable like $A$10</param>
/// <param name="worksheetDataTable">Data table</param>
/// <param name="sheetName">Sheet name in which data has to be inserted. </param>
/// <param name="SupressColumnHeader">Set to true, to hide column header and false to display column header</param>
/// <param name="FirstClearAllCells">set to true, if you want to clear all cells before pasting datatable into worksheet </param>
internal static void LoadDataTableIntoSheet(SpreadsheetGear.IWorkbook workbook, string startPosition, DataTable worksheetDataTable, string sheetName, bool SupressColumnHeader, bool firstClearAllCells)
{
// Acquire a workbook set lock.
//this.workbook.GetLock();
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetName];
if (worksheet == null)
{
workbook.Worksheets.Add();
worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
}
// Set the worksheet name.
worksheet.Name = sheetName;
//clear all values
if (firstClearAllCells)
{
worksheet.Cells.Clear();
}
// Get the cell range for the DataTable.
SpreadsheetGear.IRange cell = worksheet.Cells[startPosition];//["$A$1"];
// set the format to text
worksheet.Cells[0, 0, worksheetDataTable.Rows.Count + 7, 19].NumberFormat = "@";
// Copy the DataTable to the worksheet range.
cell.CopyFromDataTable(worksheetDataTable, SpreadsheetGear.Data.SetDataFlags.None);
// Auto size all worksheet columns which contain data.
worksheet.UsedRange.Columns.AutoFit();
// delete column headers row
if (SupressColumnHeader)
{
IRange deleteRow = worksheet.Cells[startPosition];
deleteRow.EntireRow.Delete(DeleteShiftDirection.Up);
}
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
finally
{
// Release the workbook set lock.
//this.workbook.ReleaseLock();
}
}
/* Parse */
static String ParseDataGroup(SpreadsheetGear.IWorksheet _ListSheet, SpreadsheetGear.IRange CellAccess,
int pListCount, String ParamType, String GroupStart, String GroupName, out int NewListCount)
{
int Itr = int.Parse(GroupStart.Substring(1));
String DataStr = "";
int ListCount = pListCount;
/* Locate Group 1 - ECONOMIC PARAMETERS
* A -> Names
* C -> Default Value
* D -> Start Value
* E -> End Value
* F -> Step Value */
if (ParamType == "4")
{
DataStr += "NewInputGroup|" + GroupName + "|";
while (CellAccess["A" + Itr.ToString()].Formula != "")
{
/* Allocate */
EconomicEntry eEntry = new EconomicEntry();
/* Get values */
eEntry.Name = CellAccess["A" + Itr.ToString()].Formula;
eEntry.DefaultVal = CellAccess["C" + Itr.ToString()].Formula;
eEntry.Start = CellAccess["D" + Itr.ToString()].Formula;
eEntry.End = CellAccess["E" + Itr.ToString()].Formula;
eEntry.Step = CellAccess["F" + Itr.ToString()].Formula;
DataStr += "NewInputField|5|" + eEntry.Name + "|"
+ eEntry.DefaultVal + "|" + eEntry.Start + "|" + eEntry.End + "|" + eEntry.Step + "|";
/* Increament */
Itr++;
}
}
/* Validation Items */
if (ParamType == "1L")
{
DataStr += "NewValidationGroup|Scale|";
while (CellAccess["A" + Itr.ToString()].Formula != "")
{
/* Sanity */
if (CellAccess["A" + Itr.ToString()].Formula != "")
{
/* Allocate */
ValidationEntry vEntry = new ValidationEntry();
/* Get values */
vEntry.Label = CellAccess["A" + Itr.ToString()].Formula;
Console.WriteLine(CellAccess["B" + Itr.ToString()].Validation.Formula1);
Console.WriteLine(CellAccess["B" + Itr.ToString()].Validation.Formula2);
/* Lookup first list */
String ListLoc = LocateList(_ListSheet, ListCount);
if (ListLoc != "")
{
int ListItr = 1;
while (_ListSheet.Cells[ListLoc + ListItr.ToString()].Formula != "")
{
/* Add */
vEntry.Values.Add(_ListSheet.Cells[ListLoc + ListItr.ToString()].Formula);
/* Increase */
ListItr++;
}
}
if (vEntry.Values.Count != 0)
{
DataStr += "NewValidation|" + (vEntry.Values.Count + 1).ToString()
+ "|" + vEntry.Label + "|";
foreach (String Val in vEntry.Values)
DataStr += Val + "|";
}
/* Increase */
ListCount++;
}
/* Increament */
Itr++;
}
}
/* Locate Group 2 - Petroleum Taxation -> 5
* J -> Labels
* K -> Data Type
* L -> Value
* M -> Comment/Unit */
if (ParamType == "1++")
{
DataStr += "NewInputGroup|Petroleum Taxation|";
while (CellAccess["J" + Itr.ToString()].Formula != "")
{
/* Allocate */
TaxationEntry tEntry = new TaxationEntry();
/* Get values */
tEntry.Label = CellAccess["J" + Itr.ToString()].Formula;
tEntry.dType = CellAccess["K" + Itr.ToString()].Formula;
tEntry.Value = CellAccess["L" + Itr.ToString()].Formula;
tEntry.Comment = CellAccess["E" + Itr.ToString()].Formula;
DataStr += "NewInputField|4|" + tEntry.Label + "|"
+ tEntry.dType + "|" + tEntry.Value + "|" + tEntry.Comment + "|";
/* Increament */
Itr++;
}
}
/* Parse Profiles */
if (ParamType == "1")
{
/* Locate Interest */
DataStr += "NewInputGroup|Working Interest|";
while (CellAccess["A" + Itr.ToString()].Formula != "")
{
/* Allocate */
InterestEntry tEntry = new InterestEntry();
/* Get values */
tEntry.Label = CellAccess["A" + Itr.ToString()].Formula;
tEntry.Percentage = CellAccess["B" + Itr.ToString()].Formula;
DataStr += "NewInputField|2|" + tEntry.Label + "|"
+ tEntry.Percentage + "|";
/* Increament */
Itr++;
}
}
if (ParamType == "1Y")
{
DataStr += "YearStartGroup|" + CellAccess[GroupStart].Formula + "|";
}
if (ParamType == "1+")
{
DataStr += "NewProfileGroup|";
while (CellAccess["A" + Itr.ToString()].Formula != "")
{
/* Allocate */
ProfileEntry eEntry = new ProfileEntry();
/* Get values */
eEntry.Name = CellAccess["A" + Itr.ToString()].Formula;
eEntry.DefaultVal = CellAccess["C" + Itr.ToString()].Formula;
eEntry.Start = CellAccess["D" + Itr.ToString()].Formula;
eEntry.End = CellAccess["E" + Itr.ToString()].Formula;
eEntry.Step = CellAccess["F" + Itr.ToString()].Formula;
DataStr += "NewInputField|5|" + eEntry.Name + "|"
+ eEntry.DefaultVal + "|" + eEntry.Start + "|" + eEntry.End + "|" + eEntry.Step + "|";
/* Increament */
Itr++;
}
}
/* Update */
NewListCount = ListCount;
return DataStr;
}
/* Helper */
static String LocateList(SpreadsheetGear.IWorksheet Sheet, int ListNo)
{
/* Get access cells */
SpreadsheetGear.IRange CellAccess = Sheet.Cells;
char Itr = 'A';
int ListCount = 0;
while (Itr <= 'Z')
{
/* Cast */
String ItrStr = Char.ToString(Itr);
/* Has data? */
if (CellAccess[ItrStr + "1"].Formula != "")
{
if (ListCount == ListNo)
{
return ItrStr;
}
else
ListCount++;
}
/* Increase */
Itr++;
}
/* Fail */
return "";
}
internal static void SetValidationList(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, string list)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
if (endRowIndex == -1)
{
endRowIndex = worksheet.Cells.RowCount - 1;
}
IRange Range = worksheet.Cells[startRowIndex, startColIndex, endRowIndex, endColIndex];
Range.Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, list, "");
Range.Validation.ErrorTitle = "";
Range.Validation.ErrorMessage = "";
Range.Validation.IgnoreBlank = true;
}
catch (Exception)
{
}
}
/// <summary>
/// To set array values into worksheet.
/// </summary>
/// <param name="workbook">Iworkbook Instance.</param>
/// <param name="sheetName">Sheet name in which data has to be inserted</param>
/// <param name="startRowIndex">Start row index starts from zero</param>
/// <param name="startColIndex">Start column index starts from zero</param>
/// <param name="endRowIndex">End row index starts from zero</param>
/// <param name="endColIndex">End column index starts from zero</param>
/// <param name="valueArray"></param>
internal static void SetArrayValuesIntoSheet(SpreadsheetGear.IWorkbook workbook, int sheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, object[,] valueArray, bool IsAutoFit)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
if (worksheet == null)
{
workbook.Worksheets.Add();
worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
}
// Set the worksheet name.
//worksheet.Name = sheetName;
// Get the cell range for the DataTable.
SpreadsheetGear.IRange Range = worksheet.Cells[startRowIndex, startColIndex, endRowIndex - 1, endColIndex - 1];
//set array value
Range.Value = valueArray;
if (IsAutoFit)
{
//// Auto fit the range
Range.Columns.AutoFit();
}
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
finally
{
// Release the workbook set lock.
//this.workbook.ReleaseLock();
}
}
/// <summary>
/// Append new worksheet at end
/// </summary>
/// <param name="workbook">Iworkbook Instance.</param>
/// <param name="sheetName">Sheet name </param>
internal static void AppendWorkSheet(SpreadsheetGear.IWorkbook workbook, string sheetName)
{
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetName];
worksheet.CopyAfter(worksheet);
}
catch (Exception)
{
}
}
/// <summary>
/// Define the chart range from the table worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
private SpreadsheetGear.IRange DefineChartRange(SpreadsheetGear.IWorksheet worksheet)
{
SpreadsheetGear.IRange Retval = null;
try
{
//int ColumnCount = 1;
//if (worksheet.UsedRange.ColumnCount - 2 <= 0)
//{
// ColumnCount = 1;
//}
//else
//{
// ColumnCount = worksheet.UsedRange.ColumnCount - 2;
//}
Retval = worksheet.Range[0 + 1, 0, this._TablePresentation.DataTableRowCount - 1, this._TablePresentation.DataTableColumnCount - 1];// this.moSpreadsheet.ActiveWorkbook.Worksheets[DILanguage.GetLanguageString("Table")].UsedRange.Address];
}
catch (Exception)
{
}
return Retval;
}
/// <summary>
/// Get XlChartType against spreadsheet ChartType
/// </summary>
/// <param name="InfragisticsChartType"></param>
/// <returns></returns>
public static int GetXlChartTypeFromSpreadsheetChartType(SpreadsheetGear.Charts.ChartType SpreadsheetChartType)
{
int RetVal = 51;
switch (SpreadsheetChartType)
{
case SpreadsheetGear.Charts.ChartType.Area:
break;
case SpreadsheetGear.Charts.ChartType.Area3D:
break;
case SpreadsheetGear.Charts.ChartType.AreaStacked:
break;
case SpreadsheetGear.Charts.ChartType.AreaStacked100:
break;
case SpreadsheetGear.Charts.ChartType.AreaStacked1003D:
break;
case SpreadsheetGear.Charts.ChartType.AreaStacked3D:
break;
case SpreadsheetGear.Charts.ChartType.BarClustered:
RetVal = 57;
break;
case SpreadsheetGear.Charts.ChartType.BarClustered3D:
break;
case SpreadsheetGear.Charts.ChartType.BarOfPie:
break;
case SpreadsheetGear.Charts.ChartType.BarStacked:
//-- We are using BarClustered for creating the pyramid chard. As we already creating Bar chart, so we use different Bar chart type for pyrmaid chart.
RetVal = 58;
break;
case SpreadsheetGear.Charts.ChartType.BarStacked100:
break;
case SpreadsheetGear.Charts.ChartType.BarStacked1003D:
break;
case SpreadsheetGear.Charts.ChartType.BarStacked3D:
break;
case SpreadsheetGear.Charts.ChartType.Bubble:
break;
case SpreadsheetGear.Charts.ChartType.Bubble3DEffect:
break;
case SpreadsheetGear.Charts.ChartType.Column3D:
break;
case SpreadsheetGear.Charts.ChartType.ColumnClustered:
RetVal = 51;
break;
case SpreadsheetGear.Charts.ChartType.ColumnClustered3D:
break;
case SpreadsheetGear.Charts.ChartType.ColumnStacked:
break;
case SpreadsheetGear.Charts.ChartType.ColumnStacked100:
break;
case SpreadsheetGear.Charts.ChartType.ColumnStacked1003D:
break;
case SpreadsheetGear.Charts.ChartType.ColumnStacked3D:
break;
case SpreadsheetGear.Charts.ChartType.Combination:
break;
case SpreadsheetGear.Charts.ChartType.ConeBarClustered:
break;
case SpreadsheetGear.Charts.ChartType.ConeBarStacked:
break;
case SpreadsheetGear.Charts.ChartType.ConeBarStacked100:
break;
case SpreadsheetGear.Charts.ChartType.ConeCol:
break;
case SpreadsheetGear.Charts.ChartType.ConeColClustered:
break;
case SpreadsheetGear.Charts.ChartType.ConeColStacked:
break;
case SpreadsheetGear.Charts.ChartType.ConeColStacked100:
break;
case SpreadsheetGear.Charts.ChartType.CylinderBarClustered:
break;
case SpreadsheetGear.Charts.ChartType.CylinderBarStacked:
break;
case SpreadsheetGear.Charts.ChartType.CylinderBarStacked100:
break;
case SpreadsheetGear.Charts.ChartType.CylinderCol:
break;
case SpreadsheetGear.Charts.ChartType.CylinderColClustered:
break;
case SpreadsheetGear.Charts.ChartType.CylinderColStacked:
break;
case SpreadsheetGear.Charts.ChartType.CylinderColStacked100:
break;
case SpreadsheetGear.Charts.ChartType.Doughnut:
break;
case SpreadsheetGear.Charts.ChartType.DoughnutExploded:
break;
case SpreadsheetGear.Charts.ChartType.Line:
RetVal = 4;
break;
case SpreadsheetGear.Charts.ChartType.Line3D:
break;
case SpreadsheetGear.Charts.ChartType.LineMarkers:
break;
case SpreadsheetGear.Charts.ChartType.LineMarkersStacked:
break;
case SpreadsheetGear.Charts.ChartType.LineMarkersStacked100:
break;
case SpreadsheetGear.Charts.ChartType.LineStacked:
break;
case SpreadsheetGear.Charts.ChartType.LineStacked100:
break;
case SpreadsheetGear.Charts.ChartType.Pie:
RetVal = 5;
break;
case SpreadsheetGear.Charts.ChartType.Pie3D:
break;
case SpreadsheetGear.Charts.ChartType.PieExploded:
break;
case SpreadsheetGear.Charts.ChartType.PieExploded3D:
break;
case SpreadsheetGear.Charts.ChartType.PieOfPie:
break;
case SpreadsheetGear.Charts.ChartType.PyramidBarClustered:
break;
case SpreadsheetGear.Charts.ChartType.PyramidBarStacked:
break;
case SpreadsheetGear.Charts.ChartType.PyramidBarStacked100:
break;
case SpreadsheetGear.Charts.ChartType.PyramidCol:
break;
case SpreadsheetGear.Charts.ChartType.PyramidColClustered:
break;
case SpreadsheetGear.Charts.ChartType.PyramidColStacked:
break;
case SpreadsheetGear.Charts.ChartType.PyramidColStacked100:
break;
case SpreadsheetGear.Charts.ChartType.Radar:
break;
case SpreadsheetGear.Charts.ChartType.RadarFilled:
break;
case SpreadsheetGear.Charts.ChartType.RadarMarkers:
break;
case SpreadsheetGear.Charts.ChartType.StockHLC:
break;
case SpreadsheetGear.Charts.ChartType.StockOHLC:
break;
case SpreadsheetGear.Charts.ChartType.StockVHLC:
break;
case SpreadsheetGear.Charts.ChartType.StockVOHLC:
break;
case SpreadsheetGear.Charts.ChartType.Surface:
break;
case SpreadsheetGear.Charts.ChartType.SurfaceTopView:
break;
case SpreadsheetGear.Charts.ChartType.SurfaceTopViewWireframe:
break;
case SpreadsheetGear.Charts.ChartType.SurfaceWireframe:
break;
case SpreadsheetGear.Charts.ChartType.XYScatter:
break;
case SpreadsheetGear.Charts.ChartType.XYScatterLines:
break;
case SpreadsheetGear.Charts.ChartType.XYScatterLinesNoMarkers:
break;
case SpreadsheetGear.Charts.ChartType.XYScatterSmooth:
break;
case SpreadsheetGear.Charts.ChartType.XYScatterSmoothNoMarkers:
break;
default:
break;
}
return RetVal;
}
/// <summary>
/// Update the excel chart type.
/// </summary>
/// <param name="chartType"></param>
public void UpdateChart(string filenameWpath, SpreadsheetGear.Charts.ChartType chartType)
{
try
{
this.LoadWorkbook(filenameWpath);
this.moSpreadsheet.GetLock();
SpreadsheetGear.Charts.IChart objChart;
SpreadsheetGear.Shapes.IShapes shapes = moSpreadsheet.ActiveWorksheet.Shapes;
objChart = (SpreadsheetGear.Charts.IChart)shapes[0].Chart;
objChart.ChartType = chartType;
this.moSpreadsheet.ActiveWorkbook.Save();
this.moSpreadsheet.ActiveWorkbook.Close();
}
catch (Exception)
{
}
finally
{
this.moSpreadsheet.ReleaseLock();
}
}
