C#DATAGRIDVIEW 导出EXCEL

首先需要引入程序集:Microsoft.Office.Interop.Excel  (如果没有引用过的需要右键添加引用再搜索就行了)

 

实现的方法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/// <summary>
   ///
   /// </summary>
   /// <param name="fileName">文件路径</param>
   /// <param name="myDGV">控件DataGridView</param>
   private void ExportExcels(string fileName, DataGridView myDGV)
   {
       string saveFileName = "";
       SaveFileDialog saveDialog = new SaveFileDialog();
       saveDialog.DefaultExt = "xls";
       saveDialog.Filter = "Excel文件|*.xls";
       saveDialog.FileName = fileName;
       saveDialog.ShowDialog();
       saveFileName = saveDialog.FileName;
       if (saveFileName.IndexOf(":") < 0) return//被点了取消
       Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
       if (xlApp == null)
       {
           MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
           return;
       }
       Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
       Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
       Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
       //写入标题
       for (int i = 0; i < myDGV.ColumnCount; i++)
       {
           worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
       }
       //写入数值
       for (int r = 0; r < myDGV.Rows.Count; r++)
       {
           for (int i = 0; i < myDGV.ColumnCount; i++)
           {
               worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
           }
           System.Windows.Forms.Application.DoEvents();
       }
       worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
       if (saveFileName != "")
       {
           try
           {
               workbook.Saved = true;
               workbook.SaveCopyAs(saveFileName);
           }
           catch (Exception ex)
           {
               MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
           }
       }
       xlApp.Quit();
       GC.Collect();//强行销毁
       MessageBox.Show("文件: " + fileName + ".xls 保存成功""信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
   }

 点击按钮调用

1
2
3
4
5
private void button1_Click(object sender, EventArgs e)
{
    string a = "D:" "\\KKHMD.xls";
    ExportExcels(a, dataGridView1);
}

  完成截图




以下VB代码实现从datagridview导出数据到EXCEL,并以时间作为excel的文件名

Try

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
    Dim xlApp, xlBook, xlSheet As Object
    xlApp = CreateObject("Excel.Application")
    xlBook = xlApp.Workbooks.Add
    xlSheet = xlBook.Worksheets()
    '使工作表不可见
    xlApp.Visible = False
 
    '导出DataGridView中的标题 
    Dim Cols As Integer
    For Cols = 1 To testHistoryDgv.Columns.Count
        xlApp.Cells(1, Cols) = testHistoryDgv.Columns(Cols - 1).HeaderText
    Next
 
    '逐行导出DataGridView中的数据 
    Dim i As Integer
 
    For i = 0 To testHistoryDgv.RowCount - 1
        Dim j As Integer
        For j = 0 To testHistoryDgv.ColumnCount - 1
            '判断DataGridview中的数据是否导出完毕 
            If Me.testHistoryDgv(j, i).Value Is System.DBNull.Value Then
                xlApp.Cells(i = 2, j = 1) = ""
            Else
                xlApp.Cells(i + 2, j + 1) = testHistoryDgv(j, i).Value.ToString
            End If
        Next
    Next
    '定义文件名-取系统时间作为excel文件名
    Dim fileName As String = Format(Now, "yyyyMMdd") + Format(Now, "HH:mm:ss").Replace(":", "")
    ’excelPath是文件路径,通过配置文件配置
    Dim flg As Boolean = xlBook.SaveAs(excelPath & fileName & ".xlsx")
    If flg = True Then
        MessageBox.Show("操作成功", "XXXX", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    End If
    '关闭Excel线程
    xlApp.Quit()
    xlApp = Nothing
    xlBook = Nothing
    xlSheet = Nothing
Catch ex As Exception
    '异常捕捉
    LogUtil.err(ex.ToString)
    MessageBox.Show("系统异常", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

阅读更多

c#dataGridView导出EXCEL

09-13

[code=C#]rn //设置自适应列宽rn private static void SetColumnFit(Microsoft.Office.Interop.Excel.Worksheet sheet, int iColCount)rn rn char column = 'B';rn for (int i = 0; i < iColCount; i++)rn rn Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(String.Format("01", column.ToString()),rn String.Format("01", column.ToString()));rn if (range != null)rn rn range.EntireColumn.AutoFit();rn rn column++;rn rn rn rnrn //设置适应页宽rn private static void SetPagetoFitWide(Microsoft.Office.Interop.Excel.Worksheet ws)rn rn ws.PageSetup.Zoom = false;rn ws.PageSetup.FitToPagesWide = 1;rn ws.PageSetup.FitToPagesTall = false;rn rn //导出指定列,各列名以逗号分开rn public static bool DataGridToExcel(DataGridView myDataGridView, string sColumns, string excelFileName, bool openFile)rn rn if (myDataGridView.RowCount == 0 || string.IsNullOrEmpty(sColumns.Trim()))rn rn return false;rn rnrn string[] sColAry = sColumns.Split(',');rnrn // 创建Excel对象 rn Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();rn if (xlApp == null)rn rn MessageBox.Show("Excel无法启动,可能是您未安装Excel软件");rn return false;rn rn // 创建Excel工作薄rn Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);rn Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];rnrn Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myDataGridView.ColumnCount]);rn // 列索引,行索引,总列数,总行数rn int colIndex = 0;rn int rowIndex = 0;rn int colCount = sColAry.Length;rn // int colCount = 20;rn int rowCount = myDataGridView.RowCount + 1;rn // 创建缓存数据rn object[,] objData = new object[rowCount + 1, colCount];rn // 获取列标题rn for (int i = 0; i < sColAry.Length; i++)rn rn objData[rowIndex, colIndex++] = myDataGridView.Columns[sColAry[i]].HeaderText;rn rnrn // 获取数据rn for (rowIndex = 1; rowIndex < rowCount; rowIndex++)rn rn for (colIndex = 0; colIndex < colCount; colIndex++)rn rn objData[rowIndex, colIndex] = myDataGridView.Rows[rowIndex - 1].Cells[sColAry[colIndex]].Value.ToString();rn rn System.Windows.Forms.Application.DoEvents();rn rn //设置单元格格式rn xlSheet.Cells.NumberFormatLocal = "@";rnrn // 写入Excelrn range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowCount + 1, colCount]);rn range.Value2 = objData;rn //自适应列宽rn SetColumnFit(xlSheet, colCount);rn //适应页宽rn //SetPagetoFitWide(xlSheet);rn // 保存rn tryrn rn SaveFileDialog Dlg = new SaveFileDialog();rn Dlg.DefaultExt = "xls";rn Dlg.Filter = "Excel文件(*.xls)|*.xls";rn Dlg.InitialDirectory = Directory.GetCurrentDirectory();rn if (Dlg.ShowDialog() == DialogResult.Cancel) return false;rn string filenameString = Dlg.FileName;rn if (filenameString.Trim() == "") return false;rn FileInfo file = new FileInfo(filenameString);rn if (file.Exists)rn rn tryrn rn file.Delete();rn rn catch (Exception erro)rn rn MessageBox.Show(erro.Message, "删除失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);rn return false;rn rn rnrn xlBook.Saved = true;rn if (xlApp.Version == "12.0")rn xlBook.SaveCopyAs(filenameString);rn elsern xlBook.SaveCopyAs(filenameString);rnrn if (openFile)rn rn if (xlApp.Version == "12.0")rn System.Diagnostics.Process.Start("Excel", "\"" + @filenameString + "\"");rn elsern System.Diagnostics.Process.Start("Excel", "\"" + @filenameString + "\"");rn rn return true;rn rn catchrn rn MessageBox.Show("保存出错,请检查!");rn return false;rn rn finallyrn rn System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets);rn System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);rn System.Runtime.InteropServices.Marshal.ReleaseComObject(range);rnrn xlSheet = null;rn range = null;rn xlApp.Quit();rn xlApp = null;rn GC.Collect();//垃圾回收rn rn rn public static void getExcelName(DataGridView dgv)rn rn string dgvColumns = "";rn for (int i = 0; i < dgv.ColumnCount; i++)rn rn if (dgv.Columns[i].Visible == true)rn rn dgvColumns += dgv.Columns[i].Name;rn dgvColumns += ",";rn rn rn dgvColumns = dgvColumns.Substring(0, dgvColumns.Length - 1);rn DataAccess.DataGridToExcel(dgv, dgvColumns, "", true);rn [/code]rnrn

没有更多推荐了,返回首页