ToCSVString Extension method
ToCSVString Extension Method
A friend of mine wanted to create the posibility to export some selected sharepoint list items to a spreadsheet program. So he started thinking and decided to create a new list. The clients selected the items by clicking some filter options. Items are then returned by a CAML query and inserted into that new list. The client could then open the new list and export it with the help of the 'Export to Spreadsheet' Action.
My idea was a little different though. Why copy that data to a list if we can response that list as a CSV file right away if we write a simple extension method that can export a Datatable to a CSV string? So that's what I did.
The code
public static class DataExtensions {
private static Regex quotedRegex = new Regex(@"\A(?:\A\s+.*|.*\s+\z|.*"".*|.*,.*|.*\n.*)\Z", RegexOptions.IgnoreCase);
public static string ToCSVString(this string value) {
if (string.IsNullOrEmpty(value)) {
return "\"\"";
}
string retVal = value;
if (quotedRegex.IsMatch(value)) {
retVal = string.Concat("\"", retVal.Replace("\"", "\"\""), "\"");
}
return retVal;
}
public static string ToCSVString(this DataTable value) {
StringBuilder stringBuilder = new StringBuilder();
#region add column names as CSV string to stringbuilder
int columnsCount = value.Columns.Count;
int firstColumns = columnsCount - 1;
int columnCounter = 0;
for (columnCounter = 0; columnCounter < firstColumns; columnCounter++) {
stringBuilder.Append(value.Columns[columnCounter].ColumnName.ToCSVString() + ",");
}
stringBuilder.AppendLine(value.Columns[columnCounter].ColumnName.ToCSVString());
#endregion
#region add each row as CSV string to stringBuilder
int firstRows = value.Rows.Count - 1;
int rowsCounter = 0;
for (rowsCounter = 0; rowsCounter < firstRows; rowsCounter++) {
for (columnCounter = 0; columnCounter < firstColumns; columnCounter++) {
stringBuilder.Append(value.Rows[rowsCounter][columnCounter].ToString().ToCSVString() + ",");
}
stringBuilder.AppendLine(value.Rows[rowsCounter][columnCounter].ToString().ToCSVString());
}
for (columnCounter = 0; columnCounter < firstColumns; columnCounter++) {
stringBuilder.Append(value.Rows[rowsCounter][columnCounter].ToString().ToCSVString() + ",");
}
stringBuilder.Append(value.Rows[rowsCounter][columnCounter].ToString().ToCSVString());
#endregion
return stringBuilder.ToString();
}
}It's a raw and first implementation and needs some refactoring but it does it's job perfectly. All he had to do was to write that string to the ResponseStream with the correct FileDisposition and MimeType headers and that's it.
Cheers,
Wes