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); }