【022】◀▶ C# 操作 Excel 等

---------------------------------------------------------------------------------------------------------

●·● 目录：

A1 ………… _Application 接口
A2 ………… Workbooks 接口
A3 ………… _Workbook 接口
A4 ………… Worksheets 接口
A5 ………… Sheets 接口
A6 ………… _Worksheet 接口
　　　　　　WorksheetClass
A7 ………… Range 接口
A8 ………… Areas 接口
A9 ………… Font 接口

G1 ………… 用 C# 操作 Excel
G2 ………… 用 C# 操作 Excel 代码
G3 ………… 用 C# 操作 Excel 参考博客
G4 ………… 用 C# 操作 Word
G4 ………… Convert 类
G5 ………… 可空类型 int? & double? & bool?
G6 ………… 值类型 int & double & bool
G7 ………… 引用类型 class & interface & string
G8 ………… 转义字符
G9 ………… 运算符

---------------------------------------------------------------------------------------------------------

写在前面：

引用：Microsoft.Office.Interop.Excel

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application ex = new Excel.Application();
Excel.Workbook eWorkbook;
Excel.Worksheet eWorksheet;

---------------------------------------------------------------------------------------------------------

●·● Microsoft.Office.Interop.Excel 命名空间

Application 接口：Represents the entire Microsoft Excel application.【继承于：_Application 接口】

Workbook 接口：Represents a Microsoft Excel workbook.【继承于：_Workbook 接口】

Worksheet 接口：Represents a worksheet.【继承于：_Worksheet 接口】

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A1个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● _Application 接口：

1. Represents the entire Microsoft Excel application.

2. _Application 属性：

Application：基本上具有 _Application 的调用！

ActiveCell：Returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. →返回当前选择的 Range 对象！

Excel.Range range = ex.ActiveCell;　　　　//赋值到一个 Range 对象
MessageBox.Show(range.Row.ToString());　　//获取行号
MessageBox.Show(range.Column.ToString());　　//获取列号

ActiveChart：Returns a Chart object that represents the active chart (either an embedded chart or a chart sheet).
ActiveSheet：Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. →返回当前的工作表对象！
```
Excel.Worksheet sheet = ex.ActiveSheet;
```
ActiveWorkbook：Returns a Workbookobject that represents the workbook in the active window (the window on top). →返回当前的工作簿对象！
```
Excel.Workbook book = ex.ActiveWorkbook;
```
Caption：The name that appears in the title bar of the main Microsoft Excel window.
Cursor：Returns or sets the appearance of the mouse pointer in Microsoft Excel.【返回：XlMousePointer】→鼠标显示样式！
CutCopyMode：Returns or sets the status of Cut or Copy mode.【返回：XlCutCopyMode】→选择框是否保留！
Sheets：Returns a Sheets collection that represents all the sheets in the active workbook. →返回所有工作表！
ThisWorkbook：Returns a Workbook object that represents the workbook where the current macro code is running. →返回当前工作表！
Top：The distance, in points, from the top edge of the screen to the top edge of the main Microsoft Excel window.
Left：The distance, in points, from the left edge of the screen to the left edge of the main Microsoft Excel window.
Height：The height, in points, of the main application window.
Columns：Returns a Range object that represents all the columns on the active worksheet. →返回所有列
Rows：Returns a Range object that represents all the rows on the active worksheet. →返回所有行

Cells：Returns a Range object that represents all the cells on the active worksheet. →返回所有单元格

long count = ex.Cells.CountLarge;　　　　//用 Count 会出错！
MessageBox.Show(count.ToString());　　//显示所有的单元格个数！

Visible：Determines whether the object is visible. →是否可见！
UserName：Returns or sets the name of the current user.
Value：Returns "Microsoft Excel".
Version：Determines whether the object is visible.
WindowState：Returns or sets the state of the window.【返回：XlWindowState】→最大化、最小化等！
Workbooks：Returns a Workbooks collection that represents all the open workbooks. →返回所有工作簿！
Worksheets：Returns a Sheets collection that represents all the worksheets in the active workbook. →返回所有工作表！

3. _Application 方法：

InputBox：Displays a dialog box for user input. Returns the information entered in the dialog box.
Quit：Quits Microsoft Excel.

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A2个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● Workbooks 接口：

1. A collection of all the Workbook objects that are currently open in the Microsoft Excel application.

2. Workbooks 属性：

Application：
Count：Returns the number of objects in the collection. Read-only Integer.
Item：Returns a single object from a collection.【类似数组的用法】
Parent：Returns the parent object for the specified object. Read-only.

3. Workbooks 方法：

Add：Creates a new workbook. The new workbook becomes the active workbook. Returns a Workbook object.
Close：Closes the object.
Open：Opens a workbook.

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A3个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● _Workbook 接口：

1. Represents a Microsoft Excel workbook.

2. _Workbook 属性：

ActiveChart：
ActiveSheet：
Application：When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application.
Charts：Returns a Sheets collection that represents all the chart sheets in the specified workbook. Read-only.
FullName：Returns the name of the object, including its path on disk, as a string. Read-only String. →返回文件路径
Name：
Saved：True if no changes have been made to the specified workbook since it was last saved. Read/write Boolean.
Sheets：Returns a Sheets collection that represents all the sheets in the specified workbook. Read-only Sheets object.
Worksheets：Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

2. _Workbook 方法：

Activate：Activates the first window associated with the workbook.
Close：Closes the object. →关掉当前工作簿！
Save：Saves changes to the specified workbook.
SaveAs：Saves changes to the workbook in a different file.
SaveAsXMLData：Exports the data that has been mapped to the specified XML schema map to an XML data file.
SaveCopyAs：Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A4个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● Worksheets 接口：

1. A collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet.

2. Worksheets 属性：

Application：
Count：
Item：
Parent：
Visible：Determines whether the object is visible. Read/write Object.

3. Worksheets 方法：

Add：Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.
Copy：Copies the sheet to another location in the workbook.
Delete：Deletes the object.
Move：Moves the sheet to another location in the workbook.
Select：Selects the object.

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A5个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● Sheets 接口：

1. 基本上与上面的 Worksheets 接口内容相同！

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A6个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● _Worksheet 接口：

1. Represents a worksheet.

2. _Worksheet 属性：

Application：
Cells：Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use). Read-only.
Columns：Returns a Range object that represents all the columns on the specified worksheet. Read-only.
Next：Returns a Chart, Range, or Worksheet object that represents the next sheet or cell. Read-only.
Range：Returns a Range object that represents a cell or a range of cells.
Rows：Returns a Range object that represents all the rows on the specified worksheet. Read-only Range object.
Visible：Determines whether the object is visible. Read/write XlSheetVisibility.

3. _Worksheet 方法：

Activate：Makes the current sheet the active sheet. Equivalent to clicking the sheet's tab.
Copy：Copies the sheet to another location in the workbook.
Move：Moves the sheet to another location in the workbook.
Paste：Pastes the contents of the Clipboard onto the sheet.
PasteSpecial：Pastes the contents of the Clipboard onto the sheet using a specified format. Use this method to paste data from other applications or to paste data in a specific format.
SaveAs：Saves changes to the chart or worksheet in a different file.
Select：Selects the object.

---------------------------------------------------------------------------------------------------------

●·● _Worksheet 接口：

1. This class supports the .NET Framework infrastructure and is not intended to be used directly from your code.

　　Namespace: Microsoft.Office.Interop.Excel
　　Assembly: Microsoft.Office.Interop.Excel (in microsoft.office.interop.excel.dll)

2. Worksheet 属性 & 方法：

Name：sheet的名称修改
Sheets.Add 方法 (Excel)
参考：http://msdn.microsoft.com/zh-cn/library/office/ff839847

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A7个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● Range 接口：

1. Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

2. Range 属性：

Address：Returns the range reference.
Areas：Returns an Areas collection that represents all the ranges in a multiple-area selection.
Borders：Returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format).
Cells：Returns a Range object that represents the cells in the specified range.
Column：Returns the number of the first column in the first area in the specified range.
Columns：Returns a Range object that represents the columns in the specified range.
ColumnWidth：Returns or sets the width of all columns in the specified range.
Count：Returns the number of objects in the collection.
End：Returns a Range object that represents the cell at the end of the region that contains the source range.
EntireColumn：Returns a Range object that represents the entire column (or columns) that contains the specified range.
EntireRow：Returns a Range object that represents the entire row (or rows) that contains the specified range.
Font：Returns a Font object that represents the font of the specified object.
FormatConditions：Returns a FormatConditions collection that represents all the conditional formats for the specified range.
Height：The height of the range.
Hidden：Determines if the rows or columns are hidden.
HorizontalAlignment：Returns or sets the horizontal alignment for the specified object.
Item：Returns a Range object that represents a range at an offset to the specified range.
Left：The distance from the left edge of column A to the left edge of the range.
MergeArea：Returns a Range object that represents the merged range containing the specified cell.
MergeCells：Determines if the range or style contains merged cells.
Name：Returns or sets the name of the object.
Next：Returns a Range object that represents the next cell.
NumberFormat：Returns or sets the format code for the object.
OffSet：Returns a Range object that represents a range that’s offset from the specified range.
Resize：Resizes the specified range.
Row：Returns the number of the first row of the first area in the range.
RowHeight：Returns the height of all the rows in the range specified, measured in points.
Rows：Returns a Range object that represents the rows in the specified range.
Text：Returns or sets the text for the specified object.
Top：Returns the distance, in points, from the top edge of row 1 to the top edge of the range.
Value：Returns or sets the value of the specified range.
Width：The width, in points, of the range.
Worksheet：Returns a Worksheet object that represents the worksheet containing the specified range.

3. Range 方法：

Activate：Activates a single cell, which must be inside the current selection.
BorderAround：Adds a border to a range and sets the Color, LineStyle, and Weight properties for the new border.　　
Clear：Clears the entire object.　　
ClearFormats：Clears the formatting of the object.　　　　
ClearOutline：Clears the outline for the specified range.　　
Copy：Copies the range to the specified range or to the Clipboard.　　
Cut：Cuts the object to the Clipboard or pastes it into a specified destination.　　
Delete：Deletes the object.　　
Find：Finds specific information in a range and returns a Range object that represents the first cell where that information is found.　　
FindNext：Continues a search that was begun with the Find method.
FindPrevious：Continues a search that was begun with the Find method.　　
Merge：Creates a merged cell from the specified Range object.　　
PasteSpecial：Pastes a Range from the Clipboard into the specified range.　　
Replace：Returns a Boolean indicating characters in cells within the specified range.　　
Select：Selects the object.　　
UnMerge：Separates a merged area into individual cells.

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A8个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● Areas 接口：

1. A collection of the areas, or contiguous blocks of cells, within a selection.

2. Areas 属性：

Application：
Count：
Item：
Parent：

3. Areas 方法：

GetEnumerator：Returns an enumerator to support iterating through the collection.

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第A9个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● Font 接口：

1. Contains the font attributes (font name, font size, color, and so on) for an object.

2. Font 属性：

Application：
Background：
Bold：
Color：
ColorIndex：
FontStyle：
Italic：
Name：
OutlineFont：
Parent：
Shadow：
Size：
Strikethrough：
Subscript：
Superscript：
Underline：

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第G1个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● 用 C# 操作 Excel：

① 添加引用：

　　引用的库位于“COM”选项卡下，名称为 Microsoft Excel X Object Library。其中 X 是版本号。

② 在代码中添加引用：

　　之所以像下面这样写，是因为有很多此命名空间中的类和 System.Windows.Forms 的类有很多相同的，于是可以用 Excel 强制在前面加上命名空间！

using Excel=Microsoft.Office.Interop.Excel;

③ 创建应用程序、工作簿、工作表：

Excel.Application ex = new Excel.Application();　　　　//创建程序
Excel.Workbook eWorkbook;　　　　　　　　　　　　//工作簿
Excel.Worksheet eWorksheet;　　　　　　　　　　　　//工作表

private void Form1_Load(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Filter = "Excel File(*.xlsx)|*.xlsx";
    if (ofd.ShowDialog() != DialogResult.OK)　　　　//获取 Excel 文件的路径
        return;

    eWorkbook = ex.Workbooks.Open(ofd.FileName);　　　　//打开文件，赋值到工作簿
    ex.Visible = true;　　　　　　　　　　　　　　　　　　　　//程序显示
    eWorksheet = (Excel.Worksheet)eWorkbook.Sheets[1];　　//获取第一个工作表
}

不保存关闭程序：

eWorkbook.Saved = true;　　//就不用再保存了，其实没有保存
ex.Quit();　　　　　　　　　　//退出程序

保存后关闭程序：

eWorkbook.Save();　　　　　//工作簿保存
ex.Quit();　　　　　　　　　  //退出程序

④ 获取某一列使用的最下一行的行号：

int rowCount = ex.Application.get_Range("B65535", Type.Missing).get_End(Excel.XlDirection.xlUp).Row;　　//“B”列的最下一行

　　获取某一行使用的最右边的列号：

int columnCount = ex.Application.get_Range("ZZ3", Type.Missing).get_End(Excel.XlDirection.xlToLeft).Column;　　//“3”行的最右一列

⑤ 复制某一区域到另一区域：

Excel.Range CopyRange = eWorksheet.get_Range("B2", "E14");　　//欲拷贝的区域
CopyRange.Copy();　　　　　　　　　　　　　　　　　　　　　　//复制此区域
eWorksheet.Range["B16"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);　　//选择性黏贴从单元格 B16 开始，沿用所有的主题
ex.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;　　　　//取消选择框

Excel.Range range1 = eWorksheet.get_Range("B2");　　//获取第2行的一个单元格
Excel.Range range2 = eWorksheet.get_Range("B16");　　//获取第16行的一个单元格
range2.RowHeight = range1.RowHeight;　　　　　　　//将第2行的行高赋值到第16行的行高

⑥ 练习程序：

            object path;  //文件路径变量

            MSExcel.Application excelApp; //Excel应用程序变量
            MSExcel.Workbook excelDoc; //Excel文档变量

            path = @"C:\MyExcel.xlsx";  //路径
            excelApp = new MSExcel.Application(); 
                //new MSExcel.ApplicationClass();  //初始化

            //如果已存在，则删除
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }

            //由于使用的是COM库，因此有许多变量需要用Nothing代替
            Object Nothing = Missing.Value;
            excelDoc = excelApp.Workbooks.Add(Nothing);

            //WdSaveFormat为Excel文档的保存格式
            object format = MSExcel.XlFileFormat.xlWorkbookDefault;

            //将excelDoc文档对象的内容保存为XLSX文档 
            excelDoc.SaveAs(path, format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);

            //关闭excelDoc文档对象 
            excelDoc.Close(Nothing, Nothing, Nothing);

            //关闭excelApp组件对象 
            excelApp.Quit();

　　Excel 表格修改：

            object path;  //文件路径变量

            MSExcel.Application excelApp; //Excel应用程序变量
            MSExcel.Workbook excelDoc; //Excel文档变量

            path = @"C:\MyExcel.xlsx";  //路径
            excelApp = new MSExcel.Application();  //初始化

            //如果已存在，则删除
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }

            //由于使用的是COM库，因此有许多变量需要用Nothing代替
            Object Nothing = Missing.Value;
            excelDoc = excelApp.Workbooks.Add(Nothing);

            //使用第一个工作表作为插入数据的工作表
            MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1];

            //声明一个MSExcel.Range 类型的变量r
            MSExcel.Range r;

            //获得A1处的表格，并赋值
            r = ws.get_Range("A1", "A1");
            r.Value2 = "数据1";

            //获得A2处的表格，并赋值
            r = ws.get_Range("A2", "A2");
            r.Value2 = "5.7";

            //WdSaveFormat为Excel文档的保存格式
            object format = MSExcel.XlFileFormat.xlWorkbookDefault;

            //将excelDoc文档对象的内容保存为XLSX文档 
            excelDoc.SaveAs(path, format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);

            //关闭excelDoc文档对象 
            excelDoc.Close(Nothing, Nothing, Nothing);

            //关闭excelApp组件对象 
            excelApp.Quit();

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第U2个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● 用 C# 操作 Excel 代码：

1. 一些基本操作！

range.NumberFormatLocal = "@";     //设置单元格格式为文本 
range = (Range)worksheet.get_Range("A1", "E1");     //获取Excel多个单元格区域：本例做为Excel表头
range.Merge(0);     //单元格合并动作
worksheet.Cells[1, 1] = "Excel单元格赋值";     //Excel单元格赋值
range.Font.Size = 15;     //设置字体大小
range.Font.Underline=true;     //设置字体是否有下划线
range.Font.Name="黑体";     设置字体的种类
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;     //设置字体在单元格内的对其方式
range.ColumnWidth=15;     //设置单元格的宽度
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();     //设置单元格的背景色
range.Borders.LineStyle=1;     //设置单元格边框的粗细
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     //给单元格加边框
range.EntireColumn.AutoFit();     //自动调整列宽
Range.HorizontalAlignment= xlCenter;     // 文本水平居中方式
Range.VerticalAlignment= xlCenter     //文本垂直居中方式
Range.WrapText=true;     //文本自动换行
Range.Interior.ColorIndex=39;     //填充颜色为淡紫色
Range.Font.Color=clBlue;     //字体颜色
xlsApp.DisplayAlerts=false;     //保存Excel的时候，不弹出是否保存的窗口直接进行保存
workbook.SaveCopyAs(temp);/**////填入完信息之后另存到路径及文件名字

2. 一些基本操作（貌似来自于台湾的朋友）

using Excel;
 // from bill example
         public void writeExcelAdvance(String outputFile)
         {
             string[,] myData = 
             { 
             { "車牌號", "類型", "品 牌", "型 號", "顏 色", "附加費證號", "車架號" }, 
             { "浙KA3676", "危險品", "貨車", "鐵風SZG9220YY", "白", "1110708900", "022836" }, 
             { "浙KA4109", "危險品", "貨車", "解放CA4110P1K2", "白", "223132", "010898" }, 
             { "浙KA0001A", "危險品", "貨車", "南明LSY9190WS", "白", "1110205458", "0474636" }, 
             { "浙KA0493", "上普貨", "貨車", "解放LSY9190WS", "白", "1110255971", "0094327" }, 
             { "浙KA1045", "普貨", "貨車", "解放LSY9171WCD", "藍", "1110391226", "0516003" }, 
             { "浙KA1313", "普貨", "貨車", "解放9190WCD", "藍", "1110315027", "0538701" }, 
             { "浙KA1322", "普貨", "貨車", "解放LSY9190WS", "藍", "24323332", "0538716" }, 
             { "浙KA1575", "普貨", "貨車", "解放LSY9181WCD", "藍", "1110314149", "0113018" }, 
             { "浙KA1925", "普貨", "貨車", "解放LSY9220WCD", "藍", "1110390626", "00268729" }, 
             { "浙KA2258", "普貨", "貨車", "解放LSY9220WSP", "藍", "111048152", "00320" } 
             };
             //引用Excel Application類別
             Application myExcel = null;
             //引用活頁簿類別
             Workbook myBook = null;
             //引用工作表類別
             Worksheet mySheet = null;
             //引用Range類別
             Range myRange = null;
                   //開啟一個新的應用程式
             myExcel = new Microsoft.Office.Interop.Excel.Application();             
//打开一个已经存在的excel excel2003和2007版打开参数不一样，具体可以上网上去查:             
//excelSql.Workbooks.Open(@"C:\08.xls"(已经存在的excel路径), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
//Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing,Type.Missing);
             //加入新的活頁簿
             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();
             int a = 0;
             int UpBound1 = myData.GetUpperBound(0);
                    //二維陣列數上限
             int UpBound2 = myData.GetUpperBound(1);
                        //二維陣列數上限
             //寫入報表名稱 
             myExcel.Cells[1, 4] = "普通報表";
             //以下的Select方法可省略，加速Excel運行，但VBA有些功能必須要用到Select方法。
                   //以下的Select方法可省略，加速Excel運行，但VBA有些功能必須要用到Select方法。
             //逐行寫入數據
             for (int i = 0; i < UpBound1; i++)
             {
                 for (int j = 0; j < UpBound2; j++)
                 {
                     //以單引號開頭，表示該單元格為純文字
                     a++;
                     //用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";
             //Console.WriteLine(mySheet.Name);
             //寫入報表名稱 
             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(outputFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                             , XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.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();

3. 将 Excel 中的数据导入到 DataGridView 中，在窗体中显示！

//创建一个数据链接，貌似下面的代码只支持 03 版的！Excel 8.0
string strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = F:\\Desktop\\长江水文.xls; Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();
//打开数据链接，得到一个数据集  
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//创建一个 DataSet对象  
DataSet myDataSet = new DataSet();
//得到自己的DataSet对象  
myCommand.Fill(myDataSet, "[Sheet1$]");
//关闭此数据链接  
myConn.Close();

dataGridView1.DataMember = "[Sheet1$]";
dataGridView1.DataSource = myDataSet;

注意：会自动将有内容的部分加载过来，同时只会保留原来表格中的值，而且合并的单元格都会按单个单元格来处理！

4. 打开 Excel 表格 & 往 Excel 表格中输入数据！

    Excel.Application excel = new Excel.Application ( ) ;  
    excel.Application.Workbooks.Add ( true ) ;  
    excel.Visible = true ;

    Excel.Application excel = new Excel.Application ( ) ;  
    excel.Application.Workbooks.Add ( true ) ;  
    excel.Cells[ 1 , 1 ] = "第一行第一列" ;  
    excel.Cells[ 1 , 2 ] = "第一行第二列" ;  
    excel.Cells[ 2 , 1 ] = "第二行第一列" ;  
    excel.Cells[ 2 , 2 ] = "第二行第二列" ;  
    excel.Cells[ 3 , 1 ] = "第三行第一列" ;  
    excel.Cells[ 3 , 2 ] = "第三行第二列" ;  
    excel.Visible = true ;

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第U3个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● 用 C# 操作 Excel 参考博客：

1. 参考：c# 操作Excel(标题行,页脚,行高,边框..)

2. 参考：使用VSTO复制部分Excel单元格(带格式），粘贴并保存到另一Excel文件中

3. 参考：C# 复制粘贴 Excel 表格数据，与隐藏 Excel Sheet 的操作

4. 参考：C#操作Excel开发报表系列整理

5. 参考：C# 显示excel的最大数据行数

6. 参考：C#操作excel(多种方法比较)

7. 参考：C#操作excel（上路篇）

8. 参考：几条C#的Excel编程技巧

9. 参考：C# WinForm开发系列 - Excel

a. 参考：Automating MS Excel Using Visual Studio .NET

---------------------------------------------------------------------------------------------------------

            ╔════════╗
╠════╣    第U4个    ╠══════════════════════════════════════════════════╣
            ╚════════╝

●·● 用 C# 操作 Word：

创建 word

添加引用：

引用的库位于“COM”选项卡下，名称为 Microsoft Word X Object Library。其中 X 是版本号。

            object path;  //文件路径变量
            string strContent;  //文本内容变量

            MSWord.Application wordApp; //Word应用程序变量
            MSWord.Document wordDoc; //Word文档变量

            path = @"C:\MyWord.docx";  //路径
            wordApp = new MSWord.Application();  //初始化

            //如果已存在，则删除
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }

            //由于使用的是COM库，因此有许多变量需要用Missing.Value代替
            Object Nothing = Missing.Value;
            wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);

            //strContent = "你好！\n";
            //wordDoc.Paragraphs.Last.Range.Text = strContent;

            //strContent = "Hello World";
            //wordDoc.Paragraphs.Last.Range.Text = strContent;

            //WdSaveFormat为Word 2007文档的保存格式
            object format = MSWord.WdSaveFormat.wdFormatDocumentDefault;

            //将wordDoc文档对象的内容保存为DOCX文档 
            wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);

            //关闭wordDoc文档对象 
            wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);

            //关闭wordApp组件对象 
            wordApp.Quit(ref Nothing, ref Nothing, ref Nothing);

posted on 2012-03-12 23:12 McDelfino 阅读(1294) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

alex_bn_lee

导航

公告

【022】◀▶ C# 操作 Excel 等

●·● 目录：

●·● Microsoft.Office.Interop.Excel 命名空间

●·● _Application 接口：

●·● Workbooks 接口：

●·● _Workbook 接口：

●·● Worksheets 接口：

●·● Sheets 接口：

●·● _Worksheet 接口：

●·● _Worksheet 接口：

●·● Range 接口：

●·● Areas 接口：

●·● Font 接口：

●·● 用 C# 操作 Excel：

●·● 用 C# 操作 Excel 代码：

●·● 用 C# 操作 Excel 参考博客：

●·● 用 C# 操作 Word：