C# writing text serial numbers as dates in Excel file

Question

I wrote a parser that takes some information from Excel sheets using the Spire.xls library and then writes the information to another Excel file.

I'm running into a weird problem. For some reason the program is taking serial numbers such as

03-02281 
03-02282 
03-01975

And writing them into the Excel sheet as

3/1/2281
3/1/2282
3/1/1975

This only happens with some values.

Others such as

30-04761
03-00613
03-00614

are transcribed unchanged.

I checked in the excel file, the fields are set as text format. So they were either stored that way originally or Excel is interpreting the serial numbers to be dates. Other possibility is that it doesn't happen in the original file and the text is not automatically corrected/changed if I manually type in the correct values.

Does anyone know why this is happening and how I can tell Excel to just treat these as text and nothing else?

I though about appending a ' to them in the beginning of each value, but these have to then be read by other parsers so it's not the most convenient option.

Edit: Here's some ofthe code I use for this, hopefulyl it can give you guys an idea of where I'm going wrong.

This is the code that adds all the values:

Workbook workbook = new Workbook();
        workbook.LoadFromFile(templateExcelFileUri);
        Worksheet sheet = workbook.Worksheets[0];

        int ColumnIndex = 0; //for the datatable columns iteration
        int columnCounter = 1; //for the excel sheet columns iteration
        int ColumnsToAdd = 6; //(Seccion, seccion desc, marca, marca desc, **IdArticulo**, articulo desc)

        //get the data of the new column
        DataColumn DescriptionsDataColumn;
        //First, add the suggestions.
        for (; ColumnIndex < ColumnsToAdd; ColumnIndex++,
            columnCounter++)
        {
            sheet.InsertColumn(columnCounter);
            if(columnCounter==5)
            sheet.Columns[5].NumberFormat = "@";// the column with the serial numbers.
            DescriptionsDataColumn = AutomatController.DescriptionsTable.Columns[ColumnIndex];
            //insert the data into the new column
            sheet.InsertDataColumn(DescriptionsDataColumn, true, 2, columnCounter);
        }

And for references, the table the values of which I add:

public static void SetDescriptionsTable()
    {
        DescriptionsTable.Columns.Add("Seccion", typeof(string));
        DescriptionsTable.Columns.Add("SeccionDescripcion", typeof(string));
        DescriptionsTable.Columns.Add("Marca", typeof(string));
        DescriptionsTable.Columns.Add("MarcaDescripcion", typeof(string));
        DescriptionsTable.Columns.Add("IdArticulo", typeof(string)); //Serial numbers
        DescriptionsTable.Columns.Add("ArticuloDescripcion", typeof(string));
    }

Thanks for the edits to the format of my question and the title. I'm still a little new here and I'm learning how to do that better.

As a last resort, I add a single quote before the value, so like this: '03-02281 '03-02282 '03-01975, and Excel will treat the values literally and not try to convert them to anything else. — spojam, Dec 19 '13 at 22:22
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Dec 19 '13 at 22:23
you may need to post some code as I had no issue writing those values to excel when placing them in variables and writing them to a cell. — Sorceri, Dec 19 '13 at 22:25
The last time I dealt with this garbage I had to implement the same method that @spojam did. — NotMe, Dec 19 '13 at 22:58
@spojam I caved and went with your idea. I still don't know exactly why this happens, but at least I tried it and it seems if I parse values written as "'03-02281" the initial apostrophe is removed and doesn't make it to my variable so it shouldn't interfere with parsing. The question asks why this happens so I'm leaving it open in case anyone else can help shed light on it. Thanks! — ConnorU, Dec 19 '13 at 23:03
the issue is with the method InsertDataColumn. Everything else works fine and if I use the MS office Excel Library I cannot reproduce your issue which leaves spire.xls lib as the issue. — Sorceri, Dec 19 '13 at 23:52
@ConnorU , glad that worked for you, and as far as I know, Excel does not have an option to disable it's attempts at converting values to what it thinks they represent, I hope I am wrong on that but I find nothing to the contrary, anyone ? — spojam, Dec 20 '13 at 16:14

score 0 · Answer 1 · answered Dec 19 '13 at 22:25

The reason why some values do not map to dates is because they fall outside of the format MM-dyyyy format. for example there is no month 30 (30-02281) or day 0 (03-01975).

I think the only thing you need to do is set the format of the target column and cell prior to setting its value through the API. Sometimes cloning a column or a cell defaults the formatting to "Auto" and Excel tries to be too smart.

If you can share a bit of your code the community may be able to more accurately diagnose the problem.

I tried setting the format of the column to text before adding the values but it didn't solve the problem. I edited the question and included my code now... — ConnorU, Dec 19 '13 at 22:55

score 0 · Answer 2 · answered Dec 19 '13 at 22:27

0

You should set the columns format to general before setting the value.

answered Dec 19 '13 at 22:27

injurer001

54
5

Tried setting the format to text using suggestions found here (http://stackoverflow.com/questions/8265350/how-to-change-format-of-a-cell-to-text-using-vba) but it didn't work... – ConnorU Dec 19 '13 at 22:41

C# writing text serial numbers as dates in Excel file

2 Answers2