Follow-up problems after DataGridView is imported into Excel

自由得很无力

After I import the DataGridView data into Excel using the following method:
Because some of my field values ​​are all digits and start with 0, Excel will treat them as numbers by default, which will remove the leading 0. Can you set all Excel cell types to text? ?
SaveFileDialog saveFileDialog = new SaveFileDialog ();
            saveFileDialog.Filter = "Execl files (* .xls) | * .xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel file to";

            saveFileDialog.ShowDialog ();

            if (saveFileDialog.FileName! = "")
            {
                Stream myStream;
                myStream = saveFileDialog.OpenFile ();
                StreamWriter sw = new StreamWriter (myStream, System.Text.Encoding.GetEncoding ("gb2312"));
                string str = "";
                try
                {
                    // write title
                    for (int i = 0; i <this.sc_madanDataGridView.ColumnCount; i ++)
                    {
                        if (i> 0)
                        {
                            str + = "\t";
                        }
                        str + = sc_madanDataGridView.Columns [i] .HeaderText;
                    }

                    sw.WriteLine (str);
                    // Write content
                    for (int j = 0; j <sc_madanDataGridView.Rows.Count; j ++)
                    {
                        string tempStr = "";
                        for (int k = 0; k <sc_madanDataGridView.Columns.Count; k ++)
                        {
                            if (k> 0)
                            {
                                tempStr + = "\t";
                            }
                            tempStr + = sc_madanDataGridView.Rows [j] .Cells [k] .FormattedValue.ToString ();
                        }
                        sw.WriteLine (tempStr);
                    }
                    sw.Close ();
                    myStream.Close ();
                }
                catch
                {
                    MessageBox.Show (e.ToString ());
                }
                finally
                {
                    sw.Close ();
                    myStream.Close ();
                }

johnjjj

The format of the EXCEL file can be individually set to text. If you ca n’t refer to COM for settings due to version issues, please search online and refer to the late binding method to call Selection.NumberFormatLocal = "@" in EXCEL or you can add "@" in front of all data. I do n’t know what the effect will be, hehe ~~~

自由得很无力

Adding "@" won't work

流氓法师

Because it uses stream to read and write instead of com components, other methods will not work. Add "'" single quotes before the data to be processed.

自由得很无力

Because it uses stream to read and write, not com components


Yes, but after I added single quotes, I output it directly in Excel, which didn't work.
tempStr +="'" + sc_madanDataGridView.Rows[j].Cells[k].FormattedValue.ToString();

流氓法师

Owner, please describe the problem clearly. You can create a new excel file and see the difference between "'01234" and "01234" in a cell

流氓法师

tempStr += sc_madanDataGridView.Rows[j].Cells[k].FormattedValue.ToString();
then
sw.WriteLine(tempStr);
Write in this way, do you write a line into one cell or multiple cells?

自由得很无力

for (int k = 0; k <sc_madanDataGridView.Columns.Count; k++)
   {
      if (k> 0)
        {
            tempStr += "\t";
        }
     tempStr +="'" + sc_madanDataGridView.Rows[j].Cells[k].FormattedValue.ToString();
   }
sw.WriteLine(tempStr);

The more complete is like this. Now writing in Excel shows "'001001", when double-clicking the cell, "'" disappears

流氓法师

Isn't that the result you want?

自由得很无力

But when displaying "'001001", you need to double-click each cell to hide "'"