CODEWITHSUNDEEP
c#exceldatedatetimenpoi
Prem Prakash
Prem Prakash

Reputation: 203

After double click over excel only format is getting applied over excel in npoi in C#

I have one requirement that requirement where NPOI cell value is in format of "MM/dd/yy" and when user click on that specific cell, it should show complete value as shown into image.

When i try to do manually, it works absolutely fine but when i apply through code. It does not work.

I am attaching sample code below for reference purpose. So you can guide me for possible solution. I will really appreciate your help and support. Thanks in advance.

string value = "02/17/2017 10:10:10";
 DateTime datetime;
 SimpleDateFormat dateFormat = new SimpleDateFormat ("MM/dd/yy");
 DateTime dt = Convert.ToDateTime(value);
 var dateString = dateFormat.Format(dt, CultureInfo.InvariantCulture);
 if (DateTime.TryParseExact(dateString.ToString(), "MM/dd/yy",      CultureInfo.InvariantCulture, DateTimeStyles.None, out datetime))
 {
       cell.SetCellValue(datetime.Date);
 }
 else
 {
         cell.SetCellValue(value);
 }

//For styling
var dateTypecontentStyle = workbook.CreateCellStyle();
 dateTypecontentStyle.DataFormat = dataFormatCustom.GetFormat("MM/dd/yy");
 cell.CellStyle = dateTypecontentStyle;

Upvotes: 2

Views: 1710

Answers (3)

Avijit Ghosh
Avijit Ghosh

Reputation: 1

Please check the data type of cell.SetCellValue.

  • For currency dataformatter the data type will be double cell.SetCellValue(double).
  • For date it will be date,
  • etc.

cell.SetCellValue(String) might cause your issue.

Upvotes: 0

krlzlx
krlzlx

Reputation: 5822

Set the date format of the cell as MM/dd/yy, then set the full date in the cell. The cell should display the date as MM/dd/yy but display the full date when the cell is selected.

Try this code:

DateTime dateTest = DateTime.ParseExact("02/17/2017 10:10:10", "MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture);

using (FileStream fs = new FileStream(@"C:\temp\test.xlsx", FileMode.Create, FileAccess.Write))
{
    IWorkbook workbook = new XSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("sheet");

    IRow row = sheet.CreateRow(0);

    ICell cell = row.CreateCell(0);
    ICellStyle cellStyle = workbook.CreateCellStyle();
    cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("MM/dd/yy"); 
    cell.CellStyle = cellStyle;
    cell.SetCellValue(dateTest);

    workbook.Write(fs);
}

Upvotes: 1

Adi Mohan
Adi Mohan

Reputation: 117

Ok so from what I understand you need, this should do the trick:

Lets say you want to add the date and time to cell A1, here is how i went about it and it worked:

xl.range cellA1 = yourWorksheet.Cells[1,1];
cellA1.Formula = "=NOW()";
CellA1.Value = CellA1.Value;
CellA1.NumberFormat = "m/d/yyyy";

After this you should be left with your cell showing only the date, and inside the cell you should have the time aswell. Hope this helps.

Upvotes: 0

Related Questions