public static List <RequestItem> LoadMonthData(Worksheet sheet) { List <RequestItem> items = new List <RequestItem>(); Dictionary <string, int> branches = LoadBrancheFullNameIds(); int rowIndex = 1; while (sheet.Cells[rowIndex, 2].Value != null) { RequestItem item = new RequestItem(); item.FileName = sheet.Cells[rowIndex, 2].StringValue; item.SerialNum = branches[item.FileName].ToString(); item.Money = sheet.Cells[rowIndex, 4].DoubleValue; items.Add(item); rowIndex++; } return(items); }
public static List<RequestItem> LoadMonthData(Worksheet sheet) { List<RequestItem> items = new List<RequestItem>(); Dictionary<string, int> branches = LoadBrancheFullNameIds(); int rowIndex = 1; while (sheet.Cells[rowIndex, 2].Value != null) { RequestItem item = new RequestItem(); item.FileName = sheet.Cells[rowIndex, 2].StringValue; item.SerialNum = branches[item.FileName].ToString(); item.Money = sheet.Cells[rowIndex, 4].DoubleValue; items.Add(item); rowIndex++; } return items; }
public static List <RequestItem> LoadItems(int month, string folder) { List <RequestItem> items = new List <RequestItem>(); string namePrefix = "营业部名称(签章):"; DirectoryInfo dir = new DirectoryInfo(folder); Regex reg = new Regex("^(\\d+)"); var files = dir.GetFiles(); Dictionary <int, string> branches = LoadBrancheIdNames(); Dictionary <string, int> branches_1 = LoadBrancheNameIds(); foreach (var file in files) { if (file.Extension == ".xls" || file.Extension == ".xlsx") { RequestItem requestItem = new RequestItem(); requestItem.Month = month; string fileName = file.Name; requestItem.FileName = fileName; Match matchResult = reg.Match(fileName); if (matchResult.Success) { string id = matchResult.Groups[0].Value; if (branches.ContainsKey(Convert.ToInt32(id))) { requestItem.SerialNum = id; requestItem.FullName = branches[Convert.ToInt32(id)]; } else { requestItem.SerialNum = "-" + id; ErrorMessage.AppendLine("机构编码错误 :" + id + " -->" + fileName); Console.WriteLine("机构编码错误 :" + id + " -->" + fileName); } } Workbook currentWorkbook = new Workbook(); currentWorkbook.Open(file.FullName); Worksheet sheet = currentWorkbook.Worksheets[0]; //Set Name string rawName = sheet.Cells[1, 0].StringValue; if (rawName.StartsWith(namePrefix)) { requestItem.RawName = rawName.Substring(namePrefix.Length); } else { ErrorMessage.AppendLine("名称错误,没有以【 营业部名称(签章):】开头:" + fileName); Console.WriteLine("名称错误,没有以【 营业部名称(签章):】开头:" + fileName); } //Set Full Name if (string.IsNullOrEmpty(requestItem.SerialNum) || requestItem.SerialNum.StartsWith("-")) { double sim_FileName; double sim_RawName; string serial_FileName = GuessMostSimilarBranchId(requestItem.FileName, branches_1, out sim_FileName).ToString(); string serial_RawName = GuessMostSimilarBranchId(requestItem.RawName, branches_1, out sim_RawName).ToString(); if (sim_FileName > sim_RawName) { requestItem.SerialNum = serial_FileName; } else { requestItem.SerialNum = serial_RawName; } requestItem.FullName = branches[Convert.ToInt32(requestItem.SerialNum)]; } //Set Money double money = 0.0; int sumMoneyRowIndex = 0; while (sheet.Cells[sumMoneyRowIndex, 0].StringValue != "总计金额") { sumMoneyRowIndex++; if (sumMoneyRowIndex > 2000) { break; } } if (!IsValidMoneyValue(sheet.Cells[sumMoneyRowIndex, 2].StringValue, out money)) { if (!IsValidMoneyValue(sheet.Cells[37, 2].StringValue, out money)) { if (!IsValidMoneyValue(sheet.Cells[36, 12].StringValue, out money)) { if (!IsValidMoneyValue(sheet.Cells[36, 11].StringValue, out money)) { IsValidMoneyValue(sheet.Cells[36, 9].StringValue, out money); } } } } if (money == 0.0) { ErrorMessage.AppendLine("金额提取错误,或金额为0:" + fileName); Console.WriteLine("Money Convert Error:" + fileName); } requestItem.Money = money; items.Add(requestItem); } } return(items); }
public static List<RequestItem> LoadItems(int month, string folder) { List<RequestItem> items = new List<RequestItem>(); string namePrefix = "营业部名称(签章):"; DirectoryInfo dir = new DirectoryInfo(folder); Regex reg = new Regex("^(\\d+)"); var files = dir.GetFiles(); Dictionary<int, string> branches = LoadBrancheIdNames(); Dictionary<string, int> branches_1 = LoadBrancheNameIds(); foreach (var file in files) { if (file.Extension == ".xls" || file.Extension == ".xlsx") { RequestItem requestItem = new RequestItem(); requestItem.Month = month; string fileName = file.Name; requestItem.FileName = fileName; Match matchResult = reg.Match(fileName); if (matchResult.Success) { string id = matchResult.Groups[0].Value; if (branches.ContainsKey(Convert.ToInt32(id))) { requestItem.SerialNum = id; requestItem.FullName = branches[Convert.ToInt32(id)]; } else { requestItem.SerialNum = "-" + id; Console.WriteLine("Id of :" + id + " not valid --" + fileName); } } Workbook currentWorkbook = new Workbook(); currentWorkbook.Open(file.FullName); Worksheet sheet = currentWorkbook.Worksheets[0]; //Set Name string rawName = sheet.Cells[1, 0].StringValue; if (rawName.StartsWith(namePrefix)) { requestItem.RawName = rawName.Substring(namePrefix.Length); } else { Console.WriteLine("Name Error:" + fileName); } //Set Full Name if (string.IsNullOrEmpty(requestItem.SerialNum) || requestItem.SerialNum.StartsWith("-")) { double sim_FileName; double sim_RawName; string serial_FileName = GuessMostSimilarBranchId(requestItem.FileName, branches_1, out sim_FileName).ToString(); string serial_RawName = GuessMostSimilarBranchId(requestItem.RawName, branches_1, out sim_RawName).ToString(); if (sim_FileName > sim_RawName) { requestItem.SerialNum = serial_FileName; } else { requestItem.SerialNum = serial_RawName; } requestItem.FullName = branches[Convert.ToInt32(requestItem.SerialNum)]; } //Set Money double money = 0.0; if (!IsValidMoneyValue(sheet.Cells[37, 2].StringValue, out money)) { if (!IsValidMoneyValue(sheet.Cells[36, 12].StringValue, out money)) { if (!IsValidMoneyValue(sheet.Cells[36, 11].StringValue, out money)) { IsValidMoneyValue(sheet.Cells[36, 9].StringValue, out money); } } } if (money == 0.0) { Console.WriteLine("Money Convert Error:" + fileName); } requestItem.Money = money; items.Add(requestItem); } } return items; }