你的位置:首页 > ASP.net教程

[ASP.net教程]转载:C# Office 开发


原文地址: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 }