From 3b79158563fd2f7f72d502067f178d60c31b2c28 Mon Sep 17 00:00:00 2001 From: chai Date: Tue, 26 Oct 2021 12:20:33 +0800 Subject: +xlsxToCSV --- Tools/XlsxToCsv/.gitignore | 196 ++++++++++++++++++++++++++++ Tools/XlsxToCsv/App.config | 6 + Tools/XlsxToCsv/Excel.dll | Bin 0 -> 77824 bytes Tools/XlsxToCsv/ExcelConvert.cs | 80 ++++++++++++ Tools/XlsxToCsv/ICSharpCode.SharpZipLib.dll | Bin 0 -> 192512 bytes Tools/XlsxToCsv/ILMerge.bat | 3 + Tools/XlsxToCsv/Program.cs | 101 ++++++++++++++ Tools/XlsxToCsv/Properties/AssemblyInfo.cs | 36 +++++ Tools/XlsxToCsv/README.md | 42 ++++++ Tools/XlsxToCsv/test.xlsx | Bin 0 -> 16053 bytes Tools/XlsxToCsv/xlsxToCsv.csproj | 70 ++++++++++ Tools/XlsxToCsv/xlsxToCsv.exe | Bin 0 -> 254976 bytes Tools/XlsxToCsv/xlsxToCsv.sln | 22 ++++ 13 files changed, 556 insertions(+) create mode 100644 Tools/XlsxToCsv/.gitignore create mode 100644 Tools/XlsxToCsv/App.config create mode 100644 Tools/XlsxToCsv/Excel.dll create mode 100644 Tools/XlsxToCsv/ExcelConvert.cs create mode 100644 Tools/XlsxToCsv/ICSharpCode.SharpZipLib.dll create mode 100644 Tools/XlsxToCsv/ILMerge.bat create mode 100644 Tools/XlsxToCsv/Program.cs create mode 100644 Tools/XlsxToCsv/Properties/AssemblyInfo.cs create mode 100644 Tools/XlsxToCsv/README.md create mode 100644 Tools/XlsxToCsv/test.xlsx create mode 100644 Tools/XlsxToCsv/xlsxToCsv.csproj create mode 100644 Tools/XlsxToCsv/xlsxToCsv.exe create mode 100644 Tools/XlsxToCsv/xlsxToCsv.sln (limited to 'Tools/XlsxToCsv') diff --git a/Tools/XlsxToCsv/.gitignore b/Tools/XlsxToCsv/.gitignore new file mode 100644 index 0000000..57a1574 --- /dev/null +++ b/Tools/XlsxToCsv/.gitignore @@ -0,0 +1,196 @@ +## Ignore Visual Studio temporary files, build results, and +## files generated by popular Visual Studio add-ons. + +# User-specific files +*.suo +*.user +*.userosscache +*.sln.docstates + +# User-specific files (MonoDevelop/Xamarin Studio) +*.userprefs + +# Build results +[Dd]ebug/ +[Dd]ebugPublic/ +[Rr]elease/ +[Rr]eleases/ +x64/ +x86/ +build/ +bld/ +[Bb]in/ +[Oo]bj/ + +# Visual Studo 2015 cache/options directory +.vs/ + +# MSTest test Results +[Tt]est[Rr]esult*/ +[Bb]uild[Ll]og.* + +# NUNIT +*.VisualState.xml +TestResult.xml + +# Build Results of an ATL Project +[Dd]ebugPS/ +[Rr]eleasePS/ +dlldata.c + +*_i.c +*_p.c +*_i.h +*.ilk +*.meta +*.obj +*.pch +*.pdb +*.pgc +*.pgd +*.rsp +*.sbr +*.tlb +*.tli +*.tlh +*.tmp +*.tmp_proj +*.log +*.vspscc +*.vssscc +.builds +*.pidb +*.svclog +*.scc + +# Chutzpah Test files +_Chutzpah* + +# Visual C++ cache files +ipch/ +*.aps +*.ncb +*.opensdf +*.sdf +*.cachefile + +# Visual Studio profiler +*.psess +*.vsp +*.vspx + +# TFS 2012 Local Workspace +$tf/ + +# Guidance Automation Toolkit +*.gpState + +# ReSharper is a .NET coding add-in +_ReSharper*/ +*.[Rr]e[Ss]harper +*.DotSettings.user + +# JustCode is a .NET coding addin-in +.JustCode + +# TeamCity is a build add-in +_TeamCity* + +# DotCover is a Code Coverage Tool +*.dotCover + +# NCrunch +_NCrunch_* +.*crunch*.local.xml + +# MightyMoose +*.mm.* +AutoTest.Net/ + +# Web workbench (sass) +.sass-cache/ + +# Installshield output folder +[Ee]xpress/ + +# DocProject is a documentation generator add-in +DocProject/buildhelp/ +DocProject/Help/*.HxT +DocProject/Help/*.HxC +DocProject/Help/*.hhc +DocProject/Help/*.hhk +DocProject/Help/*.hhp +DocProject/Help/Html2 +DocProject/Help/html + +# Click-Once directory +publish/ + +# Publish Web Output +*.[Pp]ublish.xml +*.azurePubxml +# TODO: Comment the next line if you want to checkin your web deploy settings +# but database connection strings (with potential passwords) will be unencrypted +*.pubxml +*.publishproj + +# NuGet Packages +*.nupkg +# The packages folder can be ignored because of Package Restore +**/packages/* +# except build/, which is used as an MSBuild target. +!**/packages/build/ +# Uncomment if necessary however generally it will be regenerated when needed +#!**/packages/repositories.config + +# Windows Azure Build Output +csx/ +*.build.csdef + +# Windows Store app package directory +AppPackages/ + +# Others +*.[Cc]ache +ClientBin/ +[Ss]tyle[Cc]op.* +~$* +*~ +*.dbmdl +*.dbproj.schemaview +*.pfx +*.publishsettings +node_modules/ +bower_components/ + +# RIA/Silverlight projects +Generated_Code/ + +# Backup & report files from converting an old project file +# to a newer Visual Studio version. Backup files are not needed, +# because we have git ;-) +_UpgradeReport_Files/ +Backup*/ +UpgradeLog*.XML +UpgradeLog*.htm + +# SQL Server files +*.mdf +*.ldf + +# Business Intelligence projects +*.rdl.data +*.bim.layout +*.bim_*.settings + +# Microsoft Fakes +FakesAssemblies/ + +# Node.js Tools for Visual Studio +.ntvs_analysis.dat + +# Visual Studio 6 build log +*.plg + +# Visual Studio 6 workspace options file +*.opt diff --git a/Tools/XlsxToCsv/App.config b/Tools/XlsxToCsv/App.config new file mode 100644 index 0000000..74ade9d --- /dev/null +++ b/Tools/XlsxToCsv/App.config @@ -0,0 +1,6 @@ + + + + + + diff --git a/Tools/XlsxToCsv/Excel.dll b/Tools/XlsxToCsv/Excel.dll new file mode 100644 index 0000000..6dfba44 Binary files /dev/null and b/Tools/XlsxToCsv/Excel.dll differ diff --git a/Tools/XlsxToCsv/ExcelConvert.cs b/Tools/XlsxToCsv/ExcelConvert.cs new file mode 100644 index 0000000..129bb66 --- /dev/null +++ b/Tools/XlsxToCsv/ExcelConvert.cs @@ -0,0 +1,80 @@ +๏ปฟusing Excel; +using System.Collections.Generic; +using System.Data; +using System.IO; +using System.Text.RegularExpressions; + +namespace xlsxToCsv +{ + public class ExcelConvert + { + private DataSet result = new DataSet(); + + public bool Convert(string src, string tar) + { + GetExcelData(src); + return ConverToCSV(tar); + } + + private void GetExcelData(string file) + { + if (file.EndsWith(".xlsx")) + { + // Reading from a binary Excel file (format; *.xlsx) + FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read); + IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); + result = excelReader.AsDataSet(); + excelReader.Close(); + } + + if (file.EndsWith(".xls")) + { + // Reading from a binary Excel file ('97-2003 format; *.xls) + FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read); + IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream); + result = excelReader.AsDataSet(); + excelReader.Close(); + } + + List items = new List(); + for (int i = 0; i < result.Tables.Count; i++) + items.Add(result.Tables[i].TableName.ToString()); + } + + private bool ConverToCSV(string toFilePath) + { + int index = 0; + // sheets in excel file becomes tables in dataset + // result.Tables[0].TableName.ToString(); // to get sheet name (table name) + + string a = ""; + int row_no = 0; + if (result.Tables.Count == 0) + { + return false; + } + while (row_no < result.Tables[index].Rows.Count) + { + for (int i = 0; i < result.Tables[index].Columns.Count; i++) + { + if (i - 1 < result.Tables[index].Columns.Count) + { + a += result.Tables[index].Rows[row_no][i].ToString() + ","; + } else + { + a += result.Tables[index].Rows[row_no][i].ToString(); + } + } + row_no++; + a = Regex.Replace(a, ",*$", ""); + a += "\n"; + } + string output = toFilePath; + StreamWriter csv = new StreamWriter(@output, false); + csv.Write(a); + csv.Close(); + + return true; + } + } +} \ No newline at end of file diff --git a/Tools/XlsxToCsv/ICSharpCode.SharpZipLib.dll b/Tools/XlsxToCsv/ICSharpCode.SharpZipLib.dll new file mode 100644 index 0000000..e829ebf Binary files /dev/null and b/Tools/XlsxToCsv/ICSharpCode.SharpZipLib.dll differ diff --git a/Tools/XlsxToCsv/ILMerge.bat b/Tools/XlsxToCsv/ILMerge.bat new file mode 100644 index 0000000..4c94669 --- /dev/null +++ b/Tools/XlsxToCsv/ILMerge.bat @@ -0,0 +1,3 @@ +@rem exeฦฤภฯฟก dllภป วีฤกฑโ +@rem Download http://www.microsoft.com/en-us/download/confirmation.aspx?id=17630 +"C:\Program Files (x86)\Microsoft\ILMerge\ILMerge.exe" bin\release\xlsxToCsv.exe Excel.dll ICSharpCode.SharpZipLib.dll /out:xlsxToCsv.exe /lib:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0" /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework64\v4.0.30319" \ No newline at end of file diff --git a/Tools/XlsxToCsv/Program.cs b/Tools/XlsxToCsv/Program.cs new file mode 100644 index 0000000..29968e1 --- /dev/null +++ b/Tools/XlsxToCsv/Program.cs @@ -0,0 +1,101 @@ +๏ปฟusing System; +using System.Collections.Generic; +using System.Diagnostics; +using System.IO; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace xlsxToCsv +{ + class Program + { + static ExcelConvert excelConvert = new ExcelConvert(); + + static int fileList() + { + var allfiles = System.IO.Directory.GetFiles(".", "*.*", System.IO.SearchOption.AllDirectories).Where(s => s.EndsWith(".xls") || s.EndsWith(".xlsx")); + if ( allfiles.Count() == 0) + { + Console.WriteLine("File not found.."); + return 1; + } + foreach (string src in allfiles) + { + var tar = Path.ChangeExtension(src, "csv"); + Console.WriteLine(src + " => " + tar); + try { + if(excelConvert.Convert(src, tar) == false) + { + Console.WriteLine("ERROR : ["+src+"] can't read excel file."); + } + } catch( InvalidCastException e) + { + Console.WriteLine(e.ToString()); + } + } + return 0; + } + + static void printHelp() + { + string filename = Process.GetCurrentProcess().ProcessName; + Console.WriteLine("Excel to CSV(UTF-8). \n\r"); + Console.WriteLine("USING 1."); + Console.WriteLine("\t"+ filename); + Console.WriteLine("\n\rUSING 2."); + Console.WriteLine("\t"+ filename +" [Source FilePath] [Target FilePath] [-h]\n\r"); + Console.WriteLine("\t -h\t Display help"); + } + + static int Main(string[] args) + { + foreach(string a in args ) + { + if( String.Compare(a, "-h", true) == 0) + { + printHelp(); + return 0; + } + } + + if (args.Length != 0 && args.Length != 2) + { + printHelp(); + return 1; + } + + // ํด๋”๋ฅผ ๊ฒ€์ƒ‰ํ•ด์„œ ๋ณ€๊ฒฝ + if (args.Length == 0) + { + return fileList(); + } + + // cli file change + string srcFile = args[0]; + string tarFile = args[1]; + + if(!File.Exists(srcFile)) + { + Console.WriteLine("File not found. [" + srcFile + "]"); + return 1; + } + + string tarPath = Path.GetDirectoryName(tarFile); + if (!Directory.Exists(tarPath) && tarPath.Length > 0) + { + Console.WriteLine("Target Folder not exist."); + return 1; + } + + Console.WriteLine(srcFile + " => " + tarFile); + if (excelConvert.Convert(srcFile, tarFile) == false) + { + Console.WriteLine("ERROR : [" + srcFile + "] can't read excel file."); + return 1; + } + + return 0; + } + } +} diff --git a/Tools/XlsxToCsv/Properties/AssemblyInfo.cs b/Tools/XlsxToCsv/Properties/AssemblyInfo.cs new file mode 100644 index 0000000..a90ad9e --- /dev/null +++ b/Tools/XlsxToCsv/Properties/AssemblyInfo.cs @@ -0,0 +1,36 @@ +๏ปฟusing System.Reflection; +using System.Runtime.CompilerServices; +using System.Runtime.InteropServices; + +// ์–ด์…ˆ๋ธ”๋ฆฌ์— ๋Œ€ํ•œ ์ผ๋ฐ˜ ์ •๋ณด๋Š” ๋‹ค์Œ ํŠน์„ฑ ์ง‘ํ•ฉ์„ ํ†ตํ•ด +// ์ œ์–ด๋ฉ๋‹ˆ๋‹ค. ์–ด์…ˆ๋ธ”๋ฆฌ์™€ ๊ด€๋ จ๋œ ์ •๋ณด๋ฅผ ์ˆ˜์ •ํ•˜๋ ค๋ฉด +// ์ด๋Ÿฌํ•œ ํŠน์„ฑ ๊ฐ’์„ ๋ณ€๊ฒฝํ•˜์„ธ์š”. +[assembly: AssemblyTitle("xlsxToCsv")] +[assembly: AssemblyDescription("")] +[assembly: AssemblyConfiguration("")] +[assembly: AssemblyCompany("")] +[assembly: AssemblyProduct("xlsxToCsv")] +[assembly: AssemblyCopyright("Copyright ยฉ 2015")] +[assembly: AssemblyTrademark("")] +[assembly: AssemblyCulture("")] + +// ComVisible์„ false๋กœ ์„ค์ •ํ•˜๋ฉด ์ด ์–ด์…ˆ๋ธ”๋ฆฌ์˜ ํ˜•์‹์ด COM ๊ตฌ์„ฑ ์š”์†Œ์— +// ํ‘œ์‹œ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. COM์—์„œ ์ด ์–ด์…ˆ๋ธ”๋ฆฌ์˜ ํ˜•์‹์— ์•ก์„ธ์Šคํ•˜๋ ค๋ฉด +// ํ•ด๋‹น ํ˜•์‹์— ๋Œ€ํ•ด ComVisible ํŠน์„ฑ์„ true๋กœ ์„ค์ •ํ•˜์„ธ์š”. +[assembly: ComVisible(false)] + +// ์ด ํ”„๋กœ์ ํŠธ๊ฐ€ COM์— ๋…ธ์ถœ๋˜๋Š” ๊ฒฝ์šฐ ๋‹ค์Œ GUID๋Š” typelib์˜ ID๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค. +[assembly: Guid("ac689d65-e66d-4883-9b03-a9cbaf3702f4")] + +// ์–ด์…ˆ๋ธ”๋ฆฌ์˜ ๋ฒ„์ „ ์ •๋ณด๋Š” ๋‹ค์Œ ๋„ค ๊ฐ€์ง€ ๊ฐ’์œผ๋กœ ๊ตฌ์„ฑ๋ฉ๋‹ˆ๋‹ค. +// +// ์ฃผ ๋ฒ„์ „ +// ๋ถ€ ๋ฒ„์ „ +// ๋นŒ๋“œ ๋ฒˆํ˜ธ +// ์ˆ˜์ • ๋ฒ„์ „ +// +// ๋ชจ๋“  ๊ฐ’์„ ์ง€์ •ํ•˜๊ฑฐ๋‚˜ ์•„๋ž˜์™€ ๊ฐ™์ด '*'๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋นŒ๋“œ ๋ฒˆํ˜ธ ๋ฐ ์ˆ˜์ • ๋ฒˆํ˜ธ๊ฐ€ ์ž๋™์œผ๋กœ +// ์ง€์ •๋˜๋„๋ก ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. +// [assembly: AssemblyVersion("1.0.*")] +[assembly: AssemblyVersion("1.0.0.0")] +[assembly: AssemblyFileVersion("1.0.0.0")] diff --git a/Tools/XlsxToCsv/README.md b/Tools/XlsxToCsv/README.md new file mode 100644 index 0000000..6752b80 --- /dev/null +++ b/Tools/XlsxToCsv/README.md @@ -0,0 +1,42 @@ +# Xlsx To Csv +Convert excel file to csv file. + +csv file encoding : utf-8 + +## Requirements +* .net framework 4.0 more +* Visual Studio 2015 + +## Support file +* Excel format : `xlsx` +* Excel 97-2003 format : `xls` + +## How to use +Convert Excel files to csv read all of the subfolders +``` +xlsxToCsv.exe +``` + +Convert specified Excel files to csv. +``` +xlsxToCsv.exe [Source file path] [Target file path] +``` + +display this help +``` +xlsxToCsv.exe -h +``` + + +## ILMerge +ILMerge is a utility for merging multiple .NET assemblies into a single .NET assembly. It works on executables and DLLs alike and comes with several options for controlling the processing and format of the output. + +### Requirements +Download and install ILMerge + +>http://www.microsoft.com/en-us/download/details.aspx?id=17630 + +### Run +``` +ILMerge.bat +``` diff --git a/Tools/XlsxToCsv/test.xlsx b/Tools/XlsxToCsv/test.xlsx new file mode 100644 index 0000000..70d1a20 Binary files /dev/null and b/Tools/XlsxToCsv/test.xlsx differ diff --git a/Tools/XlsxToCsv/xlsxToCsv.csproj b/Tools/XlsxToCsv/xlsxToCsv.csproj new file mode 100644 index 0000000..50d5de1 --- /dev/null +++ b/Tools/XlsxToCsv/xlsxToCsv.csproj @@ -0,0 +1,70 @@ +๏ปฟ + + + + Debug + AnyCPU + {AC689D65-E66D-4883-9B03-A9CBAF3702F4} + Exe + Properties + xlsxToCsv + xlsxToCsv + v4.0 + 512 + true + + + + AnyCPU + true + full + false + bin\Debug\ + DEBUG;TRACE + prompt + 4 + + + AnyCPU + pdbonly + true + bin\Release\ + TRACE + prompt + 4 + + + + .\Excel.dll + + + .\ICSharpCode.SharpZipLib.dll + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/Tools/XlsxToCsv/xlsxToCsv.exe b/Tools/XlsxToCsv/xlsxToCsv.exe new file mode 100644 index 0000000..3813431 Binary files /dev/null and b/Tools/XlsxToCsv/xlsxToCsv.exe differ diff --git a/Tools/XlsxToCsv/xlsxToCsv.sln b/Tools/XlsxToCsv/xlsxToCsv.sln new file mode 100644 index 0000000..0f18b94 --- /dev/null +++ b/Tools/XlsxToCsv/xlsxToCsv.sln @@ -0,0 +1,22 @@ +๏ปฟ +Microsoft Visual Studio Solution File, Format Version 12.00 +# Visual Studio 14 +VisualStudioVersion = 14.0.23107.0 +MinimumVisualStudioVersion = 10.0.40219.1 +Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "xlsxToCsv", "xlsxToCsv.csproj", "{AC689D65-E66D-4883-9B03-A9CBAF3702F4}" +EndProject +Global + GlobalSection(SolutionConfigurationPlatforms) = preSolution + Debug|Any CPU = Debug|Any CPU + Release|Any CPU = Release|Any CPU + EndGlobalSection + GlobalSection(ProjectConfigurationPlatforms) = postSolution + {AC689D65-E66D-4883-9B03-A9CBAF3702F4}.Debug|Any CPU.ActiveCfg = Debug|Any CPU + {AC689D65-E66D-4883-9B03-A9CBAF3702F4}.Debug|Any CPU.Build.0 = Debug|Any CPU + {AC689D65-E66D-4883-9B03-A9CBAF3702F4}.Release|Any CPU.ActiveCfg = Release|Any CPU + {AC689D65-E66D-4883-9B03-A9CBAF3702F4}.Release|Any CPU.Build.0 = Release|Any CPU + EndGlobalSection + GlobalSection(SolutionProperties) = preSolution + HideSolutionNode = FALSE + EndGlobalSection +EndGlobal -- cgit v1.1-26-g67d0