Click here to Skip to main content
15,846,144 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm trying to add data to a table in a SQLite database by getting data from a excel file using the fastexcel library.

But I cannot figure out how do I get values from specific cells/column of a row in a worksheet.

Can anyone help me with this please ...

What I have tried:

C#
public void UpdateDBtable()
{
			DataTable dt = new DataTable();

			dt.Columns.Add("Party");
			dt.Columns.Add("Bill No");
			...
			dt.Columns.Add("Head");

			
			string file_Bills = @"D:\Test\Test.xlsx";
			
			using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(file_Bills, true))
			{

				Worksheet worksheet = fastExcel.Read(1);
				
				var rows = worksheet.Rows.ToArray();
				
				//ideally the below rows.Count() should be the count of number of rows whose column A value exists but don't know how to do that either
				for (int _row = 2; _row <= rows.Count(); _row++)
				{
					DataRow dr = dt.NewRow();

					//below line is intended to get specific cells text but doesn't work
					dt.Rows.Add(worksheet.Cells[_row, 1].Text, worksheet.Cells[_row, 2].Text, ..., worksheet.Cells[_row, 7].Text);
					
					dt.AcceptChanges();
					
				}
				using(SQLiteConnection conn= new SQLiteConnection(@"Data Source="+Path.GetFullPath("./test.db")))
				{
					conn.Open();
					
					using (var cmd = new SQLiteCommand(conn))
					{
						
						string str;
						SQLiteCommand com;

						foreach (DataRow row in dt.Rows) 
						{
							str = "INSERT OR IGNORE INTO billdata(Party,BillNo,...,Head)values(@Party,@BillNo,...,@Head)";
							
							com = new SQLiteCommand(str, conn);
							com.Parameters.AddWithValue("@Party", row.Field<string>(0));
							com.Parameters.AddWithValue("@BillNo", row.Field<string>(1));
							...
							com.Parameters.AddWithValue("@Head", row.Field<string>(8));
							com.ExecuteNonQuery();
						}
						
					}
					
					conn.Close();
				}
				
			}
			
}
Posted
Updated 20-Jan-23 4:18am

1 solution

From the examples they have given in that project, Read example 2 has this snippet
C#
var rows = worksheet.Rows.ToArray();
        //Do something with rows
        Console.WriteLine(string.Format("Worksheet Rows:{0}", rows.Count()));
It would appear then that the array rows is a two-dimensional array - you can confirm this by running the example and examining rows in the Watch window. Something like rows[0,1] would return the value of Cell A2 - probably.
However, this is a question that is best asked of the author of that project.
 
Share this answer
 
Comments
Member 12692000 20-Jan-23 9:58am    
Putting a breakpoint in the for loop I get this.

If I try to do string tempCell = (rows[_row].Cells[3]).ToString(); I get the error Cannot apply indexing with [] to an expression of type 'System.Collections.Generic.IEnumerable<FastExcel.Cell>' (CS0021)
CHill60 20-Jan-23 13:20pm    
In my solution I suggested that rows was a two-dimensional array. Your code is using a collection of objects.
I still stand by my comment that these questions should be directed toward the author of the project - there is clearly a custom type involved - <fastexcel.cell>
Member 12692000 20-Jan-23 20:03pm    
Noted your points.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900