Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update SQLite database from an excel file #73

Open
DonB007 opened this issue Jan 21, 2023 · 0 comments
Open

Update SQLite database from an excel file #73

DonB007 opened this issue Jan 21, 2023 · 0 comments

Comments

@DonB007
Copy link

DonB007 commented Jan 21, 2023

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.

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();
				}
				
			}
			
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant