Skip to content

Instantly share code, notes, and snippets.

@dzonesasaki
Created May 7, 2020 03:08
Show Gist options
  • Save dzonesasaki/cc395ba7ef87aac73e66d4b0a1649226 to your computer and use it in GitHub Desktop.
Save dzonesasaki/cc395ba7ef87aac73e66d4b0a1649226 to your computer and use it in GitHub Desktop.
convert csv to xlsx using ClosedXML for attendance book
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