/// <summary> /// 设置整个连续区域的字体颜色 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="clrColor">颜色</param> public void SetColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor) { CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor); }
public static void CreateColumnName(DataTable dt, Microsoft.Office.Interop.Excel._Worksheet xlSheet, int pos_row) { int _ColumnsCount = dt.Columns.Count; //set thuoc tinh cho cac header Microsoft.Office.Interop.Excel.Range header = xlSheet.get_Range("A" + pos_row.ToString(), Convert.ToChar(_ColumnsCount + 65) + pos_row.ToString()); header.Select(); header.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; header.Font.Bold = true; header.Font.Size = 12; for (int i = 0; i < dt.Columns.Count; i++) xlSheet.Cells[pos_row, i + 1] = dt.Columns[i].ColumnName; }
public void setCellValue(System.Collections.ArrayList value, Microsoft.Office.Interop.Excel.Worksheet wsheet, int rowindex, int colum, int count) { if (value.Count > 0) { string[,] strs = new string[value.Count, ((ArrayList)value[0]).Count]; for (int i = 0; i < value.Count; i++) for (int j = 0; j < ((ArrayList)value[i]).Count; j++) { strs[i, j] = (((ArrayList)value[i])[j]).ToString() == "0" ? "" : (((ArrayList)value[i])[j]).ToString(); } wsheet.get_Range(wsheet.Cells[rowindex, colum], wsheet.Cells[value.Count + rowindex - 1, ((ArrayList)value[0]).Count + colum - 1]).Value2 = strs; } }
TryGetNamedRange ( Microsoft.Office.Interop.Excel.Worksheet worksheet, String rangeName, out Range namedRange ) { Debug.Assert(worksheet != null); Debug.Assert(!String.IsNullOrEmpty(rangeName)); namedRange = null; try { namedRange = worksheet.get_Range(rangeName, Missing.Value); return (true); } catch (System.Runtime.InteropServices.COMException) { return (false); } }
public void AddTableToWorksheet(DataTable data, Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet) { // Add columns int iCol = 0; foreach (DataColumn c in data.Columns) { iCol++; xlsWorksheet.Cells[1, iCol] = c.ColumnName; } // Add rows int iRow = 0; foreach (DataRow r in data.Rows) { iRow++; for (int i = 1; i < data.Columns.Count + 1; i++) { if (iRow == 1) { // Add the header the first time through xlsWorksheet.Cells[iRow, i] = data.Columns[i - 1].ColumnName; } if (r[1].ToString() != "") { xlsWorksheet.Cells[iRow + 1, i] = r[i - 1].ToString(); } } } var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var range = xlsWorksheet.get_Range("A1", last); range.Columns.AutoFit(); }
/// <summary> /// Write array to the worksheet Excel /// </summary> /// <param name="rows">The required amount of row into the worksheet </param> /// <param name="columns">The required amount of columns into the worksheet </param> /// <param name="worksheet">The worksheet in which data will be written </param> /// <param name="data"> The data to be written on the worksheet </param> private void WriteArrayRoadPavenment(int rows, int columns, Microsoft.Office.Interop.Excel.Worksheet worksheet, object[,] data) { var startRow = HEADERROWSRoadPavenment; var startCell = worksheet.Cells[startRow + 1, 1] as Microsoft.Office.Interop.Excel.Range; // At excel range start at the 1,1 not the 0,0 var endCell = worksheet.Cells[rows + startRow, columns] as Microsoft.Office.Interop.Excel.Range; var writeRange = worksheet.get_Range(startCell, endCell); writeRange.Value2 = data; }
private void spreadsheetDataSetTemplate(Microsoft.Office.Interop.Excel.Worksheet worksheet, string astrMethodName) { if(null != worksheet) { Microsoft.Office.Interop.Excel.Range rangeMethod = worksheet.get_Range("A1"); Microsoft.Office.Interop.Excel.Range rangeMethodName = worksheet.get_Range("B1"); Microsoft.Office.Interop.Excel.Range rangeErrors = worksheet.get_Range("A2"); Microsoft.Office.Interop.Excel.Range rangeStart = worksheet.get_Range("A3"); Microsoft.Office.Interop.Excel.Range rangeEnd = worksheet.get_Range("C3"); Microsoft.Office.Interop.Excel.Range rangeDuraction = worksheet.get_Range("E3"); rangeMethod.Value2 = "Method"; rangeMethod.Font.Bold = true; rangeMethodName.Value2 = astrMethodName; rangeDuraction.Value2 = "Duration"; rangeDuraction.Font.Bold = true; rangeStart.Value2 = "Start"; rangeStart.Font.Bold = true; rangeEnd.Value2 = "End"; rangeEnd.Font.Bold = true; rangeErrors.Value2 = "Errors"; rangeErrors.Font.Bold = true; } }
private int generateSpreadsheetColumnsRecursive(PropertyInfo[] apiProperties, Microsoft.Office.Interop.Excel.Worksheet awsWorksheet, int aiCurrentColumn) { int result = aiCurrentColumn; foreach (PropertyInfo currentProperty in apiProperties) { if (true == isPrimative(currentProperty.PropertyType)) { Microsoft.Office.Interop.Excel.Range rangeName = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition - 2)); Microsoft.Office.Interop.Excel.Range rangeType = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition + 1 - 2)); rangeName.Value2 = currentProperty.Name; rangeName.Font.Bold = true; rangeType.Value2 = extractHumanReadableType(currentProperty.PropertyType.ToString()); rangeType.Font.Italic = true; aiCurrentColumn++; } else { //Microsoft.Office.Interop.Excel.Range rangeName = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition - 3)); //Microsoft.Office.Interop.Excel.Range rangeType = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition - 4)); //rangeName.Value2 = currentProperty.Name; //rangeName.Font.Bold = true; //rangeType.Value2 = extractHumanReadableType(currentProperty.PropertyType.ToString()); //rangeType.Font.Italic = true; aiCurrentColumn = generateSpreadsheetColumnsRecursive(currentProperty.PropertyType.GetProperties(), awsWorksheet, aiCurrentColumn); } } result = aiCurrentColumn; return (result); }
/// <summary> /// 设置行高 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="dblHeight">行高</param> public void SetRowHeight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblHeight) { CurSheet.get_Range(objStartCell, objEndCell).RowHeight = dblHeight; }
/// <summary> /// 设置连续区域的显示格式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="strNF">如"#,##0.00"的显示格式</param> public void SetNumberFormat(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string strNF) { CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF; }
/// <summary> /// 设置连续区域水平居右 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetHAlignRight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; }
/// <summary> /// 为单元格设置公式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">单元格</param> /// <param name="strFormula">公式</param> public void SetFormula(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strFormula) { CurSheet.get_Range(objCell, mValue).Formula = strFormula; }
/// <summary> /// 设置连续区域的字体大小 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="strStartCell">开始单元格</param> /// <param name="strEndCell">结束单元格</param> /// <param name="intFontSize">字体大小</param> public void SetFontSize(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize) { CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString(); }
/// <summary> /// 设置连续区域的字体名称 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param> public void SetFontName(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname) { CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname; }
/// <summary> /// 设置列宽 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> /// <param name="dblWidth">宽度</param> public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblWidth) { CurSheet.get_Range(objStartCell, objEndCell).ColumnWidth = dblWidth; }
public static void Prc_Resize_Col(Microsoft.Office.Interop.Excel.Worksheet p_sheet, int p_row_count, int p_col_count) { Microsoft.Office.Interop.Excel.Range _range; // Resize the columns _range = p_sheet.get_Range(p_sheet.Cells[1, 1], p_sheet.Cells[p_row_count, p_col_count]); _range.EntireColumn.AutoFit(); }
private void callWebService(compiledAssembly compiledAsm, string astrMethod, List<Parameter> alsParameters, Microsoft.Office.Interop.Excel.Worksheet awsData, int aiStartRow) { CompilerResults compilerResult = compiledAsm.compilerResults as CompilerResults; int iErrorCount = 0; DateTime dtStart = DateTime.Now; DateTime dtEnd; if (null != compilerResult && false == string.IsNullOrEmpty(astrMethod)) { var allTypes = compilerResult.CompiledAssembly.GetTypes(); MethodInfo methodInfo = null; //compilerResult.GetType().GetMethod(astrMethod); foreach (var currentMethod in allTypes) { if (null != currentMethod && null != currentMethod.BaseType) { if(null != (methodInfo = currentMethod.GetMethod(astrMethod))) { break; } } } var headerInfo = allTypes.Where(t => t.Name == "lws" || t.Name == "mws").First(); object objHeader = System.Activator.CreateInstance(headerInfo); // this is the type of the final type in our heirachy where we plug in the spreadsheet values Type tpTypeToPopulate = null; // argumentType = getArgumentClassType(allTypes, astrMethod); List<Type> lsTypeHierarchy = getArgumentClassType(allTypes, astrMethod); List<object> lsCallObjects = new List<object>(); // create the object hierachy, the first object in the list should be the one // supplied to the webservice call. The last should be the one that we populate // with data from the spreadsheet createObjectHierachy(lsTypeHierarchy, lsCallObjects, 0); if(lsCallObjects.Count > 0) { tpTypeToPopulate = lsTypeHierarchy.Last(); // object objObjectToSet = lsCallObjects.Last(); object objObjectToPassToCall = lsCallObjects.First(); foreach(object objObjectToSet in lsCallObjects) { tpTypeToPopulate = objObjectToSet.GetType(); for (int i = aiStartRow; i < giMaxRowPosition; i++) { bool bBlank = true; foreach (Parameter currentParameter in alsParameters) { if (null != currentParameter) { var spreadSheetValue = awsData.get_Range(currentParameter.Value + i).Value2; if (null != spreadSheetValue) { // retrieve the value from the spreadsheet string strValue = awsData.get_Range(currentParameter.Value + i).Value2.ToString(); PropertyInfo currentProperty = tpTypeToPopulate.GetProperty(currentParameter.Name); if (null != currentProperty) { if (false == string.IsNullOrWhiteSpace(strValue)) { bBlank = false; } object objValue = strValue; if (typeof(int) == currentProperty.PropertyType) { int iValue = 0; int.TryParse(strValue, out iValue); objValue = iValue; } else if (typeof(decimal) == currentProperty.PropertyType) { decimal decValue = 0; decimal.TryParse(strValue, out decValue); objValue = decValue; } else if (typeof(double) == currentProperty.PropertyType) { double dblValue = 0; double.TryParse(strValue, out dblValue); objValue = dblValue; } else if (typeof(DateTime) == currentProperty.PropertyType) { DateTime dtValue = DateTime.MinValue; DateTime.TryParse(strValue, out dtValue); objValue = dtValue; } // save the parameter tpTypeToPopulate.InvokeMember(currentParameter.Name, BindingFlags.Instance | BindingFlags.Public | BindingFlags.SetProperty, Type.DefaultBinder, objObjectToSet, new object[] { objValue }); } else { // property doesn't exist } } } } // if we have a blank line we should stop if (true == bBlank) { break; } else { // call to the webservice here try { methodInfo.Invoke(compiledAsm.instantiatedObject, new object[] { objHeader, objObjectToPassToCall }); awsData.get_Range(gstrResultColumn + i).Value2 = "OK"; } catch (Exception ex) { awsData.get_Range(gstrResultColumn + i).Value2 = ex; iErrorCount++; } } } } dtEnd = DateTime.Now; awsData.get_Range(gstrErrorCountCell).Value2 = iErrorCount; awsData.get_Range(gstrStartTimeCell).Value2 = dtStart.ToString("yyyy-MM-dd HH:mm:ss"); awsData.get_Range(gstrEndTimeCell).Value2 = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); awsData.get_Range(gstrDurationCell).Value2 = (dtEnd - dtStart).TotalSeconds; } } }
/// <summary> /// 在指定单元格插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="Cell">单元格 如Cells[1,1]</param> /// <param name="objValue">文本、数字等值</param> public void WriteCell(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, object objValue) { CurSheet.get_Range(objCell, mValue).Value2 = objValue; }
private List<Parameter> getParameterColumns(Microsoft.Office.Interop.Excel.Worksheet awsData, int aiParameterTitleStartX, int aiParameterTitleStartY) { List<Parameter> result = null, lsParameters = new System.Collections.Generic.List<Parameter>(); int iXPos = aiParameterTitleStartX; int iYPos = aiParameterTitleStartY; for (int i = iXPos; i < giMaxColumnPosition; i++) { string strColumnLetter = GetColumnLetter(i); string strName = awsData.get_Range(strColumnLetter + (iYPos-2)).Value2; if(false == String.IsNullOrWhiteSpace(strName)) { lsParameters.Add(new Parameter() { Name = strName, Value = strColumnLetter }); } else { break; } } if (lsParameters.Count > 0) { result = lsParameters; } return (result); }
/// <summary> /// 在指定Range中插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="StartCell">开始单元格</param> /// <param name="EndCell">结束单元格</param> /// <param name="objValue">文本、数字等值</param> public void WriteRange(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue) { CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue; }
/// <summary> /// 为单元格添加超级链接 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">单元格</param> /// <param name="strAddress">链接地址</param> /// <param name="strTip">屏幕提示</param> /// <param name="strText">链接文本</param> public void AddHyperLink(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strAddress, string strTip, string strText) { CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell), strAddress, mValue, strTip, strText); }
/// <summary> /// 单元格自动换行 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void AutoWrapText(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).WrapText = true; }
private void SetTitleAndListValues(Microsoft.Office.Interop.Excel.Worksheet sheet,int row, int col) { Microsoft.Office.Interop.Excel.Range range; // Set the title. range = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, col]; // Get the values. // Find the last cell in the column. range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[col, Type.Missing]; Microsoft.Office.Interop.Excel.Range last_cell = range.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlDown); // Get a Range holding the values. Microsoft.Office.Interop.Excel.Range first_cell = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[row + 1, col]; Microsoft.Office.Interop.Excel.Range value_range = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(first_cell, last_cell); // Get the values. object[,] range_values = (object[,])value_range.Value2; // Convert this into a 1-dimensional array. // Note that the Range's array has lower bounds 1. int num_items = range_values.GetUpperBound(0); //string[] values1 = new string[num_items]; object[] values1 = new object[num_items]; for (int i = 0; i < num_items; i++) { values1[i] = (object)range_values[i + 1, 1]; } // Display the values in the ListBox. //lst.DataSource = values1; }
public static void Prc_Add_Body(Microsoft.Office.Interop.Excel.Worksheet p_sheet, System.Data.DataTable p_dt) { Microsoft.Office.Interop.Excel.Range _range; /* * Modify by ManhTV3 on 18/04/2012 * Content: Điều kiện kết xuất phụ lục * */ int iRow = 0; if (p_sheet.Name.Substring(0,6).Equals("Phuluc")) iRow = 3; /* * Modify by ManhTV3 on 1/5/2012 * Content: Điều kiện kết xuất báo cáo chuẩn hóa * */ else if (p_sheet.Name.Equals("BaoCaoChuanHoa")) iRow = 1; int iCol = 0; foreach (DataRow r in p_dt.Rows) { iRow++; // add row iCol = 0; foreach (DataColumn c in p_dt.Columns) { iCol++; p_sheet.Cells[iRow + 1, iCol] = r[c.ColumnName]; } } _range = p_sheet.get_Range(p_sheet.Cells[2, 1], p_sheet.Cells[iRow + 1, p_dt.Columns.Count]); _range.Font.Name = ".VnArial"; }
/// <summary> /// Adds a small Infobox and a Validation with restriction (only these values will be selectable) to the specified cell. /// </summary> /// <param name="worksheet">The excel-sheet</param> /// <param name="rowNr">1-based row index of the cell that will contain the validation</param> /// <param name="columnNr">1-based column index of the cell that will contain the validation</param> /// <param name="title">Title of the Infobox</param> /// <param name="message">Message in the Infobox</param> /// <param name="validationValues">List of available values for selection of the cell. No other value, than this list is allowed to be used.</param> /// <exception cref="Exception">Thrown, if an error occurs, or the worksheet was null.</exception> public void AddDataValidation(Microsoft.Office.Interop.Excel.Worksheet worksheet, Microsoft.Office.Interop.Excel.Worksheet validationWorksheet, string col, int row, string title, string message, List<string> validationValues, CultureInfo currentCulture) { if (validationValues == null || validationValues.Count == 0) return; //If the message-string is too long (more than 255 characters, prune it) if (message.Length > 255) message = message.Substring(0, 254); try { if (!validationRanges.ContainsKey(col)) { var valStart = validationRow; foreach (var val in validationValues) { validationWorksheet.Cells[validationRow, "A"] = val; validationRow++; } validationRanges.Add(col, string.Format("={0}!$A{1}:$A{2}", validationSheetName, valStart, validationRow - 1)); } #region old validation //The validation requires a ';'-separated list of values, that goes as the restrictions-parameter. //Fold the list, so you can add it as restriction. (Result is "Value1;Value2;Value3") //If you use another separation-character (e.g in US) change the ; appropriately (e.g. to the ,) //string values = ""; //if (currentCulture.TwoLetterISOLanguageName == "en") // values = string.Join(",", validationValues.ToArray()); //else // values = string.Join(";", validationValues.ToArray()); #endregion //Select the specified cell Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range(col + row, col + row); //Delete any previous validation cell.Validation.Delete(); //Add the validation, that only allowes selection of provided values. cell.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, validationRanges[col], Type.Missing); cell.Validation.IgnoreBlank = true; //Optional put a message there cell.Validation.InputTitle = title; cell.Validation.InputMessage = message; } catch (Exception exception) { //This part should not be reached, but is used for stability-reasons throw new Exception(String.Format("Error when adding a Validation with restriction to the specified cell Row:{0}, Column:{1}, Message: {2}", row, col, message), exception); } }
public static void Prc_Add_Header(Microsoft.Office.Interop.Excel.Worksheet p_sheet, System.Data.DataTable p_dt) { Microsoft.Office.Interop.Excel.Range _range; int iCol = 0; foreach (DataColumn c in p_dt.Columns) { iCol++; p_sheet.Cells[1, iCol] = c.ColumnName; } _range = p_sheet.get_Range(p_sheet.Cells[1, 1], p_sheet.Cells[1, p_dt.Columns.Count]); _range.Font.Bold = true; }
TryGetContiguousValuesInRowOrColumn ( Microsoft.Office.Interop.Excel.Worksheet worksheet, Int32 rowOneBased, Int32 columnOneBased, Boolean inRow, out Object[,] values ) { Debug.Assert(worksheet != null); Debug.Assert(rowOneBased >= 1); Debug.Assert(columnOneBased >= 1); values = null; Range oFirstCell = (Range)worksheet.Cells[rowOneBased, columnOneBased]; String sTemp; if (!ExcelUtil.TryGetNonEmptyStringFromCell(oFirstCell, out sTemp)) { return (false); } Range oNextCell = (Range)worksheet.Cells[ rowOneBased + (inRow ? 0 : 1), columnOneBased + (inRow ? 1 : 0) ]; Range oLastCell; // If the next cell is empty, Range.get_End() can't be used because it // jumps beyond the empty cell, possibly to the end of the worksheet. if (!ExcelUtil.TryGetNonEmptyStringFromCell(oNextCell, out sTemp)) { oLastCell = oFirstCell; } else { oLastCell = oFirstCell.get_End( inRow ? XlDirection.xlToRight : XlDirection.xlDown); } values = ExcelUtil.GetRangeValues( worksheet.get_Range(oFirstCell, oLastCell)); return (true); }
public static void Prc_Format_Col(Microsoft.Office.Interop.Excel.Worksheet p_sheet, int p_row_count, int p_col_count) { Microsoft.Office.Interop.Excel.Range _range; // Format Column _range = p_sheet.get_Range(p_sheet.Cells[1, 1], p_sheet.Cells[p_row_count, p_col_count]); _range.EntireColumn.NumberFormat = "@"; }
GetValuesInColumn ( Microsoft.Office.Interop.Excel.Worksheet worksheet, Int32 firstRowOneBased, Int32 columnOneBased, Int32 rows ) { Debug.Assert(worksheet != null); Debug.Assert(firstRowOneBased >= 1); Debug.Assert(columnOneBased >= 1); Debug.Assert(rows >= 1); Range oRange = (Range)worksheet.get_Range( (Range)worksheet.Cells[firstRowOneBased, columnOneBased], (Range)worksheet.Cells[firstRowOneBased + rows - 1, columnOneBased] ); return (ExcelUtil.GetRangeValues(oRange)); }
/// <summary> /// 设置连续区域的边框:上下左右都为黑色连续边框 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">开始单元格</param> /// <param name="objEndCell">结束单元格</param> public void SetBorderAll(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; }