一些Excel中间件的扩展方法

来源：互联网发布：windows笔记本怎么截图编辑：程序博客网时间：2024/04/30 07:16

/// <summary> /// 将Excel列的整数索引值转换为字符索引值 /// </summary> /// <param name="n"></param> /// <returns></returns> public string IntToLetter(int n) { if (n > 256) throw new Exception("索引超出范围，Excel的列索引不能超过256！"); int i = Convert.ToInt32(n / 26); int j = n % 26; char c1 = Convert.ToChar(i + 64); char c2 = Convert.ToChar(j + 64); if (n > 26) return c1.ToString() + c2.ToString(); else if (n == 26) return "Z"; else return c2.ToString(); }

/// <summary> /// 插列（在指定列右边插入指定数量列） /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void InsertColumns(int columnIndex,int count,Excel.Worksheet sheet) { try { Excel.Range range = (Excel.Range)sheet.Columns[Missing.Value, columnIndex]; for (int i = 0; i < count; i++) { range.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Missing.Value); } } catch (Exception e) { throw e; } }

/// <summary> /// 插行（在指定行上面插入指定数量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void InsertRows(int rowIndex,int count,Excel.Worksheet sheet) { try { Excel.Range range = (Excel.Range)sheet.Rows[rowIndex, Missing.Value]; for (int i = 0; i < count; i++) { range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Missing.Value); } } catch (Exception e) { throw e; } }

/// <summary> /// 复制行（在指定行下面复制指定数量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void CopyRows(int rowIndex,int count,Excel.Worksheet sheet) { try { Excel.Range range1 = (Excel.Range)sheet.Rows[rowIndex,Missing.Value]; for (int i = 1; i <= count; i++) { Excel.Range range2 = (Excel.Range)sheet.Rows[rowIndex + i,Missing.Value]; range1.Copy(range2); } } catch (Exception e) { throw e; } }

/// <summary> /// 复制列（在指定列右边复制指定数量列） /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void CopyColumns(int columnIndex,int rowIndex, int count,Excel.Worksheet sheet) { try { // range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing]; Excel.Range range1 = (Excel.Range)sheet.get_Range(this.IntToLetter(columnIndex) + rowIndex.ToString(), this.IntToLetter(columnIndex) + rowIndex.ToString()); for (int i = 1; i <= count; i++) { // range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i]; Excel.Range range2 = (Excel.Range)sheet.get_Range(this.IntToLetter(columnIndex + i) + rowIndex.ToString(), this.IntToLetter(columnIndex + i) + rowIndex.ToString()); range1.Copy(range2); } } catch { throw; } }