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);
}
