/// <summary>
///Lock cell cua hoc sinh mien giam
/// </summary>
public void LockCellExemptType(IVTWorksheet workSheet, int row1, int col1, int row2, int col2)
{
NativeExcel.IWorksheet templateSheet = workSheet.Worksheet;
NativeExcel.IRange usedRang = templateSheet.Range[row1, col1, row2, col2];
usedRang.Locked = true;
usedRang.EntireRow.Locked = true;
}
public IVTWorksheet CopySheetToBefore(IVTWorksheet worksheet, int index)
{
NativeExcel.IWorksheet sheet = Workbook.Worksheets.AddBefore(index);
IVTWorksheet vtSheet = new VTWorksheet(sheet);
vtSheet.CopySheet(worksheet);
return(vtSheet);
}
public IVTWorksheet CopySheetToBefore(IVTRange worksheetRange, IVTWorksheet beforeSheet, string sheetname = "")
{
NativeExcel.IWorksheet sheet = Workbook.Worksheets.AddBefore(beforeSheet.Worksheet);
IVTWorksheet vtSheet = new VTWorksheet(sheet);
vtSheet.CopyPasteSameSize(worksheetRange, 1, 1);
vtSheet.Name = sheetname;
return(vtSheet);
}
public IVTWorksheet CopySheetToBeforeLast(IVTWorksheet worksheet)
{
NativeExcel.IWorksheet templateSheet = worksheet.Worksheet;
NativeExcel.IWorksheet sheet = Workbook.Worksheets.AddBefore(Workbook.Worksheets.Count);
IVTWorksheet vtSheet = new VTWorksheet(sheet);
vtSheet.CopySheet(worksheet);
return(vtSheet);
}
public void Lock(IVTWorksheet workSheet, int row1, int col1, int row2, int col2)
{
NativeExcel.IWorksheet templateSheet = workSheet.Worksheet;
NativeExcel.IRange usedRang = templateSheet.Range[row1, col1, row2, col2];
usedRang.Locked = true;
usedRang.Font.Color = System.Drawing.Color.Black;
usedRang.Interior.Color = System.Drawing.Color.Yellow;
//usedRang.Font.Strikethrough = true;
}
public void CopySheet(IVTWorksheet worksheet, string lastCell)
{
NativeExcel.IWorksheet templateSheet = worksheet.Worksheet;
NativeExcel.IRange usedRang = templateSheet.UsedRange;
IVTRange vtRange = worksheet.GetRange("A1", lastCell);
CopyPasteSameSize(vtRange, 1, 1);
CopyPageSetup(templateSheet);
}
public void CopySheet(IVTWorksheet worksheet)
{
NativeExcel.IWorksheet templateSheet = worksheet.Worksheet;
NativeExcel.IRange usedRang = templateSheet.UsedRange;
IVTRange vtRange = worksheet.GetRange(1, 1, usedRang.Rows.Count + usedRang.Row - 1, usedRang.Columns.Count + 1);
CopyPasteSameSize(vtRange, 1, 1);
CopyPageSetup(templateSheet);
}
public IVTWorksheet CopySheetToLast(IVTWorksheet worksheet, string lastCell = null)
{
NativeExcel.IWorksheet templateSheet = worksheet.Worksheet;
NativeExcel.IWorksheet sheet = Workbook.Worksheets.AddAfter(Workbook.Worksheets.Count);
IVTWorksheet vtSheet = new VTWorksheet(sheet);
if (lastCell == null)
{
vtSheet.CopySheet(worksheet);
}
else
{
vtSheet.CopySheet(worksheet, lastCell);
}
return(vtSheet);
}
public IVTWorksheet CopySheetToLastClearColor(IVTWorksheet worksheet, string lastCell = null, object Color = null)
{
NativeExcel.IWorksheet templateSheet = worksheet.Worksheet;
NativeExcel.IWorksheet sheet = Workbook.Worksheets.AddAfter(Workbook.Worksheets.Count);
if (Color != null)
{
sheet.Cells.Interior.Color = (Color)Color;
}
IVTWorksheet vtSheet = new VTWorksheet(sheet);
if (lastCell == null)
{
vtSheet.CopySheet(worksheet);
}
else
{
vtSheet.CopySheet(worksheet, lastCell);
}
return(vtSheet);
}
public void FillVariableValue(Dictionary <string, object> Data, IVTWorksheet OtherRange = null)
{
foreach (IRangeRow row in Range.Rows)
{
foreach (IRange cell in row.Columns)
{
string value = cell.Value == null ? "" : cell.Value.ToString();
// value = ${abc[].xyz.123}
MatchCollection matches = Regex.Matches(value, @"\$\{([^\}]+)\}");
foreach (Match match in matches)
{
// Finally, we get the Group value and display it.
string key = match.Groups[1].Value;
string[] arrKey = key.Split('.');
string key0 = arrKey[0];
if (key0.EndsWith("[]"))
{
object val0 = Data.ContainsKey(key0.Replace("[]", "")) ? Data[key0.Replace("[]", "")] : new List <object>();
object val = null;
List <object> list = (List <object>)val0;
IRange cell1 = cell;
string value1 = value;
if (list.Count > 0)
{
foreach (object o in list)
{
val = o;
for (int i = 1; i < arrKey.Count(); i++)
{
string s = arrKey[i];
val = GetPropertyValue(val, s);
}
if (value == "${" + key + "}")
{
cell1.Formula = val;
if (OtherRange != null)
{
OtherRange.SetCellValue(cell1.Address().Replace("$", ""), val);
}
}
else
{
value1 = value1.Replace("${" + key + "}", val == null ? "" : val.ToString());
cell1.Formula = value1;
if (OtherRange != null)
{
OtherRange.SetCellValue(cell1.Address().Replace("$", ""), value1);
}
}
cell1 = Sheet.Worksheet.Range[cell1.Row + 1, cell1.Column];
}
}
else
{
cell1.Formula = "";
}
}
else
{
object val0 = Data.ContainsKey(arrKey[0]) ? Data[arrKey[0]] : null;
object val = null;
foreach (string s in arrKey)
{
if (val == null)
{
val = val0;
}
else
{
val = GetPropertyValue(val, s);
}
}
if (value == "${" + key + "}")
{
cell.Formula = val;
if (OtherRange != null)
{
OtherRange.SetCellValue(cell.Address().Replace("$", ""), val);
}
}
else
{
value = value.Replace("${" + key + "}", val == null ? "" : val.ToString());
cell.Formula = value;
if (OtherRange != null)
{
OtherRange.SetCellValue(cell.Address().Replace("$", ""), value);
}
}
}
}
}
}
}
public VTRange(IRange range, IVTWorksheet sheet)
{
Range = range;
Sheet = sheet;
}
public void FillVariableValue(Dictionary <string, object> Data, IVTWorksheet OtherRange = null)
{
IVTRange range = new VTRange(Worksheet.UsedRange, this);
range.FillVariableValue(Data, OtherRange);
}
