protected void UploadExcelFileButton_Click(object sender, EventArgs e)
{
Util util = new Util();
Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
if (util.CheckSessionTimeout(State, Response, "../../Default.aspx")) return;
if (this.FileUpload1.PostedFile != null)
{
// get the file
HttpPostedFile file = this.FileUpload1.PostedFile;
// check the length of the file
if (file.ContentLength > 0)
{
string app_name = ((Hashtable)HttpRuntime.Cache[Session.SessionID])["SelectedApp"].ToString();
string application_id = util.GetAppID((Hashtable)HttpRuntime.Cache[Session.SessionID]);
// get the Excel tables
ExcelUtil excel_util = new ExcelUtil();
DataTable[] excel_tables = excel_util.GetDataTablesFromExcelStream(file.InputStream);
if (excel_tables == null || excel_tables.Length == 0)
{
ErrorMessage.Text = "The file: '" + file.FileName + "' could not be processed as an Excel file.";
return;
}
DataTable excel_table = excel_tables[0];
//string update_type = UploadType.SelectedIndex == 0 ? "add" : "replace";
string update_type = "replace";
//check on user limit
long max_users = util.GetMaxUsers((Hashtable)HttpRuntime.Cache[Session.SessionID]);
if (max_users == 0 || max_users > 1000L)
{
ErrorMessage.Text = "No paid ViziApps service allows any production credentials";
return;
}
if (excel_table.Rows.Count - 1 > max_users)
{
ErrorMessage.Text = "The number of credentials in your Credentials File exceed the limit of the paid ViziApps service";
return;
}
StringBuilder errors = new StringBuilder("The file was successfully uploaded. Close this window.");
util.UpdateUserCredentials((Hashtable)HttpRuntime.Cache[Session.SessionID], application_id, excel_table.Rows, update_type);
ErrorMessage.Text = errors.ToString();
}
else
{
ErrorMessage.Text = "The file: '" + file.FileName + "' is empty.";
}
}
}
public static bool IsEmptyCell(Excel.Range range, int row, int column)
{
//Check not empty
Error error = null;
if (ExcelUtil.GetStringValue(range, row, column) == "")
{
error = new Error();
error.Issue = "שדה ריק";
FinallizeErrorAndAdd(error, row, column, range);
return(true);
}
return(false);
}
ReadClusterTables
(
ListObject oClusterTable,
ListObject oClusterVertexTable,
ReadWorkbookContext oReadWorkbookContext,
IGraph oGraph
)
{
Debug.Assert(oClusterTable != null);
Debug.Assert(oClusterVertexTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oGraph != null);
AssertValid();
// If a required column is missing, do nothing.
ListColumn oColumn;
if (
!ExcelUtil.TryGetTableColumn(oClusterTable,
ClusterTableColumnNames.Name, out oColumn)
||
!ExcelUtil.TryGetTableColumn(oClusterTable,
ClusterTableColumnNames.VertexColor, out oColumn)
||
!ExcelUtil.TryGetTableColumn(oClusterTable,
ClusterTableColumnNames.VertexShape, out oColumn)
||
!ExcelUtil.TryGetTableColumn(oClusterVertexTable,
ClusterVertexTableColumnNames.ClusterName, out oColumn)
||
!ExcelUtil.TryGetTableColumn(oClusterVertexTable,
ClusterVertexTableColumnNames.VertexName, out oColumn)
)
{
return;
}
// Create a dictionary from the cluster table. The key is the cluster
// name and the value is a ClusterInformation object for the cluster.
Dictionary <String, ClusterInformation> oClusterNameDictionary =
ReadClusterTable(oClusterTable, oReadWorkbookContext);
// Read the cluster vertex table and add the cluster vertex information
// to the graph.
ReadClusterVertexTable(oClusterVertexTable, oClusterNameDictionary,
oReadWorkbookContext.VertexNameDictionary, oGraph);
}
public override void UploadFileAction(string filesPathItem)
{
List <MaterialStuck> materialList = ExcelUtil.ConvertExcelToMaterialListFromLetfOvers(filesPathItem);
DataDB.InsertMaterialList(materialList);
if (materialList.Count != 0)
{
MessageBox.Show($"Se insertaron o actualizaron {materialList.Count} materiales en almacén");
}
else
{
MessageBox.Show("No se inserto ningún registro, cheque el formato");
}
}
public List <string> ReadNameChangeData(Worksheet wSheet, int rowIndex)
{
List <string> itemList = new List <string>();
string col1Value = ExcelUtil.GetRange(rowIndex, 2, wSheet).get_Value(Missing.Value).ToString();
itemList.Add(col1Value);
Range range = ExcelUtil.GetRange(rowIndex, 3, wSheet);
if (range.get_Value(Missing.Value) != null)
{
string shortName = range.get_Value(Missing.Value).ToString();
itemList.Add(shortName);
}
else
{
LogMessage(string.Format("Check the source, no short name in the row {0}", rowIndex));
}
range = ExcelUtil.GetRange(rowIndex + 1, 3, wSheet);
if (range.get_Value(Missing.Value) != null)
{
string longName = range.get_Value(Missing.Value).ToString();
string name = ChangeNameIntoCamel(longName);
itemList.Add(name);
}
else
{
LogMessage(string.Format("Check the source, no long name in the row {0}", rowIndex));
}
range = ExcelUtil.GetRange(rowIndex, 8, wSheet);
if (range.get_Value(Missing.Value) != null)
{
itemList.Add(range.get_Value(Missing.Value).ToString());
}
else
{
LogMessage(string.Format("Check the source, no date in the row {0}", rowIndex));
}
range = ExcelUtil.GetRange(rowIndex + 1, 8, wSheet);
if (range.get_Value(Missing.Value) != null)
{
string chineseName = range.get_Value(Missing.Value).ToString();
itemList.Add(chineseName);
itemList.Add(FormatName(chineseName));
}
else
{
LogMessage(string.Format("Check the source, no chinese name in the row {0}", rowIndex));
}
return(itemList);
}
public void GenerateSCBFile(SCBChangeInfo changeInfo)
{
using (ExcelApp app = new ExcelApp(false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.GENERATED_SCB_FILE_PATH);
var worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1;
ExcelUtil.GetRange(1, 1, lastUsedRow, lastUsedCol, worksheet).ClearContents();
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
writer.WriteLine("EventType");
writer.WriteLine("Function");
writer.WriteLine("FILE_CODE");
writer.WriteLine("ISIN");
writer.WriteLine("SEDOL");
writer.WriteLine("OLD TICKER");
writer.WriteLine("NEW TICKER");
writer.WriteLine("Old RIC");
writer.WriteLine("New RIC");
writer.WriteLine("MIC");
writer.WriteLine("EXCHANGE_CODE");
writer.WriteLine("EXCHANGE_NAME");
writer.WriteLine("OLD COMPANY_NAME");
writer.WriteLine("NEW COMPANY_NAME");
writer.WriteLine("ANNOUNCEMENT DATE");
writer.WriteLine("EFFECTIVE DATE");
writer.PlaceNext(2, 1);
writer.WriteLine(changeInfo.EventType);
writer.WriteLine(changeInfo.Function);
writer.WriteLine(changeInfo.FileCode);
writer.WriteLine(changeInfo.Isin);
writer.WriteLine(changeInfo.Sedol);
writer.WriteLine(changeInfo.OldTicker);
writer.WriteLine(changeInfo.NewTicker);
writer.WriteLine(changeInfo.OldRic);
writer.WriteLine(changeInfo.NewRic);
writer.WriteLine(changeInfo.Mic);
writer.WriteLine(changeInfo.ExchangeCode);
writer.WriteLine(changeInfo.ExchangeName);
writer.WriteLine(changeInfo.OldCompName);
writer.WriteLine(changeInfo.NewCompName);
writer.WriteLine(changeInfo.AnnouncementDate);
writer.WriteLine(changeInfo.EffectiveDate);
}
workbook.SaveCopyAs(GetSCBGenerateFilePath(configObj.GENERATED_SCB_FILE_PATH));
workbook.Close(false, workbook.FullName, false);
}
}
ReadAlpha
(
ExcelTableReader.ExcelTableRow oRow,
IMetadataProvider oEdgeOrVertex
)
{
Debug.Assert(oRow != null);
Debug.Assert(oEdgeOrVertex != null);
AssertValid();
String sString;
if (!oRow.TryGetNonEmptyStringFromCell(CommonTableColumnNames.Alpha,
out sString))
{
return(false);
}
Single fAlpha;
if (!Single.TryParse(sString, out fAlpha))
{
Range oInvalidCell = oRow.GetRangeForCell(
CommonTableColumnNames.Alpha);
OnWorkbookFormatError(String.Format(
"The cell {0} contains an invalid opacity. The opacity,"
+ " which is optional, must be a number. Any number is"
+ " acceptable, although {1} (transparent) is used for any"
+ " number less than {1} and {2} (opaque) is used for any"
+ " number greater than {2}."
,
ExcelUtil.GetRangeAddress(oInvalidCell),
AlphaConverter.MinimumAlphaWorkbook,
AlphaConverter.MaximumAlphaWorkbook
),
oInvalidCell
);
}
fAlpha = m_oAlphaConverter.WorkbookToGraph(fAlpha);
oEdgeOrVertex.SetValue(ReservedMetadataKeys.PerAlpha, fAlpha);
return(fAlpha == 0);
}
private void generateTXTFile(Workbook warGuideBWorkbook)
{
Worksheet insertV3Sheet = ExcelUtil.GetWorksheet(configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook);
if (insertV3Sheet == null)
{
LogMessage(string.Format("There's no worksheet {0} in workbook {1}", configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook.FullName));
}
int lastUsedRow = insertV3Sheet.UsedRange.Row + insertV3Sheet.UsedRange.Rows.Count - 1;
var sb = new StringBuilder();
sb.AppendLine("HKSE;29-JUN-29;7:1;TPS;");
sb.AppendLine("ROW80_5;ROW80_6;ROW80_7;ROW80_8;ROW80_9;ROW80_10;ROW80_11;ROW80_12;ROW80_13;ROW80_14;ROW80_15;ROW80_16;ROW80_17;ROW80_18;ROW80_19;ROW80_20;ROW80_21;ROW80_22;ROW80_23;ROW80_24;ROW80_25;");
sb.Append("HK/WTS06;");
using (var reader = new ExcelLineWriter(insertV3Sheet, 86, 1, ExcelLineWriter.Direction.Right))
{
string line = string.Empty;
int pageNum = -1;
while (reader.Row <= lastUsedRow)
{
line += reader.ReadLineCellText().PadRight(9);
line += reader.ReadLineCellText().PadRight(16);
line += reader.ReadLineCellText().PadLeft(9);
line += reader.ReadLineCellText().PadLeft(8);
line += reader.ReadLineCellText().PadRight(10);
line += reader.ReadLineCellText().PadLeft(9);
line += reader.ReadLineCellText().PadLeft(8);
line += ";";
sb.Append(line);
line = string.Empty;
if (reader.Row % 20 == 5)
{
pageNum = reader.Row / 20 + 1;
sb.AppendLine(string.Format(" PREV <HK/WTS{0}> / NEXT <HK/WTS{1}>;", (pageNum - 1).ToString("D2"), (pageNum + 1).ToString("D2")));
line = string.Format("HK/WTS{0};", (pageNum + 1).ToString("D2"));
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
using (var sw = new StreamWriter(Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), false, Encoding.UTF8))
{
sw.WriteLine(sb.ToString());
sw.AutoFlush = true;
sw.Close();
AddResult("war_guideB.txt", Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), "file");
}
}
public void SelectedSheetsPrintOutTest()
{
//想定結果：全シートが印刷される
//備考：
//確認者：橋本, 確認日：2019/3/26
COMUtil comUtil = new COMUtil();
ExcelUtil xls = new ExcelUtil();
object app = null;
object books = null;
object book = null;
object sheets = null;
try {
//読取用Excelをオープン
app = comUtil.CreateObject("Excel.Application");
xls.SetVisible(app, true);
//xls.SetDisplayAlerts(app, false);
//xls.SetScreenUpdating(app, true);
books = xls.GetWorkbooks(app);
string path = Environment.CurrentDirectory + @"\TestData\Sample.xlsx";
book = xls.Open(books, path);
sheets = xls.GetWorksheets(book);
//全シート選択
xls.WorksheetsSelect(sheets);
//印刷
if (IsExistsMESPrinter())
{
//MESPrinter
xls.SelectedSheetsPrintOut(app, "MESPrinter");
}
else
{
//デフォルトプリンタ
xls.SelectedSheetsPrintOut(app);
}
xls.Close(book);
} finally {
xls.Quit(app);
comUtil.MReleaseComObject(sheets);
comUtil.MReleaseComObject(book);
comUtil.MReleaseComObject(books);
comUtil.MReleaseComObject(app);
GC.Collect();
}
}
/// <summary>
/// Filling the newly created Lot Size file with new entry
/// Create the file if doesn't exist yet
/// </summary>
/// <param name="content"></param>
/// <param name="index"></param>
private void FillLotSize(Dictionary <string, string> content, int index)
{
Workbook workbookNda;
Worksheet worksheetNda;
string titleWork = configObj.ResultsFolder + "LotSize_" + DateTime.Now.ToString("dMMMyyyy") + ".csv";
try
{
workbookNda = ExcelUtil.CreateOrOpenExcelFile(app, titleWork);
worksheetNda = workbookNda.Worksheets[1] as Worksheet;
worksheetNda.Name = "LotSize";
int firstLine = 2 + index * 3;
int ricNb = Int32.Parse(content["Official Code:"]);
// NDA Titles
if (index == 0)
{
worksheetNda.Cells[1, 1] = "RIC";
worksheetNda.Cells[1, 2] = "ROUND LOT SIZE";
}
// RIC
worksheetNda.Cells[firstLine, 1] = content["Official Code:"] + ".HK";
worksheetNda.Cells[firstLine + 1, 1] = content["Official Code:"] + "ta.HK";
worksheetNda.Cells[firstLine + 2, 1] = content["Official Code:"] + "stat.HK";
if (content.ContainsKey("Lot Size:"))
{
worksheetNda.Cells[firstLine, 2] = content["Lot Size:"];
}
else
{
worksheetNda.Cells[firstLine, 2] = content["Lot Size (NEW):"];
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbookNda.SaveAs(workbookNda.FullName, XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
AddResult(Path.GetFileNameWithoutExtension(workbookNda.FullName), workbookNda.FullName, "nda");
workbookNda.Close();
}
catch (Exception ex)
{
string msg = "Cannot generate CSV file :" + ex;
LogMessage(msg, Logger.LogType.Error);
CleanExit();
}
}
ReadWorksheet
(
Microsoft.Office.Interop.Excel.Workbook workbook,
ReadWorkbookContext readWorkbookContext,
IGraph graph
)
{
Debug.Assert(workbook != null);
Debug.Assert(readWorkbookContext != null);
Debug.Assert(graph != null);
AssertValid();
// Attempt to get the optional tables that contain group data.
ListObject oGroupTable, oGroupVertexTable;
if (
ExcelUtil.TryGetTable(workbook, WorksheetNames.Groups,
TableNames.Groups, out oGroupTable)
&&
ExcelUtil.TryGetTable(workbook, WorksheetNames.GroupVertices,
TableNames.GroupVertices, out oGroupVertexTable)
)
{
// The code that reads the tables can handle hidden rows, but not
// hidden columns. Temporarily show all hidden columns in the
// table.
ExcelHiddenColumns oHiddenGroupColumns =
ExcelColumnHider.ShowHiddenColumns(oGroupTable);
ExcelHiddenColumns oHiddenGroupVertexColumns =
ExcelColumnHider.ShowHiddenColumns(oGroupVertexTable);
try
{
ReadGroupTables(oGroupTable, oGroupVertexTable,
readWorkbookContext, graph);
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oGroupTable,
oHiddenGroupColumns);
ExcelColumnHider.RestoreHiddenColumns(oGroupVertexTable,
oHiddenGroupVertexColumns);
}
}
}
private void button5_Click(object sender, EventArgs e)
{
Demand demand = (Demand)propertyGrid.SelectedObject;
NumberToLetterUtil converter = new NumberToLetterUtil();
NumberToLetterUtil numUtil = new NumberToLetterUtil();
if (demand.RowSequenceType == RowSequenceType.Range)
{
try
{
ExcelUtil excel = new ExcelUtil();
excel.Open(demand.Path + "/" + demand.FileName);
excel.Worksheet(demand.WorkSheet);
Object[,] obj = excel.ReadCellByRange(demand.RowRangeFrom + ":" + demand.RowRangeTo);
for (int row = 1; row <= obj.GetUpperBound(0); row++)
{
for (int column = 1; column <= obj.GetUpperBound(1); column++)
{
Console.WriteLine(numUtil.getLetterByNumber(column) + "[" + numUtil.getNumberByLetter(numUtil.getLetterByNumber(column)) + "]" + row + " = " + obj[row, column]);
}
}
excel.Close();
}
catch (WorksheetNotFoundException wnfe)
{
MessageBox.Show(null, wnfe.Message, "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (RangeInvalidException rie) {
MessageBox.Show(null, rie.Message, "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (demand.RowSequenceType == RowSequenceType.Collection)
{
try
{
readDDPDemandByCollection(demand);
}
catch (NullReferenceException nre)
{
MessageBox.Show(null, nre.Message.ToString(), "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Console.WriteLine("Success!");
Console.WriteLine(demand.Region);
}
public void CreateAccount_IfEmailIdAlreadyexistAndUserTrytoCreateAccount()
{
Currentpage = new LandingPage();
Currentpage.As <LandingPage>().Clickon_CreateAccountLink();
Currentpage = new CreateAccountPage();
ExcelUtil.PopulateInCollection("./TestDataWestband.xlsx", "RegistrationData");
Currentpage.As <CreateAccountPage>().RegistrationFpage(ExcelUtil.ReadData(8, "Firstname"), ExcelUtil.ReadData(8, "Lastname"), ExcelUtil.ReadData(8, "Email"), ExcelUtil.ReadData(8, "Contact"));
Currentpage.As <CreateAccountPage>().RegistrationSecondpage(ExcelUtil.ReadData(8, "Password"), ExcelUtil.ReadData(8, "ConfirmPassword"));
Currentpage.As <CreateAccountPage>().ClickonIAgreeCheckbox();
Currentpage.As <CreateAccountPage>().ClickonCreateAccountButton();
Currentpage.As <CreateAccountPage>().Wait4Element(Currentpage.As <CreateAccountPage>().Snakbar_UseremailAlreadyExist);
Assert.AreEqual("An account already exists with this email address. Please login (101)", ApplicationContext.Query(Currentpage.As <CreateAccountPage>().Snakbar_UseremailAlreadyExist).First().Text);
Currentpage.As <CreateAccountPage>().ClickOn_Alert_OK();
}
/// <summary>
/// Initialize the workbook and worksheet
/// </summary>
/// <param name="workbook"></param>
/// <param name="worksheet"></param>
private void InitializeExcel(out Workbook workbook, out Worksheet worksheet)
{
resultFileName = DateTime.Now.ToString("dMMMyyyy_HHmm");
string title = String.Format("{0}Results_{1}.xls", configObj.ResultFilePath, resultFileName);
workbook = ExcelUtil.CreateOrOpenExcelFile(app, title);
worksheet = null;
if (workbook != null)
{
if ((worksheet = (Worksheet)workbook.Worksheets[1]) != null)
{
worksheet.Name = "MUFD";
}
}
}
ActivateWorksheet
(
Worksheet worksheet
)
{
Debug.Assert(worksheet != null);
AssertValid();
ExcelActiveWorksheetState oExcelActiveWorksheetState =
GetActiveWorksheetState();
ExcelUtil.ActivateWorksheet(worksheet);
return (oExcelActiveWorksheetState);
}
/// <summary>
/// Obtiene y escribe el valor del primer hecho correspondiente al id de concepto enviado como parámetro
/// </summary>
/// <param name="instancia">Documento de instancia actualmente procesado</param>
/// <param name="idConcepto">Concepto a escribir</param>
private void EscribirValorPrimerHecho(DocumentoInstanciaXbrlDto instancia, string idConcepto, ISheet hojaAExportar,
int renglonDestino, int columnaDestino)
{
if (instancia.HechosPorIdConcepto.ContainsKey(idConcepto) && instancia.HechosPorIdConcepto[idConcepto].Count > 0)
{
var hecho = instancia.HechosPorId[instancia.HechosPorIdConcepto[idConcepto][0]];
if (hecho != null)
{
ExcelUtil.AsignarValorCelda(hojaAExportar, renglonDestino, columnaDestino, hecho.Valor,
instancia.Taxonomia.ConceptosPorId[idConcepto].EsTipoDatoNumerico ?
CellType.Numeric : CellType.String
, hecho);
}
}
}
private void VerifyExcelInstallation(string filePath)
{
if (!string.IsNullOrWhiteSpace(filePath))
{
if (ExcelUtil.HasExcelExtension(filePath) && !ExcelUtil.IsExcelInstalled())
{
MessageBox.Show(
"WARNING: An Excel installation was not detected! Support for Excel files requires an Excel installation.",
this.Text,
MessageBoxButtons.OK,
MessageBoxIcon.Warning
);
}
}
}
public GoogleSpecFlowFeatureSteps()
{
if (ExcelUtil._dataCollection.Count == 0)
{
var directoryInfo = Directory.GetParent(Directory.GetCurrentDirectory()).Parent;
if (directoryInfo != null)
{
if (directoryInfo.Parent != null)
{
string excelPath = directoryInfo.Parent.FullName + @"\Automation.Test\Data\Google.xlsx";
ExcelUtil.PopulateInCollection(excelPath);
}
}
}
}
public void ImportarDatosDeHojaExcel(NPOI.SS.UserModel.ISheet hojaAImportar, NPOI.SS.UserModel.ISheet hojaPlantilla, Dto.DocumentoInstanciaXbrlDto instancia,
string rol, AbaxXBRLCore.Common.Dtos.ResumenProcesoImportacionExcelDto resumenImportacion, Model.IDefinicionPlantillaXbrl plantillaDocumento)
{
//var idiomaDefault = instancia.Taxonomia != null && instancia.Taxonomia.IdiomasTaxonomia != null && instancia.Taxonomia.IdiomasTaxonomia.Keys.Count > 0 ?
// instancia.Taxonomia.IdiomasTaxonomia.Keys.First() : String.Empty;
//
string idContextoGeneral = instancia.ContextosPorId.First().Value.Id;
var numRenglones = hojaAImportar.LastRowNum;
for (var iRenglon = _renglonInicioHechos; iRenglon <= numRenglones; iRenglon++)
{
var numColumnas = hojaAImportar.GetRow(iRenglon).LastCellNum;
if (numColumnas > _columnaInicioHechos)
{
var valorConsecutivo = ExcelUtil.ObtenerValorCelda(hojaAImportar, iRenglon, _columnaInicioHechos);
if (!String.IsNullOrEmpty(valorConsecutivo))
{
//Localizar tupla
//Crear tupla
var hechoTupla = instancia.CrearHecho(_idConceptoRegistroCartera, null, null, "INV" + Guid.NewGuid().ToString());
plantillaDocumento.InyectaHechoADocumentoInstancia(hechoTupla);
hechoTupla.Hechos = new List <String>();
for (int iCol = _columnaInicioHechos; iCol <= numColumnas; iCol++)
{
var valorCelda = ExcelUtil.ObtenerValorCelda(hojaAImportar, iRenglon, iCol);
if (!String.IsNullOrEmpty(valorCelda))
{
var idConcepto = ExcelUtil.ObtenerValorCelda(hojaPlantilla, _renglonIdConceptos, iCol);
if (!String.IsNullOrEmpty(idConcepto))
{
//buscar el hecho dentro de la tupla, si no existe, crearlo
var hechoEnTupla = CrearHechoEnTupla(hechoTupla, idConcepto, instancia, plantillaDocumento, idContextoGeneral);
hechoEnTupla.Valor = valorCelda;
//hechoEnTupla.NotasAlPie = ExcelUtil.ObtenerComentariosCelda(hojaAImportar.GetRow(iRenglon), iCol, idiomaDefault);
}
}
}
}
}
}
}
private void GenerateELWFMSingleSearchTemplate_xls()
{
if (koreaList.Count > 0)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
Logger.Log("Excel could not be started. Check that your office installation and project reference are correct!", Logger.LogType.Error);
return;
}
try
{
String filename = "Search by ISIN " + DateTime.Today.ToString("yyyy-MM-dd") + ".xls";
String ipath = configObj.Korea_SearchByISIN_GenerateFileConfig.WORKBOOK_PATH + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = ExcelUtil.GetWorksheet(configObj.Korea_SearchByISIN_GenerateFileConfig.WORKSHEET_NAME, wBook);
if (wSheet == null)
{
Logger.Log("Worksheet could not be created. Check that your office installation and project reference are correct!", Logger.LogType.Error);
return;
}
CreateExcelTitle(wSheet);
int startLine = 2;
while (wSheet.get_Range("C" + startLine, Type.Missing).Value2 != null && wSheet.get_Range("C" + startLine, Type.Missing).Value2.ToString() != String.Empty)
{
startLine++;
}
LoopPrintKoreaListData(wSheet, startLine);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
}
catch (Exception ex)
{
Logger.Log("Error found in GenerateELWFMSingleSearchTemplate_xls : " + ex.ToString(), Logger.LogType.Warning);
return;
}
finally
{
excelApp.Dispose();
}
}
}
public void TC1_CreateNewCustomer()
{
//Following Variable needs to be filled accordingly for each Test Method. Do not have spaces within Module & Test name
//******************************************************************************************************
Reporter.strCurrentModule = "Manager_Account";
Reporter.strCurrentScenarioID = "SC1";
Reporter.strCurrentTestID = "TC1";
Reporter.strCurrentTestDesc = "Create New Customer";
//*******************************************************************************************************
//Default Reporting function call for Test Method. Please do not REMOVE this functions calls
Reporter.HTML_TestCase_Initialize();
DataTable oTable = ExcelUtil.ExcelToTable(GlbVar.strRelativePath + "03_Application_Tier" + GlbVar.sysFileSeperator + "TestData" + GlbVar.sysFileSeperator + "TestData.xlsx", "Data");
DataRow[] oDataRows = oTable.Select("TestCaseID = '" + Reporter.strCurrentTestID + "' and ExecutionFlag = 'Y'");
foreach (DataRow oDataRow in oDataRows)
{
//Default Test data Fetching code. Please do not remove it
ExcelUtil.oCurrentDataRow = oDataRow;
string CustName = oDataRow["CustName"].ToString();
int.TryParse(oDataRow["Iteration"].ToString(), out Reporter.intCurrentIteration);
//Default Reporting function call for Test Method. Please do not REMOVE this functions calls
Reporter.HTML_TC_Iteration_Initialize();
//Start Point for Coding the Test case.
//*****************************************************************************************************
Console.WriteLine(CustName);
LoginPage oLoginPage = new LoginPage();
oLoginPage.loginToApplication()
.navigateToNewCustPage()
.fillNewCustDetails()
.SubmitNewCustDetails()
.navigateToHomePage()
.applicationLogout();
//this.UIMap.GDrive();
//*****************************************************************************************************
//End Point for Coding the Test case.
}
//Default Reporting function call for Test Method. Please do not REMOVE this functions calls
Reporter.HTML_Execution_Summary_TCAddLink();
Reporter.HTML_TC_Iteration_Footer();
}
TryStartAutoFill
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
Boolean bShowVertexLabels,
String sVertexLabelColumnName,
out ListObject oEdgeTable,
out ListObject oVertexTable,
out ExcelHiddenColumns oHiddenEdgeColumns,
out ExcelHiddenColumns oHiddenVertexColumns
)
{
Debug.Assert(oWorkbook != null);
SetScreenUpdating(oWorkbook, false);
(new VertexWorksheetPopulator()).PopulateVertexWorksheet(
oWorkbook, false);
oEdgeTable = oVertexTable = null;
oHiddenEdgeColumns = oHiddenVertexColumns = null;
if (
!ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Edges,
TableNames.Edges, out oEdgeTable)
||
!ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oVertexTable)
)
{
return(false);
}
// Only visible cells are read and written to. Temporarily show all
// hidden columns in the tables.
oHiddenEdgeColumns = ExcelColumnHider.ShowHiddenColumns(oEdgeTable);
oHiddenVertexColumns = ExcelColumnHider.ShowHiddenColumns(
oVertexTable);
if (bShowVertexLabels)
{
TableColumnMapper.MapViaCopy(oVertexTable, sVertexLabelColumnName,
VertexTableColumnNames.Label);
}
return(true);
}
GetAllSettings()
{
AssertValid();
if (m_oSettings == null)
{
m_oSettings = new Dictionary <String, Object>();
// Attempt to get the optional table and table columns that contain
// the settings.
ListObject oPerWorkbookSettingsTable;
Range oNameColumnData, oValueColumnData;
Object [,] aoNameColumnValues, aoValueColumnValues;
if (
TryGetPerWorkbookSettingsTable(out oPerWorkbookSettingsTable)
&&
ExcelUtil.TryGetTableColumnDataAndValues(
oPerWorkbookSettingsTable,
PerWorkbookSettingsTableColumnNames.Name,
out oNameColumnData, out aoNameColumnValues)
&&
ExcelUtil.TryGetTableColumnDataAndValues(
oPerWorkbookSettingsTable,
PerWorkbookSettingsTableColumnNames.Value,
out oValueColumnData, out aoValueColumnValues)
)
{
Int32 iRows = oNameColumnData.Rows.Count;
for (Int32 iRowOneBased = 1; iRowOneBased <= iRows;
iRowOneBased++)
{
String sName;
if (ExcelUtil.TryGetNonEmptyStringFromCell(
aoNameColumnValues, iRowOneBased, 1, out sName))
{
m_oSettings[sName] =
aoValueColumnValues[iRowOneBased, 1];
}
}
}
}
return(m_oSettings);
}
ReadRadius
(
ExcelTableReader.ExcelTableRow oRow,
VertexRadiusConverter oVertexRadiusConverter,
IVertex oVertex
)
{
Debug.Assert(oRow != null);
Debug.Assert(oVertex != null);
Debug.Assert(oVertexRadiusConverter != null);
AssertValid();
String sRadius;
if (!oRow.TryGetNonEmptyStringFromCell(VertexTableColumnNames.Radius,
out sRadius))
{
return(new Nullable <Single>());
}
Single fRadius;
if (!Single.TryParse(sRadius, out fRadius))
{
Range oInvalidCell = oRow.GetRangeForCell(
VertexTableColumnNames.Radius);
OnWorkbookFormatError(String.Format(
"The cell {0} contains an invalid size. The vertex size,"
+ " which is optional, must be a number. Any number is"
+ " acceptable, although {1} is used for any number less than"
+ " {1} and {2} is used for any number greater than {2}."
,
ExcelUtil.GetRangeAddress(oInvalidCell),
VertexRadiusConverter.MinimumRadiusWorkbook,
VertexRadiusConverter.MaximumRadiusWorkbook
),
oInvalidCell
);
}
oVertex.SetValue(ReservedMetadataKeys.PerVertexRadius,
oVertexRadiusConverter.WorkbookToGraph(fRadius));
return(new Nullable <Single>(fRadius));
}
TryGetEdgeKey
(
Int32 iRowOneBased,
Object [,] aoVertex1NameValues,
Object [,] aoVertex2NameValues,
Object [,] aoThirdColumnValues,
Boolean bGraphIsDirected,
out String sEdgeKey
)
{
Debug.Assert(iRowOneBased >= 1);
Debug.Assert(aoVertex1NameValues != null);
Debug.Assert(aoVertex2NameValues != null);
AssertValid();
sEdgeKey = null;
String sVertex1Name, sVertex2Name;
if (
!ExcelUtil.TryGetNonEmptyStringFromCell(aoVertex1NameValues,
iRowOneBased, 1, out sVertex1Name)
||
!ExcelUtil.TryGetNonEmptyStringFromCell(aoVertex2NameValues,
iRowOneBased, 1, out sVertex2Name)
)
{
return(false);
}
sEdgeKey = EdgeUtil.GetVertexNamePair(sVertex1Name, sVertex2Name,
bGraphIsDirected);
String sThirdColumnValue;
if (
aoThirdColumnValues != null
&&
ExcelUtil.TryGetNonEmptyStringFromCell(aoThirdColumnValues,
iRowOneBased, 1, out sThirdColumnValue)
)
{
sEdgeKey += EdgeUtil.VertexNamePairSeparator;
sEdgeKey += sThirdColumnValue;
}
return(true);
}
OnWorkbookFormatException
(
WorkbookFormatException workbookFormatException
)
{
Debug.Assert(workbookFormatException != null);
Range oRangeToSelect = workbookFormatException.RangeToSelect;
if (oRangeToSelect != null)
{
ExcelUtil.SelectRange(oRangeToSelect);
}
FormUtil.ShowWarning(workbookFormatException.Message);
}
public string loginDataDriven2(int linha, string fileName)
{
ExcelUtil util = new ExcelUtil();
util.PopulateInCollection(fileName);
String userName = util.ReadData(linha, "Column0"); //Login
String password = util.ReadData(linha, "Column1"); //senha 01
SendKeys(campoUsuario, userName);
clicaBtnEntra();
SendKeys(campoSenha, password);
clicaBtnEntra();
return(userName);
}
//Update the worksheet format to fit some requiements:
//
//1. Delete A column;
//2. Delete D column;
//3. Insert blank column after F column;
//4. Insert black row before 11 row.
public static void UpdateWorksheetFormat(Worksheet worksheet)
{
if (worksheet == null)
{
throw new Exception(String.Format("Cannot Find the worksheet : {0}", worksheet));
}
RangeDeleteCols(1, 1, worksheet);
RangeDeleteCols(1, 4, worksheet);
Range insertCols = ExcelUtil.GetRange(1, 7, worksheet);
ExcelUtil.InsertBlankCols(insertCols, 1);
Range insertRows = ExcelUtil.GetRange(11, 1, worksheet);
ExcelUtil.InsertBlankRows(insertRows, 1);
//RangeDeleteCols(1, 9, worksheet);
}
/// <summary>
/// Initialize workbook
/// </summary>
/// <returns>the created or opened workbook</returns>
private Workbook InitializeExcel()
{
Workbook workbook;
try
{
workbook = ExcelUtil.CreateOrOpenExcelFile(app, Path.GetDirectoryName(configObj.TemplateFilePath) + @"\test" + fileNb + ".xls");
return(workbook);
}
catch (Exception ex)
{
string msg = "Error :" + ex;
Logger.Log(msg, Logger.LogType.Error);
throw new Exception("Cannot load workbook");
}
}
OnSelectionChangedInGraphGroup()
{
AssertValid();
if (ExcelUtil.WorksheetIsActive(m_oGroupWorksheet.InnerObject))
{
SelectGroupTableRows();
}
else
{
// Defer the selection of the groups the group table until the
// group worksheet is activated.
m_bUpdateGroupSelectionOnActivation = true;
}
}
/// <summary>
/// Workbook Initialization from path
/// </summary>
/// <param name="path">Path of the workbook</param>
/// <returns>The Created or opened workbook</returns>
private Workbook InitializeExcel(string path)
{
Workbook workbook;
try
{
workbook = ExcelUtil.CreateOrOpenExcelFile(app, path);
return(workbook);
}
catch (Exception ex)
{
string msg = "Error :" + ex;
Logger.Log(msg, Logger.LogType.Error);
throw new Exception("Cannot load workbook");
}
}