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

问题描述

我正在动态地创建一个excel表，并将值插入到同一个。在某些单元格中的值被插入错误的格式。
以下是我的excel表格
所选单元格中的值应该有已经是 0002-9343 ，但是它被插入为 Feb-43 。这是因为excel表中该列（列-D）的格式错误。
在输入数据之前，我需要将整个列-D（在标题Ddata2下面的所有单元格）更改为文本格式。
以下是创建excel表和插入数据的代码

  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; （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（）; 
  

现在，在循环之前，我需要将Column-D下的单元格格式更改为文本格式。
如何使用c＃中的代码执行相同操作？

解决方案

循环我需要将Column-D下的单元格格式更改为文本格式。如何使用c＃中的代码执行相同操作？

要更改整个列的格式，请使用此

  // 4为Col D 
 sheet.Cells [1，4] .EntireColumn.NumberFormat =@; 
  

在尝试写入之前，请记住格式化Col 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 :)

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