1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
| public static class XlsHelper
{
/// <summary>
///
/// </summary>
/// <param name="data">The DataSet</param>
/// <returns></returns>
public static string ToXLS(DataSet data)
{
StringBuilder xml = new StringBuilder();
xml.Append(HeaderToXLS());
foreach (DataTable dt in data.Tables)
{
xml.Append(RowToXLS(dt));
}
xml.Append(FooterToXLS());
return xml.ToString();
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private static string HeaderToXLS()
{
StringBuilder xml = new StringBuilder();
xml.Append("<?xml version=\"1.0\"?>\n");
xml.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
xml.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
xml.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
xml.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
xml.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
xml.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
xml.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
xml.Append("</DocumentProperties>");
xml.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
xml.Append("<ProtectStructure>False</ProtectStructure>\n");
xml.Append("<ProtectWindows>False</ProtectWindows>\n");
xml.Append("</ExcelWorkbook>\n");
xml.Append("<Styles>\n");
xml.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
xml.Append("<Alignment ss:Vertical=\"Bottom\"/>\n");
xml.Append("<Borders/>\n");
xml.Append("<Font/>\n");
xml.Append("<Interior/>\n");
xml.Append("<NumberFormat/>\n");
xml.Append("<Protection/>\n");
xml.Append("</Style>\n");
xml.Append("<Style ss:ID=\"s21\">\n");
xml.Append("<NumberFormat ss:Format=\"Short Date\"/>\n");
xml.Append("</Style>\n");
xml.Append("</Styles>\n");
return xml.ToString();
}
/// <summary>
///
/// </summary>
/// <param name="dt">The DataTable</param>
/// <returns></returns>
private static string RowToXLS(DataTable dt)
{
StringBuilder xml = new StringBuilder();
xml.AppendFormat("<Worksheet ss:Name=\"{0}\">\n", dt.TableName);
xml.AppendFormat("<Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\">\n",
dt.Columns.Count, dt.Rows.Count + 1);
xml.Append("<Row>\n");
foreach (DataColumn column in dt.Columns)
{
xml.AppendFormat("<Cell><Data ss:Type=\"String\">\n{0}</Data></Cell>\n",
System.Web.HttpUtility.HtmlEncode(column.ColumnName));
}
xml.Append("</Row>\n");
string dataType = string.Empty;
foreach (DataRow row in dt.Rows)
{
xml.Append("<Row>\n");
for (int i = 0; i < dt.Columns.Count; i++)
{
Type type = row[i].GetType();
if (type == typeof(decimal))
{
xml.AppendFormat("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\n",
Convert.ToDouble(row[i], CultureInfo.CurrentCulture).ToString(CultureInfo.CreateSpecificCulture("en-US")));
}
else if (type == typeof(Int32))
{
xml.AppendFormat("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\n",
Convert.ToInt32(row[i], CultureInfo.CurrentCulture).ToString(CultureInfo.CreateSpecificCulture("en-US")));
}
else if (type == typeof(DateTime))
{
xml.AppendFormat("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>\n",
Convert.ToDateTime(row[i]).ToString("s"));
}
else
{
xml.AppendFormat("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>\n", row[i].ToString());
}
}
xml.Append("</Row>\n");
}
xml.Append("</Table>\n");
xml.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
xml.Append("<Selected/>\n");
xml.Append("<Panes>\n");
xml.Append("<Pane>\n");
xml.Append("<Number>3</Number>\n");
xml.Append("<ActiveRow>1</ActiveRow>\n");
xml.Append("</Pane>\n");
xml.Append("</Panes>\n");
xml.Append("<ProtectObjects>False</ProtectObjects>\n");
xml.Append("<ProtectScenarios>False</ProtectScenarios>\n");
xml.Append("</WorksheetOptions>\n");
xml.Append("</Worksheet>\n");
return xml.ToString();
}
private static string FooterToXLS()
{
StringBuilder xml = new StringBuilder();
xml.Append("</Workbook>\n");
return xml.ToString();
}
} |
Partager