public static List <SupplierAuthorizationDestory> ParseXLSX(string filePath) { List <SupplierAuthorizationDestory> supplierAuthorizationDestories = new List <SupplierAuthorizationDestory>(); if (!PreCheck(filePath)) { return(supplierAuthorizationDestories); } Worksheet worksheet = Workbook.Load(filePath).Worksheets[0]; WorkSheetReader reader = new WorkSheetReader(worksheet); string columnName = string.Empty; int rowNo = 2; foreach (IDictionary <string, object> aRow in reader) { SupplierAuthorizationDestory supplierAuthorizationDestory = new SupplierAuthorizationDestory(); try { object value; columnName = "索赔号"; if (null != (value = aRow[columnName])) { supplierAuthorizationDestory.claimNo = value.ToString(); } columnName = "供应商号"; if (null != (value = aRow[columnName])) { supplierAuthorizationDestory.supplierNo = value.ToString(); } // if (null != (value = aRow["通知时间"])) // { // supplierAuthorizationDestory.destoryInformDate = DateTime.FromOADate((double)value); // } columnName = "定案日期"; if (null != (value = aRow[columnName])) { supplierAuthorizationDestory.decidedDate = DateTime.FromOADate((double)value); } columnName = "金额"; if (null != (value = aRow[columnName])) { Double.TryParse(value.ToString(), out supplierAuthorizationDestory.claimAmount); } rowNo++; supplierAuthorizationDestories.Add(supplierAuthorizationDestory); } catch (InvalidCastException e) { MessageBox.Show(String.Format("第{0}行,列[{1}]的格式错误!", rowNo, columnName)); return(new List <SupplierAuthorizationDestory>()); } catch (Exception e) { MessageBox.Show(e.Message + "\n需要:\n'索赔号', '供应商号', '通知时间', '定案日期', '金额'。 " + "\n\n\n实际为:\n" + StringUtils.Join(", ", aRow.Keys), "文件格式错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return(new List <SupplierAuthorizationDestory>()); } } return(supplierAuthorizationDestories); }
public static List <ClaimOrder> ParseXLSX(string filePath) { List <ClaimOrder> claimOrders = new List <ClaimOrder>(); if (!PreCheck(filePath)) { return(claimOrders); } Worksheet worksheet = Workbook.Load(filePath).Worksheets[0]; WorkSheetReader reader = new WorkSheetReader(worksheet); int rowNo = 2; string columnName = string.Empty; foreach (IDictionary <string, object> aRow in reader) { try { ClaimOrder claimOrder = new ClaimOrder(); object value; columnName = ClaimOrderTitleConstants.RTV; if (null != (value = aRow[columnName])) { claimOrder.rtv = value.ToString(); } columnName = ClaimOrderTitleConstants.WMT_CLAIM_NO; if (null != (value = aRow[columnName])) { claimOrder.claimNo = value.ToString(); } columnName = ClaimOrderTitleConstants.STORE_NO; if (null != (value = aRow[columnName])) { claimOrder.storeNo = value.ToString(); } columnName = ClaimOrderTitleConstants.LOT_NO; if (null != (value = aRow[columnName])) { claimOrder.lotNo = value.ToString(); } columnName = ClaimOrderTitleConstants.SUPPLIER_NO; if (null != (value = aRow[columnName])) { claimOrder.supplierNo = value.ToString(); } columnName = ClaimOrderTitleConstants.SUPPLIER_NAME; if (null != (value = aRow[columnName])) { claimOrder.supplierName = value.ToString(); } columnName = ClaimOrderTitleConstants.DEPT; if (null != (value = aRow[columnName])) { claimOrder.dept = value.ToString(); } columnName = ClaimOrderTitleConstants.QTY; if (null != (value = aRow[columnName])) { Double.TryParse(value.ToString(), out claimOrder.qty); } columnName = ClaimOrderTitleConstants.PCS; if (null != (value = aRow[columnName])) { Double.TryParse(value.ToString(), out claimOrder.pcs); } columnName = ClaimOrderTitleConstants.CLAIM_AMOUNT; if (null != (value = aRow[columnName])) { Double.TryParse(value.ToString(), out claimOrder.claimAmount); } columnName = ClaimOrderTitleConstants.CLAIM_REASON; if (null != (value = aRow[columnName])) { claimOrder.claimReason = value.ToString(); } columnName = ClaimOrderTitleConstants.DECIDED_DATE; if (null != (value = aRow[columnName])) { claimOrder.decidedDate = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.ARRIVE_RTV_DATE; if (null != (value = aRow[columnName])) { claimOrder.arriveRTVDate = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.INFORM_DATE; if (null != (value = aRow[columnName])) { claimOrder.informDate = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.WITHDRAW_DATE; if (null != (value = aRow[columnName])) { claimOrder.withdrawDate = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.GATEOUT_DATE; if (null != (value = aRow[columnName])) { claimOrder.gateOutDate = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.GATEOUT_TYPE; if (null != (value = aRow[columnName])) { claimOrder.gateOutType = value.ToString(); } columnName = ClaimOrderTitleConstants.DESTORY_INFORM_DATE; if (null != (value = aRow[columnName])) { claimOrder.destoryInformDate = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.DESTORY_TYPE; if (null != (value = aRow[columnName])) { claimOrder.destoryType = value.ToString(); } columnName = ClaimOrderTitleConstants.INFORM_DATE_IF_OVER_50_DAYS; if (null != (value = aRow[columnName])) { claimOrder.informDateIfOver50Days = DateTime.FromOADate((double)value); } columnName = ClaimOrderTitleConstants.INFORM_DAYS; if (null != (value = aRow[columnName])) { claimOrder.informDays = (double)value; } // //cause there is bug of fragistics to calculate the value of this cell having a formula // if (DateTimeUtils.IsEmpty(claimOrder.informDate)) // { // claimOrder.informDays = 0; // } // else // { // if (DateTimeUtils.IsEmpty(claimOrder.gateOutDate) && // !DateTimeUtils.IsEmpty(claimOrder.destoryInformDate)) // { // claimOrder.informDays = claimOrder.destoryInformDate.Subtract(claimOrder.informDate).Days; // } // else if (!DateTimeUtils.IsEmpty(claimOrder.gateOutDate) && // DateTimeUtils.IsEmpty(claimOrder.destoryInformDate)) // { // claimOrder.informDays = claimOrder.gateOutDate.Subtract(claimOrder.informDate).Days; // } // else if (!DateTimeUtils.IsEmpty(claimOrder.gateOutDate) && // !DateTimeUtils.IsEmpty(claimOrder.destoryInformDate)) // { // DateTime min = claimOrder.gateOutDate.CompareTo(claimOrder.destoryInformDate) < 0 // ? claimOrder.gateOutDate // : claimOrder.destoryInformDate; // claimOrder.informDays = min.Subtract(claimOrder.informDate).Days; // } // else // { // claimOrder.informDays = DateTime.Today.Subtract(claimOrder.informDate).Days; // } // } rowNo++; claimOrders.Add(claimOrder); } catch (InvalidCastException) { MessageBox.Show(String.Format("第{0}行,列[{1}]的格式错误!", rowNo, columnName)); return(new List <ClaimOrder>()); } catch (Exception e) { string[] required = new string[] { ClaimOrderTitleConstants.RTV, ClaimOrderTitleConstants.WMT_CLAIM_NO, ClaimOrderTitleConstants.STORE_NO, ClaimOrderTitleConstants.LOT_NO, ClaimOrderTitleConstants.SUPPLIER_NO, ClaimOrderTitleConstants.SUPPLIER_NAME, ClaimOrderTitleConstants.QTY, ClaimOrderTitleConstants.PCS, ClaimOrderTitleConstants.CLAIM_AMOUNT, ClaimOrderTitleConstants.CLAIM_REASON, ClaimOrderTitleConstants.DECIDED_DATE, ClaimOrderTitleConstants.ARRIVE_RTV_DATE, ClaimOrderTitleConstants.INFORM_DATE, ClaimOrderTitleConstants.WITHDRAW_DATE, ClaimOrderTitleConstants.GATEOUT_DATE, ClaimOrderTitleConstants.GATEOUT_TYPE, ClaimOrderTitleConstants.DESTORY_INFORM_DATE, ClaimOrderTitleConstants.DESTORY_TYPE, ClaimOrderTitleConstants.INFORM_DATE_IF_OVER_50_DAYS, ClaimOrderTitleConstants.INFORM_DAYS }; MessageBox.Show(e.Message + "\n需要:\n" + StringUtils.Join(", ", required) + "\n\n\n实际:\n" + StringUtils.Join(", ", aRow.Keys), "文件格式错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return(new List <ClaimOrder>()); } } return(claimOrders); }