How to set date format "dd/mm/yyyy" in excel one column with all rows using C# npoi.

See more:

C#

NPOI

Hello,

I am trying set Date format but not successful, please help me how will be set date format for one column with all rows like column "A".

My excel like a template, no data export in excel, only format will be export with excel.

I want to lock this format also.

Thanks in Advance.

Ankit Agarwal
Software Engineer

What I have tried:

public static string ExportTemplateToExcel()
        {
            string CreatedDateTime = "Start Date";
            //String file = "c:\\poitest.xlsx";
            string TempPath = "c:\\Testing\\";
            string Filename = "Test.xlsx";


            IWorkbook workbook = new XSSFWorkbook();

            ISheet sheet = workbook.CreateSheet();
            //sheet.ProtectSheet("password");
            IRow row = sheet.CreateRow(0);
            //ICell cell = row.CreateCell(0);
            //cell.SetCellValue("TEST");

            ICellStyle style1 = workbook.CreateCellStyle();

            
            IDataFormat dataFormatCustom = workbook.CreateDataFormat();
            //row2.CreateCell(2).SetCellValue(CreatedDateTime);
            style1.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy");
            var datecell = row.CreateCell(0);
            datecell.CellStyle = style1;

            datecell.CellStyle.IsLocked = false;
            datecell.SetCellValue(CreatedDateTime);
            datecell.CellStyle.IsLocked = true;
        
            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            IRow row1 = sheet1.CreateRow(0);

            string FilePath = TempPath + Filename;
            GC.Collect();
            FileStream xfile = new FileStream(FilePath, FileMode.Create, System.IO.FileAccess.Write, FileShare.None, bufferSize: 4096, useAsync: false);
            workbook.Write(xfile);
            return ("Success");

        }