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