Convertir .XLS en tabs séparés .TXT

Vous pouvez lire ce file XLS facilement via OleDb (fournisseur ADO.NET) et créer un object StreamWriter pour écrire des données dans le file Text / TSV.

 using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } en utilisant (OleDbConnection cn = new OleDbConnection ()) using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } en utilisant (OleDbCommand cmd = new OleDbCommand ()) using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } cn.ConnectionSsortingng = @ "Fournisseur = Microsoft.Jet.OLEDB.4.0; Source de données =" + @ "C: \ path \ file.xls" + "; properties étendues = \" Excel 8.0; HDR = Oui; IMEX = 1 \ ";"; using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } cmd.Connection = cn; using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } cmd.CommandText = "select * from [Sheet1 $]"; using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } en utilisant (OleDbDataAdapter adp = new OleDbDataAdapter (cmd)) using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } DataTable dt = new DataTable (); using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } adp.Fill (dt); using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } en utilisant (StreamWriter wr = new StreamWriter (@ "C: \ path \ flie.tsv")) using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } foreach (ligne DataRow dans dt.Rows) using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } wr.WriteLine (ligne [0] + "\ t" + rangée [1]); using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionSsortingng = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\path\file.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; cmd.Connection = cn; cmd.CommandText = "select * from [Sheet1$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adp.Fill(dt); using (StreamWriter wr = new StreamWriter(@"C:\path\flie.tsv")) { foreach (DataRow row in dt.Rows) { wr.WriteLine(row[0] + "\t" + row[1]); } } } } } 

L'utilisation d'OleDb peut être délicate et peut causer des problèmes en fonction de la version d'excel sur laquelle la feuille de calcul a été créée. Par exemple, l'exemple ci-dessus fonctionnerait avec .xls, mais pas avec .xlsx. Vous devriez changer votre string de connection de "Microsoft.Jet.OLEDB.4.0" à "Microsoft.ACE.OLEDB.12.0" afin de compenser. Cependant, il ne serait toujours pas universel pour toutes les feuilles Excel. J'utiliserais Microsoft.Office.Interop.Excel comme suit

 Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); La feuille de calcul Microsoft.Office.Interop.Excel.Worksheet; Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); myExcel = new Microsoft.Office.Interop.Excel.Application (); Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); myExcel.Workbooks.Open (inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); myWorkbook = myExcel.ActiveWorkbook; Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); feuille de calcul = (Microsoft.Office.Interop.Excel.Worksheet) myWorkbook.Worksheets [1]; Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); myWorkbook.SaveAs (outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); myWorkbook.Close (false, Missing.Value, Missing.Value); Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; myExcel = new Microsoft.Office.Interop.Excel.Application(); myExcel.Workbooks.Open(inputFileName.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook = myExcel.ActiveWorkbook; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkbook.Worksheets[1]; myWorkbook.SaveAs(outputFileName.txt, Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows, Missing.Value, Missing.Value, Missing.Value, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkbook.Close(false, Missing.Value, Missing.Value); myExcel.Quit(); 

Pas de bouclage, pas de bs. Il suffit de copyr, coller et modifier vos noms de files. Le seul problème que j'ai vu est parfois myExcel.Quit () ne semble pas fonctionner correctement et les instances d'excel restnt ouvertes je l'arrière-plan. La façon dont il s'agit est de tuer manuellement le process par votre programme, mais je vais l'save pour une discussion différente.