user131799 February 2016

Importing data to excel using C#

I have been trying to find a way to insert data in excel. What I am trying to do here is whenever I run my automation script some data are entered into excel worksheet. One more thing is that first column should be incremented when ever I add my data. Below excel sheet is just an example.

enter image description here

Based on the example. If I had ran my application first row have been created using first line after header in excel having test case name and other descriptions. When I run the application again then it should create another row with incrementing with an id of 2

Please help to find a way to insert data to excel.

Let me know if I am on a wrong post instead to voting down for this post.

string pathfile = @"Logs.xlsx";
DateTime date1 = DateTime.Now;
Console.WriteLine(date1);
XLWorkbook workbook = new XLWorkbook(pathfile);
IXLWorksheet worksheet = workbook.Worksheet("Result");
Console.Write("Enter UserName:");
string uname = Console.ReadLine();
worksheet.Cell("A2").Value = uname;
Console.Write("Enter Password:");
string pwd = Console.ReadLine();
worksheet.Cell("B2").Value = pwd;
workbook.Save();
DateTime date2 = DateTime.Now;
TimeSpan timediff = date2 - date1;
Console.WriteLine(timediff.Minutes + " minutes and " + timediff.Seconds + " seconds");
var wb = new XLWorkbook();
wb.Worksheets.Add("Result");
wb.SaveAs("Logs.xlsx");

Answers


Steve February 2016

I take it you are using the ClosedXML library. You need to find the last row and add it after, like this :

string pathfile = @"Logs.xlsx";
DateTime date1 = DateTime.Now;
Console.WriteLine(date1);
XLWorkbook workbook = new XLWorkbook(pathfile);
IXLWorksheet worksheet = workbook.Worksheet("Result");
Console.Write("Enter UserName:");
string uname = Console.ReadLine();
int lastRow = Worksheet.LastRowUsed().RowNumber + 1;
worksheet.Cell(String.Format("A{0}", lastRow)).Value = uname;
Console.Write("Enter Password:");
string pwd = Console.ReadLine();
worksheet.Cell(String.Format("B{0}", lastRow)).Value = pwd;
workbook.Save();
DateTime date2 = DateTime.Now;
TimeSpan timediff = date2 - date1;
Console.WriteLine(timediff.Minutes + " minutes and " + timediff.Seconds + " seconds");
var wb = new XLWorkbook();
wb.Worksheets.Add("Result");
wb.SaveAs("Logs.xlsx");

Post Status

Asked in February 2016
Viewed 3,583 times
Voted 13
Answered 1 times

Search




Leave an answer