/// <summary> /// Restore rows' heights after inserting, deleting /// </summary> /// <param name="doc"></param> /// <param name="startRow"></param> /// <param name="heights"></param> internal static void RestoreRowHeight(this SLDocument doc, int startRow, double[] heights) { for (int i = 0; i < heights.Length; i++) { doc.SetRowHeight(startRow + i, heights[i]); } }
} // SetRangeValue<T> /// <summary> /// Save rows' heights before deleting/inserting /// </summary> /// <param name="doc"></param> /// <param name="rowStart"></param> /// <returns></returns> internal static double[] GetRowHeights(this SLDocument doc, int rowStart) { var stat = doc.GetWorksheetStatistics(); int rowCount = stat.EndRowIndex - rowStart + 1; rowCount = rowCount < 0 ? 0 : rowCount; var result = new double[rowCount]; for (int i = 0; i < rowCount; i++) { result[i] = doc.GetRowHeight(i + rowStart); } return(result); }
/// <summary> /// Set value to DefinedName range with offset /// </summary> /// <typeparam name="T"></typeparam> /// <param name="doc"></param> /// <param name="definedName"></param> /// <param name="value"></param> /// <param name="rowOffset"></param> /// <param name="columnOffset"></param> public static void SetDefinedNameValue <T>(this SLDocument doc, string definedName, T value, int rowOffset = 0, int columnOffset = 0) { var address = doc.GetDefinedNameText(definedName); if (String.IsNullOrWhiteSpace(address)) { return; } var @switch = new Dictionary <Type, Action <int, int, object> > { { typeof(string), (row, col, val) => { doc.SetCellValue(row, col, (string)val); } }, { typeof(int), (row, col, val) => { doc.SetCellValue(row, col, (int)val); } }, { typeof(float), (row, col, val) => { doc.SetCellValue(row, col, (float)val); } }, { typeof(double), (row, col, val) => { doc.SetCellValue(row, col, (double)val); } }, { typeof(decimal), (row, col, val) => { doc.SetCellValue(row, col, (decimal)val); } }, { typeof(DateTime), (row, col, val) => { doc.SetCellValue(row, col, (DateTime)val); } }, { typeof(bool), (row, col, val) => { doc.SetCellValue(row, col, (bool)val); } }, }; var addresses = address.Split(','); for (int i = 0; i < addresses.Length; i++) { var adr = addresses[i]; // parse full address var indexExl = adr.IndexOf('!'); if (indexExl == -1) { continue; } var sheetName = adr.Substring(0, indexExl); if (adr.Contains(':')) // range in address { var addressRange = adr.Substring(indexExl + 1); var rangeArray = addressRange.Split(':'); var beginArray = rangeArray[0].Split('$'); var endArray = rangeArray[1].Split('$'); // for cycle int rowStart = -1; int colStart = -1; int rowEnd = -1; int colEnd = -1; bool doCycle = false; if (beginArray.Length == 2) // entire column or row { int val = -1; if (Int32.TryParse(beginArray[1], out val)) // is entire row { rowStart = val; doCycle = Int32.TryParse(endArray[1], out rowEnd); colStart = 1; colEnd = SLDocument.ColumnLimit; rowStart += rowOffset; rowEnd += rowOffset; if (rowStart <= 0) { if (rowEnd <= 0) { doCycle = false; } else // shrink range { rowStart = 1; } } } else // entire column { rowStart = 1; rowEnd = SLDocument.RowLimit; // very slowly!!!! Don't do it colStart = SLConvert.ToColumnIndex(beginArray[1]); colEnd = SLConvert.ToColumnIndex(endArray[1]); colStart += columnOffset; colEnd += columnOffset; doCycle = colStart > 0 && colEnd > 0; } } else // simple range { var correctStartCell = SLDocument.WhatIsRowColumnIndex(rangeArray[0].Replace("$", "") , out rowStart, out colStart); var correctEndCell = SLDocument.WhatIsRowColumnIndex(rangeArray[1].Replace("$", "") , out rowEnd, out colEnd); doCycle = correctStartCell && correctEndCell; // do with offsets rowStart += rowOffset; rowEnd += rowOffset; if (rowStart <= 0) { if (rowEnd <= 0) { doCycle = false; } else // shrink range vertically { rowStart = 1; } } colStart += columnOffset; colEnd += columnOffset; if (colStart <= 0) { if (colEnd <= 0) { doCycle = false; } else // shrink range horizontally { colStart = 1; } } } // Fill cells if (doCycle) { // select worksheet and insert value if (doc.SelectWorksheet(sheetName)) { for (int r = rowStart; r <= rowEnd; r++) { for (int c = colStart; c <= colEnd; c++) { @switch[typeof(T)](r, c, value); } } } } } else // single cells in address { var addressCell = adr.Substring(indexExl + 1).Replace("$", ""); int row = -1; int col = -1; if (SLDocument.WhatIsRowColumnIndex(addressCell, out row, out col)) { row += rowOffset; col += columnOffset; // select worksheet and insert value if (row > 0 && row <= SLDocument.RowLimit && col > 0 && col <= SLDocument.ColumnLimit && doc.SelectWorksheet(sheetName)) { @switch[typeof(T)](row, col, value); } } } } } // SetRangeValue<T>
/// <summary> /// Set value to DefinedName range /// </summary> /// <typeparam name="T"></typeparam> /// <param name="doc"></param> /// <param name="definedName"></param> /// <param name="value"></param> public static void SetDefinedNameValue <T>(this SLDocument doc, string definedName, T value) { doc.SetDefinedNameValue <T>(definedName, value, 0, 0); }