How to enter the zero in front of numbers in excel?

Question

I have an application written in C# which extracts the data from Datagridview to excel. My problem is when I have a number like 010498 when I export to excel become 10498, without the zero (0) at the beginning. I want there to be zero (0). Is there any possible solution, either writing code in C # within the application or via excel?
Thanks and sorry for bad English.

In the first picture seems to datagridview and in the second picture how after export to excel. In cells A3, F3, F4 missing zero (0) at the beginning. Any Solution how i can fix it? Thanks.
Code of Export to Excel.

private void excel_toolstripmenuitem_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application excelApp = null;
        try
        {
            // instantiating the excel application class
            object misValue = System.Reflection.Missing.Value;
            excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
            currentWorksheet.Columns.ColumnWidth = 18;
            if (dg1.Rows.Count > 0)
            {
                currentWorksheet.Cells[1, 1] = DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToShortTimeString();
                int i = 1;
                foreach (DataGridViewColumn dgviewColumn in dg1.Columns)
                {
                    // Excel work sheet indexing starts with 1
                    currentWorksheet.Cells[2, i] = dgviewColumn.Name;
                    ++i;
                }
                Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "AY2");
                headerColumnRange.Font.Bold = true;
                headerColumnRange.Font.Color = 0xFF0000;
                int rowIndex = 0;
                for (rowIndex = 0; rowIndex < dg1.Rows.Count; rowIndex++)
                {
                    DataGridViewRow dgRow = dg1.Rows[rowIndex];
                    for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
                    {
                        currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
                    }
                }
                Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "AY" + (rowIndex + 1).ToString());
                fullTextRange.WrapText = true;
                fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            }

            using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
            {
                exportSaveFileDialog.Title = "Save as";
                exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xls)|*.xls";

                if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
                {
                    string fullFileName = exportSaveFileDialog.FileName;

                    currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);                      
                    currentWorkbook.Saved = true;
                    MessageBox.Show("The export was successful", "Export to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            if (excelApp != null)
            {
                excelApp.Quit();               
                excelApp = null;
            }
        }
    }

Answer 1

After spending a couple of hours, I managed to fix the issue and successfully displayed the value "099999" in Excel instead of "99999".

Here's a brief explanation on how you can achieve the same and fix the issue:

1. Instead of using the property to set the value, use the function "SetValue(...)". My mistake was using the property, and once I changed it to the function, it worked perfectly.

Avoid assigning the value like this:

_worksheet.Cell(1, 1).Value =  "099999";

Instead, assign the value using the function::

_worksheet.Cell(1, 1).SetValue("099999");

After assigning the value, set the DataType of the cell using another function:

_worksheet.Cell(1, 1).SetDataType(XLDataType.Text);

Happy coding!

Answer 2

If you are using a DataTable you need to Take another DataTable and then you need to iterate through entire cells in the datatable and prepend the cells text with space i.e with '&nbsp';.We cannot modify the rows in the same table because it will throw an exception saying "Collection was Modified".We have to take a new datatable.

Consider the following code.

    //To get the result with leading zero's in excel this code is written.
        DataTable dtUpdated=new DataTable();
        //This gives similar schema to the new datatable
        dtUpdated = dtReports.Clone();
            foreach (DataRow row in dtReports.Rows)
            {
                for (int i = 0; i < dtReports.Columns.Count; i++)
                {
                    string oldVal = row[i].ToString();
                    string newVal = "&nbsp;"+oldVal;
                    row[i] = newVal;
                }
                dtUpdated.ImportRow(row); 
            }

We can bind this updated table to datagrid so that it can be useful for Excel Conversion.

Answer 3

Way 1

currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = 
"'0" + dgRow.Cells[cellIndex].Value;

Way 2

Case 1

I believe your datagrid cell can have numbers of different lengths for example 0001234, 00123. So if you want to retain the numbers as they are in datagrid then don't set a prefixed format like 0000000. Use Way 1 or individually format the cells. One more way I can think of is to store the DG cell's value in an int variable and then use .PadLeft after taking into account the length of DG cell's value. So a number like 0000123 will have a length of 7. However in an int variable it will be stored as 123 (length - 3). All you have to do is use .PadLeft with 7-3=4 zeros and then store the result string in the excel cell.

Case 2

If you want all numbers to be stored as the same length with a format say 00000000 then use this code.

//~~> This will format Column 1 with "00000000"
currentWorksheet.Columns[1].NumberFormat = "00000000";

PS: Tested in Excel 2010 + VS 2010 Ultimate

Answer 4

   currentWorksheet.Cells[...] = dgRow.Cells[cellIndex].Value;

I'm guessing that would be the relevant line of code where you enter the number into the cell. Assign the NumberFormat property to force Excel to display the extra leading 0:

   currentWorksheet.Cells[rowIndex + 3, cellIndex + 1].NumberFormat = "000000";

Assuming you wanted "010498".

Answer 5

You can put an apostrophe before the number in Excel to keep leading zeroes.

'010498

You can still do arithmetic operations on numbers with a leading '.

If you know the length of the numbers (i.e., the numbers will always be six digits like your example), then you can also format cells with a Custom Number Format of all 0s (e.g. 000000).