public static void ClosedXmlWriter(string inFile, string outFile)
{
var workbook = new ClosedXML.Excel.XLWorkbook();
ClosedXML.Excel.IXLWorksheet sheet = workbook.Worksheets.Add("Sheet1");
using (var sr = new StreamReader(File.OpenRead(inFile)))
{
int r = 1;
string line = "";
string s;
bool ifInner = false;
while ((s = sr.ReadLine()) != null)
{
if (ifInner)
{
line += "\n" + s;
if (s.Contains("\"\t"))
{
ifInner = false;
}
else
{
continue;
}
}
else
{
line = s;
}
int pos;
if ((pos = s.LastIndexOf('\t')) > -1 && pos != s.Length - 1)
{
if (s[pos + 1] == '"' && s[s.Length - 1] != '"')
{
ifInner = true;
continue;
}
}
var row = sheet.Row(r);
string[] columns = line.Split('\t');
for (var c = 0; c < columns.Length; c++)
{
if (double.TryParse(columns[c], out double value))
{
row.Cell(c + 1).Value = value;
}
else
{
row.Cell(c + 1).Value = columns[c].Trim('"');
}
}
r++;
}
}
workbook.SaveAs(outFile);
}
public string[,] OpenExcel(string filename)
{
using (ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook(filename))
{
ClosedXML.Excel.IXLWorksheet worksheet = workbook.Worksheet(1);
int rc = worksheet.RangeUsed().RowCount();
int cc = worksheet.RangeUsed().Row(1).CellCount();
string[,] buff = new string[rc, cc];
for (int i = 0; i < rc; i++)
{
ClosedXML.Excel.IXLRow row = worksheet.Row(i + 1);
for (int j = 0; j < cc; j++)
{
ClosedXML.Excel.IXLCell cell = row.Cell(j + 1);
string value = cell.GetValue <string>();
buff[i, j] = value;
}
}
return(buff);
}
}
private void ScriviOre(
ClosedXML.Excel.IXLWorksheet meseSheet, List <RowExcel> dataOutput)
{
int row = 3;
var it = new CultureInfo("it-IT");
while (dataOutput.Count() > 0)
{
var itemRowOutput = dataOutput.Select(a => a).OrderBy(a => a.Data).FirstOrDefault();
dataOutput.Remove(itemRowOutput);
if (!meseSheet.Cell(row, 4).IsEmpty())
{
meseSheet.Row(row).InsertRowsBelow(1);
row++;
}
meseSheet.Cell(row, 1).Value = itemRowOutput.Data.ToString("dd/MM/yyyy");
meseSheet.Cell(row, 2).Value = itemRowOutput.Data.ToString("ddd", it);
meseSheet.Cell(row, 3).Value = System.Globalization.ISOWeek.GetWeekOfYear(itemRowOutput.Data);
meseSheet.Cell(row, 4).Value = itemRowOutput.Commessa;
meseSheet.Cell(row, 6).Value = itemRowOutput.NumeroOre;
meseSheet.Cell(row, 5).Value = itemRowOutput.SedeLavoro;
if (itemRowOutput.Pasto)
{
Pernotto(meseSheet, row);
}
row++;
}
}