Export Information from a Form to Excel

Question

I am working to this part and this is my process so far, please help me.

 private void copyAlltoClipboard()
    {
        G2.SelectAll();

        DataObject dataObj = G2.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }

    private void btn_export_Click(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); 
    }

So this is the code, It will export the datagridview to excel ( but without Header text, and I need to export with header text also). And for all the textboxes above, I also want them to export to the Excel, How can I do it?

I forgot to mention, these textboxes and readonly and is called from another form from data table, not for input.

score 1 · Answer 1 · answered May 12 '17 at 04:08

1

For Datagridview Data with header text you need to change ClipboardCopyMode of datagridview

 dgv.ClipboardCopyMode =DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;

answered May 12 '17 at 04:08

Azar Shaikh

445
9
26

score 1 · Answer 2 · edited May 23 '17 at 12:34

In addition to the original where you got your code from: Export the dataGridView to Excel with all the cells format

You need to make another HTML Table for the Master Fields and combine it with a HTML Table of the DataGridView:

private string ConvertMasterFieldsToHTMLTable()
{
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("<table border='1' cellpadding='0' cellspacing='0'>");

    sb.AppendLine("<tr><td>");
    sb.AppendLine("Purchaser: ");
    sb.AppendLine("</td><td>");
    sb.AppendLine(textBox1.Text);
    sb.AppendLine("</td></tr>");

    sb.AppendLine("<tr><td>");
    sb.AppendLine("Date: ");
    sb.AppendLine("</td><td>");
    sb.AppendLine(dateTimePicker1.Value.ToString());
    sb.AppendLine("</td></tr>");

    sb.AppendLine("<tr><td>");
    sb.AppendLine("PR: ");
    sb.AppendLine("</td><td>");
    sb.AppendLine(comboBox1.Text.ToString());
    sb.AppendLine("</td></tr>");
    sb.AppendLine("</table>");
    sb.AppendLine("<br>");
    return sb.ToString();
}
private void button1_Click(object sender, EventArgs e)
{
    string fieldToHTMLTable = ConvertMasterFieldsToHTMLTable();
    string dgvToHTMLTable = ConvertDataGridViewToHTMLWithFormatting(dgv);

    //Strip the enclosing <HTML><body> tags and wrap them around both HTML Tables
    dgvToHTMLTable = dgvToHTMLTable.Replace("<html><body><center>", string.Empty);
    dgvToHTMLTable = dgvToHTMLTable.Replace("</center></body></html>", string.Empty);

    Clipboard.SetText("<html><body><center>"+ fieldToHTMLTable + dgvToHTMLTable + "</center></body></html>");
}

`string dgvToHTMLTable = ConvertDataGridViewToHTMLWithFormatting(dgv);` sir, where is this coming from? — Serenade, May 12 '17 at 04:20
See the other answer I link to *(the question has the exact same code in your question)*, you need to have a DataGridView control on the form named `dgv` — Jeremy Thompson, May 12 '17 at 04:22
An obvious optimization is using `TrimStart` `TrimEnd` instead of `Replace`, I'll try and make a GitHub project out of this... — Jeremy Thompson, May 13 '17 at 04:40

score 0 · Answer 3 · answered May 12 '17 at 04:11

I don't know whether its proper solution but it works.

Just add following function (Don't forgot to replace string values with your corresponding variables). If you want to move data to other cells or rows just change tabs and newlines accordingly.

private void PrefixOtherDataToClipboard()
{
    StringBuilder str = new StringBuilder();

    //Replace appended strings with your corresponding variables
    str.Append("Purchaser\t" + "P1"); str.AppendLine("\tReject Reason\t" + "bla bla bla..");
    str.AppendLine("Date\t" + DateTime.Now);
    str.AppendLine("PR#\t" + "23432");
    str.AppendLine("Total Values\t" + "4234");
    str.AppendLine("Status\t" + "Waiting");


    str.AppendLine(Clipboard.GetText());

    Clipboard.SetText(str.ToString());            
}

Now call this function in copyAlltoClipboard() after copying your grid data to clipboard

private void copyAlltoClipboard()
{
    G2.SelectAll();

    DataObject dataObj = G2.GetClipboardContent();
    if (dataObj != null)
    {
        Clipboard.SetDataObject(dataObj);
        PrefixOtherDataToClipboard();
    }
}

Note:

If you have any formatting set to your grid view cells it won't get copied to excel as I am reading and writing from/to clipboard in the form of text(String).

aww I forgot to mention, these textboxes and readonly and is called from another form from data table, not for input. — Serenade, May 12 '17 at 04:18
@Rajeev - see the link I referenced in my answer - it shows how to keep the formatting and export to excel in the fastest possible way. — Jeremy Thompson, May 12 '17 at 04:20

Export Information from a Form to Excel

3 Answers3