/// <summary>
/// 將ExcelTableRows中的單元格值裝載入當前工作表中。
/// </summary>
/// <param name="excelTable">數據源</param>
/// <param name="firstRowIndex">起始行號</param>
/// <param name="firstColumnIndex">起始列號</param>
public void SetExcelTableRows(ExcelTable excelTable, int firstRowIndex, int firstColumnIndex)
{
if (excelTable == null)
{
throw new ArgumentNullException("excelTable參數不能為NULL.");
}
SetColumnWithByColumns(excelTable.Columns, firstColumnIndex);
int curRowIndex = firstRowIndex;
for (int i = 0; i < excelTable.RowCount; i++)
{
int curColumnIndex = firstColumnIndex;
curRowIndex = firstRowIndex + i;
IRow row = CreateRow(curRowIndex, firstColumnIndex, excelTable.Columns.RealCount + firstColumnIndex - 1, null);
foreach (ExcelCell cell in excelTable.Rows[i].Cells)
{
for (int cellCount = curColumnIndex; cellCount < cell.OwningColumn.ColumnCount + curColumnIndex; cellCount++)
{
if (row.GetCell(cellCount) == null)
{
row.CreateCell(cellCount);
}
row.GetCell(cellCount).CellStyle
= cell.OwningColumn.DefaultExcelCellStyle == null?this._defaultExcelCellStyle.GetCellStyle(this.WorkBook) : cell.OwningColumn.DefaultExcelCellStyle.GetCellStyle(this.WorkBook);
}
if (cell.OwningColumn.ColumnCount > 1)
{
this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(curRowIndex, curRowIndex, curColumnIndex, curColumnIndex + cell.OwningColumn.ColumnCount - 1));
}
SetCellValue(cell.Value, row, curColumnIndex);
curColumnIndex += cell.OwningColumn.ColumnCount;
}
}
}
// private bool _isRow = false;
/// <summary>
/// 創建一個工作表元素對象
/// </summary>
public ExcelElement()
{
_table = null;
}
public static void CreateExcelTableFromDecisionTable(tDecisionTable decisionTable, ExcelWorksheet wsSheet, string tableName)
{
// palse Table in Excel
const int stratRow = 5;
const int stratColumn = 2;
// Calculate size of the table
var totalInput = decisionTable.input.Count();
var totalOutput = decisionTable.output.Count();
var totalRules = decisionTable.rule.Count();
var endRow = stratRow + totalRules + 1;
var endColum = stratColumn + totalInput + totalOutput;
// Create Excel table Header
using (ExcelRange rng = wsSheet.Cells[stratRow, stratColumn, endRow, endColum])
{
//Indirectly access ExcelTableCollection class
ExcelTable table = wsSheet.Tables.Add(rng, tableName);
var color = Color.FromArgb(250, 199, 111);
//Set Columns position & name
var i = 0;
foreach (var inputClause in decisionTable.input)
{
table.Columns[i].Name = inputClause.label;
//add input variable name
AddExcelCellByRowAndColumn(stratColumn + i, stratRow + 1,
inputClause.inputExpression.Item.ToString(), wsSheet, color);
i++;
}
foreach (var outputClause in decisionTable.output)
{
table.Columns[i].Name = outputClause.label;
// Add Output variableId name
var variableId = outputClause.name ?? "";
AddExcelCellByRowAndColumn(stratColumn + i, stratRow + 1, variableId, wsSheet, color);
i++;
}
// Add empty cell for annotation
table.Columns[i].Name = "Annotation";
AddExcelCellByRowAndColumn(stratColumn + i, stratRow + 1, " ", wsSheet, color);
//table.ShowHeader = false;
//table.ShowFilter = true;
//table.ShowTotal = true;
}
// Set Excel table content
var inputColumn = stratColumn;
var outputColumn = stratColumn + totalInput;
var row = stratRow + 1;
foreach (var rule in decisionTable.rule)
{
// input content
row++;
foreach (var tUnaryTestse in rule.inputEntry)
{
AddExcelCellByRowAndColumn(inputColumn, row, tUnaryTestse.text, wsSheet);
inputColumn++;
}
inputColumn = stratColumn;
// set Output result content
foreach (var literalExpression in rule.outputEntry)
{
AddExcelCellByRowAndColumn(outputColumn, row, literalExpression.Item.ToString(), wsSheet);
outputColumn++;
}
var annotationCellName = string.Concat(GetColumnName(endColum), row);
using (ExcelRange rng = wsSheet.Cells[annotationCellName])
{
rng.Value = rule.description;
}
outputColumn = stratColumn + totalInput;
}
//wsSheet1.Protection.IsProtected = false;
//wsSheet1.Protection.AllowSelectLockedCells = false;
wsSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
}