asp.net 导出Excel并设置格式

asp教程.net 导出excel并设置格式
添加引用：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.worksheets[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"或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.column;//全局行宽

  worksheet1.columns.row;//全局列高

  range range1 = (range) worksheet1.cells[2, 1];

  range1.columns.column;//单元格行宽

  range1.columns.row;//单元格列高

  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 = "123rn456";

  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 = "123rn456";

  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;