C#: Insert into Excel as log file

Question

Is there any specific way to create a log file for my functions. All these functions are as testcases. I am using the below code to add the data.

string pathfile = @"data source location";
XLWorkbook workbook = new XLWorkbook(pathfile);
IXLWorksheet worksheet = workbook.Worksheet("Common");
worksheet.Cell("A2").Value = "1";
worksheet.Cell("B2").Value = "Test Case # 1";
worksheet.Cell("C2").Value = "1/29/2016 6:18:56 PM";
worksheet.Cell("D2").Value = "1/29/2016 6:19:56 PM";
worksheet.Cell("E2").Value = "1 minute";
workbook.Save();

Now I have to specify this every time in my every function. Is there any other better and good way to create excel log file?

Example of Excel Log File

+------+-------------------+-------------------------+---------------------------+---------------+
|  ID  | Testcases No.     |        Start Time       |         End Time          |   Total Time  |  
+------+-------------------+-------------------------+---------------------------+---------------+
|  1   | Test Case # 1     |   1/29/2016 6:18:56 PM  |   1/29/2016 6:19:56 PM    |   1 minute    |  
+------+-------------------+-------------------------+---------------------------+---------------+
|  2   | Test Case # 2     |   1/29/2016 6:20:50 PM  |   1/29/2016 6:25:50 PM    |   5 minutes   |
+------+-------------------+-------------------------+---------------------------+---------------+

Program uses:

Selenium
Unit test class
Nunit to run my application

Unless you need some of the richer functionality of Excel worksheets (formulas, etc.), just dump it to a CSV. — 3Dave, Feb 01 '16 at 14:17

score 1 · Answer 1 · answered Feb 01 '16 at 14:51

1

As a developer you should have or should be building up a project(s) of utility classes. Routines you can live without or even a framework for your project / companies.

ie Cryptography, Email, Data Access, LOGGING

This way your company or you personally can drop in this project(s) into your project and have a wealth of functionality instantly at your disposal.

I find the use of Excel for this simple logging task to be a bit heavy

So why your not using 1 of the best i.e. Net4Logger http://www.codeproject.com/Articles/140911/log-net-Tutorial

answered Feb 01 '16 at 14:51

Ggalla1779

476
7
18

My Project is in console application and uses Selenium and NUnit to run all my test cases. The Link you sent I think it won't help. – Feb 01 '16 at 17:45
If you add Log4Net library reference to your project and output to a log file rather than a heavy Excel spreadsheet. Do you really need an excel file for this? – Ggalla1779 Feb 02 '16 at 08:45
Yes I do need excel. That is the reason I posted this question. – Feb 02 '16 at 13:52

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

A nice simple way is to simply create a datatable and then export it to excel using closedxml (which you already tagged, so i assume you have the library already)

XLWorkbook workbook = new XLWorkbook();
DataTable dt = //insert your datasource here
workbook.Worksheets.Add(dt,"WorksheetName");

Reference Here

edited May 23 '17 at 12:32

Community

1
1

answered Feb 01 '16 at 13:59

Takarii

1,612
2
18
29

score 0 · Answer 3 · answered Feb 01 '16 at 14:18

An alternative to Takarii's suggestion.

If you store your data in a DataTable or List<> then using my free C# library, you can create a real .xlsx file using the Microsoft OpenXML libraries.

Export to Excel

You just need to add one line of code, saying where your data is stored, and what you want to call the Excel file:

List<YourLogEntryClass> listOfLogRecords = new List<YourLogEntryClass>();

. . . 

CreateExcelFile.CreateExcelDocument(listOfLogRecords, "LogFile.xlsx");

P.S. A more sensible idea might be to not to store the log entries in an Excel file, but to use something like log4net which looks after all this logging info/error strings for you...

score -2 · Answer 4 · answered Feb 01 '16 at 14:05

-2

Is there any reason not to take that block of code, and break it out into a separate function that takes file location, id, etc. as arguments?

e.g.

public void LogTest(string fileLocation, int id, float minutes)
{
    // your code 
}

answered Feb 01 '16 at 14:05

Tyler H

413
3
11

No there are no reason. Also the snippet you have added is not a question. Question is I don't want to specify the cell number '(example Cel("A2"))' in every place so I am asking is how to enter the log data without specifying Column Number in my code. And adding it as a block of code, not all rows will be populate right? – Feb 01 '16 at 14:10
@TylerH Note, this is not an answer. You shouldn't post things that should be comments as answers. – Takarii Feb 01 '16 at 14:11
Assuming id is always equal to test number, `Cell("A" + (id + 1))` will generate the correct row, assuming the letter part doesn't change. – Tyler H Feb 01 '16 at 14:12
@Takarii I currently don't have enough reputation to add comments – Tyler H Feb 01 '16 at 14:12
1

@TylerH Then you need to wait until you do. Not trying to be a pain, but posting answers that aren't answers can prevent other people from reviewing a question and potentially giving an actual answer. – Takarii Feb 01 '16 at 14:14

C#: Insert into Excel as log file

4 Answers4