private static TplLine ParseLine(Worksheet sheet, TplBlock block, int startCol, int startRow, int colCount)
{
TplLine line = new TplLine();
line.tplRange = RangeHelper.GetRange(sheet, startCol, startRow, colCount, 1);
for (int colIndex = 0; colIndex < colCount; colIndex++)
{
Range range = RangeHelper.GetCell(sheet, startCol + colIndex, startRow);
TplCell cell = new TplCell();
cell.acrossColumns = 1;
cell.tplRange = range;
cell.lastColIndex = colIndex + startCol;
string text = range.Value2 as string;
if (!string.IsNullOrEmpty(text))
{
ParseCell(block, line, cell, text.Trim());
}
line.cellList.Add(cell);
}
return(line);
}
public int IsNeedNewLine(GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueRowIndex)
{
if (iOption == InsertOption.never)
{
return(-1);
}
if (valueRowIndex == 0 && (iOption & InsertOption.onfirst) != 0)
{
return(0);
}
if (valueRowIndex == table.Rows.Count - 1 && (iOption & InsertOption.onLast) != 0)
{
return(0);
}
if ((iOption & InsertOption.afterChange) == 0 &&
(iOption & InsertOption.BeforeChange) == 0 &&
(iOption & InsertOption.always) == 0)
{
return(-1);
}
for (int i = 0; i < cellList.Count; i++)
{
TplCell cell = cellList[i];
if (cell.align != GroupAlign.hGroup && cell.IsNeedNewCell(holder, iOption, cell.lastColIndex, currentRowIndex, table, valueRowIndex))
{
return(i);
}
}
return(-1);
}
public void UpdateRowData(GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueRowIndex)
{
int cellColIndex = cellList[0].lastColIndex;
for (int i = 0; i < cellList.Count; i++)
{
TplCell cell = cellList[i];
if (cell.formula != null)
{
Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, cellColIndex, currentRowIndex);
cell.WriteCell(tpl, holder, cellRange, table, valueRowIndex);
}
cellColIndex += cell.acrossColumns;
}
}
private void UpdateLine(int currentRowIndex, GroupDataHolder holder, int startIndex, DataTable table, int valueRowIndex, MergeOption mo, bool updateMergeOnly)
{
int cellColIndex = cellList[0].lastColIndex;
for (int i = 0; i < cellList.Count; i++)
{
TplCell cell = cellList[i];
int merge = 0;
if (cell.mOption == mo &&
( // merge before.
i < startIndex ||
// merge after. next line is new line
(i >= startIndex && (iOption & InsertOption.BeforeChange) != 0) ||
// ignore group align option.
cell.align == GroupAlign.none))
// do Merge
{
Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, cellColIndex, currentRowIndex);
merge = cell.DoMerge(currentRowIndex, cellRange);
}
if (merge == 0 && !updateMergeOnly)
{
if (cell.formula == null)
{
Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, cellColIndex, currentRowIndex);
cell.WriteCell(tpl, holder, cellRange, table, valueRowIndex);
}
}
/*
* else
* // todo: Remove this noused line?
* cell.lastGroupedValue = cell.GetValue(holder, table, valueRowIndex);
*/
cellColIndex += cell.acrossColumns;
}
}
private bool IsGroupedColumn(string colName)
{
bool isGCol;
if (groupedColMap.TryGetValue(colName, out isGCol))
{
return(isGCol);
}
for (int i = 0; i < cellList.Count; i++)
{
TplCell cell = cellList [i];
if (cell.align == GroupAlign.hGroup && cell.tplGroupColName == colName)
{
groupedColMap.Add(colName, true);
return(true);
}
}
groupedColMap.Add(colName, false);
return(false);
}
public TplCell Copy()
{
TplCell cell = new TplCell();
cell.align = align;
cell.acrossColumns = acrossColumns;
cell.lastColIndex = lastColIndex;
cell.lastRowIndex = lastRowIndex;
cell.tplGroupColName = tplGroupColName;
cell.tplValueColName = tplValueColName;
cell.tplFormat = tplFormat;
cell.tplTextContent = tplTextContent;
cell.tplDefaultContent = tplDefaultContent;
cell.useR1C1Formula = useR1C1Formula;
cell.hgOption = hgOption;
if (formula != null)
{
cell.formula = formula.Copy();
}
cell.lastGroupedValue = null;
return(cell);
}
public void CheckEachColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueIndex)
{
for (int i = 0; i < gCols; i++)
{
bool colInserted = false;
// Check each cell in this columns
for (int j = 0; j < block.lineList.Count; j++)
{
TplLine line = block.lineList [j];
TplCell cell = line.cellList [startCellIndex + i];
if (cell.align != GroupAlign.hGroup)
{
continue;
}
if (!colInserted)
{
bool needNew = cell.IsNeedNewCell(holder, cell.hgOption, 0, currentRowIndex, table, valueIndex);
if (needNew)
{
InsertOneColumn(block, i, holder, table, valueIndex, false);
colInserted = true;
}
}
else
{
if ((cell.hgOption & InsertOption.BeforeChange) != 0)
{
continue;
}
// set last grouped value
cell.lastGroupedValue = cell.GetGroupValue(holder, table, valueIndex);
}
}
}
}
public void MergeVGroupCells()
{
for (int i = 0; i < lineList.Count; i++)
{
TplLine line = lineList[i];
/*
* if (!line.containsHGroup)
* continue;
*/
int colIndex = line.colIndex;
for (int j = 0; j < line.cellList.Count; j++)
{
TplCell cell = line.cellList [j];
if (cell.mOption != MergeOption.Up ||
(cell.align != GroupAlign.none &&
cell.align != GroupAlign.always))
{
colIndex += cell.acrossColumns;
continue;
}
for (int k = 0; k < line.insertedRowList.Count; k++)
{
int rowIndex = line.insertedRowList[k];
CellRange range = RangeHelper.GetCell(tplRange.Worksheet, colIndex, rowIndex);
if (!range.HasMerged)
{
RangeHelper.MergeRanges(range, MergeOption.Up);
}
}
colIndex += cell.acrossColumns;
}
}
}
private static SearchKey GetGroupedKeys(TplBlock block, TplLine line)
{
SearchKey root = null;
SearchKey pkey = null;
// get Line cell first
for (int i = 0; i < line.cellList.Count; i++)
{
TplCell leftCell = line.cellList [i];
if (leftCell.align != GroupAlign.vGroup)
{
continue;
}
SearchKey key = new SearchKey();
key.colName = leftCell.tplGroupColName;
if (root == null)
{
root = key;
}
if (pkey == null)
{
pkey = key;
}
else
{
pkey.nextKey = key;
pkey = key;
}
}
for (int i = 0; i < block.lineList.Count; i++)
{
TplLine l = block.lineList [i];
if (l.cellList.Count <= line.cellList.Count)
{
continue;
}
TplCell upCell = l.cellList [line.cellList.Count];
if (upCell.align != GroupAlign.hGroup)
{
continue;
}
SearchKey key = new SearchKey();
key.colName = upCell.tplGroupColName;
if (root == null)
{
root = key;
}
if (pkey == null)
{
pkey = key;
}
else
{
pkey.nextKey = key;
pkey = key;
}
}
return(root);
}
private static void ParseCell(TplBlock block, TplLine line, TplCell cell, string text)
{
text = text.Trim();
if (text.StartsWith("R1C1:"))
{
cell.useR1C1Formula = true;
cell.tplTextContent = text.Substring(5);
return;
}
if (text [0] != '{')
{
// as text
cell.tplTextContent = text;
return;
}
int i = text.IndexOf('}');
if (i > 0)
{
if (i + 1 != text.Length)
{
cell.tplTextContent = text.Substring(i + 1);
}
text = text.Substring(1, i - 1);
}
// using text as col name.
cell.tplValueColName = text;
Dictionary <string, string> pair = ParseKeyValuePair(text);
// parse format string
cell.tplFormat = GetPairValue(pair, "f");
if (!string.IsNullOrEmpty(cell.tplFormat))
{
cell.tplFormat = cell.tplFormat.ToLower();
}
// parse Merge option.
cell.mOption = ParseMergeOption(GetPairValue(pair, "m"));
// parse group options.
if (GetPairValue(pair, "vg") != null)
{
cell.align = GroupAlign.vGroup;
cell.tplGroupColName = GetPairValue(pair, "vg").Trim().ToUpper();
}
else if (GetPairValue(pair, "hg") != null)
{
cell.align = GroupAlign.hGroup;
cell.tplGroupColName = GetPairValue(pair, "hg").ToUpper();
line.containsHGroup = true;
InsertOption option = GetLineInsertOption(GetPairValue(pair, "hgo"));
if (option != InsertOption.afterChange && option != InsertOption.BeforeChange)
{
option = InsertOption.afterChange;
}
cell.hgOption = option;
}
// parse value string including formula.
string v = GetPairValue(pair, "v");
if (string.IsNullOrEmpty(v))
{
if (!string.IsNullOrEmpty(cell.tplGroupColName))
{
cell.tplValueColName = cell.tplGroupColName;
}
return;
}
//pase default value
cell.tplDefaultContent = GetPairValue(pair, "default");
i = v.IndexOf('(');
if (i < 0)
{
cell.tplValueColName = v.Trim().ToUpper();
return;
}
cell.formula = new CellForumla();
cell.formula.formulaName = i == 0 ? "" : v.Substring(0, i).ToLower();
int i2 = v.IndexOf(')');
if (i2 < 0)
{
Console.WriteLine("Warning:: formula not closed. [" + v + "]");
v = v.Substring(i + 1);
}
else
{
v = v.Substring(i + 1, i2 - i - 1);
}
v = v.Trim();
string[] colList = v.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
SearchKey pkey = null;
GroupValueSearchKey gkey = new GroupValueSearchKey();
gkey.formula = cell.formula.formulaName;
cell.formula.keyList.Add(gkey);
string colName = "";
for (int j = 0; j < colList.Length; j++)
{
string colPair = colList [j];
string[] cs = colPair.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
if (j == 0)
{
colName = cs [0];
/* gkey.formula = "" ; */
gkey.valueColName = colName.Trim().ToUpper();
continue;
}
SearchKey key = new SearchKey();
key.colName = cs [0].ToUpper();
if (cs.Length > 1)
{
key.isFixedValue = true;
key.keyValue = cs [1];
}
if (key.colName == "%")
{
key = GetGroupedKeys(block, line);
if (key == null)
{
continue;
}
}
if (pkey == null)
{
gkey.key = key;
pkey = key;
}
else
{
pkey.nextKey = key;
pkey = key;
}
while (pkey.nextKey != null)
{
pkey = pkey.nextKey;
}
}
block.gkeyList.Add(gkey.Copy());
}
public void InsertColumn(TplBlock block, GroupDataHolder holder, DataTable table, int valueIndex, bool hasData)
{
// do insert
if (insertCount > 0)
{
if (hasData)
{
// block.startRowIndex ;
Range colRange = RangeHelper.GetRange(tplRange.Worksheet,
startColIndex + insertCount - gCols, block.startRowIndex,
gCols, block.rowCount);
// Insert new ;
RangeHelper.InsertCopyRange(tplRange.Worksheet, colRange,
gCols, block.rowCount,
startColIndex + insertCount, block.startRowIndex,
XlInsertShiftDirection.xlShiftToRight, tplLastColCount);
}
// Insert new Col in Template.
Range tplColRange = RangeHelper.GetRange(tplRange.Worksheet,
startColIndex + insertCount - gCols, block.tplRange.Row,
gCols, block.tplRowCount);
RangeHelper.InsertCopyRange(tplRange.Worksheet, tplColRange,
gCols, block.tplRowCount,
startColIndex + insertCount, tplColRange.Row,
XlInsertShiftDirection.xlShiftToRight, tplLastColCount);
// Refresh Line.TplRange ;
RefreshLineTplRanges(block, gCols);
block.tplColumCount += gCols;
block.colCount += gCols;
}
// Insert cell into exsit lineList.
for (int lineIndex = 0; lineIndex < block.lineList.Count; lineIndex++)
{
TplLine line = block.lineList [lineIndex];
for (int j = 0; j < gCols; j++)
{
int cellIndex = startCellIndex + (insertCount > 0 ? (insertCount - gCols) : 0) + j;
TplCell cell = line.cellList [cellIndex];
/* if (cell.lastColIndex != nextCloIndex - (insertCount > 0 ? 1 : 0)) */
// if (cell.lastColIndex < nextCloIndex || cell.lastColIndex >= nextCloIndex + gCols)
// continue ;
// if (lineIndex == 2)
// lineIndex = 2 ;
if (insertCount > 0)
{
cell = cell.Copy();
cell.lastColIndex += gCols;
line.cellList.Insert(cellIndex + gCols, cell);
}
if (cell.formula != null)
{
for (int keyIndex = 0; keyIndex < cell.formula.keyList.Count; keyIndex++)
{
GroupValueSearchKey gkey = cell.formula.keyList[keyIndex];
SearchKey key = gkey.key;
while (key != null)
{
if (IsGroupedColumn(key.colName))
{
key.keyValue = RangeHelper.GetColValue(table, valueIndex, key.colName);
key.isFixedValue = true;
}
key = key.nextKey;
}
block.gkeyList.Add(gkey.Copy());
if (gkey.key != null)
{
gkey.key.FillKey(table, valueIndex);
}
block.holder.AddValue(block.countedMap, gkey, table, valueIndex);
}
}
else
if (cell.hgOption != InsertOption.never)
{
cell.tplTextContent = Convert.ToString(cell.GetValueByIndex(valueIndex, table));
}
cell.align = GroupAlign.none;
Console.WriteLine("Inserted hg Line[" + lineIndex + "]cell[" + cellIndex + "] = " + cell.formula);
/* update Row Value */
if (lineIndex < block.rowCount)
{
Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, startColIndex + (insertCount /* == 0 ? 0 : insertCount - 1*/) + j,
block.startRowIndex + lineIndex);
cell.WriteCell(tpl, holder, cellRange, table, valueIndex);
}
}
}
// Console.WriteLine ("---- End of " + valueIndex);
// increment next
nextCloIndex += gCols;
insertCount += gCols;
}
public void InsertOneColumn(TplBlock block, int colIndex, GroupDataHolder holder, DataTable table, int valueIndex, bool hasData)
{
if (hasData)
{
// block.startRowIndex ;
Range colRange = RangeHelper.GetRange(tplRange.Worksheet,
startColIndex + colIndex, block.startRowIndex,
1, block.rowCount);
// Insert new ;
RangeHelper.InsertCopyRange(tplRange.Worksheet, colRange,
1, block.rowCount,
startColIndex + gCols + insertCount, block.startRowIndex,
XlInsertShiftDirection.xlShiftToRight, tplLastColCount);
}
// Insert new Col in Template.
Range tplColRange = RangeHelper.GetRange(tplRange.Worksheet,
startColIndex + colIndex, block.tplRange.Row,
1, block.tplRowCount);
RangeHelper.InsertCopyRange(tplRange.Worksheet, tplColRange,
1, block.tplRowCount,
startColIndex + gCols + insertCount, tplColRange.Row,
XlInsertShiftDirection.xlShiftToRight, tplLastColCount);
// Refresh Line.TplRange ;
RefreshLineTplRanges(block, 1);
block.tplColumCount += 1;
block.colCount += 1;
// Insert cell into exsit lineList.
for (int lineIndex = 0; lineIndex < block.lineList.Count; lineIndex++)
{
TplLine line = block.lineList[lineIndex];
int cellIndex = startCellIndex + colIndex;
TplCell cell0 = line.cellList[cellIndex];
TplCell cell = cell0.Copy();
cell.lastColIndex += 1;
line.cellList.Insert(startCellIndex + gCols + insertCount, cell);
/*
* if (cell.useExcelFormula)
* {
* cell.tplRange = cell0.tplRange ;
* }
*/
if (cell.formula != null)
{
for (int keyIndex = 0; keyIndex < cell.formula.keyList.Count; keyIndex++)
{
GroupValueSearchKey gkey = cell.formula.keyList[keyIndex];
if (gkey.rKey == null)
{
SearchKey key0 = new SearchKey();
key0.colName = gkey.valueColName;
gkey.rKey = ReusedKey.FindReusedKey(key0);
}
SearchKey key = gkey.key;
while (key != null)
{
if (IsGroupedColumn(key.colName))
{
key.keyValue = RangeHelper.GetColValue(table, valueIndex, key.colName);
key.isFixedValue = true;
}
key = key.nextKey;
}
block.gkeyList.Add(gkey.Copy());
if (gkey.key != null)
{
gkey.key.FillKey(table, valueIndex);
}
block.holder.AddValue(block.countedMap, gkey, table, valueIndex);
}
}
/*
* else if (cell.hgOption != InsertOption.never)
* {
* // set fixed text
* cell.tplTextContent = Convert.ToString(RangeHelper.GetColValue (table, valueIndex, cell.tplValueColName)) ;
* }
*/
cell.align = GroupAlign.none;
Console.WriteLine("Inserted hg Line[" + lineIndex + "]cell[" + cellIndex + "] = " + cell.formula);
/* update Row Value */
if (lineIndex < block.rowCount)
{
Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, startColIndex + gCols + insertCount,
block.startRowIndex + lineIndex);
cell.WriteCell(tpl, holder, cellRange, table, valueIndex);
}
}
// Console.WriteLine ("---- End of " + valueIndex);
// increment next
nextCloIndex += 1;
insertCount++;
}
private TplCloumn FindDColumn()
{
TplCloumn dcol = new TplCloumn();
dcol.gCols = 0;
for (int i = 0; i < lineList.Count; i++)
{
TplLine line = lineList [i];
// int startIndex = -1 ;
for (int j = 0; j < line.cellList.Count; j++)
{
TplCell cell = line.cellList [j];
if (cell.align == GroupAlign.hGroup)
{
// Only One DColumn
if (dcol.gCols <= 0)
{
dcol.startCellIndex = j;
dcol.startColIndex = cell.lastColIndex;
}
dcol.gCols++;
}
}
if (dcol.gCols > 0)
{
break;
}
}
if (dcol.gCols <= 0)
{
return(null);
}
dcol.tplLastColCount = tplColumCount - dcol.startCellIndex - dcol.gCols;
if (dcol.tplLastColCount <= 0)
{
dcol.tplLastColCount = 1;
}
// find out gcells
dcol.tplRange = RangeHelper.GetRange(tplRange.Worksheet, dcol.startColIndex
, tplRowCount, dcol.gCols, lineList.Count);
for (int i = 0; i < lineList.Count; i++)
{
TplLine line = lineList [i];
for (int j = 0; j < line.cellList.Count; j++)
{
TplCell cell = line.cellList [j];
if (cell.align == GroupAlign.hGroup)
{
TplCell newCell = cell.Copy();
dcol.cellList.Add(newCell);
if (!dcol.groupColList.Contains(cell.tplGroupColName))
{
dcol.groupColList.Add(cell.tplGroupColName);
dcol.groupColIndexList.Add(-1);
}
}
}
}
return(dcol);
}
public void MergeHGroupCells()
{
for (int i = 0; i < lineList.Count; i++)
{
TplLine line = lineList [i];
if (!line.containsHGroup)
{
continue;
}
for (int j = 0; j < line.insertedRowList.Count; j++)
{
int rowIndex = line.insertedRowList [j];
object lastValue = null;
int colIndex = dColumn.startColIndex;
// if (line.cellList [colIndex].mOption != MergeOption.Left)
// continue ;
// ingore this line ;
for (int k = 0; k < dColumn.insertCount; k++)
{
Range cell = RangeHelper.GetCell(tplRange.Worksheet, colIndex, rowIndex);
object value = cell.Value2;
if (lastValue != null &&
Equals(lastValue, value))
{
/* remove after debug.
* if (colIndex == 27)
* Console.WriteLine ("colINdex=27");
*/
// clear
// judge if last row is last hgrouoped row.
if (i == lineList.Count - 1 || lineList [i + 1].containsHGroup)
{
RangeHelper.MergeRanges(cell, MergeOption.Left);
}
else
{
// check is this column first column in hgrouped columns.
if (k % dColumn.gCols > 0)
{
RangeHelper.MergeRanges(cell, MergeOption.Left);
}
}
}
lastValue = value;
colIndex++;
}
// repair unmerged range
int afterIndex = dColumn.startCellIndex + dColumn.insertCount;
for (int k = afterIndex;
k < line.cellList.Count; k++)
{
TplCell cell = line.cellList [k];
if (cell.mOption == MergeOption.Left)
{
Range range = RangeHelper.GetCell(tplRange.Worksheet, colIndex, rowIndex);
RangeHelper.MergeRanges(range, MergeOption.Left);
}
colIndex += cell.acrossColumns;
}
}
}
}