应用的编程常常需要与Excel接触，例如读取Excel的数据呀，导出Excel，修改Excel呀，还可以像执行SQL样的对Excel进行查询或修改等，以下是我用到的一些Excel相关的工具类：
using System.Text;
using System.Data;
using System.Web;
using System.Xml.Xsl;
using System.Xml;
using System.IO;
using System.Data.OleDb;
using System.Text.RegularExpressions;
|
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 |
public class UtilExcel { public static string Charset = "UTF-8"; public static void Export(XmlNode xml, string fileName, HttpResponse response, HttpServerUtility server) { response.Clear(); response.ContentType = "application/vnd.ms-excel"; response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName)); XslCompiledTransform xtExcel = new XslCompiledTransform(); xtExcel.Load(server.MapPath("Excel.xsl")); xtExcel.Transform(xml, null, response.OutputStream); response.End(); } private static void SetHeaders(HttpResponse response, HttpRequest request, string strFileName) { response.Clear(); response.ContentType = "application/vnd.ms-excel"; response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", strFileName)); response.ContentEncoding = Encoding.GetEncoding(Charset); } public static void DataSetToExcel(DataTable dt, string FileName, List<ColumnInfo> lstHeaders) { try { SetHeaders(HttpContext.Current.Response, HttpContext.Current.Request, FileName); StringBuilder sb = new StringBuilder(); sb.Append("<html><meta http-equiv='Content-Type' content='text/html; charset=").Append(Charset).Append("'>").Append("<body><table width='100%' border='1'>"); sb.Append("<tr>"); for (int i = 0; i < lstHeaders.Count; i++) { ColumnInfo column = lstHeaders[i]; if (dt.Columns.Contains(column.Name)) { sb.AppendFormat("<td align='{0}'>", column.Align).Append(column.Display).Append("</td>"); } } sb.Append("</tr>"); foreach (DataRow row in dt.Rows) { sb.Append("<tr>"); for (int i = 0; i < lstHeaders.Count; i++) { ColumnInfo column = lstHeaders[i]; if (dt.Columns.Contains(column.Name)) { string strValue = string.Empty; if (row[column.Name] != null) { strValue = row[column.Name].ToString(); } sb.AppendFormat("<td style=\"mso-number-format:'\\@';text-align:'{0}'\">", column.Align); sb.Append(strValue).Append("</td>"); } } sb.Append("</tr>"); } sb.Append("</table></body></html>"); HttpContext.Current.Response.Write(sb.ToString()); HttpContext.Current.Response.End(); } catch (Exception e) { throw e; } } public static DataSet GetDataFromExcel(string strExcelFile, string strSql) { string strConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';", strExcelFile); DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strConn)) { adapter.Fill(ds); } } return ds; } public static int ExecuteNonQuery(string strExcelFile, string strSql) { string strConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';", strExcelFile); OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(strConn)) { if (!conn.State.Equals(ConnectionState.Open)) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = strSql; cmd.CommandType = CommandType.Text; int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } } |
