C# excel操作

开源的Excel操作项目：

http://www.cnblogs.com/lwme/archive/2011/11/27/2265323.html

 

添加引用：Microsoft Excel 11.0 Object Library ;
添加：using Microsoft.Office.Interop.Excel;
打开Excel文件＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Open(@"E:\aaa.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel1.Visible = true;

新建Excel对象＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);
worksheet1.Activate();//激活sheet1表
excel1.Visible = true;

新建Excel表＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
Worksheet worksheet1 =(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheet[1], 1, Type.Missing);
excel1.Visible = true;

保存Excel＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet1 = (Worksheet)workbook1.Worksheets.Add(Type.Missing, workbook1.Worksheets[1], 1, Type.Missing);
worksheet1.Activate();
worksheet1.Cells[2, 2] = 3455555;
excel1.Visible = true;
excel1.DisplayAlerts = false;//不显示提示框
workbook1.Close(true, "d:\\1.xls", null);
//关闭
worksheet1 = null;
workbook1 = null;
excel1.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
System.GC.Collect();

 

关闭Excel＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Open(@"E:\aaa.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel1.Visible = true;
worksheet1 = null;
workbook1 = null;
excel1.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
System.GC.Collect();

 

重命名Excel表名＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"或1];
worksheet1.Name = "工作计划表";
excel1.Visible = true;

 

设置或修改Excel表单元格内容＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new　Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Cells[1, 1] = "姓名";
worksheet1.Cells[1, 2] = "性别";
excel1.Visible = true;

 

 

设置Excel表行宽和列高＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Columns.ColumnWidth = 20;//全局行宽
worksheet1.Columns.RowHeight = 20;//全局列高
Range range1 = (Range) worksheet1.Cells[2, 1];
range1.Columns.ColumnWidth = 40;//单元格行宽
range1.Columns.RowHeight = 40;//单元格列高
excel1.Visible = true;

 

 

设置Excel表单元格边框＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
Range range1 = (Range)worksheet1.Cells[2, 2];
range1.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
//也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);
range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle = XlLineStyle.xlContinuous;//斜杠
range1.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle = XlLineStyle.xlContinuous;//反斜杠
range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).Color = System.Drawing.ColorTranslator.ToOle(Color.Gold);
excel1.Visible = true;

 

Excel表块操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
Range range1 = worksheet1.get_Range("A2", "E8");//选择操作块
range1.Font.Bold = true;//设置黑体
range1.Font.Size = 18;//设置字体大小
range1.Font.Name = "仿宋";//设置字体
range1.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Blue);//设置字体颜色
range1.HorizontalAlignment = XlHAlign.xlHAlignCenter;//设置水平对齐方式
range1.VerticalAlignment = XlVAlign.xlVAlignCenter;//设置垂直对齐方式
range1.Value2 = "123\r\n456";
range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
//也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);
range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//块内竖线
range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//块内横线
excel1.Visible = true;

 

Excel表单元格合并＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
Range range1 = worksheet1.get_Range("A2", "E8");//选择操作块
range1.Value2 = "123\r\n456";
excel1.Application.DisplayAlerts = false;//使合并操作不提示警告信息
range1.Merge(false);//参数为True则为每一行合并为一个单元格
excel1.Application.DisplayAlerts = true;
excel1.Visible = true;

 

复制Excel表＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Cells[1, 1] = "123";
worksheet1.Copy(Type.Missing, worksheet1);
Worksheet worksheet2 =(Worksheet)worksheet1.Next;
//worksheet2.Name = "Sheet2";
excel1.Visible = true;

页面设置＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook1 = excel1.Workbooks.Add(true);
excel1.Caption = "我的报表";
Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;//纸张大小
worksheet1.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
worksheet1.PageSetup.Orientation = XlPageOrientation.xlLandscape;//页面方向为横向
worksheet1.PageSetup.TopMargin = excel1.CentimetersToPoints(2);//上边距为２厘米(厘米转像素)
worksheet1.PageSetup.BottomMargin = excel1.CentimetersToPoints(2);//下边距为2厘米(厘米转像素)
worksheet1.PageSetup.LeftMargin = excel1.CentimetersToPoints(1.5);//左边距为1.5厘米(厘米转像素)
worksheet1.PageSetup.RightMargin = excel1.CentimetersToPoints(1.5);//右边距为1.5厘米(厘米转像素)
worksheet1.PageSetup.HeaderMargin = excel1.CentimetersToPoints(1.2);//页眉边距为1.2厘米(厘米转像素)
worksheet1.PageSetup.FooterMargin = excel1.CentimetersToPoints(1);//页脚边距为1厘米(厘米转像素)
worksheet1.PageSetup.CenterHorizontally = true;//页面水平居中
worksheet1.PageSetup.CenterVertically = false;//页面不垂直居中
worksheet1.PageSetup.CenterFooter = "第&P页，共&N页";//中间页脚内容
excel1.Visible = true;

C#将DateTable表数据导出到Excel中 
在Visual C＃中调用Excel表格，并不像读取Excel表格中的数据那么容易了，因为在Visual C＃中调用Excel表格要使用到Excel的COM组件。以VS2005为例，首先添加引用－－在COM选项中，添加Microsfot Excel 11.0 Objet Library。
然后在程序中引入命名空间，Using Microsoft.Office.Interop.Excel;
以下面几条语句来介绍一个Excel类中的几个对象。
//实例化一个Excel应用程序对象
Microsoft.Office.Interop.Excel.Application myexcel = new Microsoft.Office.Interop.Excel.Application();
//添加工作表
myexcel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myworksheet = (Microsoft.Office.Interop.Excel.Worksheet) myexcel.Worksheets["Sheet1"];
//定义一个区域范围
Microsoft.Office.Interop.Excel.Range myrange = myexcel.get_Range(myexcel.Cells[1, 1], myexcel.Cells[3, 3]);
//显示这个excel程序
myexcel.Visible = true ;

但此时的Excel表格是一个空的表格，没有任何内容，下面就来介绍如何往Excel表格中输入数据。
（3）.往Excel表格中输入数据：
在命名空间"Excel"中，还定义了一个类"Cell"，这个类所代表的就是Excel表格中的一个下单元。通过给差"Cell"赋值，从而实现往Excel表格中输入相应的数据，下列代码功能是打开Excel表格，并且往表格输入一些数据。
myexcel.Caption = " 　　花名册";
myworksheet .Cells[1, 1] = "　　花名册";
myworksheet .Cells[2, 1] = "姓名";
（4）. Visual C＃调用Excel表格，
了解了上面的这些知识，得到完成上述功能的程序代码就显得比较容易了，函数具体如下：
Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
myexcel.Caption = " 　　花名册";
myworksheet .Cells[1, 1] = "　　花名册";
myworksheet .Cells[2, 1] = "姓名";
myworksheet .Cells[2, 2] = "性别";
myworksheet .Cells[2, 3] = "出生年月";

//合并单元格(第一行的第一列至第３列)
myworksheet .get_Range(myworksheet .Cells[1, 1], myworksheet .Cells[1, 3]).MergeCells = true;

//逐行写入数据，dt为DataTable对象,从第三行开始写数据。
int i=3;
foreach(DataRow row in dt.Rows)
{
myworksheet .Cells[i, 1] = row["姓名"].ToString();
myworksheet .Cells[i, 2] = row["性别"].ToString();
myworksheet .Cells[i, 3] = row["出生年月"].ToString();
i++;
}