public CsvSerializerState Create(string assemblyName, string entityName, Stream toStream)
{
var delimiter = this.delimiterElector.Elect(toStream);
toStream.Seek(0, SeekOrigin.Begin);
var factory = new SpreadsheetFactory(delimiter);
var spreadsheet = factory.Create(toStream, 5);
toStream.Seek(0, SeekOrigin.Begin);
var delimitedHeader = this.delimitedHeaderElector.Elect(spreadsheet);
var typeBuilder = new AnonymousEntityTypeBuilder();
var typeColumns = delimitedHeader
.DelimitedColumns
.Select(x => new AnonymousEntityTypeProperty {
Name = x.Name, Type = x.Type
})
.ToArray();
var entityType = typeBuilder.Create(assemblyName, entityName, typeColumns);
var serializerType = typeof(CsvSerializer <>).MakeGenericType(entityType);
return(CsvSerializerState.Create(
delimiter,
delimitedHeader.DelimitedColumns,
entityType,
Activator.CreateInstance(serializerType)));
}
public void SpreadsheetShouldParseRequestedNumberOfRows()
{
var delimiter = new Delimiter {
DelimitedBy = ','
};
var stream = "1,2\r\n3,4\r\n4,5".ToStream();
var factory = new SpreadsheetFactory(delimiter);
var testSubject = factory.Create(stream, 1);
testSubject.Rows.Should().Be(1);
testSubject.Columns.Should().Be(2);
}
public override void Run(object input = null)
{
var spreadsheet = SpreadsheetFactory.GetSpreadsheet("10jO6XNdIrtXm8LuJsjFOO-388o54aNa28CiLiv-31qw");
var list = (List <string>)input;
var values = new List <IList <object> >();
foreach (var item in list)
{
values.Add(new List <object>()
{
item
});
}
spreadsheet.Append("test", values);
}
private static void GenerateSpreadsheet(string[] args)
{
string destination;
if (args.Length > 0)
{
destination = args[0];
}
else
{
Console.WriteLine("destination not specified");
return;
}
using (ISpreadsheetDocument document = SpreadsheetFactory.CreateSpreadsheet())
{
IWorksheet sheet = document.Workbook.AppendWorksheet("Sheet 1");
sheet.Cells[0, 0].SetValue("1. Quarter");
sheet.Cells[1, 0].SetValue("2. Quarter");
sheet.Cells[2, 0].SetValue("3. Quarter");
sheet.Cells[3, 0].SetValue("4. Quarter");
sheet.Cells[0, 1].SetValue(152306);
sheet.Cells[1, 1].SetValue(128742);
sheet.Cells[2, 1].SetValue(218737);
sheet.Cells[3, 1].SetValue(187025);
sheet.Cells[0, 2].SetValue(90123);
sheet.Cells[1, 2].SetValue(120744);
sheet.Cells[2, 2].SetValue(218681);
sheet.Cells[3, 2].SetValue(187322);
using (Stream stream = new FileStream(destination, FileMode.Create, FileAccess.ReadWrite))
{
document.Close(stream);
}
}
new Process
{
StartInfo = new ProcessStartInfo(destination)
{
UseShellExecute = true
}
}.Start();
}
private static void Main(string[] args)
{
try
{
var executingDirectory = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
var workingDirectory = Directory.GetCurrentDirectory();
var filePath = args.ElementAtOrDefault(0);
var outputFilePath = args.ElementAtOrDefault(1) ?? $"{workingDirectory}/out.csv";
var outputJsonFilePath = args.ElementAtOrDefault(1) ?? $"{workingDirectory}/out.json";
if (!string.IsNullOrEmpty(filePath))
{
if (!File.Exists(filePath))
{
throw new FileNotFoundException($"No file found @ '{filePath}'");
}
}
else
{
var inputFiles = Directory.GetFiles(workingDirectory)
.Where(f => f.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase) || f.EndsWith("csv", StringComparison.OrdinalIgnoreCase))
.Where(f => !f.EndsWith("out.csv", StringComparison.OrdinalIgnoreCase))
.ToList();
if (inputFiles.Count == 0)
{
throw new IOException($"No files found in working directory: '{workingDirectory}'");
}
if (inputFiles.Count > 1)
{
throw new IOException($"More than one spreadsheet file was found in working directory: '{workingDirectory}'");
}
filePath = inputFiles.Single();
}
WriteLine($"ifp: '{filePath}'");
WriteLine($"ofp: '{outputFilePath};");
WriteLine($"ojfp: '{outputJsonFilePath};");
var spreadSheetFactory = new SpreadsheetFactory();
var sheetService = spreadSheetFactory.CreateSpreadsheetServiceFromFilePath(filePath);
WriteLine($"ExecutingDir: '{executingDirectory}'");
WriteLine($"WorkingDir: '{workingDirectory}'");
ICategoryDataStore categoryStore = new CategoryDataStore();
var rowData = sheetService.ReadSheet(filePath)
.Select(r => new TransactionDto
{
Type = r["Type"],
TransDate = DateTime.Parse(r["Trans Date"]),
PostDate = DateTime.Parse(r["Post Date"]),
Description = r["Description"],
Amount = decimal.Parse(r["Amount"]) * -1
}).ToList();
var groupedInfo = rowData.GroupBy(r => r.Description)
.Select(g =>
{
var categoryData = categoryStore.GetCategory(g.Key);
return(new
{
Category = categoryData.Category,
Description = categoryData.Description,
RecordCount = g.Count(),
Sum = g.Sum(x => x.Amount),
DescriptionTransactions = g.Select(x => new TransactionDto
{
Type = x.Type,
TransDate = x.TransDate,
PostDate = x.PostDate,
Description = x.Description,
GenericDescription = categoryData.Description,
Amount = x.Amount,
Category = categoryData.Category
}).OrderBy(a => a.TransDate).ToList()
});
})
.GroupBy(a => a.Category).Select(g => new
{
Category = g.Key,
RecordCount = g.Count(),
CategorySum = g.Sum(x => x.Sum),
CategoryTransactions = g.OrderBy(x => x.Description).ToList()
})
.OrderBy(a => a.Category);
var flattenedData = groupedInfo.SelectMany(g => g.CategoryTransactions)
.SelectMany(cts => cts.DescriptionTransactions
.Select(tdto => new TransactionDto
{
Type = tdto.Type,
TransDate = tdto.TransDate,
PostDate = tdto.PostDate,
Description = tdto.Description,
GenericDescription = tdto.GenericDescription,
Amount = tdto.Amount,
Category = cts.Category
})
)
.Where(d => !d.Category?.Equals("payments", StringComparison.OrdinalIgnoreCase) ?? true);
var writeAbleData = flattenedData.Select(fd => new Dictionary <string, string>
{
[nameof(fd.Type)] = fd.Type,
[nameof(fd.TransDate)] = fd.TransDate.ToString(),
[nameof(fd.PostDate)] = fd.PostDate.ToString(),
[nameof(fd.Description)] = fd.Description,
[nameof(fd.GenericDescription)] = fd.GenericDescription,
[nameof(fd.Amount)] = fd.Amount.ToString(),
[nameof(fd.Category)] = fd.Category,
});
var json = JsonConvert.SerializeObject(groupedInfo, Formatting.Indented);
var data = sheetService.WriteSheet(writeAbleData);
File.WriteAllBytes(outputFilePath, data);
File.WriteAllText(outputJsonFilePath, json);
}
catch (Exception ex)
{
WriteLine($"Error: '{ex.Message}'");
}
}
