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 WriteCell(ReportSheetTemplate tpl, GroupDataHolder holder, CellRange currentCellRange, DataTable table, int valueRowIndex)
{
if (useR1C1Formula)
{
currentCellRange.FormulaR1C1 = "=" + tplTextContent;
return; // do noting
}
object value = GetValue(holder, table, valueRowIndex);
if (value == null || value == string.Empty || value == System.DBNull.Value)
{
value = tplDefaultContent;
}
// update value.
RangeHelper.UpdateCellValue(tpl, currentCellRange, value, tplFormat);
if (align == GroupAlign.vGroup)
{
lastGroupedValue = GetGroupValue(holder, table, valueRowIndex);
}
lastCellRange = currentCellRange;
}
public static IEnumerator GetRangeCells(Range range)
{
Type t = range.GetType();
PropertyInfo pCells = t.BaseType.GetProperty("Cells");
Array cells = (Array)pCells.GetValue(range, new object[0]);
return(cells.GetEnumerator());
}
public int DoMerge(int currentRowIndex, Range currentCellRange)
{
if (currentCellRange == null)
{
return(0);
}
lastCellRange = currentCellRange;
return(RangeHelper.MergeRanges(currentCellRange, mOption));
}
public IActionResult ImPortDriverAsync()
{
try
{
//定义键值对
Dictionary <int, object> pairs = new Dictionary <int, object>();
//定义Workbooks
Workbook workbooks = new Workbook();
//读取文件
workbooks.LoadFromFile(filePath);
int place = 1;
int sheetCount = workbooks.Worksheets.Count;
//循环工作薄
for (int i = 1; i < sheetCount; i++)
{
Worksheet worksheets = workbooks.Worksheets[i];
CellRange ranges = worksheets.Range[$"Item{i}"];
CellRange range = worksheets.Range[$"TailTitle{i}"];
for (int j = ranges.First().Row; j < range.First().Row; j++)
{
ranges[j, ranges.First().Column].CollapseGroup(GroupByType.ByRows);
if (ranges[j, ranges.First().Column].Text == null)
{
continue;
}
pairs.Add(place, ranges[j, ranges.First().Column].Text);
place++;
}
}
int result = 0;
StringBuilder stringBuilder = new StringBuilder();
//拼接
stringBuilder.Append("insert into DriverInfo(DriverName) values ");
using (IDbConnection connection = new SqlConnection {
ConnectionString = connectionString
})
{
foreach (var item in pairs)
{
stringBuilder.Append($"('{item.Value}'),");
}
string sql = stringBuilder.ToString().Substring(0, stringBuilder.Length - 1);
result = connection.Execute(sql);
}
if (result > 0)
{
return(Ok(1));
}
return(Ok("导入失败"));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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;
}
}
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++;
}
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;
}
}
}
}
public int CreateDColumns(DataTable table)
{
if (dColumn == null)
{
return(0);
}
// Current colCount include gCols, so substract it.
colCount -= dColumn.gCols;
// try to insert dColumns into Template.
// dColumn.CheckColumn ()
if (table == null || table.Rows.Count <= 0)
{
return(0);
}
for (int i = 0; i < table.Rows.Count; i++)
{
dColumn.CheckEachColumn(this, holder, 0, table, i);
// dColumn.InsertColumn (this, holder, table, i, false);
}
dCloumnsCreated = true;
// remove data from template
// Insert new Col in Template.
Range tplColRange = RangeHelper.GetRange(tplRange.Worksheet,
dColumn.startColIndex + dColumn.gCols, tplRange.Row,
tplColumCount - dColumn.startCellIndex - dColumn.gCols - 1,
// 50,
tplRowCount);
tplColRange.Copy(
RangeHelper.GetRange(tplRange.Worksheet,
dColumn.startColIndex, tplRange.Row,
tplColumCount - dColumn.startCellIndex - dColumn.gCols - 1,
// 50,
tplRowCount), true, true);
/*
* RangeHelper.InsertCopyRange(tplRange.Worksheet, tplColRange,
* tplColumCount - dColumn.startCellIndex - dColumn.gCols,
* tplRowCount,
* startColIndex , tplColRange.Row,
* XlInsertShiftDirection.xlShiftToRight, tplColumCount);
*/
tplColumCount -= dColumn.gCols;
for (int i = 0; i < lineList.Count; i++)
{
TplLine line = lineList [i];
line.cellList.RemoveRange(dColumn.startCellIndex, dColumn.gCols);
line.tplCellCount -= dColumn.gCols;
line.tplRange = RangeHelper.GetRange(tplRange.Worksheet,
3, line.tplRange.Row,
tplColumCount, 1);
}
// Refresh Line.TplRange ;
// RefreshLineTplRanges(block, 1);
return(1);
}
public static Range InsertCopyRange(Worksheet sheet, Range orign, int columns, int rows, int targetColumn,
int targetRow, XlInsertShiftDirection direction)
{
return(InsertCopyRange(sheet, orign, columns, rows, targetColumn,
targetRow, direction, 1));
}
public static int MergeRanges(Range currentCellRange, MergeOption mOption)
{
Worksheet sheet = currentCellRange.Worksheet;
Range range = null;
// clear value first.
currentCellRange.Value2 = null;
currentCellRange.Text = "";
switch (mOption)
{
case MergeOption.Up:
// Select previous range.
range = GetRange(sheet, currentCellRange.Column, currentCellRange.Row - 1, 1, 1);
if (range.HasMerged)
{
Range marea = range.MergeArea;
int mWidth = currentCellRange.Column - marea.Column + 1;
int mHeight = currentCellRange.Row - marea.Row + 1;
/*if (mWidth <= marea.ColumnCount && mHeight <= marea.RowCount)
* {
* Console.WriteLine ("Has be merged. ignore");
* return 1 ;
* }*/
if (mWidth < marea.ColumnCount)
{
mWidth = marea.ColumnCount;
}
if (mHeight < marea.RowCount)
{
mHeight = marea.RowCount;
}
range = GetRange(sheet,
marea.Column, marea.Row,
mWidth,
mHeight);
/*
* Console.WriteLine("Up cell has been merged: [" +
* marea.Column + ", " + marea.Row + "], " +
* (currentCellRange.Column - marea.Column + 1) + ", " +
* (currentCellRange.Row - marea.Row + 1) + ", [" +
* marea.ColumnCount + "," + marea.RowCount + "]");
*/
range.Merge();
}
else
{
range = GetRange(sheet,
currentCellRange.Column,
currentCellRange.Row - 1,
1, 2);
range.Merge();
}
return(1);
case MergeOption.Left:
// merge with Left cell.
/*
* Console.WriteLine("Merge Left: get left cell first: [" +
* (currentCellRange.Column ) +
* ", " + currentCellRange.Row + "].") ;
*/
range = GetRange(sheet, currentCellRange.Column - 1, currentCellRange.Row, 1, 1);
if (range.HasMerged)
{
Range marea = range.MergeArea;
int mWidth = currentCellRange.Column - marea.Column + 1;
int mHeight = currentCellRange.Row - marea.Row + 1;
if (mWidth < marea.ColumnCount)
{
mWidth = marea.ColumnCount;
}
if (mHeight < marea.RowCount)
{
mHeight = marea.RowCount;
}
range = GetRange(sheet,
marea.Column, marea.Row,
mWidth,
mHeight);
/*
* Console.WriteLine ("Left cell has been merged: [" +
* marea.Column + ", " + marea.Row + "], " +
* (currentCellRange.Column - marea.Column + 1) + ", " +
* (currentCellRange.Row - marea.Row + 1) + ", [" +
* marea.ColumnCount + "," + marea.RowCount + "]");
*/
range.Merge();
}
else
{
range = GetRange(sheet,
currentCellRange.Column - 1,
currentCellRange.Row,
2, 1);
range.Merge();
}
// range.Merge ();
return(1);
case MergeOption.never:
default:
return(0);
}
}
public static Range InsertCopyRange(Worksheet sheet, Range orign, int columns, int rows, int targetColumn,
int targetRow, XlInsertShiftDirection direction,
int lastColCount)
{
insertCopyRangeCallTimes++;
// return GetRange(sheet, targetColumn, targetRow, columns, rows);
int orgColumn = orign.Column;
int orgRow = orign.Row;
Range target = GetRange(sheet, targetColumn, targetRow, columns, rows);
if (direction == XlInsertShiftDirection.xlShiftToRight)
{
// insert blank
// target.Insert (direction, Missing.Value) ;
// Move target from origin to Right first
Range movedRange = GetRange(sheet, targetColumn, targetRow, lastColCount, rows);
Range movedNextRange = GetRange(sheet, targetColumn + columns, targetRow, lastColCount, rows);
movedRange.Move(movedNextRange, true, false);
target = GetRange(sheet, targetColumn, targetRow, columns, rows);
target.UnMerge();
}
orign.Copy(target, false, true);
switch (direction)
{
case XlInsertShiftDirection.xlShiftDown:
// copy row height ;
for (int i = 0; i < rows; i++)
{
Range sRow = GetEntireRow(sheet, i + orgRow);
Range tRow = GetEntireRow(sheet, i + targetRow);
try
{
tRow.RowHeight = sRow.RowHeight;
}
catch (Exception e)
{
Console.WriteLine("Set RowHeight Error: " + e);
}
}
break;
case XlInsertShiftDirection.xlShiftToRight:
// copy col width ;
for (int i = 0; i < columns; i++)
{
Range sCol = GetEntireCol(sheet, i + orgColumn);
Range tCol = GetEntireCol(sheet, i + targetColumn + 1);
try
{
tCol.ColumnWidth = sCol.ColumnWidth;
}catch (Exception e)
{
Console.WriteLine("Set ColumnWidth Error: " + e);
}
}
break;
}
return(target);
}
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]);
}
}
}
}