Format Excel cell dari text ke number atau tanggal menggunakan NPOI

Tulisan ini saya tulis untuk mengingatkan saya ketika ada masalah pada report yang saya buat menggunakan NPOI. Awal mula report yang di generated terlihat baik-baik saja. namun setelah dilakukan UAT (User acceptance test) barulah ketemu jika seharusnya data type sesuai dengan yang asli. bugs ini diketahui ketika report di buka dengan menggunakan Microsoft Excel 2013, dimana data type pada excel selalu betipe "General" padahal harusnya Number/Numeric

Agar bisa lebih bermanfaat saya share seperti dibawah ini. Get Data Table adalah contoh simple. data boleh didapatkan dari daabase mana saja. bisa mysql, ms sql server atau oracle, atau dari Ms Access dan semacemnya lah.

DataTable GetTable()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Date", typeof(DateTime));
    table.Columns.Add("CCY", typeof(string));
    table.Columns.Add("Amount", typeof(decimal));

    // Here we add five DataRows.
    table.Rows.Add(1, DateTime.Now, "IDR", 45002100);
    table.Rows.Add(2, DateTime.Now, "IDR", 1999000);
    table.Rows.Add(3, DateTime.Now, "USD", 1000);
    table.Rows.Add(4, DateTime.Now, "JPY", 356789);
    table.Rows.Add(5, DateTime.Now, "IDR", 400023000);
    return table;
}

protected override XSSFWorkbook CreateReport()
{
    XSSFWorkbook workBook = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet)workBook.CreateSheet("Contoh 1");
    IFont font = sheet.Workbook.CreateFont();
    font.Boldweight = (short)FontBoldWeight.Bold;
    // set tinggi font
    font.FontHeightInPoints = 9;
    //set font yang digunakan
    font.FontName = "Verdana";
    // atur column yang akan di jadikan header 
    ICellStyle headerCellStyle = sheet.Workbook.CreateCellStyle();
    headerCellStyle.SetFont(font);
    headerCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
    headerCellStyle.FillPattern = FillPattern.SolidForeground;
    headerCellStyle.WrapText = true;
    ICell cell;
    DataTable tbl = GetTable();
    IRow headerRow = sheet.CreateRow(0);
    //buat headernya dulu
    for (int i = 0; i < tbl.Columns.Count; i++)
    {
        cell = headerRow.CreateCell(i);
        cell.CellStyle = headerCellStyle;
        cell.SetCellValue(tbl.Columns[i].ColumnName);
    }

    IDataFormat dataFormat = sheet.Workbook.CreateDataFormat();
    // format cell jadi numeric
    ICellStyle numericCellStyle = sheet.Workbook.CreateCellStyle();
    numericCellStyle.DataFormat = dataFormat.GetFormat("#,##0");
    //format cell jadi date time
    ICellStyle dateTimeCellStyle = sheet.Workbook.CreateCellStyle();
    dateTimeCellStyle.DataFormat = dataFormat.GetFormat("dd MMM yyyy HH:mm:ss");
    for (int rowIndex = 1; rowIndex < tbl.Rows.Count; rowIndex++)
    {
        IRow row = sheet.CreateRow(rowIndex);
        for (int colIndex = 0; colIndex < tbl.Columns.Count; colIndex++)
        {
            cell = row.CreateCell(colIndex);
            DataColumn col = tbl.Columns[colIndex];
            object value = tbl.Rows[rowIndex][colIndex];
            if (col.DataType == typeof(System.Decimal))
            {
                // Set Numeric cell *****************************
                cell.SetCellType(CellType.Numeric);
                cell.CellStyle = numericCellStyle;
                double currentValue;
                if (double.TryParse(string.Format("{0}", value), out currentValue))
                    cell.SetCellValue(currentValue);
                //***********************************************
            }
            else if (col.DataType == typeof(System.DateTime))
            {
                // format datetime value-nya harus datime juga
                cell.CellStyle = dateTimeCellStyle;
                DateTime dateValue;
                if (DateTime.TryParse(string.Format("{0}", value), out dateValue))
                    cell.SetCellValue(dateValue);
            }
            else
            {
                cell.SetCellValue(string.Format("{0}", value));
            }
        }
    }
    return workBook;
}

Point dari coding diatas sebetulnya terletak pada baris ini

               // Set Numeric cell *****************************
                cell.SetCellType(CellType.Numeric);
                cell.CellStyle = numericCellStyle;
                double currentValue;
                if (double.TryParse(string.Format("{0}", value), out currentValue))
                    cell.SetCellValue(currentValue);
                //***********************************************

Dimana untuk memformat cell pada ms excel dengan menggunakan NPOI, IDataFormat harus di panggil diluar looping. entah kenapa, ketika saya mencoba menggambil DataFormat didalam loop, cell tidak terformat dengan benar. ini kejadian hanya pada ms excell 2013. untuk versi dibawah ms office 20013, misalkan excel 2010, 2008 kebawah terlihat baik-baik saja. artinya cell pada excel terformat manjadi type data Numeric tanpa terkendala apapun. 

   IDataFormat dataFormat = sheet.Workbook.CreateDataFormat();
    // format cell jadi numeric
    ICellStyle numericCellStyle = sheet.Workbook.CreateCellStyle();
    numericCellStyle.DataFormat = dataFormat.GetFormat("#,##0");

penggalan panggil data format numeric NPOI

Hasil dari coding diatas adalah sebagai berikut. cell dengan sendirinya terformat menjadi datetime pada column Date dan menjadi numric pada column Amount

Salam.
Maaf saya tidak pandai merangkai kata :'(