Excel interop - how to stop a number (saved as text) that is "evaluated"

Question

I was wondering if anyone ran into the following problem and had any ideas for fixing it: I export data from a C # application (.NET 3.5) to Excel (2003) via Interop. One of the columns stores a string value that looks like a numeric value. That is, this is a number that starts with 0, for example. 000123

I need the full number that needs to be saved, as it could be a serial number or something like that. Excel is not keen on this, so I thought I could get around it by setting the destination cell to a shared one. When I export to Excel, I find 123 instead of 000123.

I started the application in debug mode and from the watchlist I found (for "Range Range":

range.NumberFormat = "General"`
this.Rows[iGridRow].Cells[iGridCol].Value = "000123" '/* (datagrid is not truncating it)*/
range.Value2 = 123.0

It seems to be treated as a number, even if I set a numerical value in front of this point:

range.NumberFormat = sNumberFormat;
range = (Range)sheet.Cells[iExcelRow, iExcelCol];
range.Value2 = this.Rows[iGridRow].Cells[iGridCol].Value.ToString();

Can anybody help?

Answer 1

Add one apostrophe 'in front of the number. Excel then processes the number as a string.

Answer 2

I know this late, but maybe someone will need it in the future. This is not really for performance, but you can store it in a two-dimensional array.

object[,] Values = new object[iGrid.Rows.Count, IGrid.Columns.Count];
            for (int i = 0; i < alllogentry.Rows.Count; i++)
            {
                for (int j = 0; j < alllogentry.Columns.Count; j++)
                {
                    if (alllogentry.Rows[i].Cells[j].Value != null)
                    {
                        Values[i, j] = alllogentry.Rows[i].Cells[j].Value.ToString();
                    }
                    else
                    {
                        Values[i, j] = " ";
                    }
                }
            }

This path number contains a number and a string containing a string.

Also pass information through the volume insert so as not to iterate over a cell by cell. That was my code.

// Bulk Transfer
String MaxRow = (alllogentry.Rows.Count+6).ToString();
 String MaxColumn = ((String)(Convert.ToChar(alllogentry.Columns.Count / 26 + 64).ToString() + Convert.ToChar(alllogentry.Columns.Count % 26 + 64))).Replace('@', ' ').Trim();
String MaxCell = MaxColumn + MaxRow;

//Format
worksheet.get_Range("A1", MaxColumn + "1").Font.Bold = true;
worksheet.get_Range("A1", MaxColumn + "1").VerticalAlignment = XlVAlign.xlVAlignCenter;

// Insert Statement
    worksheet.get_Range("A7", MaxCell).Value2 = Values;

Answer 3

I am using this macro. It inserts an apostrophe in front of each value, which is numeric in each cell.

Sub Macro1()
    Dim rwIndex As Integer
    Dim colIndex As Integer
    For rwIndex = 1 To ActiveSheet.UsedRange.Rows.Count
            For colIndex = 1 To ActiveSheet.UsedRange.Columns.Count
                If IsNumeric(Cells(rwIndex, colIndex).Value) Then _
                    Cells(rwIndex, colIndex).Value = "'" _
                     & Cells(rwIndex, colIndex).Value
            Next colIndex
    Next rwIndex
End Sub

Answer 4

The correct type is not general, because general will try to guess the correct type, which in this case is numeric, but you must specify it as text so as not to truncate leading zeros.

Try using the following code:

Range cell = ActiveWorksheet.Cells[1,1];
//The @ is the indicator for Excel, that the content should be text
const string textIndicator = "@";
//Change the NumberFormat from 'General' to 'Text'
cell.NumberFormat = textIndicator;
//Set the Value
cell.Value2 = "000123";