创建、打开、读取、写入、保存的一般性代码： 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的样式操作 ////// 单元格背景色及填充方式 /// /// 起始行 /// 起始列 /// 结束行 /// 结束列 /// 颜色索引 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}}