How can I change the long date format to short date format?

Aug 6, 2013 at 8:29 AM
Edited Aug 6, 2013 at 8:35 AM
when I export all data to excel file, how can I change the long date format to short date format?

i.e., exported date format is 4/23/2013 12:00:00 AM, good format is 4/23/2013

Code as below:
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);

        // handling header.
        foreach (DataColumn column in srcTable.Columns)
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

        // handling value.
        int rowIndex = 1;
        foreach (DataRow row in srcTable.Rows)
        {
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
            foreach (DataColumn column in srcTable.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }
            rowIndex++;
        }
Your help was greately appreciated.
Aug 9, 2013 at 6:14 PM
I have the same question. Has anyone forced the Excel export to format the date without the time?

I know that it's possible by casting the date as a string, but this solution is not ideal because it results in improper sorting.
Aug 31, 2013 at 11:19 AM
This is working well for me, using your code I added date formatting. The date is stored in Excel as the dateserial value so should behave correctly when sorting rather than pretending to be a date.
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);

            // handling header.
            foreach (DataColumn column in srcTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            //Create date format    
            ICellStyle cellDateStyle = workbook.CreateCellStyle();
            cellDateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd/mm/yyyy");

            // handling value.
            int rowIndex = 1;
            foreach (DataRow row in srcTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in srcTable.Columns)
                {
                    if (!System.DBNull.Value.Equals(row[column]) && column.DataType == typeof(DateTime))
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDateTime(row[column]));
                        dataRow.GetCell(column.Ordinal).CellStyle = cellDateStyle;
                    }
                    else
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
Sep 11, 2013 at 6:22 AM
Thanks 540YMX

it works fine for me.
Feb 16 at 5:32 AM
Edited Feb 16 at 5:33 AM
It works fine for me. But date format always shows like dd-mm-yyyy format in Excel sheet. How to change to dd/mm/yyyy. Please reply