无法从Excel工作表中以正确的格式打印日期 [英] Not able to print the date in correct format from an excel sheet

问题描述

大家好， 

我正在尝试从Excel第三列和第六列中获取正确格式的日期 但我无法做到。

日期应打印为2016年12月31日，但其打印方式如4072。

这里是我正在使用的代码： -

                                                                      
                                 

使用System;

使用System.Linq;

使用System.Runtime.InteropServices;

使用Excel = Microsoft .Office.Interop.Excel;    
$


名称空间Sandbox

{

   公共类GetExcelFile

    {

        public static void Main（）

        {

$
            Excel.Application xlApp = new Excel.Application（）;

            Excel.Workbooks xlWorkbookS = xlApp.Workbooks;

            Excel.Workbook xlWorkbook = xlWorkbookS.Open（@" C：\ Users \ Juhi_Sharma\Desktop\CountryHolidaylist.xlsx"）;



            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets [1];

            Excel.Range xlRange = xlWorksheet.UsedRange;



            int rowCount = xlRange.Rows.Count;

            int colCount = xlRange.Columns.Count;



$


            int count = 0;



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; for（int i = 1; i< = rowCount; i ++）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; var row ="" ;;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; for（int j = 1; j< = colCount; j ++）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {

$


$
  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（xlRange.Cells [i，j]！= null&& xlRange.Cells [i，j] .Value2！= null）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {

$
  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; row = string.Format（" {0}  {1} "，row，xlRange.Cells [i，j] .Value2.ToString（））;





  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; count = i;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }


  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Console.WriteLine（row）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Console.WriteLine（" Count：" + count）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Console.ReadLine（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; //清理

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; GC.Collect（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; GC.WaitForPendingFinalizers（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Marshal.ReleaseComObject（xlRange）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Marshal.ReleaseComObject（xlWorksheet）; 

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; xlWorkbook.Close（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Marshal.ReleaseComObject（xlWorkbook）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Marshal.ReleaseComObject（xlWorkbookS）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; xlApp.Quit（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; Marshal.ReleaseComObject（xlApp）;

  &NBSP; &NBSP; &NBSP; }¥b $ b  &NBSP; } b $ b}

如果有人可以帮助解决for循环中的打印逻辑。

谢谢

解决方案

你好juhi sharma，

如果日期时间在工作簿中格式正确，您可以直接获取单元格的文本而不是值。

例如 

 row = string.Format（" {0} {1}"，row，xlRange.Cells [i，j] .Text.ToString（））; 

如果是日期时间格式不正确，您需要尝试将值转换为日期时间对象，然后将其格式化为您想要的格式。

例如 

< pre class ="prettyprint"> row = string.Format（" {0} {1}"，row，String.Format（" {0：MM / dd / yyyy}"，DateTime.FromOADate（xlRange。单元格[i，j] .Value2）））;

最好的问候，

Terry

hi everyone, 

i'm trying to get the dates in correct format from my third column and 6th column of an excel sheet  but i'm unable to do it.

date should be printed as 31/12/2016 but its printing like 4072.

here is the code i'm using:-

                                                                                                        

using System;
using System.Linq;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;    

namespace Sandbox
{
    public class GetExcelFile
    {
        public static void Main()
        {

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbooks xlWorkbookS = xlApp.Workbooks;
            Excel.Workbook xlWorkbook = xlWorkbookS.Open(@"C:\Users\Juhi_Sharma\Desktop\CountryHolidaylist.xlsx");

            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;



            int count = 0;

            for (int i = 1; i <= rowCount; i++)
            {
                var row = "";
                for (int j = 1; j <= colCount; j++)
                {



                    if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                    {

                        row = string.Format("{0}  {1}  ", row, xlRange.Cells[i, j].Value2.ToString());


                        count = i;
                    }

                }
                Console.WriteLine(row);
            }
            Console.WriteLine("Count: " + count);
            Console.ReadLine();
            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet); 
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);
            Marshal.ReleaseComObject(xlWorkbookS);
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
        }
    }
}

if anyone can help with the logic of printing in for loop.

thank you

解决方案

Hello juhi sharma,

If the datetime is correct format in your workbook, you could get the cell's text directly instead of value.

Such as 

row = string.Format("{0}  {1}  ", row, xlRange.Cells[i, j].Text.ToString());

If the datetime is not correct format, you need try to convert the value to a date time object and them format it as the format you want.

Such as 

row = string.Format("{0}  {1}  ", row, String.Format("{0:MM/dd/yyyy}", DateTime.FromOADate(xlRange.Cells[i, j].Value2)));

Best Regards,

Terry

这篇关于无法从Excel工作表中以正确的格式打印日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！