获取各个sheet的名字:
private DataTable GetSheetName()
{
string Path = openFileDialog1.FileName;
OleDbConnection objConn = null;
DataTable dt = null;
try
{
string connString =
"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt != null)
{
string[] sheetName = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
sheetName[i] = row["TABLE_NAME"].ToString();
i++;
}
}
}
catch(Exception ex)
{
MessageBox.Show("没有获取任何名字!原因:"+ ex.Message +"");
}
return dt;
}
读取数据:
private DataTable GetDT()
{
string Path = openFileDialog1.FileName;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
#region 获取sheet1的文件名
//Microsoft.Office.Interop.Excel.Application objExcelFile = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook objWorkBook;
//Microsoft.Office.Interop.Excel._Worksheet objImportSheet;
//objExcelFile.DisplayAlerts = false;
//objWorkBook = objExcelFile.Workbooks.Open(Path, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
//objImportSheet = (Microsoft.Office.Interop.Excel._Worksheet)objWorkBook.Sheets[1];
#endregion
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
DataTable dt = null;
try
{
conn.Open();
strExcel = "select * from [id$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
dt = ds.Tables[0];
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show("读取数据失败,原因:"+ ex.Message +"");
dt = null;
}
finally
{
conn.Close();
}
return dt;
}
有合并列时,读取数据:
private DataTable GetData( )
{
string path = openFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application objExcelFile = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook objWorkBook;
Microsoft.Office.Interop.Excel._Worksheet objImportSheet;
objExcelFile.DisplayAlerts = false;
objWorkBook = objExcelFile.Workbooks.Open(path, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
objImportSheet = (Microsoft.Office.Interop.Excel._Worksheet)objWorkBook.Sheets[1];
DataTable dtRenew = new DataTable();
for(int k=1;k<=4;k++)
{
dtRenew.Columns.Add(k.ToString());
}
try
{
for (int j = 2; j <= objImportSheet.Rows.Count; j++)
{
DataRow newRow = dtRenew.NewRow();
newRow[0] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 1]).Text.ToString().Trim();
newRow[1] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 2]).Text.ToString().Trim();
newRow[2] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 3]).Text.ToString().Trim();
newRow[3] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 4]).Text.ToString().Trim();
if (newRow[0].ToString().Length == 0 && newRow[1].ToString().Length == 0 && newRow[2].ToString().Length == 0 && newRow[3].ToString().Length == 0)
{
break;
}
dtRenew.Rows.Add(newRow);
}
}
catch (Exception ex)
{
MessageBox.Show("原因:" + ex.Message + "");
objExcelFile.Quit();
return null;
}
finally
{
objExcelFile.Quit();
objWorkBook = null;
objImportSheet = null;
objExcelFile = null;
}
return dtRenew;
}
杀掉死掉的 Excel 进程 :
private void Kill()
{
Process[] procs = Process.GetProcessesByName("Excel");
// 杀掉死掉的 Excel 进程
foreach (Process proc in procs)
{
proc.Kill();
}
this.label3.Visible = false;
}
总体:
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
this.textBox1.Text = openFileDialog1.FileName;
}
#region 获取各个sheet的名字
private DataTable GetSheetName()
{
string Path = openFileDialog1.FileName;
OleDbConnection objConn = null;
DataTable dt = null;
try
{
string connString =
"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt != null)
{
string[] sheetName = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
sheetName[i] = row["TABLE_NAME"].ToString();
i++;
}
}
}
catch(Exception ex)
{
MessageBox.Show("没有获取任何名字!原因:"+ ex.Message +"");
}
return dt;
}
#endregion
private DataTable GetDT()
{
string Path = openFileDialog1.FileName;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
#region 获取sheet1的文件名
//Microsoft.Office.Interop.Excel.Application objExcelFile = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook objWorkBook;
//Microsoft.Office.Interop.Excel._Worksheet objImportSheet;
//objExcelFile.DisplayAlerts = false;
//objWorkBook = objExcelFile.Workbooks.Open(Path, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
//objImportSheet = (Microsoft.Office.Interop.Excel._Worksheet)objWorkBook.Sheets[1];
#endregion
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
DataTable dt = null;
try
{
conn.Open();
strExcel = "select * from [id$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
dt = ds.Tables[0];
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show("读取数据失败,原因:"+ ex.Message +"");
dt = null;
}
finally
{
conn.Close();
}
return dt;
}
#region 数据读取的比较慢,因此在这里不用
private DataTable GetData( )
{
string path = openFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application objExcelFile = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook objWorkBook;
Microsoft.Office.Interop.Excel._Worksheet objImportSheet;
objExcelFile.DisplayAlerts = false;
objWorkBook = objExcelFile.Workbooks.Open(path, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
objImportSheet = (Microsoft.Office.Interop.Excel._Worksheet)objWorkBook.Sheets[1];
DataTable dtRenew = new DataTable();
for(int k=1;k<=4;k++)
{
dtRenew.Columns.Add(k.ToString());
}
try
{
for (int j = 2; j <= objImportSheet.Rows.Count; j++)
{
DataRow newRow = dtRenew.NewRow();
newRow[0] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 1]).Text.ToString().Trim();
newRow[1] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 2]).Text.ToString().Trim();
newRow[2] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 3]).Text.ToString().Trim();
newRow[3] = ((Microsoft.Office.Interop.Excel.Range)objImportSheet.Cells[j, 4]).Text.ToString().Trim();
if (newRow[0].ToString().Length == 0 && newRow[1].ToString().Length == 0 && newRow[2].ToString().Length == 0 && newRow[3].ToString().Length == 0)
{
break;
}
dtRenew.Rows.Add(newRow);
}
}
catch (Exception ex)
{
MessageBox.Show("原因:" + ex.Message + "");
objExcelFile.Quit();
return null;
}
finally
{
objExcelFile.Quit();
objWorkBook = null;
objImportSheet = null;
objExcelFile = null;
}
return dtRenew;
}
#endregion
private void Kill()
{
Process[] procs = Process.GetProcessesByName("Excel");
// 杀掉死掉的 Excel 进程
foreach (Process proc in procs)
{
proc.Kill();
}
this.label3.Visible = false;
}
private string sb()
{
this.label3.Visible = true;
string GetSB = "";
StringBuilder sb = new StringBuilder();
DataTable dt = this.GetDT();
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append("dn: cn=" + dt.Rows[i][0].ToString() + ",cn=users,dc=tsingtao,dc=com,dc=cn/r/n");
sb.Append("sn: " + dt.Rows[i][0].ToString() + "/r/n");
sb.Append("uid: " + dt.Rows[i][0].ToString() + "/r/n");
sb.Append("givenName: " + dt.Rows[i][2].ToString() + "/r/n");
sb.Append("shenfenzhengnumber_tsingtao: " + dt.Rows[i][3].ToString() + "/r/n");
sb.Append("userpassword: Since1903/r/n");
sb.Append("departmentNumber_tsingtao: " + dt.Rows[i][1].ToString() + "/r/n");
sb.Append("objectClass: person_tsingtao/r/n");
sb.Append("objectClass: inetorgperson/r/n");
sb.Append("objectClass: organizationalperson/r/n");
sb.Append("objectClass: top/r/n");
sb.Append("/r/n");
}
GetSB = sb.ToString();
}
else
{
MessageBox.Show("请选择Excel文件!");
}
return GetSB;
}
private void button2_Click(object sender, EventArgs e)
{
bool bResult = false;
try
{
if (this.textBox1.Text != null && this.textBox1.Text.Length > 0)
{
this.label3.Visible = true;
bResult = this.strResult();
if (bResult)
{
Kill();
this.label4.Visible = true;
}
else
{
Kill();
this.label4.Visible = false;
}
//GetTxt();
}
else
{
MessageBox.Show("请选择Excel文件!");
}
}
catch(Exception ex)
{
MessageBox.Show("转化失败,原因:"+ ex.Message +"");
}
}
#region 不用了,用下面的strResult
private void GetTxt()
{
string strGetTxt = this.sb();
try
{
if (this.textBox2.Text != null && this.textBox2.Text.Length > 0)
{
string Path = this.textBox2.Text;
FileStream fs = new FileStream("" + Path.ToString() + ".txt", FileMode.CreateNew, FileAccess.Write, FileShare.None);
StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
sw.WriteLine(strGetTxt.ToString());
sw.Close();
fs.Close();
}
else
{
MessageBox.Show("请选择保存目录!");
}
}
catch(Exception ex)
{
MessageBox.Show("转化失败,原因:"+ ex.Message +"");
}
}
#endregion
private bool strResult()
{
bool bResult = false;
string strGetTxt = this.sb();
try
{
if (this.textBox2.Text != null && this.textBox2.Text.Length > 0)
{
string Path = this.textBox2.Text;
FileStream fs = new FileStream("" + Path.ToString() + ".txt", FileMode.CreateNew, FileAccess.Write, FileShare.None);
StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
sw.WriteLine(strGetTxt.ToString());
sw.Close();
fs.Close();
bResult = true;
}
else
{
bResult = false;
MessageBox.Show("请选择保存目录!");
}
}
catch (Exception ex)
{
bResult = false;
MessageBox.Show("转化失败,原因:" + ex.Message + "");
}
return bResult;
}
private void button3_Click(object sender, EventArgs e)
{
saveFileDialog1.ShowDialog();
this.textBox2.Text = saveFileDialog1.FileName;
}
命名空间:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using System.Collections;
using System.Data.OleDb;