/// <summary>
/// Creates excel file based on a key-value pair
/// </summary>
public void CreateExcelFromKeyValuePair()
{
var path = Path.Combine(Environment.CurrentDirectory, "NetPricesByVatNumber.xlsx");
var elements = _xmlHandler.GetNetPriceGroupedByVatNumber();
var excel = new Excel();
try
{
excel.CreateNewWorkbook();
excel.SaveAs(path);
}
finally
{
excel.Close();
}
try
{
excel.Open(path);
for (int i = 0; i < _excelHeader2.Count; i++)
{
excel.WriteToCell(0, i, _excelHeader2[i]);
}
double aggregateNetPrice = 0;
int count = 1;
foreach (var item in elements)
{
excel.WriteToCell(count, 0, item.Key);
foreach (var priceString in item.Value)
{
double netPrice;
double.TryParse(priceString, out netPrice);
if (netPrice < 100000 && netPrice > -100000)
{
aggregateNetPrice += netPrice;
}
}
excel.WriteToCell(count, 1, aggregateNetPrice.ToString());
aggregateNetPrice = 0;
count++;
}
excel.Save();
}
finally
{
excel.Close();
}
}
static void Main(string[] args)
{
var folderPath = Path.Combine(Directory.GetCurrentDirectory(), "..", "..")
+
@"\SampleExcelFiles\";
var fileNames = Directory.GetFiles(folderPath, "*.xlsx");
Excel excel = null;
var hotelDAL = new HotelDAL();
try
{
foreach (var item in fileNames)
{
var success = true;
Console.WriteLine($"Path = {item}");
excel = new Excel(item, 1);
try
{
Console.WriteLine($"Loading Sheet : {Path.GetFileName(item)}");
var rows = excel.RowsNumbers();
for (var i = 1; i < rows; i++) // row 0 is the columns' names
{
if (!hotelDAL.Add(excel.GetRow(i), out var message))
{
success = false;
Console.WriteLine($"Error in row ({i}).");
Console.WriteLine($"----{message}----");
}
Console.Write("\b\b\b" + "" + (int)((double)i / rows * 100) + "%");
}
excel.Close();
Console.WriteLine("\b\b\b*********Finished" +
$"{ (success ? "Successfully." : ".")}*******\n\n");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine($"Loading {Path.GetFileName(item)} does not compeleted Successfully\n\n");
}
finally
{
excel.Close();
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
excel?.Close();
}
}
private string AmountOfPlanets(int excelPage)
{
Excel excel = OpenFileAt(excelPage);
int planet = (int)excel.ReadCellDouble(1, 8); //amount of planets
planet++;
excel.Close();
string planetNumber = "";
if (planet <= 9)
{
planetNumber = "00" + planet.ToString();
}
else if (planet >= 10 && planet <= 99)
{
planetNumber = "0" + planet.ToString();
}
else
{
planetNumber = planet.ToString();
}
return(planetNumber);
}
public void Select_and_Close(object sender, RoutedEventArgs e)
{
Console.WriteLine("bottone premuto - lettura excel");
var fogliDaLeggere = new List <int>();
foreach (Sheet s in listaFogli)
{
if (s.Seleziona)
{
fogliDaLeggere.Add(s.Numero + 1);
}
}
foreach (int foglio in fogliDaLeggere)
{
Console.WriteLine("LETTURA FOGLIO " + foglio);
for (int i = 1; i < 255; i++)
{
string indirizzo = excel.ReadCell(foglio, i, 1);
Console.WriteLine(i + ") " + indirizzo);
int a = int.Parse(indirizzo.Substring(0, 3));
int b = int.Parse(indirizzo.Substring(4, 3));
int c = int.Parse(indirizzo.Substring(8, 3));
int d = int.Parse(indirizzo.Substring(12, indirizzo.Length - 12));
int presente = int.Parse(excel.ReadCell(foglio, i, 3));
var ip = new Ip(a, b, c, d, excel.ReadCell(foglio, i, 2), null, null, presente);
Console.WriteLine(ip.descrizione + " " + ip.a, ip.b, ip.c, ip.d);
Globals.INDIRIZZI.Add(ip);
}
}
excel.Close();
this.Close();
}
public Program()
{
filereader = new Filereader();
string inPath = AppDomain.CurrentDomain.BaseDirectory + "in.xlsx";
Console.WriteLine(inPath);
Excel excel = new Excel(inPath);
try
{
for (int i = 0; i < excel.getRows() && i < 10; i++)
{
excel.WriteCell(i, 0, FixRow(excel.ReadCell(i, 0)));
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine();
Console.WriteLine("Finished! Used time: " + (DateTime.UtcNow - Process.GetCurrentProcess().StartTime.ToUniversalTime()) + "!");
excel.Save();
excel.Close();
}
public void cancelCheckout(Excel.Workbook activeWorkbook)
{
try
{
if (MessageBox.Show(resources.GetString("sure_cancel_checkout"), resources.GetString("cancelcheckout"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
{
object saveChanges = true;
object missing = Type.Missing;
String localFileName = activeWorkbook.FullName;
activeWorkbook.Close(saveChanges, missing, missing); // Always we save document
docXML.refresh(); // Refresh document list
if (docXML.isOpenKMDocument(localFileName))
{
OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName);
docXML.remove(oKMDocument);
DocumentLogic.cancelCheckout(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword());
if (File.Exists(localFileName))
{
File.Delete(localFileName);
}
}
}
}
catch (Exception e)
{
String errorMsg = "OpenKMExcelAddIn - (cancelCheckoutButton_Click)\n" + e.Message + "\n\n" + e.StackTrace;
MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
/// <summary>
/// Creates an excel file based on xml document
/// </summary>
public void CreateExcelFromXml()
{
var path = Path.Combine(Environment.CurrentDirectory, "InvoicesWithVat100OrMore.xlsx");
var elements = _xmlHandler.GetInvoicesWithVat100();
var excelModels = _xmlHandler.CreateExcelModelsFromXml(elements);
var excel = new Excel();
try
{
excel.CreateNewWorkbook();
excel.SaveAs(path);
}
finally
{
excel.Close();
}
try
{
excel.Open(path);
for (int i = 0; i < _excelHeader1.Count; i++)
{
excel.WriteToCell(0, i, _excelHeader1[i]);
}
for (int i = 0; i < excelModels.Count; i++)
{
excel.WriteToCell(i + 1, 0, excelModels[i].VatNumber);
excel.WriteToCell(i + 1, 1, excelModels[i].InvoiceNumber);
excel.WriteToCell(i + 1, 2, excelModels[i].FinalNetPrice);
excel.WriteToCell(i + 1, 3, excelModels[i].FinalVat);
}
excel.Save();
}
finally
{
excel.Close();
}
}
public void DoWorkCalThread(Action <int> percent)
{
mExcel = new Excel(ImportFileNameList, false);
float proc = (float)0.0;
for (int i = 2; i <= mExcel.RowCount; i++)
{
proc = (float)i / (float)mExcel.RowCount * (float)100;
string excelStaffName = "";
excelStaffName = mExcel.GetCell(i, 2);
excelStaffName.Trim();
if (mUiqueNameList.Contains(excelStaffName) || excelStaffName == "")
{
continue;
}
mUiqueNameList.Add(excelStaffName);
percent((int)proc);
}
mExcel.Visible = true;
mExcel.Close();
mExcel = new Excel(ImportFileOri, false);
proc = (float)0.0;
for (int i = 2; i <= mExcel.RowCount; i++)
{
StatisticsFileFormat off = new StatisticsFileFormat();
proc = (float)i / (float)mExcel.RowCount * (float)100;
off.ProjectOrder = mExcel.GetCell(i, 1);
off.ProjectID = mExcel.GetCell(i, 3);
off.ProjectName = mExcel.GetCell(i, 4);
off.ProjectManager = mExcel.GetCell(i, 5);
off.ProjectStaffName = mExcel.GetCell(i, 6);
off.ProjectDep = mExcel.GetCell(i, 7);
off.ProjectDurationDays = mExcel.GetCell(i, 8);
off.ProjectDate = mExcel.GetCell(i, 9);
off.ProjectType = mExcel.GetCell(i, 10);
percent((int)proc);
mOriFileFormatList.Add(off);
}
mExcel.Visible = true;
mExcel.Close();
GenerateTargetList();
MergeToFinalResult();
}
public ObservableCollection <Company> GetUsCompanies(Excel excel, Range range, int firstUsedRow, string stockCode)
{
var companies = new ObservableCollection <Company>();
for (var i = firstUsedRow - 1; i < range.Rows.Count; i++)
{
var company = new UsaCompany
{
Name = (string)(excel.Worksheet.Cells[i + 1, ColumnName] as Range).Value
};
var rangeValue = (excel.Worksheet.Cells[i + 1, ColumnSymbol] as Range).Value;
company.Stock.Code = rangeValue.ToString();
company.Stock.StockExchange = GetStockExchange();
var industry = (string)(excel.Worksheet.Cells[i + 1, ColumnIndustry] as Range).Value;
if (!_stockPriceValuationModel.Industries.Contains(industry))
{
_stockPriceValuationModel.Industries.Add(industry);
}
company.Industry = industry;
var sector = (string)(excel.Worksheet.Cells[i + 1, ColumnSector] as Range).Value;
if (!_stockPriceValuationModel.Sectors.Contains(sector))
{
_stockPriceValuationModel.Sectors.Add(sector);
}
company.Sector = sector;
if (string.IsNullOrEmpty(stockCode) || string.Equals(company.Stock.Code, stockCode, StringComparison.OrdinalIgnoreCase))
{
companies.Add(company);
}
ProgressBarValue++;
if (!string.IsNullOrEmpty(stockCode) && string.Equals(company.Stock.Code, stockCode, StringComparison.OrdinalIgnoreCase))
{
break;
}
}
excel.Close();
return(companies);
}
//******INNER_METHODS*****************************************************************************************************************************************
public GACShip_Agent_LandingPage GACShipLoginToApplication()
{
GACShip_Agent_LoginPage loginPage = new GACShip_Agent_LoginPage();
Excel excel = new Excel(@"C:\Users\edqu01\Documents\GAC Automation\GACShip Agent Automation - August-31\ConsoleApp1\TestData\GACShipTestData.xlsx", "LogIn");
PropertyCollection.ChildTest = PropertyCollection.ExtentTest.CreateNode(GetActions.GetMyMethodName());
//Checks first if element is present then type in the Username
if (InitialAssertion.ifElementIsPresentandVisible(loginPage.Username) == true)
{
SetActions.FillInTextField((excel.ReadDatabyColumnName("GACShip", "UserName")), loginPage.Username);
SeleniumReporting.ElementPresentVerification(true, "UserName Text Field");
SeleniumReporting.WriteMessageOnTheReport("Username was typed in the text field.");
}
else
{
SeleniumReporting.ElementPresentVerification(false, "UserName Text Field");
}
//Checks first if element is present then type in the Password
if (InitialAssertion.ifElementIsPresentandVisible(loginPage.Password) == true)
{
SetActions.FillInTextField((excel.ReadDatabyColumnName("GACShip", "Password")), loginPage.Password);
SeleniumReporting.ElementPresentVerification(true, "PassWord Text Field");
SeleniumReporting.WriteMessageOnTheReport("Password was typed in the text field.");
}
else
{
SeleniumReporting.ElementPresentVerification(false, "PassWord Text Field");
}
SetActions.ClickButton(loginPage.LogInButton, "LogIn");
excel.Close();
GACShip_Agent_LandingPage landingPage = new GACShip_Agent_LandingPage();
InitialAssertion.WaitForElementLoad(By.XPath(LandingPageHeader), 30);
return(new GACShip_Agent_LandingPage());
//************************************************************************************************************************************************************************
}
static void Main()
{
Excel excel = new Excel();
var us = new TImesheetWriter.Properties.Settings1();
string path = us.FileLocation; // done
string dateChoice; //done
string favChoice; //done
string jobNumber; //done either us.previous job, or new job
string firstTimeSetupName = us.Name; //done
string name;
string userInitials = ""; //done
double hours; //done
string day;
string month = Convert.ToString(DateTime.Today.Month);
string year = Convert.ToString(DateTime.Today.Year);
bool offsite = us.Offsite;
#region InitialGreeting
Console.WriteLine("**********************************************************************");
Console.WriteLine("");
Console.WriteLine(" Welcome to the Proto Timesheet writer");
Console.WriteLine(" Created by Michael Spence-High ");
Console.WriteLine(" Press Y to run start up ");
Console.WriteLine("**********************************************************************");
Thread.Sleep(2000);
Console.WriteLine("\n Press Enter to Continue....");
if ('y' == Console.ReadKey().KeyChar)
{
us.FirstTimeSetupRequired = true;
us.Save();
}
else
{
us.FirstTimeSetupRequired = false;
}
if (us.FirstTimeSetupRequired == true)
{
bool _setter = false;
Console.WriteLine("Lets go through the first time setup, please enter your Name: ");
Thread.Sleep(1000);
us.Name = Console.ReadLine();
Console.WriteLine("Please Select a File Location");
Thread.Sleep(1000);
var FBD = new System.Windows.Forms.FolderBrowserDialog();
FBD.ShowDialog();
us.FileLocation = FBD.SelectedPath;
path = us.FileLocation;
us.FirstTimeSetupRequired = _setter;
us.Save();
}
Console.Clear();
Thread.Sleep(1000);
#endregion
//by this point we have greeted the user, first time run sets Name and Path
#region CaseStatementForTodayYesterday
while (true)
{
Console.WriteLine("\n\n");
Console.WriteLine("Are you looking to create a timesheet for today, yesterday, or other?...");
Console.WriteLine("Press Y for yesterday, T for today, O for other");
string _todayYesterday = Console.ReadLine().ToLower();
if (_todayYesterday != "")
{
if (_todayYesterday == "y" || _todayYesterday == "t" || _todayYesterday == "o")
{
dateChoice = _todayYesterday;
break;
}
else
{
Console.WriteLine("Response was not 'Y' or 'T'.");
Thread.Sleep(2000);
}
}
else
{
Console.WriteLine("Cannot be empty.");
Thread.Sleep(2000);
}
}
if (dateChoice == "y")
{
day = Convert.ToString(DateTime.Today.AddDays(-1).Day);
}
else if (dateChoice == "t")
{
day = Convert.ToString(DateTime.Today.Day);
}
else
{
Console.WriteLine("this isnt implemented yet, using today");
day = Convert.ToString(DateTime.Today.Day);
}
Console.Clear();
Thread.Sleep(1000);
#endregion
//by this point, we have greeted the user, and gotten the choice of when they would like the timesheet to be created for (yesterday or today)
#region IsFavourite?
while (true)
{
Console.WriteLine("\n\n");
Console.WriteLine("is this timesheet the same as last time?: " + us.PreviousJobNumbers + "\noffsite?: " + us.Offsite);
Console.WriteLine("\nPress Y for yes, N for no");
string _favResponse = Console.ReadLine().ToLower();
if (_favResponse != "")
{
if (_favResponse == "y" || _favResponse == "n")
{
favChoice = _favResponse;
break;
}
else
{
Console.WriteLine("Response was not 'Y' or 'N'." + us.PreviousJobNumbers);
Thread.Sleep(2000);
}
}
else
{
Console.WriteLine("Cannot be empty." + us.PreviousJobNumbers);
Thread.Sleep(2000);
}
}
Console.Clear();
Thread.Sleep(1000);
#endregion
//by this point we have greated, decided which day this sheet will be made for, and know whether to use yesterday or todays timesheet
#region Job number, Name, Initials, hours
//JobNumber loop
while (true)
{
if (favChoice == "y")
{
jobNumber = us.PreviousJobNumbers;
break;
}
else
{
Console.WriteLine("\n\n");
Console.WriteLine("What is the Job Number? : ");
string _jobNumber = Console.ReadLine().ToLower();
if (_jobNumber != "")
{
jobNumber = _jobNumber;
us.PreviousJobNumbers = jobNumber;
us.Save();
while (true)
{
Console.WriteLine("\n\n");
Console.WriteLine("Were you offsite? Y/N : ");
string _offsResp = Console.ReadLine().ToLower();
if (_offsResp == "y")
{
us.Offsite = true;
offsite = us.Offsite;
us.Save();
break;
}
else
{
us.Offsite = false;
offsite = us.Offsite;
us.Save();
break;
}
}
break;
}
else
{
Console.WriteLine("Cannot be empty.");
Thread.Sleep(2000);
}
}
}
//Name and initials loop
#region Initial Generation
name = us.Name;
string[] nameSplit = name.Split(new char[] { ' ', '-' });
foreach (var n in nameSplit)
{
userInitials += n[0];
}
userInitials.ToUpper();
#endregion
//string[] initArray = name.Split();
//foreach (string item in initArray)
//{
// userIntials += item[0].ToString();
//}
//hours worked loop
while (true)
{
Console.WriteLine("\n\n");
Console.WriteLine("Total hours worked for this job?");
string _hours = Console.ReadLine();
if (_hours != "")
{
hours = Convert.ToDouble(_hours);
break;
}
else
{
Console.WriteLine("Cannot be empty.");
Thread.Sleep(2000);
}
}
Console.Clear();
Thread.Sleep(1000);
#endregion
excel.OpenExcel(@"Z:\Git Repos\TimesheetWritingApp\TImesheetWriter\bin\Debug\EmptyTimesheet", 1);
excel.WriteExcel(2, 2, name);
excel.WriteExcel(16, 2, userInitials);
excel.WriteExcel(2, 11, year + "-" + month + "-" + day);
//eventually a for loop here for multiple jobs
if (offsite)
{
excel.WriteExcel((5), 0, "X");
}
excel.WriteExcel((5), 1, jobNumber.ToUpper());
excel.WriteExcel((5), 5, Convert.ToString(hours));
excel.SaveAs(path + "\\" + userInitials + "TimeSheet" + year + month + day);
excel.Close();
Console.WriteLine("END");
Console.ReadLine();
}
//çıktı al
private void button2_Click(object sender, EventArgs e)
{
int satir = dataGridView2.RowCount;
string zaman = "", miktar = "", bas = "", klasor_ad = "";
string[] temp;
int sutun = 2;
if (satir > 0)
{
Fatura_Hedef_Kaynak();
if (excel_hedef == "")
{
pnl_Excel_Hedef hedef = new pnl_Excel_Hedef();
hedef.Show();
}
if (excel_kaynak == "")
{
Excel_Kaynak kaynak = new Excel_Kaynak();
kaynak.Show();
}
klasor_ad = yil.ToString() + "-" + turkce_ay;
//string path_belgeler = @"C:\\Users\\İlker\\Documents\\";
Klasor_Olustur_Sil klasor = new Klasor_Olustur_Sil();
if (!(klasor.klasor_varmi(excel_hedef, klasor_ad)))
{
klasor.klasor_olustur(klasor_ad, excel_hedef + "\\");
}
Excel excel = new Excel(excel_kaynak, 1);
bas = DateTime.Now.ToLongDateString();
excel.Hucreye_yaz(5, 2, firma_adi.ToUpper() + " FATURA DÖKÜMÜ");
excel.Hucreye_yaz(9, 6, adet.ToString());
excel.Hucreye_yaz(10, 6, birim_fiyat.ToString() + " ₺");
excel.Hucreye_yaz(11, 6, toplam_ucret.ToString() + " ₺");
excel.Hucreye_yaz(1, 6, bas);
for (int i = 0; i < satir; i++)
{
zaman = dataGridView2.Rows[i].Cells[0].Value.ToString();
temp = zaman.Split(' ');
excel.Hucreye_yaz(10 + i, sutun, temp[0]);
sutun++;
miktar = dataGridView2.Rows[i].Cells[1].Value.ToString();
excel.Hucreye_yaz(10 + i, sutun, miktar);
sutun = 2;
}
//excel.Save();
if (excel.excel_varmi(excel_hedef + "\\" + klasor_ad, firma_adi + ".xlsx"))
{
excel.excel_sil(excel_hedef + "\\" + klasor_ad, firma_adi + ".xlsx");
excel.SaveAs(@excel_hedef + "\\" + klasor_ad + "\\" + firma_adi + ".xlsx");
}
else
{
excel.SaveAs(@excel_hedef + "\\" + klasor_ad + "\\" + firma_adi + ".xlsx");
}
excel.Close();
//MessageBox.Show(excel_hedef + "\\" + klasor_ad + "\n Adresine Fatura Oluşturuldu.");
DialogResult result1 = MessageBox.Show("Fatura Başarıyla Oluşturuldu.\nFatura Gösterilsin Mi?",
"Fatura",
MessageBoxButtons.YesNo);
if (result1 == DialogResult.Yes)
{
Process.Start(@excel_hedef + "\\" + klasor_ad);
}
}
else
{
MessageBox.Show("Siparişler Listesi Boş Çıktı Alınamaz!!");
}
}
