public static IEnumerable <string> GetStringArray(this IXLRow row) { foreach (var cell in row.Cells()) { yield return(cell.Value.ToString()); } }
public virtual List <PropertyMapParser> GetDefaultPropertyMapParsers <T>(IXLRow headerRow) where T : class { var result = new List <PropertyMapParser>(); var headerCells = headerRow.Cells( firstColumn: headerRow.FirstCellUsed().WorksheetColumn().ColumnNumber(), lastColumn: headerRow.LastCellUsed().WorksheetColumn().ColumnNumber()); foreach (var p in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public).Where(p => p.CanWrite && p.CanRead)) { var cell = headerCells.SingleOrDefault( c => String.Compare( strA: c.GetString(), strB: this.GetColumnName(p), comparisonType: StringComparison.InvariantCultureIgnoreCase) == 0); if (cell != null) { var mapping = new PropertyMapParser { ObjectPropertyInfo = p, ExcelColumnIndex = cell.WorksheetColumn().ColumnNumber(), TryGetProperty = this.TryParseProperty }; result.Add(mapping); } else { throw new Exception($"Excel did not provide required column: {p.Name}"); } } return(result); }
public static bool IgnoreRow(this IXLRow row) { if (row == null) { return(true); } return(row.Cells().All(c => string.IsNullOrWhiteSpace(c.GetValue <string>()))); }
public static void Set_AllBorder(IXLRow iXLRow) { foreach (var cell in iXLRow.Cells()) { cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; //cell.Style.Border.RightBorder = XLBorderStyleValues.Thin; } }
public static string LookupColumnLetter(IXLRow row, string text) { var targetText = ToLettersOnlyString(text); var targetRow = row .Cells(c => ToLettersOnlyString(c.GetString()) == targetText) .FirstOrDefault(); return(targetRow?.Address.ColumnLetter ?? throw new UnexpectedFormatException($"Failed to find header for '{text}'")); }
private dynamic LoadRow(List <string> columNames, IXLRow row) { var expandoObject = new ExpandoObject() as IDictionary <string, object>; for (var i = 0; i < columNames.Count; i++) { expandoObject.Add(columNames[i], row.Cells().ElementAt(i).GetString()); } return(expandoObject); }
/// <summary> /// Converts a IXLRow to a List<string>. /// </summary> /// <param name="row"></param> /// <returns></returns> public List <string> RowToList(IXLRow row) { List <string> list = new List <string>(); foreach (IXLCell c in row.Cells()) { list.Add(c.Value.ToString()); } return(list); }
/// <summary> /// 迭代 <see cref="IXLRow"/> 实例并将返回自定内容的 <seealso cref="IEnumerable{T}"/> 集合 /// </summary> /// <typeparam name="T">返回集合中的内容</typeparam> /// <param name="row">需要迭代的 <see cref="IXLRow"/> 实例</param> /// <param name="func">要对 <see cref="IXLRow"/> 中每个 <see cref="IXLCell"/> 操作的 <see cref="Func{T1, T2}"/> 委托</param> /// <returns>返回通过 <see cref="Func{T, TResult}"/> 中得到的值的 <see cref="IEnumerable{T}"/> 集合 </returns> static private IEnumerable <T> ToDataRow <T>(this IXLRow row, Func <IXLCell, T> func) { var list = new List <T>(); foreach (var cell in row.Cells()) { list.Add(func(cell)); } return(list); }
private static void AddDatatableRow(System.Data.DataTable dataResult, string readRange, IXLRow row) { dataResult.Rows.Add(); int cellIndex = 0; foreach (IXLCell cell in row.Cells(readRange)) { dataResult.Rows[dataResult.Rows.Count - 1][cellIndex] = cell.Value.ToString(); cellIndex++; } }
private void highlightEmailCell(Email email, IXLRow row) { try { if (email.IsConfirmed) { IXLCell cell; if (email.EmailAddress.Contains("@outlook.com")) { cell = row.Cells().ToList()[2]; } else { cell = row.Cells().ToList()[3]; } highlightCell(cell, XLColor.LightGreen); } } catch (NullReferenceException) { return; } catch (ArgumentOutOfRangeException) { return; } }
/// <summary> /// Find the ColumnLetter of a column header. /// </summary> /// <param name="headerName"></param> /// <returns>ColumnLetter</returns> public string FindColumnHeader(string headerName) { IXLRow headers = GetColumnHeaders(); foreach (IXLCell c in headers.Cells()) { if (c.Value.ToString().Replace(" ", String.Empty).Equals(headerName.Replace(" ", String.Empty), StringComparison.InvariantCultureIgnoreCase)) { return(c.Address.ColumnLetter); } } return(String.Empty); }
private string[] ReadColumns(CremaDataTable dataTable, IXLRow row) { var columns = new List <string>(); foreach (var item in row.Cells()) { var text = item.GetString(); if (text == string.Empty) { var message = string.Format("'{0}!{1}'의 이름이 공백입니다. 열의 이름은 공백이 될 수 없습니다.", item.Address.Worksheet.Name, item.Address); throw new CremaDataException(message); } if (text == CremaSchema.Tags || text == CremaSchema.Enable || text == CremaSchema.RelationID) { columns.Add(text); continue; } if (text == CremaSchema.Creator || text == CremaSchema.CreatedDateTime || text == CremaSchema.Modifier || text == CremaSchema.ModifiedDateTime) { columns.Add(text); continue; } if (dataTable.Columns.Contains(text) == false) { var message = string.Format("'{0}!{1}'의 '{2}'열은 '{3}'테이블에 존재하지 않는 열입니다.", item.Address.Worksheet.Name, item.Address, item.Value, dataTable.TableName); throw new CremaDataException(message); } if (columns.Contains(text) == true) { var message = string.Format("'{0}!{1}'의 '{2}'열은 이미 존재합니다.", item.Address.Worksheet.Name, item.Address, item.Value); throw new CremaDataException(message); } columns.Add(text); } return(columns.ToArray()); }
private bool ValidateSheetFormat2(IXLRow row) { string infoline = @"Ação|ID Externo*|Nome*|Data de Nascimento*|Gênero*|Login*|Senha*|CPF|RG|Email|Celular|ID Externo Responsável|Papel*|Código Grupo*|Nome Grupo*|Descrição Grupo|TAG"; string[] headerInfo = infoline.Split('|'); int i = 1; foreach (IXLCell cell in row.Cells()) { if (cell.Value.ToString() != headerInfo[i - 1]) { return(false); } i++; } return(true); }
private static string GetColumnsNames(List <GridColumnBase> columnList, System.Data.DataTable dataResult, IXLRow row) { string readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber); foreach (IXLCell cell in row.Cells(readRange)) { GridColumnBase gridColumn = columnList.FirstOrDefault(x => x.Name == cell.Value.ToString()); if (gridColumn != null) { var columName = gridColumn.PropertyInfo.Name; dataResult.Columns.Add(columName); } else { throw new System.Exception($"Column Name {cell.Value.ToString()} does not match any property in object"); } } return(readRange); }
protected void ReadUsingClosedXML(IFormFile fileExcel, string fullPath) { using (XLWorkbook wb = new XLWorkbook(fileExcel.OpenReadStream())) { IXLWorksheet worksheet = wb.Worksheet(1); wb.SaveAs(fullPath); bool FirstRow = true; //Range for reading the cells based on the last cell used. string readRange = "1:1"; var sheetRows = worksheet.RowsUsed().ToArray(); var rowscount = sheetRows.Length; for (int i = 0; i < rowscount; i++) { IXLRow row = sheetRows[i]; //If Reading the First Row (used) then add them as column name if (FirstRow) { var rowCells = row.Cells(readRange).ToArray(); var resNo = rowCells[0].Value; } } } }
static void Main(string[] args) { Log.Init("ExcelDiffTool_New_Log.txt"); #if !DEBUG try #endif { string[] files = { "", "" }; string[] names = { "src", "dst" }; List <string>[] sheetLists = { null, null }; // ファイル入力待ち { if (args.Length == 2) { files[0] = args[0]; files[1] = args[1]; } else { Log.WriteLine("ファイル1をドラッグアンドドロップしてください"); files[0] = Log.ReadLine(); Log.WriteLine("ファイル2をドラッグアンドドロップしてください"); files[1] = Log.ReadLine(); } } // CSVにコンバート for (int i = 0; i < names.Length; ++i) { Directory.CreateDirectory(names[i]); Log.WriteLine(string.Format("{0} をCSVに変換しています。", files[i])); sheetLists[i] = CsvConverter.SaveAsCsv(new XLWorkbook(files[i]), names[i]); } // 出力用ワークシート var outWb = new XLWorkbook(); // シート差分 ワークシートの追加、削除を検出 bool sheetDiffFound = false; sheetLists[0].Sort(); sheetLists[1].Sort(); var sheetDiffList = Diff.Execute(sheetLists[0], sheetLists[1]); foreach (var sheetDiff in sheetDiffList) { var sheet = outWb.AddWorksheet(sheetDiff.Text); // シートのステータスによって色を変更 if (sheetDiff.Status == Diff.Status.Add) { Log.WriteLine(string.Format("「{0}」シートが追加されました。", sheet.Name)); sheetDiffFound = true; sheet.TabColor = XLColor.LightGreen; var lines = CsvConverter.ReadCsvLines(Path.Combine(names[1], sheet.Name)); for (int rowIndex = 0; rowIndex < lines.Count; ++rowIndex) { IXLRow row = sheet.Row(rowIndex + 1); // 行番号出力 row.Cell(1).Value = rowIndex + 1; var columnList = CsvConverter.GetCsvValues(lines[rowIndex]); for (int columnIndex = 0; columnIndex < columnList.Count; ++columnIndex) {// カラム出力 row.Cell(columnIndex + 2).Value = columnList[columnIndex]; } var cells = row.Cells(1, columnList.Count + 1); cells.Style.Fill.PatternType = XLFillPatternValues.Solid; cells.Style.Fill.BackgroundColor = XLColor.LightGreen; } } else if (sheetDiff.Status == Diff.Status.Delete) { Log.WriteLine(string.Format("「{0}」シートが削除されました。", sheet.Name)); sheetDiffFound = true; sheet.TabColor = XLColor.LightCoral; var lines = CsvConverter.ReadCsvLines(Path.Combine(names[0], sheet.Name)); for (int rowIndex = 0; rowIndex < lines.Count; ++rowIndex) { IXLRow row = sheet.Row(rowIndex + 1); // 行番号出力 row.Cell(1).Value = rowIndex + 1; var columnList = CsvConverter.GetCsvValues(lines[rowIndex]); for (int columnIndex = 0; columnIndex < columnList.Count; ++columnIndex) {// カラム出力 row.Cell(columnIndex + 2).Value = columnList[columnIndex]; } var cells = row.Cells(1, columnList.Count + 1); cells.Style.Fill.PatternType = XLFillPatternValues.Solid; cells.Style.Fill.BackgroundColor = XLColor.LightCoral; } } else { // 両方に存在するシートなので、差分を検出する Log.WriteLine(string.Format("「{0}」シートの差分を検出中です。", sheet.Name)); // 行差分 行の追加、削除を検出 List <Diff.Line> rowDiffList = null; { var src = CsvConverter.ReadCsvLines(Path.Combine(names[0], sheet.Name)); var dst = CsvConverter.ReadCsvLines(Path.Combine(names[1], sheet.Name)); rowDiffList = Diff.Execute(src, dst); } bool rowDiffFound = false; int addCount = 0; int deleteCount = 0; for (int rowIndex = 0; rowIndex < rowDiffList.Count; ++rowIndex) { // とりあえず全部出力 var rowDiff = rowDiffList[rowIndex]; IXLRow row = sheet.Row(rowIndex + 1); // 行番号出力 { var cell = row.Cell(1); cell.Value = rowDiff.Index + 1; cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; } var columnList = CsvConverter.GetCsvValues(rowDiff.Text); for (int columnIndex = 0; columnIndex < columnList.Count; ++columnIndex) { // カラム出力 row.Cell(columnIndex + 2).Value = columnList[columnIndex]; } if (rowDiff.Status == Diff.Status.Add) { addCount++; rowDiffFound = true; var cells = row.Cells(1, columnList.Count + 1); cells.Style.Fill.PatternType = XLFillPatternValues.Solid; cells.Style.Fill.BackgroundColor = XLColor.LightGreen; } else if (rowDiff.Status == Diff.Status.Delete) { deleteCount++; rowDiffFound = true; var cells = row.Cells(1, columnList.Count + 1); cells.Style.Fill.PatternType = XLFillPatternValues.Solid; cells.Style.Fill.BackgroundColor = XLColor.LightCoral; } } if (rowDiffFound) { sheetDiffFound = true; Log.WriteLine("差分が検出されました。"); Log.WriteLine(string.Format("追加された行数 : {0}", addCount)); Log.WriteLine(string.Format("削除された行数 : {0}", deleteCount)); } else { sheet.Delete(); // 差分は無いので出力に含める必要が無い } } } if (!sheetDiffFound) { Log.WriteLine("シート差分はありません。"); } else { Log.WriteLine("保存中です。"); outWb.SaveAs("ExcelDiffTool_結果.xlsx"); } } #if !DEBUG catch (Exception e) { Log.WriteLine("=== 例外が発生!! ==="); Log.WriteLine(e.ToString()); } #endif Log.WriteLine("終了しました。"); Log.Term(); Console.ReadLine(); }
private Dictionary <string, object> CollectFields(CremaDataTable dataTable, string[] columns, IXLRow row) { var fields = new Dictionary <string, object>(columns.Length); foreach (var item in row.Cells()) { if (item.Value == null || item.Value.ToString() == string.Empty) { continue; } var columnIndex = item.Address.ColumnNumber - 1; if (columnIndex >= columns.Length) { throw new CremaDataException("'{0}!{1}'의 값'{2}'은(는) 테이블 범위 밖에 있으므로 사용할 수 없습니다.", item.Address.Worksheet.Name, item.Address, item.Value); } var columnName = columns[columnIndex]; var column = dataTable.Columns[columnName]; try { if (columnName == CremaSchema.Creator || columnName == CremaSchema.CreatedDateTime || columnName == CremaSchema.Modifier || columnName == CremaSchema.ModifiedDateTime) { continue; } if (columnName == CremaSchema.Tags) { fields.Add(columnName, item.GetString()); } else if (columnName == CremaSchema.Enable) { fields.Add(columnName, item.GetBoolean()); } else if (columnName == CremaSchema.RelationID) { fields.Add(columnName, item.GetValue <int>()); } else if (column.DataType == typeof(byte)) { fields.Add(columnName, item.GetValue <byte>()); } else if (column.DataType == typeof(sbyte)) { fields.Add(columnName, item.GetValue <sbyte>()); } else if (column.DataType == typeof(short)) { fields.Add(columnName, item.GetValue <short>()); } else if (column.DataType == typeof(ushort)) { fields.Add(columnName, item.GetValue <ushort>()); } else if (column.DataType == typeof(int)) { fields.Add(columnName, item.GetValue <int>()); } else if (column.DataType == typeof(uint)) { fields.Add(columnName, item.GetValue <uint>()); } else if (column.DataType == typeof(float)) { fields.Add(columnName, item.GetValue <float>()); } else if (column.DataType == typeof(double)) { fields.Add(columnName, item.GetDouble()); } else if (column.DataType == typeof(bool)) { fields.Add(columnName, item.GetBoolean()); } else if (column.DataType == typeof(TimeSpan)) { fields.Add(columnName, item.GetTimeSpan()); } else if (column.DataType == typeof(DateTime)) { fields.Add(columnName, item.GetDateTime()); } else { fields.Add(columnName, item.GetString()); } } catch (Exception e) { throw new CremaDataException(string.Format("'{0}!{1}'의 값'{2}'은(는) '{3}'열의 적합한 값이 아닙니다.", item.Address.Worksheet.Name, item.Address, item.Value, columnName), e); } } return(fields); }
public static Bio2DA ReadExcelTo2DA(IExportEntry export, string Filename) { var Workbook = new XLWorkbook(Filename); IXLWorksheet iWorksheet = null; if (Workbook.Worksheets.Count() > 1) { try { iWorksheet = Workbook.Worksheet("Import"); } catch { MessageBox.Show("Import Sheet not found"); return(null); } } else { iWorksheet = Workbook.Worksheet(1); } //Do we want to limit user to importing same column structure as existing? Who would be stupid enough to do something else??? ME. // - Kinkojiro, 2019 //STEP 1 Clear existing data Bio2DA bio2da = new Bio2DA(); bio2da.export = export; //STEP 2 Read columns and row names //Column names IXLRow hRow = iWorksheet.Row(1); foreach (IXLCell cell in hRow.Cells(hRow.FirstCellUsed().Address.ColumnNumber, hRow.LastCellUsed().Address.ColumnNumber)) { if (cell.Address.ColumnNumber > 1) //ignore excel column 1 { bio2da.ColumnNames.Add(cell.Value.ToString()); } } //Row names IXLColumn column = iWorksheet.Column(1); foreach (IXLCell cell in column.Cells()) { if (cell.Address.RowNumber > 1) //ignore excel row 1 { bio2da.RowNames.Add(cell.Value.ToString()); } } //Populate the Bio2DA now that we know the size bio2da.Cells = new Bio2DACell[bio2da.RowCount, bio2da.ColumnCount]; //Step 3 Populate the table. //indices here are excel based. Subtract two to get Bio2DA based. for (int rowIndex = 2; rowIndex < (bio2da.RowCount + 2); rowIndex++) { for (int columnIndex = 2; columnIndex < bio2da.ColumnCount + 2; columnIndex++) { IXLCell xlCell = iWorksheet.Cell(rowIndex, columnIndex); string xlCellContents = xlCell.Value.ToString(); if (!string.IsNullOrEmpty(xlCellContents)) { Bio2DACell newCell = new Bio2DACell(); if (int.TryParse(xlCellContents, out int intVal)) { newCell.Type = Bio2DACell.Bio2DADataType.TYPE_INT; newCell.Data = BitConverter.GetBytes(intVal); } else if (float.TryParse(xlCellContents, out float floatVal)) { newCell.Type = Bio2DACell.Bio2DADataType.TYPE_FLOAT; newCell.Data = BitConverter.GetBytes(floatVal); } else { newCell.Type = Bio2DACell.Bio2DADataType.TYPE_NAME; newCell.Pcc = export.FileRef; //for displaying, if this displays before the export is reloaded and 2da is refreshed newCell.Data = BitConverter.GetBytes((long)export.FileRef.FindNameOrAdd(xlCellContents)); //long because names are 8 bytes not 4 } bio2da[rowIndex - 2, columnIndex - 2] = newCell; } else { bio2da.IsIndexed = true; //Null cells = indexing } } } return(bio2da); }