Click here to Skip to main content
14,599,021 members
Rate this:
Please Sign up or sign in to vote.
See more:
if have same Data in My DataGridView form My DataBase and When i Export Data in Excel then this Problem i have

in DataGridView DataShow in Columns[1]
00001
00002
00005
00010
00015
00100


Code For Load DGV

private void LoanData()
        {
            String connstring = ConfigurationManager.ConnectionStrings["Data"].ConnectionString;
            using (OleDbConnection con = new OleDbConnection(connstring))
            {
                con.Open();
                using (OleDbDataAdapter da = new OleDbDataAdapter("select Sr, RequestNo, Date, Status from RequestData where RequestNo = @RequestNo", con))
                {
                    da.SelectCommand.Parameters.AddWithValue("@RequestNo", txtRequestNo.Text);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[0].Width = 50;
                    dataGridView1.Columns[0].HeaderText = "Sr.No";
                    dataGridView1.Columns[1].Width = 85;
                    dataGridView1.Columns[1].HeaderText = "Request No.";
                    dataGridView1.Columns[2].Width = 110;
                    dataGridView1.Columns[2].HeaderText = "Date";
                    dataGridView1.Columns[2].DefaultCellStyle.Format = "dd/MM/yyyy";
                    dataGridView1.Columns[3].Width = 65;
                    dataGridView1.Columns[3].HeaderText = "Request Status";
                }
            }
        }


and for Export use this Code
private void button1_Click(object sender, EventArgs e)
        {
            DateTime fullDate = DateTime.Now;

            string folderPath = "C:\\Report\\";
            if (!Directory.Exists(folderPath))
            {
                Directory.CreateDirectory(folderPath);
            }

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            

            for (int i = 0; i < dataGridView1.Columns.Count; i++)
            {
                
                xlWorkSheet.Cells[10, i + 1] = dataGridView1.Columns[i].HeaderText;
            }

            
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 1 + 10, j + 1] = cell.Value;
                    
                }
            }
            

            xlWorkSheet.Range["C2:C19"].NumberFormat = "dd/MM/yyy";
            xlWorkSheet.Range["B2:B19"].NumberFormat = "@";

           
            xlApp.Columns.AutoFit();

            string filename = string.Format("Report_ {0}.xls", DateTime.Now.ToString("yyyyMMMdd"));

            string Savepath = Path.Combine(folderPath, filename);
                



            xlWorkBook.SaveAs(Savepath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            var ConFirmResult = MessageBox.Show("Export Complected in " + Savepath + "" + Environment.NewLine + "Do you Want open this File ", "File Export Successfully", MessageBoxButtons.YesNo);
            if (ConFirmResult == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(Savepath);
            }
            else
            {

            }

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }


What I have tried:

but When i Export Data in Excel
data Export

1
2
5
10
15
100


i use code
xlWorkSheet.Range["B8:B19"].NumberFormat = "@";
Posted
Updated 15-Jun-20 21:48pm
v3
Comments
OriginalGriff 16-Jun-20 3:04am
   
This is not a good question - we cannot work out from that little what you are trying to do.

Start by showing us the code you use to load the DGV, the code you use to export to Excel, and some idea of what your DB fields look like would probably help. Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with, And there are many different ways to do these things, all of which can give different results!

Use the "Improve question" widget to edit your question and provide better information.
Amar chand123 16-Jun-20 3:31am
   
i Improved My Question

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Bsaed on the documentation: Number format codes[^], you need to set NumberFormat to: 00000
   
Comments
Amar chand123 16-Jun-20 3:39am
   
Thank You
Maciej Los 16-Jun-20 3:46am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100