原文地址:http://blog.sina.com.cn/s/blog_604fb7ae0100x2s7.html
中小企业办公自动化系统都需要有与微软办公软件连接的功能,如把数据导入到电子表格、Word等功能。C#.NET在Office方面提供了强大的功能,只要导入 Microsoft.Office.Interop.Excel 命名空间并调用此命名空间下的类,就可以在程序调用Excel、Word。
(一)Excel开发
首先导入 Microsoft.Office.Interop.Excel 命名空间
需要的类
_Application excel = new ApplicationClass(); //实例化对象
int rowIndex = 6;
int colIndex = 0;
_Workbook xBk;
_Worksheet xSt;
xBk = excel.Workbooks.Add(true);
xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
for (int i = 0; i < dgvYingShouAmount.Columns.Count; i++){
colIndex++;
excel.Cells[rowIndex, colIndex] = Convert.ToString(dgvYingShouAmount.Columns[i].HeaderText);
}
//取得表格中的数据
for (int i = 0; i < dgvYingShouAmount.Rows.Count; i++){
rowIndex++;
colIndex = 0;
for (int j = 0; j < dgvYingShouAmount.Columns.Count; j++){
colIndex++;
excel.Cells[rowIndex, colIndex] =Convert.ToString(dgvYingShouAmount.Rows[i].Cells[j].Value);
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
}
excel.Cells[1, 1] = "***有限公司";
excel.Cells[2, 1] = "地址";
excel.Cells[3, 1] = "电话 传真";
excel.Cells[4, 1] = "客户对账单";
excel.Cells[5, 1] = "操作日期:" + dtpStartDate.Value.ToString("yyyy-MM-dd") + "/" + dtpEndDate.Value.ToString("yyyy-MM-dd");
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true;
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 22;
xSt.get_Range(excel.Cells[3, 1], excel.Cells[3, 1]).Font.Bold = true;
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[6, 2], excel.Cells[rowIndex, colIndex]).Select();
xSt.get_Range(excel.Cells[6, 2], excel.Cells[rowIndex, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Select();
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
xSt.get_Range(excel.Cells[3, 1], excel.Cells[3, colIndex]).Select();
xSt.get_Range(excel.Cells[3, 1], excel.Cells[3, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
xSt.get_Range(excel.Cells[4, 1], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 1], excel.Cells[4, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
xSt.get_Range(excel.Cells[5, 1], excel.Cells[5, colIndex]).Select();
xSt.get_Range(excel.Cells[5, 1], excel.Cells[5, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[6, 1], excel.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[6, 1], excel.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[6, 1], excel.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[6, 1], excel.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[6, 1], excel.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置下边线加粗
excel.Visible = true;
string file = "保存的路径";
xBk.SaveCopyAs(file);
以下为我仿写的代码:
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Text; 7 using System.Windows.Forms; 8 9 using MSExcel = Microsoft.Office.Interop.Excel;10 11 namespace testoffice12 {13 public partial class Form1 : Form14 {15 public Form1()16 {17 InitializeComponent();18 }19 20 private void button1_Click(object sender, EventArgs e)21 {22 MSExcel._Application excel = new MSExcel.ApplicationClass();23 24 int rowIndex = 6, colIndex = 0, myCCount = 10, myRCount = 3;25 26 MSExcel._Workbook xBk = null;27 MSExcel._Worksheet xSt = null;28 29 xBk = excel.Workbooks.Add(true);30 xSt = (MSExcel._Worksheet)xBk.ActiveSheet;31 32 //33 for (int i = 0; i < myCCount; i++)34 {35 colIndex++;36 xSt.Cells[rowIndex, colIndex] = "标题" + colIndex.ToString();37 }38 39 //40 for (int i = 0; i < myRCount; i++)41 {42 rowIndex++;43 colIndex = 0;44 for (int j = 0; j < myCCount; j++)45 {46 colIndex++;47 xSt.Cells[rowIndex, colIndex] = "内容" + rowIndex.ToString() + ":" + colIndex.ToString();48 xSt.get_Range(xSt.Cells[rowIndex, colIndex], xSt.Cells[rowIndex, colIndex]).HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenter;49 }50 }51 52 xSt.Cells[1, 1] = "宇宙无限公司";53 xSt.Cells[2, 1] = "地址";54 xSt.Cells[3, 1] = "电话 传真";55 xSt.Cells[4, 1] = "客户对账单";56 xSt.Cells[5, 1] = "操作日期:" + DateTime.Now.ToLongTimeString();57 58 //59 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, 1]).Font.Bold = true;60 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, 1]).Font.Size = 22;61 xSt.get_Range(xSt.Cells[3, 1], xSt.Cells[3, 1]).Font.Bold = true;62 63 xSt.get_Range(xSt.Cells[6, 2], xSt.Cells[1, 1]).Font.Bold = true;64 65 //66 for (int i = 1; i < 6; i++)67 {68 xSt.get_Range(xSt.Cells[i, 1], xSt.Cells[i, colIndex]).Select();69 xSt.get_Range(xSt.Cells[i, 1], xSt.Cells[i, colIndex]).HorizontalAlignment = MSExcel.XlHAlign.xlHAlignCenterAcrossSelection;70 }71 72 //73 xSt.get_Range(xSt.Cells[6, 1], xSt.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;74 xSt.get_Range(xSt.Cells[6, 1], xSt.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = MSExcel.XlBorderWeight.xlThick;75 xSt.get_Range(xSt.Cells[6, 1], xSt.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlThick;76 xSt.get_Range(xSt.Cells[6, 1], xSt.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = MSExcel.XlBorderWeight.xlThick;77 xSt.get_Range(xSt.Cells[6, 1], xSt.Cells[rowIndex, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThick;78 79 excel.Visible = true;80 string file = "e:/testexcel.xlsx";81 xBk.SaveCopyAs(file);82 }83 }84 }
原标题:转载:C# Office 开发
关键词:C#