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

Question

请各位牛人帮帮手，生成一个excel文件以及把数据写入excel文件的函数是什么？不胜感激！

Answer 1

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的一个例子。

Answer 2

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.