C # Implementation of Excel data to TXT document conversion _ Practical skills

The data format in Excel before C # Data conversion is as follows:
Equipment name Specification Model equipment number use Department fixed asset number
Computer 1 IBM5660 10001 Management Department 100010001
Computer 2 IBM5661 10002 Research and Development Department 100010002
Computer 3 IBM5662 10003 Management Department 100010003
C # Data conversion to TXT document format:
"Test equipment Asset Tag", "Equipment name", "Computer 1", "Specification Model", "IBM5660", "Equipment number", "10001", "use department", "management department", "fixed asset number", "100010001"
"Testing equipment Asset Tag", "Equipment name", "Computer 2", "Specification Model", "IBM5661", "Equipment number", "10002", "use department", "Research and Development department", "fixed asset number", "100010002"
"Test equipment Asset Tag", "Equipment name", "Computer 3", "Specification Model", "IBM5662", "Equipment number", "10003", "use department", "management department", "fixed asset number", "100010003"
End

Page Design Code:

Copy Code code as follows:

Namespace Exceltotxt


{


Partial class Form1


{


<summary>


The required designer variable.


</summary>


Private System.ComponentModel.IContainer components = null;





<summary>


Clean up all resources that are in use.


</summary>


<param name= "disposing" > if the managed resource should be freed, true; otherwise, false. </param>


protected override void Dispose (bool disposing)


{


if (disposing && (components!= null))


{


Components. Dispose ();


}


Base. Dispose (disposing);


}





Code generated #region the Windows forms Designer





<summary>


Designer supports the required methods-No


Use the Code Editor to modify the contents of this method.


</summary>


private void InitializeComponent ()


{


This.dgvshow = new System.Windows.Forms.DataGridView ();


This.btnselect = new System.Windows.Forms.Button ();


This.btnchange = new System.Windows.Forms.Button ();


((System.ComponentModel.ISupportInitialize) (this.dgvshow)). BeginInit ();


This. SuspendLayout ();


//


Dgvshow


//


This.dgvShow.AllowUserToAddRows = false;


This.dgvShow.AllowUserToDeleteRows = false;


This.dgvShow.AllowUserToResizeRows = false;


This.dgvShow.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize ;


This.dgvShow.Dock = System.Windows.Forms.DockStyle.Top;


This.dgvShow.Location = new System.Drawing.Point (0, 0);


This.dgvShow.Name = "Dgvshow";


This.dgvShow.RowTemplate.Height = 23;


This.dgvShow.Size = new System.Drawing.Size (885, 600);


This.dgvShow.TabIndex = 0;


//


Btnselect


//


This.btnSelect.Location = new System.Drawing.Point (202, 611);


This.btnSelect.Name = "Btnselect";


This.btnSelect.Size = new System.Drawing.Size (148, 23);


This.btnSelect.TabIndex = 1;


This.btnSelect.Text = "Select Excel file";


This.btnSelect.UseVisualStyleBackColor = true;


This.btnSelect.Click + = new System.EventHandler (This.btnselect_click);


//


Btnchange


//


This.btnChange.Location = new System.Drawing.Point (403, 611);


This.btnChange.Name = "Btnchange";


This.btnChange.Size = new System.Drawing.Size (152, 23);


This.btnChange.TabIndex = 2;


This.btnChange.Text = "Convert to txt document";


This.btnChange.UseVisualStyleBackColor = true;


This.btnChange.Click + = new System.EventHandler (This.btnchange_click);


//


Form1


//


This. Autoscaledimensions = new System.Drawing.SizeF (6F, 12F);


This. AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;


This. ClientSize = new System.Drawing.Size (885, 646);


This. Controls.Add (This.btnchange);


This. Controls.Add (This.btnselect);


This. Controls.Add (this.dgvshow);


This. Name = "Form1";


This. Text = "File Conversion";


((System.ComponentModel.ISupportInitialize) (this.dgvshow)). EndInit ();


This. ResumeLayout (FALSE);





}





#endregion





Private System.Windows.Forms.DataGridView dgvshow;


Private System.Windows.Forms.Button Btnselect;


Private System.Windows.Forms.Button Btnchange;


}


}

C # Data Transformation Implementation code:

Copy Code code as follows:

Using System;


Using System.Collections.Generic;


Using System.ComponentModel;


Using System.Data;


Using System.Data.OleDb;


Using System.Drawing;


Using System.Text;


Using System.Windows.Forms;


Using System.IO;








Namespace Exceltotxt


{


public partial class Form1:form


{


Private DataTable DT; Storing data in a excle





Public Form1 ()


{


InitializeComponent ();


this.btnChange.Enabled = false;//Initialization settings control is not available


}








<summary>


This method opens an Excel file


</summary>


<param name= "Sender" ></param>


<param name= "E" ></param>


private void Btnselect_click (object sender, EventArgs e)


{


String excelfilepath = ""; Store the path to an open file





OpenFileDialog selectfile = new OpenFileDialog ();





Select the Open File settings


Selectfile.filter = "Excel (*.xls) |*.xls";


Selectfile.filterindex = 1;


Selectfile.defaultext = "xls";


Selectfile.addextension = true;


Selectfile.restoredirectory = true;


Selectfile.multiselect = false;





Select File


if (selectfile.showdialog () = = DialogResult.OK)


{


Excelfilepath = selectfile.filename;//Get the selected file path


}


Else


{


Return


}





Get the data source for the control


DT = Getexceldata (Excelfilepath);





Displaying data in a display control


Showdatagridview ();





Controls that set the conversion format are available


This.btnChange.Enabled = true;


}








<summary>


This method converts the selected Excel file into a txt document


</summary>


<param name= "Sender" ></param>


<param name= "E" ></param>


private void Btnchange_click (object sender, EventArgs e)


{


String txtfilepath = "";//Store the file name of the selected TXT document


SaveFileDialog Savetxtfile = new SaveFileDialog ();





Select the saved File settings


Savetxtfile.filter = "Text (. txt) |*.txt";


Savetxtfile.filterindex = 1;


Savetxtfile.defaultext = "txt";


Savetxtfile.addextension = true;


Savetxtfile.restoredirectory = true;


Savetxtfile.overwriteprompt = true;





Select a folder to create a file


if (savetxtfile.showdialog () = = DialogResult.OK)


{


Txtfilepath = Savetxtfile.filename; Get the selected file path


}


Else


{


Return


}





Write files in a DataTable to a TXT document


Cursor.current = Cursors.waitcursor; Set Mouse state


int dtcols = dt. Columns.count;


StringBuilder sbtxtdata = new StringBuilder ();; Temporarily stores every piece of data read from DT








Create a new TXT document first


FileStream fstxtfile = new FileStream (Txtfilepath, FileMode.CreateNew, FileAccess.Write);


StreamWriter swtxtfile = new StreamWriter (Fstxtfile, encoding.getencoding ("gb2312"));





if (Dtcols > 3)


{


string[] TempStr = new STRING[11];





Set a fixed value


Tempstr[0] = "\" + "testing equipment Asset Tag" + "" + ",";


TEMPSTR[1] = "\" + "Device name" + "\" + ",";


TEMPSTR[3] = "" "+" specification Model "+" "+", ";


TEMPSTR[5] = "\" + "equipment number" + "\" + ",";


TEMPSTR[7] = "\" + "use department" + "\" + ",";


TEMPSTR[9] = "\" + "Fixed asset number" + "\" + ",";





The format of label 2 is written to TXT document


for (int rows = 0; rows < dt. Rows.Count; rows++)


{


for (int cols = 0; cols < dt. Columns.count; cols++)


{


int tempindex = 2* (cols+1);


Tempstr[tempindex] = "\" + dt. Rows[rows][cols]. ToString () + "\";


}





TEMPSTR[2] = tempstr[2] + ",";


TEMPSTR[4] = tempstr[4] + ",";


TEMPSTR[6] = tempstr[6] + ",";


TEMPSTR[8] = Tempstr[8] + ",";


TEMPSTR[10] = tempstr[10] + "\ r \ n";





Write Bank data to buffer


foreach (String str in TEMPSTR)


{


Sbtxtdata. Append (str);


}


Swtxtfile.write (Sbtxtdata);





Clear the data in the bank


Sbtxtdata. Remove (0, Sbtxtdata. Length);





Clears the newly added data in the array


for (int i = 0; i < dt. Columns.count; i++)


{


int tempindex = 2* (i+1);


Tempstr[tempindex] = "";


}


}


}


Else


{


string[] TempStr = new STRING[5];


Label 0 or 1 in TXT document format


for (int rows = 0; rows < dt. Rows.Count; rows++)


{


for (int cols = 0; cols < dt. Columns.count; cols++)


{


The string temp = "";//Temporary store current time





if (cols = 0)


{


Tempstr[0] = "\" + dt. Rows[rows][cols] + "\" "+", ";


}


else if (cols = 1)


{


temp = dt. Rows[rows][cols]. ToString ();


TEMPSTR[1] = "\" + temp. Substring (0, 4) + "\" "+", "; Year of interception


TEMPSTR[2] = "\" + temp. Substring (4, 2) + "\" "+", "; Intercept month


TEMPSTR[3] = "\" + temp. Substring (6, 2) + "\" "+", "; Interception Day


}


else if (cols = 2)


{


TEMPSTR[4] = "\" + dt. Rows[rows][cols] + "\" + "\ r \ n";


}


}





Write Bank data to buffer


foreach (String str in TEMPSTR)


{


Sbtxtdata. Append (str);


}


Swtxtfile.write (Sbtxtdata);





Clear the data in the bank


Sbtxtdata. Remove (0, Sbtxtdata. Length);





Clears the newly added data in the array


for (int i = 0; i < dt. Columns.count; i++)


{


Tempstr[i] = "";


}


}


}





Writing data to a document


Swtxtfile.write ("End");


Swtxtfile.flush ();


Swtxtfile.close ();


Fstxtfile.close ();





Reset Mouse Format


Cursor.current = Cursors.Default;


MessageBox.Show ("File conversion succeeded!", "Prompt",


MessageBoxButtons.OK, MessageBoxIcon.Information);


}








<summary>


Get the data in an Excel file


</summary>


Path </param> <param name= "path" >excel file


<returns>datatable: Load data from Excel files into a DataTable </returns>


Private DataTable Getexceldata (string path)


{


Connection string OK


String excelstr = "Provider = microsoft.jet.oledb.4.0;" + "Data source=" + path + ";"


+ "Extended Properties = Excel 8.0;";





OleDbConnection excelconn = new OleDbConnection (EXCELSTR);





To open a data source connection


Try


{


if (excelconn.state = = connectionstate.closed)


{


Excelconn.open ();


}


}


catch (Exception ex)


{


MessageBox.Show ("Open Data source connection failed!") "," Error ",


MessageBoxButtons.OK, Messageboxicon.error);


Application.exit ();


}


Finally


{


if (excelconn.state = = ConnectionState.Open)


Excelconn.close ();


}





Set query command


OleDbDataAdapter mycommand = new OleDbDataAdapter ("SELECT * from [sheet1$]", excelconn);


DataSet ds = new DataSet ();





To execute the query Excel table command


Try


{


Mycommand.fill (ds, "exceltable");


}


catch (Exception ex)


{


MessageBox.Show ("The name of the worksheet for this Excel file is not [sheet1$]!", "Error",


MessageBoxButtons.OK, Messageboxicon.error);


Application.exit ();


}


Finally


{


if (excelconn.state = = connectionstate.closed)


{


Excelconn.close ();


}


}





Determine if there is data in the DataTable


if (ds. tables["Exceltable"]. Rows.Count > 0)


{


Return DS. tables["Exceltable"];


}


Else


{


MessageBox.Show ("Not reading data from Excel tables!", "Error",


MessageBoxButtons.OK, Messageboxicon.error);


return null;


}


}








<summary>


The data in the selected Excel table is now DataGridView.


</summary>


private void Showdatagridview ()


{


To set the style of a display control


This.dgvShow.DefaultCellStyle.BackColor = Color.beige;


This.dgvShow.DefaultCellStyle.Font = new Font ("Tahoma", 12);





DataGridViewCellStyle Highlightcellstyle = new DataGridViewCellStyle ();


Highlightcellstyle.backcolor = color.red;





DataGridViewCellStyle Currencycellstyle = new DataGridViewCellStyle ();


Currencycellstyle.format = "C";


Currencycellstyle.forecolor = Color.green;





Set the data source for a display control


Dgvshow.datasource = DT;


}





}


}