How to Get Date Data Information in Excel with C#

In order to present data information more exactly, we often need to set data value type after exporting to Excel.

Actually, MS Excel provides us with several value types, including number, date, currency, text and so on. Even, we can customize value types according to requirements. In this post, I want to introduce way to get date information with C#.

Date information often appears in many files, for example, birthday, production date information and so on. Also, sometimes, we need to display present date that readers open this file. So, this date is not a fixed time but changed.

In my example, I enter a date in Excel. And I will separate it into three parts: month, day and year and display the parts in other cells. Then use one formula to show present date.

Date

Note: Spire.XLS is used. Therefore, if you want to use the following code, please remember to add its DLL file as reference.

Step 1: load file which I want to get data date from computer and initialize current worksheet.

Step 2: get date information of Cell A2.

Step 3: get present date. This date is changed according to when readers open the file.

Step 4: Set format for the information which I got right now.

Step 5: Save and Launch file.

Coding:

using System;

using System.Drawing;

using Spire.Xls;

namespace DataTime

{

    class Time

    {

        static void Main(string[] args)

        {

            //Get File and Initialize Worksheet

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”D:\work\My Documents\Date.xlsx”);

            Worksheet sheet = workbook.Worksheets[0];

            //Get Month/Day/Year of Cell A2

            sheet.Range[“A5”].Formula = “MONTH(A2)”;

            sheet.Range[“B5”].Formula = “DAY(A2)”;

            sheet.Range[“C5”].Formula = “YEAR(A2)”;

            //Get Present Date

            sheet.Range[“E5”].DateTimeValue = DateTime.Now;

            //Set Format

            sheet.Range[“A5:E5”].Style.Font.FontName = “Calibri”;

            sheet.Range[“A5:E5”].Style.Font.Size = 11;

            sheet.Range[“A5:E5”].Style.Font.IsBold = true;

            sheet.Range[“A5:E5”].Style.Font.Color = Color.DarkCyan;

            //Auto Fit Column and Set Alignment

            sheet.AutoFitColumn(5);

            sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;

            //Save and Launch File

            workbook.SaveToFile(“DataTime.xlsx”, ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(workbook.FileName);

        }

    }

}

Result:

————————————————————————————————–

Click Here to learn more about Spire.XLS

Click Here to download

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s