Hi, I'm doing an application to read excel sheets, and I need to know if what I'm doing is the best or if someone else comes up with another idea, tell me before I get into this.
I explain the process, first I take the excel file and I transform it into a csv file with the following method.
public static bool SaveAsCsv(string excelFilePath, string destinationCsvFilePath)
{
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
if (excelFilePath.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (excelFilePath.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
return false;
var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
var csvContent = string.Empty;
int row_no = 0;
while (row_no < ds.Tables[0].Rows.Count)
{
var arr = new List<string>();
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
arr.Add(ds.Tables[0].Rows[row_no][i].ToString());
}
row_no++;
csvContent += string.Join(",", arr) + "\n";
}
StreamWriter csv = new StreamWriter(destinationCsvFilePath, false);
csv.Write(csvContent);
csv.Close();
return true;
}
}
and I call it in the following way
var excelFilePath = "Direccion del archivo";
string output = Path.ChangeExtension(excelFilePath, ".csv");
Form1.SaveAsCsv(excelFilePath, output);
Then the csv file I take it and I read it
public List parseCSV (string path) { List parsedData = new List ();
using (StreamReader readFile = new StreamReader(path))
{
string line;
string[] row;
while ((line = readFile.ReadLine()) != null)
{
row = line.Split(',');
parsedData.Add(row);
}
}
return parsedData;
}
And here I call the method
try
{
dt.Clear();
List<string[]> testParse = parseCSV(@"Direccion del archivo");
foreach (string column in testParse[0])
{
dt.Columns.Add(column);
}
foreach (string[] row in testParse)
{
if (row.Contains("Condiciones que no quiero")) { }
else if (row.Contains("Condiciones que no quiero")) { }
else if (row.Contains("Condiciones que no quiero")) { }
else if (row.Contains("Condiciones que no quiero")) { }
else { dt.Rows.Add(row); }
}
}
catch(Exception ex)
{
MessageBox.Show("No existe ningún archivo CSV con ese nombre. " + ex.Message);
}
dataGridView1.DataSource = dt;
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
//row[column] obtiene el valor de las columnas de la fila
}
}
And it works already I saw the data that I have what happens is that now I want to pass everything to a database to make queries there and my idea is to send all the records to the database with the following
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
//row[column] obtiene el valor de las columnas de la fila
}
}
but I do not know if it is correct or possible because apart from that what I have to compare from this document with the other are names and hours, but the time of one of the documents is in the following format 7:55 am or pm, while in the other document is from 3:40 pm or even 30:02 that would be 6:02 am and I want to know how I can convert this data to this format 6:02 am and then how to compare ... I do not know if explain me well any lack of information tell me, thanks in advance friends