使用NPOI 操作Excel 個人使用的電腦基本預設安裝Excel 操作起來 調用Excel的組件便可.如果是一臺伺服器.沒有安裝Excel,也就無法調用Excel組件. 在此推薦第三方插件.NPOI 支持XLS(2007)和XLSX(2012)讀寫. 讀: 寫: 消息在Rabbit中.A接觸Rab ...
使用NPOI 操作Excel
個人使用的電腦基本預設安裝Excel 操作起來
調用Excel的組件便可.如果是一臺伺服器.沒有安裝Excel,也就無法調用Excel組件.
在此推薦第三方插件.NPOI 支持XLS(2007)和XLSX(2012)讀寫.
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.IO; namespace WebApplication1.Helper { public class ExcelHelper : IDisposable { private string fileName = null; //文件名 private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public ExcelHelper(string fileName) { this.fileName = fileName; disposed = false; fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); } public List<string> SheetName { get { List<string> data = null; if (workbook != null) { data = new List<string>(); for (int i = 0; i < workbook.NumberOfSheets; i++) { data.Add(workbook.GetSheetAt(i).SheetName.ToString()); } } return data; } } public int SheetCount { get { return workbook == null ? 0 : workbook.NumberOfSheets; } } /// <summary> /// 將excel中的數據導入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名稱</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { //fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); //if (fileName.IndexOf(".xlsx") > 0) // 2007版本 // workbook = new XSSFWorkbook(fs); //else if (fileName.IndexOf(".xls") > 0) // 2003版本 // workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果沒有找到指定的sheetName對應的sheet,則嘗試獲取第一個sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最後一個cell的編號 即總的列數 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最後一列的標號 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //沒有數據的行預設是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,沒有數據的單元格都預設是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } public static void CreateExcel(string projectName) { try { string fileName = $"{projectName}.xlsx"; // 文件名稱 string filePath = $"{ConfigurationManager.AppSettings["file"].ToString()}" + "\\" + fileName; // 2.解析單元格頭部,設置單元頭的中文名稱 XSSFWorkbook workbook = new XSSFWorkbook(); // 工作簿 ISheet sheet = workbook.CreateSheet("sheet"); //#region 設置Excel表格第一行的樣式 //IRow titleInfo = sheet.CreateRow(0); //ICell cellTitle = titleInfo.CreateCell(0); //cellTitle.SetCellValue("會員信息批量錄入模板"); //ICellStyle titleStyle = workbook.CreateCellStyle(); //IFont titleFont = workbook.CreateFont(); //titleFont.FontHeightInPoints = 25; //titleFont.Boldweight = short.MaxValue;//字體加粗 //titleStyle.SetFont(titleFont); //cellTitle.CellStyle = titleStyle; //#endregion //IRow dataFields = sheet.CreateRow(2); //ICellStyle style = workbook.CreateCellStyle();//創建樣式對象 //style.Alignment = HorizontalAlignment.CENTER;//水平對齊 //style.VerticalAlignment = VerticalAlignment.CENTER;//垂直對齊 //IFont font = workbook.CreateFont(); //創建一個字體樣式對象 //font.FontName = "宋體"; //和excel裡面的字體對應 //font.Color = new HSSFColor.RED().GetIndex();//顏色參考NPOI的顏色對照表(替換掉PINK()) //font.FontHeightInPoints = 10;//字體大小 //font.Boldweight = short.MaxValue;//字體加粗 //style.SetFont(font); //將字體樣式賦給樣式對象 //sheet.SetColumnWidth(0, 20 * 256);//設置列寬 //string[] colums = { "*會員卡號", "*會員手機", "*會員姓名", "*會員等級", "會員性別", "電子郵箱", "會員狀態", "固定電話", "永久有效", "身份證號", "開卡費用", "會員地址", "備註信息" }; //ICell Cell = null; //for (int i = 0; i < colums.Count(); i++) //{ // Cell = dataFields.CreateCell(i); // Cell.CellStyle = style; // Cell.SetCellValue(colums[i]); // sheet.SetColumnWidth(i, 17 * 256); //} //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colums.Count()));//合併單元格 // 4.生成文件 FileStream file = new FileStream(filePath, FileMode.Create); workbook.Write(file); file.Close(); } catch (Exception ex) { throw ex; } } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { if (fs != null) fs.Close(); } fs = null; disposed = true; } } } }
讀:
using (ExcelHelper excelHelper = new ExcelHelper(ConfigurationManager.AppSettings["ExcelFile"])) { var dataTable = excelHelper.ExcelToDataTable(null, true); }
ConfigurationManager.AppSettings["ExcelFile"]) 指文件位置 ExcelToDataTable(null, true); sheetName指Excel中每個Sheet的名字 isFirstRowColumn指第一行是否是列名
寫:
ExcelHelper.CreateExcel(projectName); FileStream fs = new FileStream($"{ConfigurationManager.AppSettings["file"].ToString()}\\{projectName}.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(fs); for (int i = 0; i < Sheet.Count; i++) { var Srarch = data.Where(a => a.Sheet == Sheet[i]).ToList(); DataTable dataTable = new DataTable(); dataTable.Columns.Add("Template"); dataTable.Columns.Add("Code"); dataTable.Columns.Add("ZH_CN"); dataTable.Columns.Add("Description"); dataTable.Columns.Add("EN_US"); dataTable.Columns.Add("ZH_TW"); dataTable.Columns.Add("KO"); dataTable.Columns.Add("ZH_HK"); for (int a = 0; a < Srarch.Count; a++) { DataRow row = dataTable.NewRow(); row[0] = Srarch[a].Template; row[1] = Srarch[a].Code; row[2] = Srarch[a].ZH_CN; row[3] = Srarch[a].Description; row[4] = Srarch[a].EN_US; row[5] = Srarch[a].ZH_TW; row[6] = Srarch[a].KO; row[7] = Srarch[a].ZH_HK; dataTable.Rows.Add(row); } dataTable.TableName = Sheet[i]; ISheet sheet = workbook.CreateSheet(dataTable.TableName); //表頭 IRow row2 = sheet.CreateRow(0); for (int a = 0; a < dataTable.Columns.Count; a++) { ICell cell = row2.CreateCell(a); cell.SetCellValue(dataTable.Columns[a].ColumnName); } //數據 for (int a = 0; a < dataTable.Rows.Count; a++) { IRow row1 = sheet.CreateRow(a + 1); for (int j = 0; j < dataTable.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(dataTable.Rows[a][j].ToString()); } } } MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存為Excel文件 using (FileStream fs1 = new FileStream($"{ConfigurationManager.AppSettings["file"].ToString()}\\{projectName}.xlsx", FileMode.Open, FileAccess.Write)) { fs1.Write(buf, 0, buf.Length); fs1.Flush(); }
ExcelHelper.CreateExcel(projectName);
創建Excel ,projectName 是Excel名字,路徑預設是解決方案下File文件夾
兩個For迴圈,第一個迴圈是創建列名,第二個迴圈是創建列下欄位的內容
當然這隻是創建Datatable
所以後面還有兩個迴圈 將每個TABLE中列名放到Excel中的Sheet.以及內容
最後fs1.Write寫入Excel
RabbitMQ
是一種應用程式對應用程式的通信方法
舉個例子
前端站點向服務站點發起請求。一般服務站點要考慮到併發和數據總量保持運行穩定.
如果前端的請求發送到Rabbit並做持久化.服務端隔段時間批量響應請求。就大大減少了服務站點的壓力
接下來的慄子就是A站點向Rabbit發消息並走入隊列.然後B站點拉取消息並回應,至於隔多久取,一次取多少條.這部分就沒做限制(計數就好).
下麵是A站點
ConnectionFactory factory = new ConnectionFactory(); factory.HostName = "127.0.0.1"; //預設埠 factory.Port = 5672; factory.UserName = "guest";//用戶名 factory.Password = "guest";//密碼 using (IConnection conn = factory.CreateConnection()) { using (IModel channel = conn.CreateModel()) { //在MQ上定義一個持久化隊列,如果名稱相同不會重覆創建 channel.QueueDeclare("MQTest", true, false, false, null); while (true) { string message = string.Format("Message_{0}", Console.ReadLine()); byte[] buffer = Encoding.UTF8.GetBytes(message); IBasicProperties properties = channel.CreateBasicProperties(); properties.DeliveryMode = 2; channel.BasicPublish("", "MQTest", properties, buffer); Console.WriteLine("消息發送成功:" + message); } } }
消息在Rabbit中.A接觸Rabbit,B接觸Rabbit.
最終是AB交互.但是過程相互不接觸
ConnectionFactory factory = new ConnectionFactory(); factory.HostName = "127.0.0.1"; //預設埠 factory.Port = 5672; factory.UserName = "guest";//用戶名 factory.Password = "guest";//密碼 using (IConnection conn = factory.CreateConnection()) { using (IModel channel = conn.CreateModel()) { //在MQ上定義一個持久化隊列,如果名稱相同不會重覆創建 channel.QueueDeclare("MQTest", true, false, false, null); //輸入1,那如果接收一個消息,但是沒有應答,則客戶端不會收到下一個消息 channel.BasicQos(0, 1, false); Console.WriteLine("Listening..."); //在隊列上定義一個消費者 QueueingBasicConsumer consumer = new QueueingBasicConsumer(channel); //消費隊列,並設置應答模式為程式主動應答 channel.BasicConsume("MQTest", false, consumer); while (true) { //阻塞函數,獲取隊列中的消息 BasicDeliverEventArgs ea = (BasicDeliverEventArgs)consumer.Queue.Dequeue(); byte[] bytes = ea.Body; string str = Encoding.UTF8.GetString(bytes); Console.WriteLine("隊列消息:" + str.ToString()); //回覆確認 channel.BasicAck(ea.DeliveryTag, false); } } }
EF
像這個被大家用的滾瓜爛熟的框架.我這會拿出來說似乎有點小兒科.不過我就是要寫(還寫得賊簡單)
Nuget中引入EF
自定義DbContext繼承DbContext
public class HotelDbContext : DbContext { public HotelDbContext() : base("name=ConnCodeFirst") { Database.SetInitializer(new MigrateDatabaseToLatestVersion<HotelDbContext, Configuration>("ConnCodeFirst")); } public DbSet<TSM_Admin1111111111111> TSM_Admin { get; set; } }
ConnCodeFirst 是資料庫連接字元串
Configuration 如下
internal sealed class Configuration : DbMigrationsConfiguration<HotelDbContext> { public Configuration() { // 自動遷移 TRUE code frist AutomaticMigrationsEnabled = true; } protected override void Seed(HotelDbContext context) { context.TSM_Admin.AddOrUpdate( p => p.UserID, new TSM_Admin1111111111111 { UserID = "1", UserName = "111" }, new TSM_Admin1111111111111 { UserID = "2", UserName = "222" }, new TSM_Admin1111111111111 { UserID = "3", UserName = "333" } ); } }
AutomaticMigrationsEnabled 意思就是找不到資料庫的時候自動創建資料庫。順帶還會創建初始數據(表和數據)。在Seed中
說真的很雞肋.用的很想罵街
接下來是EF的各種使用方式。。嬰兒教學版
new Configuration(); using (var data = new HotelDbContext()) { //查詢.全表 var a = data.TSM_Admin.ToList(); //插入 data.TSM_Admin.Add(new TSM_Admin1111111111111 { UserID = "高1030測試" }); data.SaveChanges(); //修改 var model = data.TSM_Admin.Where(b => b.UserID == "高1030測試").First(); if (model != null) { model.UserName = "高1030測試名字"; data.Entry<TSM_Admin1111111111111>(model).State = System.Data.Entity.EntityState.Modified; data.SaveChanges(); } //刪除 var delete = data.TSM_Admin.Where(b => b.UserID == "高1030測試").First(); if (delete != null) { var result = data.TSM_Admin.Where(b => b.UserID == delete.UserID).FirstOrDefault(); data.TSM_Admin.Remove(result); data.SaveChanges(); } var select = data.Database.SqlQuery<TSM_Admin1111111111111>("select * from tsm_123 ").ToList(); var insert = data.Database.ExecuteSqlCommand("insert into TSM_Admin(UserID) values('高1030測試')"); }
能用Dapper。就別用EF..
Attribute。
很多人都夢想著自定義屬於自己的屬性.那麼
我用雙手成就你的夢想
StringLength是自定義屬性的名字Attribute是必須加上去的。並且在使用中會被忽視
[AttributeUsage(AttributeTargets.Property)] public class StringLengthAttribute : Attribute { private int _maximumLength; public StringLengthAttribute(int maximumLength) { _maximumLength = maximumLength; } public int MaximumLength { get { return _maximumLength; } } }
接下來定義使用的實體
public class People { [StringLength(8)] public string Name { get; set; } [StringLength(15)] public string Description { get; set; } }
接下來定義獲取屬性內容的方法
public class ValidationModel { public void Validate(object obj) { var t = obj.GetType(); //由於我們只在Property設置了Attibute,所以先獲取Property var properties = t.GetProperties(); foreach (var property in properties) { //這裡只做一個stringlength的驗證,這裡如果要做很多驗證,需要好好設計一下,千萬不要用if elseif去鏈接 //會非常難於維護,類似這樣的開源項目很多,有興趣可以去看源碼。 if (!property.IsDefined(typeof(StringLengthAttribute), false)) continue; var attributes = property.GetCustomAttributes(false); foreach (var attribute in attributes) { //這裡的MaximumLength 最好用常量去做 var maxinumLength = (int)attribute.GetType(). GetProperty("MaximumLength"). GetValue(attribute); //獲取屬性的值 var propertyValue = property.GetValue(obj) as string; if (propertyValue == null) throw new Exception("exception info");//這裡可以自定義,也可以用具體系統異常類 if (propertyValue.Length > maxinumLength) throw new Exception(string.Format("屬性{0}的值{1}的長度超過了{2}", property.Name, propertyValue, maxinumLength)); } } } }
接下來是調用過程
var people = new People() { Name = "qweasdzxcasdqweasdzxc", Description = "description" }; try { new ValidationModel().Validate(people); } catch (Exception ex) { Console.WriteLine(ex.Message); }
這個段子充分解釋瞭如何使用自定義Attribute
核心思想是萬物皆可supreme
HttpRuntime.Cache
開發中很多不常改動的數據。可以考慮將數據從資料庫里取出來後的指定時間記憶體在本地記憶體中
using System; using System.Web; using System.Web.Caching; namespace Chinamoney.Core { public class CacheHelper { /// <summary> /// 獲取數據緩存 /// </summary> /// <param name="cacheKey">鍵</param> public static object GetCache(string cacheKey) { var objCache = HttpRuntime.Cache.Get(cacheKey); return objCache; } /// <summary> /// 設置數據緩存 /// </summary> public static void SetCache(string cacheKey, object objObject) { var objCache = HttpRuntime.Cache; objCache.Insert(cacheKey, objObject); } /// <summary> /// 設置數據緩存 /// </summary> public static void SetCache(string cacheKey, object objObject, int Hours) { try { if (objObject == null) return; var objCache = HttpRuntime.Cache; //相對過期 //objCache.Insert(cacheKey, objObject, null, DateTime.MaxValue, timeout, CacheItemPriority.NotRemovable, null); //絕對過期時間 objCache.Insert(cacheKey, objObject, null, DateTime.Now.AddHours(Hours), TimeSpan.Zero, CacheItemPriority.High, null); } catch (Exception e) { Log.Logger.Log("緩存寫入異常:",e); } } /// <summary> /// 移除指定數據緩存 /// </summary> public static void RemoveAllCache(string cacheKey) { var cache = HttpRuntime.Cache; cache.Remove(cacheKey); } /// <summary> /// 移除全部緩存 /// </summary> public static void RemoveAllCache() { var cache = HttpRuntime.Cache; var cacheEnum = cache.GetEnumerator(); while (cacheEnum.MoveNext()) { cache.Remove(cacheEnum.Key.ToString()); } } } }
讀取
CacheHelper.GetCache("cacheExtraterritorial") as List<ExtraterritorialDataEntity>;
寫入
CacheHelper.SetCache("cacheExtraterritorial", enumerable, result.Count);
清空全部緩存(帶名稱清理指定緩存,不帶名稱清理全部緩存)
CacheHelper.RemoveAllCache("cacheExtraterritorial");
AD是微軟推出的又一款對象資料庫,
集成了許可權和員工信息。
在此貼出AD操作的Helper
using Chinamoney.Core.Log; using System; using System.Collections; using System.DirectoryServices; namespace Chinamoney.Core { /// <summary> /// AD域 /// </summary> public class ADHelper { /// <summary> /// 獲取單個用戶 /// </summary> ///