Has anyone worked with ClosedXML?

Hey everyone, I think this is the first time I am posting in this community. I have a few questions that hopefully someone can help with if you have worked with ClosedXML. It is a pretty straightforward workaround to OpenXML, meaning less work for the same output pretty much.

To give an overview:I originally made an excel spreadsheat through the oh so lovely SSRS, but I needed a little more control over how the information panned out on the spreadsheat.

So I have a very simple function that displays information pulled from my db in the following function:

public byte[] BuildSysMaintExcelFile(Systems sysRec, Approver Name)

{

const int dataRowStart = 3;

const int dataColStart = 1;

string templateName = HttpContext.Current.Server.MapPath("~/App_Data/SystemMaintenance_Template.xlsx");

var workbook = new XLWorkbook(templateName);

var worksheet = workbook.Worksheet(1);

string folderPath = HttpContext.Current.Server.MapPath("~//Downloads//");

if (!Directory.Exists(folderPath))

{

Directory.CreateDirectory(folderPath);

}

// TODO: ONCE TEMPLATE NAME AND FOLDERPATH ARE DYNAMIC THIS WILL WORK ITSELF OUT

string fileName = folderPath + "SysReport -" + DateTime.Now.ToString("yyyyMMddHHmmssfffff") + ".xlsx";

DataTable dt = new DataTable(templateName);

worksheet.Cell(dataRowStart, dataColStart).InsertData(dt.AsEnumerable());

dt.Columns.Add("systemName", typeof(string));

dt.Columns.Add("isActive", typeof(bool));

dt.Columns.Add("localIAO", typeof(bool));

dt.Columns.Add("localStaff", typeof(bool));

dt.Columns.Add("informationOfficerRequired", typeof(bool));

dt.Columns.Add("iaoRequired", typeof(bool));

dt.Columns.Add("IAOs", typeof(string));

dt.Columns.Add("StaffProcessors", typeof(string));

dt.Columns.Add("StaffRevalidators", typeof(string));

var dr = dt.NewRow();

// TODO: GRANT THIS IS WHERE IT IS NOW ERRORING

worksheet.Column(1).Cell(dataRowStart).InsertData(dt.AsEnumerable());

worksheet.Cell("A3").Value = dr[0] = sysRec.systemName;

worksheet.Cell("B3").Value = dr[1] = sysRec.isActive;

worksheet.Cell("C3").Value = dr[2] = sysRec.localIAO;

worksheet.Cell("D3").Value = dr[3] = sysRec.localStaff;

worksheet.Cell("E3").Value = dr[4] = sysRec.informationOfficerRequired;

worksheet.Cell("F3").Value = dr[5] = sysRec.iaoRequired;

worksheet.Cell("G3").Value = dr[6] = Name;

worksheet.Cell("H3").Value = dr[7] = "";

worksheet.Cell("I3").Value = dr[8] = "";

worksheet.Cell("J3").Value = dr[9] = sysRec.StaffProcessors;

worksheet.Cell("K3").Value = dr[10] = "";

worksheet.Cell("L3").Value = dr[11] = sysRec.StaffRevalidators;

worksheet.Cell("Q3").Value = dr[16] = "";

worksheet.Cell("R3").Value = dr[16] = "";

worksheet.Cell("S3").Value = dr[16] = "";

worksheet.Cell("T3").Value = dr[16] = "";

worksheet.Cell("U3").Value = dr[16] = "";

using (var ms = new MemoryStream())

{

workbook.SaveAs(ms);

ms.Position = 0;

return ms.ToArray();

}

}

Is there a “simple” way for a special display on a boolean? Meaning if a particular item returns false, it displays no, if the db returns true, it displays yes?

submitted by /u/triumphover
[link] [comments]

Leave a Reply