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  ########

do you know the column Name of the Date Fields if so why not add a conditional check within your code that formats the DateFiled or assign that fields as string representation prefixing it with `#mmddyyyy#` and appending `#` at the end of that field does this make sense..? — MethodMan, Nov 13 '14 at 16:07
also since you are parsing the data into a string array.. use the debugger to view it in the initial split and see what `data[8]` position for example and check what data[i] value is and if it's not = to string.empty or not null then format it as a string right then within the loop of the split function — MethodMan, Nov 13 '14 at 16:11
There is no column heading so can not use condition for date field. — user2897967, Nov 13 '14 at 16:18
you can use the indexed position so try that instead.. I work with .csv / splitting files on a daily basis so I know that this will work.. — MethodMan, Nov 13 '14 at 16:23
`user2897967` I posted a working version of a Method that I've created a few months back that works on converting any DataTable into CSV also notice how I use conditional checks based on string[] position for example if `fieldData[45]` has a date field then I could check and or format my final datefield assignment in that conditional check.. try it out and I am sure you will be more than satisfied.. — MethodMan, Nov 13 '14 at 16:41
When splitting, you'll need to trim the values and you may want to read up on Marshall's COM release otherwise you may have memory leaks. — Andrew Grinder, Nov 13 '14 at 17:00

Andrew Grinder · Accepted Answer · 2014-11-13T16:30:00.877

1

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];
            }

        }

    }

edited Nov 13 '14 at 16:30

answered Nov 13 '14 at 16:04

Andrew Grinder

585
5
21

Not necessarily date fields that contain negative dates can also show up as ##### even if they fit in the cell – chancea Nov 13 '14 at 16:08
`xApp.Selection.DateFormat = "MM/dd/yyyy";` Does DateFormat work on rages/selections? – Andrew Grinder Nov 13 '14 at 16:10
`Chancea` I agree but the OP stated specifically that it happens only on DateFields which is why your answer should address his issue respectfully – MethodMan Nov 13 '14 at 16:12
there are 500 rows and 250 columns.Among that column 5 columns represents data for 2 date columns and for 2 records only i can see date but remaining columns displayed like ####### – user2897967 Nov 13 '14 at 16:19
The String.Format with a date format worked for me. I just tried it out. – Andrew Grinder Nov 13 '14 at 16:20
Directly into the cell itself. – Andrew Grinder Nov 13 '14 at 16:22
1

So `excel.Cells[iRow + 1, iCol] = r[c.ColumnName];` where `r[c.ColumnName]` is. You need to check if the value is a date then use String.Format like above, otherwise don't use a String.Format. – Andrew Grinder Nov 13 '14 at 16:25
also doesn't matter how many row you have if the column positions never change then reference those columns by their Indexed position since you will already have it from the split function... have you stepped thru the code..? just try it using the debugger and you will see what I am talking about – MethodMan Nov 13 '14 at 16:27
Updated answer, although you shoulnd't ever use try catches for logic. You should create a method to check for a date then return a more appropriate answer. – Andrew Grinder Nov 13 '14 at 16:31
If the date still returns ###### then I think your data is not a correct date time or even has a date present in the data table for that field. – Andrew Grinder Nov 13 '14 at 16:32
1

I think that he can determine that with one initial step thru the debugger he will see the format also I will post a Method that I wrote a while back that you can pass any DataTable to and it will convert it into a .CSV File format.. – MethodMan Nov 13 '14 at 16:34
Thanks for help I tried the above updated answer but still it is the same. I see datable contains all dates but those dates are not displaying in excel file . – user2897967 Nov 13 '14 at 16:51
1

if I close the file and open it again in office 2013 then it will prompt a message saying file is corrupted do you want to open it any way. If I click on ok then i can see date fields. – user2897967 Nov 13 '14 at 18:22

score 1 · Answer 2 · edited May 23 '17 at 12:33

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

Excel will open a .csv file btw.. so it's the same thing basically — MethodMan, Nov 13 '14 at 16:41

How to change the format of excel cells using C#

2 Answers2