如何更改Excel表单在C＃中列的格式？ [英] How to change format of a column of excel sheet in c#?

问题描述

我是动态创建一个Excel工作表，并在在某些细胞中的same.But值插入值越来越插入格式错误。
以下是我的excel表
在选定单元格的值应该已经 0002-9343 但它插入为二月-43 。这是由于在该列（列D）的一些错误的格式Excel工作表。
我需要进入数据之前改变整个列D（标题D数据2正在添加下的所有细胞）文本格式。
以下是code创建的excel表和插入数据

  Excel中=新的应用程序（）;
            excel.Visible = FALSE;
            WB =（_Workbook）（excel.Workbooks.Add（System.Reflection.Missing.Value））;
            片= wb.Sheets.Add（）;
            sheet.Name =TestSheet1;
            sheet.Cells [1，A]值2 =ID。
            。sheet.Cells [1，B]值2 =姓名;
            sheet.Cells [1中，C]值2 =数据1。
            sheet.Cells [1，D]值2 =数据2。
            sheet.Cells [1，E]值2 =数据3。            的for（int i = 0;我小于10;我++）
            {
                    ID = I;
                    结果=对象;
                    数据= JsonConvert.DeserializeObject（结果）;        名=（数据！= NULL）？ data.name：的String.Empty;
                    DATA1 =（数据！= NULL）？ data.data1：的String.Empty;
                    数据2 =（数据！= NULL）？ data.data2：的String.Empty;
                    DATA3 =（数据！= NULL）？ data.data3：的String.Empty;                    。sheet.Cells [I + 2，] =值2名;
                    sheet.Cells [I + 2，B]值2 = DATA1。
                    sheet.Cells [I + 2，C] =值2 DATA2。
                    sheet.Cells第[i + 2，D]值2 = DATA3。
            }
            字符串ExcelPath = SOME_PATH;
            wb.SaveAsExcelPath，XlFileFormat.xlWorkbookNormal，NULL，NULL，假的，假的，XlSaveAsAccessMode.xlShared，假的，假的，NULL，NULL，NULL）;
            wb.Close（真）;
            excel.Quit（）;
 

现在，在循环之前我需要改变细胞的格式下列-D为文本格式。
如何做到从code同样在C＃？

解决方案

  

现在，在循环之前我需要改变细胞的格式下列-D为文本格式。如何做到从code同样在C＃？

要改变整列使用的格式的

  // 4是山口ð
sheet.Cells [1，4] .EntireColumn.NumberFormat =@;
 

记住要格式化上校d。您试图写入之前。使用 .EntireColumn 将确保你不会有单独修改Excel单元格的格式：）

I am creating an excel sheet dynamically and inserting values in to the same.But value in some cells are getting inserted in the wrong format. Following is my excel sheet value in the selected cell should have been 0002-9343 but it is inserted as Feb-43.This due to some wrong format for that column(column-D) in the excel sheet. I need to change the whole column-D (all cells comming under heading D "data2") to 'Text Format' before entering the data. Following is the code for creating the excel sheet and inserting data

excel = new Application();
            excel.Visible = false;
            wb = (_Workbook)(excel.Workbooks.Add(System.Reflection.Missing.Value));
            sheet = wb.Sheets.Add();
            sheet.Name = "TestSheet1";
            sheet.Cells[1, "A"].Value2 = "Id";
            sheet.Cells[1, "B"].Value2 = "Name";
            sheet.Cells[1, "C"].Value2 = "Data1";
            sheet.Cells[1, "D"].Value2 = "Data2";
            sheet.Cells[1, "E"].Value2 = "Data3";

            for (int i = 0; i < 10; i++)
            {
                    id = i;
                    result = object;
                    data = JsonConvert.DeserializeObject(result);

        name  = (data != null) ? data.name : string.Empty;
                    data1 = (data != null) ? data.data1 : string.Empty;
                    data2 = (data != null) ? data.data2 : string.Empty;
                    data3 = (data != null) ? data.data3 : string.Empty;

                    sheet.Cells[i + 2, "A"].Value2 = name;  
                    sheet.Cells[i + 2, "B"].Value2 = data1; 
                    sheet.Cells[i + 2, "C"].Value2 = data2; 
                    sheet.Cells[i + 2, "D"].Value2 = data3;
            }
            string ExcelPath = Some_path;
            wb.SaveAsExcelPath,XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlShared, false, false, null, null, null);
            wb.Close(true);
            excel.Quit();

Now ,before the loop I need to change the format of cells under Column-D to Text Format. How to do the same from code in c#?

解决方案

Now ,before the loop I need to change the format of cells under Column-D to Text Format. How to do the same from code in c#?

To change the format of the entire column use this

// 4 is for Col D
sheet.Cells[1, 4].EntireColumn.NumberFormat = "@";

Remember to format Col D before you attempt writing to it. Using .EntireColumn will ensure that you will not have to change the format of the Excel Cells individually :)

这篇关于如何更改Excel表单在C＃中列的格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！