public static string DataTableToCsv(this DataTable dataTable)
{
var excelSanitizer = new NoFormulaeExcelSanitizer();
const char separator = ',';
var sb = new StringBuilder();
for (var i = 0; i < dataTable.Columns.Count; i++)
{
sb.Append(dataTable.Columns[i]);
if (i < dataTable.Columns.Count - 1)
{
sb.Append(separator);
}
}
sb.AppendLine();
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
for (var i = 0; i < dataTable.Columns.Count; i++)
{
sb.Append(EncodeAndCheck(dr[i].ToString(), excelSanitizer));
if (i < dataTable.Columns.Count - 1)
{
sb.Append(separator);
}
}
sb.AppendLine();
}
}
return(sb.ToString());
}
public static string DataSetSentOnToCsv(this DataTable datatable, DataTable columnNameDataTable)
{
var excelSanitizer = new NoFormulaeExcelSanitizer();
var seperator = ',';
var sb = new StringBuilder();
//Remove Column 0 from table for nil returns
if (datatable.Columns.Contains("0"))
{
datatable.Columns.Remove("0");
}
for (var i = 0; i < datatable.Columns.Count; i++)
{
//Replace columnnames from number 1 starting from 14th column
if (columnNameDataTable != null && columnNameDataTable.Rows.Count > 0)
{
var matchingRow = columnNameDataTable.AsEnumerable().FirstOrDefault(
x => x.Field <int>("SiteOperatorId").ToString() == datatable.Columns[i].ColumnName);
if (matchingRow != null)
{
sb.Append("\"");
sb.Append(matchingRow.Field <string>("SiteOperatorData"));
sb.Append("\"");
}
else
{
sb.Append(datatable.Columns[i]);
}
}
else
{
sb.Append(datatable.Columns[i]);
}
if (i < datatable.Columns.Count - 1)
{
sb.Append(seperator);
}
}
sb.AppendLine();
if (datatable.Rows.Count > 0)
{
foreach (DataRow dr in datatable.Rows)
{
for (var i = 0; i < datatable.Columns.Count; i++)
{
sb.Append(EncodeAndCheck(dr[i].ToString(), excelSanitizer));
if (i < datatable.Columns.Count - 1)
{
sb.Append(seperator);
}
}
sb.AppendLine();
}
}
return(sb.ToString());
}
public void Sanitize_WithUnsafeInput_RemovesLeadingEqualsSign()
{
// Arrange
string input = "=cmd|'/C ping 127.0.0.1'!A0";
NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer();
// Act
string result = sanitizer.Sanitize(input);
// Assert
Assert.Equal("cmd|'/C ping 127.0.0.1'!A0", result);
}
public void Sanitize_WithSafeInput_ReturnsInputUnmodified()
{
// Arrange
string input = "This is a safe input.";
NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer();
// Act
string result = sanitizer.Sanitize(input);
// Assert
Assert.Equal("This is a safe input.", result);
}
public void IsThreat_WithUnsafeInput_ReturnsTrue()
{
// Arrange
string input = "=cmd|'/C ping 127.0.0.1'!A0";
NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer();
// Act
bool result = sanitizer.IsThreat(input);
// Assert
Assert.Equal(true, result);
}
public void IsThreat_WithSafeInput_ReturnsFalse()
{
// Arrange
string input = "This is a safe input.";
NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer();
// Act
bool result = sanitizer.IsThreat(input);
// Assert
Assert.Equal(false, result);
}
public static string DataTableToCsv(this DataTable dataTable, List <string> columnsToRemove)
{
foreach (var columnName in columnsToRemove)
{
for (var columnCount = dataTable.Columns.Count - 1; columnCount >= 0; columnCount--)
{
if (dataTable.Columns[columnCount].ColumnName.Contains(columnName))
{
dataTable.Columns.RemoveAt(columnCount);
}
}
}
var excelSanitizer = new NoFormulaeExcelSanitizer();
const char separator = ',';
var sb = new StringBuilder();
for (var i = 0; i < dataTable.Columns.Count; i++)
{
sb.Append(dataTable.Columns[i]);
if (i < dataTable.Columns.Count - 1)
{
sb.Append(separator);
}
}
sb.AppendLine();
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
for (var i = 0; i < dataTable.Columns.Count; i++)
{
sb.Append(EncodeAndCheck(dr[i].ToString(), excelSanitizer));
if (i < dataTable.Columns.Count - 1)
{
sb.Append(separator);
}
}
sb.AppendLine();
}
}
return(sb.ToString());
}
public static string EncodeAndCheck(string value, NoFormulaeExcelSanitizer excelSanitizer)
{
string result;
if (value == null)
{
result = string.Empty;
}
else
{
result = value.Equals("0.000") ? string.Empty : value.ToString();
}
if (excelSanitizer.IsThreat(result))
{
var message = string.Format(
"A potentially dangerous string was identified and sanitised when writing CSV data. The value was \"{0}\".",
result);
Trace.TraceWarning(message);
result = excelSanitizer.Sanitize(result);
}
if (result.Contains(","))
{
result = string.Concat("\"", value, "\"");
}
result = result.Replace("\r\n", " ");
result = result.Replace("\n\n", " ");
result = result.Replace("\r", " ");
result = result.Replace("\n", " ");
result = result.Trim();
return(result);
}
