/// <summary>
/// 处理分析
/// </summary>
private void process_analyze() {
FileStream stream = File.Open(_strFilePath, FileMode.Open, FileAccess.Read);
var reader = ExcelReaderFactory.CreateReader(stream);
//结构
//row1 中文注释
//row2 开始常规数据
int row = 0;
do {
while (reader.Read()) {
row++;
ExcelObj excelObj = new ExcelObj();
for (int i = 0; i < reader.FieldCount; i++) {
string content = reader.GetValue(i).ToString();
if (row == 1) {
firstRow.Add(content.Trim());
continue;
}
if (i == firstRow.IndexOf("工单号")) { // 工单号
excelObj.id = Convert.ToInt32(content);
} else if (i == firstRow.IndexOf("优先级")) { // 优先级
excelObj.order = content;
} else if (i == firstRow.IndexOf("标题")) { // 标题
excelObj.title = content;
} else if (i == firstRow.IndexOf("状态")) { // 状态
excelObj.state = content;
} else if (i == firstRow.IndexOf("模板类型")) { // 模板类型
excelObj.mod = content;
} else if (i == firstRow.IndexOf("发起人")) { // 发起人
excelObj.senderName = content;
} else if (i == firstRow.IndexOf("受理人 ")) { // 受理人
excelObj.acceptName = content;
} else if (i == firstRow.IndexOf("漏单类型")) { // 漏单类型
excelObj.missType = content;
} else if (i == firstRow.IndexOf("平台")) { // 平台
excelObj.platform = content;
} else if (i == firstRow.IndexOf("渠道号")) { // 渠道号
excelObj.channel = content;
} else if (i == firstRow.IndexOf("漏单渠道")) { // 漏单渠道
excelObj.missChannel = content;
}
}
if (row != 1) {
// 归类内容
classifyExcelObj(excelObj);
}
}
} while (reader.NextResult());
stream.Close();
}
/// <summary>
/// 归类内容
/// </summary>
/// <param name="obj"></param>
private void classifyExcelObj(ExcelObj obj) {
if (this.checkBox1.Checked &&
obj.title.Contains("重复提交") || obj.title.Contains("重複提交")) {
return;
}
if (this.checkBox2.Checked &&
obj.title.Contains("重复记录") || obj.title.Contains("重複記錄")) {
return;
}
if (this.checkBox3.Checked &&
obj.title.Contains("模板错误") || obj.title.Contains("模板錯誤")) {
return;
}
if (this.checkBox4.Checked &&
obj.title.Contains("无需处理") || obj.title.Contains("無需處理")) {
return;
}
if (this.checkBox5.Checked &&
obj.title.Contains("信息不全") || obj.title.Contains("信息不全")) {
return;
}
if (this.checkBox6.Checked &&
obj.title.Contains("待跟进") || obj.title.Contains("待跟進")) {
return;
}
switch (obj.mod) {
case "绑定实名认证":
list1.Add(obj);
break;
case "绑定账号":
list2.Add(obj);
break;
case "充值未到账":
if (obj.missType.Contains("异常") || obj.missType.Contains("異常")) {
listOrderExcp.Add(obj);
} else {
listOrderMiss.Add(obj);
}
break;
case "道具补发":
list3.Add(obj);
break;
case "后台功能需求":
list4.Add(obj);
break;
case "默认类型":
list5.Add(obj);
break;
case "其他问题":
list6.Add(obj);
break;
case "实名查询账号":
list7.Add(obj);
break;
case "数据关联":
list8.Add(obj);
break;
case "系统bug反馈":
list9.Add(obj);
break;
case "修改密码":
list10.Add(obj);
break;
case "已禁平台添加设备号":
list11.Add(obj);
break;
case "账号绑定信息修改":
list12.Add(obj);
break;
}
}
//fi: fileinfo of a single file (path, and so on..)
public static bool readExcelFile(FileInfo fi, string dstpath, ExcelObj dst, int count)
{
string filePath = "not initialized";
int internalCount;
Dictionary <string, string> sysInfo;
ExcelObj src = null;
try
{
//full path of a xlsx file
filePath = fi.FullName;
internalCount = 1;
Console.WriteLine(filePath);
sysInfo = new Dictionary <string, string>();
src = new ExcelObj(filePath, false);
Console.WriteLine("Rows: " + src.row + ", Column: " + src.column);
#region readingSysInfo
//reading everything that is on these fixed cells (most of the time)
sysInfo.Add("IP-Name", src.getValue(1, 1));
sysInfo.Add("Benutzer", src.getValue(8, 1));
sysInfo.Add("CPU", src.getValue(3, 1));
sysInfo.Add("Typenbezeichner", src.getValue(5, 1));
sysInfo.Add("Seriennummer", src.getValue(10, 1));
sysInfo.Add("Massenspeicher", src.getValue(12, 1) + " " + src.getValue(14, 1));
/*
* sysInfo.Add("IP-Adresse", src.getValue(28, 1));
*/
//filtering ip adress -> not in the same cell every time
string value = "empty";
for (int i = 20; i <= 40; i++)
{
value = src.getValue(i, 1);
if (value != null)
{
//looking for possible ip adresses
if (value.Contains("IPv4-Adresse") && (value.Contains("192.") || value.Contains("169.") || value.Contains("10.")))
{
//trimming ip adress, neccessary because a lot of useless information is attached here
value = src.getValue(i, 1);
string[] seperator = new string[1];
seperator[0] = ":";
string[] trimmed = value.Split(seperator, StringSplitOptions.RemoveEmptyEntries);
value = trimmed[1];
break;
}
}
else
{
value = null;
}
}
sysInfo.Add("IP-Adresse", value);
#endregion
//writing to the console every read info, debug purposes
foreach (KeyValuePair <string, string> kvp in sysInfo)
{
if (kvp.Key != null && kvp.Value != null)
{
Console.WriteLine(kvp.Key + ", " + kvp.Value);
}
}
//free excel and free resources
//src.Free(true);
#region saving in Summary
//save to big summary
//IP-Name
//validating everything and writing it into the summary excel
Console.WriteLine(dst.row + 1);
if (sysInfo["IP-Name"] != null)
{
//writing to excel
Console.WriteLine("Saved IP-name");
dst.saveValue(dst.row + 1, 9, sysInfo["IP-Name"]);
}
else
{
//writing none to excel
dst.saveValue(dst.row + 1, 9, "none");
}
//CPU
if (sysInfo["CPU"] != null)
{
Console.WriteLine("Saved CPU");
dst.saveValue(dst.row + 1, 10, sysInfo["CPU"]);
}
else
{
dst.saveValue(dst.row + 1, 10, "none");
}
//Typenbezeichner
if (sysInfo["Typenbezeichner"] != null)
{
Console.WriteLine("Saved typenbezeichner");
dst.saveValue(dst.row + 1, 7, sysInfo["Typenbezeichner"]);
}
else
{
dst.saveValue(dst.row + 1, 7, "none");
}
//Benutzer
if (sysInfo["Benutzer"] != null)
{
Console.WriteLine("Saved Benutzer");
dst.saveValue(dst.row + 1, 4, sysInfo["Benutzer"]);
}
else
{
dst.saveValue(dst.row + 1, 4, "none");
}
//Seriennummer
if (sysInfo["Seriennummer"] != null)
{
Console.WriteLine("Saved Seriennummer");
dst.saveValue(dst.row + 1, 8, sysInfo["Seriennummer"]);
}
else
{
dst.saveValue(dst.row + 1, 8, "none");
}
//Massenspeicher
if (sysInfo["Massenspeicher"] != null)
{
Console.WriteLine("Saved massenspeicher");
dst.saveValue(dst.row + 1, 12, sysInfo["Massenspeicher"]);
}
else
{
Console.WriteLine("no mass storage");
dst.saveValue(dst.row + 1, 12, "none");
}
//IP-Adresse
if (sysInfo["IP-Adresse"] != null)
{
dst.saveValue(dst.row + 1, 13, sysInfo["IP-Adresse"]);
}
else
{
Console.WriteLine("no ip adress");
dst.saveValue(dst.row + 1, 13, "none");
}
dst.IncrementRow();
#endregion
internalCount++;
return(true);
}
catch (Exception ex)
{
errorCount++;
errorFiles[errorCount] = filePath;
/*
* if(src != null)
* {
* Console.WriteLine("tried to error free");
* src.Free(true);
* }
*/
Console.WriteLine("Error Single Read and Save, " + ex.Message);
Console.WriteLine("error file: " + filePath);
}
finally
{
if (src != null)
{
Console.WriteLine("trying to free single read");
src.Free(true);
}
}
return(false);
}
//src: file path where the path for the source excel files is saved
//dst: file path where the path for the destination excel file is saved
public static bool readAndWriteExcelFiles(string src, string dst)
{
successful = 0;
count = 0;
ExcelObj dstExcel = null;
errorFiles = new string[10];
try
{
//checking if both paths were entered
if (src == null || dst == null)
{
return(false);
}
//get all the .xlsx files in the folder
DirectoryInfo d;
FileInfo[] files;
try
{
//getting every excel file needed
d = new DirectoryInfo(src);
files = d.GetFiles("*.csv");
errorFiles = new string[files.Count <FileInfo>()];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("error while scanning direcotry");
return(false);
}
count = files.Count <FileInfo>();
//open Destination excel file
dstExcel = new ExcelObj(getDstPath(), true);
int oldRow = dstExcel.row;
foreach (FileInfo file in files)
{
//send each file to reader function
if (readExcelFile(file, dst, dstExcel, count))
{
successful++;
}
}
//release every process needed for the destination excel
dstExcel.deleteCells(oldRow);
Console.WriteLine();
Console.WriteLine("Error trying to read these Files: ");
foreach (string errorfile in errorFiles)
{
//writing every failed excel file to the console
if (errorfile != null)
{
Console.WriteLine(errorfile);
}
}
Console.WriteLine();
Console.WriteLine(successful + " of " + count + " have been read and re-saved");
Console.WriteLine("Hit save on the Windows Save Prompt to continue. (if you dont see it use alt+tab to look for it)");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(errorFiles.Count <string>());
Console.WriteLine("Big error");
}
finally
{
if (dstExcel != null)
{
Console.WriteLine("trying to free data collection excel");
dstExcel.Free(true);
}
}
return(true);
}
