private bool GetQualityCotrols(FileInfo file, string attribute)
{
try
{
var excel = new LinqToExcel.ExcelQueryFactory(file.ToString());
var columnsNames = excel.GetColumnNames(excel.GetWorksheetNames().ToList().First()).ToList();
foreach (var item in excel.GetWorksheetNames().ToList())
{
foreach (LinqToExcel.Row r in excel.Worksheet(item).ToList())
{
var enumerator = r.GetEnumerator();
while (enumerator.MoveNext())
{
if (string.IsNullOrEmpty(enumerator.Current.Value.ToString()))
{
enumerator.MoveNext();
continue;
}
var coded = enumerator.Current.Value.ToString();
GetIdRemarkLabel remarkId = GetIdRemarkLabelBL.GetIdRemarkLabel(columnsNames[r.IndexOf(enumerator.Current)]);
enumerator.MoveNext();
SetClientsCatalogsNextelBL.SetClientsCatalogsNextel(attribute, remarkId.IDRemarkLabel, coded, enumerator.Current.Value.ToString());
}
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
var file = openFileDialog1.FileName;
lblFile.Text = file;
lblFile.Show();
var excel = new LinqToExcel.ExcelQueryFactory(openFileDialog1.FileName);
if (file.Substring(file.Length - 3) == "csv")
{
dataGridView1.DataSource = excel.Worksheet <measure>().ToList();
}
else
{
cmbSheets.DataSource = excel.GetWorksheetNames().ToList();
}
}
public ActionResult UploadData(int WeekId)
{
string fileName = string.Empty;
string destinationPath = string.Empty;
var uniqueGUID = Guid.NewGuid();
var file_Uploader = Request.Files["UploadedImage"];
if (file_Uploader != null)
{
fileName = Path.GetFileName(file_Uploader.FileName);
destinationPath = Path.Combine(Server.MapPath("~/Upload/"), uniqueGUID + "_" + fileName);
file_Uploader.SaveAs(destinationPath);
}
string userName = _cookieHelper.GetCookie("userid");
//string fileName = System.Web.HttpContext.Current.Server.MapPath("~/Upload/File.xlsx");
string sheetNameFirst = "Summary";
var bookFirst = new LinqToExcel.ExcelQueryFactory(destinationPath);
var dataFirstTab = from x in bookFirst.Worksheet(sheetNameFirst.TrimEnd())
select x;
var worksheetsList = bookFirst.GetWorksheetNames().ToList();
var dataSecondTab = from y in bookFirst.Worksheet(worksheetsList[0])
select y;
int error = this._projectSummaryService.SaveDataFromExcelSheet(worksheetsList, dataFirstTab, dataSecondTab, WeekId, userName, destinationPath);
if (error == -1)
{
return(Json(new { Message = "Failure,First Tab Excel Format Is Not Correct" }));
}
else if (error == -2)
{
return(Json(new { Message = "Failure" }));
}
else
{
return(Json(new { Message = "Success" }));
}
}
public IEnumerable <TModel> ParseExcelFile <TModel>(string fileName, ref string errorMsg)
{
var excelFile = new LinqToExcel.ExcelQueryFactory(fileName);
IEnumerable <string> workSheetNames = excelFile.GetWorksheetNames();
IEnumerable <TModel> sheetData = new List <TModel>();
foreach (string sheetName in workSheetNames)
{
List <string> excelHeaders = excelFile.GetColumnNames(sheetName).ToList();
List <string> props = typeof(TModel).GetProperties().Select(x => x.Name).ToList();
bool isSubset = !excelHeaders.Except(props).Any();
if (isSubset)
{
errorMsg = "";
var data = from row in excelFile.Worksheet <TModel>(sheetName)
select row;
sheetData = data.AsEnumerable();
}
}
return(sheetData);
}
private static void UpdateBasedOnExcelFile(List <IssueItem> issuesInRedmineProject,
List <StatItem> statItems,
bool allWithinDirectory)
{
//********************************************************************************************************/
//read data from Excel
List <string> filesToProcess = null;
if (allWithinDirectory)
{
filesToProcess = Directory.EnumerateFiles(MOM_FILES_DIR, "*.xlsx").ToList();
}
else
{
filesToProcess = new List <string>();
filesToProcess.Add(MOM_FILE_PATH);
}
foreach (string singleXSLXfile in filesToProcess)
{
var xlsx = new LinqToExcel.ExcelQueryFactory(singleXSLXfile);
output.WriteLine("File: {0}", singleXSLXfile);
foreach (string tabName in xlsx.GetWorksheetNames())
{
output.WriteLine("--------------------------------------------");
output.WriteLine("Processing of {0}...", tabName);
output.WriteLine("--------------------------------------------");
StatItem statItem = new StatItem(tabName);
statItem.Env = tabName;
var query =
from row in xlsx.Worksheet(tabName)
let item = new
{
ProblemID = row["Problem ID"].Cast <string>(),
ProblemCode = row["Problem Code"].Cast <string>(),
MessageId = row["Message ID"].Cast <string>(),
EventCode = row["Event Code"].Cast <string>(),
Details = row["Details"].Cast <string>(),
SenderCode = row["Sender Code"].Cast <string>(),
}
select item;
IdentifiableName p = IdentifiableName.Create <Project>(Consts.PROJECT_NAMES.MOM.PROBLEMS);
foreach (var itemFromExcel in query)
{
//look for the item in RM
var redmineIssue = issuesInRedmineProject.Where(issueFromRedmine => issueFromRedmine.Env == tabName && issueFromRedmine.ProblemId == itemFromExcel.ProblemID).FirstOrDefault();
if (redmineIssue != null && string.IsNullOrEmpty(redmineIssue.SenderCode))
{
if (!string.IsNullOrEmpty(itemFromExcel.SenderCode))
{
var issue = RMManegerService.RMManager.GetObject <Issue>(redmineIssue.Id.ToString(), null);
issue.Subject = issue.Subject + string.Format(" - {0}", itemFromExcel.SenderCode);
RMManegerService.RMManager.UpdateObject(redmineIssue.Id.ToString(), issue);
redmineIssue.SenderCode = itemFromExcel.SenderCode;
statItem.Updated++;
// string subject = redmineIssue.sub
//string subject = string.Format("{0} - {1} - {2} - {3} - {4}", tabName, itemFromExcel.ProblemID, itemFromExcel.EventCode, itemFromExcel.ProblemCode, itemFromExcel.SenderCode);
}
else
{
statItem.NotUpdated++;
}
}
else
{
statItem.NotUpdated++;
}
}
statItems.Add(statItem);
}
}
}
private static void ProcessExcelFile(List <IssueItem> issuesInRedmineProject, List <StatItem> statItems, List <string> envsNotExistingInConfigs)
{
//********************************************************************************************************/
//read data from Excel
var xlsx = new LinqToExcel.ExcelQueryFactory(MOM_FILE_PATH);
foreach (string tabName in xlsx.GetWorksheetNames())
{
output.WriteLine("--------------------------------------------");
output.WriteLine("Processing of {0}...", tabName);
output.WriteLine("--------------------------------------------");
MOMEnvSettings momEnvSettings = null;
if (!MOM_ENV_SETTINGS.TryGetValue(tabName, out momEnvSettings))
{
output.WriteLine("No MOMEnvSettings for {0}", tabName);
envsNotExistingInConfigs.Add(tabName);
//output.ReadKey();
}
else
{
output.WriteLine("Start processing: {0}", tabName);
StatItem statItem = new StatItem();
statItem.Env = tabName;
var query =
from row in xlsx.Worksheet(tabName)
let item = new
{
ProblemID = row["Problem ID"].Cast <string>(),
ProblemCode = row["Problem Code"].Cast <string>(),
MessageId = row["Message ID"].Cast <string>(),
EventCode = row["Event Code"].Cast <string>(),
Details = row["Details"].Cast <string>(),
SenderCode = row["Sender Code"].Cast <string>(),
}
select item;
IdentifiableName p = IdentifiableName.Create <Project>(Consts.PROJECT_NAMES.MOM.PROBLEMS);
foreach (var itemFromExcel in query)
{
string subject = string.Format("{0} - {1} - {2} - {3} - {4}", tabName, itemFromExcel.ProblemID, itemFromExcel.EventCode, itemFromExcel.ProblemCode, itemFromExcel.SenderCode);
//check if such the item exists in the Redmine project
var redmineIssue = issuesInRedmineProject.Where(issueFromRedmine => issueFromRedmine.Env == tabName && issueFromRedmine.ProblemId == itemFromExcel.ProblemID);
if (redmineIssue.Count() == 0)
{
string details = string.Format("{0}\r\nMessage link: {1}\r\nProblem link: {2}", itemFromExcel.Details, momEnvSettings.GetMessageLink(itemFromExcel.MessageId), momEnvSettings.GetProblemLink(itemFromExcel.MessageId));
var newIssue = new Issue {
Subject = subject, Project = p, Description = details
};
RMManegerService.RMManager.CreateObject(newIssue);
//add a new item to local cached items from redmine
IssueItem item = new IssueItem();
item.Env = tabName;
item.ProblemId = itemFromExcel.ProblemID;
issuesInRedmineProject.Add(item);
statItem.Added++;
}
else
{
output.WriteLine("Issue exists! {0}", subject);
statItem.AlreadyExisted++;
}
}
statItems.Add(statItem);
}
}
}
public Batch Parse()
{
var fileInfo = new FileInfo(FilePath);
if (!fileInfo.Exists)
{
throw new FileNotFoundException(string.Format("The file '{0}' does not exist", FilePath));
}
// The file represents the batch. The name of the batch is the name of the file.
var batch = new Batch();
batch.Name = DetermineFileName(fileInfo);
batch.SubBatches = new List<SubBatch>();
try
{
using (var factory = new LinqToExcel.ExcelQueryFactory(FilePath))
{
// The work sheets are the sub batches.
var subBatchNames = factory.GetWorksheetNames();
foreach (var subBatchName in subBatchNames)
{
// Loop the sub batches.
var subBatch = new SubBatch
{
Code = subBatchName
};
var subBatchRows = factory.Worksheet(subBatchName);
// Read the rows. The rows are matched using the row number.
subBatch.SimCards = subBatchRows.Select(row => new SIMCard
{
TrackingCode = row[0].Value.ToString(),
SubBatch = subBatch,
DateCaptured = DateTime.Parse(row[3].Value.ToString()),
CapturedBy = row[4].Value.ToString(),
Network = new Network
{
Name = row[5].Value.ToString()
},
Package = new Package
{
Name = row[6].Value.ToString()
},
ICCID = row[7].Value.ToString(),
IMSI = row[8].Value.ToString(),
MSISDN = row[9].Value.ToString(),
Group = new Group
{
Name = row[10].Value.ToString()
}
}).ToList();
batch.SubBatches.Add(subBatch);
}
}
}
catch (Exception)
{
// perhaps some cleaning up?
throw;
}
return batch;
}