导出excel并设置格式通用方法.docx

.net 导出excel并设置格式 2010-12asp教程.net 导出excel并设置格式添加引用：microsoft excel 11.0 object library ;添加：using erop.excel;一、打开excel文件 erop.excel.application excel1 = new erop.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对象 erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(xlwbatemplate.xlwbatworksheet或true); worksheet1.activate();/激活sheet1表 excel1.visible = true;三、新建excel表erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; worksheet worksheet1 =(worksheet)workbook1.worksheets.add(type.missing,workbook1.worksheets1, 1, type.missing); excel1.visible = true;四、重命名excel表名 erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1或1; = 工作计划表; excel1.visible = true;五、设置或修改excel表单元格内容 erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; worksheet1.cells1, 1 = 姓名; worksheet1.cells1, 2 = 性别; excel1.visible = true;六、设置excel表行宽和列高 erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; worksheet1.columns.columnwidth = 20;/全局行宽 worksheet1.columns.rowheight = 20;/全局列高 range range1 = (range) worksheet1.cells2, 1; range1.columns.columnwidth = 40;/单元格行宽 range1.columns.rowheight = 40;/单元格列高 excel1.visible = true;七、设置excel表单元格边框 erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; range range1 = (range)worksheet1.cells2, 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表块操作erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; range range1 = worksheet1.get_range(a2, e8);/选择操作块 range1.font.bold = true;/设置黑体 range1.font.size = 18;/设置字体大小 = 仿宋;/设置字体 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表单元格合并erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; 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表 erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; worksheet1.cells1, 1 = 123; worksheet1.copy(type.missing, worksheet1); worksheet worksheet2 =(worksheet)worksheet1.next; / = sheet2; excel1.visible = true;十一、页面设置erop.excel.application excel1 = new erop.excel.application(); workbook workbook1 = excel1.workbooks.add(true); excel1.caption = 我的报表; worksheet worksheet1 = (worksheet)workbook1.worksheetssheet1; worksheet1.pagesetup.papersize = xlpapersize.xlpapera3;/纸张大小 worksheet1.pagesetup.printtitlerows = $1:$3;/顶端标题行 worksheet1.pagesetup.orientation = xlpageorientation.xllandscape;/页面方向为横向 worksheet1.pagesetup.topmargin =