Today I write an example use MS Office Interop Excel to import and export Excel file and DataGridView. It's common with many results when you search on google.
But this class is very easy to use and study for beginner. I hope you enjoy it!
For the first, you need add reference Microsoft.Office.Interop.Excel
And use this class
1: using System;
2: using System.Reflection;
3: using System.Runtime.InteropServices;
4: using System.Windows.Forms;
5: using Microsoft.Office.Interop.Excel;
6: using Application = Microsoft.Office.Interop.Excel.Application;
7:
8: namespace ImportExcel
9: {
10: public static class ExcelUtils
11: {
12: public static bool ImportExcelOnRange(string filename, DataGridView view, string from, string to)
13: {
14: try
15: {
16: var excelApp = new Application();
17: Workbook workbook = excelApp.Workbooks.Open(
18: filename, 0, true, 5, string.Empty, string.Empty, true, XlPlatform.xlWindows,
19: "\t", false, false, 0, true, 1, 0);
20: var worksheet = (Worksheet)workbook.Worksheets.Item[1];
21: //var range = worksheet.UsedRange;
22: Range range = worksheet.Range[from, to];
23:
24: for (int row = 1; row <= range.Rows.Count; row++)
25: {
26: int newRow = view.Rows.Add();
27: for (int col = 1; col <= range.Columns.Count; col++)
28: {
29: view[col - 1, newRow].Value = ((Range)range.Cells[row, col]).Value2;
30: }
31: }
32:
33: workbook.Close(true, null, null);
34: excelApp.Quit();
35:
36: ReleaseObject(worksheet);
37: ReleaseObject(workbook);
38: ReleaseObject(excelApp);
39:
40: return true;
41: }
42: catch
43: {
44: return false;
45: }
46: }
47:
48: public static bool TranferExcelOnRange
49: (string filename, DataGridView view, string from, string to)
50: {
51: try
52: {
53: Missing objOpt = Missing.Value;
54: // Start a new workbook in Excel.
55: var excelApp = new Application();
56: var workbook = (_Workbook)(excelApp.Workbooks.Add(objOpt));
57: var worksheet = (_Worksheet)(workbook.Worksheets.Item[1]);
58:
59: var objData = new Object[view.Rows.Count, view.Columns.Count];
60: for (int i = 0; i < view.Rows.Count; i++)
61: {
62: for (int j = 0; j < view.Columns.Count; j++)
63: {
64: objData[i, j] = view[j, i].Value;
65: }
66: }
67: var objRange = worksheet.Range[from, objOpt];
68: objRange = objRange.Resize[view.Rows.Count, view.Columns.Count];
69: objRange.Value = objData;
70:
71: // Save the Workbook and quit Excel.
72: workbook.SaveAs(filename, objOpt, objOpt,
73: objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange,
74: objOpt, objOpt, objOpt, objOpt);
75: workbook.Close(false, objOpt, objOpt);
76: excelApp.Quit();
77:
78: ReleaseObject(worksheet);
79: ReleaseObject(workbook);
80: ReleaseObject(excelApp);
81:
82: return false;
83: }
84: catch
85: {
86: return false;
87: }
88: }
89:
90: private static void ReleaseObject(object obj)
91: {
92: try { Marshal.ReleaseComObject(obj); }
93: finally { GC.Collect(); }
94: }
95: }
96: }
- "filename" is full path of excel file
- "view" is DataGridView to import or export.
- "from" and "to" is string address of Excel cell, for example: "A1", "C100"
Thanks,
