Tip & How-To about Computers & Internet

How to datagridview to excel in C#

First, add this to your namespace.
using Microsoft.Office.Interop.Excel;

Then, copy the codes below and paste it into the export button click event.

Microsoft.Office.Interop.Excel.Application wapp;
Microsoft.Office.Interop.Excel.Worksheet wsheet;
Microsoft.Office.Interop.Excel.Workbook wbook;
wapp = new Microsoft.Office.Interop.Excel.Application();
wapp.Visible = false;
wbook = wapp.Workbooks.Add(true);
wsheet = (Worksheet)wbook.ActiveSheet;

if (dataGridView1.RowCount == 0)
{
mssgbox_noretrieve();
return;
}
try
{
for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
wsheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;
}

for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
{
DataGridViewRow row = this.dataGridView1.Rows[i];
for (int j = 0; j < row.Cells.Count; j++)
{
DataGridViewCell cell = row.Cells[j];
try
{
wsheet.Cells[i + 2, j + 1] = (cell.Value == null) ? "" : cell.Value.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
wapp.Visible = true;
}
catch (Exception ex1)
{
MessageBox.Show(ex1.Message);
}
wapp.UserControl = true;




That's all and enjoy...
Larrix



Posted by on

Computers & Internet Logo

Related Topics:

Related Questions:

1 Answer

I want to copy an Excel chart as a Image onto a PowerPoint Slide (Office 2007). What is the sequence of APIs to apply?


Copy worksheet data to a PowerPoint presentation
  1. In Excel, select the worksheet data that you want to copy to a PowerPoint presentation.
  2. On the Home tab, in the Clipboard group, click Copy za006044679.gif.

za010165428.gif

Keyboard shortcut You can also press CTRL+C.

  1. Click in the PowerPoint presentation where you want to paste the copied worksheet data.
  2. On the Home tab, in the Clipboard group, click Paste.

Keyboard shortcut You can also press CTRL+V.

  1. Click Paste Options za006043532.gif next to the data, and then do one of the following:
    • To use the original format of the copied data, click Keep Source Formatting.
    • To use the document theme that is applied to the PowerPoint presentation, click Use Destination Theme.
    • To paste table data that you copied as text, click Keep Text Only.

Notes

  • If you don't see the Paste Options button, you may have to turn it on. Click the Microsoft Office Button za010077102.gif, and then click PowerPoint Options. In the Advanced category, under Cut, Copy, and Paste, select the Show Paste Options buttons check box.
  • If you paste the data into a PowerPoint table, the Paste Options button is not displayed.
Tips
  • To paste the data in another format (such as a worksheet object, HTML format, bitmap, picture, or to text format) or to paste a link to the source data in Excel, on the Home tab, in the Clipboard group, click the arrow on the Paste button, and then click Paste Special. In the As list, click the format that you want to use.
    • If you want to edit the entire worksheet later, click Microsoft Office Excel Worksheet Object. Clicking Microsoft Office Excel Worksheet Object provides access to the entire worksheet in the presentation, including data that you may want to keep private. To automatically update the table in the presentation when changes are made to the source data in Excel, click Paste link. Both of these options also preserve the Excel formatting, even when the formatting features that are used are not supported in PowerPoint. If you saved the source workbook before you copied the data, you can also click Attach Hyperlink to insert a hyperlink in your presentation that directs you to the source data in Excel.
    • To paste the content as an Object Linking and Embedding (OLE) (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) PowerPoint application icon (rather than pasting the actual content), click Microsoft Office Excel Worksheet Object, and then select the Display as icon check box. You can click the icon to open the application and then view the content.
    • If you want to use HTML to copy the data to PowerPoint into a PowerPoint table, click HTML format.
    • To paste a static picture, click Device Independent Bitmap or Bitmap, or to paste a picture that can be ungrouped, click Picture (Enhanced Metafile) or Picture (Windows Metafile).
    • To paste the data as formatted or unformatted text, click Formatted Text (RTF) or Unformatted Text.
  • To paste the data as a hyperlink that directs you to the source data in Excel, click the arrow on the Paste button, and then click Paste As Hyperlink. If the workbook that contains the source data has not been saved yet, this option is unavailable.
  • If you want the range of data that you paste as a Microsoft Excel Worksheet Object into a PowerPoint presentation to expand when the corresponding data expands on the Excel worksheet, you can define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the range of data in Excel before you copy the data. You can then paste a link to the named range by using the Paste Special command. On the Home tab, in the Clipboard group, click the arrow on the Paste button, click Paste Special, and then click Paste link. This works only when you copy data that is not in table format in Excel.

Mar 09, 2011 | Individual Software Professor Teaches Word...

2 Answers

Visual Basic code to paste all items from Office Clipboard into Excel spreadsheet


This may sound like a **** solution, but sometimes SENDKEYS can come in handy for situations like this instead of messing with with more advanced solutions. Also you can try to use win32API calls

Jul 12, 2008 | Microsoft Office Standard for PC

1 Answer

Export data in excel shld yoeet through VB


When i first figured out how to pull data from SQL and put the results in an excel file i referenced these two articles....
Reading and writing excel file using VB.NET (http://www.codeproject.com/KB/vb/Work_with_Excel__VBNET_.aspx)
Get the Values From DataBase and Stored into excell Sheet (http://www.codeproject.com/KB/vb/Getvaluesfromdatabase.aspx)

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"
Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the connection.
cnt.Open(stcon)

'Open the recordset.
With rst
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly , _
ADODB.CommandTypeEnum.adCmdText)
.ActiveConnection = Nothing 'Disconnect the Recordset.
End With
'Close the connection
cnt.Close ()
Dim exp As Export = New Export()
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )
Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")
Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation
Dim i As Short

'Turn off Excel's calculation.
With xlApp
xlCalc = .Calculation
.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
End With
'Write the fieldnames.
For Each fld In rst.Fields
xlRange.Offset(0, i).Value = fld.Name
i = i + 1
Next
'Populate the range.
xlRange.Offset(1, 0).CopyFromRecordset(rst)
'Close the recordset.
rst.Close()
'Make Excel available to the user.
With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With
'Release variables from memory.
fld = Nothing
rst = Nothing
cnt = Nothing
xlRange = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

Jan 03, 2008 | Computers & Internet

Not finding what you are looking for?

338 people viewed this tip

Ask a Question

Usually answered in minutes!

Top Computers & Internet Experts

Doctor PC
Doctor PC

Level 3 Expert

7733 Answers

kakima

Level 3 Expert

102366 Answers

David Payne
David Payne

Level 3 Expert

14161 Answers

Are you a Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Loading...