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; } } |
Excel NPOI读写示例
Epplus：Nuget地址，项目地址 参考：Epplus上手
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
/// <summary> /// 从Excel中第一列第二行起取数据，并查询oracle数据库中数据取ima70字段值写入同行的第二列 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button46_Click(object sender, EventArgs e) { //Opening an existing Excel file FileInfo fi = new FileInfo(@"D:\Luxshare-ict2019\工作\SAP切换\料号对比\目标数据\Compare.xlsx"); using (ExcelPackage excelPackage = new ExcelPackage(fi)) { //Get a WorkSheet by index. Note that EPPlus indexes are base 1, not base 0! ExcelWorksheet firstWorksheet = excelPackage.Workbook.Worksheets[1]; ////Get a WorkSheet by name. If the worksheet doesn't exist, throw an exeption //ExcelWorksheet namedWorksheet = excelPackage.Workbook.Worksheets["SomeWorksheet"]; ////If you don't know if a worksheet exists, you could use LINQ, ////So it doesn't throw an exception, but return null in case it doesn't find it //ExcelWorksheet anotherWorksheet = // excelPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == "SomeWorksheet"); string cOString = "data source=topprod;user id=帐号;password=密码;Unicode=True"; OracleConnection con = new OracleConnection(); con.ConnectionString = cOString; con.Open(); ExcelWorksheets worksheets = excelPackage.Workbook.Worksheets;//取Excel的所有Sheet string part = string.Empty; string ima70 = string.Empty; string sqlStr = string.Empty; foreach (var worksheet in worksheets) { int rowCount = worksheet.Dimension.End.Row;//sheet行数 for (int i = 2; i <= rowCount; i++)//从第2行取到最后一行 { part= worksheet.Cells[i, 1].Value.ToString(); if (string.IsNullOrWhiteSpace(part)) { continue; } sqlStr = $"SELECT IMA70 FROM LXXT.IMA_FILE WHERE IMA01 ='{part}'"; OracleDataAdapter oda = new OracleDataAdapter(sqlStr, con); System.Data.DataSet ods = new System.Data.DataSet(); oda.Fill(ods); //ods.Tables[0]; if (ods.Tables[0].Rows.Count>0) { worksheet.Cells[i, 2].Value = ods.Tables[0].Rows[0][0].ToString();//如果从数据库中取导致，写入第二列 } System.Diagnostics.Debug.WriteLine($"{worksheet.Name}:{i.ToString()},{part}");//实时查看处理进度。 //if (i>4) //{ // break; //} } //break; } //Save your file excelPackage.Save(); con.Close(); } |
Excel 2013 VlookUp函数使用
注意点：Excel还有一个Vlook函数，不要用错。 VlookUp使用示例： =VLOOKUP(D:D,D:E,2) 参数解释： D:D，要匹配的列。也可以写…