C#将数据导入到excel文件

最近在做C#对excel的操作程序,简单的与datagridview的交互如下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.IO;
namespace exceltest1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
      
        /// <summary> 
        /// 结束进程 
        /// </summary> 
      
        private void Button1_Click_1(object sender, EventArgs e)
        {
            KillSpecialExcel();
            string SourcePath = @"C:\201906\host\exceltest1.xlsx";
            //string Targepath = @"E:\Target.xls";
            string Targetpath = @"C:\201906\host\exceltest1.xlsx";
            string result = string.Empty;
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + SourcePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + SourcePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(dt);
            this.dataGridView1.DataSource = dt;
            try
            {
                if (File.Exists(Targetpath))
                {
                    File.Delete(Targetpath);
                }
                //                string conns = "Provider=Microsoft.ACE.OLEDB.12.0; Data source=" + Targetpath + ";Extended Properties=Excel 8.0;";
                string conns = "Provider=Microsoft.ACE.OLEDB.12.0; Data source=" + Targetpath + ";Extended Properties=Excel 12.0;";
                OleDbConnection connection = new OleDbConnection(conns);
                string sqlCreate = "CREATE TABLE Sheet1([longitude] varChar,[latitude] varChar)";
                OleDbCommand connectiondata = new OleDbCommand(sqlCreate, connection);
                connection.Open();
                connectiondata.ExecuteNonQuery();
                if (dataGridView1.Rows.Count > 0)
                {
                    DataRow dr = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        string longitude = dr["longitude"].ToString();
                        string latitude = dr["latitude"].ToString();
                        connectiondata.CommandText = "insert into Sheet1 values('" + longitude + "','" + latitude + "')";
                        connectiondata.ExecuteNonQuery();
                    }
                }
                connection.Close();
                MessageBox.Show("导入成功!");
            }
            catch (Exception)
            {
                result = "请保存或关闭可能已打开的Excel文件";
            }
            finally
            {
                dt.Dispose();
            }
        }
        private static void KillSpecialExcel()
        {
            foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
            {
                if (!theProc.HasExited)
                {
                    bool b = theProc.CloseMainWindow();
                    if (b == false)
                    {
                        theProc.Kill();
                    }
                    theProc.Close();
                }
            }
        }
    }
}
posted @ 2019-06-14 14:19  RunningSoybean  阅读(2474)  评论(0编辑  收藏  举报