Results Export To Excel Adds Extra Rows
I am currently running a LINQPad script which is giving me the desired results but when I use the Export to Excel functionality the resultant output file contains extra rows which I don't want. Hopefully someone can explain to me how to avoid the single results row being split into multiple excel rows.
Below are screenshots of my Results from running a LINQpad script (a copy of which is included at the bottom of the post) and a screenshot of the Export to Excel. It shows how a single row of the results is split across 11 rows when I perform an Export to Excel, saving as xlsx file. I need to find a way to stop this from happening so that a single line is exported as it is in the Results. I believe the issue is caused by the conversion of the HTML using Util.RawHtml as each extra row corresponds with a <br/> element . The Description & DescriptionConverted (3rd & 4th) fields contain the actual and Util.RawHtml processed data.
I am currently removing the extra excel rows using an Excel macro but this can take well over an hour to run as the excel file can have in excess of 100,000 rows and is not sustainable.
The script, screenshots & resultant excel file have all been attached in a 7z file.
Results
Excel
void Main()
{
var _db = this;
string assetTypeCode = "HVAC69";
var dataSet = from pt in _db.ProcedureTasks
join p in _db.Procedures on pt.ProcedureID equals p.ProcedureID
join at2ltv in _db.AssetTypeToLibraryTypeVersions on p.AssetTypeToLibraryTypeVersionID equals at2ltv.AssetTypeToLibraryTypeVersionID
join atm2lat in _db.AssetTypeMasterToLibraryAssetTypes on at2ltv.AssetTypeToLibraryTypeVersionID equals atm2lat.AssetTypeToLibraryTypeVersionID
join at in _db.AssetTypes on atm2lat.AssetTypeID equals at.AssetTypeID
where at.AssetTypeCode == (assetTypeCode == null ? at.AssetTypeCode : assetTypeCode)
select new ExportToExcelTest {
SFG20Code = at2ltv.LibraryTypeVersionAssetTypeCode,
Task = pt.Task == null ? "" : pt.Task,
Description = pt.Description == null ? "" : pt.Description,
Notes = pt.Notes == null ? "" : pt.Notes
};
dataSet.Dump();
}
class ExportToExcelTest
{
public string SFG20Code { get; set; }
public string Task { get; set; }
public string Description { get; set; }
public string Notes { get; set; }
public string MarkAsString(string value) {
return "'" + value;
}
object ToDump() => new { SFG20Code
, Task
, Description
, DescriptionConverted = Util.RawHtml(Description)
, Notes
};
}
Comments
Try the following:
https://www.bennadel.com/blog/1095-maintaining-line-breaks-in-an-html-excel-file.htm
...Util.RawHtml (....Replace ("<br />", "<br style='mso-data-placement:same-cell;'>"Thanks Joe. That works a treat