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 void FillTemplate()
{
DateTime now = DateTime.Now;
for (int i = 0; i < sheetList.Count; i++)
{
ReportSheetTemplate tpl = sheetList [i];
tpl.FillTemplate();
}
// setup chart
for (int i = 0; i < sheetList.Count; i++)
{
ReportSheetTemplate tpl = sheetList [i];
for (int j = 0; j < tpl.blockList.Count; j++)
{
TplBlock block = tpl.blockList [j];
if (!block.isChart)
{
continue;
}
block.SetupChart();
}
}
for (int i = 0; i < sheetList.Count; i++)
{
ReportSheetTemplate tpl = sheetList[i];
for (int j = 0; j < tpl.blockList.Count; j++)
{
TplBlock block = tpl.blockList[j];
Console.WriteLine("Block: " + block.name +
" gKeyList: " + block.gkeyList.Count +
" ValueList: " + block.holder.valueList.Count);
}
}
Console.WriteLine("GetColVlaue Call Times: " + RangeHelper.getColValueCount);
Console.WriteLine("GetColVlaueByIndex Call Times: " + RangeHelper.getColValueByIndexCount);
Console.WriteLine("getReusedValueCallCount Call Times: " + ReusedKey.getReusedValueCallCount);
Console.WriteLine("columnNameFindCount Call Times: " + ReusedKey.columnNameFindCount);
Console.WriteLine("valueReusedCount Call Times: " + ReusedKey.valueReusedCount);
Console.WriteLine("ReusedKeyPool Size: " + ReusedKey.keyPool.Count);
Console.WriteLine("GetRange Call Times: " + RangeHelper.getRangeCallTimes);
Console.WriteLine("UpdateCellValue Call Times: " + RangeHelper.getRangeCallTimes);
Console.WriteLine("insertCopyRange Call Times: " + RangeHelper.insertCopyRangeCallTimes);
Console.WriteLine("Total time: " + (DateTime.Now.Subtract(now)).TotalMilliseconds + "ms.");
}
private void RefreshLineTplRanges(TplBlock block, int colCount)
{
for (int i = 0; i < block.lineList.Count; i++)
{
TplLine line = block.lineList [i];
line.tplRange = RangeHelper.GetRange(line.tplRange.Worksheet,
line.tplRange.Column,
line.tplRange.Row,
line.tplCellCount + insertCount,
colCount
);
line.tplCellCount += colCount;
}
}
private TplBlock FindDataBlock(string blockName)
{
for (int i = 0; i < sheetList.Count; i++)
{
ReportSheetTemplate tpl = sheetList [i];
for (int j = 0; j < tpl.blockList.Count; j++)
{
TplBlock block = tpl.blockList [j];
if (block.name == blockName)
{
return(block);
}
}
}
return(null);
}
public int CheckColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueIndex)
{
// if grouped Value changed.
// if value has be inserted.
// Insert Cell At EachLine
// Update TplLine Info.
int startIndex = IsNeedNewCol(currentRowIndex, holder, table, valueIndex);
if (startIndex < 0)
{
return(0);
}
// Console.WriteLine("---- Start of " + valueIndex);
InsertColumn(block, holder, table, valueIndex, true);
return(1);
}
public void LoadTemplate(DBO db, Workbook book, Dictionary <string, object> paramMap)
{
for (int i = 0; i < book.Worksheets.Count; i++)
{
Worksheet sheet = book.Worksheets [i];
ReportSheetTemplate tpl = TplLoader.ParseTemplate(sheet, i + 1);
sheetList.Add(tpl);
/* tpl.startRowIndex = 300; */
tpl.dset = dataSet;
tpl.paramMap = paramMap;
tpl.LoadDataSource(db);
}
for (int i = 0; i < sheetList.Count; i++)
{
ReportSheetTemplate tpl = sheetList [i];
for (int j = 0; j < tpl.blockList.Count; j++)
{
TplBlock block = tpl.blockList [j];
if (!block.isChart)
{
continue;
}
if (block.chartDataBlock != null)
{
continue;
}
// find block in each sheet.
block.chartDataBlock = FindDataBlock(block.chartDataBlockName);
}
}
return;
}
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);
}
}
}
}
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 static ReportSheetTemplate ParseTemplate(Worksheet sheet, int sheetIndex)
{
/* Range topRange = RangeHelper.GetRange (sheet, 1, 1, 1, 1).EntireColumn ; */
ReportSheetTemplate tpl = new ReportSheetTemplate();
tpl.sheet = sheet;
// Load pics
PicturesCollection pictures = sheet.Pictures;
List <Rectangle> pics = new List <Rectangle>();
for (int i = 0; i < pictures.Count; i++)
{
ExcelPicture picture = pictures [i];
pics.Add(new Rectangle(picture.Left, picture.Top, picture.Width, picture.Height));
}
tpl.pics = pics;
// clear pictures.
sheet.Pictures.Clear();
int lastValuedRowIndex = 1;
for (int rowIndex = 1; rowIndex < MAX_ROW_COUNT; rowIndex++)
{
Range range = RangeHelper.GetRange(sheet, 1, rowIndex, 1, 1);
string text = (string)range.Value2;
if (string.IsNullOrEmpty(text))
{
continue;
}
lastValuedRowIndex = rowIndex;
if (text.Equals("sql", StringComparison.CurrentCultureIgnoreCase))
{
// here is a SQL.
range = RangeHelper.GetRange(sheet, 2, rowIndex, 1, 1);
string tableName = range.Value2 as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
range = RangeHelper.GetRange(sheet, 3, rowIndex, 1, 1);
string sql = range.Value2 as string;
if (string.IsNullOrEmpty(sql))
{
continue;
}
// add sheet prefix to tableName
if (tableName.IndexOf('.') < 0)
{
tableName = "S" + sheetIndex + "." + tableName;
}
tpl.sqlList [tableName] = sql;
continue;
}
Dictionary <string, string> blockParams = ParseKeyValuePair(text);
TplBlock block = new TplBlock();
block.startColIndex = 2;
block.startRowIndex = rowIndex;
block.colCount = block.tplColumCount = int.Parse(blockParams ["cols"]);
if (blockParams.ContainsKey("name"))
{
block.name = blockParams ["name"];
}
else
{
block.name = "S" + sheetIndex + ".block" + (tpl.blockList.Count + 1);
}
// parse chart params
if (blockParams.ContainsKey("ischart") &&
"true".Equals(blockParams ["ischart"]))
{
block.isChart = true;
// parse dataBlock
if (blockParams.ContainsKey("datablock"))
{
block.chartDataBlockName = blockParams["datablock"];
// add sheet prefix to tableName
if (block.chartDataBlockName.IndexOf('.') < 0)
{
block.chartDataBlockName = "S" + sheetIndex + "." +
block.chartDataBlockName;
}
}
else
{
block.chartDataBlockName = "S" + sheetIndex + ".block2";
}
// find chartDataBlock
for (int i = 0; i < tpl.blockList.Count; i++)
{
TplBlock blk = tpl.blockList [i];
if (blk.name.Equals(block.chartDataBlockName))
{
block.chartDataBlock = blk;
}
}
if (blockParams.ContainsKey("seriesfrom") &&
"col".Equals(blockParams ["seriesfrom"]))
{
block.chartSeriesFrom = false;
}
}
int blockRows = block.tplRowCount = int.Parse(blockParams ["rows"]);
lastValuedRowIndex += blockRows;
if (blockParams.ContainsKey("copy"))
{
block.copyOnly = "true".Equals(blockParams ["copy"]);
}
block.tableName = blockParams ["table"];
// add sheet prefix to tableName
if (block.tableName.IndexOf('.') < 0)
{
block.tableName = "S" + sheetIndex + "." + block.tableName;
}
if (blockParams.ContainsKey("updateallrow"))
{
block.updateAllRow = "true".Equals(blockParams["updateallrow"]);
}
if (blockParams.ContainsKey("autofit") && blockParams["autofit"] == "true")
{
tpl.autoFit = true;
}
if (blockParams.ContainsKey("joinat"))
{
if (!int.TryParse(blockParams ["joinat"], out block.joinat))
{
block.joinat = -1;
}
}
//if (blockParams.ContainsKey("emptycount"))
//{
// if (!int.TryParse(blockParams["emptycount"], out block.defaultEmptyRowsCount))
// block.defaultEmptyRowsCount = 0;
//}
if (blockParams.ContainsKey("emptytable"))
{
block.emptyTableName = blockParams["emptytable"];
// add sheet prefix to tableName
if (block.emptyTableName.IndexOf('.') < 0)
{
block.emptyTableName = "S" + sheetIndex + "." + block.emptyTableName;
}
}
if (blockParams.ContainsKey("coltable"))
{
block.tplColTableName = blockParams["coltable"];
// add sheet prefix to tableName
if (block.tplColTableName.IndexOf('.') < 0)
{
block.tplColTableName = "S" + sheetIndex + "." + block.tplColTableName;
}
}
block.tplRange = RangeHelper.GetRange(sheet, block.startColIndex, block.startRowIndex,
block.colCount, blockRows);
if (block.copyOnly)
// Just return directly.
{
tpl.blockList.Add(block);
continue;
}
for (int i = 0; i < blockRows; i++)
{
TplLine line = ParseLine(sheet, block, 3, i + block.startRowIndex, block.colCount);
line.tpl = tpl;
line.colIndex = 3;
line.iOption = GetLineInsertOption(RangeHelper.GetCell(sheet, 2, i + block.startRowIndex).Value2 as string);
line.tplCellCount = block.colCount;
block.lineList.Add(line);
}
block.InitDColumn();
if (block.dColumn != null)
{
block.dColumn.tpl = tpl;
}
tpl.blockList.Add(block);
}
tpl.startRowIndex = lastValuedRowIndex + 5;
return(tpl);
}
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 void ClearExcelReport(bool autoFit, string fileName, List<ReportSheetTemplate> tplList)
* {
* bool reusedFlag = false;
* if (xlapp.Workbooks.Count > 0)
* {
* reusedFlag = true;
* }
* xlapp.Workbooks.Open(fileName,
* Missing.Value, Missing.Value, Missing.Value, Missing.Value,
* Missing.Value, Missing.Value, Missing.Value, Missing.Value,
* Missing.Value, Missing.Value, Missing.Value, Missing.Value,
* Missing.Value, Missing.Value);
*
* xlapp.DisplayAlerts = false;
*
* for (int i = 0; i < xlapp.ActiveWorkbook.Worksheets.Count &&
* i < tplList.Count; i++)
* {
* Worksheet worksheet = (Worksheet)xlapp.ActiveWorkbook.Worksheets[i + 1];
*
* ReportSheetTemplate tpl = tplList[i];
* JoinTable(worksheet, tpl);
* // Clear Data
* Clear(worksheet, tpl.startRowIndex);
*
* if (autoFit || tpl.autoFit)
* {
* Range range = worksheet.get_Range("A1", "DZ1").EntireColumn;
* range.AutoFit();
* }
* // .GetType ().GetMethod ("AutoFit").Invoke (range, new object[0]) ;
*
* }
*
* // remove warnning sheet.
*
* IEnumerator e = xlapp.ActiveWorkbook.Worksheets.GetEnumerator();
* while (e.MoveNext())
* {
* Worksheet sheet = (Worksheet)e.Current;
*
* if (sheet.Name.IndexOf("Warning") >= 0)
* sheet.Delete();
* }
* ((_Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).Activate();
* ((Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).get_Range("A1", "A1").Activate();
* // only save activeWorkBook
* xlapp.ActiveWorkbook.Save();
* // only close activeWorkbook ;
* xlapp.ActiveWorkbook.Close(true, Missing.Value, Missing.Value);
* // xlapp.Workbooks.Close ();
* }
*/
public static void JoinTable(Spire.Xls.Worksheet sheet, ReportSheetTemplate tpl)
{
if (tpl.blockList.Count < 2)
{
return;
}
TplBlock firstBlock = tpl.blockList [1];
int blockRow = firstBlock.startRowIndex;
int blocklastColum = firstBlock.startColIndex + firstBlock.colCount
/* -
* (firstBlock.dColumn == null ? 0 : firstBlock.dColumn.gCols)*/;
int joinedRows = 0;
for (int i = 2; i < tpl.blockList.Count; i++)
{
TplBlock block = tpl.blockList [i];
if (block.joinat >= 0 && block.rowCount > 0)
{
// CopyRangeToFirstTable
CellRange range = RangeHelper.GetRange(sheet, block.startColIndex + block.joinat + 1,
block.startRowIndex - joinedRows,
block.colCount, block.rowCount);
range.Copy(
RangeHelper.GetRange(sheet, blocklastColum + 1, firstBlock.startRowIndex, block.colCount, block.rowCount));
/* range.EntireRow.Delete (Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp) ; */
// delete rows.
for (int k = 0; k < block.rowCount; k++)
{
sheet.DeleteRow(block.startRowIndex - joinedRows);
}
joinedRows += block.rowCount;
if (block.dColumn != null && block.dColumn.startCellIndex == block.joinat)
{
// Merge Joined Table Columns.
for (int j = 0; j < block.lineList.Count; j++)
{
TplLine line = block.lineList [j];
if (!line.containsHGroup)
{
continue;
}
Boolean hasMerged = false;
for (int k = 0; k < line.insertedRowList.Count; k++)
{
int rowIndex = line.insertedRowList [k];
rowIndex = rowIndex - block.startRowIndex + blockRow;
CellRange leftRange = RangeHelper.GetRange(sheet, blocklastColum, rowIndex, 1, 1);
if (leftRange.MergeArea != null)
{
leftRange = RangeHelper.GetRange(sheet, leftRange.MergeArea.Column, leftRange.MergeArea.Row, 1, 1);
}
CellRange rightRange = RangeHelper.GetRange(sheet, blocklastColum + 1, rowIndex, 1, 1);
if (rightRange.MergeArea != null)
{
rightRange = RangeHelper.GetRange(sheet, rightRange.MergeArea.Column, rightRange.MergeArea.Row, 1, 1);
}
if (leftRange.Text.Equals(rightRange.Text))
{
// Merge
RangeHelper.GetRange(sheet, leftRange.Column,
leftRange.Row,
rightRange.Column + rightRange.Columns.Length - leftRange.Column,
Math.Min(rightRange.Rows.Length, leftRange.Rows.Length)
).Merge();
hasMerged = true;
}
}
if (!hasMerged)
{
break;
}
} // end for
} // end if
blocklastColum += block.colCount - block.joinat;
}
}
}
public void FillTemplate()
{
for (int i = 0; i < blockList.Count; i++)
{
TplBlock block = blockList [i];
if (block.copyOnly)
{
Range range = RangeHelper.InsertCopyRange(sheet, block.tplRange,
block.tplColumCount, block.tplRowCount,
block.startColIndex, startRowIndex + rowCount,
XlInsertShiftDirection.xlShiftDown);
IEnumerator e = RangeHelper.GetRangeCells(range);
while (e.MoveNext())
{
Range cell = (Range)e.Current;
if (cell.HasMerged)
{
continue;
}
string s = cell.Value2 as string;
if (s != null && s.StartsWith("#") && s.Length > 1 &&
paramMap != null)
{
s = s.Substring(1);
string[] s2 = s.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
object pValue = null;
paramMap.TryGetValue(s2 [0], out pValue);
string format = "";
if (s2.Length > 1)
{
format = s2 [1].ToLower();
}
RangeHelper.UpdateCellValue(this, cell, pValue, format);
}
CellRange origin = RangeHelper.GetRange(sheet, cell.Column, cell.Row - startRowIndex - rowCount + block.startRowIndex, 1, 1);
if (origin.HasMerged)
{
// doMerge
int col = origin.MergeArea.Column;
int mWidth = origin.MergeArea.ColumnCount;
int row = origin.MergeArea.Row + startRowIndex + rowCount - block.startRowIndex;
int mHeight = origin.MergeArea.RowCount;
CellRange mRange = RangeHelper.GetRange(sheet, col, row, mWidth, mHeight);
if (!mRange.HasMerged)
{
mRange.Merge();
}
}
}
rowCount += block.tplRowCount;
}
else
{
block.startRowIndex = startRowIndex + rowCount;
if (block.isChart)
{
// chart block should be filled at last.
return;
}
// check cloumn table first
if (!string.IsNullOrEmpty(block.tplColTableName) &&
dset.Tables.Contains(block.tplColTableName))
{
block.CreateDColumns(dset.Tables [block.tplColTableName]);
}
if (!dset.Tables.Contains(block.tableName))
{
throw new ArgumentException("DataTable [" + block.tableName + "] of Block [" + block.name + "] not found in DataSet!");
}
if (dset.Tables[block.tableName].Rows.Count <= 0 && block.emptyTableName != null)
{
rowCount += block.FillBlock(dset.Tables[block.emptyTableName]);
}
else
{
rowCount += block.FillBlock(dset.Tables [block.tableName]);
}
}
}
}
