欢迎光临
我们一直在努力

c#源码读取excel数据到程序中-sql server-到dataset中_c#应用

建站超值云服务器,限时71元/月

一、将excel数据只读到程序中显示:


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Reflection;


namespace ExcelDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();  


        }


        private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass app = new ApplicationClass();
            app.Visible = false;
          
            WorkbookClass w = (WorkbookClass)app.Workbooks.Open(@”C:\Documents and Settings\qqq\桌面\002.xls”, //Environment.CurrentDirectory+
                Missing.Value,                Missing.Value,                Missing.Value,


                Missing.Value,                Missing.Value,                Missing.Value,


                Missing.Value,                Missing.Value,                Missing.Value,


                Missing.Value,                Missing.Value,                Missing.Value,


                Missing.Value,                Missing.Value);


           
            object missing = Type.Missing;          
            Sheets sheets = w.Worksheets;
            Worksheet datasheet = null;
            foreach (Worksheet sheet in sheets)
            {
                if (sheet.Name == “Recovered_Sheet1”)
                {
                    datasheet = sheet;
                    break;
                }
            }
            if (null == datasheet)
            {
                MessageBox.Show(this, “没有名称为 Recovered_Sheet1 的Sheet.”);
                return;
            }
         
            Range range = datasheet.get_Range(“A8″,”N35”);


            System.Array values = (System.Array)range.Formula;
            if (values != null)
            {
                int len1 = values.GetLength(0);
                int len2 = values.GetLength(1);


                for (int i = 1; i <= len1; i++)
                {
                    this.textBox1.Text += “\r\n”;
                    for (int j = 1; j <= len2; j++)
                    {
                        if (values.GetValue(i, j).ToString().Length == 0)
                            this.textBox1.Text += “\t\t”;
                        this.textBox1.Text += “\t” + values.GetValue(i, j).ToString();
                    }
                }
            }


            app.Quit();
            app = null;           
        }
    }


二、读取到dataset中/从dataset中写入sql server:


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;


namespace ExcelDemo
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }


        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name=”Path”>文件名称</param>
        /// <returns>返回一个数据集</returns>
        public DataSet ExcelToDS(string Path)
        {
            string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + Path + “;” + “Extended Properties=Excel 8.0;”;
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = “”;
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = “select * from [Recovered_Sheet1$]”;
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            return ds;
        }


        /// <summary>
        /// 写入Excel文档
        /// </summary>
        /// <param name=”Path”>文件名称</param>
        //public bool SaveFP2toExcel(string Path)
        //{
        //    try
        //    {
        //        string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + Path + “;” + “Extended Properties=Excel 8.0;”;
        //        OleDbConnection conn = new OleDbConnection(strConn);
        //        conn.Open();
        //        System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
        //        cmd.Connection = conn;
        //        //cmd.CommandText =”UPDATE [sheet1$] SET 姓名=2005-01-01 WHERE 工号=日期”;
        //        //cmd.ExecuteNonQuery ();
        //        for (int i = 0; i < fp2.Sheets[0].RowCount – 1; i++)
        //        {
        //            if (fp2.Sheets[0].Cells[i, 0].Text != “”)
        //            {
        //                cmd.CommandText = “INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES(” + fp2.Sheets[0].Cells[i, 0].Text + “,” +
        //                fp2.Sheets[0].Cells[i, 1].Text + “,” + fp2.Sheets[0].Cells[i, 2].Text + “,” + fp2.Sheets[0].Cells[i, 3].Text +
        //                “,” + fp2.Sheets[0].Cells[i, 4].Text + “,” + fp2.Sheets[0].Cells[i, 5].Text + “)”;
        //                cmd.ExecuteNonQuery();
        //            }
        //        }
        //        conn.Close();
        //        return true;
        //    }
        //    catch (System.Data.OleDb.OleDbException ex)
        //    {
        //        System.Diagnostics.Debug.WriteLine(“写入Excel发生错误:” + ex.Message);
        //    }
        //    return false;
        //}


        private void Form2_Load(object sender, EventArgs e)
        {
            DataSet ds=ExcelToDS(@”C:\Documents and Settings\qqq\桌面\002.xls”);
            int x = ds.Tables.Count;           
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.AddRange(
                new DataColumn[]{
               
                    new DataColumn(“col1”,typeof(string)),
                    new DataColumn(“col2”,typeof(string)),
                    new DataColumn(“col3”,typeof(string)),
                    new DataColumn(“col4”,typeof(string)),
                    new DataColumn(“col5”,typeof(string)),
                    new DataColumn(“col6”,typeof(string)),
                    new DataColumn(“col7”,typeof(string)),
                    new DataColumn(“col8”,typeof(string)),
                    new DataColumn(“col9”,typeof(string)),
                    new DataColumn(“col10”,typeof(string)),
                    new DataColumn(“col11”,typeof(string)),
                    new DataColumn(“col12”,typeof(string)),
                    new DataColumn(“col13”,typeof(string)),
                    new DataColumn(“col14”,typeof(string))
                });


                for(int i=0;i<ds.Tables[0].Rows.Count;i++)
                {


                    if (ds.Tables[0].Rows[i][13].ToString().Length <= 0)
                        continue;
                    DataRow dr=dt.NewRow();


                    dr.ItemArray=ds.Tables[0].Rows[i].ItemArray;


                   dt.Rows.Add(dr);


              }
            this.dataGridView1.DataSource=dt;
            this.dataGridView1.AutoGenerateColumns=false;


            }
           
        }


    }

赞(0)
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com 特别注意:本站所有转载文章言论不代表本站观点! 本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。未经允许不得转载:IDC资讯中心 » c#源码读取excel数据到程序中-sql server-到dataset中_c#应用
分享到: 更多 (0)