How to export a DataGridView Excel file in WinForm

How to export a DataGridView Excel file in WinForm

1. note: The export efficiency is not very high, but at least it can be received. according to the many efficient Excel export methods on the Internet, the export speed can be achieved at that time, but the defect is that cell Formatting cannot be performed well, for example, if the value in the "pinyin code" field "000000000012120" is displayed after export, it is quite depressing! O (answer _ Answer) o .......
2. Add an Excel reference
 
3. Implementation Code
/// <Summary>
/// DataGridView export Excel
/// </Summary>
/// <Param name = "strCaption"> title in an Excel file </param>
/// <Param name = "myDGV"> DataGridView Control </param>
/// <Returns> 0: Successful; 1: no records in the dview; 2: Excel cannot be started; 9999: Exception error </returns>
Private int ExportExcel (string strCaption, DataGridView myDGV)
{
Int result = 9999;
// Column index, row index, total number of columns, total number of rows
Int ColIndex = 0;
Int RowIndex = 0;
Int ColCount = myDGV. ColumnCount;
Int RowCount = myDGV. RowCount;

If (myDGV. RowCount = 0)
{
Result = 1;
}

// Create an Excel Object
Microsoft. Office. Interop. Excel. Application xlApp = new Microsoft. Office. Interop. Excel. ApplicationClass ();
If (xlApp = null)
{
Result = 2;
}
Try
{
// Create an Excel worksheet
Microsoft. Office. Interop. Excel. Workbook xlBook = xlApp. Workbooks. Add (true );
Microsoft. Office. Interop. Excel. Worksheet xlSheet = (Microsoft. Office. Interop. Excel. Worksheet) xlBook. Worksheets [1];
// Set the title
Microsoft. office. interop. excel. range range = xlSheet. get_Range (xlApp. cells [1, 1], xlApp. cells [1, ColCount]); // The number of Cells in the title is the same as the number of columns in the DataGridView.
Range. MergeCells = true;
XlApp. ActiveCell. FormulaR1C1 = strCaption;
XlApp. ActiveCell. Font. Size = 20;
XlApp. ActiveCell. Font. Bold = true;
XlApp. ActiveCell. HorizontalAlignment = Microsoft. Office. Interop. Excel. Constants. xlCenter;
// Create cache data
Object [,] objData = new object [RowCount + 1, ColCount];
// Obtain the column title
Foreach (DataGridViewColumn col in myDGV. Columns)
{
ObjData [RowIndex, ColIndex ++] = col. HeaderText;
}
// Obtain data
For (RowIndex = 1; RowIndex <RowCount; RowIndex ++)
{
For (ColIndex = 0; ColIndex <ColCount; ColIndex ++)
{
If (myDGV [ColIndex, RowIndex-1]. ValueType = typeof (string)
| MyDGV [ColIndex, RowIndex-1]. valueType = typeof (DateTime) // verify the type in the DataGridView cell. If the type is string or DataTime, add "" Before the content when it is cached "";
{
ObjData [RowIndex, ColIndex] = "" + myDGV [ColIndex, RowIndex-1]. Value;
}
Else
{
ObjData [RowIndex, ColIndex] = myDGV [ColIndex, RowIndex-1]. Value;
}
}
System. Windows. Forms. Application. DoEvents ();
}
// Write to Excel
Range = xlSheet. get_Range (xlApp. Cells [2, 1], xlApp. Cells [RowCount, ColCount]);
Range. Value2 = objData;

// Save
XlBook. Saved = true;
XlBook. SaveCopyAs ("C: // test" + DateTime. Now. ToString ("yyyyMMddHHmmss") + ". xls ");
// Return Value
Result = 0;
}
Catch (Exception err)
{
Result = 9999;
}
Finally
{
XlApp. Quit ();
GC. Collect (); // forcibly Recycle
}
Return result;
}
4. Call the method (onClick event of the "generate Excel file" button)
Private void button4_Click (object sender, EventArgs e)
{
Int result = this. ExportExcel ("test", this. dataGridView1); // this. dataGridView1: DataGridView Control
MessageBox. Show (result. ToString ());
}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Reference Microsoft. Office. Interop. Excel Problems

08-06-11 14: 41 View: 5700

Operation background: Asp.net Excel operation

Problems: Add reference locally (com): Microsoft Office 11.0 Object Library, and write the program to debug normally. when deployed to the server, an exception occurs. Excel. Application is not an Object.

Initial Diagnosis: The Excel component is not installed on the server.

Step 1: try to solve the problem: Install Excel and other Office components on the server to test the program: failed!

Step 2 try to solve the problem: Generate Interop. Excel. dll from excel.exe and reference the Dll with sdk. The program is compiled successfully. The test program is successful!

Cause: The locally referenced com will not generate dll files in the bin directory of the program, but the program is searching for dll files based on the path. when deployed on the server, if Excel and other dll files are inconsistent with the local path, an exception will be thrown, and the defined Excel Object certainly does not exist.

Method:

1. How to generate Interop. Excel. dll?

Go to the bindirectory of your visual Studio sdkand find the tlbimp.exe file. If not, install this file on a CD. For details, see MSDN.
Run the command line (cmd) to enter the bin directory and run TlbImp/out: Interop. Excel. dll officeinstallation directory +excel.exe.

At this time, the following error may be reported: TlbImp error: Unable to locate input type library: 'C:/program files/mcrosoft offi.
Ce/office/EXCEL. EXE'
Copy the notebook to the bin directory and run TlbImp/out: Interop. Excel. dll Excel.exe directly. The prompt "Type library imported to Interop. Excel. dll path" is displayed"

Find the Interop. Excel. dll file in the bin directory. Reference it in your visual studio.

2. What if it is excel2000 or excel2002?

If excel2000, change excel.exe to Excel9.olb.
Excel2002 same as 2003

3. Parameters of Reference Components of different versions are as follows:

File/version	Interop. Excel. dll	Interop. Office. dll	Interop. VBIDE. dll	Add reference/COM components
2000	V1.3.0.0	V2.1.0.0	V5.3.0.0	Microsoft Excel 9.0 Object Library (EXCEL9.OLB)
2002 (XP)	V1.4.0.0	V2.2.0.0	V5.3.0.0	Microsoft Excel 10.0 Object Library (Excel. EXE file)
2003	V1.5.0.0	V2.3.0.0	V5.3.0.0	Microsoft Excel 11.0 Object Library (Excel. EXE file)