protected override void FormattaInformazione(DataRowView info, Excel.Range rngInfo, Excel.Range rngRow, Excel.Range rngData, object testoAlternativo = null)
{
base.FormattaInformazione(info, rngInfo, rngRow, rngData, testoAlternativo);
string siglaInformazione = info["SiglaInformazione"].ToString();
if (Regex.IsMatch(siglaInformazione, @"OFFERTA_MI\d_G\dE") || Regex.IsMatch(siglaInformazione, @"OFFERTA_MI\d_G\dP"))
{
Range rng = new Range(rngData.Address);
Excel.Validation v = _ws.Range[rng.ToString()].Validation;
v.Delete();
v.Add(Type: Excel.XlDVType.xlValidateDecimal,
AlertStyle: Excel.XlDVAlertStyle.xlValidAlertStop,
Operator: Excel.XlFormatConditionOperator.xlGreaterEqual,
Formula1: "0");
v.IgnoreBlank = false;
// v.InputTitle = "Valore";
// v.InputMessage = "Digitare un valore maggiore o uguale a zero";
v.ErrorTitle = "Valore non ammesso";
v.ErrorMessage = "Il valore digitato non è corretto. Sono ammessi solo valori positivi";
v.ShowError = true;
v.ShowInput = true;
Marshal.ReleaseComObject(v);
v = null;
}
//switch (info["SiglaInformazione"].ToString())
//{
//case "UNIT_COMM":
//case "RISPETTO_PROG_PREC": break; //info["SiglaInformazione"].Equals("RISPETTO_PROG_PREC")
//default: //OFFERTA_MI1_G1E //OFFERTA_MI1_G1P
// if (!(info["DesInformazione"].Equals("ACQ/VEN")) && !(info["DesInformazione"].Equals("Codice bilanciamento")) )
// {
// Range rng = new Range(rngData.Address);
// Excel.Validation v = _ws.Range[rng.ToString()].Validation;
// v.Delete();
// v.Add(Type: Excel.XlDVType.xlValidateDecimal,
// AlertStyle: Excel.XlDVAlertStyle.xlValidAlertStop,
// Operator: Excel.XlFormatConditionOperator.xlGreaterEqual,
// Formula1: "0");
// v.IgnoreBlank = false;
// // v.InputTitle = "Valore";
// // v.InputMessage = "Digitare un valore maggiore o uguale a zero";
// v.ErrorTitle = "Valore non ammesso";
// v.ErrorMessage = "Il valore digitato non è corretto. Sono ammessi solo valori positivi";
// v.ShowError = true;
// v.ShowInput = true;
// Marshal.ReleaseComObject(v);
// v = null;
// }
// break;
//}
//}
}
/// <summary>
/// ENUM 값에 드롭박스 적용
/// </summary>
private void ApplyDropboxOnEnumCells(Excel.Worksheet worksheet, Schema schema, object[,] tableDatas)
{
int rowCount = tableDatas.GetLength(0);
int colCount = tableDatas.GetLength(1);
if (rowCount <= 1 || colCount == 0)
{
return;
}
bool hasRef = schema.Fields.Any(prop => prop.Type == FieldType.Ref || !string.IsNullOrEmpty(prop.RefSchemaName) || prop.Type == FieldType.Bool);
if (!hasRef)
{
return;
}
Field[] fields = schema.Fields.ToArray();
Excel.Range sheetCells = worksheet.Cells;
Excel.Worksheet enumSheet = GetWorksheet(RefTableSheetName);
if (enumSheet == null)
{
enumSheet = AddWorksheet(RefTableSheetName);
}
else
{
enumSheet.Cells.Clear();
}
Dictionary <string, string> enumRangeAddrs = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
if (fields.Any(field => field.Type == FieldType.Bool))
{
Excel.Range startCell = (Excel.Range)enumSheet.Cells[1, 1];
Excel.Range endCell = (Excel.Range)enumSheet.Cells[3, 1];
Excel.Range enumRange = enumSheet.Range[startCell, endCell];
object[,] boolTable = new object[3, 1]
{
{ BooleanTableName },
{ "TRUE" },
{ "FALSE" }
};
enumRange.Cells.Value2 = boolTable;
enumRange = enumSheet.Range[startCell.Offset[1], endCell];
enumRangeAddrs.Add(BooleanTableName, $"={enumSheet.Name}!{enumRange.Address}");
}
for (int fieldIdx = 0; fieldIdx < fields.Length; ++fieldIdx)
{// 이 테이블에 사용되는 Enum 셀을 생성 후 주소를 기억
Field field = fields[fieldIdx];
if (field.Type != FieldType.Ref && string.IsNullOrEmpty(field.RefSchemaName))
{// 참조 프로퍼티가 Flat화 되면서 String으로 변경되어서 일단 이렇게 했음. 추후에 방식 수정 필요
continue;
}
string refTablePropName = $"{field.RefSchemaName}({field.RefFieldName})";
if (enumRangeAddrs.ContainsKey(refTablePropName))
{
continue;
}
if (!linker.HasTable(field.RefSchemaName))
{
continue;
}
Table enumTable = linker.GetTable(field.RefSchemaName);
int refPropIndex = enumTable.Schema.GetFieldIndex(field.RefFieldName);
if (refPropIndex == -1)
{
continue;
}
if (enumTable.RowCount == 0)
{
continue;
}
object[,] enumValueNames = new object[enumTable.RowCount + 1, 1];
enumValueNames[0, 0] = refTablePropName;
for (int rowIdx = 0; rowIdx != enumTable.RowCount; ++rowIdx)
{
enumValueNames[rowIdx + 1, 0] = enumTable[rowIdx][refPropIndex];
}
int enumRangeColumnIdx = 1 + enumRangeAddrs.Count;
Excel.Range startCell = enumSheet.Cells[1, enumRangeColumnIdx] as Excel.Range;
Excel.Range endCell = enumSheet.Cells[1 + enumTable.RowCount, enumRangeColumnIdx] as Excel.Range;
Excel.Range enumRange = enumSheet.Range[startCell, endCell];
enumRange.Value2 = enumValueNames;
enumRange = enumSheet.Range[startCell.Offset[1], endCell];
enumRangeAddrs.Add(refTablePropName, $"={enumSheet.Name}!{enumRange.Address}");
}
// 테이블 레코드에 필터로 넣음
for (int fieldIdx = 0; fieldIdx < fields.Length; ++fieldIdx)
{
Field field = fields[fieldIdx];
string refTableFieldName;
if (field.Type == FieldType.Ref || !string.IsNullOrEmpty(field.RefSchemaName))
{// 참조 프로퍼티가 Flat화 되면서 String으로 변경되어서 일단 이렇게 했음. 추후에 방식 수정 필요
refTableFieldName = $"{field.RefSchemaName}({field.RefFieldName})";
}
else if (field.Type == FieldType.Bool)
{
refTableFieldName = BooleanTableName;
}
else
{
refTableFieldName = string.Empty;
}
if (!enumRangeAddrs.ContainsKey(refTableFieldName))
{
continue;
}
string enumRangeAddr = enumRangeAddrs[refTableFieldName];
Excel.Range startCell = (Excel.Range)sheetCells.Cells[MarginRowCount + 2 /* 첫 데이터 행 위치 */, MarginColumnCount + 1 + fieldIdx];
Excel.Range endCell = (Excel.Range)sheetCells.Cells[MarginRowCount + rowCount, MarginColumnCount + 1 + fieldIdx];
Excel.Range range = sheetCells.Range[startCell, endCell];
Excel.Validation valid = range.Validation;
if (valid != null)
{
valid.Delete();
}
valid.Add(
Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertWarning,
Excel.XlFormatConditionOperator.xlBetween,
enumRangeAddr,
Type.Missing);
valid.InCellDropdown = true;
}
}
