Created
May 7, 2020 03:08
-
-
Save dzonesasaki/cc395ba7ef87aac73e66d4b0a1649226 to your computer and use it in GitHub Desktop.
convert csv to xlsx using ClosedXML for attendance book
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; // using visual studio 2019 | |
using System.IO; //for StreamReader | |
using System.Text;// for encoding | |
using ClosedXML.Excel; //for xlsx , install from nuget, v0.95.2 | |
//convert csv to xlsx using ClosedXML for attendance book | |
namespace csv2xlsx | |
{ | |
class Program | |
{ | |
static void Main(string[] args) | |
{ | |
const int MAX_NUM_ROW = 33; // num of days in month + id + label =33 | |
const int MAX_NUM_COLUMN = 16;// num of labels =16 | |
try | |
{ | |
if (args.Length < 1) throw new ArgumentException(); | |
string myFnameCsv = args[0]; | |
string myFnameXlsx = myFnameCsv.Replace(".csv", ".xlsx"); | |
//string mySheetName = myFnameCsv.Replace(".csv", ""); | |
string mySheetName = Path.GetFileName(myFnameCsv).Replace(".csv", ""); | |
Encoding myEncSjis = Encoding.GetEncoding("Shift_JIS"); | |
string[,] myAryStr = new string[MAX_NUM_ROW, MAX_NUM_COLUMN]; | |
UInt32 uiMaxRow = 0; | |
UInt32 uiMaxColumn = 0; | |
// read csv file | |
using (StreamReader myrd = new StreamReader(myFnameCsv,myEncSjis)) | |
{ | |
UInt32 uiR = 0; | |
while (!myrd.EndOfStream) | |
{ | |
string[] tmpStAry = myrd.ReadLine().Split(','); | |
UInt32 uiC = 0; | |
foreach (string tmpstr in tmpStAry) | |
{ | |
string tmpstr2 = tmpstr.Replace("\"", ""); | |
myAryStr[uiR, uiC] = tmpstr2; | |
//Console.Write(tmpstr2 + "\t"); | |
uiC += 1; | |
}//end of foreach | |
uiMaxColumn = uiMaxColumn < uiC ? uiC : uiMaxColumn; | |
uiR += 1; | |
//Console.WriteLine(""); | |
}// end while | |
uiMaxRow = uiR; | |
}//end using csv file | |
// write xlsx file | |
using (var myBook = new XLWorkbook(XLEventTracking.Disabled)) | |
{ | |
var mySheet1 = myBook.AddWorksheet(mySheetName); | |
for (int uiR = 0; uiR < uiMaxRow; uiR++) | |
{ | |
for (int uiC = 0; uiC < uiMaxColumn; uiC++) | |
{ | |
mySheet1.Cell(uiR + 1, uiC + 1).Value = myAryStr[uiR, uiC]; | |
//Console.Write(myAryStr[uiR, uiC] + "\t"); | |
}//end for uiC | |
//Console.WriteLine(""); | |
}//end for uiR | |
myBook.SaveAs(myFnameXlsx); | |
}//end of using xlsx file | |
}//end of try | |
catch (Exception e) | |
{ | |
Console.WriteLine(e); | |
Console.WriteLine("error! \n usage: csv2xlsx.exe file.csv"); | |
Console.ReadKey(); | |
return; | |
}//end of catch | |
}//end of Main | |
}//end of class | |
}//end of namespace |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment