前言
平常都是用 Excel 的多, 但这一次遇到 Google Ads. 谷歌嘛, 当然不喜欢微软的 Excel.
硬是要求 CSV. 没办法, 只能找 Library 搞一搞了.
参考
介绍
CsvHelper 只能简单的读写 CSV file, 接口也很少. 它最好的使用方式是一个 CSV file 对应一个 List<Item>.
它甚至不能像 Excel 那样, 选择某个 cell 直接修改. 只能 replace all.
Read CSV
首先定义 DataClass
和 Mapping logic
public class OfflineConversionData { // format refer: https://support.google.com/google-ads/answer/7014069#prepare_data public string GoogleClickId { get; set; } = ""; public string ConversionName { get; set; } = ""; public string ConversionTime { get; set; } = ""; public decimal ConversionValue { get; set; } public string ConversionCurrency { get; set; } = ""; } public sealed class OfflineConversionDataMap : ClassMap<OfflineConversionData> { public OfflineConversionDataMap() { Map(e => e.GoogleClickId).Name("Google Click ID").Index(0); Map(e => e.ConversionName).Name("Conversion Name").Index(1); Map(e => e.ConversionTime).Name("Conversion Time").Index(2); Map(e => e.ConversionValue).Name("Conversion Value").Index(3); Map(e => e.ConversionCurrency).Name("Conversion Currency").Index(4); } }
然后
var rootPath = webHostEnvironment.WebRootPath; var csvFilePath = Path.Combine(rootPath, "google-offline-conversion.csv"); using var streamReader = new StreamReader(csvFilePath); using var csvReader = new CsvReader(streamReader, CultureInfo.InvariantCulture); csvReader.Context.RegisterClassMap<OfflineConversionDataMap>(); var datas = csvReader.GetRecords<OfflineConversionData>();
read to DataTable
var rootPath = webHostEnvironment.WebRootPath; var csvFilePath = Path.Combine(rootPath, "google-offline-conversion.csv"); using var streamReader = new StreamReader(csvFilePath); using var csvReader = new CsvReader(streamReader, CultureInfo.InvariantCulture); using var dataReader = new CsvDataReader(csvReader); var table = new DataTable(); table.Load(dataReader); var value = table.Rows[0][0].ToString(); // data only no header
Write CSV
using var memoryStream = new MemoryStream(); using var streamWriter = new StreamWriter(memoryStream); using var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture); csvWriter.Context.RegisterClassMap<OfflineConversionDataMap>(); var conversionDatas = new List<OfflineConversionData> { new OfflineConversionData { GoogleClickId = "EAIaIQobChMI9dfq693m_QIVRAQrCh3XcAANEAAYAyAAEgJJgPD_BwE", ConversionName = "Test Offline Conversion", ConversionTime = "2023-03-19 08:28:41 America/Los_Angeles", ConversionValue = 230, ConversionCurrency = "SGD", }, new OfflineConversionData { GoogleClickId = "CjwKCAjwq-WgBhBMEiwAzKSH6C7RiEiGFcxvUAr6GEMhGPcYyWSOSo5eiiD_rzedifklbBVhKO4fVBoCUH8QAvD_BwE", ConversionName = "Test Offline Conversion", ConversionTime = "2023-03-22 11:09:01 Asia/Singapore", ConversionValue = 250, ConversionCurrency = "SGD", } }; await csvWriter.WriteRecordsAsync<OfflineConversionData>(conversionDatas); await csvWriter.FlushAsync(); var rootPath = webHostEnvironment.WebRootPath; var csvFilePath = Path.Combine(rootPath, "google-offline-conversion.csv"); using var fileStream = System.IO.File.Create(csvFilePath); await fileStream.WriteAsync(memoryStream.ToArray()); await fileStream.FlushAsync();
其它写入
csvWriter.WriteField("Hello World"); // first cell csvWriter.NextRecord(); // next row csvWriter.WriteField("Hello World 2"); // first cell