How to change the format of excel cells using C#

Question

IHi I am trying to read flat file n to excel. I am able to generate excel file using datatable but date fields are displaying like #####. I am trying to change the format of cells but unable to that . I have added the code for reference. kindly guide me as I need to create another sheet from this generated sheet along with formulas. The funniest thing about this is I see dates as

on this sheet but if I copy this data on another sheet I can see dates fields instead of #####.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Threading.Tasks;
using System.Reflection;

namespace report
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = @"flat.txt";  //Flat file
            System.Data.DataTable table = ReadFile(path);
            Excel_FromDataTable(table);

        }
    private static System.Data.DataTable ReadFile(string path)
    {
        System.Data.DataTable table = new System.Data.DataTable("dataFromFile");
        DataColumn colu;
        for (int i = 0; i < 250; i++)
        {
            colu = new DataColumn("", System.Type.GetType("System.String"));
            colu.AllowDBNull = true;
            table.Columns.Add(colu);
        }
         using (StreamReader sr = new StreamReader(path))
        {
            string line;
            int rowsCount = 0;
            while ((line = sr.ReadLine()) != null)
            {
                string[] data = line.Split(new string[] { "|" },StringSplitOptions.None);// Separated by delimiter |
                table.Rows.Add();
                for (int i = 0; i < data.Length; i++)
                {
                    //if (data[i].Contains(""))
                    //if (data[i].Equals(""))
                    //    table.Rows[rowsCount][i] = "---";
                    //    data[i] = "   ";
                    if (!data[i].Equals(""))
                        table.Rows[rowsCount][i] = data[i];

                }
                rowsCount++;
            }
        }
        return table;

    }
     private static void Excel_FromDataTable(System.Data.DataTable dt)
    {

        //create an excel object and add to a work book....

        Application excel = new Application(); //check if you can use ApplicationClass
        Workbook workbook = excel.Application.Workbooks.Add(true);


        //add coulmn heading...
        int iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            excel.Cells[1, iCol] = c.ColumnName;
        }

        //add row
        int iRow = 0;
        foreach (DataRow r in dt.Rows)
        {
            iRow++;

            //add each row's cell data...
            iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
            }

        }
        //Globalmissing refernce for objects we are not defining...

        object missing = System.Reflection.Missing.Value;

                    //excel.get_Range("C3", iRow).NumberFormat = "mm/dd/yyyy";
        workbook.SaveAs(@"C:/report.xls", XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
        // If wanting to make Excel visible and activate the worksheet 
        excel.Visible = true;



    }
    }
  }

   Excel file is like this





Column1 Column2 Column3
AAA #########   103
D-1 17        ########
D-2   17            ########
D-3 17  ########

Answer 1

Here is a simple Method that I have written that will convert any DataTable into CSV

//Declared at the class Level 
private const string tableDelim = "|";

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
    csvData = new DataTable(defaultTableName);
    try
    {
        using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
        {
            csvReader.SetDelimiters(new string[]
            {
                //this will be a constant declared at the class level private const string tableDelim = ",";
                tableDelim 
            });
            csvReader.HasFieldsEnclosedInQuotes = true;
            string[] colFields = csvReader.ReadFields();

            foreach (string column in colFields)
            {
                DataColumn datecolumn = new DataColumn(column);
                datecolumn.AllowDBNull = true;
                csvData.Columns.Add(datecolumn);
            }

            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                //Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == string.Empty)
                    {
                        fieldData[i] = string.Empty; //fieldData[i] = null
                    }
                    //Skip rows that have any csv header information or blank rows in them
                    if (fieldData[0].Contains("Disclaimer") || string.IsNullOrEmpty(fieldData[0]))
                    {
                        continue;
                    }
                 }
                 csvData.Rows.Add(fieldData);
            }
        }
    }
    catch (Exception ex)
    {
      //write your own Exception Messaging here
    }
    return csvData;
}

Convert the .CSV File and save it as .XLS format here is a good simple reference from Stackoverflow as well Convertting ExcelFile .CSV to .XLS Format

Answer 2

The date field is showing as ###### because the date is longer than the column. Try re-sizing the columns.

sheet.Columns.AutoFit();

Also try:

sheet.Cells[row, column] = String.Format("{0:MM/dd/yyyy}", object.DateEntered);

Updated Answer:

    int iRow = 0;
    foreach (DataRow r in dt.Rows)
    {
        iRow++;

        //add each row's cell data...
        iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;

            try
            {
                DateTime date = Convert.ToDateTime(r[c.ColumnName]);
                excel.Cells[iRow + 1, iCol] = String.Format("{0:MM/dd/yyyy", date);
            }
            catch(Exception e)
            {
                excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
            }

        }

    }