/// <summary> /// Get the height of specified rows. /// </summary> /// <param name="Unit">The unit of the height to be returned.</param> /// <param name="StartRowIndex">The row index of the start row.</param> /// <param name="EndRowIndex">The row index of the end row.</param> /// <returns>The height in the specified unit type.</returns> public double GetHeight(SLMeasureUnitTypeValues Unit, int StartRowIndex, int EndRowIndex) { if (StartRowIndex < 1) { StartRowIndex = 1; } if (StartRowIndex > SLConstants.RowLimit) { StartRowIndex = SLConstants.RowLimit; } if (EndRowIndex < 1) { EndRowIndex = 1; } if (EndRowIndex > SLConstants.RowLimit) { EndRowIndex = SLConstants.RowLimit; } long lHeight = 0; int i = 0; SLRowProperties rp; for (i = StartRowIndex; i <= EndRowIndex; ++i) { if (slws.RowProperties.ContainsKey(i)) { rp = slws.RowProperties[i]; lHeight += rp.HeightInEMU; } else { lHeight += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } double result = 0; switch (Unit) { case SLMeasureUnitTypeValues.Centimeter: result = SLConvert.FromEmuToCentimeter((double)lHeight); break; case SLMeasureUnitTypeValues.Emu: result = (double)lHeight; break; case SLMeasureUnitTypeValues.Inch: result = SLConvert.FromEmuToInch((double)lHeight); break; case SLMeasureUnitTypeValues.Point: result = SLConvert.FromEmuToPoint((double)lHeight); break; } return(result); }
/// <summary> /// Get the width of the specified columns. /// </summary> /// <param name="Unit">The unit of the width to be returned.</param> /// <param name="StartColumnIndex">The column index of the start column.</param> /// <param name="EndColumnIndex">The column index of the end column.</param> /// <returns>The width in the specified unit type.</returns> public double GetWidth(SLMeasureUnitTypeValues Unit, int StartColumnIndex, int EndColumnIndex) { if (StartColumnIndex < 1) { StartColumnIndex = 1; } if (StartColumnIndex > SLConstants.ColumnLimit) { StartColumnIndex = SLConstants.ColumnLimit; } if (EndColumnIndex < 1) { EndColumnIndex = 1; } if (EndColumnIndex > SLConstants.ColumnLimit) { EndColumnIndex = SLConstants.ColumnLimit; } long lWidth = 0; int i = 0; SLColumnProperties cp; for (i = StartColumnIndex; i <= EndColumnIndex; ++i) { if (slws.ColumnProperties.ContainsKey(i)) { cp = slws.ColumnProperties[i]; lWidth += cp.WidthInEMU; } else { lWidth += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } double result = 0; switch (Unit) { case SLMeasureUnitTypeValues.Centimeter: result = SLConvert.FromEmuToCentimeter((double)lWidth); break; case SLMeasureUnitTypeValues.Emu: result = (double)lWidth; break; case SLMeasureUnitTypeValues.Inch: result = SLConvert.FromEmuToInch((double)lWidth); break; case SLMeasureUnitTypeValues.Point: result = SLConvert.FromEmuToPoint((double)lWidth); break; } 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>