C implements the conversion of EXCEL data to TXT documents
- 2020-05-26 08:16:15
- OfStack
The data format in excel before the C# data conversion is as follows:
Equipment name, specification, model, equipment number, department's fixed asset number
Computer 1 IBM5660 10001 management department 100010001
Computer 2 IBM5661 10002 r & d department 100010002
Computer 3 IBM5662 10003 administration department 100010003
Conversion of C# data to TXT document format:
Name "testing equipment asset tag", "equipment", "computer 1", "specifications", "IBM5660", "equipment serial number", "10001", "use the department", "management", "number of fixed assets", "100010001"
Name "testing equipment asset tag", "equipment", "computer 2", "specifications", "IBM5661", "equipment serial number", "10002", "use the department", "research and development department", "number of fixed assets", "100010002"
Name "testing equipment asset tag", "equipment", "computer 3", "specifications", "IBM5662", "equipment serial number", "10003", "use the department", "management", "number of fixed assets", "100010003"
end
Page design code:
namespace ExcelToTxt
{
partial class Form1
{
/// <summary>
/// Required designer variables.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up all resources in use.
/// </summary>
/// <param name="disposing"> If the managed resource should be released, is true ; Otherwise, for false . </param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form designer generated code
/// <summary>
/// The designer supports the required methods - Don't
/// 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 = " choose 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 txt The 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:
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; // storage EXCLE The data in the
public Form1()
{
InitializeComponent();
this.btnChange.Enabled = false;// Initializes the Settings control to be unavailable
}
/// <summary>
/// This method opens 1 a Excel file
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSelect_Click(object sender, EventArgs e)
{
string excelFilePath = ""; // The path to store the open file
OpenFileDialog selectFile = new OpenFileDialog();
// Select open file Settings
selectFile.Filter = "Excel(*.xls)|*.xls";
selectFile.FilterIndex = 1;
selectFile.DefaultExt = "xls";
selectFile.AddExtension = true;
selectFile.RestoreDirectory = true;
selectFile.Multiselect = false;
// Select the file
if (selectFile.ShowDialog() == DialogResult.OK)
{
excelFilePath = selectFile.FileName;// Gets the selected file path
}
else
{
return;
}
// Gets the data source for the control
dt = GetExcelData(excelFilePath);
// Displays data in a display control
ShowDataGridView();
// Controls that set the conversion format are available
this.btnChange.Enabled = true;
}
/// <summary>
/// The method will be selected EXCEL File conversion to TXT The document
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnChange_Click(object sender, EventArgs e)
{
string txtFilePath = "";// storage-selected TXT File name of the 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 the folder to create the file
if (saveTxtFile.ShowDialog() == DialogResult.OK)
{
txtFilePath = saveTxtFile.FileName; // Gets the selected file path
}
else
{
return;
}
// will DataTable Is written to txt In the document
Cursor.Current = Cursors.WaitCursor; // Set mouse state
int dtcols = dt.Columns.Count;
StringBuilder sbtxtdata = new StringBuilder(); ; // Temporary storage from dt Each read in 1 The data
// First create 1 A new one TXT The document
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] = "\"" + " Test equipment asset label " + "\"" + ",";
tempstr[1] = "\"" + " Device name " + "\"" + ",";
tempstr[3] = "\"" + " specifications " + "\"" + ",";
tempstr[5] = "\"" + " Equipment serial number " + "\"" + ",";
tempstr[7] = "\"" + " Use the department " + "\"" + ",";
tempstr[9] = "\"" + " Fixed asset no. " + "\"" + ",";
// The label 2 Format write to Txt The 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";
// Writes the line data to the buffer
foreach (string str in tempstr)
{
sbtxtdata.Append(str);
}
swTxtFile.Write(sbtxtdata);
// Empty the data in the bank
sbtxtdata.Remove(0, sbtxtdata.Length);
// Empty the newly added data from the array
for (int i = 0; i < dt.Columns.Count; i++)
{
int tempindex = 2*(i+1);
tempstr[tempindex] = "";
}
}
}
else
{
string[] tempstr = new string[5];
// The label 0 or 1 Format write to Txt The document
for (int rows = 0; rows < dt.Rows.Count; rows++)
{
for (int cols = 0; cols < dt.Columns.Count; cols++)
{
string temp = "";// Temporarily stores the 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) + "\"" + ","; // Interception of years
tempstr[2] = "\"" + temp.Substring(4, 2) + "\"" + ","; // Interception of month
tempstr[3] = "\"" + temp.Substring(6, 2) + "\"" + ","; // Intercept,
}
else if (cols == 2)
{
tempstr[4] = "\"" + dt.Rows[rows][cols] + "\"" + "\r\n";
}
}
// Writes the line data to the buffer
foreach (string str in tempstr)
{
sbtxtdata.Append(str);
}
swTxtFile.Write(sbtxtdata);
// Empty the data in the bank
sbtxtdata.Remove(0, sbtxtdata.Length);
// Empty the newly added data from the array
for (int i = 0; i < dt.Columns.Count; i++)
{
tempstr[i] = "";
}
}
}
// Write the data to the document
swTxtFile.Write("end");
swTxtFile.Flush();
swTxtFile.Close();
fsTxtFile.Close();
// Reformat the mouse
Cursor.Current = Cursors.Default;
MessageBox.Show(" File conversion successful !", " prompt ",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// To obtain Excel Data in file
/// </summary>
/// <param name="path">Excel Path to file </param>
/// <returns>DataTable: will Excel The file's data is loaded into DataTable In the </returns>
private DataTable GetExcelData(string path)
{
// Concatenation string determination
string excelstr = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source= " + path + " ;"
+ " Extended Properties = Excel 8.0;";
OleDbConnection excelConn = new OleDbConnection(excelstr);
// Open 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();
// Execute the query EXCEL The orders of the table
try
{
myCommand.Fill(ds, "excelTable");
}
catch (Exception ex)
{
MessageBox.Show(" the Excel The name of the file's worksheet is not [Sheet1$]!", " error ",
MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
finally
{
if (excelConn.State == ConnectionState.Closed)
{
excelConn.Close();
}
}
// judge DataTable Is there any data in
if (ds.Tables["excelTable"].Rows.Count > 0)
{
return ds.Tables["excelTable"];
}
else
{
MessageBox.Show(" Did not read Excel Table data !", " error ",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
/// <summary>
/// Will choose the excel The data in the table is now DataGridView In the
/// </summary>
private void ShowDataGridView()
{
// Sets the style of the 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;
// Sets the data source for the display control
dgvShow.DataSource = dt;
}
}
}