Export Excel with DateTime format problem (edit)

Ví dụ kinh điển:

https://www.leniel.net/2009/10/npoi-with-excel-table-and-dynamic-chart.html

Lý thuyết:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

https://www.codeguru.com/columns/dotnet/using-string.format-and-data-formatting-in-c.htm

Xử lý DateTime với NPOI

https://www.c-sharpcorner.com/blogs/export-to-excel-using-npoi-dll-library

https://stackoverflow.com/questions/26529254/issue-with-writing-a-date-to-excel-file-using-npoi

https://www.c-sharpcorner.com/article/using-epplus-to-import-and-export-data-in-asp-net-core/

• NPOI
• EPPlus
• ExcelDna
• XLSXHelper
• CSVHelper

Những ứng dụng yêu cầu Import/Export Excel:

- ASP.NET web pages
- Windows applications
- Windows Forms (WinForms)
- Console applications
- Windows service applications
- ASP.NET MVC web applications

MVC 5 with Excel

Install-Package ExcelDataReader

https://techbrij.com/read-excel-xls-xlsx-asp-net-mvc-upload

Install-Package NPOI

https://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus

Mvc5 + EF6 + CRUD with Upload multiple files

https://techbrij.com/crud-file-upload-asp-net-mvc-ef-multiple

https://techbrij.com/add-edit-delete-jqgrid-asp-net-web-api

Dapper CRUD with PostgreSQL

https://techbrij.com/asp-net-core-postgresql-dapper-crud

https://techbrij.com/crud-select-insert-update-delete-web-app

Console Application with Excel

http://burnignorance.com/c-coding-tips/exporting-any-data-into-excel-file-using-console-application/

Export DataTable to Excel using EPPlus

https://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus

Export DataTable to Excel using NPOI

Import OR Reading

• ExcelDataReader
• EPPlus
• Open XML SDK

Snippet Code

Install-Package NPOI

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
 
namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
 
            dt.Columns.Add("UserId", typeof(Int32));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Email", typeof(string));
            dt.Columns.Add("Phone", typeof(string));
            dt.Columns.Add("DOB", typeof(DateTime));
 
            dt.Rows.Add(1, "Ram", "ram@techbrij.com", "111-222-3333", DateTime.Now.ToString());
            dt.Rows.Add(2, "Shyam", "shyam@techbrij.com", "159-222-1596", DateTime.Now.ToString());
            dt.Rows.Add(3, "Mohan", "mohan@techbrij.com", "456-222-4569", DateTime.Now.ToString());
            dt.Rows.Add(4, "Sohan", "sohan@techbrij.com", "789-456-3333", DateTime.Now.ToString());
            dt.Rows.Add(5, "Karan", "karan@techbrij.com", "111-222-1234", DateTime.Now.ToString());
            dt.Rows.Add(6, "Brij", "brij@techbrij.com", "111-222-3333", DateTime.Now.ToString());
 
            WriteExcelWithNPOI(dt, "xls");
            WriteExcelWithNPOI(dt, "xlsx");
        }
 
        public static void WriteExcelWithNPOI(DataTable dt, String extension)
        {
 
            IWorkbook workbook;
 
            if (extension == "xlsx")
            {
                workbook = new XSSFWorkbook();
            }
            else if (extension == "xls")
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                throw new Exception("This format is not supported");
            }
 
            ISheet sheet1 = workbook.CreateSheet("Sheet 1");
 
            //make a header row
            IRow row1 = sheet1.CreateRow(0);
 
            for (int j = 0; j < dt.Columns.Count; j++)
            {
 
                ICell cell = row1.CreateCell(j);
                String columnName = dt.Columns[j].ToString();
                cell.SetCellValue(columnName);
            }
 
            //loops through data
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet1.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
 
                    ICell cell = row.CreateCell(j);
                    String columnName = dt.Columns[j].ToString();
 
                    cell.SetCellValue(dt.Rows[i][columnName].ToString());
 
                    var style = workbook.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderLeft = BorderStyle.Thin;
                    style.BorderTop = BorderStyle.Thin;
                    style.BorderRight = BorderStyle.Thin;
 
                    if (j == 4)
                    {
                        var newDataFormat = workbook.CreateDataFormat();
                        style.DataFormat = newDataFormat.GetFormat("MM/dd/yyyy HH:mm:ss");
                    }
 
                    cell.CellStyle = style;
 
 
                    //var dateStyle = workbook.CreateCellStyle();
                    //dateStyle.DataFormat = (short)DateTimeKind.Unspecified;
                    //cell.CellStyle = dateStyle;
                }
            }
 
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                if (extension == "xlsx")
                {
                    ByteArrayToFile(@"C:\test.xlsx", exportData.ToArray());
                }
                else if(extension == "xls")
                {
                    ByteArrayToFile(@"C:\test.xls", exportData.ToArray());
                }
            }
        }
 
        public static bool ByteArrayToFile(string fileName, byte[] byteArray)
        {
            try
            {
                using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(byteArray, 0, byteArray.Length);
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception caught in process: {0}", ex);
                return false;
            }
        }
    }
}