How can I export datagridview cell formatting value to excel?

Question

I`m making windows form application.

When Click Export button, datagridview`s data export to Excel file.

I already built that code and it works well. But today I updated my code.

I add CellFormatting event of Datagridview, change file size value, and display

it to the Datagridview.

And then, I exported to excel but in excel file, still original data showend

which means, original data is 451936256 and converted data is 431MB

In excel sheet, it showed 451936256.

my code is below

   //Button click Event
    private void mtbtnExportToExcel_Click(object sender, EventArgs e)
            {
                DataGridView[] dgv = new DataGridView[] { mgrdWebApplication, mgrdContentDatabase, mgrdSiteCollections, mgrdSites, mgrdLists, mgridDocumentLibrary };
                mtProgressStatus.Spinning = true;
                ExportDataToExcel(dgv, "MigStatus");
                mtProgressStatus.Spinning = false;

            }
    //Export gridview data to excel
    private bool ExportDataToExcel(DataGridView[] dgv, string fileName)
            {
                string saveFileName = "";
                SaveFileDialog saveDialog1 = new SaveFileDialog();
                saveDialog1.DefaultExt = "xlsx";
                saveDialog1.Filter = "Excel file|*.xlsx";
                saveDialog1.FileName = fileName;
                saveDialog1.ShowDialog();
                saveFileName = saveDialog1.FileName;

                if (saveFileName.IndexOf(":") < 0)
                    return false;

                Excel.Application xlApp = new Excel.Application();

                if (xlApp == null) 
                {
                    MessageBox.Show("Can`t create Excel");
                    return false;
                }

                Excel.Workbooks workBooks = xlApp.Workbooks;
                Excel.Workbook workBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

                try
                {
                    for (int index = 0; index < dgv.Length; index++)
                    {
                        for (int i = 0; i < dgv[index].ColumnCount; i++)
                        {
                            if (dgv[index].Columns[i].Visible)
                                workSheet.Cells[1, i + 1] = dgv[index].Columns[i].HeaderText;
                        }

                        for (int r = 0; r < dgv[index].Rows.Count; r++)
                        {
                            for (int i = 0; i < dgv[index].ColumnCount; i++)
                            {
                                if (dgv[index].Columns[i].Visible)
                                    workSheet.Cells[r + 2, i + 1] = dgv[index].Rows[r].Cells[i].Value.ToString();
                            }
                            Application.DoEvents();
                        }
                        ((Excel.Range)workSheet.Rows[1, Type.Missing]).Font.Bold = true;
                        workSheet.Columns.EntireColumn.AutoFit();

                        if (index < dgv.Length - 1)
                        {
                            workSheet = (Excel.Worksheet)workBook.Worksheets.Add();
                        }
                    }
                }
                catch(Exception ex)
                {
                    //LogWrite logWrites = new LogWrite();
                    writeLog.LogsWrite(ex.ToString());
                }

                if (saveFileName != "")
                {
                    try
                    {
                        workBook.Saved = true;
                        workBook.SaveCopyAs(saveFileName);
                    }
                    catch(Exception ex)
                    {
                        MessageBox.Show("Error, file is already opened!\n" + ex.Message);
                    }
                }
                xlApp.Quit();
                GC.Collect();
                MessageBox.Show("File : " + fileName + ".xls saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return true;
            }
        //CellFormatting Event
        private void mgrdContentDatabase_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if(this.mgrdContentDatabase.Columns[e.ColumnIndex].HeaderText== "Size(GB)")
            {
                if (e.Value != null)
                {
                    CovertFileSize(e);
                }
            }
        }
        //convert to file size
        private void CovertFileSize(DataGridViewCellFormattingEventArgs formatting)
        {
            if (formatting.Value != null)
            {
                try
                {
                    long bytes;
                    bytes = Convert.ToInt64(formatting.Value);
                    string size = "0 Bytes";

                    //GB
                    if (bytes >= 1073741824.0)
                        size = String.Format("{0:##.##}", bytes / 1073741824.0) + " GB";
                    //MB
                    else if (bytes >= 1048576.0)
                        size = String.Format("{0:##.##}", bytes / 1048576.0) + " MB";
                    //KB
                    else if (bytes >= 1024.0)
                        size = String.Format("{0:##.##}", bytes / 1024.0) + " KB";
                    //Bytes
                    else if (bytes > 0 && bytes < 1024.0)
                        size = bytes.ToString() + " Bytes";

                    formatting.Value = size;
                    formatting.FormattingApplied = true;
                }
                catch(FormatException)
                {
                    formatting.FormattingApplied = false;
                }
            }

        }

I want to export converted data to excel.

Please help me how can I fix or add my code..

thanks

Answer 1

You should use FormattedValue property of the cell:

string value = string.Format("{0}" , dataGridView1.Rows[r].Cells[i].FormattedValue);