none
生成一个excel文件以及把数据写入excel文件的函数 RRS feed

  • 问题

  • 请各位牛人帮帮手,生成一个excel文件以及把数据写入excel文件的函数是什么?不胜感激!
    2011年10月25日 10:20

答案

  • using Microsoft.Office.Interop.Excel; 

    // 引用Excel類別

    //引用Excel Application類別
    _Application myExcel = null; 
    //引用活頁簿類別 
    _Workbook myBook = null;
    //引用工作表類別
    _Worksheet mySheet = null;
    //引用Range類別 
    Range myRange = null;

     

     

     

     

    <![CDATA[ .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }]]

     

     

     

    // 建立一個新的Excel

    //開啟一個新的應用程式
    myExcel = new Microsoft.Office.Interop.Excel.Application();

     

     

    <![CDATA[ .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }]]>

    // 快樂的使用Excel

    //加入新的活頁簿 
    myExcel.Workbooks.Add(true);
    //停用警告訊息
    myExcel.DisplayAlerts = false;
    //讓Excel文件可見 
    myExcel.Visible = true;
    //引用第一個活頁簿
    myBook = myExcel.Workbooks[1];
    //設定活頁簿焦點
    myBook.Activate();
    //引用第一個工作表
    mySheet = (_Worksheet)myBook.Worksheets[1];
    //命名工作表的名稱為 
    "Array"mySheet.Name = "Cells";
    //設工作表焦點
    mySheet.Activate(); 
    //用offset寫入陣列資料
    myRange = mySheet.get_Range("A2", Type.Missing);myRange.get_Offset(i, j).Select();
    myRange.Value2 = "'" + myData[i, j]; 
    //用Cells寫入陣列資料
    myRange.get_Range(myExcel.Cells[2 + i, 1 + j], myExcel.Cells[2 + i, 1 + j]).Select();
    myExcel.Cells[2 + i, 1 + j] = "'" + myData[i, j]; 
    //加入新的工作表在第1張工作表之後
    myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
    //引用第2個工作表mySheet = (_Worksheet)myBook.Worksheets[2];
    //命名工作表的名稱為 "Array"
    mySheet.Name = "Array";

    //加入新的工作表在第1張工作表之後
    myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
    //引用第2個工作表
    mySheet = (_Worksheet)myBook.Worksheets[2];
    //命名工作表的名稱為 "Array"
    mySheet.Name = "Array";
    //寫入報表名稱
    myExcel.Cells[1, 4] = "普通報表";
    //設定範圍
    myRange=(Range)mySheet.get_Range(myExcel.Cells[2, 1], myExcel.Cells[UpBound1 + 1, UpBound2 + 1]);
    myRange.Select();
    //用陣列一次寫入資料
    myRange.Value2 = "'" + myData;
    //設定儲存路徑
    string PathFile = Directory.GetCurrentDirectory() + @"\button4.xls";
    //另存活頁簿
    myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
    , XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, ype.Missing,Type.Missing,Type.Missing);
    //關閉活頁簿
    myBook.Close(false, Type.Missing, Type.Missing);
    //關閉Excel
    myExcel.Quit();
    //釋放Excel資源
    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
    myBook = null;
    mySheet = null;
    myRange = null;
    myExcel = null;
    GC.Collect();

     

    这是我写Excel的一个例子。

    2011年10月25日 10:34
  • Hi kellypp,

    欢迎来到MSDN论坛!

    可以根据系统安装的Office版本,给项目引入对应版本的Microsoft.Office.Interop.Excel,使用COM Interop创建Excel电子表格。例如,在Office2003下,您应当引入Microsoft.Office.Interop.Excel 11.0.0.0版本。

    请您参考以下链接:

    如何:使用 COM Interop 创建 Excel 电子表格(C# 编程指南)
    http://msdn.microsoft.com/zh-cn/library/ms173186(v=vs.80).aspx

    有关代码如下:

     

    using System;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
     
    namespace ExcelTest
    {
        public class CreateExcelWorksheet
        {
            static void Main()
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
                    return;
                }
     
                xlApp.Visible = true;
     
                Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet ws = (Worksheet)wb.Worksheets[1];
     
                if (ws == null)
                {
                    Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
                }
     
                // Select the Excel cells, in the range c1 to c7 in the worksheet.
                Range aRange = ws.get_Range("C1", "C7");
                if (aRange == null)
                {
                    Console.WriteLine("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
                }
     
                // Fill the cells in the C1 to C7 range of the worksheet with the number 6.
                Object[] args = new Object[1];
                args[0] = 6;
                aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args);
     
                // Change the cells in the C1 to C7 range of the worksheet to the number 8.
                aRange.Value2 = 8;
            }
        }
    }
    

    祝您,一切顺利!


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    2011年10月27日 6:41
    版主

全部回复

  • using Microsoft.Office.Interop.Excel; 

    // 引用Excel類別

    //引用Excel Application類別
    _Application myExcel = null; 
    //引用活頁簿類別 
    _Workbook myBook = null;
    //引用工作表類別
    _Worksheet mySheet = null;
    //引用Range類別 
    Range myRange = null;

     

     

     

     

    <![CDATA[ .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }]]

     

     

     

    // 建立一個新的Excel

    //開啟一個新的應用程式
    myExcel = new Microsoft.Office.Interop.Excel.Application();

     

     

    <![CDATA[ .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }]]>

    // 快樂的使用Excel

    //加入新的活頁簿 
    myExcel.Workbooks.Add(true);
    //停用警告訊息
    myExcel.DisplayAlerts = false;
    //讓Excel文件可見 
    myExcel.Visible = true;
    //引用第一個活頁簿
    myBook = myExcel.Workbooks[1];
    //設定活頁簿焦點
    myBook.Activate();
    //引用第一個工作表
    mySheet = (_Worksheet)myBook.Worksheets[1];
    //命名工作表的名稱為 
    "Array"mySheet.Name = "Cells";
    //設工作表焦點
    mySheet.Activate(); 
    //用offset寫入陣列資料
    myRange = mySheet.get_Range("A2", Type.Missing);myRange.get_Offset(i, j).Select();
    myRange.Value2 = "'" + myData[i, j]; 
    //用Cells寫入陣列資料
    myRange.get_Range(myExcel.Cells[2 + i, 1 + j], myExcel.Cells[2 + i, 1 + j]).Select();
    myExcel.Cells[2 + i, 1 + j] = "'" + myData[i, j]; 
    //加入新的工作表在第1張工作表之後
    myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
    //引用第2個工作表mySheet = (_Worksheet)myBook.Worksheets[2];
    //命名工作表的名稱為 "Array"
    mySheet.Name = "Array";

    //加入新的工作表在第1張工作表之後
    myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
    //引用第2個工作表
    mySheet = (_Worksheet)myBook.Worksheets[2];
    //命名工作表的名稱為 "Array"
    mySheet.Name = "Array";
    //寫入報表名稱
    myExcel.Cells[1, 4] = "普通報表";
    //設定範圍
    myRange=(Range)mySheet.get_Range(myExcel.Cells[2, 1], myExcel.Cells[UpBound1 + 1, UpBound2 + 1]);
    myRange.Select();
    //用陣列一次寫入資料
    myRange.Value2 = "'" + myData;
    //設定儲存路徑
    string PathFile = Directory.GetCurrentDirectory() + @"\button4.xls";
    //另存活頁簿
    myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
    , XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, ype.Missing,Type.Missing,Type.Missing);
    //關閉活頁簿
    myBook.Close(false, Type.Missing, Type.Missing);
    //關閉Excel
    myExcel.Quit();
    //釋放Excel資源
    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
    myBook = null;
    mySheet = null;
    myRange = null;
    myExcel = null;
    GC.Collect();

     

    这是我写Excel的一个例子。

    2011年10月25日 10:34
  • Hi kellypp,

    欢迎来到MSDN论坛!

    可以根据系统安装的Office版本,给项目引入对应版本的Microsoft.Office.Interop.Excel,使用COM Interop创建Excel电子表格。例如,在Office2003下,您应当引入Microsoft.Office.Interop.Excel 11.0.0.0版本。

    请您参考以下链接:

    如何:使用 COM Interop 创建 Excel 电子表格(C# 编程指南)
    http://msdn.microsoft.com/zh-cn/library/ms173186(v=vs.80).aspx

    有关代码如下:

     

    using System;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
     
    namespace ExcelTest
    {
        public class CreateExcelWorksheet
        {
            static void Main()
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
                    return;
                }
     
                xlApp.Visible = true;
     
                Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet ws = (Worksheet)wb.Worksheets[1];
     
                if (ws == null)
                {
                    Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
                }
     
                // Select the Excel cells, in the range c1 to c7 in the worksheet.
                Range aRange = ws.get_Range("C1", "C7");
                if (aRange == null)
                {
                    Console.WriteLine("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
                }
     
                // Fill the cells in the C1 to C7 range of the worksheet with the number 6.
                Object[] args = new Object[1];
                args[0] = 6;
                aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args);
     
                // Change the cells in the C1 to C7 range of the worksheet to the number 8.
                aRange.Value2 = 8;
            }
        }
    }
    

    祝您,一切顺利!


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    2011年10月27日 6:41
    版主