NPOI Date Format Cell

Question

I'm using NPOI to create fixed worksheet template in Sheet1, and need data from Sheet2 that's in date format.I generate DataTable from database to set data in Sheet2. It's my code :

private DataTable getWeek()
{    
  strConn = WebConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
  conn = new OdbcConnection(strConn);
  conn.Open();
  sql = "SELECT week, sunday, saturday FROM tb_weekreferences";     
  da = new OdbcDataAdapter(sql, conn);
  dt = new DataTable();
  da.Fill(dt);
  conn.Close();        

  return dt;

}

and then export DataTable to Excel :

private int ExportDataTableToExcel(DataTable sourceTable, ISheet sheet)
    {
        IRow headerRow = sheet.CreateRow(0);
        ICell headerCell;
        ICell cell = null;
        Dictionary<String, ICellStyle> styles = CreateExcelStyles(hssfwb);

        //handling value
        int rowIdx = 1;
        foreach (DataRow row in sourceTable.Rows)
        {
            IRow dataRow = sheet.CreateRow(rowIdx);            
            foreach (DataColumn column in sourceTable.Columns)
            {
                cell = dataRow.CreateCell(column.Ordinal);                
                cell.SetCellValue(row[column].ToString());
                cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("");
                cell.CellStyle = styles["cell"]; 
            }            
            rowIdx++;
        }

        //handling header
        foreach (DataColumn column in sourceTable.Columns)
        {
            headerCell = headerRow.CreateCell(column.Ordinal);
            headerCell.SetCellValue(column.ColumnName);
            headerCell.CellStyle = styles["header"];
            sheet.AutoSizeColumn(column.Ordinal);
            sheet.SetColumnWidth(column.Ordinal, (sheet.GetColumnWidth(column.Ordinal) + (5 * 256))); // set width = autofit() + (5 * 256)
        }
        return rowIdx;
    }

In Sheet1 i use vlookup formula to get 'sunday' and 'saturday' from Sheet2. But it doesn't work, because value of week, sunday, and saturday in Sheet2 seems like string (left alignment cell). How to set cell format in date when generate data to excel ? Please give me solution for this.

Thanks.

Answer 1

First problem is this line

cell.SetCellValue(row[column].ToString());

You are converting value to string, but you probably convert it to DateTime format, so NPOI knows this is datetime.

or try if this works:

cell.SetCellValue(DateTime.Now);

Second, try first set the style for your cell and then change it DataFormat attribute, but with custom format like this:

IDataFormat dataFormatCustom = workbook.CreateDataFormat();
cell.CellStyle = styles["cell"]; 
cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyyMMdd HH:mm:ss");

which will format your DateTime value to human readable format.