.net面试
物理架构：主要是项目最后的部署问题
逻辑架构：设计各个项目的模块及模块之间的关系，
比如webAPI,支付模块，数据库，中间件
开发架构：
接口，更好扩展，更好的团队合作
三层：UI+数据+业务
三层+接口层
三层+接口层+通信层（有多个终端用户的时候需要）
三层+接口层+通信层+服务层（API，可以对内的，也可以是对外的，比如天气服务，订票服务）
三层+MVC+web通信+DB服务（不直接操作数据库，通过中间件实现）
三层（UI+BLL+DAL+model）+接口层+通信层+控件库
UI(表现层):可以是多个解决方案MVC、webAPI、WCF、WPF
BizLayer(业务层)：可以建立多个类库，不同的业务可以有不同的类库；BLL(基本业务模块)+独立算法模块
DataLayer(数据访问层)：类库，数据访问类
DAL(基础数据访问)+ORM（做数据持久化的）+DButility(主要提供针对各个数据库的数据访问类，例如mysq/oracle)+IDAL（主要是抽象数据访问接口）
Models(模型层)：实体类，各种通用的工具类（数据验证、加密解密、XML操作类）
linq
var data =from b in imgDt.AsEnumerable()from a in wsDt.AsEnumerable()where a.Field(“img01”) == StringPlus.Trim(b.Field(“img01”))&& a.Field(“img02”) == StringPlus.Trim(b.Field(“img02”))&& a.Field(“img03”) == (string.IsNullOrEmpty(b.Field(“img03”)) ? “” : StringPlus.Trim(b.Field(“img03”)))&& a.Field(“img04”) == (string.IsNullOrEmpty(b.Field(“img04”)) ? “” : StringPlus.Trim(b.Field(“img04”)))&& a.Field(“img10”) != b.Field(“img10”)select new{img01 = a.Field(“img01”),img02 = a.Field(“img02”),img03 = a.Field(“img03”),img04 = a.Field(“img04”),img09 = a.Field(“img09”),img10_1 = a.Field(“img10”),img10_2 = b.Field(“img10”),differ = a.Field(“img10”) - b.Field(“img10”)}; var result = (from d in wsDt.AsEnumerable() select new{ img01 = d.Field<string>("img01"), img02 = d.Field<string>("img02")}).Union( from w in imgDt.AsEnumerable()select new{ img01 = w.Field<string>("img01"), img02 = w.Field<string>("img02") });
C#常用方法合集
//把DataTable 轉換成JSON格式數據
public static string ToJson(DataTable dt)
{
string json = JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);
if (string.IsNullOrEmpty(json) || json.Equals("null"))
{
return "[]";
}
return json.Replace("<", "＜");
}
//把json轉成datatable
public static DataTable JsonToDataTable(string json)
{
JavaScriptSerializer jss = new JavaScriptSerializer();
jss.MaxJsonLength = Int32.MaxValue;
ArrayList arrList = jss.Deserialize<ArrayList>(json);
DataTable dtJson = new DataTable();
if (arrList != null && arrList.Count > 0)
{
foreach (Dictionary<string, object> item in arrList)
{
if (dtJson.Columns.Count == 0)
{
//表頭
foreach (string key in item.Keys)
{
dtJson.Columns.Add(key);
}
}
DataRow row = dtJson.NewRow();
foreach (var curr in item.Keys)
{
row[curr] = item[curr];
}
dtJson.Rows.Add(row);
}
}
return dtJson;
}
C#獲取IP地址
//獲取本地電腦IP
//skipproxy是否跳過代理
public static string GetClientIP(bool skipproxy)
{
string result = null;
foreach (var head in new[] { "HTTP_CLIENT_IP", "HTTP_X_FORWARDED_FOR", "HTTP_FROM", "REMOTE_ADDR" })
{
if (System.Web.HttpContext.Current != null)
{
string temp = System.Web.HttpContext.Current.Request.ServerVariables[head];
if (!string.IsNullOrEmpty(temp))
{
result = temp;
break;
}
}
}
if (string.IsNullOrEmpty(result))
{
if (System.Web.HttpContext.Current != null)
{
result = System.Web.HttpContext.Current.Request.UserHostAddress;
}
}
if (string.IsNullOrEmpty(result))
{
string stringHostName = Dns.GetHostName();
IPHostEntry ipHostEntries = Dns.GetHostEntry(stringHostName);
IPAddress[] arrIpAddress = ipHostEntries.AddressList;
if (arrIpAddress.Length >= 2)
{
result = arrIpAddress[arrIpAddress.Length - 2].ToString();
}
else if (arrIpAddress.Length > 0)
{
result = arrIpAddress[0].ToString();
}
else
{
arrIpAddress = Dns.GetHostAddresses(stringHostName);
if (arrIpAddress.Length >= 0)
{
result = arrIpAddress[0].ToString();
}
else
{
result = "127.0.0.1";
}
}
}
//多个代理情况处理,一般第一个会是真实iP
if (!string.IsNullOrEmpty(result))
{
result = result.Split(',')[0].Trim();
}
if (result == "::1")
{
return "127.0.0.1";
}
else
{
return result;
}
}
不同數據類型相互轉換
json与DataTable相互转换
// 把表转换成JSON格式数据 public static string ToJson(DataTable dt) { string json = JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented); if (string.IsNullOrEmpty(json) || json.Equals("null")) { return "[]"; } return json.Replace("<", "＜"); } //使用IContractResolver序列化,【属性名】实现【驼峰命名法】 public static string ToJosnResolver(DataTable dt) { string json = JsonConvert.SerializeObject(dt, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }); if (string.IsNullOrEmpty(json) || json.Equals("null")) { return "[]"; } return json; } //json转Datatable public static DataTable JsonToDataTable(string json) { JavaScriptSerializer jss = new JavaScriptSerializer(); jss.MaxJsonLength = Int32.MaxValue; ArrayList arrList = jss.Deserialize<ArrayList>(json); DataTable dtJson = new DataTable(); if (arrList != null && arrList.Count > 0) { foreach (Dictionary<string, object> item in arrList) { if (dtJson.Columns.Count == 0) { //表頭 foreach (string key in item.Keys) { dtJson.Columns.Add(key); } } DataRow row = dtJson.NewRow(); foreach (var curr in item.Keys) { row[curr] = item[curr]; } dtJson.Rows.Add(row); } } return dtJson; }
字符串与字节数组相互转换
//以UTF8編碼方式將【字符串】轉化為【字節數組】 public static byte[] TextToBytes(string str) { return Encoding.UTF8.GetBytes(str); } // 以UTF8編碼方式將【字節數組】轉化為【字符串】 public static string BytesToText(byte[] binaryBytes) { return Encoding.UTF8.GetString(binaryBytes); }
stream与byte[]相互转换
// 把stream转换成byte[] public static byte[] StreamToBytes(Stream stream) { byte[] bytes = new byte[stream.Length]; stream.Read(bytes, 0, bytes.Length); // 设置当前流的位置为流的开始 stream.Seek(0, SeekOrigin.Begin); return bytes; } //将byte[]转成Stream public static Stream BytesToStream(byte[] bytes) { return new MemoryStream(bytes); }
Dictionary与json相互转换
// 将字典类型序列化为json字符串 public static string SerializeDictionaryToJsonString<TKey, TValue>(Dictionary<TKey, TValue> dict) { if (dict.Count == 0) return ""; return JsonConvert.SerializeObject(dict); } //将json字符串反序列化为字典类型 public static Dictionary<TKey, TValue> DeserializeStringToDictionary<TKey, TValue>(string jsonStr) { if (string.IsNullOrEmpty(jsonStr)) return new Dictionary<TKey, TValue>(); return JsonConvert.DeserializeObject<Dictionary<TKey, TValue>>(jsonStr); } //調用實例 Dictionary<string, string> rtnData = DeserializeStringToDictionary<string, string>(jsonStr);
繁体与简体相互转换
// 繁体转简体 public static string Big5ToGB(string strRows) { strRows = Microsoft.VisualBasic.Strings.StrConv(strRows, Microsoft.VisualBasic.VbStrConv.SimplifiedChinese, System.Globalization.CultureInfo.CreateSpecificCulture("zh-CN").LCID); strRows = Encoding.GetEncoding("gb2312").GetString(Encoding.Convert(Encoding.UTF8, Encoding.GetEncoding("gb2312"), Encoding.UTF8.GetBytes(strRows))); return strRows; } //简体转繁体 public static string GBToBig(string strRows) { strRows = Microsoft.VisualBasic.Strings.StrConv(strRows, Microsoft.VisualBasic.VbStrConv.TraditionalChinese, System.Globalization.CultureInfo.CreateSpecificCulture("zh-CN").LCID); strRows = Encoding.GetEncoding("Big5").GetString(Encoding.Convert(Encoding.UTF8, Encoding.GetEncoding("Big5"), Encoding.UTF8.GetBytes(strRows))); return strRows; }
c# 文件操作
/// <summary> /// 压缩文件 /// </summary> /// <param name="streams">文件流字典</param> /// <returns></returns> public static Stream CompressFiles(Dictionary<string, Stream> streams) { byte[] buffer = new byte[6500]; MemoryStream returnStream = new MemoryStream(); MemoryStream compressStream = new MemoryStream(); using (ZipOutputStream zipStream = new ZipOutputStream(compressStream)) { zipStream.SetLevel(9); foreach (var item in streams) { string filename = item.Key; using (var streamInput = item.Value) { ZipEntry ze = new ZipEntry(filename); ze.IsUnicodeText = true; zipStream.PutNextEntry(ze); while (true) { var readCount = streamInput.Read(buffer, 0, buffer.Length); if (readCount > 0) { zipStream.Write(buffer, 0, readCount); } else { break; } } zipStream.Flush(); } } zipStream.Finish(); compressStream.Position = 0; compressStream.CopyTo(returnStream);//加上这个名是因为using后 compressStream已关闭，所以在沒有关闭之前把数据Copy到另一个对象上进行返回 } returnStream.Position = 0; return returnStream; }
C#加密解密常用方法
MD5加密字符串
public static string GetMD5String(string str) { MD5 md5 = new MD5CryptoServiceProvider(); byte[] data = System.Text.Encoding.Default.GetBytes(str); byte[] md5data = md5.ComputeHash(data); md5.Clear(); StringBuilder builder = new StringBuilder(); for (int i = 0; i < md5data.Length - 1; i++) { builder.Append(md5data[i].ToString("X2")); } return builder.ToString(); }
base64加密解密
/// <summary> /// Base64加密 /// </summary> /// <param name="codeType">CODE类型</param> /// <param name="code">要加密的字串</param> /// <returns></returns> public static string EncodeBase64(string codeType, string code) { string encode = ""; byte[] bytes = Encoding.GetEncoding(codeType).GetBytes(code); try { encode = Convert.ToBase64String(bytes); } catch { encode = code; } return encode; } /// <summary> /// Base64解密 /// </summary> /// <param name="codeType">CODE类型</param> /// <param name="code">要解密的字串</param> /// <returns></returns> public static string DecodeBase64(string codeType, string code) { string decode = ""; byte[] bytes = Convert.FromBase64String(code); try { decode = Encoding.GetEncoding(codeType).GetString(bytes); } catch { decode = code; } return decode; }
DES加密解密方法一
/// <summary> /// DES加密 /// </summary> /// <param name="str">要進行DES加密的字串</param> /// <param name="key">密鋝</param> /// <returns></returns> public static string DesEncrypt(this string str, string key) { DESCryptoServiceProvider des = new DESCryptoServiceProvider(); byte[] inputByteArray; inputByteArray = Encoding.Default.GetBytes(str); des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(key, "md5").Substring(0, 8)); des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(key, "md5").Substring(0, 8)); System.IO.MemoryStream ms = new System.IO.MemoryStream(); CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write); cs.Write(inputByteArray, 0, inputByteArray.Length); cs.FlushFinalBlock(); StringBuilder ret = new StringBuilder(); foreach (byte b in ms.ToArray()) { ret.AppendFormat("{0:X2}", b); } return ret.ToString().UrlParamEncode(); } /// <summary> /// DES解密 /// </summary> /// <param name="str">要進行DES解密的字串</param> /// <param name="key">密鋝</param> /// <returns></returns> public static string DesDecrypt(this string str, string key) { str = str.UrlParamDecode(); DESCryptoServiceProvider des = new DESCryptoServiceProvider(); int len; len = str.Length / 2; byte[] inputByteArray = new byte[len]; int x, i; for (x = 0; x < len; x++) { i = Convert.ToInt32(str.Substring(x * 2, 2), 16); inputByteArray[x] = (byte)i; } des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(key, "md5").Substring(0, 8)); des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(key, "md5").Substring(0, 8)); System.IO.MemoryStream ms = new System.IO.MemoryStream(); CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write); cs.Write(inputByteArray, 0, inputByteArray.Length); cs.FlushFinalBlock(); return Encoding.Default.GetString(ms.ToArray()); } 實例：Encrypt(conStr, "ljm");
DES加密解密方法二
/// <summary> /// DES加密算法 /// </summary> /// <param name="encryptString">要加密的字符串</param> /// <param name="sKey">加密碼Key</param> /// <returns>正確返回加密后的結果，錯誤返回原字符串</returns> public static string ToDESEncrypt(string encryptString, string sKey) { try { //加密碼Key必須8位 不夠補齊，長了截取 if (sKey.Length < 8) { int length = sKey.Length; for (int i = 0; i < 8 - length; i++) { sKey += i; } } else { sKey = sKey.Substring(0, 8); } byte[] keyBytes = Encoding.UTF8.GetBytes(sKey); byte[] keyIV = keyBytes; byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString); DESCryptoServiceProvider desProvider = new DESCryptoServiceProvider(); // Java默認的是ECB模式，PKCS5padding； C#默認的是CBC模式，PKCS7padding desProvider.Mode = CipherMode.CBC; desProvider.Padding = PaddingMode.PKCS7; MemoryStream memStream = new MemoryStream(); CryptoStream crypStream = new CryptoStream(memStream, desProvider.CreateEncryptor(keyBytes, keyIV), CryptoStreamMode.Write); crypStream.Write(inputByteArray, 0, inputByteArray.Length); crypStream.FlushFinalBlock(); StringBuilder builder = new StringBuilder(); foreach (byte num in memStream.ToArray()) { builder.AppendFormat("{0:X2}", num); } memStream.Close(); return builder.ToString(); } catch (Exception) { return encryptString; } } /// <summary> /// DES解密算法 /// </summary> /// <param name="decryptString">要解密的字符串</param> /// <param name="sKey">加密Key</param> /// <returns>正確返回解密后的結果，錯發返回原字符串</returns> public static string ToDESDecrypt(string decryptString, string sKey) { //加密碼Key必須8位 不夠補齊，長了截取 if (sKey.Length < 8) { int length = sKey.Length; for (int i = 0; i < 8 - length; i++) { sKey += i; } } else { sKey = sKey.Substring(0, 8); } byte[] keyBytes = Encoding.UTF8.GetBytes(sKey); byte[] keyIV = keyBytes; byte[] inputByteArry = new byte[decryptString.Length / 2]; int num2 = 0; try { for (int i = 0; i < (decryptString.Length / 2); i++) { num2 = Convert.ToInt32(decryptString.Substring(i * 2, 2), 0x10); inputByteArry[i] = (byte)num2; } } catch { Console.Write("i = " + num2); } DESCryptoServiceProvider desProvider = new DESCryptoServiceProvider(); // java 使用CCB模式，PKCS5Padding； // C#默認的是CBC模式，PKCS7Padding, PKCS5Padding, PKCS7padding格式 desProvider.Mode = CipherMode.CBC; desProvider.Padding = PaddingMode.PKCS7; MemoryStream memStream = new MemoryStream(); CryptoStream crypStream = new CryptoStream(memStream, desProvider.CreateDecryptor(keyBytes, keyIV), CryptoStreamMode.Write); crypStream.Write(inputByteArry, 0, inputByteArry.Length); crypStream.FlushFinalBlock(); return Encoding.UTF8.GetString(memStream.ToArray()); }
C#操作XML
public class XmlHelper{ #region 反序列化 /// <summary> /// 反序列化 /// </summary> /// <param name="type">类型</param> /// <param name="xml">XML字符串</param> /// <returns></returns> public static object Deserialize(Type type, string xml) { try { using (StringReader sr = new StringReader(xml)) { XmlSerializer xmldes = new XmlSerializer(type); return xmldes.Deserialize(sr); } } catch (Exception) { return null; } } /// <summary> /// 反序列化 /// </summary> /// <param name="type"></param> /// <param name="xml"></param> /// <returns></returns> public static object Deserialize(Type type, Stream stream) { XmlSerializer xmldes = new XmlSerializer(type); return xmldes.Deserialize(stream); } #endregion #region 序列化XML文件 /// <summary> /// 序列化XML文件 /// </summary> /// <param name="type">类型</param> /// <param name="obj">对象</param> /// <returns></returns> public static string Serializer(Type type, object obj) { MemoryStream Stream = new MemoryStream(); XmlSerializer xml = new XmlSerializer(type); try { xml.Serialize(Stream, obj); } catch (InvalidOperationException) { throw; } Stream.Position = 0; StreamReader sr = new StreamReader(Stream); string str = sr.ReadToEnd(); return str; } #endregion #region 将XML转换为DATATABLE /// <summary> /// 将XML转换为DATATABLE /// </summary> /// <param name="FileURL"></param> /// <returns></returns> public static DataTable XmlAnalysisArray() { try { string FileURL = System.Configuration.ConfigurationManager.AppSettings["Client"].ToString(); DataSet ds = new DataSet(); ds.ReadXml(FileURL); return ds.Tables[0]; } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write(ex.Message.ToString()); return null; } } /// <summary> /// 将XML转换为DATATABLE /// </summary> /// <param name="FileURL"></param> /// <returns></returns> public static DataTable XmlAnalysisArray(string FileURL) { try { DataSet ds = new DataSet(); ds.ReadXml(FileURL); return ds.Tables[0]; } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write(ex.Message.ToString()); return null; } } #endregion #region 获取对应XML节点的值 /// <summary> /// 摘要:获取对应XML节点的值 /// </summary> /// <param name="stringRoot">XML节点的标记</param> /// <returns>返回获取对应XML节点的值</returns> public static string XmlAnalysis(string stringRoot, string xml) { if (stringRoot.Equals("") == false) { try { XmlDocument XmlLoad = new XmlDocument(); XmlLoad.LoadXml(xml); return XmlLoad.DocumentElement.SelectSingleNode(stringRoot).InnerXml.Trim(); } catch (Exception ex) { throw new Exception(ex.Message); } } return ""; } #endregion /// <summary> /// XML转换成DataSet /// </summary> /// <param name="xmlStr"></param> /// <returns></returns> public static DataTable XmlStrConvertToDataTable(string xmlStr) { StringReader xmlReader = null; try { xmlReader = new StringReader(xmlStr); DataSet ds = new DataSet(); ds.ReadXml(xmlReader); return ds.Tables[0]; } catch (Exception) { return null; } finally { if (xmlReader != null) { xmlReader.Close(); } } } public static string ConvertDataTableToXML(DataTable xmlDS) { MemoryStream stream = null; XmlTextWriter writer = null; try { stream = new MemoryStream(); writer = new XmlTextWriter(stream, Encoding.Default); xmlDS.WriteXml(writer); int count = (int)stream.Length; byte[] arr = new byte[count]; stream.Seek(0, SeekOrigin.Begin); stream.Read(arr, 0, count); UTF8Encoding utf = new UTF8Encoding(); return utf.GetString(arr).Trim(); } catch { return String.Empty; } finally { if (writer != null) writer.Close(); } } public static DataSet ConvertXMLToDataSet(string xmlData) { StringReader stream = null; XmlTextReader reader = null; try { DataSet xmlDS = new DataSet(); stream = new StringReader(xmlData); reader = new XmlTextReader(stream); xmlDS.ReadXml(reader); return xmlDS; } catch (Exception ex) { string strTest = ex.Message; return null; } finally { if (reader != null) reader.Close(); } } public static string ConvertDataTableToXml(DataTable dt) { if (dt == null) return null; StringBuilder sb = new StringBuilder(); for (int i = 0; i < dt.Rows.Count; i++) { sb.Append("<"); sb.Append(dt.TableName); sb.Append(">"); for (int row = 0; row < dt.Columns.Count; row++) { sb.Append("<"); sb.Append(dt.Columns[row].ColumnName.ToUpper()); sb.Append(">"); sb.Append(dt.Rows[i][dt.Columns[row].ColumnName].ToString().Replace("&", "&").Replace("'", "'").Replace("\"", """).Replace("<", "<").Replace(">", ">").Replace("00:00:00", "")); sb.Append("</"); sb.Append(dt.Columns[row].ColumnName.ToUpper()); sb.Append(">"); } sb.Append("</"); sb.Append(dt.TableName); sb.Append(">"); } return sb.ToString(); } /// <summary> /// XML文件中特殊字符轉義 /// </summary> /// <param name="xml"></param> /// <returns></returns> public static string ReplaceXmlCode(string xml) { if (StringPlus.IsNullOrDBNull(xml)) { return xml; } else { return xml.Trim().Replace("&", "&").Replace("'", "'").Replace("\"", """).Replace("<", "<").Replace(">", ">").Replace("00:00:00", ""); } } /// <summary> /// 把XML字串转换成实体 /// </summary> /// <typeparam name="T">实体类名</typeparam> /// <param name="xml">XML字串</param> /// <param name="model">实体</param> /// <returns></returns> public static T XmlToModel<T>(string xml) { StringReader xmlReader = new StringReader(xml); XmlSerializer xmlSer = new XmlSerializer(typeof(T)); return (T)xmlSer.Deserialize(xmlReader); } /// <summary> /// 把实体转换成XML字串 /// </summary> /// <typeparam name="T">实体类名</typeparam> /// <param name="model">实体</param> /// <returns></returns> public static string ModelToXml<T>(T model) { MemoryStream stream = new MemoryStream(); XmlSerializer xmlSer = new XmlSerializer(typeof(T)); xmlSer.Serialize(stream, model); stream.Position = 0; StreamReader sr = new StreamReader(stream); return sr.ReadToEnd(); } public static DataTable XmlToTable(string xml) { StringReader xmlReader = null; try { xmlReader = new StringReader(xml); DataSet ds = new DataSet(); ds.ReadXml(xmlReader); return ds.Tables[0]; } catch (Exception) { return null; } finally { if (xmlReader != null) { xmlReader.Close(); } } }}
C#通过aspose.cells.dll导出EXCEL
public class AsposeExcel{ /// <summary> /// 導出方法 /// </summary> /// <param name="dt"></param> /// <param name="filePath"></param> /// <param name="Mytitle"></param> /// <param name="MyIndex"></param> /// <returns></returns> public static Stream ExportExcel(DataTable dt, string filePath, bool Mytitle = true, int[] MyIndex = null) { //初始化 Workbook book = new Workbook(FileFormatType.Xlsx); Worksheet sheet = book.Worksheets[0]; sheet.Name = dt.TableName; if (MyIndex == null) { MyIndex = new int[dt.Columns.Count]; for (int c = 0; c < dt.Columns.Count; c++) { MyIndex[c] = c; } } //添加样式 Style headStyle = SetHeadCellStyle(); Style bodyStyle = SetBodyCellStyle(); if (Mytitle == true) { for (int i = 0; i < dt.Columns.Count; i++) { Cell cell1; if (dt.Columns.Count == 1) { sheet.Cells.Merge(0, 0, 1, dt.Columns.Count); cell1 = sheet.Cells[0, 0]; cell1.PutValue(dt.Columns[i].ColumnName); } else { cell1 = sheet.Cells[0, i]; cell1.PutValue(dt.Columns[i].ColumnName); //添加标题 } cell1.SetStyle(headStyle); sheet.Cells.SetRowHeight(0, 18); } //给第一行之后添加数据 for (int r = 0; r < dt.Rows.Count; r++) { var index = 0; //当前位置 for (int c = 0; c < dt.Columns.Count; c++) { if (MyIndex.Contains(c)) { sheet.Cells[r + 1, c - index].PutValue(dt.Rows[r][c]); //添加内容 sheet.Cells[r + 1, c - index].SetStyle(bodyStyle); } else { index = index + 1; //不存在 位置+1 } } sheet.Cells.SetRowHeight(r + 1, 18); } } else { //没有标题 直接添加数据 for (int r = 0; r < dt.Rows.Count; r++) { var index = 0; for (int c = 0; c < dt.Columns.Count; c++) { if (MyIndex.Contains(c)) { sheet.Cells[r, c - index].PutValue(dt.Rows[r][c]); sheet.Cells[r, c - index].SetStyle(bodyStyle); } else { index = index + 1; //不存在 位置+1 } } sheet.Cells.SetRowHeight(r + 1, 18); } } sheet.AutoFitColumns(); MemoryStream ms = new MemoryStream() ;//book.SaveToStream() book.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx)); return ms; } /// <summary> /// DataSet->Excel【每一個Table生成一個Sheet】 /// </summary> /// <param name="ds"></param> /// <param name="filePath"></param> /// <param name="Mytitle"></param> /// <returns></returns> public static Stream ExportExcel(DataSet ds, string filePath, bool Mytitle = true) { //初始化 Workbook book = new Workbook(FileFormatType.Xlsx); for (int d = 0; d < ds.Tables.Count; d++) { Worksheet sheet = null; if (d == 0) { sheet = book.Worksheets[d]; sheet.Name = ds.Tables[d].TableName; } else { sheet = book.Worksheets.Add(ds.Tables[d].TableName.Trim()); } int[] MyIndex = new int[ds.Tables[d].Columns.Count]; for (int c = 0; c < ds.Tables[d].Columns.Count; c++) { MyIndex[c] = c; } //添加样式 Style headStyle = SetHeadCellStyle(); Style bodyStyle = SetBodyCellStyle(); if (Mytitle == true) { for (int i = 0; i < ds.Tables[d].Columns.Count; i++) { Cell cell1; if (ds.Tables[d].Columns.Count == 1) { sheet.Cells.Merge(0, 0, 1, ds.Tables[d].Columns.Count); cell1 = sheet.Cells[0, 0]; cell1.PutValue(ds.Tables[d].Columns[i].ColumnName); } else { cell1 = sheet.Cells[0, i]; cell1.PutValue(ds.Tables[d].Columns[i].ColumnName); //添加标题 } cell1.SetStyle(headStyle); sheet.Cells.SetRowHeight(0, 18); } //给第一行之后添加数据 for (int r = 0; r < ds.Tables[d].Rows.Count; r++) { var index = 0; //当前位置 for (int c = 0; c < ds.Tables[d].Columns.Count; c++) { if (MyIndex.Contains(c)) { sheet.Cells[r + 1, c - index].PutValue(ds.Tables[d].Rows[r][c].ToString()); //添加内容 sheet.Cells[r + 1, c - index].SetStyle(bodyStyle); } else { index = index + 1; //不存在 位置+1 } } sheet.Cells.SetRowHeight(r + 1, 18); } } else { //没有标题 直接添加数据 for (int r = 0; r < ds.Tables[d].Rows.Count; r++) { var index = 0; for (int c = 0; c < ds.Tables[d].Columns.Count; c++) { if (MyIndex.Contains(c)) { sheet.Cells[r, c - index].PutValue(ds.Tables[d].Rows[r][c].ToString()); sheet.Cells[r, c - index].SetStyle(bodyStyle); } else { index = index + 1; //不存在 位置+1 } } sheet.Cells.SetRowHeight(r + 1, 18); } } sheet.AutoFitColumns(); } MemoryStream ms = new MemoryStream();//book.SaveToStream() book.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx)); return ms; } /// <summary> /// 輸出到瀏覽器下載 /// </summary> /// <param name="ms"></param> /// <param name="fileName"></param> public static void OutputClient(MemoryStream ms, string fileName) { HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; string downFile = Path.GetFileName(fileName);//这里也可以随便取名 string EncodeFileName = HttpUtility.UrlEncode(downFile, System.Text.Encoding.UTF8);//防止中文出现乱码 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + EncodeFileName + ";"); HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("UTF-8"); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Cookies["CompareCookie"].Value = HttpContext.Current.Request["timeCookie"];//前臺必須傳入代號為【timeCookie】的Cookies HttpContext.Current.Response.Flush(); // HttpContext.Current.Response.End(); } /// <summary> /// 設定表頭格式 /// </summary> /// <returns></returns> public static Style SetHeadCellStyle() { Style style = new Aspose.Cells.Style(); style.Pattern = BackgroundType.Solid; style.Font.Name = "SimHei"; //字体 style.Font.Size = 12; //大小 style.Font.IsBold = true; //加粗 //設置單元格上下左右邊框線 style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //文字水平和垂直對齊方式 style.HorizontalAlignment = TextAlignmentType.Center; style.VerticalAlignment = TextAlignmentType.Center; //背景顏色 style.ForegroundColor = System.Drawing.Color.FromArgb(193, 216, 240); return style; } /// <summary> /// 設定表體格式 /// </summary> /// <param name="workbook"></param> /// <returns></returns> public static Style SetBodyCellStyle() { Style style = new Aspose.Cells.Style(); style.Font.Name = "新細明體"; //字体 style.Font.Size = 11; //大小 //設置單元格上下左右邊框線 style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //文字水平和垂直對齊方式 style.HorizontalAlignment = TextAlignmentType.Center; style.VerticalAlignment = TextAlignmentType.Center; return style; } }
调用示例
string filePath = "/sendlog/downLoad/" + fileName; MemoryStream ms = (MemoryStream)AsposeExcel.ExportExcel(rtnDt.Item1, filePath); AsposeExcel.OutputClient(ms, fileName);
C#利用NPOI導出excel
调用示例string filePath = "/sendlog/downLoad/" + fileName;MemoryStream ms = (MemoryStream)NPOIExcelHelper.RenderDataTableToExcelForStyle(excelDt, true, 18, true, null, null, false);NPOIExcelHelper.ExportHSSFWorkbookByWeb(context, ms, fileName);public class NPOIExcelCommon{ public static IWorkbook GetWorkbook(string fullname) { FileStream fileStream = new FileStream(fullname, FileMode.Open, FileAccess.Read, FileShare.Read); byte[] bytes = new byte[fileStream.Length]; fileStream.Read(bytes, 0, bytes.Length); fileStream.Close(); fileStream = null; Stream stream = new MemoryStream(bytes); if (fullname.EndsWith(".xlsx")) { return new HSSFWorkbook(stream); } else { return new HSSFWorkbook(stream); } } /// <summary> /// 获取存储格位置 /// </summary> /// <param name="row">行索引</param> /// <param name="col">列索引</param> /// <returns></returns> public static string GetCellPosition(int row, int col) { col = Convert.ToInt32('A') + col; row = row + 1; return ((char)col) + row.ToString(); }
#region 資料形態转換 public static void WriteSteamToFile(MemoryStream ms, string FileName) { FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); data = null; ms = null; fs = null; } public static void WriteSteamToFile(byte[] data, string FileName) { FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); data = null; fs = null; } public static Stream WorkBookToStream(HSSFWorkbook InputWorkBook) { MemoryStream ms = new MemoryStream(); InputWorkBook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } public static HSSFWorkbook StreamToWorkBook(Stream InputStream) { HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream); return WorkBook; } public static HSSFWorkbook MemoryStreamToWorkBook(MemoryStream InputStream) { HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream as Stream); return WorkBook; } public static MemoryStream WorkBookToMemoryStream(HSSFWorkbook InputStream) { MemoryStream file = new MemoryStream(); InputStream.Write(file); return file; } public static Stream FileToStream(string FileName) { FileInfo fi = new FileInfo(FileName); if (fi.Exists == true) { FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read); return fs; } else return null; } public static Stream MemoryStreamToStream(MemoryStream ms) { return ms as Stream; } #endregion #region DataTable 与 Excel 的资料转换 public static Stream RenderDataTableToExcelForStyle(DataTable sourceTable, bool haveGridLine, int rowheight, bool HasHeader, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth, bool isFormula = false) { IWorkbook workbook = new XSSFWorkbook(); NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; XSSFRow headerRow = null; XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); short sRowHeight = Convert.ToInt16(rowheight * 20); ICellStyle headStyle = SetHeadCellStyle(workbook); ICellStyle cellStyle = SetBodyCellStyle(workbook); IFont font = workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; if (HasHeader) { headerRow = (XSSFRow)sheet.CreateRow(0); headerRow.Height = sRowHeight; foreach (DataColumn column in sourceTable.Columns) { ICell newcell = headerRow.CreateCell(column.Ordinal); if (haveGridLine) { newcell.CellStyle = headStyle; } newcell.SetCellValue(column.ColumnName); } } // handling value. int rowIndex = HasHeader ? 1 : 0; foreach (DataRow row in sourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in sourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { //公式單元格 string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = cellStyle; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString().Trim()); } if (haveGridLine) { newcell.CellStyle = cellStyle; } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0) { foreach (CellRangeAddress range in rangelist) { if (HasHeader) { range.FirstRow++; range.LastRow++; } sheet.AddMergedRegion(range); } } if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } } else { for (int i = 0; i < sourceTable.Columns.Count; i++) { sheet.AutoSizeColumn(i);//自適應列寬 } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.ForceFormulaRecalculation = isFormula; sheet = null; if (HasHeader) { headerRow = null; } workbook = null; return ms; } public static Stream RenderDataTableToExcelForStyle(DataTable sourceTable, bool haveGridLine, int rowheight, bool HasHeader, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth, List<int> colorRowIndex, bool isFormula = false) { IWorkbook workbook = new XSSFWorkbook(); NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; XSSFRow headerRow = null; XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); short sRowHeight = Convert.ToInt16(rowheight * 20); //表頭樣式 ICellStyle headStyle = SetHeadCellStyle(workbook); //單元格樣式 ICellStyle cellStyle = SetBodyCellStyle(workbook); //指定RowIndex樣式 ICellStyle colorStyle = SetColorCellStyle(workbook); IFont font = workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; if (HasHeader) { headerRow = (XSSFRow)sheet.CreateRow(0); headerRow.Height = sRowHeight; foreach (DataColumn column in sourceTable.Columns) { ICell newcell = headerRow.CreateCell(column.Ordinal); if (haveGridLine) { newcell.CellStyle = headStyle; } newcell.SetCellValue(column.ColumnName); } } // handling value. int rowIndex = HasHeader ? 1 : 0; foreach (DataRow row in sourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in sourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { //公式單元格 string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = cellStyle; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString().Trim()); } if (haveGridLine) { if (colorRowIndex.Contains(rowIndex)) { newcell.CellStyle = colorStyle; } else { newcell.CellStyle = cellStyle; } } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0) { foreach (CellRangeAddress range in rangelist) { if (HasHeader) { range.FirstRow++; range.LastRow++; } sheet.AddMergedRegion(range); } } if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } } else { for (int i = 0; i < sourceTable.Columns.Count; i++) { sheet.AutoSizeColumn(i);//自适应列宽 } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.ForceFormulaRecalculation = isFormula; sheet = null; if (HasHeader) { headerRow = null; } workbook = null; return ms; } public static Stream RenderDataTableToExcelFor112Form(DataSet SourceDataSet, bool haveGridLine, int rowheight, bool HasHeader, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth, bool isFormula = false) { IWorkbook workbook = new XSSFWorkbook(); NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; XSSFRow headerRow = null; ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; IDataFormat format = workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("0"); foreach (DataTable SourceTable in SourceDataSet.Tables) { XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(SourceTable.TableName); short sRowHeight = Convert.ToInt16(rowheight * 20); if (HasHeader) { headerRow = (XSSFRow)sheet.CreateRow(0); headerRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = headerRow.CreateCell(column.Ordinal); if (haveGridLine) { newcell.CellStyle = style; } newcell.SetCellValue(column.ColumnName); } } int rowIndex = HasHeader ? 1 : 0; foreach (DataRow row in SourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { //公式單元格 string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = style; } } else { newcell.SetCellValue(row[column].ToString()); if (haveGridLine) { newcell.CellStyle = style; } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0) { foreach (CellRangeAddress range in rangelist) { if (HasHeader) { range.FirstRow++; range.LastRow++; } sheet.AddMergedRegion(range); } } if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } } else { for (int i = 0; i < SourceTable.Columns.Count; i++) { sheet.AutoSizeColumn(i);//自适应列宽 } } sheet.ForceFormulaRecalculation = isFormula; sheet = null; if (HasHeader) { headerRow = null; } } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; } /// <summary> /// 将DataTable转成Workbook(自定资料形态)输出. /// </summary> /// <param name="SourceTable">The source table.</param> /// <returns></returns> public static HSSFWorkbook RenderDataTableToWorkBook(DataTable SourceTable) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); // handling header. foreach (DataColumn column in SourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value. int rowIndex = 1; foreach (DataRow row in SourceTable.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } return workbook; } /// <summary> /// 从位元流读取资料到DataTable. /// </summary> /// <param name="ExcelFileStream">The excel file stream.</param> /// <param name="SheetName">Name of the sheet.</param> /// <param name="HeaderRowIndex">Index of the header row.</param> /// <param name="HaveHeader">if set to <c>true</c> [have header].</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, bool HaveHeader) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(SheetName); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString(); DataColumn column = new DataColumn(ColumnName); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; for (int i = RowStart; i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) dataRow[j] = row.GetCell(j).ToString(); } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary> /// 从位元流读取资料到DataTable. /// </summary> /// <param name="ExcelFileStream">The excel file stream.</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <param name="HeaderRowIndex">Index of the header row.</param> /// <param name="HaveHeader">if set to <c>true</c> [have header].</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, bool HaveHeader) { XSSFWorkbook workbook = new XSSFWorkbook(ExcelFileStream); XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); XSSFRow headerRow = (XSSFRow)sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString(); DataColumn column = new DataColumn(ColumnName); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; for (int i = RowStart; i <= sheet.LastRowNum; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row.FirstCellNum > -1) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } table.Rows.Add(dataRow); } } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } #endregion #region 字串阵列与Excel资料格式转换 /// <summary> /// 建立datatable /// </summary> /// <param name="ColumnName">栏位名用逗号分隔</param> /// <param name="value">dat阵列</param> /// <returns>DataTable</returns> public static DataTable CreateDataTable(string ColumnName, string[,] value) { /* 输入实例 string cname = " name , sex "; string[,] aaz = new string[4, 2]; for (int q = 0; q < 4; q++) for (int r = 0; r < 2; r++) aaz[q, r] = "1"; dataGridView1.DataSource = NewMediaTest1.Model.Utility.DataSetUtil.CreateDataTable(cname, aaz); */ int i, j; DataTable ResultTable = new DataTable(); string[] sep = new string[] { "," }; string[] TempColName = ColumnName.Split(sep, StringSplitOptions.RemoveEmptyEntries); DataColumn[] CName = new DataColumn[TempColName.Length]; for (i = 0; i < TempColName.Length; i++) { DataColumn c1 = new DataColumn(TempColName[i].ToString().Trim(), typeof(object)); ResultTable.Columns.Add(c1); } if (value != null) { for (i = 0; i < value.GetLength(0); i++) { DataRow newrow = ResultTable.NewRow(); for (j = 0; j < TempColName.Length; j++) { newrow[j] = string.Copy(value[i, j].ToString()); } ResultTable.Rows.Add(newrow); } } return ResultTable; } /// <summary> /// Creates the string array. /// </summary> /// <param name="dt">The dt.</param> /// <returns></returns> public static string[,] CreateStringArray(DataTable dt) { int ColumnNum = dt.Columns.Count; int RowNum = dt.Rows.Count; string[,] result = new string[RowNum, ColumnNum]; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { result[i, j] = string.Copy(dt.Rows[i][j].ToString()); } } return result; } /// <summary> /// 将阵列输出成WorkBook(自定资料形态). /// </summary> /// <param name="ColumnName">Name of the column.</param> /// <param name="SourceTable">The source table.</param> /// <returns></returns> public static HSSFWorkbook RenderArrayToWorkBook(string ColumnName, string[,] SourceTable) { DataTable dt = CreateDataTable(ColumnName, SourceTable); return RenderDataTableToWorkBook(dt); } /// <summary> /// 将位元流资料输出成阵列. /// </summary> /// <param name="ExcelFileStream">The excel file stream.</param> /// <param name="SheetName">Name of the sheet.</param> /// <param name="HeaderRowIndex">Index of the header row.</param> /// <param name="HaveHeader">if set to <c>true</c> [have header].</param> /// <returns></returns> public static string[,] RenderArrayFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, bool HaveHeader) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(SheetName); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; for (int i = RowStart; i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) dataRow[j] = row.GetCell(j).ToString(); } ExcelFileStream.Close(); workbook = null; sheet = null; return CreateStringArray(table); } /// <summary> /// 将位元流资料输出成阵列. /// </summary> /// <param name="ExcelFileStream">The excel file stream.</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <param name="HeaderRowIndex">Index of the header row.</param> /// <param name="HaveHeader">if set to <c>true</c> [have header].</param> /// <returns></returns> public static string[,] RenderArrayFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, bool HaveHeader) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; for (int i = RowStart; i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } table.Rows.Add(dataRow); } ExcelFileStream.Close(); workbook = null; sheet = null; return CreateStringArray(table); } #endregion #region 设定字体形态 public static HSSFCellStyle SetCellStyle(HSSFWorkbook workbook, HSSFFont InputFont) { HSSFCellStyle style1 = (HSSFCellStyle)(workbook.CreateCellStyle()); style1.SetFont(InputFont); return style1; } /// <summary> /// 设定字体颜色大小到位元流. /// </summary> /// <param name="InputStream">The input stream.</param> /// <param name="FontName">Name of the font.</param> /// <param name="FontSize">Size of the font.</param> /// <param name="IsAllSheet">if set to <c>true</c> [is all sheet].</param> /// <param name="SheetName">Name of the sheet.</param> /// <returns></returns> public static Stream ApplyStyleToFile(Stream InputStream, string FontName, short FontSize, bool IsAllSheet, params string[] SheetName) { HSSFWorkbook workbook = new HSSFWorkbook(InputStream); HSSFFont font = (HSSFFont)workbook.CreateFont(); HSSFCellStyle Style = (HSSFCellStyle)workbook.CreateCellStyle(); font.FontHeightInPoints = FontSize; font.FontName = FontName; Style.SetFont(font); MemoryStream ms = new MemoryStream(); int i; if (IsAllSheet == true) { for (i = 0; i < workbook.NumberOfSheets; i++) { HSSFSheet Sheets = (HSSFSheet)workbook.GetSheetAt(0); for (int k = Sheets.FirstRowNum; k <= Sheets.LastRowNum; k++) { HSSFRow row = (HSSFRow)Sheets.GetRow(k); for (int l = row.FirstCellNum; l < row.LastCellNum; l++) { HSSFCell Cell = (HSSFCell)row.GetCell(l); Cell.CellStyle = Style; } } } } else { for (i = 0; i < SheetName.Length; i++) { HSSFSheet Sheets = (HSSFSheet)workbook.GetSheet(SheetName[i]); for (int k = Sheets.FirstRowNum; k <= Sheets.LastRowNum; k++) { HSSFRow row = (HSSFRow)Sheets.GetRow(k); for (int l = row.FirstCellNum; l < row.LastCellNum; l++) { HSSFCell Cell = (HSSFCell)row.GetCell(l); Cell.CellStyle = Style; } } } } workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 设定字体颜色大小到位元流. /// </summary> /// <param name="InputStream">The input stream.</param> /// <param name="FontName">Name of the font.</param> /// <param name="FontSize">Size of the font.</param> /// <param name="IsAllSheet">if set to <c>true</c> [is all sheet].</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <returns></returns> public static Stream ApplyStyleToFile(Stream InputStream, string FontName, short FontSize, bool IsAllSheet, params int[] SheetIndex) { HSSFWorkbook workbook = new HSSFWorkbook(InputStream); MemoryStream ms = new MemoryStream(); HSSFFont font = (HSSFFont)workbook.CreateFont(); HSSFCellStyle Style = (HSSFCellStyle)workbook.CreateCellStyle(); font.FontHeightInPoints = FontSize; font.FontName = FontName; Style.SetFont(font); int i; if (IsAllSheet == true) { for (i = 0; i < workbook.NumberOfSheets; i++) { HSSFSheet Sheets = (HSSFSheet)workbook.GetSheetAt(0); for (int k = Sheets.FirstRowNum; k <= Sheets.LastRowNum; k++) { HSSFRow row = (HSSFRow)Sheets.GetRow(k); for (int l = row.FirstCellNum; l < row.LastCellNum; l++) { HSSFCell Cell = (HSSFCell)row.GetCell(l); Cell.CellStyle = Style; } } } } else { for (i = 0; i < SheetIndex.Length; i++) { HSSFSheet Sheets = (HSSFSheet)workbook.GetSheetAt(SheetIndex[i]); for (int k = Sheets.FirstRowNum; k <= Sheets.LastRowNum; k++) { HSSFRow row = (HSSFRow)Sheets.GetRow(k); for (int l = row.FirstCellNum; l < row.LastCellNum; l++) { HSSFCell Cell = (HSSFCell)row.GetCell(l); Cell.CellStyle = Style; } } } } workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 设定字体颜色大小到位档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="InputStream">The input stream.</param> /// <param name="FontName">Name of the font.</param> /// <param name="FontSize">Size of the font.</param> /// <param name="IsAllSheet">if set to <c>true</c> [is all sheet].</param> /// <param name="SheetName">Name of the sheet.</param> public static void ApplyStyleToFile(string FileName, Stream InputStream, string FontName, short FontSize, bool IsAllSheet, params string[] SheetName) { MemoryStream ms = ApplyStyleToFile(InputStream, FontName, FontSize, IsAllSheet, SheetName) as MemoryStream; WriteSteamToFile(ms, FileName); } /// <summary> /// 设定字体颜色大小到位档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="InputStream">The input stream.</param> /// <param name="FontName">Name of the font.</param> /// <param name="FontSize">Size of the font.</param> /// <param name="IsAllSheet">if set to <c>true</c> [is all sheet].</param> /// <param name="SheetIndex">Index of the sheet.</param> public static void ApplyStyleToFile(string FileName, Stream InputStream, string FontName, short FontSize, bool IsAllSheet, params int[] SheetIndex) { MemoryStream ms = ApplyStyleToFile(InputStream, FontName, FontSize, IsAllSheet, SheetIndex) as MemoryStream; WriteSteamToFile(ms, FileName); } #endregion #region 设定单元格样式 /// <summary> /// 设定表頭格式 /// </summary> /// <param name="workbook"></param> /// <returns></returns> public static ICellStyle SetHeadCellStyle(IWorkbook workbook) { ICellStyle cellStyle = workbook.CreateCellStyle(); //设置单元格上下左右边框线 cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; //边框颜色 cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Brown.Index; cellStyle.TopBorderColor = HSSFColor.OliveGreen.Brown.Index; cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Brown.Index; cellStyle.RightBorderColor = HSSFColor.OliveGreen.Brown.Index; //背景顏色 cellStyle.FillForegroundColor = HSSFColor.LightGreen.Index; cellStyle.FillBackgroundColor = HSSFColor.Maroon.Index; //填充顏色【与背景顏色配套使用】,默认为:FillPattern.NoFill cellStyle.FillPattern = FillPattern.SolidForeground; //设置单元格字体 IFont fontcolorblue = workbook.CreateFont(); fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index; fontcolorblue.IsBold = false; //fontcolorblue.FontName = "微软正黑体"; //fontcolorblue.FontName = "SimHei"; cellStyle.SetFont(fontcolorblue); return cellStyle; } /// <summary> /// 设定表体格式 /// </summary> /// <param name="workbook"></param> /// <returns></returns> public static ICellStyle SetBodyCellStyle(IWorkbook workbook) { ICellStyle cellStyle = workbook.CreateCellStyle(); //设置单元格上下左右边框线 cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; //边框颜色 cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index; cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index; cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index; cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index; //设置单元格字体 IFont fontcolorblue = workbook.CreateFont(); fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index; fontcolorblue.IsBold = false; cellStyle.SetFont(fontcolorblue); //设置时间格式 IDataFormat format = workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("@"); return cellStyle; } /// <summary> /// 设定表头格式 /// </summary> /// <param name="workbook"></param> /// <returns></returns> public static ICellStyle SetColorCellStyle(IWorkbook workbook) { ICellStyle cellStyle = workbook.CreateCellStyle(); //设置单元格上下左右边框线 cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; //边框颜色 cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Brown.Index; cellStyle.TopBorderColor = HSSFColor.OliveGreen.Brown.Index; cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Brown.Index; cellStyle.RightBorderColor = HSSFColor.OliveGreen.Brown.Index; //背景顏色 cellStyle.FillForegroundColor = HSSFColor.Yellow.Index; cellStyle.FillBackgroundColor = HSSFColor.Maroon.Index; //填填充顏色【与背景顏色配套使用】,默认为:FillPattern.NoFill cellStyle.FillPattern = FillPattern.SolidForeground; //设置单元格字体 IFont fontcolorblue = workbook.CreateFont(); fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index; fontcolorblue.IsBold = false; cellStyle.SetFont(fontcolorblue); return cellStyle; } #endregion #region 设定格线 /// <summary> /// 定格线到位元流. /// </summary> /// <param name="InputSteam">The input steam.</param> /// <param name="haveGridLine">if set to <c>true</c> [have grid line].</param> /// <param name="SheetName">Name of the sheet.</param> /// <returns></returns> public static Stream SetGridLine(Stream InputSteam, bool haveGridLine, params string[] SheetName) { HSSFWorkbook workbook = new HSSFWorkbook(InputSteam); MemoryStream ms = new MemoryStream(); if (SheetName == null) { for (int i = 0; i < workbook.NumberOfSheets; i++) { HSSFSheet s1 = (HSSFSheet)workbook.GetSheetAt(i); s1.DisplayGridlines = haveGridLine; } } else { foreach (string TempSheet in SheetName) { HSSFSheet s1 = (HSSFSheet)workbook.GetSheet(TempSheet); s1.DisplayGridlines = haveGridLine; } } workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 定格线到位元流. /// </summary> /// <param name="InputSteam">The input steam.</param> /// <param name="haveGridLine">if set to <c>true</c> [have grid line].</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <returns></returns> public static Stream SetGridLine(Stream InputSteam, bool haveGridLine, params int[] SheetIndex) { HSSFWorkbook workbook = new HSSFWorkbook(InputSteam); MemoryStream ms = new MemoryStream(); if (SheetIndex == null) { for (int i = 0; i < workbook.NumberOfSheets; i++) { HSSFSheet s1 = (HSSFSheet)workbook.GetSheetAt(i); s1.DisplayGridlines = haveGridLine; } } else { foreach (int TempSheet in SheetIndex) { HSSFSheet s1 = (HSSFSheet)workbook.GetSheetAt(TempSheet); s1.DisplayGridlines = haveGridLine; } } workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 定格线到档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="InputSteam">The input steam.</param> /// <param name="haveGridLine">if set to <c>true</c> [have grid line].</param> /// <param name="SheetIndex">Index of the sheet.</param> public static void SetGridLine(string FileName, Stream InputSteam, bool haveGridLine, params int[] SheetIndex) { MemoryStream ms = SetGridLine(InputSteam, haveGridLine, SheetIndex) as MemoryStream; WriteSteamToFile(ms, FileName); } /// <summary> /// 定格线到档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="InputSteam">The input steam.</param> /// <param name="haveGridLine">if set to <c>true</c> [have grid line].</param> /// <param name="SheetName">Name of the sheet.</param> public static void SetGridLine(string FileName, Stream InputSteam, bool haveGridLine, params string[] SheetName) { MemoryStream ms = SetGridLine(InputSteam, haveGridLine, SheetName) as MemoryStream; WriteSteamToFile(ms, FileName); } #endregion #region 撷取字串从excel档案 /// <summary> /// 从位元流将資料转成字串輸出 /// </summary> /// <param name="InputStream">The input stream.</param> /// <returns></returns> public static string ExtractStringFromFileStream(Stream InputStream) { HSSFWorkbook HBook = new HSSFWorkbook(InputStream); ExcelExtractor extractor = new ExcelExtractor(HBook); return extractor.Text; } /// <summary> /// 从档案将資料转成字串輸出 /// </summary> /// <param name="FileName">Name of the file.</param> /// <returns></returns> public static string ExtractStringFromFileStream(string FileName) { FileInfo fi = new FileInfo(FileName); if (fi.Exists == true) { using (FileStream fs = fi.Open(FileMode.Open)) { HSSFWorkbook HBook = new HSSFWorkbook(fs); ExcelExtractor extractor = new ExcelExtractor(HBook); return extractor.Text; } } else return null; } #endregion #region 设定群組 /// <summary> /// 设定群組到位元流. /// </summary> /// <param name="SheetName">Name of the sheet.</param> /// <param name="IsRow">if set to <c>true</c> [is row].</param> /// <param name="From">From.</param> /// <param name="End">The end.</param> /// <returns></returns> public static Stream CreateGroup(string SheetName, bool IsRow, int From, int End) { MemoryStream ms = new MemoryStream(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sh = (HSSFSheet)workbook.CreateSheet(SheetName); for (int i = 0; i <= End; i++) { sh.CreateRow(i); } if (IsRow == true) sh.GroupRow(From, End); else sh.GroupColumn((short)From, (short)End); workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 建立群組到档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="SheetName">Name of the sheet.</param> /// <param name="IsRow">if set to <c>true</c> [is row].</param> /// <param name="From">From.</param> /// <param name="End">The end.</param> public static void CreateGroup(string FileName, string SheetName, bool IsRow, int From, int End) { MemoryStream ms = CreateGroup(SheetName, IsRow, From, End) as MemoryStream; WriteSteamToFile(ms, FileName); } #endregion #region 合并储存格 /// <summary> /// 合并储存格于位元流. /// </summary> /// <param name="InputStream">The input stream.</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <param name="RowFrom">The row from.</param> /// <param name="ColumnFrom">The column from.</param> /// <param name="RowTo">The row to.</param> /// <param name="ColumnTo">The column to.</param> /// <returns></returns> public static Stream MergeCell(Stream InputStream, int SheetIndex, int RowFrom, int ColumnFrom, int RowTo, int ColumnTo) { HSSFWorkbook workbook = new HSSFWorkbook(InputStream); MemoryStream ms = new MemoryStream(); HSSFSheet sheet1 = (HSSFSheet)workbook.GetSheetAt(SheetIndex); sheet1.AddMergedRegion(new CellRangeAddress(RowFrom, RowTo, ColumnFrom, ColumnTo)); workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 合并储存格于档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="InputStream">The input stream.</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <param name="RowFrom">The row from.</param> /// <param name="ColumnFrom">The column from.</param> /// <param name="RowTo">The row to.</param> /// <param name="ColumnTo">The column to.</param> public static void MergeCell(string FileName, Stream InputStream, int SheetIndex, int RowFrom, int ColumnFrom, int RowTo, int ColumnTo) { MemoryStream ms = MergeCell(InputStream, SheetIndex, RowFrom, ColumnFrom, RowTo, ColumnTo) as MemoryStream; WriteSteamToFile(ms, FileName); } /// <summary> /// 建立新位元流並合并储存格. /// </summary> /// <param name="RowFrom">The row from.</param> /// <param name="ColumnFrom">The column from.</param> /// <param name="RowTo">The row to.</param> /// <param name="ColumnTo">The column to.</param> /// <returns></returns> public static Stream MergeCell(int RowFrom, int ColumnFrom, int RowTo, int ColumnTo) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet(); sheet1.AddMergedRegion(new Region(RowFrom, ColumnFrom, RowTo, ColumnTo)); workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 建立新档案並合并储存格. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="RowFrom">The row from.</param> /// <param name="ColumnFrom">The column from.</param> /// <param name="RowTo">The row to.</param> /// <param name="ColumnTo">The column to.</param> public static void MergeCell(string FileName, int RowFrom, int ColumnFrom, int RowTo, int ColumnTo) { MemoryStream ms = MergeCell(RowFrom, ColumnFrom, RowTo, ColumnTo) as MemoryStream; WriteSteamToFile(ms, FileName); } #endregion #region 通过模板导出Excel /// <summary> /// 数据导出.xls -wps /// </summary> /// <param name="localFilePath">模板路径</param> /// <param name="dtSource">数据源 data</param> /// <param name="downLoadName">下载名称</param> public static void ExportXlsByModel(string localFilePath, DataTable dtSource, string downLoadName, int rowIndex, int rowheight, List<CellRangeAddress> rangelist, bool isFormula = false, bool haveGridLine = true) { if (!File.Exists(localFilePath)) new AccessViolationException("模板不存在"); FileStream file = new FileStream(localFilePath, FileMode.Open, FileAccess.Read); if (null == file) { throw new Exception("模板文件打开失败"); } IWorkbook workbook = null; bool is2003 = localFilePath.EndsWith(".xls"); if (is2003) { workbook = new HSSFWorkbook(file); } else { workbook = new XSSFWorkbook(file); } file.Close(); file = null; ISheet sheet = null; if (is2003) { sheet = (HSSFSheet)workbook.GetSheetAt(0); } else { sheet = (XSSFSheet)workbook.GetSheetAt(0); } short sRowHeight = Convert.ToInt16(rowheight * 20); ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; IFont font = workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; foreach (DataRow row in dtSource.Rows) { IRow dataRow = null; if (is2003) { dataRow = (HSSFRow)sheet.CreateRow(rowIndex); } else { dataRow = (XSSFRow)sheet.CreateRow(rowIndex); } dataRow.Height = sRowHeight; foreach (DataColumn column in dtSource.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { //公式單元格 string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = style; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString()); } if (haveGridLine) { newcell.CellStyle = style; } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0) { foreach (CellRangeAddress range in rangelist) { sheet.AddMergedRegion(range); } } if (is2003) { ExportHSSFWorkbookByWeb((HSSFWorkbook)workbook, null, downLoadName); } else { ExportHSSFWorkbookByWeb(null, (XSSFWorkbook)workbook, downLoadName); } } #endregion #region NPOI-下载数据 /// <summary> /// 将指定的HSSFWorkbook输出到流 /// </summary> /// <param name="workbook"></param> /// <param name="strFileName">文件名</param> public static void ExportHSSFWorkbookByWeb(HSSFWorkbook hssWorkbook = null, XSSFWorkbook xssWorkbook = null, string strFileName = null) { using (MemoryStream ms = new MemoryStream()) { if (hssWorkbook == null) xssWorkbook.Write(ms); else hssWorkbook.Write(ms); ms.Flush(); ms.Position = 0; HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); curContext.Response.ContentType = "application/octet-stream"; long fileSize = ms.Length; curContext.Response.AddHeader("Content-Length", fileSize.ToString()); curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); } } /// <summary> /// 将Excel数据直接输出前台保存【异步下载】 /// 頁面调用方式:window.open(href); /// </summary> /// <param name="context"></param> /// <param name="ms"></param> /// <param name="fileName">文件名称</param> /// <param name="cookieinfo">前台传入的Cookies信息</param> public static void ExportHSSFWorkbookByWeb(HttpContext context, MemoryStream ms, string fileName) { context.Response.Buffer = true; context.Response.Clear(); context.Response.ContentType = "application/download"; string downFile = System.IO.Path.GetFileName(fileName);//这里也可以随便取名 string EncodeFileName = HttpUtility.UrlEncode(downFile, System.Text.Encoding.UTF8);//防止中文出现乱码 context.Response.AddHeader("Content-Disposition", "attachment;filename=" + EncodeFileName + ";"); context.Response.BinaryWrite(ms.ToArray());//返回文件数据给客户端下载 context.Response.Cookies["CompareCookie"].Value = context.Request["timeCookie"];//前台必须传入代号为【timeCookie】的Cookies context.Response.Flush(); } #endregion #region 设定储存格公式 /// <summary> /// 设定储存格公式于位元流. /// </summary> /// <param name="InputStream">The input stream.</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <param name="Formula">The formula.</param> /// <param name="RowIndex">Index of the row.</param> /// <param name="ColumnIndex">Index of the column.</param> /// <returns></returns> public static Stream SetFormula(Stream InputStream, int SheetIndex, string Formula, int RowIndex, int ColumnIndex) { //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file' //So we insert three sheet just like what Excel does HSSFWorkbook workbook = new HSSFWorkbook(InputStream); MemoryStream ms = new MemoryStream(); HSSFSheet sheet1 = (HSSFSheet)workbook.GetSheetAt(SheetIndex); sheet1.CreateRow(RowIndex).CreateCell(ColumnIndex).SetCellFormula(Formula); workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 设定储存格公式于档案. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="InputStream">The input stream.</param> /// <param name="SheetIndex">Index of the sheet.</param> /// <param name="Formula">The formula.</param> /// <param name="RowIndex">Index of the row.</param> /// <param name="ColumnIndex">Index of the column.</param> public static void SetFormula(string FileName, Stream InputStream, int SheetIndex, string Formula, int RowIndex, int ColumnIndex) { MemoryStream ms = SetFormula(InputStream, SheetIndex, Formula, RowIndex, ColumnIndex) as MemoryStream; WriteSteamToFile(ms, FileName); } /// <summary> /// 建立新位元流并设定储存格公式. /// </summary> /// <param name="Formula">The formula.</param> /// <param name="RowIndex">Index of the row.</param> /// <param name="ColumnIndex">Index of the column.</param> /// <returns></returns> public static Stream SetFormula(string Formula, int RowIndex, int ColumnIndex) { //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file' //So we insert three sheet just like what Excel does HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet(); sheet1.CreateRow(RowIndex).CreateCell(ColumnIndex).SetCellFormula(Formula); workbook.Write(ms); ms.Flush(); return ms; } /// <summary> /// 建立新档案並设定储存格公式. /// </summary> /// <param name="FileName">Name of the file.</param> /// <param name="Formula">The formula.</param> /// <param name="RowIndex">Index of the row.</param> /// <param name="ColumnIndex">Index of the column.</param> public static void SetFormula(string FileName, string Formula, int RowIndex, int ColumnIndex) { MemoryStream ms = SetFormula(Formula, RowIndex, ColumnIndex) as MemoryStream; WriteSteamToFile(ms, FileName); } #endregion public static Stream RenderDataTableToExcelForStylefont(DataTable SourceTable, bool haveGridLine, int startcoloums, int rowheight, bool HasHeader, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth, Dictionary<int, int> colfont = null, Dictionary<int, int> colper = null, bool isqianfenwei = false, bool isFormula = false) { IWorkbook workbook = new XSSFWorkbook(); NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; XSSFRow headerRow = null; XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); short sRowHeight = Convert.ToInt16(rowheight * 20); ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; IDataFormat format = workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("#,##0");// IFont fontfont = workbook.CreateFont(); fontfont.FontName = "Calibri"; style.SetFont(fontfont); ICellStyle stylefont = workbook.CreateCellStyle(); stylefont.BorderBottom = BorderStyle.Thin; stylefont.BorderLeft = BorderStyle.Thin; stylefont.BorderRight = BorderStyle.Thin; stylefont.BorderTop = BorderStyle.Thin; stylefont.VerticalAlignment = VerticalAlignment.Center; IFont font = workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; stylefont.SetFont(font); ICellStyle stylecolor = workbook.CreateCellStyle(); stylecolor.BorderBottom = BorderStyle.Thin; stylecolor.BorderLeft = BorderStyle.Thin; stylecolor.BorderRight = BorderStyle.Thin; stylecolor.BorderTop = BorderStyle.Thin; stylecolor.VerticalAlignment = VerticalAlignment.Center; IFont fontcolor = workbook.CreateFont(); fontcolor.Color = (short)NPOI.SS.UserModel.FontColor.Red; stylecolor.SetFont(fontcolor); IDataFormat formatcolor = workbook.CreateDataFormat(); stylecolor.DataFormat = formatcolor.GetFormat("#,##0"); ICellStyle styleper = workbook.CreateCellStyle(); styleper.BorderBottom = BorderStyle.Thin; styleper.BorderLeft = BorderStyle.Thin; styleper.BorderRight = BorderStyle.Thin; styleper.BorderTop = BorderStyle.Thin; styleper.VerticalAlignment = VerticalAlignment.Center; IDataFormat dataformat = workbook.CreateDataFormat(); styleper.DataFormat = dataformat.GetFormat("0%"); if (HasHeader) { headerRow = (XSSFRow)sheet.CreateRow(0); headerRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = headerRow.CreateCell(column.Ordinal); if (haveGridLine) { newcell.CellStyle = style; } newcell.SetCellValue(column.ColumnName); } }
int rowIndex = HasHeader ? 1 : 0; foreach (DataRow row in SourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = style; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString()); } if (haveGridLine) { newcell.CellStyle = style; } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0) { foreach (CellRangeAddress range in rangelist) { if (HasHeader) { range.FirstRow++; range.LastRow++; } sheet.AddMergedRegion(range); } } if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } } else { for (int i = 0; i < SourceTable.Columns.Count; i++) { sheet.AutoSizeColumn(i); } } if (colfont != null && colfont.Count > 0) { foreach (var item in colfont) { XSSFRow row = (XSSFRow)sheet.GetRow(item.Key); ICell cell = row.GetCell(item.Value); cell.CellStyle = stylefont; } } if (colper != null && colper.Count > 0) { foreach (var item in colper) { for (int i = 1; i < SourceTable.Rows.Count; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); ICell cell = row.GetCell(item.Key); cell.CellStyle = styleper; } } } for (int i = 1; i < SourceTable.Rows.Count; i++) { for (int j = startcoloums; j < SourceTable.Columns.Count; j++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); ICell cell = row.GetCell(j); string cellcell = row.GetCell(j).ToString(); decimal ii = cellcell == "" ? 0 : Convert.ToDecimal(cellcell); if (ii < 0) { cell.CellStyle = stylecolor; } } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.ForceFormulaRecalculation = isFormula; sheet = null; if (HasHeader) { headerRow = null; } workbook = null; return ms; } public static Stream RenderDataTableToExcelForStylefontYS(DataTable SourceTable, bool haveGridLine, int startrow, int startcoloum, int rowheight, int btlength, bool HasHeader, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth, Dictionary<int, int> leftfontcolum, List<int> fontrow, List<int> backcolorrow, bool isFormula = false, List<int> startcloums = null, List<int> interval = null) { IWorkbook workbook = new XSSFWorkbook(); NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; XSSFRow headerRow = null; XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); short sRowHeight = Convert.ToInt16(rowheight * 20); ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Right; IDataFormat format = workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("#,##0");// IFont fontfont = workbook.CreateFont(); fontfont.FontName = "Calibri"; style.SetFont(fontfont); ICellStyle stylefscolor = workbook.CreateCellStyle(); stylefscolor.BorderBottom = BorderStyle.Thin; stylefscolor.BorderLeft = BorderStyle.Thin; stylefscolor.BorderRight = BorderStyle.Thin; stylefscolor.BorderTop = BorderStyle.Thin; stylefscolor.VerticalAlignment = VerticalAlignment.Center; stylefscolor.Alignment = HorizontalAlignment.Right; IFont fontcolor = workbook.CreateFont(); fontcolor.Color = (short)NPOI.SS.UserModel.FontColor.Red; fontcolor.FontName = "Calibri"; stylefscolor.SetFont(fontcolor); IDataFormat formatcolor = workbook.CreateDataFormat(); stylefscolor.DataFormat = formatcolor.GetFormat("#,##0");// ICellStyle style_leftfontcolum = workbook.CreateCellStyle(); style_leftfontcolum.BorderBottom = BorderStyle.Thin; style_leftfontcolum.BorderLeft = BorderStyle.Thin; style_leftfontcolum.BorderRight = BorderStyle.Thin; style_leftfontcolum.BorderTop = BorderStyle.Thin; style_leftfontcolum.VerticalAlignment = VerticalAlignment.Center; style_leftfontcolum.Alignment = HorizontalAlignment.Left; IFont font_leftfontcolum = workbook.CreateFont(); font_leftfontcolum.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font_leftfontcolum.FontName = "Calibri"; style_leftfontcolum.SetFont(font_leftfontcolum); //表头加粗 ICellStyle style_fontrow = workbook.CreateCellStyle(); style_fontrow.BorderBottom = BorderStyle.Thin; style_fontrow.BorderLeft = BorderStyle.Thin; style_fontrow.BorderRight = BorderStyle.Thin; style_fontrow.BorderTop = BorderStyle.Thin; IFont font_fontrow = workbook.CreateFont(); font_fontrow.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; style_fontrow.SetFont(font_fontrow); //背景色行(整數） ICellStyle style_backcolorrow = workbook.CreateCellStyle(); style_backcolorrow.BorderBottom = BorderStyle.Thin; style_backcolorrow.BorderLeft = BorderStyle.Thin; style_backcolorrow.BorderRight = BorderStyle.Thin; style_backcolorrow.BorderTop = BorderStyle.Thin; style_backcolorrow.VerticalAlignment = VerticalAlignment.Center; style_backcolorrow.Alignment = HorizontalAlignment.Right; IFont font_backcolorrow = workbook.CreateFont(); font_backcolorrow.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; style_backcolorrow.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; style_backcolorrow.FillPattern = FillPattern.SolidForeground; style_backcolorrow.SetFont(font_backcolorrow); IDataFormat backcolorrowz = workbook.CreateDataFormat(); style_backcolorrow.DataFormat = backcolorrowz.GetFormat("#,##0"); //背景色行(kuaijikemu） ICellStyle style_backcolorrowkm = workbook.CreateCellStyle(); style_backcolorrowkm.BorderBottom = BorderStyle.Thin; style_backcolorrowkm.BorderLeft = BorderStyle.Thin; style_backcolorrowkm.BorderRight = BorderStyle.Thin; style_backcolorrowkm.BorderTop = BorderStyle.Thin; style_backcolorrowkm.VerticalAlignment = VerticalAlignment.Center; style_backcolorrowkm.Alignment = HorizontalAlignment.Left; IFont font_backcolorrowkm = workbook.CreateFont(); font_backcolorrowkm.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; style_backcolorrowkm.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; style_backcolorrowkm.FillPattern = FillPattern.SolidForeground; style_backcolorrowkm.SetFont(font_backcolorrowkm); //背景色行(負數） ICellStyle style_backcolorrowfs = workbook.CreateCellStyle(); style_backcolorrowfs.BorderBottom = BorderStyle.Thin; style_backcolorrowfs.BorderLeft = BorderStyle.Thin; style_backcolorrowfs.BorderRight = BorderStyle.Thin; style_backcolorrowfs.BorderTop = BorderStyle.Thin; style_backcolorrowfs.VerticalAlignment = VerticalAlignment.Center; style_backcolorrowfs.Alignment = HorizontalAlignment.Right; IFont font_backcolorrowfs = workbook.CreateFont(); font_backcolorrowfs.Color = (short)NPOI.SS.UserModel.FontColor.Red; font_backcolorrowfs.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; style_backcolorrowfs.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; style_backcolorrowfs.FillPattern = FillPattern.SolidForeground; style_backcolorrowfs.SetFont(font_backcolorrowfs); IDataFormat backcolorrowf = workbook.CreateDataFormat(); style_backcolorrowfs.DataFormat = backcolorrowf.GetFormat("#,##0"); //百分比 ICellStyle perstyle = workbook.CreateCellStyle(); perstyle.BorderBottom = BorderStyle.Thin; perstyle.BorderLeft = BorderStyle.Thin; perstyle.BorderRight = BorderStyle.Thin; perstyle.BorderTop = BorderStyle.Thin; perstyle.VerticalAlignment = VerticalAlignment.Center; perstyle.Alignment = HorizontalAlignment.Right; IDataFormat performat = workbook.CreateDataFormat(); perstyle.DataFormat = performat.GetFormat("0%"); //百分比加粗 ICellStyle perstylefont = workbook.CreateCellStyle(); perstylefont.BorderBottom = BorderStyle.Thin; perstylefont.BorderLeft = BorderStyle.Thin; perstylefont.BorderRight = BorderStyle.Thin; perstylefont.BorderTop = BorderStyle.Thin; perstylefont.VerticalAlignment = VerticalAlignment.Center; perstylefont.Alignment = HorizontalAlignment.Right; IDataFormat performatfont = workbook.CreateDataFormat(); IFont font_perfontrow = workbook.CreateFont(); font_perfontrow.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; perstylefont.SetFont(font_perfontrow); perstylefont.DataFormat = performatfont.GetFormat("0%"); //百分比加粗 ICellStyle perstylefontbk = workbook.CreateCellStyle(); perstylefontbk.BorderBottom = BorderStyle.Thin; perstylefontbk.BorderLeft = BorderStyle.Thin; perstylefontbk.BorderRight = BorderStyle.Thin; perstylefontbk.BorderTop = BorderStyle.Thin; perstylefontbk.VerticalAlignment = VerticalAlignment.Center; perstylefontbk.Alignment = HorizontalAlignment.Right; IDataFormat performatfontbk = workbook.CreateDataFormat(); IFont font_perfontrowbk = workbook.CreateFont(); font_perfontrowbk.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; perstylefontbk.SetFont(font_perfontrowbk); perstylefontbk.DataFormat = performatfontbk.GetFormat("0%"); perstylefontbk.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; perstylefontbk.FillPattern = FillPattern.SolidForeground; if (HasHeader) { headerRow = (XSSFRow)sheet.CreateRow(0); headerRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = headerRow.CreateCell(column.Ordinal); if (haveGridLine) { newcell.CellStyle = style; } newcell.SetCellValue(column.ColumnName); } }
int rowIndex = HasHeader ? 1 : 0; foreach (DataRow row in SourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = style; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString()); } if (haveGridLine) { newcell.CellStyle = style; } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0) { foreach (CellRangeAddress range in rangelist) { if (HasHeader) { range.FirstRow++; range.LastRow++; } sheet.AddMergedRegion(range); } } //整行加粗 if (fontrow != null && fontrow.Count > 0) { foreach (var item in fontrow) { XSSFRow row = (XSSFRow)sheet.GetRow(item); for (int i = 0; i < SourceTable.Columns.Count; i++) { ICell cell = row.GetCell(i); cell.CellStyle = style_fontrow; } } } //设置百分比 if (startcloums != null && interval != null) { for (int i = startrow; i < SourceTable.Columns.Count; i++) { foreach (var startcolum in startcloums) { for (int m = Convert.ToInt32(startcolum.ToString()); m < SourceTable.Columns.Count + 1; m = m + Convert.ToInt32(interval[0].ToString())) { if (i == m) { for (int j = startrow; j < SourceTable.Rows.Count + 1; j++) { XSSFRow row = (XSSFRow)sheet.GetRow(j); ICell cell = row.GetCell(m); if (fontrow != null && fontrow.Count > 0) { foreach (var item in fontrow) { if (item == j) { cell.CellStyle = perstylefont; } else { cell.CellStyle = perstyle; } } } } } } } } }
if (leftfontcolum != null && leftfontcolum.Count > 0)
{
for (int i = 0; i < SourceTable.Rows.Count + btlength; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
foreach (var item in leftfontcolum)
{
ICell cell = row.GetCell(item.Key);
cell.CellStyle = style_leftfontcolum;
}
}
}
//负数变红
for (int i = startrow; i < SourceTable.Rows.Count + btlength; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
for (int j = 0; j < SourceTable.Columns.Count; j++)
{
ICell cell = row.GetCell(j);
string cellcell = row.GetCell(j).ToString();
if (cellcell.StartsWith("-")) { cell.CellStyle = stylefscolor; } } } //背景色 if (backcolorrow != null && backcolorrow.Count > 0) { foreach (var item in backcolorrow) { XSSFRow row = (XSSFRow)sheet.GetRow(item); for (int i = 0; i < SourceTable.Columns.Count; i++) { ICell cell = row.GetCell(i); string cellcell = row.GetCell(i).ToString(); if (i < startcoloum) { cell.CellStyle = style_backcolorrowkm; } else { if (cellcell.StartsWith("-")) { cell.CellStyle = style_backcolorrowfs; } else { cell.CellStyle = style_backcolorrow; } } } } } if (backcolorrow != null && backcolorrow.Count > 0) { if (startcloums != null && interval != null) { foreach (var itemrow in backcolorrow) { XSSFRow row = (XSSFRow)sheet.GetRow(itemrow); foreach (var startcolum in startcloums) { for (int m = Convert.ToInt32(startcolum.ToString()); m < SourceTable.Columns.Count + 1; m = m + Convert.ToInt32(interval[0].ToString())) { ICell cell = row.GetCell(m); cell.CellStyle = perstylefontbk; } } } } } //列宽 if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } for (int j = startcoloum; j < SourceTable.Columns.Count; j++) { sheet.SetColumnWidth(j, 5000); } } else { for (int i = 0; i < SourceTable.Columns.Count; i++) { sheet.AutoSizeColumn(i); } }
workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.ForceFormulaRecalculation = isFormula; sheet = null; if (HasHeader) { headerRow = null; } workbook = null; return ms; } /// <summary> /// 模板导出 /// </summary> /// <param name="localFilePath">模板</param> /// <param name="SourceTable">数据来源</param> /// <param name="downLoadName">导出后命名</param> /// <param name="haveGridLine">是否要加边框</param> /// <param name="startrow">起始行</param> /// <param name="rowheight"></param> /// <param name="HasHeader"></param> /// <param name="rangelist">合并单元格</param> /// <param name="colwidth">列宽</param> /// <param name="colbold">是否加粗</param> /// <param name="colper">百分比</param> /// <param name="rowbold">按行加粗</param> /// <param name="rowcolor">按行加颜色</param> /// <param name="numformat">数字单元格格式</param> /// <param name="satrtcolums">起始列</param> /// <param name="centerCol">居中的列</param> /// <param name="leftCol">居左的列</param> /// <param name="percentformat">百分比格式</param> /// <param name="specialCells">特殊栏位的值</param> public static void ExportDataTableToExcelForTemplete(string localFilePath, DataTable SourceTable, string downLoadName, bool haveGridLine, int startrow, int satrtcolums, int rowheight, bool HasHeader, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth, Dictionary<int, int> colbold = null, List<int> colper = null, List<int> rowbold = null, Dictionary<int, int> rowcolor = null, List<int> centerCol = null, List<int> leftCol = null, string numformat = "0", string percentformat = "0%", List<Tuple<int, int, string>> specialCells = null) { if (!File.Exists(localFilePath)) new AccessViolationException("模板不存在"); FileStream file = new FileStream(localFilePath, FileMode.Open, FileAccess.Read); if (null == file) { throw new Exception("模板文件打开失败"); } XSSFWorkbook workbook = new XSSFWorkbook(file); file.Close(); file = null; XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); short sRowHeight = Convert.ToInt16(rowheight * 20); //靠右 ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Right; IDataFormat format = workbook.CreateDataFormat(); if (!string.IsNullOrEmpty(numformat)) { style.DataFormat = format.GetFormat(numformat); } IFont fontfont = workbook.CreateFont(); fontfont.FontName = "Calibri"; fontfont.FontHeightInPoints = 10; style.SetFont(fontfont); //靠左 ICellStyle styleleft = workbook.CreateCellStyle(); styleleft.BorderBottom = BorderStyle.Thin; styleleft.BorderLeft = BorderStyle.Thin; styleleft.BorderRight = BorderStyle.Thin; styleleft.BorderTop = BorderStyle.Thin; styleleft.VerticalAlignment = VerticalAlignment.Center; styleleft.Alignment = HorizontalAlignment.Left; IDataFormat formatc = workbook.CreateDataFormat(); if (!string.IsNullOrEmpty(numformat)) { style.DataFormat = format.GetFormat(numformat); } IFont fontfontc = workbook.CreateFont(); fontfontc.FontName = "Calibri"; fontfontc.FontHeightInPoints = 10; styleleft.SetFont(fontfontc); //靠中 ICellStyle stylecenter = workbook.CreateCellStyle(); stylecenter.BorderBottom = BorderStyle.Thin; stylecenter.BorderLeft = BorderStyle.Thin; stylecenter.BorderRight = BorderStyle.Thin; stylecenter.BorderTop = BorderStyle.Thin; stylecenter.VerticalAlignment = VerticalAlignment.Center; stylecenter.Alignment = HorizontalAlignment.Center; IDataFormat formatcen = workbook.CreateDataFormat(); IFont formatcen1 = workbook.CreateFont(); if (!string.IsNullOrEmpty(numformat)) { stylecenter.DataFormat = format.GetFormat(numformat); } formatcen1.FontName = "Calibri"; formatcen1.FontHeightInPoints = 10; stylecenter.SetFont(fontfontc); ICellStyle stylefont = workbook.CreateCellStyle(); stylefont.BorderBottom = BorderStyle.Thin; stylefont.BorderLeft = BorderStyle.Thin; stylefont.BorderRight = BorderStyle.Thin; stylefont.BorderTop = BorderStyle.Thin; stylefont.VerticalAlignment = VerticalAlignment.Center; stylefont.Alignment = HorizontalAlignment.Left; IFont font = workbook.CreateFont(); if (!string.IsNullOrEmpty(numformat)) { stylefont.DataFormat = format.GetFormat(numformat); } font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗 font.FontHeightInPoints = 10; stylefont.SetFont(font); ICellStyle bstylefont = workbook.CreateCellStyle(); bstylefont.BorderBottom = BorderStyle.Thin; bstylefont.BorderLeft = BorderStyle.Thin; bstylefont.BorderRight = BorderStyle.Thin; bstylefont.BorderTop = BorderStyle.Thin; bstylefont.VerticalAlignment = VerticalAlignment.Center; bstylefont.Alignment = HorizontalAlignment.Left; if (!string.IsNullOrEmpty(numformat)) { bstylefont.DataFormat = format.GetFormat(numformat); } IFont bfont = workbook.CreateFont(); bfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗 bfont.FontHeightInPoints = 10; bstylefont.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; bstylefont.FillPattern = FillPattern.SolidForeground; bstylefont.SetFont(bfont); ICellStyle styleper = workbook.CreateCellStyle(); styleper.BorderBottom = BorderStyle.Thin; styleper.BorderLeft = BorderStyle.Thin; styleper.BorderRight = BorderStyle.Thin; styleper.BorderTop = BorderStyle.Thin; styleper.VerticalAlignment = VerticalAlignment.Center; styleper.Alignment = HorizontalAlignment.Right; if (!string.IsNullOrEmpty(numformat)) { styleper.DataFormat = format.GetFormat(numformat); } IDataFormat dataformat = workbook.CreateDataFormat(); styleper.DataFormat = dataformat.GetFormat(percentformat);//百分位 IFont fontfontper = workbook.CreateFont(); fontfontper.FontHeightInPoints = 10; fontfontper.FontName = "Calibri"; styleper.SetFont(fontfontper); ICellStyle stylebackcolor = workbook.CreateCellStyle(); stylebackcolor.BorderBottom = BorderStyle.Thin; stylebackcolor.BorderLeft = BorderStyle.Thin; stylebackcolor.BorderRight = BorderStyle.Thin; stylebackcolor.BorderTop = BorderStyle.Thin; stylebackcolor.VerticalAlignment = VerticalAlignment.Center; stylebackcolor.Alignment = HorizontalAlignment.Right; stylebackcolor.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; stylebackcolor.FillPattern = FillPattern.SolidForeground; if (!string.IsNullOrEmpty(numformat)) { stylebackcolor.DataFormat = format.GetFormat(numformat); } IDataFormat formatbackcolor = workbook.CreateDataFormat(); IFont fontback = workbook.CreateFont(); fontback.FontHeightInPoints = 10; fontback.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗 stylebackcolor.SetFont(fontback); ICellStyle xstylebackcolor = workbook.CreateCellStyle(); xstylebackcolor.BorderBottom = BorderStyle.Thin; xstylebackcolor.BorderLeft = BorderStyle.Thin; xstylebackcolor.BorderRight = BorderStyle.Thin; xstylebackcolor.BorderTop = BorderStyle.Thin; xstylebackcolor.VerticalAlignment = VerticalAlignment.Center; xstylebackcolor.Alignment = HorizontalAlignment.Right; xstylebackcolor.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; xstylebackcolor.FillPattern = FillPattern.SolidForeground; if (!string.IsNullOrEmpty(numformat)) { xstylebackcolor.DataFormat = format.GetFormat(numformat); } IDataFormat xformatbackcolor = workbook.CreateDataFormat(); IFont xfontback = workbook.CreateFont(); xfontback.FontHeightInPoints = 10; xfontback.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; xfontback.Color = (short)NPOI.SS.UserModel.FontColor.Red; xstylebackcolor.SetFont(xfontback); int rowIndex = startrow; foreach (DataRow row in SourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { //公式單元格 string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = style; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString()); } if (haveGridLine) { newcell.CellStyle = style; } if (column.DataType == typeof(DateTime)) { DateTime celldate; if (DateTime.TryParse(row[column].ToString(), out celldate)) { newcell.SetCellValue(celldate.ToString("yyyy/MM/dd HH:mm:ss")); } } } } rowIndex++; } if ((leftCol != null && leftCol.Count > 0) || (centerCol != null && centerCol.Count > 0)) { for (var i = startrow; i < SourceTable.Rows.Count + startrow; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); if (leftCol != null) { foreach (var item in leftCol) { ICell cell = row.GetCell(item); cell.CellStyle = styleleft; } } if (centerCol != null) { foreach (var item in centerCol) { ICell cell = row.GetCell(item); cell.CellStyle = stylecenter; } } } } //合并单元格 if (rangelist != null && rangelist.Count > 0)// { foreach (CellRangeAddress range in rangelist) { sheet.AddMergedRegion(range); } } //设置列宽 if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } } if (colbold != null && colbold.Count > 0) { foreach (var item in colbold) { XSSFRow row = (XSSFRow)sheet.GetRow(item.Key); ICell cell = row.GetCell(item.Value); cell.CellStyle = stylefont; } } if (rowcolor != null && rowcolor.Count > 0) { foreach (var item in rowcolor) { XSSFRow row = (XSSFRow)sheet.GetRow(item.Key); ICell cell = row.GetCell(item.Value); cell.CellStyle = bstylefont; } } if (colper != null && colper.Count > 0) { foreach (var item in colper) { for (int i = startrow; i < SourceTable.Rows.Count + startrow; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); ICell cell = row.GetCell(item); string cellcell = row.GetCell(item).ToString(); cell.CellStyle = styleper; } } } if (rowbold != null && rowbold.Count > 0) { foreach (var i in rowbold) { XSSFRow row = (XSSFRow)sheet.GetRow(i); for (int j = 0; j < SourceTable.Columns.Count; j++) { ICell cell = row.GetCell(j); if (j > 0) { string cellcell = row.GetCell(j).ToString(); if (cellcell.StartsWith("-")) { cell.CellStyle = xstylebackcolor; } else { cell.CellStyle = stylebackcolor; } } } } } if (specialCells != null) { foreach (Tuple<int, int, string> item in specialCells) { XSSFRow row = (XSSFRow)sheet.GetRow(item.Item1); ICell cell = row.GetCell(item.Item2); cell.SetCellValue(item.Item3); } } ExportHSSFWorkbookByWeb(null, workbook, downLoadName); } public static Stream ExportDataTableToExcelForStyleYS(string localFilePath, DataTable SourceTable, string downLoadName, int startrow, int starcolum, int localfilelength, Dictionary<int, int> leftfont, bool haveGridLine, int rowIndex, int rowheight, List<CellRangeAddress> rangelist, Dictionary<int, int> colwidth) { if (!File.Exists(localFilePath)) new AccessViolationException("模板不存在"); FileStream file = new FileStream(localFilePath, FileMode.Open, FileAccess.Read); if (null == file) { throw new Exception("模板文件打開失敗"); } NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; XSSFWorkbook workbook = new XSSFWorkbook(file); file.Close(); file = null; XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); short sRowHeight = Convert.ToInt16(rowheight * 20); ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Right; IDataFormat format = workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("#,##0");// IFont fontfont = workbook.CreateFont(); fontfont.FontName = "Calibri"; style.SetFont(fontfont);
//會計科目靠左加粗 ICellStyle style_leftfont = workbook.CreateCellStyle(); style_leftfont.BorderBottom = BorderStyle.Thin; style_leftfont.BorderLeft = BorderStyle.Thin; style_leftfont.BorderRight = BorderStyle.Thin; style_leftfont.BorderTop = BorderStyle.Thin; style_leftfont.VerticalAlignment = VerticalAlignment.Center; style_leftfont.Alignment = HorizontalAlignment.Left; IDataFormat formatc = workbook.CreateDataFormat(); style_leftfont.DataFormat = formatc.GetFormat("#,##0");// IFont font_leftfont = workbook.CreateFont(); font_leftfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗 font_leftfont.FontName = "Calibri"; style_leftfont.SetFont(font_leftfont);
foreach (DataRow row in SourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); dataRow.Height = sRowHeight; foreach (DataColumn column in SourceTable.Columns) { ICell newcell = dataRow.CreateCell(column.Ordinal); if (row[column].ToString().StartsWith("{{F}}")) { //公式單元格 string content = row[column].ToString().Replace("{{F}}", ""); newcell.CellFormula = content.Replace("{ROWINDEX}", (rowIndex + 1).ToString()); if (haveGridLine) { newcell.CellStyle = style; } } else { double cellcontent = 0; if (double.TryParse(row[column].ToString(), out cellcontent)) { newcell.SetCellValue(cellcontent); } else { newcell.SetCellValue(row[column].ToString()); } if (haveGridLine) { newcell.CellStyle = style; } } } rowIndex++; } if (rangelist != null && rangelist.Count > 0)// { foreach (CellRangeAddress range in rangelist) { sheet.AddMergedRegion(range); } } if (colwidth != null && colwidth.Count > 0) { foreach (var item in colwidth) { sheet.SetColumnWidth(item.Key, item.Value * 256); } } if (leftfont != null && leftfont.Count > 0) { for (int i = 0; i < SourceTable.Rows.Count + localfilelength; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); foreach (var item in leftfont) { ICell cell = row.GetCell(item.Key); cell.CellStyle = style_leftfont; } } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; workbook = null; return ms; }}public class NpoiMemoryStream : MemoryStream{ public NpoiMemoryStream() { AllowClose = true; } public bool AllowClose { get; set; } public override void Close() { if (AllowClose) { base.Close(); } }}
OracleDbHelper数据库操作封装
using IBM.Data.Informix;using Oracle.ManagedDataAccess.Client;using System;using System.Collections;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.Odbc;using System.Reflection;using System.Security.Cryptography;using System.Text;namespace DbHelper{ public abstract class DbHelperOra { public DbHelperOra(){} public static string connectionString = GetConStr(); #region 连接字符串 public static string GetConStr() { // <add key="ConStringEncrypt" value="true" /> // 数据库连接字符串,（如果采用加密方式，上面一项要设置为true；加密工具，可在官方下载， //如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 //<add key = "ConnectionString" value="8BD905D1B935DF2ABF5D546EA0CC7FE654D89FE4185E"/> string _connectionString = ConfigurationManager.AppSettings["ConnectionString"]; string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"]; if (ConStringEncrypt == "true" && _connectionString != null) { return Decrypt(_connectionString, "ljm"); } return null; } public static string Decrypt(string Text, string sKey) { DESCryptoServiceProvider des = new DESCryptoServiceProvider(); int len; len = Text.Length / 2; byte[] inputByteArray = new byte[len]; int x, i; for (x = 0; x < len; x++) { i = Convert.ToInt32(Text.Substring(x * 2, 2), 16); inputByteArray[x] = (byte)i; } des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8)); des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8)); System.IO.MemoryStream ms = new System.IO.MemoryStream(); CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write); cs.Write(inputByteArray, 0, inputByteArray.Length); cs.FlushFinalBlock(); return Encoding.Default.GetString(ms.ToArray()); } #endregion /// <summary> /// 功能描述：打开数据库 /// </summary> /// <returns></returns> public static OracleConnection Openconn() { OracleConnection conn = new OracleConnection(connectionString); if (conn.State.Equals(ConnectionState.Closed)) { conn.Open(); } return conn; } #region 建立SQL語句的時候判斷是否可以加入SQL語句 /// <summary> /// 验证是否有效的字段 /// </summary> public static bool IsValid(PropertyInfo pi) { string _flag = string.Empty; object[] laAttributes = pi.GetCustomAttributes(typeof(SqlBuildAttribute), false); if (laAttributes != null && laAttributes.Length > 0) { _flag = ((SqlBuildAttribute)laAttributes[0]).Flag; } //如果SQL標志不為空，且屬性的SQL特性值與SQL標志不一樣，則自動跳過 if (_flag == "NULL") { return false; } return true; } #endregion #region 公用方法 public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql) { object obj = GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(string strSql, params OracleParameter[] cmdParms) { object obj = GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(OracleTransaction trans, string strSql, params OracleParameter[] cmdParms) { object obj = GetSingle(trans, strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { using (OracleConnection connection = new OracleConnection(connectionString)) { using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (OracleException E) { connection.Close(); throw new Exception(E.Message); } } } } /// <summary> /// 執行SQL語句，返回影響的記錄數 /// </summary> /// <param name="tran">事務對象</param> /// <param name="SQLString">SQL語句</param> /// <param name="oParams">參數列表</param> /// <returns>返回影響的行數</returns> public static int ExecuteSql(OracleTransaction tran, string SQLString, params OracleParameter[] oParams) { OracleCommand cmd = new OracleCommand(); cmd.Transaction = tran; cmd.Connection = tran.Connection; cmd.CommandText = SQLString; if (oParams != null) { cmd.Parameters.AddRange(oParams); } return cmd.ExecuteNonQuery(); } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; OracleTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (OracleException E) { tx.Rollback(); throw new Exception(E.Message); } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(SQLString, connection); OracleParameter myParameter = new OracleParameter("@content", OracleDbType.NVarchar2); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (OracleException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(strSQL, connection); OracleParameter myParameter = new OracleParameter("@fs", OracleDbType.LongRaw); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (OracleException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>OracleDataReader</returns> public static OracleDataReader ExecuteReader(string strSQL) { OracleConnection connection = new OracleConnection(connectionString); OracleCommand cmd = new OracleCommand(strSQL, connection); try { connection.Open(); OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } catch (OracleException e) { throw new Exception(e.Message); } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (OracleConnection connection = new OracleConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); OracleDataAdapter command = new OracleDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (OracleException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 擴展方法(傳入數據庫連接字符串查詢) /// </summary> /// <param name="connStr"></param> /// <param name="SQLString"></param> /// <returns></returns> public static DataSet Query(string connStr, string SQLString) { using (OracleConnection connection = new OracleConnection(connStr)) { DataSet ds = new DataSet(); try { connection.Open(); OracleDataAdapter command = new OracleDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (OracleException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString, params OracleParameter[] cmdParms) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (OracleException ex) { throw new Exception(ex.Message); } return ds; } } } /// <summary> /// 擴展方法(傳入數據庫連接字符串查詢) /// </summary> /// <param name="connStr"></param> /// <param name="SQLString"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static DataSet Query(string connStr, string SQLString, params OracleParameter[] cmdParms) { using (OracleConnection connection = new OracleConnection(connStr)) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (OracleException ex) { throw new Exception(ex.Message); } return ds; } } } /// <summary> /// 擴展方法(事務查詢) /// </summary> /// <param name="connStr"></param> /// <param name="SQLString"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static DataSet Query(OracleTransaction trans, string SQLString, params OracleParameter[] cmdParms) { DataSet ds = new DataSet(); OracleCommand cmd = new OracleCommand(); cmd.Transaction = trans; cmd.Connection = trans.Connection; cmd.CommandText = SQLString; cmd.Parameters.AddRange(cmdParms); using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (OracleException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 执行带参数的SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, params OracleParameter[] cmdParms) { using (OracleConnection connection = new OracleConnection(connectionString)) { using (OracleCommand cmd = new OracleCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (OracleException E) { throw new Exception(E.Message); } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); using (OracleTransaction trans = conn.BeginTransaction()) { OracleCommand cmd = new OracleCommand(); try { foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); OracleParameter[] cmdParms = (OracleParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString) { using (OracleConnection connection = new OracleConnection(connectionString)) { using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (OracleException e) { connection.Close(); throw new Exception(e.Message); } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString, params OracleParameter[] cmdParms) { using (OracleConnection connection = new OracleConnection(connectionString)) { using (OracleCommand cmd = new OracleCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (OracleException e) { throw new Exception(e.Message); } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string connStr, string SQLString, params OracleParameter[] cmdParams) { using (OracleConnection connection = new OracleConnection(connStr)) { using (OracleCommand cmd = new OracleCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParams); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (OracleException e) { throw new Exception(e.Message); } } } } public static object GetSingle(OracleTransaction trans, string SQLString, params OracleParameter[] cmdParams) { using (OracleCommand cmd = new OracleCommand(SQLString, trans.Connection)) { cmd.Parameters.AddRange(cmdParams); object obj = cmd.ExecuteScalar(); if (obj != DBNull.Value) { return obj; } else { return null; } } } /// <summary> /// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>OracleDataReader</returns> public static OracleDataReader ExecuteReader(string SQLString, params OracleParameter[] cmdParms) { OracleConnection connection = new OracleConnection(connectionString); OracleCommand cmd = new OracleCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (OracleException e) { throw new Exception(e.Message); } } private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (OracleParameter parm in cmdParms) cmd.Parameters.Add(parm); } } #endregion #region 查詢COUNT public static int Count(string connectionString, string SQLString) { object obj = GetSingle(connectionString, SQLString); if (obj != null && obj != DBNull.Value) { return Convert.ToInt32(obj); } return 0; } public static int Count(string connectionString, string SQLString, params OracleParameter[] cmdParms) { object obj = GetSingle(connectionString, SQLString, cmdParms); if (obj != null && obj != DBNull.Value) { return Convert.ToInt32(obj); } return 0; } public static int Count(OracleTransaction trans, string SQLString, params OracleParameter[] cmdParms) { object obj = GetSingle(trans, SQLString, cmdParms); if (obj != null && obj != DBNull.Value) { return Convert.ToInt32(obj); } return 0; } #endregion #region 存储过程操作 /// <summary> /// 执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (OracleConnection connection = new OracleConnection(connectionString)) { int result; connection.Open(); OracleCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// <summary> /// 执行存储过程 返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>OracleDataReader</returns> public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { OracleConnection connection = new OracleConnection(connectionString); OracleDataReader returnReader; connection.Open(); OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); return returnReader; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (OracleConnection connection = new OracleConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); OracleDataAdapter sqlDA = new OracleDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// <summary> /// 執行存儲過程 /// </summary> /// <param name="storedProcName">存儲過程名稱</param> /// <param name="parameters">存儲過程參數</param> /// <param name="outparam">存儲過程輸出參數的字段名（單個）</param> /// <returns></returns> public static string RunProcedureT(string storedProcName, IDataParameter[] parameters, string outparam) { using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); OracleCommand command = new OracleCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in parameters) { command.Parameters.Add(parameter); } command.ExecuteNonQuery(); connection.Close(); return command.Parameters[outparam].Value.ToString(); } } /// <summary> /// 執行存儲過程 /// </summary> /// <param name="storedProcName">存儲過程名稱</param> /// <param name="parameters">存儲過程參數</param> /// <param name="outparam">存儲過程輸出參數的字段名（單個）</param> /// <returns></returns> public static string[] RunProcedureT(string storedProcName, IDataParameter[] parameters, string[] outparam) { using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); OracleCommand command = new OracleCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in parameters) { command.Parameters.Add(parameter); } command.ExecuteNonQuery(); connection.Close(); List<string> returnstringList = new List<string>(); for (int i = 0; i < outparam.Length; i++) { returnstringList.Add(command.Parameters[outparam[i]].Value.ToString()); } return returnstringList.ToArray(); } } /// <summary> /// 執行存儲過程 /// </summary> /// <param name="storedProcName">存儲過程名稱</param> /// <param name="parameters">存儲過程參數</param> /// <returns></returns> public static void RunProcedureT(OracleTransaction trans, string storedProcName, IDataParameter[] parameters) { OracleCommand command = new OracleCommand(storedProcName, trans.Connection); command.Transaction = trans; command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in parameters) { command.Parameters.Add(parameter); } command.ExecuteNonQuery(); } /// <summary> /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>OracleCommand</returns> private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters) { OracleCommand command = new OracleCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in parameters) { command.Parameters.Add(parameter); } return command; } /// <summary> /// 创建 OracleCommand 对象实例(用来返回一个整数值) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>OracleCommand 对象实例</returns> private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters) { OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new OracleParameter("ReturnValue", OracleDbType.Int32, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } #endregion #region public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string return ExecuteDataset(connection, commandType, commandText, commandParameters); } } public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution OracleCommand cmd = new OracleCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); // Return the dataset return ds; } } private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, out bool mustCloseConnection) { if (command == null) throw new ArgumentNullException("command"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); // If the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { mustCloseConnection = true; connection.Open(); } else { mustCloseConnection = false; } // Associate the connection with the command command.Connection = connection; // Set the command text (stored procedure name or SQL statement) command.CommandText = commandText; // If we were provided a transaction, assign it if (transaction != null) { if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); command.Transaction = transaction; } // Set the command type command.CommandType = commandType; // Attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } private static void AttachParameters(OracleCommand command, OracleParameter[] commandParameters) { if (command == null) throw new ArgumentNullException("command"); if (commandParameters != null) { foreach (OracleParameter p in commandParameters) { if (p != null) { // Check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } } } #endregion /// <summary> /// 构造插入SQL语句 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="model">实体</param> /// <param name="dbtype">数据库类型</param> /// <param name="table_name">表名</param> /// <returns></returns> public static BuildSqlResult buildInsertSQL<T>(T model, string table_name, BuildDbType dbtype) where T : class { if (model == null) return null; string strSQL_pre = "insert into " + table_name + "("; string strSQL_last = " values("; int _index = 0; BuildSqlResult bsr = new BuildSqlResult(); switch (dbtype) { case BuildDbType.Oracle: bsr.oraParams = new List<OracleParameter>(); break; case BuildDbType.Informix: bsr.odbcParams = new List<OdbcParameter>(); break; case BuildDbType.IfxAdo: bsr.ifxParams = new List<IfxParameter>(); break; } foreach (PropertyInfo pi in typeof(T).GetProperties()) { //集合属性不能加入 if ((pi.PropertyType.Name != typeof(List<>).Name) && (pi.Name != "ROWID") && IsValid(pi)) { object value = pi.GetValue(model, null); if (value != null) { if (_index > 0) { strSQL_pre += ","; strSQL_last += ","; } else { _index++; } strSQL_pre += pi.Name; if (dbtype.Equals(BuildDbType.Oracle)) { strSQL_last += ":" + pi.Name; bsr.oraParams.Add(new OracleParameter(":" + pi.Name, value)); } else if (dbtype.Equals(BuildDbType.Informix)) { strSQL_last += "?"; bsr.odbcParams.Add(new OdbcParameter(_index.ToString(), value)); } else if (dbtype.Equals(BuildDbType.IfxAdo)) { strSQL_last += "?"; bsr.ifxParams.Add(new IfxParameter(_index.ToString(), value)); } } } } strSQL_pre += ")"; strSQL_last += ")"; bsr.SQL = strSQL_pre + strSQL_last; return bsr; } /// <summary> /// 构造更新SQL语句 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="model">实体</param> /// <param name="dbtype">数据库类型</param> /// <param name="table_name">表名</param> /// <param name="keyFields">條件字段</param> /// <returns></returns> public static BuildSqlResult buildUpdateSQL<T>(T model, string table_name, BuildDbType dbtype, List<string> keyFields) where T : class { if (model == null) return null; BuildSqlResult bsr = new BuildSqlResult(); object objParams = null; switch (dbtype) { case BuildDbType.Informix: bsr.odbcParams = new List<OdbcParameter>(); objParams = new List<OdbcParameter>(); break; case BuildDbType.Oracle: bsr.oraParams = new List<OracleParameter>(); objParams = new List<OracleParameter>(); break; case BuildDbType.IfxAdo: bsr.ifxParams = new List<IfxParameter>(); objParams = new List<IfxParameter>(); break; } bsr.SQL = "update " + table_name + " set "; string conditionSQL = string.Empty; int _index = 0; int _keyCount = 0; foreach (PropertyInfo pi in typeof(T).GetProperties()) { //集合属性不能加入 if (pi.PropertyType.Name != typeof(List<>).Name && IsValid(pi)) { object value = pi.GetValue(model, null); if (value != null) { if (!keyFields.Contains(pi.Name.ToUpper())) { if (_index > 0) { bsr.SQL += ","; } else { bsr.SQL += " "; _index++; } switch (dbtype) { case BuildDbType.Informix: bsr.SQL += pi.Name + " = ? "; bsr.odbcParams.Add(new OdbcParameter(_index.ToString(), value)); break; case BuildDbType.Oracle: bsr.SQL += pi.Name + "=:" + pi.Name; bsr.oraParams.Add(new OracleParameter(":" + pi.Name, value)); break; case BuildDbType.IfxAdo: bsr.SQL += pi.Name + " = ? "; bsr.ifxParams.Add(new IfxParameter(_index.ToString(), value)); break; } } else { _keyCount++; if (string.IsNullOrEmpty(conditionSQL)) { conditionSQL += " WHERE "; } else { conditionSQL += " AND "; } switch (dbtype) { case BuildDbType.Informix: conditionSQL += pi.Name + " = ? "; (objParams as List<OdbcParameter>).Add(new OdbcParameter(_index.ToString(), value)); break; case BuildDbType.Oracle: conditionSQL += pi.Name + "=:" + pi.Name; (objParams as List<OracleParameter>).Add(new OracleParameter(":" + pi.Name, value)); break; case BuildDbType.IfxAdo: conditionSQL += pi.Name + " = ? "; (objParams as List<IfxParameter>).Add(new IfxParameter(_index.ToString(), value)); break; } } } } } if (_keyCount != keyFields.Count) { throw new Exception("【keyFields】条件值与参数不匹配!"); } bsr.SQL += conditionSQL; switch (dbtype) { case BuildDbType.Informix: bsr.odbcParams.AddRange(objParams as List<OdbcParameter>); break; case BuildDbType.Oracle: bsr.oraParams.AddRange(objParams as List<OracleParameter>); break; case BuildDbType.IfxAdo: bsr.ifxParams.AddRange(objParams as List<IfxParameter>); break; } return bsr; } } /// <summary> /// 数据库类型 /// </summary> public enum BuildDbType { Oracle = 1, Informix = 2, IfxAdo = 3 } /// <summary> /// 構建SQL時的返回結果 /// </summary> public class BuildSqlResult { /// <summary> /// SQL語句 /// </summary> public string SQL { set; get; } /// <summary> /// Oracle參數列表 /// </summary> public List<OracleParameter> oraParams { set; get; } /// <summary> /// ODBC參數列表 /// </summary> public List<OdbcParameter> odbcParams { set; get; } /// <summary> /// Informix Ado參數列表 /// </summary> public List<IfxParameter> ifxParams { set; get; } } [AttributeUsage(AttributeTargets.Property)] public class SqlBuildAttribute : Attribute { private string _flag; /// <summary> /// SQL语句建立标志 /// </summary> public string Flag { get { return _flag; } set { _flag = value; } } public SqlBuildAttribute() { } public SqlBuildAttribute(string flag) { this._flag = flag; } }}
Informix数据库操作类
using IBM.Data.Informix;using System;using System.Collections;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Reflection;namespace DbHelper{ public abstract class IfxDbHelper { public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.AppSettings["ConnStr"]; //Create a hashtable for the parameter cached private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string connectionString, string SQLString) { using (IfxConnection connection = new IfxConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); IfxDataAdapter command = new IfxDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (IfxException ex) { throw new Exception(ex.Message); } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } return ds; } } public static DataSet Query(string connectionString, string SQLString, params IfxParameter[] cmdParms) { using (IfxConnection connection = new IfxConnection(connectionString)) { IfxCommand cmd = new IfxCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (IfxDataAdapter da = new IfxDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (IfxException ex) { throw new Exception(ex.Message); } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } return ds; } } } public static DataSet Query(IfxTransaction tran, string SQLString, params IfxParameter[] cmdParams) { DataSet ds = new DataSet(); IfxCommand cmd = new IfxCommand(); cmd.Transaction = tran; cmd.Connection = tran.Connection; cmd.CommandText = SQLString; cmd.Parameters.AddRange(cmdParams); using (IfxDataAdapter da = new IfxDataAdapter(cmd)) { try { da.Fill(ds); } catch (Exception) { throw; } } return ds; } public static List<T> Query<T>(string connStr, string strSQL, params IfxParameter[] cmdParams) where T : new() { using (IfxConnection conn = new IfxConnection(connStr)) { conn.Open(); IfxCommand cmd = new IfxCommand(strSQL, conn); PrepareCommand(cmd, conn, null, strSQL, cmdParams); List<T> modelList = new List<T>(); Type tType = typeof(T); PropertyInfo[] piList = tType.GetProperties(); using (IfxDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { DataTable dt = odr.GetSchemaTable(); IEnumerable<string> columns = dt.AsEnumerable().Select(p => p.Field<string>("ColumnName")); while (odr.Read()) { T model = new T(); foreach (PropertyInfo pi in piList) { if (columns.Contains(pi.Name.ToLower())) { if (pi != null && odr[pi.Name] != null && odr[pi.Name] != DBNull.Value) { object objvalue = odr[pi.Name]; if (pi.PropertyType.FullName.IndexOf("System.Decimal") > -1) { if (objvalue == null || objvalue.ToString() == "") { objvalue = null; } else { objvalue = Convert.ToDecimal(objvalue); } } if (pi.PropertyType.FullName.IndexOf("System.DateTime") > 0) { if (objvalue == null || objvalue.ToString() == "") { objvalue = null; } else { objvalue = Convert.ToDateTime(objvalue); } } if (objvalue != null) { if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) { pi.SetValue(model, Convert.ChangeType(objvalue, Nullable.GetUnderlyingType(pi.PropertyType)), null); } else { pi.SetValue(model, objvalue, null); } } } } } modelList.Add(model); } } return modelList; } } public static DataTable Query1(string connStr, string strSQL, params IfxParameter[] cmdParams) { using (IfxConnection conn = new IfxConnection(connStr)) { IfxDataReader odr = null; DataTable dt = null; try { conn.Open(); IfxCommand cmd = new IfxCommand(strSQL, conn); PrepareCommand(cmd, conn, null, strSQL, cmdParams); odr = cmd.ExecuteReader(); dt = new DataTable(); for (int i = 0; i < odr.FieldCount; i++) { dt.Columns.Add(odr.GetName(i)); } while (odr.Read()) { DataRow row = dt.NewRow(); for (int i = 0; i < odr.FieldCount; i++) { row[i] = odr.GetValue(i); } dt.Rows.Add(row); } } catch (Exception) { } finally { if (!odr.IsClosed) { odr.Close(); odr.Dispose(); } if (conn.State != ConnectionState.Closed) { conn.Close(); conn.Dispose(); } } return dt; } } private static void PrepareCommand(IfxCommand cmd, IfxConnection conn, IfxTransaction trans, string cmdText, IfxParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; cmd.CommandTimeout = 3600;//延長數據庫的連接時間。 if (cmdParms != null) { foreach (IfxParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } public static bool Exists(string connectionString, string strOdbc) { object obj = IfxDbHelper.GetSingle(connectionString, strOdbc); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(string connectionString, string strSQL, params IfxParameter[] cmdParms) { object obj = IfxDbHelper.GetSingle(connectionString, strSQL, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(IfxTransaction trans, string strSQL, params IfxParameter[] cmdParms) { object obj = IfxDbHelper.GetSingle(trans, strSQL, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// Execute a database query which does not include a select /// </summary> /// <param name="connString">Connection string to database</param> /// <param name="cmdType">Command type either stored procedure or SQL</param> /// <param name="cmdText">Acutall SQL Command</param> /// <param name="commandParameters">Parameters to bind to the command</param> /// <returns></returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params IfxParameter[] commandParameters) { // Create a new ODBC command IfxCommand cmd = new IfxCommand(); //Create a connection using (IfxConnection connection = new IfxConnection(connectionString)) { //Prepare the command PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); //Execute the command int val = cmd.ExecuteNonQuery(); connection.Close(); cmd.Parameters.Clear(); return val; } } /// <summary> /// Execute an IfxCommand (that returns no resultset) against an existing database transaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new IfxParameter(":prodid", 24)); /// </remarks> /// <param name="trans">an existing database transaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OdbcParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(IfxTransaction trans, CommandType cmdType, string cmdText, params IfxParameter[] commandParameters) { IfxCommand cmd = new IfxCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute an IfxCommand (that returns no resultset) against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new IfxParameter(":prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OdbcParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(IfxConnection connection, CommandType cmdType, string cmdText, params IfxParameter[] commandParameters) { IfxCommand cmd = new IfxCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute an IfxCommand (that returns no resultset) against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new IfxParameter(":prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, string cmdText) { IfxCommand cmd = new IfxCommand(); IfxConnection connection = new IfxConnection(connectionString); PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute a select query that will return a result set /// </summary> /// <param name="connectionString">Connection string</param> /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param> /// <param name="cmdText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OdbcParamters used to execute the command</param> /// <returns></returns> public static IfxDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params IfxParameter[] commandParameters) { IfxCommand cmd = new IfxCommand(); IfxConnection conn = new IfxConnection(connectionString); try { //Prepare the command to execute PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); IfxDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// Execute an IfxCommand that returns the first column of the first record against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new IfxParameter(":prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OdbcParamters used to execute the command</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params IfxParameter[] commandParameters) { IfxCommand cmd = new IfxCommand(); using (IfxConnection conn = new IfxConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// Execute a IfxCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or PL/SQL command</param> /// <param name="commandParameters">An array of OdbcParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(IfxTransaction transaction, CommandType commandType, string commandText, params IfxParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution IfxCommand cmd = new IfxCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute an IfxCommand that returns the first column of the first record against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new IfxParameter(":prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OdbcParamters used to execute the command</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(IfxConnection connectionString, CommandType cmdType, string cmdText, params IfxParameter[] commandParameters) { IfxCommand cmd = new IfxCommand(); PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// Add a set of parameters to the cached /// </summary> /// <param name="cacheKey">Key value to look up the parameters</param> /// <param name="commandParameters">Actual parameters to cached</param> public static void CacheParameters(string cacheKey, params IfxParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// Fetch parameters from the cache /// </summary> /// <param name="cacheKey">Key to look up the parameters</param> /// <returns></returns> public static IfxParameter[] GetCachedParameters(string cacheKey) { IfxParameter[] cachedParms = (IfxParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; // If the parameters are in the cache IfxParameter[] clonedParms = new IfxParameter[cachedParms.Length]; // return a copy of the parameters for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (IfxParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } /// <summary> /// Internal function to prepare a command for execution by the database /// </summary> /// <param name="cmd">Existing command object</param> /// <param name="conn">Database connection object</param> /// <param name="trans">Optional transaction object</param> /// <param name="cmdType">Command type, e.g. stored procedure</param> /// <param name="cmdText">Command test</param> /// <param name="commandParameters">Parameters for the command</param> private static void PrepareCommand(IfxCommand cmd, IfxConnection conn, IfxTransaction trans, CommandType cmdType, string cmdText, IfxParameter[] commandParameters) { //Open the connection if required if (conn.State != ConnectionState.Open) conn.Open(); //Set up the command cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = cmdType; //Bind it to the transaction if it exists if (trans != null) cmd.Transaction = trans; // Bind the parameters passed in if (commandParameters != null) { foreach (IfxParameter parm in commandParameters) cmd.Parameters.Add(parm); } } /// <summary> /// Converter to use boolean data type with Odbc /// </summary> /// <param name="value">Value to convert</param> /// <returns></returns> public static string OraBit(bool value) { if (value) return "Y"; else return "N"; } /// <summary> /// Converter to use boolean data type with Odbc /// </summary> /// <param name="value">Value to convert</param> /// <returns></returns> public static bool OraBool(string value) { if (value.Equals("Y")) return true; else return false; } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList) { using (IfxConnection conn = new IfxConnection(conStr)) { conn.Open(); IfxCommand cmd = new IfxCommand(); cmd.Connection = conn; IfxTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo c in cmdList) { if (!String.IsNullOrEmpty(c.CommandText)) { PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (IfxParameter[])c.Parameters); if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine) { if (c.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("Odbc:违背要求" + c.CommandText + "必须符合select count(..的格式"); //return false; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("Odbc:违背要求" + c.CommandText + "返回值必须大于0"); //return false; } if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("Odbc:违背要求" + c.CommandText + "返回值必须等于0"); //eturn false; } continue; } int res = cmd.ExecuteNonQuery(); if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0) { tx.Rollback(); throw new Exception("Odbc:违背要求" + c.CommandText + "必须有影像行"); // return false; } } } tx.Commit(); return true; } catch (IfxException E) { tx.Rollback(); throw E; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(string connStr, List<String> SQLStringList) { using (IfxConnection conn = new IfxConnection(connStr)) { conn.Open(); IfxCommand cmd = new IfxCommand(); cmd.Connection = conn; IfxTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (string sql in SQLStringList) { if (!String.IsNullOrEmpty(sql)) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (IfxException E) { tx.Rollback(); throw new Exception(E.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } } public static int Count(string connectionString, string SQLString) { object obj = GetSingle(connectionString, SQLString); if (obj != null && obj != DBNull.Value) { return Convert.ToInt32(obj); } return 0; } public static int Count(string connectionString, string SQLString, IfxParameter[] cmdParms) { object obj = GetSingle(connectionString, SQLString, cmdParms); if (obj != null && obj != DBNull.Value) { return Convert.ToInt32(obj); } return 0; } public static int Count(IfxTransaction trans, string SQLString, IfxParameter[] cmdParms) { object obj = GetSingle(trans, SQLString, cmdParms); if (obj != null && obj != DBNull.Value) { return Convert.ToInt32(obj); } return 0; } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string connectionString, string SQLString) { using (IfxConnection connection = new IfxConnection(connectionString)) { using (IfxCommand cmd = new IfxCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (IfxException ex) { throw new Exception(ex.Message); } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } } } } public static object GetSingle(string connectionString, string SQLString, IfxParameter[] cmdParms) { using (IfxConnection connection = new IfxConnection(connectionString)) { using (IfxCommand cmd = new IfxCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (IfxException e) { throw new Exception(e.Message); } } } } public static object GetSingle(IfxTransaction trans, string SQLString, IfxParameter[] cmdParams) { using (IfxCommand cmd = new IfxCommand(SQLString, trans.Connection, trans)) { cmd.Parameters.AddRange(cmdParams); object obj = cmd.ExecuteScalar(); if (obj != DBNull.Value) { return obj; } else { return null; } } } //called a stored procedure public static IfxDataReader callStoredProcedure(string connectionString, string procedureName, params IfxParameter[] cmdParms) { // Create a connection IfxConnection conn = new IfxConnection(connectionString); conn.Open(); IfxCommand spCmd = new IfxCommand(procedureName, conn); //set command type spCmd.CommandType = CommandType.StoredProcedure; //add params //spCmd.Parameters.AddRange(cmdParms); if (cmdParms != null) { foreach (IfxParameter parm in cmdParms) spCmd.Parameters.Add(parm); } IfxDataReader testDataReader; try { testDataReader = spCmd.ExecuteReader(); testDataReader.Close(); } catch (Exception ex) { throw new Exception(ex.Message); } // Close the connection conn.Close(); return testDataReader; } } public class CommandInfo { public object ShareObject = null; public object OriginalData = null; event EventHandler _solicitationEvent; public event EventHandler SolicitationEvent { add { _solicitationEvent += value; } remove { _solicitationEvent -= value; } } public void OnSolicitationEvent() { if (_solicitationEvent != null) { _solicitationEvent(this, new EventArgs()); } } public string CommandText; public System.Data.Common.DbParameter[] Parameters; public EffentNextType EffentNextType = EffentNextType.None; public CommandInfo() { } public CommandInfo(string sqlText, SqlParameter[] para) { this.CommandText = sqlText; this.Parameters = para; } public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type) { this.CommandText = sqlText; this.Parameters = para; this.EffentNextType = type; } } public enum EffentNextType { /// <summary> /// 對其它語句無任何影響 /// </summary> None, /// <summary> /// 當前語句必須為“SELECT COUNT(*) FROM ...”格式，如果存在則繼續執行，不存在回滾事務 /// </summary> WhenHaveContine, /// <summary> /// 當前語句必須為“SELECT COUNT(*) FROM ...”格式，如果存在則繼續執行，不存在回滾事務 /// </summary> WhenNoHaveContine, /// <summary> /// 當前語句影響到的行數必須大于0，否則回滾事務 /// </summary> ExcuteEffectRows, /// <summary> /// 引發事件--當前語句必須為“SELECT COUNT(*) FROM ...”格式， 如果不存在則繼續執行，存在回滾事務 /// </summary> SolicitationEvent }}
token-C#
服務端
public string GetToken(dynamic si){ try { var str = si.userInfo.Value; string userStr = StringPlus.ToDESDecrypt(str, "mcebg.bi"); dynamic user = JsonConvert.DeserializeObject(userStr); SysWsUserBll userBll = new SysWsUserBll(); SYS_WS_USERS model = userBll.GetModel(user.userId.Value, user.password.Value); if (model == null) { throw new Exception("【" + user.userId.Value + "】不存在,請與IT確認!"); } if (model.USERPWD != user.password.Value) { throw new Exception("【" + user.password.Value + "】密碼不正確,請與IT確認!"); } string token = StringPlus.ToDESEncrypt(str, user.password.Value); msg = token; } catch (Exception ex) { msg = ex.Message; } return msg;}
public class ApiAuthorize : AuthorizeAttribute{ public override void OnAuthorization(HttpActionContext actionContext) { var attributes = actionContext.ActionDescriptor.GetCustomAttributes<AllowAnonymousAttribute>().OfType<AllowAnonymousAttribute>(); bool isAnonymous = attributes.Any(a => a is AllowAnonymousAttribute); if (isAnonymous) { base.IsAuthorized(actionContext); return; } //url获取token var content = actionContext.Request.Properties["MS_HttpContext"] as HttpContextBase; //var token = content.Request.Headers.GetValues("Authorization").FirstOrDefault(); var token = actionContext.Request.Headers.Authorization.Parameter; if (!string.IsNullOrEmpty(token)) { if (PassAuthor(token)) { base.IsAuthorized(actionContext); } else { HandleUnauthorizedRequest(actionContext); } } else { HandleUnauthorizedRequest(actionContext); } } /// <summary> /// 身份验签 /// </summary> /// <returns></returns> private bool PassAuthor(string token) { string userStr = StringPlus.ToDESDecrypt(token, "mcebg.bi"); dynamic user = JsonConvert.DeserializeObject(userStr); SysWsUserBll userBll = new SysWsUserBll(); SYS_WS_USERS model = userBll.GetModel(user.userId.Value, user.password.Value); if (model == null) { return false; } else { return true; } } /// <summary> /// 验证不通过 返回401 /// </summary> /// <param name="actionContext"></param> protected override void HandleUnauthorizedRequest(HttpActionContext actionContext) { var response = actionContext.Response = actionContext.Response ?? new HttpResponseMessage(); response.StatusCode = HttpStatusCode.Unauthorized; response.Headers.Add("WWW-Authenticate", "Basic"); response.Content = new StringContent("身份验签不通过", Encoding.UTF8, "application/json"); //throw new HttpResponseException(response); }}
public static string ToDESDecrypt(string decryptString, string sKey){ //加密碼Key必須8位 不夠補齊，長了截取 if (sKey.Length < 8) { int length = sKey.Length; for (int i = 0; i < 8 - length; i++) { sKey += i; } } else { sKey = sKey.Substring(0, 8); } byte[] keyBytes = Encoding.UTF8.GetBytes(sKey); byte[] keyIV = keyBytes; byte[] inputByteArry = new byte[decryptString.Length / 2]; int num2 = 0; try { for (int i = 0; i < (decryptString.Length / 2); i++) { num2 = Convert.ToInt32(decryptString.Substring(i * 2, 2), 0x10); inputByteArry[i] = (byte)num2; } } catch { Console.Write("i = " + num2); } DESCryptoServiceProvider desProvider = new DESCryptoServiceProvider(); // java 使用CCB模式，PKCS5Padding； // C#默認的是CBC模式，PKCS7Padding, PKCS5Padding, PKCS7padding格式 desProvider.Mode = CipherMode.CBC; desProvider.Padding = PaddingMode.PKCS7; MemoryStream memStream = new MemoryStream(); CryptoStream crypStream = new CryptoStream(memStream, desProvider.CreateDecryptor(keyBytes, keyIV), CryptoStreamMode.Write); crypStream.Write(inputByteArry, 0, inputByteArry.Length); crypStream.FlushFinalBlock(); return Encoding.UTF8.GetString(memStream.ToArray());}
public static string ToDESEncrypt(string encryptString, string sKey){ try { //加密碼Key必須8位 不夠補齊，長了截取 if (sKey.Length < 8) { int length = sKey.Length; for (int i = 0; i < 8 - length; i++) { sKey += i; } } else { sKey = sKey.Substring(0, 8); } byte[] keyBytes = Encoding.UTF8.GetBytes(sKey); byte[] keyIV = keyBytes; byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString); DESCryptoServiceProvider desProvider = new DESCryptoServiceProvider(); // Java默認的是ECB模式，PKCS5padding； C#默認的是CBC模式，PKCS7padding desProvider.Mode = CipherMode.CBC; desProvider.Padding = PaddingMode.PKCS7; MemoryStream memStream = new MemoryStream(); CryptoStream crypStream = new CryptoStream(memStream, desProvider.CreateEncryptor(keyBytes, keyIV), CryptoStreamMode.Write); crypStream.Write(inputByteArray, 0, inputByteArray.Length); crypStream.FlushFinalBlock(); StringBuilder builder = new StringBuilder(); foreach (byte num in memStream.ToArray()) { builder.AppendFormat("{0:X2}", num); } memStream.Close(); return builder.ToString(); } catch (Exception ee) { return encryptString; }}
客戶端
private void button1_Click(object sender, EventArgs e){ Hashtable headDt = new Hashtable(); headDt["USERID"] = "F1639711";//利潤中心代碼 headDt["USERPWD"] = "1111";//年月版本 string userInfo_json = JsonConvert.SerializeObject(headDt); string token = GetToken("F1680474", "123456", "mcebg.bi"); HttpPostClient("http://localhost:63114/api/SysWsUser/QueryData", token, userInfo_json);}public static string HttpPostClient(string url, string token, string jsonStr){ try { byte[] buf = Encoding.GetEncoding("UTF-8").GetBytes(jsonStr); HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(url); myRequest.Method = "POST"; myRequest.ContentType = "application/json"; myRequest.Accept = "text/html, application/xhtml+xml, */*"; myRequest.Headers["Authorization"] = "Bearer " + token; //API請求需帶Token myRequest.Timeout = 300000; //寫入請求內容 Stream newStream = myRequest.GetRequestStream(); newStream.Write(buf, 0, buf.Length); newStream.Close(); HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse(); StreamReader reader = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8); string rtnData = reader.ReadToEnd(); //返回结果 reader.Close(); myResponse.Close(); return rtnData; } catch (Exception ex) { throw new Exception(ex.Message); }}/// <summary>/// 獲取接口令牌/// </summary>/// <param name="passKEY">加密KEY值</param>/// <returns></returns>public static string GetToken(string userID, string userPwd, string passKEY){ try { //請求Token的用戶信息 Hashtable userHash = new Hashtable(); userHash["userId"] = userID;//API帳號 userHash["password"] = userPwd;//API密碼 userHash["applyTime"] = DateTime.UtcNow;//請求時間 10分鐘有效期 ERP API處理請求時會檢查時間 string userInfo_json = JsonConvert.SerializeObject(userHash); //請求Token的用戶信息加密處理 用戶請求Token信息加密的sKey申請API帳號時會告知 string strUserInfo = StringHelper.ToDESEncrypt(userInfo_json, passKEY); //加密后再包裝成json格式{"userInfo":"xxxxxxx"} Hashtable ht_request = new Hashtable(); ht_request["userInfo"] = strUserInfo; string req_json = JsonConvert.SerializeObject(ht_request); byte[] buf = Encoding.GetEncoding("UTF-8").GetBytes(req_json); //獲取Token POST請求 string url = "http://localhost:63114/api/SysWsUser/GetToken"; HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(url); myRequest.Method = "POST"; myRequest.ContentLength = buf.Length; myRequest.ContentType = "application/json"; myRequest.Accept = "text/html, application/xhtml+xml, */*"; //寫入請求內容 Stream newStream = myRequest.GetRequestStream(); newStream.Write(buf, 0, buf.Length); newStream.Close(); //取得請求響應內容 HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse(); StreamReader reader = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8); string ReturnData = reader.ReadToEnd(); reader.Close(); myResponse.Close(); //JSON格式字符串轉換成正常字符串，得到加密后的Token string result = JsonConvert.DeserializeObject<string>(ReturnData); //對有加密處理的Token做解密處理，解密的sKey為API密碼 return StringHelper.ToDESDecrypt(result, userHash["password"].ToString()); } catch (Exception ex) { return ex.Message; }}
webservice接口附件传输
private void button3_Click(object sender, EventArgs e){ WebAPM.WebAPM ws = new WebAPM.WebAPM(); WebAPM.TPSoap tpsoap = new WebAPM.TPSoap(); tpsoap.UserID = "****"; tpsoap.UserPWD = "***"; ws.TPSoapValue = tpsoap; WebAPM.ServiceIn si = new WebAPM.ServiceIn(); string xmlCode = @"<dbPlant>11111</dbPlant> <PMI_FILE> <PMI03>1111</PMI03> <PMI04>備注說明</PMI04> </PMI_FILE> <PMJ_FILE> <PMJ03>123456</PMJ03> <PMJ05>RMB</PMJ05> <PMJ07>1.234</PMJ07> <PMJ09>2019/04/01 </PMJ09> <PMJ11>100 </PMJ11> <PMJ13>2019/12/31 </PMJ13> </PMJ_FILE> "; string attachXml = @"<Attach> <fileName>{0}</fileName> <fileByte><![CDATA[{1}]]></fileByte> </Attach>"; StringBuilder attachXmls = new StringBuilder(); //第一個附件 FileStream fs = new FileStream("D:\\temp\\2dfa8ceb66a840b996ecca4175279227.pdf", FileMode.Open, FileAccess.Read); byte[] fileByte1 = StreamToBytes(fs); fs.Close(); fs = null; string fileStr = Convert.ToBase64String(fileByte1); attachXmls.AppendFormat(attachXml, "2dfa8ceb66a840b996ecca4175279227.pdf", fileStr); //第二個附件 //fs = new FileStream("D:\\temp\\2dfa8ceb66a840b996ecca4175279227.pdf", FileMode.Open, FileAccess.Read); //fileByte1 = StreamToBytes(fs); //fs.Close(); //fs = null; //fileStr = Convert.ToBase64String(fileByte1); //attachXmls.AppendFormat(attachXml, "2dfa8ceb66a840b996ecca4175279227.pdf", fileStr); //合并XML文件 xmlCode += attachXmls.ToString(); si.Content = StringHelper.EncodeBase64("UTF-8", "<?xml version=\"1.0\"?><root>" + xmlCode + "</root>"); WebAPM.ServiceOut so = ws.AddCP(si); if (so.Status == "Y") { string rtnXML = StringHelper.DecodeBase64("UTF-8", so.Message); }}
webapi-C#
新建WEBAPI項目
- 文件 - >新建 - >项目
- Visual C＃ - >Web - >ASP.NET Web应用程序（.NET Framework）
- 在下一个对话框中，如果选择Web API，这样它就会为项目准备所有基本的初始文件；如果选择Empty，记得勾选下面的MVC和Web API。
- 右键单击“Controllers”，选择Add，然后选择Controller。在给定的对话框中，如果选择具有读/写操作的Web API 2 Controller，它将为控制器准备所有适当的CRUD操作；如果选择Web API 2 Controller-空白，它将建立一个空白的api控制器。
路由配置
config.Routes.MapHttpRoute( name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new { id = RouteParameter.Optional }, constraints: new { id = @"\d*" });config.Routes.MapHttpRoute( name: "ActionApi", routeTemplate: "api/{controller}/{action}/{id}", defaults: new { id = RouteParameter.Optional });
WebApi参数传递
如何通过get、post、put、delete四种请求方式分别传递基础类型（包括int/string/datetime等）、实体、数组等类型的参数?
private void button1_Click(object sender, EventArgs e){ Hashtable headDt = new Hashtable(); headDt["USERID"] = "111";//利潤中心代碼 headDt["USERPWD"] = "1111";//年月版本 var jsonSet = new JsonSerializerSettings(); string userInfo_json = JsonConvert.SerializeObject(headDt, jsonSet); HttpPostClient("http://localhost:63114/api/SysWsUser/QueryData", "1233", userInfo_json); } public static string HttpPostClient(string url, string token, string jsonStr) { byte[] buf = Encoding.GetEncoding("UTF-8").GetBytes(jsonStr); HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(url); myRequest.Method = "POST"; myRequest.ContentType = "application/json"; myRequest.Accept = "text/html, application/xhtml+xml, */*"; myRequest.Headers["Authorization"] = "Bearer " + token; //API請求需帶Token myRequest.Timeout = 300000; //寫入請求內容 Stream newStream = myRequest.GetRequestStream(); newStream.Write(buf, 0, buf.Length); newStream.Close(); HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse(); StreamReader reader = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8); string rtnData = reader.ReadToEnd(); //返回结果 reader.Close(); myResponse.Close(); return rtnData; }
WebApi参数接收
[HttpPost]public string QueryData(dynamic si)//[FromBody]SYS_WS_USERS ws{ HttpContextBase context = (HttpContextBase)Request.Properties["MS_HttpContext"];//获取传统的context HttpRequestBase request = context.Request; var b = context.Request.Headers.GetValues("Authorization").FirstOrDefault(); byte[] bdate = new byte[context.Request.InputStream.Length]; context.Request.InputStream.Read(bdate, 0, bdate.Length); var req = System.Text.Encoding.Default.GetString(bdate); req = HttpContext.Current.Server.UrlDecode(req); var sjsonstr = JsonConvert.DeserializeObject<dynamic>(req); var a = sjsonstr.USERID; return msg;}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
