C# Excel Sheet 转为[列号、<字段名、显示名>]的字典
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
//将表字段与名称做映射，改善模板与程式维护：不必每次增加栏位就要重新编译发布一次 Ethan20020424 Dictionary<int, KeyValuePair<string, string>> bmbFIeldMap = GetFiledColumnMap(table); table.Rows[0].Delete();//删除字段行 Ethan20020424 table.Rows[0].Delete();//删除标题行 Ethan20020424 table.AcceptChanges();//接收删除的变更操作 DataView dv = new DataView(table); dv.RowFilter = "F1=F2";//过滤相同值 DataTable dt0 = dv.ToTable(); if (dt0.Rows.Count > 0) { strerror = "系统拒绝导入！"; goto ReturnError; } #region 0列开始模板新增列自动处理：自动匹配字段与值。目前现有列Remark 12（13列） Ethan20020424 string filedName = "",filedValue=""; var newColumns = dt.Rows[i].ItemArray.Skip(13);//取remark列后面新增的列 for (int j = 0; j < newColumns.Count(); j++) { filedName = bmbFIeldMap[13+j].Key.ToLower(); filedValue = dt.Rows[i][13 + j]?.ToString(); ipartbom.setProperty(filedName, filedValue); } #endregion /// <summary> /// 将DataTable的前两列转为[列、<字段名、显示名>]的字典 /// </summary> /// <param name="dataTable">要转换的Datatable</param> /// <returns>转换后的[列、<字段名、显示名>]</returns> Dictionary<int, KeyValuePair<string,string>> GetFiledColumnMap(DataTable dataTable) { Dictionary<int, KeyValuePair<string, string>> FieldColumnMap = new Dictionary<int, KeyValuePair<string, string>>(); for (int i = 0; i < dataTable.Columns.Count; i++) { FieldColumnMap.Add(i, new KeyValuePair<string, string>(dataTable.Rows[0][i].ToString(), dataTable.Rows[1][i].ToString())); } return FieldColumnMap; } |
C# 获取Excel Sheet
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
{ /... string strConn; DataSet myDataSet = new DataSet(); //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0"; strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;'"; OleDbConnection conn = new OleDbConnection(strConn); try { conn.Open(); List<SheetName> shees = GetSheetNames(conn); if (shees.Count < 1) { conn.Dispose(); ViewBag.error = "Excel中Sheet为0,无导入资料"; return ViewBag.error; } string tableName = shees[0].sheetName; //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn); OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + tableName+"]", strConn); myCommand.Fill(myDataSet, "ExcelInfo"); conn.Dispose(); } catch (Exception ex) { conn.Dispose(); outlogInnovator(); ViewBag.error = ex.Message; //return View(); return ViewBag.error; } DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); } // List<SheetName> GetSheetNames(OleDbConnection conn) { List<SheetName> sheetNames = new List<SheetName>(); if (conn.State != ConnectionState.Open) { conn.Open(); } DataTable excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow row in excelSchema.Rows) { if ((!row["TABLE_NAME"].ToString().Contains("FilterDatabase"))) { sheetNames.Add(new SheetName() { sheetName = row["TABLE_NAME"].ToString(), sheetType = row["TABLE_TYPE"].ToString(), sheetCatalog = row["TABLE_CATALOG"].ToString(), sheetSchema = row["TABLE_SCHEMA"].ToString() }); } } return sheetNames; } class SheetName { public string sheetName { get; set; } public string sheetType { get; set; } public string sheetCatalog { get; set; } public string sheetSchema { get; set; } } |
oracle 递归查询，单列查询慢
sql关联查询的时候查询所有字段速度很快，查询单个字段反而很慢很慢！ ORA-01436: 用户数据中的 CONNECT BY 循环 实操例子：单列查询慢 …
C# 正则
A，只包含字母、数字，逗点：^[0-9a-zA-Z,]{1,}$ B，只包含字母、数字，逗点，且以字母或数字结尾：^[0-9a-zA-Z,]{1,}[0-9a-…