public ValueSegment(object[] values) { _values = values; Type valueType = _values[0].GetType(); _type = ColumnTypeMapping.GetColumnType(valueType); }
protected virtual void SetToPreparedStatement(IDbCommand cmd, object obj, int parameterIndex, bool nullable, ColumnType columnType) { var parameter = cmd.CreateParameter(); parameter.Direction = ParameterDirection.Input; parameter.Value = obj ?? DBNull.Value; cmd.Parameters.Add(parameter); parameter.DbType = ColumnTypeMapping.GetSqlType(columnType); }
public void ReadSheet(Stream stream, ReadSheetDicOptions option) { Inspector.NotNull(stream, "Excel文件流不能为空"); Inspector.NotNull(option, $"{nameof(ReadSheetDicOptions)} can not be null"); Inspector.Validation(option.ExcelFields == null || option.ExcelFields.Length == 0 || option.ExcelFields.Count(t => string.IsNullOrWhiteSpace(t.field)) > 0, "Excel中的列头信息不能为空或存在为空的列名"); //匹配SheetName var sheetName = ""; { var sheetNames = this.GetSheetNames(stream, false); Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的SheetIndex {option.SheetIndex} 无效,实际只存在{sheetNames.Count()}个Sheet"); Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的SheetName {option.SheetName} 不存在"); sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName }; } //Excel中的表头列信息(index:集合中元素的位置,cellRef:单元格的A1 B1中的A B这种) var fieldLoc = new List <(int index, string excelField, ColumnType columnType, bool allowNull, string cellRef)>(); { for (int index = 0; index < option.ExcelFields.Count(); index++) { Inspector.Validation(fieldLoc.Exists(t => t.excelField == option.ExcelFields[index].field?.Trim()), "指定读取的 ExcelFields 中存在相同的列名"); fieldLoc.Add((index, option.ExcelFields[index].field, option.ExcelFields[index].type, option.ExcelFields[index].allowNull, "")); } } using (var sheetDoc = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = sheetDoc.WorkbookPart; //1.目标Sheet的Rid是否存在 string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value; Inspector.NotNullOrWhiteSpace(rId, $"不存在名为 {sheetName} 的Sheet"); SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); } string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray(); //2.反转Sheet顺序 foreach (var workSheetPart in workbookPart.WorksheetParts?.Reverse()) { //是否是指定Sheet的Rid,不是则忽略 string partRelationshipId = workbookPart.GetIdOfPart(workSheetPart); if (partRelationshipId != rId) { continue; } //读取失败的原始数据信息 (Dictionary <string, object> odata, List <(string rowIndex, string columnName, string cellValue, string errorMsg)> failInfos)failRowData = (new Dictionary <string, object>(), new List <(string rowIndex, string columnName, string cellValue, string errorMsg)>()); //创建Reader OpenXmlReader reader = OpenXmlReader.Create(workSheetPart); //工具类实例 var reflection = ReflectionHelper.NewInstance; while (reader.Read()) { if (reader.ElementType == typeof(Worksheet)) { reader.ReadFirstChild(); } if (reader.ElementType == typeof(Row)) { var row = (Row)reader.LoadCurrentElement(); //3.读取表头列,匹配字段信息 if (row.RowIndex == option.HeadRow) { foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference != null && cell.CellReference.HasValue) { //excel中的表头列字段 string excelField = cell.GetValue(shareStringItemValues); if (fieldLoc.Exists(t => t.excelField == excelField)) { var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == excelField); fieldInfo.cellRef = StringHelper.RemoveNumber(cell.CellReference); fieldLoc[fieldInfo.index] = fieldInfo; } } } //实体上定义了ExcelKit特性的字段未在Excel中匹配到 var unMatchedField = fieldLoc.Where(t => string.IsNullOrWhiteSpace(t.cellRef)); if (unMatchedField.Count() > 0) { var unmatchFields = string.Join("、", unMatchedField.Select(t => t.excelField)); var msg = $"指定的ExcelFields中的字段【{unmatchFields}】不存在于Excel中"; throw new ExcelKitException(msg); } continue; } if (row.RowIndex < option.DataStartRow) { continue; } if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow) { break; } //读取到的每行数据 var rowData = new Dictionary <string, object>(); //excel原始数据 failRowData.odata.Clear(); //失败信息 failRowData.failInfos.Clear(); //是否读取成功 var readSuc = true; //4. row.Elements<Cell>()获取出来的会自动跳过为空的单元格 foreach (Cell cell in row.Elements <Cell>()) { //4.1 跳过cell引用为空的 if (cell.CellReference == null || !cell.CellReference.HasValue) { continue; } //4.2 当前循环的cell列位置(不含数字) var loopCellRef = StringHelper.RemoveNumber(cell.CellReference); //不存在或匹配列信息不一致的跳过 var fieldInfo = fieldLoc.FirstOrDefault(t => t.cellRef.Equals(loopCellRef, StringComparison.OrdinalIgnoreCase)); if (fieldInfo == (0, null, 0, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase)) { continue; } //Excel中读取到的值 string readVal = null; try { readVal = cell.GetValue(shareStringItemValues); Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(readVal), $"Excel中列 {fieldInfo.excelField} 为必填项"); object value = ColumnTypeMapping.Convert(readVal, fieldInfo.columnType, fieldInfo.allowNull); rowData.Add(fieldInfo.excelField, value); } catch (Exception ex) { readSuc = false; failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, readVal?.ToString(), ex.Message)); } } //5.单元格为空缺失的key补齐(这样做key的顺序和原始的不一致了,有需求时可以使用header上面的cellRef排序解决,为了读取速度此处暂不做) var lackKeys = fieldLoc.Select(t => t.excelField).Except(rowData.Keys); foreach (var lackKey in lackKeys) { rowData.TryAdd(lackKey, null); } //读取成功执行 if (readSuc) { option.SucData?.Invoke(rowData, row.RowIndex.Value); } else { option.FailData?.Invoke(failRowData.odata, failRowData.failInfos); } } } } sheetDoc.Close(); } }