public async Task ToTextSkipLinesAsync() { //Setup var fmt = new ExcelOutputTextFormat() { SkipLinesBeginning = 1, SkipLinesEnd = 1 }; var text = await _ws.Cells["A1:D5"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var colsHeaders = lines[0].Split(fmt.Delimiter); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(3, lines.Length); Assert.AreEqual(_ws.Cells["A1"].Text, colsHeaders[0]); Assert.AreEqual(_ws.Cells["B1"].Text, colsHeaders[1]); Assert.AreEqual(_ws.Cells["C1"].Text, colsHeaders[2]); Assert.AreEqual(_ws.Cells["D1"].Text, colsHeaders[3]); Assert.AreEqual(_ws.Cells["A3"].Text, cols[0]); Assert.AreEqual(_ws.Cells["B3"].Text, cols[1]); Assert.AreEqual(_ws.Cells["C3"].Text, cols[2]); Assert.AreEqual("99.00", cols[3]); }
public async Task ToTextFormatTextAndCellFormatAsync() { var ci = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); //Setup var fmt = new ExcelOutputTextFormat() { Delimiter = '.', TextQualifier = '\'', UseCellFormat = true, Culture = new CultureInfo("sv-SE"), Formats = new string[] { "", "$", "$", null } }; var text = await _ws.Cells["A1:D5"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].Text, cols[0]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["B2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[1]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["C2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[2]); Assert.AreEqual("66,00", cols[3]); Thread.CurrentThread.CurrentCulture = ci; }
public void ToTextTextIgnoreHeaders() { _sheet.Cells["A1"].Value = 1; _sheet.Cells["B1"].Value = 2; var format = new ExcelOutputTextFormat { TextQualifier = '\'', FirstRowIsHeader = false }; var text = _sheet.Cells["A1:B1"].ToText(format); Assert.AreEqual("1,2", text); }
public async Task ToTextHeaderAndFooterAsync() { //Setup var fmt = new ExcelOutputTextFormat() { Header = "Starts With", Footer = "Ends With" }; var text = await _ws.Cells["A1:D5"].ToTextAsync(fmt).ConfigureAwait(false); //Assert Assert.IsTrue(text.StartsWith(fmt.Header + fmt.EOL)); Assert.IsTrue(text.EndsWith(fmt.EOL + fmt.Footer)); }
public void ToTextDefault() { //Setup var fmt = new ExcelOutputTextFormat(); var text = _ws.Cells["A1:D5"].ToText(fmt); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].Text, cols[0]); Assert.AreEqual(_ws.Cells["B2"].Text, cols[1]); Assert.AreEqual(_ws.Cells["C2"].Text, cols[2]); Assert.AreEqual("66.00", cols[3]); }
public void ToTextTextTextQualifier() { _sheet.Cells["A1"].Value = "h1"; _sheet.Cells["B1"].Value = "h2"; _sheet.Cells["A2"].Value = 1; _sheet.Cells["B2"].Value = 2; var format = new ExcelOutputTextFormat { TextQualifier = '\'' }; var text = _sheet.Cells["A1:B2"].ToText(format); Assert.AreEqual("'h1','h2'" + Environment.NewLine + "1,2", text); }
public void ToTextHeaderAndFooter() { //Setup var fmt = new ExcelOutputTextFormat() { Header = "Starts With", Footer = "Ends With" }; var text = _ws.Cells["A1:D5"].ToText(fmt); //Assert Assert.IsTrue(text.StartsWith(fmt.Header + fmt.EOL)); Assert.IsTrue(text.EndsWith(fmt.EOL + fmt.Footer)); }
private static async Task ExportTableAsync(ExcelPackage package) { var ws = package.Workbook.Worksheets[1]; var tbl = ws.Tables[0]; var format = new ExcelOutputTextFormat { Delimiter = ';', Culture = new CultureInfo("en-GB"), Encoding = new UTF8Encoding(), SkipLinesEnd = 1 //Skip the totals row }; await ws.Cells[tbl.Address.Address].SaveToTextAsync(FileOutputUtil.GetFileInfo("05-ExportedFromEPPlus.csv"), format); Console.WriteLine($"Writing the text file 'ExportedTable.csv'..."); }
public void ToTextNoCellFormat() { //Setup var fmt = new ExcelOutputTextFormat() { UseCellFormat = false }; var text = _ws.Cells["A1:D5"].ToText(fmt); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].GetValue <DateTime>().ToString("G", CultureInfo.InvariantCulture), cols[0]); Assert.AreEqual(_ws.Cells["B2"].Text, cols[1]); Assert.AreEqual(_ws.Cells["C2"].Text, cols[2]); Assert.AreEqual(_ws.Cells["D2"].GetValue <double>().ToString("r", CultureInfo.InvariantCulture), cols[3]); }
public void ToTextSwedishCulture() { //Setup var culture = new CultureInfo("sv-SE"); var fmt = new ExcelOutputTextFormat() { Culture = culture, }; var text = _ws.Cells["A1:D5"].ToText(fmt); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].GetValue <DateTime>().ToString("yyyy-MM-dd", culture), cols[0]); Assert.AreEqual(_ws.Cells["B2"].Text, cols[1]); Assert.AreEqual(_ws.Cells["C2"].Text, cols[2]); Assert.IsTrue(lines[1].EndsWith(_ws.Cells["D2"].GetValue <double>().ToString("0.00", culture))); }
public async Task ToTextTextQualifierDoubleAsync() { //Setup var fmt = new ExcelOutputTextFormat() { TextQualifier = '"', Formats = new string[] { "yyyy-MM-dd" } }; var text = await _ws.Cells["A1:D6"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[5].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A6"].GetValue <DateTime>().ToString("yyyy-MM-dd"), cols[0]); Assert.AreEqual(_ws.Cells["B6"].Text, cols[1]); Assert.AreEqual(new string(fmt.TextQualifier, 2) + _ws.Cells["C6"].Text + new string(fmt.TextQualifier, 2), cols[2]); Assert.AreEqual("198.00", cols[3]); }
public async Task ToTextFormatAndTextQualifierAsync() { //Setup var fmt = new ExcelOutputTextFormat() { TextQualifier = '"', Formats = new string[] { "yyyy-MM-dd", null, null, "0.00" }, }; var text = await _ws.Cells["A1:D5"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].GetValue <DateTime>().ToString("yyyy-MM-dd"), cols[0]); Assert.AreEqual(_ws.Cells["B2"].Text, cols[1]); Assert.AreEqual(fmt.TextQualifier + _ws.Cells["C2"].Text + fmt.TextQualifier, cols[2]); Assert.AreEqual(_ws.Cells["D2"].GetValue <double>().ToString("0.00", CultureInfo.InvariantCulture), cols[3]); }
public async Task ToTextFormatTextNoCellFormatAsync() { //Setup var fmt = new ExcelOutputTextFormat() { TextQualifier = '\'', UseCellFormat = false, Formats = new string[] { "$", "$", "$", "$" } }; var text = await _ws.Cells["A1:D5"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["A2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[0]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["B2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[1]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["C2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[2]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["D2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[3]); }
public async Task ToTextDelimiterAndCustomDecimalDelimiterAsync() { //Setup var fmt = new ExcelOutputTextFormat() { Delimiter = ';', DecimalSeparator = ",", Formats = new string[] { null, null, null, "0.00" } }; var text = await _ws.Cells["A1:D6"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].Text, cols[0]); Assert.AreEqual(_ws.Cells["B2"].Text, cols[1]); Assert.AreEqual(_ws.Cells["C2"].Text, cols[2]); Assert.AreEqual("66,00", cols[3]); }
public async Task ToTextCustomThousandSeparatorAsync() { //Setup var fmt = new ExcelOutputTextFormat() { Delimiter = '|', DecimalSeparator = ",", ThousandsSeparator = " ", Formats = new string[] { null, null, null, "#,##0.00" } }; var text = await _ws.Cells["A1:D35"].ToTextAsync(fmt).ConfigureAwait(false); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[34].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A35"].Text, cols[0]); Assert.AreEqual(_ws.Cells["B35"].Text, cols[1]); Assert.AreEqual(_ws.Cells["C35"].Text, cols[2]); Assert.AreEqual("1 155,00", cols[3]); }
public void ToTextFormatTextAndCellFormat() { //Setup var fmt = new ExcelOutputTextFormat() { Delimiter = '.', TextQualifier = '\'', UseCellFormat = true, Culture = new CultureInfo("sv-SE"), Formats = new string[] { "", "$", "$", null } }; var text = _ws.Cells["A1:D5"].ToText(fmt); var lines = text.Split(new string[] { fmt.EOL }, StringSplitOptions.None); var cols = lines[1].Split(fmt.Delimiter); //Assert Assert.AreEqual(_ws.Cells["A2"].Text, cols[0]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["B2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[1]); Assert.AreEqual(fmt.TextQualifier.ToString() + _ws.Cells["C2"].Value.ToString() + fmt.TextQualifier.ToString(), cols[2]); Assert.AreEqual(_ws.Cells["D2"].Text, cols[3]); }
static void CreateCsv(CsvWriter csvWriter) { TargetType targetType = TargetType.Client; string serverFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, serverFolder); if (!Directory.Exists(serverFolderPath)) { Directory.CreateDirectory(serverFolderPath); } string clientFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, clientFolder); if (!Directory.Exists(clientFolderPath)) { Directory.CreateDirectory(clientFolderPath); } string srcFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, srcFolder); if (!Directory.Exists(clientFolderPath)) { Directory.CreateDirectory(clientFolderPath); } DirectoryInfo di = new DirectoryInfo(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, srcFolder)); foreach (FileInfo srcFile in di.GetFiles()) { if (srcFile.Extension.ToLower().CompareTo(".xlsx") == 0) { using (ExcelPackage excelPackage = new ExcelPackage(srcFile)) { //excelPackage.ConvertToCsv(Path.Combine(csvFolderPath, "test")); var format = new ExcelOutputTextFormat(); format.Encoding = Encoding.UTF8; for (int cnt = 0; cnt < excelPackage.Workbook.Worksheets.Count; cnt++) { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[cnt]; int totalColumn = worksheet.Dimension.End.Column; for (int i = totalColumn; i > 0; i--) { string targetStr = worksheet.Cells[3, i].Text.ToLower(); if (targetStr.CompareTo("nodata") == 0) { worksheet.DeleteColumn(i); } if (targetType == TargetType.Client && targetStr.CompareTo("server") == 0) { worksheet.DeleteColumn(i); } if (targetType == TargetType.Server && targetStr.CompareTo("client") == 0) { worksheet.DeleteColumn(i); } } worksheet.DeleteRow(3); for (int i = 0; i < worksheet.Dimension.End.Row; i++) { List <string> strList = new List <string>(); for (int j = 0; j < worksheet.Dimension.End.Column; j++) { strList.Add(worksheet.Cells[i + 1, j + 1].Text); } csvWriter.AddRow(strList.ToArray()); } //Path.Combine(csvFolderPath, worksheet.Name) string dstPath = Path.Combine(targetType == TargetType.Server ? serverFolderPath : clientFolderPath, $"{worksheet.Name}.csv"); //worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].SaveToText(dstFile, format); string csv = csvWriter.Write(); File.WriteAllText(dstPath, csv, Encoding.UTF8); } } } } }
/// <summary> /// Converts the table range to CSV format /// </summary> /// <param name="format">Parameters/options for conversion to text</param> /// <returns></returns> /// <seealso cref="ExcelRangeBase.ToText(ExcelOutputTextFormat)"/> public string ToText(ExcelOutputTextFormat format) { return(Range.ToText(format)); }
/// <summary> /// Converts the table range to CSV format /// </summary> /// <returns></returns> /// <seealso cref="ExcelRangeBase.ToText(ExcelOutputTextFormat)"/> public Task <string> ToTextAsync(ExcelOutputTextFormat format) { return(Range.ToTextAsync(format)); }
/// <summary> /// Exports the table to a file /// </summary> /// <param name="file">The export file</param> /// <param name="format">Export options</param> /// <seealso cref="ExcelRangeBase.SaveToText(FileInfo, ExcelOutputTextFormat)"></seealso> public void SaveToText(FileInfo file, ExcelOutputTextFormat format) { Range.SaveToText(file, format); }
/// <summary> /// Exports the table to a <see cref="Stream"/> /// </summary> /// <param name="stream">Data will be exported to this stream</param> /// <param name="format">Export options</param> /// <seealso cref="ExcelRangeBase.SaveToText(Stream, ExcelOutputTextFormat)"></seealso> public void SaveToText(Stream stream, ExcelOutputTextFormat format) { Range.SaveToText(stream, format); }
/// <summary> /// Exports the table to a <see cref="Stream"/> /// </summary> /// <param name="stream">Data will be exported to this stream</param> /// <param name="format">Export options</param> /// <seealso cref="ExcelRangeBase.SaveToText(Stream, ExcelOutputTextFormat)"></seealso> public async Task SaveToTextAsync(Stream stream, ExcelOutputTextFormat format) { await Range.SaveToTextAsync(stream, format); }
public ExportToCsv(TargetType targetType) { string serverFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, serverFolder); if (!Directory.Exists(serverFolderPath)) { Directory.CreateDirectory(serverFolderPath); } string clientFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, clientFolder); if (!Directory.Exists(clientFolderPath)) { Directory.CreateDirectory(clientFolderPath); } string srcFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, srcFolder); if (!Directory.Exists(clientFolderPath)) { Directory.CreateDirectory(clientFolderPath); } //FileInfo srcFile = new FileInfo(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, srcfileName)); DirectoryInfo di = new DirectoryInfo(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, srcFolder)); foreach (FileInfo srcFile in di.GetFiles()) { if (srcFile.Extension.ToLower().CompareTo(".xlsx") == 0) { using (ExcelPackage excelPackage = new ExcelPackage(srcFile)) { //excelPackage.ConvertToCsv(Path.Combine(csvFolderPath, "test")); var format = new ExcelOutputTextFormat(); format.Encoding = Encoding.UTF8; for (int cnt = 0; cnt < excelPackage.Workbook.Worksheets.Count; cnt++) { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[cnt]; int totalColumn = worksheet.Dimension.End.Column; for (int i = totalColumn; i > 0; i--) { string targetStr = worksheet.Cells[3, i].Text.ToLower(); if (targetStr.CompareTo("nodata") == 0) { worksheet.DeleteColumn(i); } if (targetType == TargetType.Client && targetStr.CompareTo("server") == 0) { worksheet.DeleteColumn(i); } if (targetType == TargetType.Server && targetStr.CompareTo("client") == 0) { worksheet.DeleteColumn(i); } } worksheet.DeleteRow(3); //for (int i = 0; i < worksheet.Dimension.End.Row; i++) //{ // for (int j = 0; j < worksheet.Dimension.End.Column; j++) // { // worksheet.Cells[i + 1, j + 1].Value = string.Format($"\"{worksheet.Cells[i + 1, j + 1].Text}\""); // } //} //Path.Combine(csvFolderPath, worksheet.Name) FileInfo dstFile = new FileInfo(Path.Combine(targetType == TargetType.Server ? serverFolderPath : clientFolderPath, $"{worksheet.Name}.csv")); worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].SaveToText(dstFile, format); } } } } }
/// <summary> /// Exports the table to a file /// </summary> /// <param name="file">Data will be exported to this stream</param> /// <param name="format">Export options</param> /// <seealso cref="ExcelRangeBase.SaveToTextAsync(FileInfo, ExcelOutputTextFormat)"/> public async Task SaveToTextAsync(FileInfo file, ExcelOutputTextFormat format) { await Range.SaveToTextAsync(file, format); }