澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:把Excel数据导入数据库,

2019-09-11 作者:www.8455.com   |   浏览(104)

  关于下载更新数据于滚动条类似PrograssBar控件的问题,我网上搜索了下,总体结合大致采用微软的定时器Timer控件更新数据。

    public class ImportExportToExcel

  1     public class ImportExportToExcel
  2     {
  3         private string strConn ;
  4         
  5         private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
  6         private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      
  7         
  8         public ImportExportToExcel()
  9         {
 10             //
 11             // TODO: 在此处添加构造函数逻辑
 12             //
 13             this.openFileDlg.DefaultExt = "xls";
 14             this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
 15 
 16             this.saveFileDlg.DefaultExt="xls";
 17             this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
 18 
 19         }
 20 
 21         从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
 22         //        /// <summary>
 23         //        /// 从Excel导入文件
 24         //        /// </summary>
 25         //        /// <param name="strExcelFileName">Excel文件名</param>
 26         //        /// <returns>返回DataSet</returns>
 27         //        public DataSet ImportFromExcel(string strExcelFileName)
 28         //        {
 29         //            return doImport(strExcelFileName);
 30         //        }
 31         /**//// <summary>
 32         /// 从选择的Excel文件导入
 33         /// </summary>
 34         /// <returns>DataSet</returns>
 35         public DataSet ImportFromExcel()
 36         {
 37             DataSet ds=new DataSet();
 38             if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
 39                 ds=doImport(openFileDlg.FileName);
 40             return ds;
 41         }
 42         /**//// <summary>
 43         /// 从指定的Excel文件导入
 44         /// </summary>
 45         /// <param name="strFileName">Excel文件名</param>
 46         /// <returns></returns>
 47         public DataSet ImportFromExcel(string strFileName)
 48         {
 49             DataSet ds=new DataSet();
 50             ds=doImport(strFileName);
 51             return ds;
 52         }
 53         /**//// <summary>
 54         /// 执行导入
 55         /// </summary>
 56         /// <param name="strFileName">文件名</param>
 57         /// <returns>DataSet</returns>
 58         private DataSet doImport(string strFileName)
 59         {
 60             if (strFileName=="") return null;
 61               
 62             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" 
 63                 "Data Source="    strFileName   ";" 
 64                 "Extended Properties=Excel 8.0;";
 65             OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
 66 
 67             DataSet ExcelDs = new DataSet();
 68             try
 69             {
 70                 ExcelDA.Fill(ExcelDs, "ExcelInfo");
 71                 
 72             }
 73             catch(Exception err)
 74             {
 75                 System.Console.WriteLine( err.ToString() );
 76             }
 77             return ExcelDs;
 78             
 79             
 80         
 81         }
 82         #endregion
 83 
 84         从DataSet到出到Excel#region 从DataSet到出到Excel
 85         /**//// <summary>
 86         /// 导出指定的Excel文件
 87         /// </summary>
 88         /// <param name="ds">要导出的DataSet</param>
 89         /// <param name="strExcelFileName">要导出的Excel文件名</param>
 90         public void ExportToExcel(DataSet ds,string strExcelFileName)
 91         {
 92             if (ds.Tables.Count==0 || strExcelFileName=="") return;
 93             doExport(ds,strExcelFileName);
 94     
 95 
 96         }
 97         /**//// <summary>
 98         /// 导出用户选择的Excel文件
 99         /// </summary>
100         /// <param name="ds">DataSet</param>
101         public void ExportToExcel(DataSet ds)
102         {
103             if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
104                 doExport(ds,saveFileDlg.FileName);
105             
106         }
107         /**//// <summary>
108         /// 执行导出
109         /// </summary>
110         /// <param name="ds">要导出的DataSet</param>
111         /// <param name="strExcelFileName">要导出的文件名</param>
112         private void doExport(DataSet ds,string strExcelFileName)
113         {
114             
115             Excel.Application excel= new Excel.Application();
116             
117             //            Excel.Workbook obj=new Excel.WorkbookClass();
118             //            obj.SaveAs("c:zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
119 
120             int rowIndex=1;
121             int colIndex=0;
122 
123             excel.Application.Workbooks.Add(true);
124             
125     
126             System.Data.DataTable table=ds.Tables[0] ;
127             foreach(DataColumn col in table.Columns)
128             {
129                 colIndex ;    
130                 excel.Cells[1,colIndex]=col.ColumnName;                
131             }
132 
133             foreach(DataRow row in table.Rows)
134             {
135                 rowIndex ;
136                 colIndex=0;
137                 foreach(DataColumn col in table.Columns)
138                 {
139                     colIndex ;
140                     excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
141                 }
142             }
143             excel.Visible=false;    
144             excel.Sheets[0] = "sss";
145             excel.ActiveWorkbook.SaveAs(strExcelFileName ".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
146             
147             
148             //wkbNew.SaveAs strBookName
149 
150 
151             //excel.Save(strExcelFileName);
152             excel.Quit();
153             excel=null;
154             
155             GC.Collect();//垃圾回收
156         }
157         #endregion
158 
159         从XML导入到Dataset#region 从XML导入到Dataset
160 
161         /**//// <summary>
162         /// 从选择的XML文件导入
163         /// </summary>
164         /// <returns>DataSet</returns>
165         public DataSet ImportFromXML()
166         {
167             DataSet ds=new DataSet();
168             System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
169             openFileDlg.DefaultExt="xml";
170             openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
171             if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
172                 try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
173                 catch{}
174             return ds;
175         }
176         /**//// <summary>
177         /// 从指定的XML文件导入
178         /// </summary>
179         /// <param name="strFileName">XML文件名</param>
180         /// <returns></returns>
181         public DataSet ImportFromXML(string strFileName)
182         {
183             if (strFileName=="")
184                 return null;
185             DataSet ds=new DataSet();
186             try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
187             catch{}
188             return ds;
189         }
190         
191         #endregion
192 
193         从DataSet导出到XML#region 从DataSet导出到XML
194         /**//// <summary>
195         /// 导出指定的XML文件
196         /// </summary>
197         /// <param name="ds">要导出的DataSet</param>
198         /// <param name="strXMLFileName">要导出的XML文件名</param>
199         public void ExportToXML(DataSet ds,string strXMLFileName)
200         {
201             if (ds.Tables.Count==0 || strXMLFileName=="") return;
202             doExportXML(ds,strXMLFileName);
203         }
204         /**//// <summary>
205         /// 导出用户选择的XML文件
206         /// </summary>
207         /// <param name="ds">DataSet</param>
208         public void ExportToXML(DataSet ds)
209         {
210             System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
211             saveFileDlg.DefaultExt="xml";
212             saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
213             if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
214                 doExportXML(ds,saveFileDlg.FileName);
215         }
216 
217         /**//// <summary>
218         /// 执行导出
219         /// </summary>
220         /// <param name="ds">要导出的DataSet</param>
221         /// <param name="strExcelFileName">要导出的XML文件名</param>
222         private void doExportXML(DataSet ds,string strXMLFileName)
223         {
224             try
225             {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
226             catch(Exception ex)
227             {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
228         }
229 
230         #endregion
231 
232     
233     
234     }
235 

 

public void Import(DataSet ds, string table)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
            conn.Open();
            SqlBulkCopy sbc = new SqlBulkCopy(conn);
            sbc.DestinationTableName = table;// ds.Tables[0].TableName;
            //将数据集合和目标服务器的字段对应
            for (int q = 0; q < ds.Tables[0].Columns.Count; q )
            {
                sbc.ColumnMappings.Add(ds.Tables[0].Columns.ColumnName, ds.Tables[0].Columns.ColumnName);
            }
            try
            {
                sbc.WriteToServer(ds.Tables[0]);//把数据插入到数据库中   
            }
            catch (Exception e)
            {
                throw new Exception("Import Failed");
            }
            finally
            {
                ds.Dispose();
                conn.Close();
                sbc.Close();
            }
        }
//把Excel文件导入到数据库中
        protected void BtnImport_Click(object sender, EventArgs e)
        {
            if (this.FileUpload1.HasFile == true)
            {
                ArrayList sheetname = ExcelSheetName(this.FileUpload1.PostedFile.FileName);
                ds = ExcelDataSource(this.FileUpload1.PostedFile.FileName, sheetname[0].ToString());
                int count = ds.Tables[0].Rows.Count;
                int ret = stuBll.Import(ds, "zxsjbxx");
                if (ret == 1)
                {
                    this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert("" "数据导入成功!" "");</script>"));
                    //Response.Write("<script language=javascript>alert('数据导入成功!');</script>");
                }
                else
                {
                    this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert("" "数据导入失败!请重试!" "");</script>"));
                }
            }
            else
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert("" "没有选择文件!" "");</script>"));
            }
        }
        //获得Excel中的所有sheetname。
        public ArrayList ExcelSheetName(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" filepath "';Extended Properties='Excel 8.0;HDR=yes'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.DataTable  sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            return al;
        }
        //该方法实现从Excel中导出数据到DataSet中,其中filepath为Excel文件的绝对路径,sheetname为表示那个Excel表;
        public DataSet ExcelDataSource(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" filepath "';Extended Properties='Excel 8.0;HDR=yes'";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" sheetname "]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            return ds;
        }

  在网上发现了典型的例子是使用TImer定时器和BackgroundWorker组件的结合更新PrograssBar进度的操作,网址如下:

    {

可能用进程的强制删除方法:kill()

概要:          子线程工作,发送成功邮件做标记,逐条提取数据,气泡提示信息,最小化到托盘,实时进度条   代码: using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Threading; using System.Text; using System.Windows.Forms; using System.Net.Mail; using System.Net; using System.Text.RegularExpressions;   namespace MailGroupSends {     public partial class Form1 : Form     {

 

        private string strConn ;

 1 #region "强制结束Word进程(垃圾回收)" 
 2 /// <summary> 
 3 /// 强制结束Word进程(垃圾回收) 
 4 /// </summary> 
 5 public void KillWordProcess() 
 6 { 
 7 int ProceedingCount = 0; 
 8 try 
 9 { 
10 System.Diagnostics.Process [] ProceddingCon = System.Diagnostics.Process.GetProcesses(); 
11 foreach(System.Diagnostics.Process IsProcedding in ProceddingCon) 
12 { 
13 if(IsProcedding.ProcessName.ToUpper() == "WINWORD") 
14 { 
15 ProceedingCount  = 1; 
16 IsProcedding.Kill(); 
17 } 
18 } 
19 } 
20 catch(System.Exception err) 
21 { 
22 MessageBox.Show(err.Message   "r"  "("   err.Source   ")"   "r"   err.StackTrace); 
23 } 
24 } 
25 #endregion

        private string subject = string.Empty, mailto = string.Empty, body = string.Empty;

  但是使用Timer定时器更新滚动条并非实时更新数据,顶多只是尽量模拟程序执行的步骤,本篇文章采用另一种下载更新滚动条数据的方式来实现实时更新数据,结合WebService网络服务和线程执行操作。

        

关于Excel进程一直驻留的,参考:

        private int sucNum = 0;

  •   关于WebService的引用大致分为组件引用和网址引用,如图:澳门新萄京官方网站 1
  •   关于WebService的部署和发布,结合本地IIS方式,如图:

        private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();

 

        private int TotalDate=0;

  澳门新萄京官方网站 2

        private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      

        private string ConnString

澳门新萄京官方网站 3

        

        {             get             {

  (1)主程序入口:

        public ImportExportToExcel()

                string pathFull = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;

/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
  Application.EnableVisualStyles();
  Application.SetCompatibleTextRenderingDefault(false);
  //Application.Run(new Form1());
  System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ThreadStart(UpdateLoading));
  thread.Start();
}

        {

                string path = pathFull.Substring(0, pathFull.LastIndexOf(@""));

static void UpdateLoading()
{
  ConvMyReord.Uil.Welcome.DownLoader downLoader = new Uil.Welcome.DownLoader();
  downLoader.Show();
  downLoader.Start();
}

            //

                return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

  (2)DownLoader类:

            // TODO: 在此处添加构造函数逻辑

  • path @"mail.mdb";

private void downLoading_DownLoadingCancelEvnet()
{
  if (_downLoading != null && _downLoading.GetDialogResult == DialogResult.No)
  Application.Exit();
}

            //

            }         }

public void Start()
{
  if (_downLoading == null) _downLoading = new DownLoading();
  _downLoading.Dock = DockStyle.Fill;
  this.pnlMain.Controls.Add(_downLoading);
  _downLoading.DownLoadingCancelEvnet = downLoading_DownLoadingCancelEvnet;
  _downLoading.StartDownLoading();
}

            this.openFileDlg.DefaultExt = "xls";

        private bool mailSent = false; //邮件是否发送成功

  (3)自定义控件:DownLoading

            this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";

        private int mailTotalCount = 0;

澳门新萄京官方网站 4

            this.saveFileDlg.DefaultExt="xls";

        private int CategoryId = 0;

public delegate void DownLoadingCancel();
public event DownLoadingCancel DownLoadingCancelEvnet;
public DialogResult GetDialogResult {get;set;}

            this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";

        private int SentCount = 0; //已发送数

private void btnCancel_Click(object sender, EventArgs e)
{
  if(DownLoadingCancelEvnet != null)
  {
    this.GetDialogResult = DialogResult.No;
    DownLoadingCancelEvnet();
  }
}

        }

        private int UnitConversion = 1; //默认为秒

public void StartDownLoading()
{
  ConvMyReord.WebReference.ConverRecordWebService converRecordWebService
  = new WebReference.ConverRecordWebService();
  System.Data.DataSet ds = converRecordWebService.DownLoadingScoure();//调用服务方法
  DownLoadHelper downLoadHelper = new DownLoadHelper();
  downLoadHelper.DownLoadProgressEvent = downloader_onDownLoadProgress;
  downLoadHelper.StartLoading(ds, this.progressBarControl1, lblDownLoad);
}

        从Excel文件导入到DataSet#region 从Excel文件导入到DataSet

                /// 发送间隔

//同步更新UI
private void downloader_onDownLoadProgress(long totalCount, long current)
{
  float percent = 0;
  if (this.InvokeRequired)
  {
    this.Invoke(new DownLoadHelper.DownLoadProgress(downloader_onDownLoadProgress), new object[] { totalCount, current });
  }
  else
  {
    if (this.progressBarControl1.Properties.Maximum == this.progressBarControl1.Position)
    {
      this.GetDialogResult = DialogResult.Yes;
      this.btnCancel.Enabled = false;
    }
    this.progressBarControl1.Properties.Maximum = (int)totalCount;
    this.progressBarControl1.Position = (int)current;
    percent = (float)current / (float)totalCount * 100;
    this.lblDownLoad.Text = "当前补丁下载进度:" percent.ToString()

        //        /// <summary>

        private int Interval

  • "%";
        System.Windows.Forms.Application.DoEvents();
      }
    }

        //        /// 从Excel导入文件

        {             get             {

  (4)DownLoadHelper帮助类:

        //        /// </summary>

                int timer = 0;

public delegate void DownLoadProgress(long total, long current);
public event DownLoadProgress DownLoadProgressEvent;

        //        /// <param name="strExcelFileName">Excel文件名</param>

                int totalMis = (TotalDate * UnitConversion * 1000);

public void StartLoading(System.Data.DataSet ds, DevExpress.XtraEditors.ProgressBarControl progressBar1, System.Windows.Forms.Label label1)
{
  if (ds == null) return;
  long totalCount = GetRowCounts(ds);
  LoadingData(totalCount, ds, progressBar1, label1);
}

        //        /// <returns>返回DataSet</returns>

                timer = totalMis / (mailTotalCount-SentCount);

private static long GetRowCounts(System.Data.DataSet ds)
{
  long count = 0;
  if (ds.Tables.Count <= 0) return count;
  for (int i = 0; i < ds.Tables.Count; i )
  {
    System.Data.DataTable table = ds.Tables[i];
    count = table.Rows.Count;
  }
  return count;
}

        //        public DataSet ImportFromExcel(string strExcelFileName)

                return timer;

public void LoadingData(long totalCount, System.Data.DataSet ds, DevExpress.XtraEditors.ProgressBarControl progressBar1, System.Windows.Forms.Label label1)
{
  if (totalCount <= 0 || ds.Tables.Count <= 0) return;
  long count = 0;
  for (int i = 0; i < ds.Tables.Count; i )
  {
    System.Data.DataTable table = ds.Tables[i];
    if (table == null || table.Rows.Count <= 0) continue;
    for (int j = 0; j < table.Rows.Count; j )
    {
       count;
      System.Windows.Forms.Application.DoEvents();
      if (DownLoadProgressEvent != null)
      DownLoadProgressEvent(100, (100/totalCount) * count);
       System.Threading.Thread.Sleep(100);
    }
  }
}

        //        {

            }         }

效果图:

        //            return doImport(strExcelFileName);

private void Form1_Load(object sender, EventArgs e)

澳门新萄京官方网站 5

        //        }

        {

 

        /**//// <summary>

            Control.CheckForIllegalCrossThreadCalls = false;

  以上是我前段时间自己编的部分程序,考虑从内存空间和效率上都存在不足,本篇主要是让大家了解事件式实时更新滚动条数据的编程思路,不足之处,请留言赐教。
  A young idler  ~  an old beggar !

        /// 从选择的Excel文件导入

            InitMailCategory();             SentMailCount();             LoadMailList();             ClearTxt(txtAnnexPath);            InitTime();         } /// 发送

        /// </summary>

        private void btnSendMail_Click(object sender, EventArgs e)

        /// <returns>DataSet</returns>

        {             TotalDate = Convert.ToInt32(this.txtDateCount.Text.Trim());             ControlEnabled(false);             subject = this.txtSubject.Text.Trim();             body = this.rtxtBody.Text.Trim();

        public DataSet ImportFromExcel()

            this.progressBar1.Visible = true;

        {

            this.lblProgress.Visible = true;

            DataSet ds=new DataSet();

            //用子线程工作            new Thread(new ThreadStart(StartMailSend)).Start();         } /// 邮件发送

            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 

        private bool Send(string mailTo, string subject, string body)

                ds=doImport(openFileDlg.FileName);

        {             try             {

            return ds;

                if (!IsEmail(mailTo)) //邮箱格式验证

        }

                {

        /**//// <summary>

                    WriteToTxt(DateTime.Now "   Faild      " mailTo, txtLogPath "   Error Message:邮箱格式不正确");

        /// 从指定的Excel文件导入

                    MarkErrorMail(mailTo);

        /// </summary>

                    return false;

        /// <param name="strFileName">Excel文件名</param>

                }

        /// <returns></returns>

                MailMessage msg = new MailMessage();

        public DataSet ImportFromExcel(string strFileName)

                msg.From = new MailAddress("***@****", "xiaoyaosr", Encoding.UTF8);

        {

                if (mailTo.IndexOf(",") > -1)

            DataSet ds=new DataSet();

                    msg.Bcc.Add(mailTo);                 else                     msg.To.Add(mailTo);                 msg.Subject = subject;                 msg.Body = body;  

            ds=doImport(strFileName);

                ArrayList annexList = ReadTxt(txtAnnexPath);

            return ds;

                for (int i = 0; i < annexList.Count; i )

        }

                {                     msg.Attachments.Add(new Attachment(annexList[i].ToString()));                 }  

        /**//// <summary>

                SmtpClient smtp = new SmtpClient("mail.163.com");

        /// 执行导入

                smtp.Credentials = new NetworkCredential("***@****", "*******");                 smtp.Send(msg);                 mailSent = true;                 sucNum ;             }

        /// </summary>

            catch (Exception ex)

        /// <param name="strFileName">文件名</param>

            {

        /// <returns>DataSet</returns>

                WriteToTxt(DateTime.Now "   Faild   Error Message:" ex.Message, txtLogPath);

        private DataSet doImport(string strFileName)

                this.notifyIcon1.ShowBalloonTip(Interval, "", ex.Message, ToolTipIcon.None);

        {

                System.Threading.Thread.Sleep(Interval * 2);                 KillThread();                 MarkErrorMail(mailTo);                 mailSent = false;             }

            if (strFileName=="") return null;

            return mailSent;

              

        }   //开始发送         public void StartMailSend()         {

            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

            MailSend mailSend = new MailSend();

                "Data Source="  strFileName ";"

            mailSend.iTotalCount = mailTotalCount;

                "Extended Properties=Excel 8.0;";

            mailSend.interval = Interval;             mailSend.iSentCount = SentCount;             mailSend.onMailSendProgress = new MailSend.dMailSendProgress(mailSend_onMailSendProgress);             mailSend.Start();         }           //同步更新

            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

        void mailSend_onMailSendProgress(int total, int current)

            DataSet ExcelDs = new DataSet();

        {             try             {

            try

                if (this.InvokeRequired)

            {

                {

                ExcelDA.Fill(ExcelDs, "ExcelInfo");

                    this.Invoke(new MailSend.dMailSendProgress(mailSend_onMailSendProgress), new object[] { total, current });

                

                }                 else                 {                     InitMailList();

            }

                    string percent = (((current 1) *100) / mailTotalCount).ToString() "%";

            catch(Exception err)

                    string progress = percent " " (current 1) "/" mailTotalCount;

            {

                    this.lableTimer.Text = progress;

                System.Console.WriteLine( err.ToString() );

                    this.notifyIcon1.ShowBalloonTip(Interval,"", progress,ToolTipIcon.None);//气泡提示信息

            }

                    this.progressBar1.Maximum = total;

            return ExcelDs;

                    this.progressBar1.Value = current;

            

                   if (total > 0 && subject.Length > 0)

            

                    {                                                                                  

        

                        if (Send(mailto, subject, body))                         {

        }

                            UpdateMailState(mailto, 1);

        #endregion

                            LoadMailList();                         }                     }

        从DataSet到出到Excel#region 从DataSet到出到Excel

                    if (current == total - 1)

        /**//// <summary>

                    {

        /// 导出指定的Excel文件

                        this.notifyIcon1.ShowBalloonTip(Interval, "", "发送完毕,成功发送" sucNum "封", ToolTipIcon.None);

        /// </summary>

                        this.progressBar1.Visible = false;                         this.lblProgress.Visible = false;

        /// <param name="ds">要导出的DataSet</param>

                        this.lableTimer.Text = "";

        /// <param name="strExcelFileName">要导出的Excel文件名</param>

                        ControlEnabled(true);                         return;                     }                 }             }             catch(Exception ex)             {

        public void ExportToExcel(DataSet ds,string strExcelFileName)

                this.notifyIcon1.ShowBalloonTip(Interval, "", ex.Message, ToolTipIcon.None);

        {

                System.Threading.Thread.Sleep(Interval * 2);                 base.Dispose(true);                 Application.ExitThread();                 KillThread();             }         }         /// 逐条读取未发送的邮件,具体代码就不在这里唠叨了,详见附件

            if (ds.Tables.Count==0 || strExcelFileName=="") return;

        private void InitMailList(){}

            doExport(ds,strExcelFileName);

        /// 加载邮件列表 DataGridView),不是主要内容,如需了解,详见附件

    

        private void LoadMailList(){ }

        }

        ///取状态为已发送的邮件个数,详见附件

        /**//// <summary>

       private void SentMailCount(){}

        /// 导出用户选择的Excel文件

        ///修改邮箱状态,这里标记为10,详见附件

        /// </summary>

        private void MarkErrorMail(string mail) {}

        /// <param name="ds">DataSet</param>

        ///标记已发送或出错的邮箱,详见附件

        public void ExportToExcel(DataSet ds)

        private void UpdateMailState(string mail, int state) {}

        {

        /// 初始化时间长

            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 

        private void InitTime()

                doExport(ds,saveFileDlg.FileName);

        {

            

            if (mailTotalCount > 0 && SentCount < mailTotalCount)

        }

            {

        /**//// <summary>

                this.txtDateCount.Text = ((mailTotalCount - SentCount) * 10).ToString();

        /// 执行导出

                this.lblTime.Text = TimeConvert((mailTotalCount - SentCount) *澳门新萄京官方网站:把Excel数据导入数据库,excel和DataSet之间相互转化。 10);

        /// </summary>

            }         }         /// 添加附件

        /// <param name="ds">要导出的DataSet</param>

        private void btnAnnexSelect_Click(object sender, EventArgs e)

        /// <param name="strExcelFileName">要导出的文件名</param>

        {

        private void doExport(DataSet ds,string strExcelFileName)

            OpenFileDialog ofd = new OpenFileDialog();

        {

            ofd.InitialDirectory = Application.ExecutablePath;             ofd.FileName = "";             ofd.Filter = "所有文件(*)|*";

            

            string strFileName = "";

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

            if (ofd.ShowDialog() == DialogResult.OK)

            

            {                 strFileName = ofd.FileName;

            //            Excel.Workbook obj=new Excel.WorkbookClass();

                this.txtAnnex.Text = strFileName;

            //            obj.SaveAs("c:zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

            }

            int rowIndex=1;

            WriteToTxt(strFileName, txtAnnexPath);

            int colIndex=0;

            ArrayList list = ReadTxt(txtAnnexPath);

            excel.Application.Workbooks.Add(true);

            if (list.Count > 0)

            

            {

    

                string str = string.Empty;

            System.Data.DataTable table=ds.Tables[0] ;

                for (int i = 0; i < list.Count; i )

            foreach(DataColumn col in table.Columns)

                {

            {

                    string file = list[i].ToString();

                colIndex ;    

                    str = "·¡è" file.Substring(file.LastIndexOf("\")

                excel.Cells[1,colIndex]=col.ColumnName;                

  • 1) ";";                 }

            }

                this.lblAnnexList.Text = str.Trim(";".ToCharArray());

            foreach(DataRow row in table.Rows)

            }         }                 /// 结束进程

            {

        private void KillThread()

                rowIndex ;

        {

                colIndex=0;

            foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcesses())

                foreach(DataColumn col in table.Columns)

            {

                {

                if (thisproc.ProcessName.Equals("MailGroupSends"))

                    colIndex ;

                {                     thisproc.Kill();                 }             }         }         #region 最小化到托盘

                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();

        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)

                }

        {

            }

            this.ShowInTaskbar = true;

            excel.Visible=false;    

            this.WindowState = FormWindowState.Normal;

            excel.Sheets[0] = "sss";

            this.Activate();         }

            excel.ActiveWorkbook.SaveAs(strExcelFileName ".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

        private void Form1_SizeChanged(object sender, EventArgs e)

            

        {

            

            if (this.WindowState == FormWindowState.Minimized)

            //wkbNew.SaveAs strBookName

                this.ShowInTaskbar = false;

            //excel.Save(strExcelFileName);

        }         #endregion         /// 邮箱分类comboBox)

            excel.Quit();

        private void InitMailCategory()

            excel=null;

       {

            

            OleDbConnection Conn = new OleDbConnection(ConnString);

            GC.Collect();//垃圾回收

            try             {

        }

                string sql = @"select categoryid,categoryname from mailCategory where recordstate=0";

        #endregion

                if (Conn.State == ConnectionState.Closed) Conn.Open();

        从XML导入到Dataset#region 从XML导入到Dataset

                OleDbCommand cmd = new OleDbCommand(sql, Conn);

        /**//// <summary>

                OleDbDataReader reader = cmd.ExecuteReader();

        /// 从选择的XML文件导入

                mailTotalCount = 0;

        /// </summary>

                DataTable table = new DataTable();

        /// <returns>DataSet</returns>

                table.Columns.Add("categoryid");                 table.Columns.Add("categoryname");

        public DataSet ImportFromXML()

                DataRow row = table.NewRow();

        {

                row[0] = "1000";                 row[1] = "全部";                 table.Rows.Add(row);

            DataSet ds=new DataSet();

                while (reader.Read())

            System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();

                {                     row = table.NewRow();

            openFileDlg.DefaultExt="xml";

                    row[0] = reader[0].ToString();

            openFileDlg.Filter= "xml文件 (*.xml)|*.xml";

                    row[1] = reader[1].ToString();

            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 

                    table.Rows.Add(row);                 }                 reader.Close();

                try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}

                if (reader != null) reader.Dispose();

                catch{}

                this.comboBox_mailcategory.ValueMember = "categoryid";

            return ds;

                this.comboBox_mailcategory.DisplayMember = "categoryname";

        }

                this.comboBox_mailcategory.DataSource = table;

        /**//// <summary>

                this.comboBox_mailcategory.SelectedIndex = 0;

        /// 从指定的XML文件导入

            }             catch{}            finally             {

        /// </summary>

                if (Conn.State == ConnectionState.Open) Conn.Close();

        /// <param name="strFileName">XML文件名</param>

                if (Conn != null) Conn.Dispose();

        /// <returns></returns>

            }         }         /// 选择邮箱分类

        public DataSet ImportFromXML(string strFileName)

        private void comboBox_mailcategory_SelectedIndexChanged(object sender, EventArgs e)

        {

        {             CategoryId =Convert.ToInt32(this.comboBox_mailcategory.SelectedValue);             LoadMailList();             SentMailCount();             InitTime();         }         /// 时间长秒)转换成时分秒格式

            if (strFileName=="")

        private string TimeConvert(int time)

                return null;

        {

            DataSet ds=new DataSet();

            int h = time / 3600;

            try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}

            int m = (time - h * 3600) / 60;

            catch{}

            int s = time - h * 3600 - m * 60;

            return ds;

            return h "时" m "分" s "秒";

        }

        }         /// 验证邮件格式是否正确

        

        private bool IsEmail(string email)

        #endregion

        {

        从DataSet导出到XML#region 从DataSet导出到XML

            string strExp = @"w ([- .']w )*@w ([-.]w )*.w ([-.]w )*";

        /**//// <summary>

            Regex r = new Regex(strExp);

        /// 导出指定的XML文件

            Match m = r.Match(email);

        /// </summary>

            return m.Success ? true : false;

        /// <param name="ds">要导出的DataSet</param>

        }           #region TxtOperator         //由于篇幅有限,这里就不具体贴出了,如需了解,详见附件         #endregion }       public class MailSend     {         //委托

        /// <param name="strXMLFileName">要导出的XML文件名</param>

        public delegate void dMailSendProgress(int total, int current);

        public void ExportToXML(DataSet ds,string strXMLFileName)

        //事件

        {

        public event dMailSendProgress onMailSendProgress;

            if (ds.Tables.Count==0 || strXMLFileName=="") return;

          public int iTotalCount;         public int interval;         public int iSentCount;         //开始模拟工作         public void Start()         {

            doExportXML(ds,strXMLFileName);

            for (int i = iSentCount; i < iTotalCount; i )

        }

            {

        /**//// <summary>

                if (onMailSendProgress != null)

        /// 导出用户选择的XML文件

                    onMailSendProgress(iTotalCount, i);

        /// </summary>

                Thread.Sleep(interval);             }         }     } }

        /// <param name="ds">DataSet</param>

点击下载源码

        public void ExportToXML(DataSet ds)

本文出自 “逍遥居” 博客,请务必保留此出处

        {

子线程工作,发送成功邮件做标记,逐条提取数据,气泡提示信息,最小化到托盘,实时进度条 代码: using System.Collections;using Sy...

            System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 

            saveFileDlg.DefaultExt="xml";

            saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";

            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 

                doExportXML(ds,saveFileDlg.FileName);

        }

        /**//// <summary>

        /// 执行导出

        /// </summary>

        /// <param name="ds">要导出的DataSet</param>

        /// <param name="strExcelFileName">要导出的XML文件名</param>

        private void doExportXML(DataSet ds,string strXMLFileName)

        {

            try

            {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}

            catch(Exception ex)

            {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    

        }

        #endregion

    }

本文由澳门新萄京官方网站发布于www.8455.com,转载请注明出处:澳门新萄京官方网站:把Excel数据导入数据库,

关键词: