学步园

一、准备工作

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宏的使用很有用，有问题的时候参照宏代码，能找到灵感