public override void DataToExcel(DataDictionaryList data, bool delete = false)
{
var indexedData = data.IndexById();
var ids = new HashSet <string>(indexedData.Keys);
var restIds = new HashSet <string>(indexedData.Keys);
foreach (var row in Worksheet.Rows().Skip(DataStartRowIndex - 1))
{
var id = row.Cell(IdColumnIndex).GetValue <string>();
if (ids.Contains(id))
{
var rowData = indexedData[id];
Columns.ForEach(column => {
var cell = row.Cell(column.Index);
var value = rowData[column.Name];
if (!cell.HasFormula)
{
cell.SetValue <string>(value != null ? Convert.ToString(value) : "");
}
});
restIds.Remove(id);
}
}
;
}
public override void DataToExcel(DataDictionaryList data, bool delete = false)
{
var indexedData = data.IndexById();
var restIds = new HashSet <string>(indexedData.Keys);
var indexedRows = new Dictionary <string, Row>();
var rows = Worksheet.Descendants <Row>();
foreach (var row in rows.Where(row => row.RowIndex >= ColumnNamesRowIndex + 1))
{
var idCellReference = IdColumnIndex + row.RowIndex.ToString();
var idCell = (Cell)row.FirstOrDefault(cell => ((Cell)cell).CellReference.Value == idCellReference);
if (idCell == null)
{
continue;
}
var id = SeedTableCell.ValueString(idCell, SharedStringTable);
indexedRows[id] = row;
Dictionary <string, object> rowData;
if (indexedData.TryGetValue(id, out rowData))
{
restIds.Remove(id);
var columnIndexToName = ColumnIndexToName(row.RowIndex.ToString());
foreach (Cell cell in row)
{
// 数式セルは飛ばす
if (cell.CellFormula != null)
{
continue;
}
string columnName;
if (columnIndexToName.TryGetValue(cell.CellReference.Value, out columnName))
{
object value;
if (rowData.TryGetValue(columnName, out value))
{
SeedTableCell.SetValue(cell, Convert.ToString(value), SharedStringTable);
}
}
}
}
}
}
public override void DataToExcel(DataDictionaryList data, bool delete = false)
{
var indexedData = data.IndexById();
var idIndexes = new List <IdIndex>();
var restIds = new HashSet <string>(indexedData.Keys);
// replace
foreach (var rowIndex in Enumerable.Range(DataStartRowIndex, Worksheet.Dimension.Rows))
{
var id = Worksheet.Cells[rowIndex, IdColumnIndex].GetValue <string>();
if (id == null || id == "")
{
continue;
}
idIndexes.Add(new IdIndex(id, rowIndex));
Dictionary <string, object> rowData;
if (indexedData.TryGetValue(id, out rowData))
{
restIds.Remove(id);
foreach (var column in Columns)
{
object value;
if (rowData.TryGetValue(column.Name, out value))
{
var cell = Worksheet.Cells[rowIndex, column.Index];
if (cell.Formula != null && cell.Formula.Length != 0)
{
continue;
}
cell.Value = value;
}
}
}
}
// add
var restIdGroups = GetReversedIdGroups(
idIndexes.Select(idIndex => {
try {
return(long.Parse(idIndex.Id));
} catch (FormatException exception) {
throw new IdParseException(idIndex.Id, exception);
}
}).ToList(),
restIds.Select(id => {
try {
return(long.Parse(id));
} catch (FormatException exception) {
throw new IdParseException(id, exception);
}
}).ToList()
);
var doAdd = restIdGroups.Count != 0;
List <string> lastRestIdGroup = null;
if (restIdGroups.TryGetValue("0", out lastRestIdGroup))
{
restIdGroups.Remove("0");
}
var endColumnIndex = Worksheet.Dimension.Columns;
// 逆順に処理
idIndexes.Reverse();
foreach (var idIndex in idIndexes)
{
List <string> idGroup;
if (restIdGroups.TryGetValue(idIndex.Id, out idGroup))
{
var previousRowIndex = idIndex.Index;
var newRowIndex = previousRowIndex + 1;
// 行を挿入数分増やす
Worksheet.InsertRow(newRowIndex, idGroup.Count, previousRowIndex);
// 直上行
var previousRow = Worksheet.Cells[previousRowIndex, 1, previousRowIndex, endColumnIndex];
// idGroupはID逆順
var rowIndexOffset = idGroup.Count - 1;
foreach (var id in idGroup)
{
var rowIndex = newRowIndex + rowIndexOffset;
// 直上行の値をコピー (数式等のため)
previousRow.Copy(Worksheet.Cells[rowIndex, 1]);
var rowData = indexedData[id];
foreach (var column in Columns)
{
object value;
if (rowData.TryGetValue(column.Name, out value))
{
var cell = Worksheet.Cells[rowIndex, column.Index];
if (cell.Formula != null && cell.Formula.Length != 0)
{
continue;
}
cell.Value = value;
}
}
--rowIndexOffset;
}
}
}
// 最初の行には異なった処理が必要
if (lastRestIdGroup != null)
{
Worksheet.InsertRow(DataStartRowIndex, lastRestIdGroup.Count, DataStartRowIndex);
var newRowIndex = DataStartRowIndex;
var nextRowIndex = DataStartRowIndex + lastRestIdGroup.Count;
var nextRow = Worksheet.Cells[nextRowIndex, 1, nextRowIndex, endColumnIndex];
var rowIndexOffset = lastRestIdGroup.Count - 1;
foreach (var id in lastRestIdGroup)
{
var rowIndex = newRowIndex + rowIndexOffset;
nextRow.Copy(Worksheet.Cells[rowIndex, 1]);
var rowData = indexedData[id];
foreach (var column in Columns)
{
object value;
if (rowData.TryGetValue(column.Name, out value))
{
var cell = Worksheet.Cells[rowIndex, column.Index];
if (cell.Formula != null && cell.Formula.Length != 0)
{
continue;
}
cell.Value = value;
}
}
--rowIndexOffset;
}
}
// delete
if (delete)
{
var allIds = new HashSet <string>(indexedData.Keys);
// 行追加がある場合はインデックスの対応を再走査する
if (doAdd)
{
idIndexes = new List <IdIndex>();
foreach (var rowIndex in Enumerable.Range(DataStartRowIndex, Worksheet.Dimension.Rows))
{
var id = Worksheet.Cells[rowIndex, IdColumnIndex].GetValue <string>();
if (id == null || id == "")
{
continue;
}
idIndexes.Add(new IdIndex(id, rowIndex));
}
}
var toDeleteIndexes = idIndexes.Where(idIndex => !allIds.Contains(idIndex.Id)).Select(idIndex => idIndex.Index).ToList();
toDeleteIndexes.Sort();
toDeleteIndexes.Reverse();
foreach (var rowIndex in toDeleteIndexes)
{
Worksheet.DeleteRow(rowIndex);
}
}
}