如何格式化细胞Excel工作表编程？ [英] How to format cells in excel sheet programmatically?

问题描述

我有一个asp.net C＃程序读取从Excel工作表，并写出它作为一个新的工作表（也添加一列中，如果从SQL Server表中的数据。

唯一的问题是新表中的数据未格式化的日期与时间，例如他们是正确的调整，这是我们要左形容词。等等。

如何做一种格式？是这里吗？

  newSheet =（表）sheets.Add（张[1]，Type.Missing，Type.Missing，Type.Missing）;
                newSheet.Name = worksheetName;                的for（int i = 0; I＆LT; headerList.Count;我++）
                {
                    newSheet.Cells [1，I + 1] = headerList [I]
                    范围headerRange = newSheet.Cells [1，headerList.Count]; ;
                    headerRange.Font.Bold = TRUE;
                }
                的for（int i = 0; I＆LT; listDrugOrder.Count;我++）
                {
                    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（）;
 

解决方案

刚刚成立的你的细胞相应的属性（范围）对象。

设置NumberFormat来控制细胞数字格式，例如：

  newSheet.Cells [I，J] .NumberFormat =M / D / YYYY
 

设置Horizo​​ntalAlignment以控制对齐方式，即：

  newSheet.Cells [I，J] .Horizo​​ntalAlignment = ExcelAlignment.xlLeft; //或Excel.XlHAlign.xlHAlignLeft
 

I have an asp.net c# program that reads a sheet from an excel, and writes it out as a new sheet (also adding in one column if data from a sql server table.

The only issue is the new sheet the data is not formatted dates are with time for example and they are right adjusted which we want left adj. etc.

how does one format ? is it here?

                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();

解决方案

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

这篇关于如何格式化细胞Excel工作表编程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！