C# DataGridView导出成Excel

C# DataGridView导出成Excel

2种方法参考:

第一种:用流保存成xls文件. 这种方法比较好, 可以参考

  1

  2using System.IO;

  3

  4        

  5

  6        /**//// <summary>

  7

  8        /// 另存新档按钮

  9

 10        /// </summary>

 11

 12        private void SaveAs() //另存新档按钮   导出成Excel

 13

 14        {

 15

 16            SaveFileDialog saveFileDialog = new SaveFileDialog();

 17

 18            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

 19

 20            saveFileDialog.FilterIndex = 0;

 21

 22            saveFileDialog.RestoreDirectory = true;

 23

 24            saveFileDialog.CreatePrompt = true;

 25

 26            saveFileDialog.Title = "Export Excel File To"; 

 27

 28

 29            saveFileDialog.ShowDialog();

 30

 31

 32            Stream myStream;

 33

 34            myStream = saveFileDialog.OpenFile();

 35

 36            //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));

 37

 38            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

 39

 40            string str = "";

 41

 42            try

 43

 44            {

 45

 46                //写标题

 47

 48                for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++)

 49

 50                {

 51

 52                    if (i > 0)

 53

 54                    {

 55

 56                        str += "\t";

 57

 58                    }

 59

 60                    str += dgvAgeWeekSex.Columns[i].HeaderText;

 61

 62                }

 63

 64

 65                sw.WriteLine(str);

 66

 67 

 68

 69                //写内容

 70

 71                for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)

 72

 73                {

 74

 75                    string tempStr = "";

 76

 77                    for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)

 78

 79                    {

 80

 81                        if (k > 0)

 82

 83                        {

 84

 85                            tempStr += "\t";

 86

 87                        }

 88

 89                        tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString();

 90

 91                    }

 92

 93                    

 94

 95                    sw.WriteLine(tempStr);                    

 96

 97                }

 98

 99                sw.Close();

100

101                myStream.Close();

102

103            }

104

105            catch (Exception e)

106

107            {

108

109                MessageBox.Show(e.ToString());

110

111            }

112

113            finally

114

115            {

116

117                sw.Close();

118

119                myStream.Close();

120

121            }           

122

123      }

124

125

第二种方法:引用Excel组件,先定义Excel对象(实例),再根据DataGridView中数据,一格一格去填充Excel对象(实例),单元格,最后保存Excel对象(实例)[调用对象Save()方法].

 1

 2      /**//// <summary>

 3

 4        /// 另存新档按钮   导出成Excel

 5

 6        /// </summary>

 7

 8        private void SaveToExcel() //另存新档按钮   导出成Excel

 9

10        {

11

12            SaveFileDialog saveFileDialog = new SaveFileDialog();

13

14            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

15

16            saveFileDialog.FilterIndex = 0;

17

18            saveFileDialog.RestoreDirectory = true;

19

20            saveFileDialog.CreatePrompt = true;

21

22            saveFileDialog.Title = "Export Excel File To";

23

24

25            saveFileDialog.ShowDialog();

26

27            string strName = saveFileDialog.FileName; 

28

29

30

31            System.Reflection.Missing miss = System.Reflection.Missing.Value;   

32

33

34            Excel.Application excel = new Excel.ApplicationClass();

35

36            Excel.Workbooks books = (Excel.Workbooks)excel.Workbooks;

37

38            Excel.Workbook book = (Excel.Workbook)(books.Add(miss));

39

40            Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;

41

42            sheet.Name = "test";

43

44

45            int colIndex=0;

46

47            foreach (DataGridViewColumn column in dgvAgeWeekSex.Columns)

48

49            {

50

51                colIndex++;

52

53                excel.Cells[1, colIndex] = column.HeaderText; 

54

55            }

56

57

58            for (int i = 0; i < dgvAgeWeekSex.Rows.Count; i++)

59

60            {

61

62                for (int j = 0; j < dgvAgeWeekSex.Columns.Count; j++)

63

64                {

65

66                    excel.Cells[i + 2, j + 1] = dgvAgeWeekSex.Rows[i].Cells[j].Value.ToString();

67

68                }

69

70            }

71

72         

73

74            sheet.SaveAs(strName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss);         

75

76            

77

78            book.Close(false, miss, miss);

79

80            books.Close();

81

82            excel.Quit();

83

84

85            //System.Runtime.InteropServices.Marshal.ReleaseComObject();   

86

87            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);

88

89            System.Runtime.InteropServices.Marshal.ReleaseComObject(book);

90

91            System.Runtime.InteropServices.Marshal.ReleaseComObject(books);

92

93            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

94

95            GC.Collect();

96

97        }