Export the Datagridview to Excel with All the Cells Format

How can I export datagridview cell formatting value to excel?

You should use FormattedValue property of the cell:

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

How to export dataGridView data Instantly to Excel on button click?

I solved this by simple copy and paste method. I don't know it is the best way to do this but,for me it works good and almost instantaneously. Here is my code.

    private void copyAlltoClipboard()
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Thanks.

Export Excel Range to DataGridView maintaining cell formatting and formulas

There is no direct method to export Excel formatting to DataGridView, you may write plenty of code to translate the Excel formatting to a DataGridView. If you want to export the actual value of the formula instead of the formula itself, you can use the ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue) method.

Here is a basic example:

private void button1_Click(object sender, EventArgs e)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("Input.xlsx");

    Worksheet sheet = workbook.Worksheets[0];
        
    DataTable dt = sheet.ExportDataTable(sheet.Range, true, true);
        
    this.dataGridView1.DataSource = dt;
    this.dataGridView1.EnableHeadersVisualStyles = false;
    this.dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = sheet.Rows[0].Style.Color;
}

The input Excel:

The output DataGridView:

Converting exported value(time) from datagridview to excel

Already solve! I use this code

cellvalue = DGVResult.Rows(i).Cells(j).Value.ToString

Date.Parse(cellvalue).ToString("hh:mm tt").ToString

I use a loop catch what cell is to be formatted

Thanks for the tip!

---

Related Topics