首页 > 代码库 > 用C#写一个Excel转Txt的小工具
用C#写一个Excel转Txt的小工具
using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.Linq; using System.Text; using System.Threading.Tasks; using System.IO; using Microsoft.Office.Core; using Excel = Microsoft.Office.Interop.Excel; namespace excel_test { class Program { // 获得字段的实际最大长度 static int GetMaxLength(DataTable dt, string captionName) { DataColumn maxLengthColumn = new DataColumn(); maxLengthColumn.ColumnName = "MaxLength"; maxLengthColumn.Expression = String.Format("len(convert({0},‘System.String‘))", captionName); dt.Columns.Add(maxLengthColumn); object maxLength = dt.Compute("max(MaxLength)", "true"); dt.Columns.Remove(maxLengthColumn); return Convert.ToInt32(maxLength); } static void convertExcelToTxt(string inputFile, string outputPath) { string newFileName = Path.GetFileNameWithoutExtension(inputFile) + ".txt"; string newFile = outputPath + "\\" +newFileName; Console.WriteLine("Convert file[{0}] to [{1}]", inputFile, newFile); FileStream fs = new FileStream(newFile, FileMode.OpenOrCreate); StreamWriter sw = new StreamWriter(fs); var conn = new OleDbConnection(); conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source={0}" + ";Extended Properties=\"Excel 12.0 Xml;HDR=No\"", inputFile); conn.Open(); OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn); var ds = new DataSet(); da.Fill(ds); var tb1 = ds.Tables[0]; foreach (DataRow row in tb1.Rows) { for (int j = 0; j < tb1.Columns.Count; ++j) { DataColumn col = tb1.Columns[j]; string fmt = String.Format("{0}0,-{1}{2}", "{", GetMaxLength(tb1, col.Caption) + 2, "}"); sw.Write(fmt, row[j]); Console.Write(fmt, row[j]); } sw.WriteLine(); Console.WriteLine(); } sw.Close(); } static int Main(string[] args) { if(args.Length < 1) { Console.WriteLine("No input file arg."); return -1; } string inputFile = args[0]; if (!File.Exists(inputFile)) { Console.WriteLine("No input file exist, {0}", inputFile); return -1; } string fileName = Path.GetFileName(inputFile); string outputPath; if (args.Length > 1) { outputPath = args[1]; } else { outputPath = Path.GetDirectoryName(inputFile); } if (!Directory.Exists(outputPath)) { Console.WriteLine("Output directory not exist, {0}.", outputPath); return -1; } convertExcelToTxt(inputFile, outputPath); return 0; } } }
参考:
1. 【C#读取Excel 2010】,汪宇杰
2. 【C#格式化字符串中转义大括号“{}”】, ^_^肥仔John
3. 【浅谈 DataSet 的用法】
用C#写一个Excel转Txt的小工具
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。