C# 对Excel的样式操作-白红宇

C# 对Excel的样式操作
阅读量：7285 次
发布时间：2019-06-30
本文共 8399 字，大约阅读时间需要 27 分钟。
///    /// 单元格背景色及填充方式   /// 
   /// 起始行   /// 起始列   /// 结束行   /// 结束列   /// 颜色索引   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;   }   ///    /// 单元格背景色及填充方式   /// 
   /// 起始行   /// 起始列   /// 结束行   /// 结束列   /// 颜色索引   /// 填充方式   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;   }   ///    /// 设置行高   /// 
   /// 起始行   /// 结束行   /// 行高   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;   }   ///    /// 自动调整行高   /// 
   /// 列号   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();   }   ///    /// 设置列宽   /// 
   /// 起始列(列对应的字母)   /// 结束列(列对应的字母)   ///    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;   }   ///    /// 设置列宽   /// 
   /// 起始列   /// 结束列   ///    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;   }   ///    /// 自动调整列宽   /// 
   /// 列号   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();   }           ///    /// 自动调整列宽   /// 
   /// 列号   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();                }   ///    /// 字体颜色   /// 
   /// 起始行   /// 起始列   /// 结束行   /// 结束列   /// 颜色索引   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;   }   ///    /// 字体样式(加粗,斜体,下划线)   /// 
   /// 起始行   /// 起始列   /// 结束行   /// 结束列   /// 是否加粗   /// 是否斜体   /// 下划线类型   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;   }   ///    /// 单元格字体及大小   /// 
   /// 起始行   /// 起始列   /// 结束行   /// 结束列   /// 字体名称   /// 字体大小   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;   }   ///    /// 打开一个存在的Excel文件   /// 
   /// Excel完整路径加文件名   public void Open(string fileName)   {    myExcel = new Excel.Application();    myWorkBook = myExcel.Workbooks.Add(fileName);    myFileName = fileName;   }   ///    /// 保存Excel   /// 
   /// 
     
      保存成功返回True
        public bool Save()   {    if (myFileName == "")    {     return false;    }    else    {     try     {      myWorkBook.Save();      return true;     }     catch (Exception ex)     {      return false;     }    }   }   ///    /// Excel文档另存为   /// 
   /// 保存完整路径加文件名   /// 
     
      保存成功返回True
        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;    }   }   ///    /// 关闭Excel   /// 
   public void Close()   {    myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);    myExcel.Quit();    myWorkBook = null;    myExcel = null;    GC.Collect();   }   ///    /// 关闭Excel   /// 
   /// 是否保存   public void Close(bool isSave)   {    myWorkBook.Close(isSave, Type.Missing, Type.Missing);    myExcel.Quit();    myWorkBook = null;    myExcel = null;    GC.Collect();   }   ///    /// 关闭Excel   /// 
   /// 是否保存   /// 存储文件名   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}/// /// 水平对齐方式/// 
public enum ExcelHAlign{   常规 = 1,   靠左,   居中,   靠右,   填充,   两端对齐,   跨列居中,   分散对齐}/// /// 垂直对齐方式/// 
public enum ExcelVAlign{   靠上 = 1,   居中,   靠下,   两端对齐,   分散对齐}/// /// 线粗/// 
public enum BorderWeight{   极细 = 1,   细 = 2,   粗 = -4138,   极粗 = 4}/// /// 线样式/// 
public enum LineStyle{   连续直线 = 1,   短线 = -4115,   线点相间 = 4,   短线间两点 = 5,   点 = -4118,   双线 = -4119,   无 = -4142,   少量倾斜点 = 13}/// /// 下划线方式/// 
public enum UnderlineStyle{   无下划线 = -4142,   双线 = - 4119,   双线充满全格 = 5,   单线 = 2,   单线充满全格 = 4}/// /// 单元格填充方式/// 
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}/// /// 常用颜色定义,对就Excel中颜色名/// 
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}}