public static void AddOrUpdateStation(csvData data, int idx, GameData outputData) { int foundSystem = -1, foundStation = -1; // See if we already have the StarSystem foundSystem = Routines.IndexOfSystem(outputData, data.stations[idx].SystemName); if (foundSystem == -1) { // Not found, add StarSystem. outputData.StarSystems.Add(new StarSystem()); foundSystem = outputData.StarSystems.Count - 1; outputData.StarSystems[foundSystem].Name = data.stations[idx].SystemName; outputData.StarSystems[foundSystem].Stations.Add(new Station(data.stations[idx])); } else { // Found StarSystem, check if we already have the Station foundStation = Routines.IndexOfStation(outputData.StarSystems[foundSystem], data.stations[idx].Name); if (foundStation == -1) { // Not found, add Station outputData.StarSystems[foundSystem].Stations.Add(new Station(data.stations[idx])); foundStation = outputData.StarSystems[foundSystem].Stations.Count - 1; } else { // Found, update Commodities Routines.UpdateCommodities(outputData.StarSystems[foundSystem].Stations[foundStation], data.stations[idx].Commodities); } } }
private void browseCSV_Click(object sender, EventArgs e) { OpenFileDialog fdlg = new OpenFileDialog(); fdlg.Title = "CSV File"; //fdlg.InitialDirectory = @"c:\"; fdlg.Filter = "Comma Seperated Variable (*.csv)|*.csv"; fdlg.FilterIndex = 2; fdlg.RestoreDirectory = true; if (fdlg.ShowDialog() == DialogResult.OK) { csvFilename.Text = fdlg.FileName; data = new csvData(csvFilename.Text); } }
public List <csvData> GetCSVAsync(string value) { #region variables List <csvData> res = new List <csvData>(); #endregion #region logic using (StreamReader reader = new StreamReader(fileLocation)) { while (true) { string line = reader.ReadLine(); if (line == null) { //if the next line is empty, then time to break it; break; } var arrContent = line.Split(','); int matchRes = match(arrContent[1], value); if (matchRes > 0) { csvData tempCsvData = new csvData(); tempCsvData.id = arrContent[0]; tempCsvData.content = arrContent[1]; tempCsvData.matchTimes = matchRes; res.Add(tempCsvData); } //everytime the looping done, dispose the resources. or else, it will be pilled to 4GB then boom! crash. Dispose(); } Dispose(); } #endregion return(res); }
private void csvFilename_TextChanged(object sender, EventArgs e) { data = new csvData(csvFilename.Text); }
public MemoryStream MergeExcel(MemoryStream csvStream, MemoryStream xlsStream) { //Create an instance of ExcelEngine using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Load xls to file stream // DateTime now = DateTime.Now; // string csvFileName = now.ToString() + ".csv"; // string xlsFileName = now.ToString() + ".xlsx"; FileStream csvInputStream = new FileStream("test.csv", FileMode.Create); //mStream.Position = 0; csvStream.WriteTo(csvInputStream); csvStream.Close(); //reset write position from tail to head csvInputStream.Position = 0; IWorkbook csvworkbook = application.Workbooks.Open(csvInputStream); IWorksheet csvworksheet = csvworkbook.Worksheets[0]; int csvLastRow = csvworksheet.UsedRange.LastRow; // int csvLastCol = csvworksheet.UsedRange.LastColumn; Dictionary <string, List <csvData> > csvPairs = new Dictionary <string, List <csvData> >(); // csvData[] csvDatas = new csvData[csvLastRow - 1]; for (int i = 2; i <= csvLastRow; i++) { csvData newCsvData = new csvData(); newCsvData.Name = csvworksheet.Range["A" + i.ToString()].Text; newCsvData.Case_Status__c = csvworksheet.Range[("B" + i.ToString())].Text; newCsvData.Case_Sub_Status__c = csvworksheet.Range[("C" + i.ToString())].Text; newCsvData.Confirmed_Hearing_Date__c = csvworksheet.Range[("D" + i.ToString())].Text; newCsvData.Lead_Advocate1__r_Name = csvworksheet.Range[("E" + i.ToString())].Text; newCsvData.Advocate_Status__c = csvworksheet.Range[("F" + i.ToString())].Text; //csvPairs.Add(newCsvData.Name + newCsvData.Confirmed_Hearing_Date__c, newCsvData); if (!csvPairs.ContainsKey(newCsvData.Name)) { csvPairs.Add(newCsvData.Name, new List <csvData> { newCsvData }); } else { csvPairs[newCsvData.Name].Add(newCsvData); } } csvInputStream.Close(); FileStream xlsInputStream = new FileStream("test.xlsx", FileMode.Create); xlsStream.WriteTo(xlsInputStream); xlsStream.Close(); xlsInputStream.Position = 0; IWorkbook xlsworkbook = application.Workbooks.Open(xlsInputStream); IWorksheet xlsworksheet = xlsworkbook.Worksheets[0]; List <xlsData> xlsList = new List <xlsData>(); int xlsLastRow = xlsworksheet.UsedRange.LastRow; //xlsData[] xlsDatas = new xlsData[xlsLastRow - 1]; for (int i = 2; i <= xlsLastRow; i++) { //string joinKey = xlsworksheet.Range[("D" + i.ToString())].Text + xlsworksheet.Range[("G" + i.ToString())].Text; // string joinKey = xlsworksheet.Range[("D" + i.ToString())].Text; //if (csvPairs.ContainsKey(joinKey)) //{ // csvData match = csvPairs[joinKey]; // if (null != match) // { // xlsworksheet.Range["L" + i.ToString()].Text = match.Case_Status__c; // xlsworksheet.Range["M" + i.ToString()].Text = match.Case_Sub_Status__c; // xlsworksheet.Range["N" + i.ToString()].Text = match.Lead_Advocate1__r_Name; // /* // xlsworksheet.Range["L" + i.ToString()].Text = "1"; // xlsworksheet.Range["M" + i.ToString()].Text = "2"; // xlsworksheet.Range["N" + i.ToString()].Text = "3"; // */ // } //} xlsData newXlsData = new xlsData(); //newXlsData.Id = (i - 1).ToString(); //newXlsData.LastName = xlsworksheet.Range["A" + i.ToString()].Text; //newXlsData.FirstName = xlsworksheet.Range["B" + i.ToString()].Text; //newXlsData.MiddleName = xlsworksheet.Range[("C" + i.ToString())].Text; ////newXlsData.Account = xlsworksheet.Range[("D" + i.ToString())].Text; //newXlsData.Account = xlsworksheet.Range["B" + i.ToString()].Text + " " + xlsworksheet.Range["A" + i.ToString()].Text; //newXlsData.Last4SSN = xlsworksheet.Range[("E" + i.ToString())].Text; //newXlsData.HearingOfficeWithJurisdiction = xlsworksheet.Range[("F" + i.ToString())].Text; //newXlsData.HearingScheduledDate = xlsworksheet.Range[("G" + i.ToString())].Text; //newXlsData.HearingTime = xlsworksheet.Range[("H" + i.ToString())].Text; //newXlsData.ALJLastName = xlsworksheet.Range[("I" + i.ToString())].Text; //newXlsData.MedicalExpert = xlsworksheet.Range[("J" + i.ToString())].Text; //newXlsData.VocationalExpert = xlsworksheet.Range[("K" + i.ToString())].Text; newXlsData.Id = (i - 1).ToString(); newXlsData.LastName = xlsworksheet.Range["A" + i.ToString()].Text; newXlsData.FirstName = xlsworksheet.Range["B" + i.ToString()].Text; // newXlsData.MiddleName = xlsworksheet.Range[("C" + i.ToString())].Text; //newXlsData.Account = xlsworksheet.Range[("D" + i.ToString())].Text; newXlsData.Account = xlsworksheet.Range["B" + i.ToString()].Text + " " + xlsworksheet.Range["A" + i.ToString()].Text; newXlsData.Last4SSN = ""; newXlsData.MiddleName = ""; // newXlsData.Last4SSN = xlsworksheet.Range[("E" + i.ToString())].Text; newXlsData.HearingOfficeWithJurisdiction = xlsworksheet.Range[("C" + i.ToString())].Text; //newXlsData.HearingScheduledDate = xlsworksheet.Range[("F" + i.ToString())].Text; newXlsData.HearingScheduledDate = xlsworksheet.Range[("D" + i.ToString())].DateTime; newXlsData.HearingTime = xlsworksheet.Range[("E" + i.ToString())].Text; newXlsData.ALJLastName = xlsworksheet.Range[("F" + i.ToString())].Text; newXlsData.MedicalExpert = xlsworksheet.Range[("G" + i.ToString())].Text; newXlsData.VocationalExpert = xlsworksheet.Range[("H" + i.ToString())].Text; xlsList.Add(newXlsData); } //xlsworksheet.Range["A1"].Text = "ID"; //xlsworksheet.Range["B1"].Text = "Last Name"; //xlsworksheet.Range["C1"].Text = "First Name"; //xlsworksheet.Range["D1"].Text = "Middle Name"; //xlsworksheet.Range["E1"].Text = "Account"; //xlsworksheet.Range["F1"].Text = "Last 4 SSN"; //xlsworksheet.Range["G1"].Text = "Hearing Office with Jurisdiction"; //xlsworksheet.Range["H1"].Text = "Hearing Scheduled Date"; //xlsworksheet.Range["I1"].Text = "Hearing Time"; //xlsworksheet.Range["J1"].Text = "ALJ Last Name"; //xlsworksheet.Range["K1"].Text = "Medical Expert"; //xlsworksheet.Range["L1"].Text = "Vocational Expert"; //xlsworksheet.Range["M1"].Text = "Case_Status__c"; //xlsworksheet.Range["N1"].Text = "Case_Sub_Status__c"; //xlsworksheet.Range["O1"].Text = "Confirmed_Hearing_Date__c"; //xlsworksheet.Range["P1"].Text = "Lead_Advocate1__r.Name"; //xlsworksheet.Range["A1"].Text = "ID"; xlsworksheet.Range["A1"].Text = "Last Name"; xlsworksheet.Range["B1"].Text = "First Name"; //xlsworksheet.Range["D1"].Text = "Middle Name"; xlsworksheet.Range["C1"].Text = "Account"; // xlsworksheet.Range["F1"].Text = "Last 4 SSN"; xlsworksheet.Range["D1"].Text = "Hearing Office with Jurisdiction"; xlsworksheet.Range["E1"].Text = "Hearing Scheduled Date"; xlsworksheet.Range["F1"].Text = "Hearing Time"; xlsworksheet.Range["G1"].Text = "ALJ Last Name"; xlsworksheet.Range["H1"].Text = "Medical Expert"; xlsworksheet.Range["I1"].Text = "Vocational Expert"; xlsworksheet.Range["J1"].Text = "Case_Status__c"; xlsworksheet.Range["K1"].Text = "Case_Sub_Status__c"; xlsworksheet.Range["L1"].Text = "Confirmed_Hearing_Date__c"; xlsworksheet.Range["M1"].Text = "Lead_Advocate1__r.Name"; xlsworksheet.Range["N1"].Text = "Advocate_Status__c"; int curRow = 2; for (int i = 2; i <= xlsList.Count + 1; i++) { //xlsworksheet.Range["A" + curRow.ToString()].Text = xlsList[i - 2].Id; //xlsworksheet.Range["B" + curRow.ToString()].Text = xlsList[i - 2].LastName; //xlsworksheet.Range["C" + curRow.ToString()].Text = xlsList[i - 2].FirstName; //xlsworksheet.Range["D" + curRow.ToString()].Text = xlsList[i - 2].MiddleName; //xlsworksheet.Range["E" + curRow.ToString()].Text = xlsList[i - 2].Account; //xlsworksheet.Range["F" + curRow.ToString()].Text = xlsList[i - 2].Last4SSN; //xlsworksheet.Range["G" + curRow.ToString()].Text = xlsList[i - 2].HearingOfficeWithJurisdiction; //xlsworksheet.Range["H" + curRow.ToString()].Text = xlsList[i - 2].HearingScheduledDate; //xlsworksheet.Range["I" + curRow.ToString()].Text = xlsList[i - 2].HearingTime; //xlsworksheet.Range["J" + curRow.ToString()].Text = xlsList[i - 2].ALJLastName; //xlsworksheet.Range["K" + curRow.ToString()].Text = xlsList[i - 2].MedicalExpert; //xlsworksheet.Range["L" + curRow.ToString()].Text = xlsList[i - 2].VocationalExpert; //xlsworksheet.Range["A" + curRow.ToString()].Text = xlsList[i - 2].Id; xlsworksheet.Range["A" + curRow.ToString()].Text = xlsList[i - 2].LastName; xlsworksheet.Range["B" + curRow.ToString()].Text = xlsList[i - 2].FirstName; //xlsworksheet.Range["D" + curRow.ToString()].Text = xlsList[i - 2].MiddleName; xlsworksheet.Range["C" + curRow.ToString()].Text = xlsList[i - 2].Account; // xlsworksheet.Range["F" + curRow.ToString()].Text = xlsList[i - 2].Last4SSN; xlsworksheet.Range["D" + curRow.ToString()].Text = xlsList[i - 2].HearingOfficeWithJurisdiction; //xlsworksheet.Range["E" + curRow.ToString()].Text = xlsList[i - 2].HearingScheduledDate; xlsworksheet.Range["E" + curRow.ToString()].DateTime = xlsList[i - 2].HearingScheduledDate; xlsworksheet.Range["F" + curRow.ToString()].Text = xlsList[i - 2].HearingTime; xlsworksheet.Range["G" + curRow.ToString()].Text = xlsList[i - 2].ALJLastName; xlsworksheet.Range["H" + curRow.ToString()].Text = xlsList[i - 2].MedicalExpert; xlsworksheet.Range["I" + curRow.ToString()].Text = xlsList[i - 2].VocationalExpert; // var temp = xlsList[i - 2]; if (csvPairs.ContainsKey(xlsList[i - 2].Account)) { //List<csvData> csvList = csvPairs[xlsList[i - 2].Account]; List <csvData> csvList = csvPairs[xlsList[i - 2].Account]; //List<csvData> csvList = csvPairs[xlsList[i - 2].Account]; for (int j = 0; j < csvList.Count; j++) { if (0 == j) { xlsworksheet.Range["J" + curRow.ToString()].Text = csvList[j].Case_Status__c; xlsworksheet.Range["K" + curRow.ToString()].Text = csvList[j].Case_Sub_Status__c; xlsworksheet.Range["L" + curRow.ToString()].Text = csvList[j].Confirmed_Hearing_Date__c; //xlsworksheet.Range["M" + curRow.ToString()].Text = csvList.Count.ToString(); xlsworksheet.Range["M" + curRow.ToString()].Text = csvList[j].Lead_Advocate1__r_Name; xlsworksheet.Range["N" + curRow.ToString()].Text = csvList[j].Advocate_Status__c; } else { curRow++; xlsworksheet.Range["A" + curRow.ToString()].Text = " "; xlsworksheet.Range["B" + curRow.ToString()].Text = " "; xlsworksheet.Range["C" + curRow.ToString()].Text = " "; xlsworksheet.Range["D" + curRow.ToString()].Text = " "; xlsworksheet.Range["E" + curRow.ToString()].Text = " "; xlsworksheet.Range["F" + curRow.ToString()].Text = " "; xlsworksheet.Range["G" + curRow.ToString()].Text = " "; xlsworksheet.Range["H" + curRow.ToString()].Text = " "; xlsworksheet.Range["I" + curRow.ToString()].Text = " "; xlsworksheet.Range["J" + curRow.ToString()].Text = csvList[j].Case_Status__c; xlsworksheet.Range["K" + curRow.ToString()].Text = csvList[j].Case_Sub_Status__c; xlsworksheet.Range["L" + curRow.ToString()].Text = csvList[j].Confirmed_Hearing_Date__c; //xlsworksheet.Range["M" + curRow.ToString()].Text = csvList.Count.ToString(); xlsworksheet.Range["M" + curRow.ToString()].Text = csvList[j].Lead_Advocate1__r_Name; xlsworksheet.Range["N" + curRow.ToString()].Text = csvList[j].Advocate_Status__c; } } } else { xlsworksheet.Range["J" + curRow.ToString()].Text = "NULL"; xlsworksheet.Range["K" + curRow.ToString()].Text = "NULL"; xlsworksheet.Range["L" + curRow.ToString()].Text = "NULL"; xlsworksheet.Range["M" + curRow.ToString()].Text = "NULL"; xlsworksheet.Range["N" + curRow.ToString()].Text = "NULL"; } curRow++; } //Create a workbook //IWorkbook workbook = application.Workbooks.Create(1); //IWorksheet xlsworksheet = xlsworkbook.Worksheets[0]; //int lastRow = worksheet.UsedRange.LastRow; //int lastCol = worksheet.UsedRange.LastColumn; //Disable gridlines in the worksheet //worksheet.IsGridLinesVisible = false; //Enter values to the cells from A5 to C5 //xlsworksheet.Range["A1"].Text = csvDatas[1].Lead_Advocate1__r_Name; // worksheet.Range["B5"].Text = "Tony"; // worksheet.Range["C5"].Text = "HR"; //File.Delete("test.csv"); //File.Delete("test.xlsx"); using (MemoryStream stream = new MemoryStream()) { //Save the created Excel document to MemoryStream xlsworkbook.SaveAs(stream); xlsInputStream.Close(); //csvworkbook.SaveAs(stream, ","); File.Delete("test.csv"); File.Delete("test.xlsx"); return(stream); } } }