简单实用的excel编程代码

private _workbook _workbook = null;
private worksheet _worksheet = null;
private excel.application _excelapplicatin = null;

_excelapplicatin = new excel.application();
_excelapplicatin.visible = true;
_excelapplicatin.displayalerts = true;

_workbook = _excelapplicatin.workbooks.add(xlsheettype.xlworksheet);
_worksheet = (worksheet)_workbook.activesheet;
_worksheet.name = "worksheetname";

//打开已存在的excel
            string strexcelpathname = appdomain.currentdomain.basedirectory + "excelsheetname.xls";
            excel.workbook workbook = application.workbooks.open(strexcelpathname, type.missing, type.missing,
              type.missing, type.missing, type.missing, type.missing, type.missing,
              type.missing, type.missing, type.missing, type.missing, type.missing);
           //读取已打开的excel
            excel.worksheet worksheet1 = (excel.worksheet)workbook.sheets["sheetname1"];
            excel.worksheet worksheet2 = (excel.worksheet)workbook.sheets["sheetname2"];       

            //添加一个worksheet
            worksheet worksheet = (worksheet)workbook.worksheets.add(system.type.missing, system.type.missing, system.type.missing, system.type.missing);

//rowheight   "1:1"表示第一行, "1:2"表示,第一行和第二行
((excel.range)_worksheet.rows["1:1", system.type.missing]).row;

//columnwidth "a:b"表示第一列和第二列, "a:a"表示第一列
((excel.range)_worksheet.columns["a:b", system.type.missing]).column;

// excel操作(需要冻结的字段 按住alt+w 再按f)
            excel.range excelrange = _worksheet .get_range(_worksheet .cells[10, 5], _worksheet .cells[10, 5]);
            excelrange.select();
            excelapplication.activewindow.freezepanes = true;

//borders.linestyle 单元格边框线
excel.range excelrange = _worksheet.get_range(_worksheet.cells[2, 2], _worksheet.cells[4, 6]);
//单元格边框线类型(线型,虚线型)
excelrange.borders.linestyle = 1;
excelrange.borders.get_item(xlbordersindex.xledgetop).linestyle = excel.xllinestyle.xlcontinuous;
//指定单元格下边框线粗细,和色彩
excelrange.borders.get_item(xlbordersindex.xledgebottom).weight = excel.xlborderweight.xlmedium;

excelrange.borders.get_item(xlbordersindex.xledgebottom).colorindex =3;

//设置字体大小
excelrange.font.size = 15;
//设置字体是否有下划线
excelrange.font.underline = true; 

//设置字体在单元格内的对其方式
excelrange.horizontalalignment = xlhalign.xlhaligncenter;
//设置单元格的宽度
excelrange.column;
//设置单元格的背景色
excelrange.cells.interior.color = system.drawing.color.fromargb(255, 204, 153).toargb();
// 给单元格加边框
excelrange.borderaround(xllinestyle.xlcontinuous, xlborderweight.xlthick,
                                          xlcolorindex.xlcolorindexautomatic, system.drawing.color.black.toargb());
//自动调整列宽
excelrange.entirecolumn.autofit();
// 文本水平居中方式
excelrange.horizontalalignment = excel.xlhalign.xlhaligncenter;           
//文本自动换行
excelrange.wraptext = true;
//填充颜色为淡紫色
excelrange.interior.colorindex = 39;

//合并单元格
excelrange.merge(excelrange.mergecells);
_worksheet.get_range("a15", "b15").merge(_worksheet.get_range("a15", "b15").mergecells);

///

/// 常用颜色定义,对就excel中颜色名
///

public enum colorindex
{
   无色 = -4142,   自动 = -4105,   黑色 = 1,   褐色 = 53,   橄榄 = 52,   深绿 = 51,   深青 = 49,
   深蓝 = 11,   靛蓝 = 55,   灰色80 = 56,   深红 = 9,   橙色 = 46,   深黄 = 12,   绿色 = 10,
   青色 = 14,   蓝色 = 5,   蓝灰 = 47,   灰色50 = 16,   红色 = 3,   浅橙色 = 45,   酸橙色 = 43,
   海绿 = 50,   水绿色 = 42,   浅蓝 = 41,       紫罗兰 = 13,   灰色40 = 48,   粉红 = 7,
   金色 = 44,   黄色 = 6,   鲜绿 = 4,   青绿 = 8,   天蓝 = 33,   梅红 = 54,   灰色25 = 15,
   玫瑰红 = 38,   茶色 = 40,   浅黄 = 36,   浅绿 = 35,   浅青绿 = 34,   淡蓝 = 37,   淡紫 = 39,
   白色 = 2
}

 

1.range.numberformatlocal = "@";     //设置单元格格式为文本  
2. 
3.range = (range)worksheet.get_range("a1", "e1");     //获取excel多个单元格区域：本例做为excel表头  
4. 
5.range.merge(0);     //单元格合并动作  
6. 
7.worksheet.cells[1, 1] = "excel单元格赋值";     //excel单元格赋值  
8. 
9.range.font.size = 15;     //设置字体大小  
10. 
11.range.font.underline=true;     //设置字体是否有下划线  
12. 
13.range.font.name="黑体";       设置字体的种类  
14. 
15.range.horizontalalignment=xlhalign.xlhaligncenter;     //设置字体在单元格内的对其方式  
16. 
17.range.column;     //设置单元格的宽度  
18. 
19.range.cells.interior.color=system.drawing.color.fromargb(255,204,153).toargb();     //设置单元格的背景色  
20. 
21.range.borders.linestyle=1;     //设置单元格边框的粗细  
22. 
23.range.borderaround(xllinestyle.xlcontinuous,xlborderweight.xlthick,xlcolorindex.xlcolorindexautomatic,system.drawing.color.black.toargb());     //给单元格加边框  
24. 
25.range.borders.get_item(microsoft.office.interop.excel.xlbordersindex.xledgetop).linestyle = microsoft.office.interop.excel.xllinestyle.xllinestylenone; //设置单元格上边框为无边框  
26. 
27.    range.entirecolumn.autofit();     //自动调整列宽  
28. 
29.range.horizontalalignment= xlcenter;     // 文本水平居中方式  
30. 
31.range.verticalalignment= xlcenter     //文本垂直居中方式  
32. 
33.range.wraptext=true;     //文本自动换行  
34. 
35.range.interior.colorindex=39;     //填充颜色为淡紫色  
36. 
37.range.font.color=clblue;     //字体颜色  
38. 
39.xlsapp.displayalerts=false;     //保存excel的时候，不弹出是否保存的窗口直接进行保存