DataGridView导出excel+excel转换成datatable_datagird 与 excle 相互转化

1、DataGridView导出excel

 //导出到xls;
        //参数：dgvSource：为需要到处xls的dataGridView；strWordsheetName：为欲导出的表名;
        //使用时不需要进行dgvSource为空的判断;
        public void DgvToXls(DataGridView dgvSource,string strWorksheetName)
        {
            int intCount_blank=0;
            //当dataGridView可以增加行时，空白dataGridView最小行为1;
            if (dgvSource.AllowUserToAddRows)
                intCount_blank = 1;
            else
                intCount_blank = 0;

            //if (dgvSource.Rows.Count > intCount_blank)
            //{
                int intCount = 0;
                Excel.Application myExcel=new Excel.Application(); ; //新建excel对象;               
                myExcel.Visible = true;
                Excel._Workbook myWorkbook; //新建工作簿;
                myWorkbook = (Excel._Workbook)(myExcel.Workbooks.Add(Missing.Value));
                Excel._Worksheet myWorksheet; //新建工作表;
                myWorksheet = (Excel._Worksheet)myWorkbook.ActiveSheet;//已经有了默认的sheet1表,可以进行操作;
                myWorksheet = (Excel.Worksheet)myWorkbook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);    //再增加一个新表;
                myWorksheet.Name = strWorksheetName;   //为新表赋值;
                Excel._Worksheet WantDeleteSheet; //新建欲删除的工作表;
                WantDeleteSheet = (Excel._Worksheet)myWorkbook.Sheets["Sheet1"];
                WantDeleteSheet.Delete();
                //WantDeleteSheet = (Excel._Worksheet)myWorkbook.Sheets["Sheet2"];
                //WantDeleteSheet.Delete();
                //WantDeleteSheet = (Excel._Worksheet)myWorkbook.Sheets["Sheet3"];
                //WantDeleteSheet.Delete();
                WantDeleteSheet = null;

                try
                {
                    //为excle表头赋值;
                    if (dgvSource.AllowUserToAddRows)
                    {
                        for (int k = 0; k < dgvSource.ColumnCount; k++)
                        {
                            myWorksheet.Cells[1, k + 1] = dgvSource.Columns[k].HeaderText.ToString();
                        }

                        for (int i = 0; i < dgvSource.Rows.Count; i++) //循环dataGridView;
                        {
                            for (int j = 0; j < dgvSource.ColumnCount; j++)
                            {
                                myWorksheet.Cells[i + 2, j + 1] = dgvSource.Rows[i].Cells[j].Value.ToString().Trim(); //写入工作表;
                            }
                            intCount++;
                        }
                    }
                    else
                    {
                        for (int k = 0; k < dgvSource.ColumnCount; k++)
                        {
                            myWorksheet.Cells[1, k + 1] = dgvSource.Columns[k].HeaderText.ToString();
                        }

                        for (int i = 0; i < dgvSource.Rows.Count; i++) //循环dataGridView;
                        {
                            for (int j = 0; j < dgvSource.ColumnCount; j++)
                            {
                                myWorksheet.Cells[i + 2, j + 1] = dgvSource.Rows[i].Cells[j].Value.ToString().Trim(); //写入工作表;
                            }
                            intCount++;
                        }
                    }
                    //列宽自适应;
                    myWorksheet.Columns.EntireColumn.AutoFit();
                }
                catch
                {
                    MessageBox.Show("导出xls数据表时出现了错误，请检查！");
                }
                myWorksheet = null;
                myWorkbook = null;
                myExcel = null;
                MessageBox.Show("导出完毕！共导出记录" + intCount.ToString() + "条。", "提示！", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //}
            //else
            //{
            //    MessageBox.Show("没有可以导出excle的内容,请先进行查询!");
            //}
        }
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

引用：DgvToXls(DataGridView1, “导出的数据”);

2、从xls中读取表到datatable

 //从xls中读取表,并保证xls中没有空值(允许最后一列的值为空),否则返回null;
        //参数：PathFileName：等于导入对话框的.FileName,strszLieming：是被导入电子表格的列名（必须有列名而不能直接就是数据）;
        //使用时必须加上if (dtb != null),以判断是否读出的结果为空;在行循环内还必须加上第一个单元格是否为空的判断，已去除前六行系统数据;
        //注意被导入的xls文件只能有一个表,支持中文;
        public DataTable ReadFromXLS(string PathFileName,string[] strszLieming)
        {
            string strSql = "";
            Boolean blCellIsEmpty = false;
            int k = 0;
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + PathFileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
            //HDR=1;表示第一行为表头;
            //IMEX=1;表示采用文本格式，但是如果前8行不出现文本格式的内容，就默认为字符串格式;
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            conn.Open();
            DataTable tbFromXls = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            //DataTable tbFromXls = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            try
            {
                strSql = "select ";
                for (int i = 0; i < strszLieming.Length; i++)
                {
                    if (i == strszLieming.Length - 1)
                    {
                        strSql = strSql + strszLieming[i];
                    }
                    else
                    {
                        strSql = strSql + strszLieming[i] + ",";
                    }
                }

                //if (tbFromXls.Rows.Count > 1)
                //{
                //    MessageBox.Show("被导入xls文件中的表大于1个，表名为:" + tbFromXls.Rows[1]["Table_Name"].ToString() + "，请删除多余的表！或者尝试把内容复制到新的excle文件，并保存为03的xls格式！");
                //    tbFromXls = null;
                //    conn.Close();
                //    return null;
                //}
                OleDbCommand myOleDbCommand = new OleDbCommand(strSql + " FROM [" + tbFromXls.Rows[0]["Table_Name"].ToString() + "]", conn);
                OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
                myData.Fill(tbFromXls);
                conn.Close();
                if (tbFromXls.Columns.Count == 10)  //如果是空表,就是10列,和要导入表的格式和列数没有关系;
                {
                    return null;
                }
                else
                {
                    //判断是否有空值;
                    for (int j = 0; j < tbFromXls.Rows.Count; j++)  //遍历整个表;
                    {
                        blCellIsEmpty = true;
                        //遍历第j行的每个单元格;
                        for (int m = 0; m < strszLieming.Length - strszLieming.Length + 1; m++)  //为strszLieming.Length-1是因为最后一列不判断是否为空，因为一般都为备注;改为只判断第一列;
                        {
                            if (tbFromXls.Rows[j][strszLieming[m]].ToString() != string.Empty)
                            {
                                blCellIsEmpty = false;
                            }
                            //MessageBox.Show("第" + j.ToString() + "行的第一列：" + tbFromXls.Rows[j][strszLieming[m]].ToString());
                        }
                        if (blCellIsEmpty)
                        {
                            k = k + 1;
                        }
                    }
                    if (k > 300)
                    {
                        MessageBox.Show("被导入的数据都必须有值，但是有的为空白，因此所有数据都没有被处理，请检查！");
                        tbFromXls = null;
                        conn.Close();
                        return null;
                    }
                    else
                    {
                        return tbFromXls;
                    }
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                MessageBox.Show("请检查表的标题是否正确！", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

3、从excel转换datatable，使用Microsoft.Office.Interop.Excel

 /// <summary>
        /// 读取excel到datatable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="hasTitle"></param>
        /// <returns></returns>
        public static DataTable excelToDataTable(string filePath, bool hasTitle = false)
        {

            int iRowCount;
            int iColCount;

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Sheets sheets;
            object oMissiong = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            DataTable dt = new DataTable();

            try
            {
                if (app == null) return null;
                workbook = app.Workbooks.Open(filePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                sheets = workbook.Worksheets;

                //将数据读入到DataTable中
                Microsoft.Office.Interop.Excel.Worksheet worksheet = sheets.get_Item(1);
                if (worksheet == null) return null;
                iRowCount = worksheet.UsedRange.Rows.Count;
                iColCount = worksheet.UsedRange.Columns.Count;
                //生成列头
                for (int i = 0; i < iColCount; i++)
                {
                    var name = "column" + i;
                    if (hasTitle)
                    {
                        var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
                        if (!string.IsNullOrWhiteSpace(txt)) name = txt;
                    }
                    while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
                    dt.Columns.Add(new DataColumn(name, typeof(string)));
                }
                //生成行数据
                Microsoft.Office.Interop.Excel.Range range;
                int rowIdx = hasTitle ? 2 : 1;
                for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
                        dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
                    }
                    dt.Rows.Add(dr);
                }

                return dt;


            }
            catch { return null; }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
        }
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72