VS操作Excel文档

 

1、打开一个Excel模板文档

Excel.Application app = new Excel.Application();

app.Visible = false;

app.UserControl = true;

Workbooks workbooks = app.Workbooks;

_Workbook workbook = workbooks.Add(template_path + "//文档.xls");

2、获取sheet表

Sheets sheets = workbook.Worksheets;

_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//读第一个sheet

_Worksheet worksheet2 = (_Worksheet)sheets.get_Item(2);//读第二个sheet

 

 

 

3、设置连续单元格边框颜色

worksheet.get_Range(worksheet.Cells[i,1],worksheet.Cells[i, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//表示第i行的第1列到14列的边框颜色为黑色，只需给定开始单元格和结束单元格

4、设置行高

excelOperate.SetRowHeight(worksheet, worksheet.Cells[i,1],worksheet.Cells[i, 14], 24.75);//行高设定为24.75

5、设置连续单元格的背景颜色

excelOperate.SetBgColor(worksheet, worksheet.Cells[i,1],worksheet.Cells[i,14], System.Drawing.Color.Silver);//设置背景颜色为银白色

6、设置连续单元格的字体大小

excelOperate.SetFontSize(worksheet, worksheet.Cells[i,1],worksheet.Cells[i,14],16);//字体设为16号大小

7、设置单元格字体

excelOperate.SetBold(worksheet, worksheet.Cells[i,1],worksheet.Cells[i,14]);//黑体字

8、设置单元格字体颜色

excelOperate.SetColor(worksheet, worksheet.Cells[i,1],worksheet.Cells[i,14], System.Drawing.Color.Red); //体字颜色设为红色

9、设置单元格内容居中显示

excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[i,1],worksheet.Cells[i,14]);

10、合并单元格

worksheet.get_Range(worksheet.Cells[i,1],worksheet.Cells[i,14]).Merge(Missing.Value);//合并第i行1至14列的单元格     

worksheet.get_Range (worksheet.Cells[i,1],worksheet.Cells[i,1]).Value2 = "XXXX";//给合并后的单元格赋值，合并后的单元格为第i行的第一个单元格

11、保存EXCEL文件

workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//save_path为保存路径

excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程