I am an intern, I am programming in C# right now and I have to retrieve a string of characters from an excel file. This excel is actually results gathered after a chemical analysis called ICP from 2 different machines (as you can see below, the example 1 correspond to the machine called "thermo" and the other example for the machine called " Agilent").
This Excel is a one empty excel file configured where the user can copy and paste data and press some buttons to do some treatments. One of these is the fact that when you press the button, all items you will find in the column A, will be put in the cell B2 separated by semicolons like a list.
After retrieving this string in the cell B2, I should return it in another method which should split this string into 2 lists (Sample and Reference) using a regex. My problem is that I don't know if my initial string is returned correctly from the first method because when I am running the code, it's not working correctly, the string is split correctly but there are more elements than expected.
Here is my code :
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
namespace excel
{
class Class1
{
public static string ReadExcel()
{
string filepath = @"C:\Users\Lionel84100\Desktop\Excel\fezfFzfe.xlsx";
try
{
IWorkbook workbook = null;
FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read);
if (filepath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filepath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheet("Formatage_IM");
if (sheet != null)
{
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; i++)
{
IRow curRow = sheet.GetRow(i);
string cellValue1 = curRow.GetCell(1).StringCellValue;
return cellValue1;
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return "";
}
public static (List<string> sample, List<string> reference) parse()
{
string input = ReadExcel();
Regex regex = new Regex(@"^(\s*;\s*)+|(\s*;\s*)+$");
string output = regex.Replace(input, " ");
List<string> sample = new List<string>();
List<string> reference = new List<string>();
string pattern = @"C-[0-9]{6}-S-[0-9]{3}( [0-9])?";
Regex rgx = new Regex(pattern);
foreach (string term in output.Split(';').Select(s => s.Trim()).Where(s => !string.IsNullOrEmpty(s)))
{
if (rgx.IsMatch(term))
{
sample.Add(term);
}
else
{
reference.Add(term);
reference.ForEach(Console.WriteLine);
}
}
return (sample, reference);
}
}
}
In my main I am only calling the second method.
Examples of string of characters :
Example 1: "QC4 1000 ppm ; C-191108-S-0095 ; C-191108-S-0095 ; QC4 100 ppm ; QC4 1000 ppm ; Et 500 ppm ; Et 1000 ppm ; Et 2000 ppm ; "
Example2 : " ; C-210104-S-117 2 ; C-210104-S-118 1 ; C-210104-S-118 2 ; Ref 100ppm QC4 ; Ref 1000ppm QC4 ; "
error in my console more elements than expected:
The expected results : Example 1 / list sample : C-191108-S-0095 ; C-191108-S-0095 ; (This correspond to the name of the samples)
Example 1/ list reference : QC4 1000 ppm ;QC4 100 ppm ; QC4 1000 ppm ; Et 500 ppm ; Et 1000 ppm ; Et 2000 ppm ; " (this correspond to the names of references taken as reference values to compare results after an chemical analysis)
Example 2 / list sample : C-210104-S-117 2 ; C-210104-S-118 1 ; C-210104-S-118 2
Example 2/ list reference : Ref 100ppm QC4 ; Ref 1000ppm QC4 ;
can you say where is an error in my code please ?
Thank you for your comprehension.
