DataGridView导出到Excel的三个方法 (转载)

#region DataGridView数据显示到Excel     
02./// <summary>      
03./// 打开Excel并将DataGridView控件中数据导出到Excel     
04./// </summary>      
05./// <param name="dgv">DataGridView对象 </param>      
06./// <param name="isShowExcle">是否显示Excel界面 </param>      
07./// <remarks>     
08./// add com "Microsoft Excel 11.0 Object Library"     
09./// using Excel=Microsoft.Office.Interop.Excel;     
10./// </remarks>     
11./// <returns> </returns>      
12.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)     
13.{     
14.    if (dgv.Rows.Count == 0)     
15.        return false;     
16.    //建立Excel对象      
17.    Excel.Application excel = new Excel.Application();     
18.    excel.Application.Workbooks.Add(true);     
19.    excel.Visible = isShowExcle;     
20.    //生成字段名称      
21.    for (int i = 0; i < dgv.ColumnCount; i++)     
22.    {     
23.        excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;     
24.    }     
25.    //填充数据      
26.    for (int i = 0; i < dgv.RowCount - 1; i++)     
27.    {     
28.        for (int j = 0; j < dgv.ColumnCount; j++)     
29.        {     
30.            if (dgv[j, i].ValueType == typeof(string))     
31.            {     
32.                excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();     
33.            }     
34.            else    
35.            {     
36.                excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();     
37.            }     
38.        }     
39.    }     
40.    return true;     
41.}    
42.#endregion     
43.  
44.#region DateGridView导出到csv格式的Excel     
45./// <summary>     
46./// 常用方法，列之间加/t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。     
47./// </summary>     
48./// <remarks>     
49./// using System.IO;     
50./// </remarks>     
51./// <param name="dgv"></param>     
52.private void DataGridViewToExcel(DataGridView dgv)     
53.{     
54.    SaveFileDialog dlg = new SaveFileDialog();     
55.    dlg.Filter = "Execl files (*.xls)|*.xls";     
56.    dlg.FilterIndex = 0;     
57.    dlg.RestoreDirectory = true;     
58.    dlg.CreatePrompt = true;     
59.    dlg.Title = "保存为Excel文件";     
60.    
61.    if (dlg.ShowDialog() == DialogResult.OK)     
62.    {     
63.        Stream myStream;     
64.        myStream = dlg.OpenFile();     
65.        StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));     
66.        string columnTitle = "";     
67.        try    
68.        {     
69.            //写入列标题     
70.            for (int i = 0; i < dgv.ColumnCount; i++)     
71.            {     
72.                if (i > 0)     
73.                {     
74.                    columnTitle += "/t";     
75.                }     
76.                columnTitle += dgv.Columns[i].HeaderText;     
77.            }     
78.            sw.WriteLine(columnTitle);     
79.    
80.            //写入列内容     
81.            for (int j = 0; j < dgv.Rows.Count; j++)     
82.            {     
83.                string columnValue = "";     
84.                for (int k = 0; k < dgv.Columns.Count; k++)     
85.                {     
86.                    if (k > 0)     
87.                    {     
88.                        columnValue += "/t";     
89.                    }     
90.                    if (dgv.Rows[j].Cells[k].Value == null)     
91.                        columnValue += "";     
92.                    else    
93.                        columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();     
94.                }     
95.                sw.WriteLine(columnValue);     
96.            }     
97.            sw.Close();     
98.            myStream.Close();     
99.        }     
100.        catch (Exception e)     
101.        {     
102.            MessageBox.Show(e.ToString());     
103.        }     
104.        finally    
105.        {     
106.            sw.Close();     
107.            myStream.Close();     
108.        }     
109.    }     
110.}     
111.#endregion    
112.  
113.#region DataGridView导出到Excel，有一定的判断性     
114./// <summary>      
115.///方法，导出DataGridView中的数据到Excel文件      
116./// </summary>      
117./// <remarks>     
118./// add com "Microsoft Excel 11.0 Object Library"     
119./// using Excel=Microsoft.Office.Interop.Excel;     
120./// using System.Reflection;     
121./// </remarks>     
122./// <param name= "dgv"> DataGridView </param>      
123.public static void DataGridViewToExcel(DataGridView dgv)     
124.{    
125.  
126.  
127.    #region   验证可操作性     
128.    
129.    //申明保存对话框      
130.    SaveFileDialog dlg = new SaveFileDialog();     
131.    //默然文件后缀      
132.    dlg.DefaultExt = "xls ";     
133.    //文件后缀列表      
134.    dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";     
135.    //默然路径是系统当前路径      
136.    dlg.InitialDirectory = Directory.GetCurrentDirectory();     
137.    //打开保存对话框      
138.    if (dlg.ShowDialog() == DialogResult.Cancel) return;     
139.    //返回文件路径      
140.    string fileNameString = dlg.FileName;     
141.    //验证strFileName是否为空或值无效      
142.    if (fileNameString.Trim() == " ")     
143.    { return; }     
144.    //定义表格内数据的行数和列数      
145.    int rowscount = dgv.Rows.Count;     
146.    int colscount = dgv.Columns.Count;     
147.    //行数必须大于0      
148.    if (rowscount <= 0)     
149.    {     
150.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
151.        return;     
152.    }     
153.    
154.    //列数必须大于0      
155.    if (colscount <= 0)     
156.    {     
157.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
158.        return;     
159.    }     
160.    
161.    //行数不可以大于65536      
162.    if (rowscount > 65536)     
163.    {     
164.        MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
165.        return;     
166.    }     
167.    
168.    //列数不可以大于255      
169.    if (colscount > 255)     
170.    {     
171.        MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
172.        return;     
173.    }     
174.    
175.    //验证以fileNameString命名的文件是否存在，如果存在删除它      
176.    FileInfo file = new FileInfo(fileNameString);     
177.    if (file.Exists)     
178.    {     
179.        try    
180.        {     
181.            file.Delete();     
182.        }     
183.        catch (Exception error)     
184.        {     
185.            MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);     
186.            return;     
187.        }     
188.    }    
189.    #endregion     
190.    Excel.Application objExcel = null;     
191.    Excel.Workbook objWorkbook = null;     
192.    Excel.Worksheet objsheet = null;     
193.    try    
194.    {     
195.        //申明对象      
196.        objExcel = new Microsoft.Office.Interop.Excel.Application();     
197.        objWorkbook = objExcel.Workbooks.Add(Missing.Value);     
198.        objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;     
199.        //设置EXCEL不可见      
200.        objExcel.Visible = false;     
201.    
202.        //向Excel中写入表格的表头      
203.        int displayColumnsCount = 1;     
204.        for (int i = 0; i <= dgv.ColumnCount - 1; i++)     
205.        {     
206.            if (dgv.Columns[i].Visible == true)     
207.            {     
208.                objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();     
209.                displayColumnsCount++;     
210.            }     
211.        }     
212.        //设置进度条      
213.        //tempProgressBar.Refresh();      
214.        //tempProgressBar.Visible   =   true;      
215.        //tempProgressBar.Minimum=1;      
216.        //tempProgressBar.Maximum=dgv.RowCount;      
217.        //tempProgressBar.Step=1;      
218.        //向Excel中逐行逐列写入表格中的数据      
219.        for (int row = 0; row <= dgv.RowCount - 1; row++)     
220.        {     
221.            //tempProgressBar.PerformStep();      
222.    
223.            displayColumnsCount = 1;     
224.            for (int col = 0; col < colscount; col++)     
225.            {     
226.                if (dgv.Columns[col].Visible == true)     
227.                {     
228.                    try    
229.                    {     
230.                        objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();     
231.                        displayColumnsCount++;     
232.                    }     
233.                    catch (Exception)     
234.                    {     
235.    
236.                    }     
237.    
238.                }     
239.            }     
240.        }     
241.        //隐藏进度条      
242.        //tempProgressBar.Visible   =   false;      
243.        //保存文件      
244.        objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,     
245.                Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,     
246.                Missing.Value, Missing.Value);     
247.    }     
248.    catch (Exception error)     
249.    {     
250.        MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);     
251.        return;     
252.    }     
253.    finally    
254.    {     
255.        //关闭Excel应用      
256.        if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);     
257.        if (objExcel.Workbooks != null) objExcel.Workbooks.Close();     
258.        if (objExcel != null) objExcel.Quit();     
259.    
260.        objsheet = null;     
261.        objWorkbook = null;     
262.        objExcel = null;     
263.    }     
264.    MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
265.    
266.}    
267.  
268.#endregion    
269. 
270.#region DataGridView数据显示到Excel  
271./// <summary>   
272./// 打开Excel并将DataGridView控件中数据导出到Excel  
273./// </summary>   
274./// <param name="dgv">DataGridView对象 </param>   
275./// <param name="isShowExcle">是否显示Excel界面 </param>   
276./// <remarks>  
277./// add com "Microsoft Excel 11.0 Object Library"  
278./// using Excel=Microsoft.Office.Interop.Excel;  
279./// </remarks>  
280./// <returns> </returns>   
281.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)  
282.{  
283.    if (dgv.Rows.Count == 0)  
284.        return false;  
285.    //建立Excel对象   
286.    Excel.Application excel = new Excel.Application();  
287.    excel.Application.Workbooks.Add(true);  
288.    excel.Visible = isShowExcle;  
289.    //生成字段名称   
290.    for (int i = 0; i < dgv.ColumnCount; i++)  
291.    {  
292.        excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;  
293.    }  
294.    //填充数据   
295.    for (int i = 0; i < dgv.RowCount - 1; i++)  
296.    {  
297.        for (int j = 0; j < dgv.ColumnCount; j++)  
298.        {  
299.            if (dgv[j, i].ValueType == typeof(string))  
300.            {  
301.                excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();  
302.            }  
303.            else  
304.            {  
305.                excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();  
306.            }  
307.        }  
308.    }  
309.    return true;  
310.}  
311.#endregion   
312. 
313.#region DateGridView导出到csv格式的Excel  
314./// <summary>  
315./// 常用方法，列之间加/t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。  
316./// </summary>  
317./// <remarks>  
318./// using System.IO;  
319./// </remarks>  
320./// <param name="dgv"></param>  
321.private void DataGridViewToExcel(DataGridView dgv)  
322.{  
323.    SaveFileDialog dlg = new SaveFileDialog();  
324.    dlg.Filter = "Execl files (*.xls)|*.xls";  
325.    dlg.FilterIndex = 0;  
326.    dlg.RestoreDirectory = true;  
327.    dlg.CreatePrompt = true;  
328.    dlg.Title = "保存为Excel文件";  
329.  
330.    if (dlg.ShowDialog() == DialogResult.OK)  
331.    {  
332.        Stream myStream;  
333.        myStream = dlg.OpenFile();  
334.        StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));  
335.        string columnTitle = "";  
336.        try  
337.        {  
338.            //写入列标题  
339.            for (int i = 0; i < dgv.ColumnCount; i++)  
340.            {  
341.                if (i > 0)  
342.                {  
343.                    columnTitle += "/t";  
344.                }  
345.                columnTitle += dgv.Columns[i].HeaderText;  
346.            }  
347.            sw.WriteLine(columnTitle);  
348.  
349.            //写入列内容  
350.            for (int j = 0; j < dgv.Rows.Count; j++)  
351.            {  
352.                string columnValue = "";  
353.                for (int k = 0; k < dgv.Columns.Count; k++)  
354.                {  
355.                    if (k > 0)  
356.                    {  
357.                        columnValue += "/t";  
358.                    }  
359.                    if (dgv.Rows[j].Cells[k].Value == null)  
360.                        columnValue += "";  
361.                    else  
362.                        columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();  
363.                }  
364.                sw.WriteLine(columnValue);  
365.            }  
366.            sw.Close();  
367.            myStream.Close();  
368.        }  
369.        catch (Exception e)  
370.        {  
371.            MessageBox.Show(e.ToString());  
372.        }  
373.        finally  
374.        {  
375.            sw.Close();  
376.            myStream.Close();  
377.        }  
378.    }  
379.}   
380.#endregion  
381. 
382.#region DataGridView导出到Excel，有一定的判断性  
383./// <summary>   
384.///方法，导出DataGridView中的数据到Excel文件   
385./// </summary>   
386./// <remarks>  
387./// add com "Microsoft Excel 11.0 Object Library"  
388./// using Excel=Microsoft.Office.Interop.Excel;  
389./// using System.Reflection;  
390./// </remarks>  
391./// <param name= "dgv"> DataGridView </param>   
392.public static void DataGridViewToExcel(DataGridView dgv)  
393.{  
394. 
395. 
396.    #region   验证可操作性  
397.  
398.    //申明保存对话框   
399.    SaveFileDialog dlg = new SaveFileDialog();  
400.    //默然文件后缀   
401.    dlg.DefaultExt = "xls ";  
402.    //文件后缀列表   
403.    dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";  
404.    //默然路径是系统当前路径   
405.    dlg.InitialDirectory = Directory.GetCurrentDirectory();  
406.    //打开保存对话框   
407.    if (dlg.ShowDialog() == DialogResult.Cancel) return;  
408.    //返回文件路径   
409.    string fileNameString = dlg.FileName;  
410.    //验证strFileName是否为空或值无效   
411.    if (fileNameString.Trim() == " ")  
412.    { return; }  
413.    //定义表格内数据的行数和列数   
414.    int rowscount = dgv.Rows.Count;  
415.    int colscount = dgv.Columns.Count;  
416.    //行数必须大于0   
417.    if (rowscount <= 0)  
418.    {  
419.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
420.        return;  
421.    }  
422.  
423.    //列数必须大于0   
424.    if (colscount <= 0)  
425.    {  
426.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
427.        return;  
428.    }  
429.  
430.    //行数不可以大于65536   
431.    if (rowscount > 65536)  
432.    {  
433.        MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
434.        return;  
435.    }  
436.  
437.    //列数不可以大于255   
438.    if (colscount > 255)  
439.    {  
440.        MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
441.        return;  
442.    }  
443.  
444.    //验证以fileNameString命名的文件是否存在，如果存在删除它   
445.    FileInfo file = new FileInfo(fileNameString);  
446.    if (file.Exists)  
447.    {  
448.        try  
449.        {  
450.            file.Delete();  
451.        }  
452.        catch (Exception error)  
453.        {  
454.            MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
455.            return;  
456.        }  
457.    }  
458.    #endregion  
459.    Excel.Application objExcel = null;  
460.    Excel.Workbook objWorkbook = null;  
461.    Excel.Worksheet objsheet = null;  
462.    try  
463.    {  
464.        //申明对象   
465.        objExcel = new Microsoft.Office.Interop.Excel.Application();  
466.        objWorkbook = objExcel.Workbooks.Add(Missing.Value);  
467.        objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;  
468.        //设置EXCEL不可见   
469.        objExcel.Visible = false;  
470.  
471.        //向Excel中写入表格的表头   
472.        int displayColumnsCount = 1;  
473.        for (int i = 0; i <= dgv.ColumnCount - 1; i++)  
474.        {  
475.            if (dgv.Columns[i].Visible == true)  
476.            {  
477.                objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();  
478.                displayColumnsCount++;  
479.            }  
480.        }  
481.        //设置进度条   
482.        //tempProgressBar.Refresh();   
483.        //tempProgressBar.Visible   =   true;   
484.        //tempProgressBar.Minimum=1;   
485.        //tempProgressBar.Maximum=dgv.RowCount;   
486.        //tempProgressBar.Step=1;   
487.        //向Excel中逐行逐列写入表格中的数据   
488.        for (int row = 0; row <= dgv.RowCount - 1; row++)  
489.        {  
490.            //tempProgressBar.PerformStep();   
491.  
492.            displayColumnsCount = 1;  
493.            for (int col = 0; col < colscount; col++)  
494.            {  
495.                if (dgv.Columns[col].Visible == true)  
496.                {  
497.                    try  
498.                    {  
499.                        objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();  
500.                        displayColumnsCount++;  
501.                    }  
502.                    catch (Exception)  
503.                    {  
504.  
505.                    }  
506.  
507.                }  
508.            }  
509.        }  
510.        //隐藏进度条   
511.        //tempProgressBar.Visible   =   false;   
512.        //保存文件   
513.        objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,  
514.                Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,  
515.                Missing.Value, Missing.Value);  
516.    }  
517.    catch (Exception error)  
518.    {  
519.        MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
520.        return;  
521.    }  
522.    finally  
523.    {  
524.        //关闭Excel应用   
525.        if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);  
526.        if (objExcel.Workbooks != null) objExcel.Workbooks.Close();  
527.        if (objExcel != null) objExcel.Quit();  
528.  
529.        objsheet = null;  
530.        objWorkbook = null;  
531.        objExcel = null;  
532.    }  
533.    MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
534.  
535.}  
536. 
537.#endregion  

View Code

 以测试的：

   public bool GetDatagridview(DataGridView dv, bool isShowExcel)
        {
            if (dv.Rows.Count == 0)
            {
                return false;
            }
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
              excel.Application.Workbooks.Add(true);
              excel.Visible = isShowExcel;
              for (int i = 0; i < dv.ColumnCount; i++)
              {
                  excel.Cells[1, i + 1] = dv.Columns[i].HeaderText; 
              }
              for (int i = 0; i < dv.RowCount - 1; i++)     
    {
        for (int j = 0; j < dv.ColumnCount; j++)     
        {
            if (dv[j, i].ValueType == typeof(string))     
           {
               excel.Cells[i + 2, j + 1] = "'" + dv[j, i].Value.ToString();     
            }     
            else    
            {
                excel.Cells[i + 2, j + 1] = dv[j, i].Value.ToString();     
            }     
        }     
    }     
         return true;    

        }

View Code

 

转载于:https://www.cnblogs.com/haimingkaifa/p/6069189.html