c#操作excel文件整理

一、准备工作

ExcelOperation 的摘要说明。 操作excel需要借助Excel.dll文件，生成Excel.dll文件的方法如下： 1.将office安装目录下的Excel.exe文件拷贝到 C:/Program Files/Microsoft Visual Studio .NET 2003/SDK/v1.1/Bin下 2.然后打开命令窗口 运行 TlbImp.exe Excel.exe Excel.dll命令即可 3.在项目中添加Excel。dll引用

System.Reflection.Missing miss = System.Reflection.Missing.Value; Excel.Application excel = null; Excel.Range rng = null; Excel.Worksheet sheet = null; Excel.Workbook book = null;

二，打开excel文件 excel = new Excel.ApplicationClass(); //打开工作薄 book = excel.Application.Workbooks.Open(sourcePath,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss,     miss); //获取工作表 sheet = (Excel.Worksheet)book.Sheets[sheetIndex]; sheet = (Excel.Worksheet)book.ActiveSheet;

//给excel单元格赋值 sheet.Cells[rowIndex,columnIndex] = cellValue; sheet.Cells["A:A",miss] = cellValue; //给A列赋值

rng = sheet.get_Range("C6","D5"); //获取单元格区域 rng.Merge(miss);//合并单元格 //合并单元格 UnMerge() 取消合并 rng.VerticalAlignment = Excel.XlHAlign .xlHAlignCenter; //设置对齐方式 rng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; rng.Value2="Hello"; //合并单元格赋值 rng.Borders[Excel.XlBordersIndex .xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick; //设置边框粗细

//设置页边距(距页面上和下各1厘米,数值的获取可通过excel工具中的宏工具来获取,点击录制宏,然后操作excel,停止录制,查看宏代码即可) sheet.PageSetup.TopMargin = excel.InchesToPoints(0.59110236255118); sheet.PageSetup.BottomMargin = excel.InchesToPoints(0.59110236255118);

//设置单元格宽度 ((Excel.Range)sheet.Columns["A:A",miss]).ColumnWidth = 20;

//设置行高度 rng = sheet.get_Range("A6","L6"); rng.WrapText = true; //设置行文字自动换行 rng.RowHeight = 20; //设置行高

// 添加工作薄并命名 // sheetNum为当前工作表的索引,excel文件默认有三张工作表,如果保存数据所需要的工作表少于三张就不需要添加, // 否则就添加,sheetNum就是用来记录当前使用的工作表的数量的 if (sheetNum > book .Sheets .Count) {     string currentSheet = ((Excel.Worksheet)book.Sheets[foldNum-1]).Name; //获取前一个工作表的名字     Excel.Worksheet excelWorksheet = (Excel.Worksheet)book.Sheets.get_Item(currentSheet);     sheet = (Excel.Worksheet)book.Worksheets.Add(excelWorksheet, miss, miss, miss);   //添加工作表     sheet.Name = nextFolder.Name; //给工作表命名 } else {     sheet = (Excel.Worksheet)book.Sheets[sheetNum];     ((Excel.Worksheet)book.Sheets[foldNum]).Name = nextFolder.Name; //设置工作表的名字 }

//彻底kill掉excel进程 try {  String directory = ConfigurationSettings.AppSettings["RootDirectory"].ToString();  Excel.Application excel = new Excel.Application();  excel.Application.Workbooks.Add(true);  excel.Visible = false; //操作excel的过程中不显示excel,true则显示  book = excel.Application.Workbooks.Open(sourceExcel,      miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);

 //这是遍历文件夹生成文件夹中文件目录的代码,省略  Program c = new Program();  c.DisplayFolderList(directory);

 book.SaveAs(sourceExcel, miss, miss, miss, miss, miss,      Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);  book.Close(true, miss, miss);  excel.Application.Workbooks.Close();  excel.Application.Quit();  excel.Quit();  System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);  System.Runtime.InteropServices.Marshal.ReleaseComObject(book);  System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); } catch (Exception ee) {  Console.WriteLine(ee.Message); } finally {  foreach (System.Diagnostics.Process pros in System.Diagnostics.Process.GetProcessesByName("EXCEL"))  {      pros.Kill();  } }

// 程序连接操作excel(两个重要参数的使用) string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + opnFileName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";

HDR=Yes;" indicates that the first row contains columnnames, not data "IMEX=1;" tells the driver to always read "intermixed" data columns as text

ok，希望能对大家有用，另外excel宏的使用很有用，有问题的时候参照宏代码，能找到灵感