public static void AddForeignAddress(Address address)
{
XSSFWorkbook hssfwb = ExcelReader_NPOI.OpenXLSX("ForeignAddressList.xlsx", false);
ISheet sheet = hssfwb.GetSheet("Sheet1");
int numberOfRows = sheet.LastRowNum;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
if (sheet.GetRow(i) != null)
{
if (sheet.GetRow(i).GetCell(0).StringCellValue == address.PostCode)
{
break;
}
}
if (i == sheet.LastRowNum)
{
sheet.CreateRow(i + 1); //Creating Row Overwrites whole row
sheet.GetRow(i + 1).CreateCell(0).SetCellValue(address.PostCode);
sheet.GetRow(i + 1).CreateCell(1).SetCellValue(address.Road);
sheet.GetRow(i + 1).CreateCell(2).SetCellValue(address.Town);
sheet.GetRow(i + 1).CreateCell(3).SetCellValue(address.Latitude);
sheet.GetRow(i + 1).CreateCell(4).SetCellValue(address.Longitude);
}
}
ExcelReader_NPOI.WriteXLSX(hssfwb, "ForeignAddressList.xlsx");
totalAddresses.Add(address);
}
public static void AddCustomerToCount(Address address)
{
XSSFWorkbook hssfwb = ExcelReader_NPOI.OpenXLSX("Customers.xlsx", false);
ISheet sheet = hssfwb.GetSheet("Sheet1");
int numberOfRows = sheet.LastRowNum;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
if (sheet.GetRow(i) != null)
{
if (sheet.GetRow(i).GetCell(0).StringCellValue == address.Number && sheet.GetRow(i).GetCell(1).StringCellValue == address.Road)
{
int count = (int)sheet.GetRow(i).GetCell(2).NumericCellValue;
sheet.GetRow(i).GetCell(2).SetCellValue(count + 1);
break;
}
}
if (i == sheet.LastRowNum)
{
sheet.CreateRow(i + 1); //Creating Row Overwrites whole row
sheet.GetRow(i + 1).CreateCell(0).SetCellValue(address.Number);
sheet.GetRow(i + 1).CreateCell(1).SetCellValue(address.Road);
sheet.GetRow(i + 1).CreateCell(2).SetCellValue(0);
sheet.GetRow(i + 1).CreateCell(3).SetCellValue(address.PhoneNumber);
}
}
ExcelReader_NPOI.WriteXLSX(hssfwb, "Customers.xlsx");
}
private static DataTable LoadTotalCustomers()
{
DataTable tempCustomers = new DataTable();
tempCustomers.Columns.Add("Number", typeof(string));
tempCustomers.Columns.Add("Address", typeof(Address));
tempCustomers.Columns.Add("Count", typeof(int));
int[] columnsString = new int[2] {
0, 1
};
int[] columnNumber = new int[1] {
2
};
string[,] output = ExcelReader_NPOI.ReadAllXLSX("Customers.xlsx", columnsString, columnNumber, false);
for (int i = 0; i < output.GetLength(0); i++)
{
if (output[i, 0] != null)
{
string houseNumber = output[i, 0].ToString();
float totalBought = float.Parse(output[i, 2]);
Address address = null;
for (int j = 0; j < totalAddresses.Count; j++)
{
if (totalAddresses[j].Road == output[i, 1])
{
address = totalAddresses[j];
break;
}
}
tempCustomers.Rows.Add(houseNumber, address, totalBought);
}
}
return(tempCustomers);
}
public static void _LoadForeignAddresses()
{
int[] columns = new int[3] {
0, 1, 2,
};
int[] latLong = new int[2] {
3, 4
};
string[,] output = ExcelReader_NPOI.ReadAllXLSX("ForeignAddressList.xlsx", columns, latLong, false);
for (int i = 1; i < output.GetLength(0); i++)
{
Address address = new Address(output[i, 0], output[i, 1], output[i, 2], double.Parse(output[i, 3]), double.Parse(output[i, 4]));
totalAddresses.Add(address);
}
}
private static List <Address> LoadAddressesFrom(string source, bool local)
{
List <Address> addresses = new List <Address>();
int[] columns = new int[3] {
0, 1, 2,
};
int[] latLong = new int[2] {
3, 4
};
string[,] output = ExcelReader_NPOI.ReadAllXLSX(source, columns, latLong, local);
for (int i = 1; i < output.GetLength(0); i++)
{
Address address = new Address(output[i, 0], output[i, 1], output[i, 2], double.Parse(output[i, 3]), double.Parse(output[i, 4]));
addresses.Add(address);
}
return(addresses);
}
public static void AddToRecentOrder(OrderDetails orderDetails)
{
XSSFWorkbook hssfwb = null;
try
{
hssfwb = ExcelReader_NPOI.OpenXLSX("OrderLog.xlsx", false);
}
catch (IOException)
{
Debug.WriteLine("File in use? Cannot open file.");
return;
}
ISheet sheet = hssfwb.GetSheet("Sheet1");
Address address = orderDetails.CurrentAddress;
var items = orderDetails.ItemBasket.Items;
int lastRow = sheet.LastRowNum;
sheet.CreateRow(lastRow + 1);
if (address == null)
{
sheet.GetRow(lastRow + 1).CreateCell(0).SetCellValue("N/A");
sheet.GetRow(lastRow + 1).CreateCell(1).SetCellValue("Pick up order");
sheet.GetRow(lastRow + 1).CreateCell(2).SetCellValue("N/A");
}
else
{
sheet.GetRow(lastRow + 1).CreateCell(0).SetCellValue(address.Number);
sheet.GetRow(lastRow + 1).CreateCell(1).SetCellValue(address.Road);
sheet.GetRow(lastRow + 1).CreateCell(2).SetCellValue(address.PhoneNumber);
}
sheet.GetRow(lastRow + 1).CreateCell(3).SetCellValue(DateTime.Now);
for (int i = 4; i < items.Count; i++)
{
IItem currentItem = items[i - 4];
sheet.GetRow(lastRow + 1).CreateCell(i).SetCellValue(currentItem.EnglishName + " " + currentItem.ChineseName);
}
ExcelReader_NPOI.WriteXLSX(hssfwb, "OrderLog.xlsx");
}
public static DataTable LoadOrderLog()
{
DataTable tempCustomers = new DataTable();
tempCustomers.Columns.Add("Number", typeof(string));
tempCustomers.Columns.Add("Address", typeof(Address));
tempCustomers.Columns.Add("PhoneNumber", typeof(int));
tempCustomers.Columns.Add("Date", typeof(string));
int[] columnsString = new int[] { 0, 1, 2 };
int[] columnNumber = new int[] { 3 };
string[,] output = ExcelReader_NPOI.ReadAllXLSX("OrderLog.xlsx", columnsString, columnNumber, false);
for (int i = 0; i < output.GetLength(0); i++)
{
if (output[i, 0] != null)
{
string houseNumber = output[i, 0].ToString();
int phoneNumber;
string phoneNumberString = output[i, 2];
bool phoneNumberWork = int.TryParse(phoneNumberString, out phoneNumber);
string date = output[i, 3].ToString();
Address address = null;
for (int j = 0; j < totalAddresses.Count; j++)
{
if (totalAddresses[j].Road == output[i, 1])
{
address = totalAddresses[j];
break;
}
}
tempCustomers.Rows.Add(houseNumber, address, phoneNumber, date);
}
}
/*DataView dv = tempCustomers.DefaultView;
* dv.Sort = "occr desc";
* tempCustomers = dv.ToTable();*/
return(tempCustomers);
}
