16

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();
Massimiliano Kraus
  • 3,638
  • 5
  • 27
  • 47
Booksman
  • 1,565
  • 5
  • 19
  • 32
  • I put this way but there is an syntax error: newSheet.Cells[i + 2, 3] = drugorder.DATE; HorizontalAlignment == ExcelAlignment.xlLeft; Error 2 'System.Windows.Forms.HorizontalAlignment' is a 'type' but is used like a 'variable' C:\ADL\ExcelReader\ExcelReader\Form1.cs 257 68 ExcelReader Error 3 Only assignment, call, increment, decrement, and new object expressions can be used as a statement C:\ADL\ExcelReader\ExcelReader\Form1.cs 257 68 ExcelReader – Booksman Sep 21 '11 at 17:45
  • thats because you just copied and pasted the answer from Alain. The error occurs because you are using ExcelAlignment.xlLeft; instead try using rng.HorizontalAlignment = XlHAlign.xlHAlignLeft; – Anonymous Type Sep 15 '14 at 23:41

1 Answers1

19

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
Alain
  • 26,663
  • 20
  • 114
  • 184