C # convert EXCEL Data to TXT files

C # the data format in excel before data conversion is as follows:
Device name specification model device No. uses the Department's fixed asset no.
Computer 1 IBM5660 10001 Management Department 100010001
Computer 2 IBM5661 10002 R & D department 100010002
Computer 3 IBM5662 10003 Management Department 100010003
C # format for converting data to TXT files:
"Device asset tags", "device name", "Computer 1", "type and model", "IBM5660", "device No.", "10001", "Department used ", "Management Department", "fixed asset no.", "100010001"
"Device asset tags", "device name", "computer 2", "type and model", "IBM5661", "device No.", "10002", "Department used ", "R & D department", "fixed asset number", "100010002"
"Device asset tags", "device name", "computer 3", "type and model", "IBM5662", "device No.", "10003", "Department used ", "Management Department", "fixed asset no.", "100010003"
End

Page design code:

Copy codeThe Code is as follows: namespace ExcelToTxt
{
Partial class Form1
{
/// <Summary>
/// Required designer variables.
/// </Summary>
Private System. ComponentModel. IContainer components = null;

/// <Summary>
/// Clear all resources in use.
/// </Summary>
/// <Param name = "disposing"> If the managed resource should be released, the value is true; otherwise, the value is false. </Param>
Protected override void Dispose (bool disposing)
{
If (disposing & (components! = Null ))
{
Components. Dispose ();
}
Base. Dispose (disposing );
}

# Region code generated by Windows Form Designer

/// <Summary>
/// The designer supports the required methods-do not
/// Use the code editor to modify the content of this method.
/// </Summary>
Private void InitializeComponent ()
{
This. dgvShow = new System. Windows. Forms. dview ();
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 conversion implementation code:

Copy codeThe Code is 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; // store data in EXCLE

Public Form1 ()
{
InitializeComponent ();
This. btnChange. Enabled = false; // initialize and set the control to unavailable.
}

/// <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 = ""; // stores the path of the opened file

OpenFileDialog selectFile = new OpenFileDialog ();

// Select the file to open
SelectFile. Filter = "Excel (*. xls) | *. xls ";
SelectFile. FilterIndex = 1;
SelectFile. DefaultExt = "xls ";
SelectFile. AddExtension = true;
SelectFile. RestoreDirectory = true;
SelectFile. Multiselect = false;

// Select a file
If (selectFile. ShowDialog () = DialogResult. OK)
{
ExcelFilePath = selectFile. FileName; // obtain the selected file path
}
Else
{
Return;
}

// Obtain the Control Data Source
Dt = GetExcelData (excelFilePath );

// Display data in the display control
ShowDataGridView ();

// Controls that set the conversion format are available
This. btnChange. Enabled = true;
}

/// <Summary>
/// This method converts the selected EXCEL file to a TXT file
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Private void btnChange_Click (object sender, EventArgs e)
{
String txtFilePath = ""; // stores the selected TXT file name
SaveFileDialog saveTxtFile = new SaveFileDialog ();

// Select the settings of the saved file
SaveTxtFile. Filter = "Text(.txt) | *. txt ";
SaveTxtFile. FilterIndex = 1;
SaveTxtFile. DefaultExt = "txt ";
SaveTxtFile. AddExtension = true;
SaveTxtFile. RestoreDirectory = true;
SaveTxtFile. OverwritePrompt = true;

// Select the folder for creating the file
If (saveTxtFile. ShowDialog () = DialogResult. OK)
{
TxtFilePath = saveTxtFile. FileName; // obtain the selected file path
}
Else
{
Return;
}

// Write the file in the DataTable to the txt file
Cursor. Current = Cursors. WaitCursor; // sets the mouse status
Int dtcols = dt. Columns. Count;
StringBuilder sbtxtdata = new StringBuilder (); // temporarily store each piece of data read from dt

// Create a new TXT file 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] = "\" "+" Detect Device asset tags "+" \ "" + ",";
Tempstr [1] = "\" "+" device name "+" \ "" + ",";
Tempstr [3] = "\" "+" type "+" \ "" + ",";
Tempstr [5] = "\" "+" device number "+" \ "" + ",";
Tempstr [7] = "\" "+" department "+" \ "" + ",";
Tempstr [9] = "\" "+" fixed asset no. "+" \ "" + ",";

// The format of tag 2 is written to the 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 data of the row to the buffer zone
Foreach (string str in tempstr)
{
Sbtxtdata. Append (str );
}
SwTxtFile. Write (sbtxtdata );

// Clear the data in the row
Sbtxtdata. Remove (0, sbtxtdata. Length );

// Clear 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];
// Write the Txt file in the format of tag 0 or 1
For (int rows = 0; rows <dt. Rows. Count; rows ++)
{
For (int cols = 0; cols <dt. Columns. Count; cols ++)
{
String temp = ""; // current time of temporary storage

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
Tempstr [2] = "\" "+ temp. Substring (4, 2) +" \ "" + ","; // month Truncation
Tempstr [3] = "\" "+ temp. Substring (6, 2) +" \ "" + ","; // capture a day
}
Else if (cols = 2)
{
Tempstr [4] = "\" "+ dt. Rows [rows] [cols] +" \ "" + "\ r \ n ";
}
}

// Write data of the row to the buffer zone
Foreach (string str in tempstr)
{
Sbtxtdata. Append (str );
}
SwTxtFile. Write (sbtxtdata );

// Clear the data in the row
Sbtxtdata. Remove (0, sbtxtdata. Length );

// Clear the newly added data in the array
For (int I = 0; I <dt. Columns. Count; I ++)
{
Tempstr [I] = "";
}
}
}

// Write data to the document
SwTxtFile. Write ("end ");
SwTxtFile. Flush ();
SwTxtFile. Close ();
FsTxtFile. Close ();

// Reset the mouse format
Cursor. Current = Cursors. Default;
MessageBox. Show ("file conversion successful! "," Prompt ",
MessageBoxButtons. OK, MessageBoxIcon. Information );
}

/// <Summary>
/// Obtain the data in the Excel file
/// </Summary>
/// <Param name = "path"> Excel file path </param>
/// <Returns> DataTable: load the data in the Excel file to the DataTable. </returns>
Private DataTable GetExcelData (string path)
{
// Confirm the connection string
String excelstr = "Provider = Microsoft. Jet. OLEDB.4.0;" + "Data Source =" + path + ";"
+ "Extended Properties = Excel 8.0 ;";

OleDbConnection excelConn = new OleDbConnection (excelstr );

// Open the data source connection
Try
{
If (excelConn. State = ConnectionState. Closed)
{
ExcelConn. Open ();
}
}
Catch (Exception ex)
{
MessageBox. Show ("An error occurred while connecting to the data source! "," Error ",
MessageBoxButtons. OK, MessageBoxIcon. Error );
Application. Exit ();
}
Finally
{
If (excelConn. State = ConnectionState. Open)
ExcelConn. Close ();
}

// Set the query command
OleDbDataAdapter myCommand = new OleDbDataAdapter ("SELECT * FROM [Sheet1 $]", excelConn );
DataSet ds = new DataSet ();

// Execute the command to query the EXCEL table
Try
{
MyCommand. Fill (ds, "excelTable ");
}
Catch (Exception ex)
{
MessageBox. Show ("the worksheet name of this Excel file is not [Sheet1 $]! "," Error ",
MessageBoxButtons. OK, MessageBoxIcon. Error );
Application. Exit ();
}
Finally
{
If (excelConn. State = ConnectionState. Closed)
{
ExcelConn. Close ();
}
}

// Determine whether data exists in the DataTable
If (ds. Tables ["excelTable"]. Rows. Count> 0)
{
Return ds. Tables ["excelTable"];
}
Else
{
MessageBox. Show ("no data in the Excel table is read! "," Error ",
MessageBoxButtons. OK, MessageBoxIcon. Error );
Return null;
}
}

/// <Summary>
/// Display the selected excel table data in the DataGridView
/// </Summary>
Private void ShowDataGridView ()
{
// Set the display control style
This. dgvShow. DefaultCellStyle. BackColor = Color. Beige;
This. dgvShow. DefaultCellStyle. Font = new Font ("Tahoma", 12 );

Maid style highlightCellStyle = new maid ();
HighlightCellStyle. BackColor = Color. Red;

Maid style ();
CurrencyCellStyle. Format = "C ";
CurrencyCellStyle. ForeColor = Color. Green;

// Set the data source of the display control
DgvShow. DataSource = dt;
}

}
}