چگونه در برنامه های تحت ویندوز یک CSV FILE UPLOADER بسازیم؟
شنبه 9 خرداد 1394در این مقاله ما یاد خواهیم گرفت چگونه در برنامه تحت ویندوز خود یک CSV FILE UPLOADER داشته باشیم. CSV یا همان Comma Seperated Values یک قالب فایل معروف و خیلی قدیمی است که در محیط ها و نرم افزارهای بسیار مختلفی مورد استفاده قرار میگیرد.
CSV یا همان Comma Seperated Values یک قالب فایل معروف و خیلی قدیمی است که در محیط ها و نرم افزارهای بسیار مختلفی مورد استفاده قرار میگیرد. در این قالب هر رکورد از اطلاعات در یک سطر فایل ذخیره میشود. و در هر سطر هم فیلدها به وسیله کاراکتر کاما «,» از هم جدا میشوند.
این آموزش برای آپلود فایلهای CSV در برنامه های تحت ویندوز و ذخیره داده در بانک اطلاعاتی اکسس مفید می باشد.
یک کلاس برای آپلود فایل های CSV بسازید.
using System.IO; using System.Text.RegularExpressions; using System.Data; public sealed class CsvReader: System.IDisposable { public CsvReader(string fileName): this(new FileStream(fileName, FileMode.Open, FileAccess.Read)) {} public CsvReader(Stream stream) { __reader = new StreamReader(stream); } public DataSet RowEnumerator { get { if (null == __reader) throw new System.ApplicationException("I can't start reading without CSV input."); __rowno = 0; string sLine; string sNextLine; DataSet ds = new DataSet(); DataTable dt = ds.Tables.Add("TheData"); while (null != (sLine = __reader.ReadLine())) { while (rexRunOnLine.IsMatch(sLine) && null != (sNextLine = __reader.ReadLine())) sLine += "\n" + sNextLine; __rowno++; DataRow dr = dt.NewRow(); string[] values = rexCsvSplitter.Split(sLine); for (int i = 0; i < values.Length; i++) { values[i] = Csv.Unescape(values[i]); if (__rowno == 1) { dt.Columns.Add(values[i].Trim()); } else { if (Csv.CharNotAllowes(values[i])) { dr[i] = values[i].Trim(); } } } if (__rowno != 1) { dt.Rows.Add(dr); } //yield return values; } __reader.Close(); return ds; } } public long RowIndex { get { return __rowno; } } public void Dispose() { if (null != __reader) __reader.Dispose(); } //============================================ private long __rowno = 0; private TextReader __reader; private static Regex rexCsvSplitter = new Regex(@ ",(?=(?:[^" "]*" "[^" "]*" ")*(?![^" "]*" "))"); private static Regex rexRunOnLine = new Regex(@ "^[^" "]*(?:" "[^" "]*" "[^" "]*)*" "[^" "]*$"); } public static class Csv { public static string Escape(string s) { if (s.Contains(QUOTE)) s = s.Replace(QUOTE, ESCAPED_QUOTE); if (s.IndexOfAny(CHARACTERS_THAT_MUST_BE_QUOTED) > -1) s = QUOTE + s + QUOTE; return s; } public static string Unescape(string s) { if (s.StartsWith(QUOTE) && s.EndsWith(QUOTE)) { s = s.Substring(1, s.Length - 2); if (s.Contains(ESCAPED_QUOTE)) s = s.Replace(ESCAPED_QUOTE, QUOTE); } return s; } public static bool CharNotAllowes(string s) { if (s.IndexOfAny(CHARACTERS_THAT_NOT_ALLOWED) > -1) { return false; } else { return true; } } private const string QUOTE = "\""; private const string ESCAPED_QUOTE = "\"\""; private static char[] CHARACTERS_THAT_MUST_BE_QUOTED = { ',', '"', '\n' }; private static char[] CHARACTERS_THAT_NOT_ALLOWED = { '?', '!', '^', '*', '~', 'Ñ', '½', 'Ð', '', '»', 'µ', 'º', 'Ñ', '´' }; }
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.Data.Odbc; using System.IO; using System.Data.OleDb; namespace Upload_Stock_Data { /// <summary> ///sharhe kholase baraye frmMain /// </summary> public class frmMain: System.Windows.Forms.Form { #region Declarations private System.Windows.Forms.GroupBox gbMain; private System.Windows.Forms.TextBox txtCSVFolderPath; private System.Windows.Forms.Button btnOpenFldrBwsr; private System.Windows.Forms.FolderBrowserDialog fbdCSVFolder; private System.Windows.Forms.TextBox txtCSVFilePath; private System.Windows.Forms.Button btnOpenFileDlg; private System.Windows.Forms.OpenFileDialog openFileDialogCSVFilePath; private System.Windows.Forms.Button btnImport; private System.Windows.Forms.DataGrid dGridCSVdata; string strCSVFile = ""; private System.Windows.Forms.GroupBox gbMainUploadData; private System.Windows.Forms.Button btnUpload; System.Data.Odbc.OdbcDataAdapter obj_oledb_da; private bool bolColName = true; string strFormat = "CSVDelimited"; private System.Windows.Forms.Label lblFolderPath; private System.Windows.Forms.Label lblFilePath; /// <summary> /// variable mored niaz baraye tarahi /// </summary> private System.ComponentModel.Container components = null;#endregion #region Constructor public frmMain() { InitializeComponent(); // // TODO: ezafe kardan har code constructor bad az seda zadan InitializeComponent // }#endregion #region Destructor /// <summary> /// pak kardan hameye manabe estefade shode /// </summary> protected override void Dispose(bool disposing) { if (disposing) { if (components != null) { components.Dispose(); } } base.Dispose(disposing); } #endregion #region Windows Form Designer generated code /// <summary> ///methode mored niaz baraye tarahi,mohtavaye in method ra dar code editor taghir nadahid /// </summary> private void InitializeComponent() { this.gbMain = new System.Windows.Forms.GroupBox(); this.lblFilePath = new System.Windows.Forms.Label(); this.lblFolderPath = new System.Windows.Forms.Label(); this.dGridCSVdata = new System.Windows.Forms.DataGrid(); this.btnImport = new System.Windows.Forms.Button(); this.btnOpenFileDlg = new System.Windows.Forms.Button(); this.txtCSVFilePath = new System.Windows.Forms.TextBox(); this.btnOpenFldrBwsr = new System.Windows.Forms.Button(); this.txtCSVFolderPath = new System.Windows.Forms.TextBox(); this.fbdCSVFolder = new System.Windows.Forms.FolderBrowserDialog(); this.openFileDialogCSVFilePath = new System.Windows.Forms.OpenFileDialog(); this.gbMainUploadData = new System.Windows.Forms.GroupBox(); this.btnUpload = new System.Windows.Forms.Button(); this.gbMain.SuspendLayout(); ((System.ComponentModel.ISupportInitialize)(this.dGridCSVdata)).BeginInit(); this.gbMainUploadData.SuspendLayout(); this.SuspendLayout(); // // gbMain // this.gbMain.BackColor = System.Drawing.SystemColors.InactiveCaptionText; this.gbMain.Controls.Add(this.lblFilePath); this.gbMain.Controls.Add(this.lblFolderPath); this.gbMain.Controls.Add(this.dGridCSVdata); this.gbMain.Controls.Add(this.btnImport); this.gbMain.Controls.Add(this.btnOpenFileDlg); this.gbMain.Controls.Add(this.txtCSVFilePath); this.gbMain.Controls.Add(this.btnOpenFldrBwsr); this.gbMain.Controls.Add(this.txtCSVFolderPath); this.gbMain.FlatStyle = System.Windows.Forms.FlatStyle.System; this.gbMain.Location = new System.Drawing.Point(16, 8); this.gbMain.Name = "gbMain"; this.gbMain.Size = new System.Drawing.Size(504, 416); this.gbMain.TabIndex = 0; this.gbMain.TabStop = false; this.gbMain.Text = "Import CSV Data"; // // lblFilePath // this.lblFilePath.Location = new System.Drawing.Point(32, 47); this.lblFilePath.Name = "lblFilePath"; this.lblFilePath.Size = new System.Drawing.Size(72, 20); this.lblFilePath.TabIndex = 12; this.lblFilePath.Text = "File Path:"; this.lblFilePath.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; // // lblFolderPath // this.lblFolderPath.Location = new System.Drawing.Point(32, 23); this.lblFolderPath.Name = "lblFolderPath"; this.lblFolderPath.Size = new System.Drawing.Size(72, 20); this.lblFolderPath.TabIndex = 11; this.lblFolderPath.Text = "Folder Path:"; this.lblFolderPath.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; // // dGridCSVdata // this.dGridCSVdata.AlternatingBackColor = System.Drawing.SystemColors.ControlLightLight; this.dGridCSVdata.CaptionForeColor = System.Drawing.Color.AliceBlue; this.dGridCSVdata.CaptionText = "Imported CSV Data"; this.dGridCSVdata.DataMember = ""; this.dGridCSVdata.ForeColor = System.Drawing.Color.YellowGreen; this.dGridCSVdata.HeaderBackColor = System.Drawing.Color.BlanchedAlmond; this.dGridCSVdata.HeaderForeColor = System.Drawing.Color.Black; this.dGridCSVdata.Location = new System.Drawing.Point(8, 124); this.dGridCSVdata.Name = "dGridCSVdata"; this.dGridCSVdata.ParentRowsForeColor = System.Drawing.Color.Yellow; this.dGridCSVdata.ReadOnly = true; this.dGridCSVdata.SelectionForeColor = System.Drawing.SystemColors.ControlLight; this.dGridCSVdata.Size = new System.Drawing.Size(488, 276); this.dGridCSVdata.TabIndex = 5; // // btnImport // this.btnImport.Cursor = System.Windows.Forms.Cursors.Hand; this.btnImport.FlatStyle = System.Windows.Forms.FlatStyle.System; this.btnImport.Location = new System.Drawing.Point(112, 92); this.btnImport.Name = "btnImport"; this.btnImport.Size = new System.Drawing.Size(280, 26); this.btnImport.TabIndex = 4; this.btnImport.Text = "Import CSV Data"; this.btnImport.Click += new System.EventHandler(this.btnImport_Click); // // btnOpenFileDlg // this.btnOpenFileDlg.Cursor = System.Windows.Forms.Cursors.Hand; this.btnOpenFileDlg.FlatStyle = System.Windows.Forms.FlatStyle.System; this.btnOpenFileDlg.Location = new System.Drawing.Point(368, 47); this.btnOpenFileDlg.Name = "btnOpenFileDlg"; this.btnOpenFileDlg.Size = new System.Drawing.Size(24, 23); this.btnOpenFileDlg.TabIndex = 3; this.btnOpenFileDlg.Click += new System.EventHandler(this.btnOpenFileDlg_Click); // // txtCSVFilePath // this.txtCSVFilePath.BackColor = System.Drawing.SystemColors.Info; this.txtCSVFilePath.Location = new System.Drawing.Point(112, 48); this.txtCSVFilePath.Name = "txtCSVFilePath"; this.txtCSVFilePath.ReadOnly = true; this.txtCSVFilePath.Size = new System.Drawing.Size(240, 20); this.txtCSVFilePath.TabIndex = 2; this.txtCSVFilePath.Text = "D:\\Test\\Test.csv"; // // btnOpenFldrBwsr // this.btnOpenFldrBwsr.Cursor = System.Windows.Forms.Cursors.Hand; this.btnOpenFldrBwsr.FlatStyle = System.Windows.Forms.FlatStyle.System; this.btnOpenFldrBwsr.Location = new System.Drawing.Point(368, 24); this.btnOpenFldrBwsr.Name = "btnOpenFldrBwsr"; this.btnOpenFldrBwsr.Size = new System.Drawing.Size(24, 23); this.btnOpenFldrBwsr.TabIndex = 1; this.btnOpenFldrBwsr.Click += new System.EventHandler(this.btnOpenFldrBwsr_Click); // // txtCSVFolderPath // this.txtCSVFolderPath.BackColor = System.Drawing.SystemColors.Info; this.txtCSVFolderPath.Location = new System.Drawing.Point(112, 24); this.txtCSVFolderPath.Name = "txtCSVFolderPath"; this.txtCSVFolderPath.ReadOnly = true; this.txtCSVFolderPath.Size = new System.Drawing.Size(240, 20); this.txtCSVFolderPath.TabIndex = 1; this.txtCSVFolderPath.Text = "D:\\Test"; // // openFileDialogCSVFilePath // this.openFileDialogCSVFilePath.Filter = "CSV Files (*.csv)|*.csv|DAT Files (*.dat)|*.dat"; this.openFileDialogCSVFilePath.Title = "Select the CSV file for importing"; this.openFileDialogCSVFilePath.FileOk += new System.ComponentModel.CancelEventHandler(this.openFileDialogCSVFilePath_FileOk); // // gbMainUploadData // this.gbMainUploadData.BackColor = System.Drawing.SystemColors.InactiveCaptionText; this.gbMainUploadData.Controls.Add(this.btnUpload); this.gbMainUploadData.FlatStyle = System.Windows.Forms.FlatStyle.System; this.gbMainUploadData.Location = new System.Drawing.Point(16, 432); this.gbMainUploadData.Name = "gbMainUploadData"; this.gbMainUploadData.Size = new System.Drawing.Size(504, 56); this.gbMainUploadData.TabIndex = 1; this.gbMainUploadData.TabStop = false; this.gbMainUploadData.Text = "Save Data in Table"; // // btnUpload // this.btnUpload.Cursor = System.Windows.Forms.Cursors.Hand; this.btnUpload.Enabled = false; this.btnUpload.FlatStyle = System.Windows.Forms.FlatStyle.System; this.btnUpload.Location = new System.Drawing.Point(112, 24); this.btnUpload.Name = "btnUpload"; this.btnUpload.Size = new System.Drawing.Size(280, 23); this.btnUpload.TabIndex = 1; this.btnUpload.Text = "Save"; this.btnUpload.Click += new System.EventHandler(this.btnUpload_Click); // // frmMain // this.AcceptButton = this.btnImport; this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.BackColor = System.Drawing.SystemColors.ActiveCaptionText; this.ClientSize = new System.Drawing.Size(536, 494); this.Controls.Add(this.gbMainUploadData); this.Controls.Add(this.gbMain); this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D; this.MaximizeBox = false; this.Name = "frmMain"; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = "CSV Reader"; this.Closing += new System.ComponentModel.CancelEventHandler(this.frmMain_Closing); this.Load += new System.EventHandler(this.frmMain_Load); this.gbMain.ResumeLayout(false); this.gbMain.PerformLayout(); ((System.ComponentModel.ISupportInitialize)(this.dGridCSVdata)).EndInit(); this.gbMainUploadData.ResumeLayout(false); this.ResumeLayout(false); }#endregion #region Main() Method /// <summary> /// نقطه ورودی اصلی برنامه /// </summary> [STAThread] static void Main() { Application.EnableVisualStyles(); Application.DoEvents(); Application.Run(new frmMain()); }#endregion #region Form Load private void frmMain_Load(object sender, System.EventArgs e) { try { } catch (Exception ex) { MessageBox.Show(ex.Message); } finally {} }#endregion #region Open Folder Browser Button //ba click rooye in dokme FOLDERBROWSERDIALOG baz shode ta karbar betavanad adresse fooldere shamele file csv ra entekhab namayad private void btnOpenFldrBwsr_Click(object sender, System.EventArgs e) { try { if (fbdCSVFolder.ShowDialog() == DialogResult.OK) { txtCSVFolderPath.Text = fbdCSVFolder.SelectedPath.Trim(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { } }#endregion #region Open File Dialog Button //ba click roye in dokme FOLDERBROWSERDIALOG baz shode ta karbar betavanad file csv ra entekhab konad private void btnOpenFileDlg_Click(object sender, System.EventArgs e) { try { openFileDialogCSVFilePath.InitialDirectory = txtCSVFolderPath.Text.Trim(); if (openFileDialogCSVFilePath.ShowDialog() == DialogResult.OK) { txtCSVFilePath.Text = openFileDialogCSVFilePath.FileName.Trim(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally {} }#endregion #region Function For Importing Data From CSV File public DataSet ConnectCSV() { DataSet ds = new DataSet(); string fileName = openFileDialogCSVFilePath.FileName; CsvReader reader = new CsvReader(fileName); ds = reader.RowEnumerator; dGridCSVdata.DataSource = ds; dGridCSVdata.DataMember = "TheData"; return ds; } #endregion #region Button Import CSV Data private void btnImport_Click(object sender, System.EventArgs e) { try { if (txtCSVFolderPath.Text == "") { MessageBox.Show("The Folder Path TextBox cannot be empty.", "Warning"); return; } else if (txtCSVFilePath.Text == "") { MessageBox.Show("The File Path TextBox cannot be empty.", "Warning"); return; } else { //int intLengthOfFileName = txtCSVFilePath.Text.Trim().Length; //int intLastIndex = txtCSVFilePath.Text.Trim().LastIndexOf("\\"); //strCSVFile = txtCSVFilePath.Text.Trim().Substring(intLastIndex, intLengthOfFileName - intLastIndex); //strCSVFile = strCSVFile.Remove(0, 1).Trim(); // writeSchema(); ConnectCSV(); btnUpload.Enabled = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally {} }#endregion #region Button Insert Data //inja ma dadeh haye vared shode dar bank etelaati ra darj khahim kard private void btnUpload_Click(object sender, System.EventArgs e) { try { //sakhte connection sql //SqlConnection con1 = new SqlConnection(ReadConFile().Trim()); OleDbConnection con1 = new OleDbConnection(); con1.ConnectionString = @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\\Users\\KML Surani\\Documents\\ImportCSV.accdb;Persist Security Info=False;"; OleDbCommand cmd = new OleDbCommand(); // sakhte Dataset DataSet da = new DataSet(); // // dar vaghe dataset ra por mikonad,methode importcsv ra seda zade va ekhtesase datasete bazgashti be datasete jadid motabegh zir da = this.ConnectCSV(); //hala az data table dadeha ra jamavari karde va tak be tak dar database darj mikonim //dar ebteda dadeh i dar database nakhahad bod banabarin dade ra dar 2 sotoone aval darj karde sepas dadeh hara dar haman radif baraye //sotoon haye baghimande beroozresani mikonim //mantegh sadeh ast.'i' neshan dahande radif va 'j' neshandahande sotoon mibashad con1.Open(); cmd.Connection = con1; cmd.CommandType = CommandType.Text; for (int i = 0; i <= da.Tables["TheData"].Rows.Count - 1; i++) { cmd.CommandText = "Insert into tblImportCSV (Name,City) values('" + da.Tables["TheData"].Rows[i]["Name"] + "','" + da.Tables["TheData"].Rows[i]["City"] + "')"; //baraye beroozresanie statement , dar in band shoma niaz be yek shenasehe radif monhaser be fard darid //ma dar WHERE az ‘srno’ estefade mikonim. //cmd1.CommandText = "Update Test set " + da.Tables["Stocks"].Columns[j].ColumnName.Trim() + " = '" + da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j) + "' where srno =" + (i + 1); cmd.ExecuteNonQuery(); //cmd1.ExecuteNonQuery(); } con1.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { btnUpload.Enabled = false; } }#endregion #region Form Closing private void frmMain_Closing(object sender, System.ComponentModel.CancelEventArgs e) { try { Application.Exit(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally {} }#endregion private void openFileDialogCSVFilePath_FileOk(object sender, CancelEventArgs e) { } } }
به همین طریق شما همچنین می توانید در برنامه های تحت ویندوز فایل های اکسل به فرمت .xlsx را آپلود نمایید.
- C#.net
- 2k بازدید
- 2 تشکر