How to format cells in excel sheet programmatically?

We have an asp.net c# program that reads a sheet from an Excel file and writes it out in a new sheet (also adding one column if data come from a Sql Server table).

Issue: in the new sheet the data is not formatted as we want. For example we want date without time and left-aligned, but they’re formatted with the time and right-aligned, etc.

How can we format an Excel cell?

This is our code:

newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = worksheetName;

for (int i = 0; i < headerList.Count; i++)
{
    newSheet.Cells[1, i + 1] = headerList[i];
    Range headerRange = newSheet.Cells[1, headerList.Count]; ;
    headerRange.Font.Bold = true;
}
for (int i = 0; i < listDrugOrder.Count; i++)
{
    DrugOrder drugorder = listDrugOrder[i];

    newSheet.Cells[i + 2, 1] = drugorder.RES_ID;
    newSheet.Cells[i + 2, 2] = drugorder.STATION;
    newSheet.Cells[i + 2, 3] = drugorder.DATE;
    newSheet.Cells[i + 2, 4] = drugorder.DRUG;
    newSheet.Cells[i + 2, 5] = drugorder.NDC;
    newSheet.Cells[i + 2, 6] = drugorder.UNITS_PER_DOSE;
    newSheet.Cells[i + 2, 7] = drugorder.FORM;
    newSheet.Cells[i + 2, 8] = drugorder.ROUTE;

    newSheet.Cells[i + 2, 10] = drugorder.FREQUENCY;
    newSheet.Cells[i + 2, 11] = drugorder.Heading_LAKE_ORDERS;
    newSheet.Cells[i + 2, 12] = drugorder.HOA;
    newSheet.Cells[i + 2, 13] = drugorder.INSTRUCTIONS;
    newSheet.Cells[i + 2, 14] = drugorder.DIAGNOSIS;
    newSheet.Cells[i + 2, 15] = drugorder.DIAGNOSIS_CODES;
    newSheet.Cells[i + 2, 16] = drugorder.MAR;
    newSheet.Cells[i + 2, 17] = drugorder.TAR;
    newSheet.Cells[i + 2, 18] = drugorder.DRUG_ALERT;
}

workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Just set the appropriate property on your cell (Range) objects.

Set NumberFormat to control the cell number formatting, i.e.:

newSheet.Cells[i, j].NumberFormat = "m/d/yyyy"

Set HorizontalAlignment to control the alignment, i.e.:

newSheet.Cells[i, j].HorizontalAlignment = ExcelAlignment.xlLeft; //or Excel.XlHAlign.xlHAlignLeft

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0