C# 操作 Excel 要点

    技术2022-05-11  0

    C# 操作 Excel 要点

    1、 引用Microsoft.Office.Interop.Excel.dll

    打开VS2008的“解决方案资源管理器”,在“引用”文件夹下点击鼠标右键,选择“添加引用”,打开“添加引用”对话框。

    在“组件名称”那里找到“Microsoft.Office.Interop.Excel”,如上图所示。这里显示有两个可供选择的“Microsoft.Office.Interop.Excel”版本,我们选择其中的一个就可以了。单击“确定”,该组件就被正确引用进来了,如下图所示。

    2、引用命名空间、使用别名

    using System.Reflection;

    using Excel = Microsoft.Office.Interop.Excel;

     

    3、简单例子

    public void ToExcel(string strTitle)

    {

    int rowCount = nMax - nMin + 1;//总行数

    const int columnCount = 4;//总列数

     

    //创建Excel对象

    Excel.Application excelApp = new Excel.ApplicationClass();

     

    //新建工作簿

    Excel.Workbook workBook = excelApp.Workbooks.Add(true);

     

    //新建工作表

    Excel.Worksheet worksheet = workBook.ActiveSheet as Excel.Worksheet;

     

    //设置标题

    Excel.Range titleRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]);//选取单元格

    titleRange.Merge(true);//合并单元格

    titleRange.Value2 = strTitle; //设置单元格内文本

    titleRange.Font.Name = "宋体";//设置字体

    titleRange.Font.Size = 18;//字体大小

    titleRange.Font.Bold = true;//加粗显示

    titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中

    titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中

    titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框

    titleRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细

     

    //设置表头

    string[] strHead = new string[columnCount] { "序号", "分组", "频数", "相对频数" };

    int[] columnWidth = new int[4] { 8, 16, 8, 10 };

    for (int i = 0; i < columnCount; i++)

    {

    Excel.Range headRange = worksheet.Cells[2, i + 1] as Excel.Range;//获取表头单元格

    headRange.Value2 = strHead[i];//设置单元格文本

    headRange.Font.Name = "宋体";//设置字体

    headRange.Font.Size = 12;//字体大小

    headRange.Font.Bold = true;//加粗显示

    headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中

    headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中

    headRange.ColumnWidth = columnWidth[i];//设置列宽

    headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框

    headRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细

    }

     

    //设置每列格式

    for (int i = 0; i < columnCount; i++)

    {

    Excel.Range contentRange = worksheet.get_Range(worksheet.Cells[3, i+1], worksheet.Cells[rowCount-1 + 3, i+1]);

    contentRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中

    contentRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中

    contentRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框

    contentRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细

    contentRange.WrapText = true;//自动换行

    contentRange.NumberFormatLocal = "@";//文本格式

    }

     

    //填充数据

    for (int i = nMin; i <= nMax; i++)

    {

    int k = i - nMin;

    excelApp.Cells[k + 3, 1] = string.Format("{0}", k + 1);

    excelApp.Cells[k + 3, 2] = string.Format("{0}-{1}", i - 0.5, i + 0.5);

    excelApp.Cells[k + 3, 3] = string.Format("{0}", subordinativeValues[k].count);

    excelApp.Cells[k + 3, 4] = string.Format("{0:0.0000}", subordinativeValues[k].frequency);

    }

     

    //设置Excel可见

    excelApp.Visible = true;

    }


    最新回复(0)