C#excel导入dategridview并保存到数据库/dategridview增加一行或几行一键保存数据库

本文介绍: excel 导入到 dategridview 显示并保存到数据库 dategridview增加一行或几行一键保存数据库。

Ex celHel per类(这个要导入NPOI包)

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace WindowsFormsApp1
{
    /// &lt;summary&gt;
    /// 操作Excel通用类
    /// &lt;/summary&gt;
    public class ExcelHelper
    {
        /// &lt;summary&gt;
        /// 从Excel读取数据，只支持单表
        /// </summary&gt;
        /// <param name="FilePath"&gt;文件路径</param&gt;
        public static DataTable ReadFromExcel(string FilePath)
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                //构建DataTable
                IRow row = sheet.GetRow(0);
                DataTable result = BuildDataTable(row);
                if (result != null)
                {
                    if (sheet.LastRowNum &gt;= 1)
                    {
                        for (int i = 1; i < sheet.LastRowNum + 1; i++)
                        {
                            IRow temp_row = sheet.GetRow(i);
                            if (temp_row == null) { continue; }//2019-01-14 修复 行为空时会出错
                            List<object> itemArray = new List<object>();
                            for (int j = 0; j < result.Columns.Count; j++)//解决Excel超出DataTable列问题    lqwvje20181027
                            {
                                //itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : temp_row.GetCell(j).ToString());
                                itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel  时间格式问题  lqwvje 20180904
                            }

                            result.Rows.Add(itemArray.ToArray());
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 从Excel读取数据，支持多表
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static DataSet ReadFromExcels(string FilePath)
        {
            DataSet ds = new DataSet();
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                int SheetCount = wk.NumberOfSheets;//获取表的数量
                if (SheetCount < 1)
                {
                    return ds;
                }
                for (int s = 0; s < SheetCount; s++)
                {
                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(s);
                    //构建DataTable
                    IRow row = sheet.GetRow(0);
                    if (row == null) { continue; }
                    DataTable tempDT = BuildDataTable(row);
                    tempDT.TableName = wk.GetSheetName(s);
                    if (tempDT != null)
                    {
                        if (sheet.LastRowNum >= 1)
                        {
                            for (int i = 1; i < sheet.LastRowNum + 1; i++)
                            {
                                IRow temp_row = sheet.GetRow(i);
                                if (temp_row == null) { continue; }//2019-01-14 修复 行为空时会出错
                                List<object> itemArray = new List<object>();
                                for (int j = 0; j < tempDT.Columns.Count; j++)//解决Excel超出DataTable列问题    lqwvje20181027
                                {
                                    itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel  时间格式问题  lqwvje 20180904
                                }
                                tempDT.Rows.Add(itemArray.ToArray());
                            }
                        }
                        ds.Tables.Add(tempDT);
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="fileName">导出的文件途径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten = true)
        {
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                if (workbook == null) { return -1; }

                try
                {
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    int count = 0;
                    if (isColumnWritten) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (int j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                        }
                        count = 1;
                    }

                    for (int i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (int j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                        count++;
                    }
                    workbook.Write(fs); //写入到excel

                    return count;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return -1;
                }
            }
        }
        /// <summary>
        /// 将DataSet数据导入到excel中   每个datatable一个sheet,sheet名为datatable名
        /// </summary>
        /// <param name="ds">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="fileName">导出的文件途径</param>
        public static bool DataTableToExcel(DataSet ds, string fileName, bool isColumnWritten = true)
        {
            if (ds == null || ds.Tables.Count < 1)
            {
                return false;
            }
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                if (workbook == null) { return false; }
                try
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        ISheet sheet = workbook.CreateSheet(dt.TableName);
                        if (isColumnWritten) //写入DataTable的列名
                        {
                            IRow row = sheet.CreateRow(0);
                            for (int j = 0; j < dt.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
                            }
                        }

                        for (int i = 0; i < dt.Rows.Count; ++i)
                        {
                            IRow row = sheet.CreateRow(isColumnWritten ? i + 1 : i);
                            for (int j = 0; j < dt.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                    }
                    workbook.Write(fs); //写入到excel
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return false;
                }
            }
            return true;
        }

        private static DataTable BuildDataTable(IRow Row)
        {
            DataTable result = null;
            if (Row.Cells.Count > 0)
            {
                result = new DataTable();
                for (int i = 0; i < Row.LastCellNum; i++)
                {
                    if (Row.GetCell(i) != null)
                    {
                        result.Columns.Add(Row.GetCell(i).ToString());
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                    cell.SetCellType(CellType.String);
                    return cell.StringCellValue;
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }
}

Form1

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace 增添excel测试
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        DataTable dt = new DataTable();
        string connString = @"Data Source=; Database = 测试; Integrated Security=true";
        SqlConnection conn;

  
        private void bind(string fileName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Data Source=" + fileName + ";" +
                 "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT *  FROM [Sheet1$]", strConn);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                dt = ds.Tables[0];
                this.dataGridView1.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("操作失败！" + err.ToString());
            }
        }

        private void insertToSql(DataRow dr)
        {
            //excel表中的列名和数据库中的列名一定要对应  
            string name = dr["L1"].ToString();
            string sex = dr["L2"].ToString();
            string no = dr["L3"].ToString();
            //string major = dr["无量纲承载力F"].ToString();
            string sql = "insert into [Sheet1$] values('" + name + "','" + sex + "','" + no + "')";
            this.textBox1.Text += "HHH"+sql;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }

        //导入excel到dategridview
        private void button1_Click_1(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                string filePath = openFile.FileName;
                DataTable excelDt = WindowsFormsApp1.ExcelHelper.ReadFromExcel(filePath);
                //把Excel读取到DataTable里面 然后再把DataTable存入数据库
                dataGridView1.DataSource = excelDt;
            }
            
        }

        //将Datagridview1的记录插入到数据库
        private void button2_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connString);
            conn.Open();
            DataTable dtt = (DataTable)dataGridView1.DataSource;
            textBox1.Text = "SD:" + dtt.Rows.Count;
            if (dataGridView1.Rows.Count > 0)
            {
                DataRow dr = null;
                for (int i = 0; i < dtt.Rows.Count; i++)
                {
                    dr = dtt.Rows[i];
                    insertToSql(dr);
                }
                conn.Close();
                MessageBox.Show("导入成功！");
            }
            else
            {
                MessageBox.Show("没有数据！");
            }
            
        }
        //加入
        private void button3_Click(object sender, EventArgs e)
        {
            this.Hide();
            Form2 form2 = new Form2();
            form2.Show();

        }
    }
}

Form2

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 增添excel测试
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        DataTable dt = new DataTable();
        string connString = @"Data Source=; Database = 测试; Integrated Security=true";
        SqlConnection conn;

        private void insertToSql(DataRow dr)
        {
            //excel表中的列名和数据库中的列名一定要对应  
            string name = dr[0].ToString();
            string sex = dr[1].ToString();
            string no = dr[2].ToString();
            //string major = dr["无量纲承载力F"].ToString();
            string sql = "insert into [Sheet1$] values('" + name + "','" + sex + "','" + no + "')";
            this.textBox1.Text += "HHH" + sql;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }

        //未绑定数据源dategridview转datatable
        private static DataTable GetDgvToTable(DataGridView dgv)
        {
            DataTable dt = new DataTable();

            // 列强制转换
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                System.Data.DataColumn dc = new System.Data.DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }

            // 循环行
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.Add();
        }
        //保存
        private void button2_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connString);
            conn.Open();
            textBox1.Text = "SD:" + dataGridView1.Rows[0].Cells[0].Value.ToString();
            
            DataTable dtt = GetDgvToTable(dataGridView1);
            textBox1.Text = "SD:" + dtt.Rows.Count;
            if (dataGridView1.Rows.Count > 0)
            {
                DataRow dr = null;
                for (int i = 0; i < dtt.Rows.Count; i++)
                {
                    dr = dtt.Rows[i];
                    //textBox1.Text+= "HH:" + dr["L1"].ToString();
                    insertToSql(dr);
                }
                conn.Close();
                MessageBox.Show("导入成功！");
            }
            else
            {
                MessageBox.Show("没有数据！");
            }
            
        }
    }
}