c#控制Excel样式

来源：互联网发布：mac invalid argument 编辑：程序博客网时间：2024/04/25 20:25

文章是转的，我也没试过，看了下应该好使，为了下次好找，所以转下

C#对Excel的样式操作

/// <summary>
   /// 单元格背景色及填充方式
   /// </summary>
   /// <param name="startRow">起始行</param>
   /// <param name="startColumn">起始列</param>
   /// <param name="endRow">结束行</param>
   /// <param name="endColumn">结束列</param>
   /// <param name="color">颜色索引</param>
   public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
   {
    Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
    range.Interior.ColorIndex = color;
    range.Interior.Pattern = Pattern.Solid;
   }

   /// <summary>
   /// 单元格背景色及填充方式
   /// </summary>
   /// <param name="startRow">起始行</param>
   /// <param name="startColumn">起始列</param>
   /// <param name="endRow">结束行</param>
   /// <param name="endColumn">结束列</param>
   /// <param name="color">颜色索引</param>
   /// <param name="pattern">填充方式</param>
   public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern)
   {
    Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
    range.Interior.ColorIndex = color;
    range.Interior.Pattern = pattern;
   }

   /// <summary>
   /// 设置行高
   /// </summary>
   /// <param name="startRow">起始行</param>
   /// <param name="endRow">结束行</param>
   /// <param name="height">行高</param>
   public void SetRowHeight(int startRow, int endRow, int height)
   {
    //获取当前正在使用的工作表
    Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
    Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
    range.RowHeight = height;
   }

   /// <summary>
   /// 自动调整行高
   /// </summary>
   /// <param name="columnNum">列号</param>
   public void RowAutoFit(int rowNum)
   {
    //获取当前正在使用的工作表
    Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
    Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
    range.EntireColumn.AutoFit();

}

   /// <summary>
   /// 设置列宽
   /// </summary>
   /// <param name="startColumn">起始列(列对应的字母)</param>
   /// <param name="endColumn">结束列(列对应的字母)</param>
   /// <param name="width"></param>
   public void SetColumnWidth(string startColumn, string endColumn, int width)
   {
    //获取当前正在使用的工作表
    Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
    Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
    range.ColumnWidth = width;
   }

   /// <summary>
   /// 设置列宽
   /// </summary>
   /// <param name="startColumn">起始列</param>
   /// <param name="endColumn">结束列</param>
   /// <param name="width"></param>
   public void SetColumnWidth(int startColumn, int endColumn, int width)
   {
    string strStartColumn = GetColumnName(startColumn);
    string strEndColumn = GetColumnName(endColumn);
    //获取当前正在使用的工作表
    Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
    Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
    range.ColumnWidth = width;
   }

   /// <summary>
   /// 自动调整列宽
   /// </summary>
   /// <param name="columnNum">列号</param>
   public void ColumnAutoFit(string column)
   {
    //获取当前正在使用的工作表
    Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
    Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
    range.EntireColumn.AutoFit();

   }

   /// <summary>
   /// 自动调整列宽
   /// </summary>
   /// <param name="columnNum">列号</param>
   public void ColumnAutoFit(int columnNum)
   {
    string strcolumnNum = GetColumnName(columnNum);
    //获取当前正在使用的工作表
    Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
    Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
    range.EntireColumn.AutoFit();

   }

   /// <summary>
   /// 字体颜色
   /// </summary>
   /// <param name="startRow">起始行</param>
   /// <param name="startColumn">起始列</param>
   /// <param name="endRow">结束行</param>
   /// <param name="endColumn">结束列</param>
   /// <param name="color">颜色索引</param>
   public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
   {
    Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
    range.Font.ColorIndex = color;
   }

   /// <summary>
   /// 字体样式(加粗,斜体,下划线)
   /// </summary>
   /// <param name="startRow">起始行</param>
   /// <param name="startColumn">起始列</param>
   /// <param name="endRow">结束行</param>
   /// <param name="endColumn">结束列</param>
   /// <param name="isBold">是否加粗</param>
   /// <param name="isItalic">是否斜体</param>
   /// <param name="underline">下划线类型</param>
   public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
   {
    Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
    range.Font.Bold = isBold;
    range.Font.Underline = underline;
    range.Font.Italic = isItalic;
   }

   /// <summary>
   /// 单元格字体及大小
   /// </summary>
   /// <param name="startRow">起始行</param>
   /// <param name="startColumn">起始列</param>
   /// <param name="endRow">结束行</param>
   /// <param name="endColumn">结束列</param>
   /// <param name="fontName">字体名称</param>
   /// <param name="fontSize">字体大小</param>
   public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn,string fontName, int fontSize)
   {
    Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
    range.Font.Name = fontName;
    range.Font.Size = fontSize;
   }

   /// <summary>
   /// 打开一个存在的Excel文件
   /// </summary>
   /// <param name="fileName">Excel完整路径加文件名</param>
   public void Open(string fileName)
   {
    myExcel = new Excel.Application();
    myWorkBook = myExcel.Workbooks.Add(fileName);
    myFileName = fileName;
   }

   /// <summary>
   /// 保存Excel
   /// </summary>
   /// <returns>保存成功返回True</returns>
   public bool Save()
   {
    if (myFileName == "")
    {
     return false;
    }
    else
    {
     try
     {
      myWorkBook.Save();
      return true;
     }
     catch (Exception ex)
     {
      return false;
     }
    }
   }

   /// <summary>
   /// Excel文档另存为
   /// </summary>
   /// <param name="fileName">保存完整路径加文件名</param>
   /// <returns>保存成功返回True</returns>
   public bool SaveAs(string fileName)
   {
    try
    {
     myWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
     return true;

    }
    catch (Exception ex)
    {
     return false;

}
}

   /// <summary>
   /// 关闭Excel
   /// </summary>
   public void Close()
   {
    myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
    myExcel.Quit();
    myWorkBook = null;
    myExcel = null;
    GC.Collect();
   }

   /// <summary>
   /// 关闭Excel
   /// </summary>
   /// <param name="isSave">是否保存</param>
   public void Close(bool isSave)
   {
    myWorkBook.Close(isSave, Type.Missing, Type.Missing);
    myExcel.Quit();
    myWorkBook = null;
    myExcel = null;
    GC.Collect();
   }

   /// <summary>
   /// 关闭Excel
   /// </summary>
   /// <param name="isSave">是否保存</param>
   /// <param name="fileName">存储文件名</param>
   public void Close(bool isSave,string fileName)
   {
    myWorkBook.Close(isSave, fileName, Type.Missing);
    myExcel.Quit();
    myWorkBook = null;
    myExcel = null;
    GC.Collect();
   }

   #region 私有成员
   private string GetColumnName(int number)
   {
    int h, l;
    h = number / 26;
    l = number % 26;
    if (l == 0)
    {
     h -= 1;
     l = 26;
    }
    string s = GetLetter(h) + GetLetter(l);
    return s;
   }

   private string GetLetter(int number)
   {
    switch (number)
    {
     case 1:
      return "A";
     case 2:
      return "B";
     case 3:
      return "C";
     case 4:
      return "D";
     case 5:
      return "E";
     case 6:
      return "F";
     case 7:
      return "G";
     case 8:
      return "H";
     case 9:
      return "I";
     case 10:
      return "J";
     case 11:
      return "K";
     case 12:
      return "L";
     case 13:
      return "M";
     case 14:
      return "N";
     case 15:
      return "O";
     case 16:
      return "P";
     case 17:
      return "Q";
     case 18:
      return "R";
     case 19:
      return "S";
     case 20:
      return "T";
     case 21:
      return "U";
     case 22:
      return "V";
     case 23:
      return "W";
     case 24:
      return "X";
     case 25:
      return "Y";
     case 26:
      return "Z";
     default:
      return "";
    }
   }
   #endregion

}

/// <summary>
/// 水平对齐方式
/// </summary>
public enum ExcelHAlign
{
   常规 = 1,
   靠左,
   居中,
   靠右,
   填充,
   两端对齐,
   跨列居中,
   分散对齐
}

/// <summary>
/// 垂直对齐方式
/// </summary>
public enum ExcelVAlign
{
   靠上 = 1,
   居中,
   靠下,
   两端对齐,
   分散对齐
}

/// <summary>
/// 线粗
/// </summary>
public enum BorderWeight
{
   极细 = 1,
   细 = 2,
   粗 = -4138,
   极粗 = 4
}

/// <summary>
/// 线样式
/// </summary>
public enum LineStyle
{
   连续直线 = 1,
   短线 = -4115,
   线点相间 = 4,
   短线间两点 = 5,
   点 = -4118,
   双线 = -4119,
   无 = -4142,
   少量倾斜点 = 13
}

/// <summary>
/// 下划线方式
/// </summary>
public enum UnderlineStyle
{
   无下划线 = -4142,
   双线 = - 4119,
   双线充满全格 = 5,
   单线 = 2,
   单线充满全格 = 4
}

/// <summary>
/// 单元格填充方式
/// </summary>
public enum Pattern
{
   Automatic = -4105,
   Checker = 9,
   CrissCross = 16,
   Down = -4121,
   Gray16 = 17,
   Gray25 = -4124,
   Gray50 = -4125,
   Gray75 = -4126,
   Gray8 = 18,
   Grid = 15,
   Horizontal = -4128,
   LightDown = 13,
   LightHorizontal = 11,
   LightUp = 14,
   LightVertical = 12,
   None = -4142,
   SemiGray75 = 10,
   Solid = 1,
   Up = -4162,
   Vertical = -4166
}

/// <summary>
/// 常用颜色定义,对就Excel中颜色名
/// </summary>
public enum ColorIndex
{
   无色 = -4142,
   自动 = -4105,
   黑色 = 1,
   褐色 = 53,
   橄榄 = 52,
   深绿 = 51,
   深青 = 49,
   深蓝 = 11,
   靛蓝 = 55,
   灰色80 = 56,
   深红 = 9,
   橙色 = 46,
   深黄 = 12,
   绿色 = 10,
   青色 = 14,
   蓝色 = 5,
   蓝灰 = 47,
   灰色50 = 16,
   红色 = 3,
   浅橙色 = 45,
   酸橙色 = 43,
   海绿 = 50,
   水绿色 = 42,
   浅蓝 = 41,
   紫罗兰 = 13,
   灰色40 = 48,
   粉红 = 7,
   金色 = 44,
   黄色 = 6,
   鲜绿 = 4,
   青绿 = 8,
   天蓝 = 33,
   梅红 = 54,
   灰色25 = 15,
   玫瑰红 = 38,
   茶色 = 40,
   浅黄 = 36,
   浅绿 = 35,
   浅青绿 = 34,
   淡蓝 = 37,
   淡紫 = 39,
   白色 = 2
}
}