This project has moved. For the latest updates, please go here.

Does ClosedXML instantiate a new instance of Excel?

Apr 28, 2014 at 5:31 PM
I am writing a C# program that will run on a task scheduler 3 - 4 times a day. It will be installed on a server that is also used as a terminal server w/ folks using Remote Desktop to access it numerous times a day. That being said, I can't just arbitrarily install Excel on it b/c Microsoft requires different licensing if the server is used as a terminal server. When I test my program on the server, it acts like it is running successfully but the spreadsheet never gets created. I have plenty of logging, so I know that it has gotten past the new XLWorkbook() line and has went through a WHILE loop populating the cells (supposedly). I don't need to open the spreadsheet on the server, rather it will just be created and then used as an import file into 3rd party web application. Will it only work if Excel is installed on the server? I don't get any errors telling me that it can't find Excel or anything to point me in the right direction.
Apr 28, 2014 at 5:42 PM
You do not need Excel installed on the server.
To create the file you need to do a workbook.SaveAs() call to save the workbook to a file.
Put a try/catch around the SaveAs to see if it is getting any error and log the error.
Apr 28, 2014 at 5:46 PM

I already did that and there is no error. It works OK on my laptop (but then again, I have Excel – thus the reason why I thought it was req’d). I attached the snippet of code that does this. I have plenty of logging in it to trap any errors. None found.

var workbook = new XLWorkbook();

wsname = wsname + PayDate.ToString("MMDDYYYY");

fn = sFilePath + sNomenclature + ".xlsx";

var worksheet = workbook.Worksheets.Add(wsname);

sql = "SELECT EmployeeID, PayDate, CostCenter4, CostCenter5, Costcenter6, Qty, PieceWork " +

"FROM WRFXUpld " +

"WHERE cast(PayDate as smalldatetime) >= cast(@PayDate as smalldatetime) " +

"ORDER BY EmployeeID, PayDate, CostCenter6, CostCenter5 ";

SqlConnection mySQLConnection = new SqlConnection(sConnectionString);

mySQLConnection.Open();

SqlCommand mySQLCommand = new SqlCommand(sql, mySQLConnection);

mySQLCommand.Parameters.Add("@PayDate", SqlDbType.Char);

mySQLCommand.Parameters["@PayDate"].Value = cPayDate;

try

{

string s15 = "Attempting to SELECT fields from WRFXUpld for the spreadsheet...";

Log(s15);

SqlDataReader mySQLReader = mySQLCommand.ExecuteReader();

string s16 = "Finished SELECT fields for the spreadsheet...";

Log(s16);

if (mySQLReader.Read())

{

string indcell = "";

int i = 1;

string s17 = "Going into loop to populate spreadsheet...";

Log(s17);

while (mySQLReader.Read())

{

indcell = "A" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["EmployeeID"].ToString().ToUpper().Trim();

string s17a = "In the spreadsheet loop for EmployeeID = " + mySQLReader["EmployeeID"].ToString().ToUpper().Trim();

Log(s17a);

indcell = "B" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["PayDate"].ToString().ToUpper().Trim();

indcell = "C" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["CostCenter4"].ToString().ToUpper().Trim();

indcell = "D" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["CostCenter5"].ToString().ToUpper().Trim();

indcell = "E" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["CostCenter6"].ToString().ToUpper().Trim();

indcell = "F" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["Qty"];

indcell = "G" + i.ToString();

worksheet.Cell(indcell).Value = mySQLReader["PieceWork"];

i = i + 1;

}

string s18 = "Finished looping to populate spreadsheet...";

Log(s18);

mySQLReader.Close();

mySQLReader.Dispose();

workbook.SaveAs(fn);

string s19 = "SAVED the spreadsheet!!!";

Log(s19);

}

else

{

string s20 = "No spreadsheet to save due to no data.";

Log(s20);

}

mySQLConnection.Close();

mySQLConnection.Dispose();

}

catch (Exception e4)

{

mySQLConnection.Close();

mySQLConnection.Dispose();

string s = "Unable to create spreadsheet due to: " + e4.Message;

Log(s);

}

}

Thanks!

Mark Skinnell

ITP Sr. Project Manager

Office: 828-322-6261

www.itpbarcode.com

ITP logo tag1-small

Coordinator
Apr 28, 2014 at 6:31 PM
You don't need Excel to use ClosedXML.

Put a log before and after workbook.SaveAs
Apr 28, 2014 at 7:17 PM
There is a log try before the SaveAs (actually right before the connection is closed & disposed) and then one right afterwards as shown below:

.
.
.
.
indcell = "F" + i.ToString();
worksheet.Cell(indcell).Value = mySQLReader["Qty"];
indcell = "G" + i.ToString();
worksheet.Cell(indcell).Value = mySQLReader["PieceWork"];

i = i + 1;

}

string s18 = "Finished looping to populate spreadsheet...";
Log(s18);

mySQLReader.Close();
mySQLReader.Dispose();

workbook.SaveAs(fn);

string s19 = "SAVED the spreadsheet!!!";
Log(s19);

}
else
{
string s20 = "No spreadsheet to save due to no data.";
Log(s20);
}
Coordinator
Apr 28, 2014 at 7:26 PM
No clue but it has to be a server setup/configuration/environment thing.
Apr 28, 2014 at 7:28 PM
I think I just found it!!! The specified directory needed to have a trailing '\' on it. Now it works! Thanks for the help!