C#操作excel.docx
《C#操作excel.docx》由会员分享,可在线阅读,更多相关《C#操作excel.docx(35页珍藏版)》请在冰点文库上搜索。
C#操作excel
C#操作Excel电子表格
添加引用:
MicrosoftExcel11.0ObjectLibrary;
添加:
usingMicrosoft.Office.Interop.Excel;
★打开Excel文件============================
Microsoft.Office.Interop.Excel.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=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.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);
worksheet1.Activate();//激活sheet1表
excel1.Visible=true;
★新建Excel表============================
Microsoft.Office.Interop.Excel.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
Worksheetworksheet1=(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheet[1],1,Type.Missing);
excel1.Visible=true;
★保存Excel==============================
Microsoft.Office.Interop.Excel.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(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.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=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.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"或1];
worksheet1.Name="工作计划表";
excel1.Visible=true;
★设置或修改Excel表单元格内容========================
Microsoft.Office.Interop.Excel.Applicationexcel1=new Microsoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Cells[1,1]="姓名";
worksheet1.Cells[1,2]="性别";
excel1.Visible=true;
★设置Excel表行宽和列高===========================
Microsoft.Office.Interop.Excel.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Columns.ColumnWidth=20;//全局行宽
worksheet1.Columns.RowHeight=20;//全局列高
Rangerange1=(Range)worksheet1.Cells[2,1];
range1.Columns.ColumnWidth=40;//单元格行宽
range1.Columns.RowHeight=40;//单元格列高
excel1.Visible=true;
★设置Excel表单元格边框===========================
Microsoft.Office.Interop.Excel.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
Rangerange1=(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.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
Rangerange1=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.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
Rangerange1=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.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
Worksheetworksheet1=(Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Cells[1,1]="123";
worksheet1.Copy(Type.Missing,worksheet1);
Worksheetworksheet2=(Worksheet)worksheet1.Next;
//worksheet2.Name="Sheet2";
excel1.Visible=true;
★页面设置============================
Microsoft.Office.Interop.Excel.Applicationexcel1=newMicrosoft.Office.Interop.Excel.Application();
Workbookworkbook1=excel1.Workbooks.Add(true);
excel1.Caption="我的报表";
Worksheetworksheet1=(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);//上边距为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#导出Excel总结
2007-11-0610:
5418934人阅读评论(9)收藏举报
一、中导出Execl的方法:
在中导出Execl有两种方法,一种是将导出的文件存放在服务器某个文件夹下面,然后将文件地址输出在浏览器上;一种是将文件直接将文件输出流写给浏览器。
在Response输出时,/t分隔的数据,导出execl时,等价于分列,/n等价于换行。
1、将整个html全部输出execl
此法将html中所有的内容,如按钮,表格,图片等全部输出到Execl中。
Response.Clear();
Response.Buffer= true;
Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls");
Response.ContentEncoding=System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;
这里我们利用了ContentType属性,它默认的属性为text/html,这时将输出为超文本,即我们常见的网页格式到客户端,如果改为ms-excel将将输出excel格式,也就是说以电子表格的格式输出到客户端,这时浏览器将提示你下载保存。
ContentType的属性还包括:
image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword。
同理,我们也可以输出(导出)图片、word文档等。
下面的方法,也均用了这个属性。
2、将DataGrid控件中的数据导出Execl
上述方法虽然实现了导出的功能,但同时把按钮、分页框等html中的所有输出信息导了进去。
而我们一般要导出的是数据,DataGrid控件上的数据。
System.Web.UI.Controlctl=this.DataGrid1;
//DataGrid1是你在窗体中拖放的控件
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
HttpContext.Current.Response.Charset="UTF-8";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType="application/ms-excel";
ctl.Page.EnableViewState=false;
System.IO.StringWriter tw=newSystem.IO.StringWriter();
System.Web.UI.HtmlTextWriterhw=newSystem.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Curre