c#如何操作excel文件、Interior.ColorIndex 色彩列表
创建、打开、读取、写入、保存的一般性代码： using System; using System.Reflection; // 引用这个才能使用Missing字段 namespace CExcel1 { class Class1 { [STAThread] static void Main(string[] args) { //创建Application对象 Excel.Application xApp=new Excel.ApplicationClass(); xApp.Visible=true; //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件 Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls", Missing.Value,Missing.Value,Missing.Value,Missing.Value ,Missing.Value,Missing.Value,Missing.Value,Missing.Value ,Missing.Value,Missing.Value,Missing.Value,Missing.Value); //xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码 //指定要操作的Sheet，两种方式： Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1]; //Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet; //读取数据，通过Range对象 Excel.Range rng1=xSheet.get_Range("A1",Type.Missing); Console.WriteLine(rng1.Value2); //读取，通过Range对象，但使用不同的接口得到Range Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1]; Console.WriteLine(rng2.Value2); //写入数据 Excel.Range rng3=xSheet.get_Range("C6",Missing.Value); rng3.Value2="Hello"; rng3.Interior.ColorIndex=6; //设置Range的背景色 //保存方式一：保存WorkBook xBook.SaveAs(@"D:\CData.xls", Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value, Missing.Value,Missing.Value); //保存方式二：保存WorkSheet xSheet.SaveAs(@"D:\CData2.xls", Missing.Value,Missing.Value,Missing.Value,Missing.Value, Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value); //保存方式三 xBook.Save(); xSheet=null; xBook=null; xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 xApp=null; } } }
Interior.ColorIndex 色彩列表
|
具体的样式颜色操作
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 } }
