.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 = "工作计划表";  历史老照片不能说的秘密慈禧军阀明末清初文革晚清                    .net 导出excel并设置格式                         2010-12   2 / 5    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);                      .net 导出excel并设置格式                         2010-12   3 / 5    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;                      .net 导出excel并设置格式                         2010-12   4 / 5    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;