Click here to Skip to main content
15,887,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

C#
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
C#
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

Bsaed on the documentation: Number format codes[^], you need to set NumberFormat to: 00000
 
Share this answer
 
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900