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("ファイル１をドラッグアンドドロップしてください");
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);
}