ArcGIS Pro C#二次开发书中，Excel操作部分代码

using System;

using System.Collections.Generic;

using System.Linq;

using System.IO;

using System.Data;

using System.Text;

//usingSystem.Windows.Forms;

using System.Reflection; //引用这个才能使用Missing字段

//using Excel;

using Excel = Microsoft.Office.Interop.Excel;

using Microsoft.Office.Core;

using ArcGIS.Desktop.Framework.Dialogs;

//先添加引用Microsoft Excel 11.0 Object Library

//usingMicrosoft.Office.Core;

//using Excel = Microsoft.Office.Interop.Excel;

//using Excel = Microsoft.Office.Interop.Excel;

namespace YLPub

{

// 定义一个MyExcel类，用于操作Excel文档

public class MyExcel

{

// 定义类的字段

stringmyFileName;                // Excel文件名

intOpennum = 0;                  // 打开的文件数量

Excel._Application myExcel = null;   // Excel应用实例

publicExcel.Workbook myWorkBook;    // 当前操作的工作簿

// 关闭Excel工作簿的方法

public void CloseSheet()

{

if(myWorkBook != null)

{

myWorkBook.Close(true);

}

}

// 向指定单元格写入数据并设置小数点位数

public void WriteDataxs2(stringdata, int row, intcolumn, int num)

{

myExcel.Cells[row, column] = data;

Excel.Range r = (Excel.Range)(myExcel.Cells[row, column]);

switch(num)

{

case2:

r.NumberFormatLocal = "0.00_ ";

break;

case3:

r.NumberFormatLocal = "0.000_ ";

break;

case1:

r.NumberFormatLocal = "0.0_ ";

break;

default:

r.NumberFormatLocal = "0.000_ ";

break;

}

}

// 一些针对Excel表格的操作（未明确功能名称）

public void ZJ周至县JZD(int pnum)

{

Excel.Range pRange = myExcel.Rows[52] as Excel.Range;

pRange.Select();

//pRange = pRange["D52"] as Excel.Range;

//pRange.Activate();

intnum = pnum - 25;

for(int i = 0; i < num * 2; i++)

{

pRange.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

}

intMAXN = pnum * 2 + 2;

pRange = myExcel.Range["A51:A" + MAXN] as Excel.Range;

pRange.UnMerge();

pRange = myExcel.Range["B51:B" + MAXN] as Excel.Range;

pRange.UnMerge();

pRange = myExcel.Range["C51:C" + MAXN] as Excel.Range;

pRange.UnMerge();

for(int i = 0; i < num + 1; i++)

{

pRange = myExcel.Range["A" + ((i * 2) + 51).ToString() + ":A" + ((i * 2) + 52).ToString()] as Excel.Range;

pRange.Merge();

pRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.连续直线;

pRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.连续直线;

//Range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.无;

//Range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.无;

pRange = myExcel.Range["B" + ((i * 2) + 51).ToString() + ":B" + ((i * 2) + 52).ToString()] as Excel.Range;

pRange.Merge();

pRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.连续直线;

pRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.连续直线;

pRange = myExcel.Range["C" + ((i * 2) + 51).ToString() + ":C" + ((i * 2) + 52).ToString()] as Excel.Range;

pRange.Merge();

pRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.连续直线;

pRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.连续直线;

}

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

{

pRange = myExcel.Range["D" + ((i * 2) + 52).ToString() + ":D" + ((i * 2) + 53).ToString()] as Excel.Range;

pRange.Merge();

}

}

// 根据进程名称结束进程的方法

public static void Kill(string ProcessName)

{

System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName(ProcessName);

foreach(System.Diagnostics.Process p in process)

{

if(!p.HasExited)  //如果程序没有关闭，结束程序

{

p.Kill();

p.WaitForExit();

}

}

}

/// <summary>

///类的构造函数，不创建Excel工作薄

/// </summary>

publicMyExcel()

{

//ylbasicpub.YLCover.KillWordProcess("EXCEL.exe");

Kill("EXCEL"); // 结束Excel进程

try

{

myExcel = new Excel.Application();

//myExcel = new Excel.ApplicationClass();

myExcel.DisplayAlerts = false;//不提示

//请不要删除以下信息

//版权：http://XingFuStar.cnblogs.com

}

catch(Exception ex)

{

MessageBox.Show("错误:" + ex.Message.ToString());

}

Opennum = 0;

}

// 设置Excel应用的可见性

public void SetVisible(boolv)

{

myExcel.Visible = v;

}

// 滚动到Excel的左上角

public void ScrollTopLeft()

{

myExcel.ActiveWindow.ScrollColumn = 1;

myExcel.ActiveWindow.ScrollRow = 1;

}

//插入指定行

public void Insert4Row(intStartRow, int EndRow)

{

Excel.Range Range = (Excel.Range)myExcel.Rows[StartRow.ToString() + ":" + EndRow.ToString(), System.Type.Missing];

Range.Copy();

intidx = EndRow + 1;

Excel.Range pSelection = myExcel.Rows[idx] as Excel.Range;

pSelection.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlCutCopyMode.xlCopy);

}

//设置普通视图

public void SetNormalView()

{

myExcel.ActiveWindow.View = Excel.XlWindowView.xlNormalView;//普通视图xlNormalView和打印视图xlPageBreakPreview

//myExcel.ActiveWindow.SelectedSheets.PrintPreview();//打印预览

}

//在第8行之前插入分页符：ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;

public void AddPageBreak(introw)

{

try

{

Excel.Range pRange = myExcel.Rows[row] as Excel.Range;

pRange.PageBreak = 1;

}

catch(Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

}

/// <summary>

/// 创建Excel工作薄

/// </summary>

public void CreateExcel()

{

//myExcel = new Excel.Application();

myWorkBook = myExcel.Application.Workbooks.Add(true);

}

// 添加新的工作表并返回

publicExcel.Worksheet AddSheeet()

{

//myExcel = new Excel.Application();

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myWorkBook.Sheets.Add();

excelWorksheet.Select();

returnexcelWorksheet as Excel.Worksheet;

}

// 添加新的工作表到最后并返回

publicExcel.Worksheet AddSheeetLast()

{

//myExcel = new Excel.Application();

intnum = myWorkBook.Sheets.Count;

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myWorkBook.Sheets.get_Item(num);

excelWorksheet.Select();

returnmyWorkBook.Sheets.Add(Missing.Value, excelWorksheet) as Excel.Worksheet;

}

// 获取所有工作表的名称列表

public void AddSheeet(intid, int num)

{

//myExcel = new Excel.Application();

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(id);

excelWorksheet.Select();//一定要选

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

{

myWorkBook.Sheets.Add(Missing.Value, excelWorksheet);

//AddSheeetLast();

//AddSheeet();

//myExcel.Sheets.Add(Missing.Value, excelWorksheet);

}

}

//隐藏某个工作表

public void hideSheet(stringsheetname, bool hide)

{

//获取指定名称的工作表

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(sheetname);

if(hide)

{//隐藏工作表

excelWorksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;

}

else

{ //显示工作表

excelWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;

}

}

//删除隐藏的工作表

public void deletehideSheet(stringsheetname)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(sheetname);

if(excelWorksheet != null)

{

excelWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;

excelWorksheet.Delete();

}

}

//获取指定索引的工作表

publicExcel.Worksheet getSheeet(int idx)

{

//myExcel = new Excel.Application();

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(idx);

excelWorksheet.Select();

returnexcelWorksheet;

}

//获取所有工作表名称列表

public List<string> getSheeetList()

{

//myExcel = new Excel.Application();

intnum = myExcel.Sheets.Count;

List<string> pList = new List<string>();

for(int i = 1; i < num + 1; i++)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(i);

pList.Add(excelWorksheet.Name);

}

returnpList;

}

//获取第一行字段名称

public List<string> getFieldName()

{

Excel.Worksheet xlSheet = (Excel.Worksheet)myExcel.ActiveSheet;

intcnum = xlSheet.UsedRange.Columns.Count;

List<string> pList = new List<string>();

for(int i = 1; i <= cnum; i++)

{

Excel.Range r1 = (Excel.Range)(xlSheet.Cells[1, i]);

pList.Add(r1.Text.ToString());

}

returnpList;

}

//复制指定行内容

public void CopyRownum(introw, int num)

{

Excel.Range pRange = myExcel.Rows[row] as Excel.Range;

pRange.Copy();

pRange = myExcel.Rows[row + 2] as Excel.Range;

pRange.Select();

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

{

pRange.Insert();

}

}

//从指定区间复制多行到指定行

public void CopyRownum(stringstartidx, string endidx, int Copyto)

{

Excel.Range pRange = myExcel.Range[startidx, endidx] as Excel.Range;

pRange.Copy();

pRange = myExcel.Rows[Copyto] as Excel.Range;

pRange.Select();

Excel.Worksheet pWorksheet = (Excel.Worksheet)myWorkBook.ActiveSheet;

pWorksheet.Paste();

}

//从指定区间复制多行到指定行并设置行高

public void CopyRownum2(stringstartidx, string endidx, int Copyto, int num)

{

Excel.Range pRange = myExcel.Range[startidx, endidx] as Excel.Range;

pRange.Copy();

pRange = myExcel.Rows[Copyto] as Excel.Range;

pRange.Select();

Excel.Worksheet pWorksheet = (Excel.Worksheet)myWorkBook.ActiveSheet;

double RowH = getRowHeight(4);

pWorksheet.Paste();

SetRowHeight(Copyto + 3, Copyto + 3 + num, RowH);

}

//专门定制,//为特定功能定制的复制函数

public void CopyRownum3(stringstartidx, string endidx, int Copyto, int num)

{

Excel.Range pRange = myExcel.Range[startidx, endidx] as Excel.Range;

pRange.Copy();

pRange = myExcel.Rows[Copyto] as Excel.Range;

pRange.Select();

Excel.Worksheet pWorksheet = (Excel.Worksheet)myWorkBook.ActiveSheet;

double RowH1 = getRowHeight(1);

double RowH2 = getRowHeight(10);

pWorksheet.Paste();

SetRowHeight(Copyto, Copyto + 7, RowH1);

SetRowHeight(Copyto + 8, Copyto + num, RowH2);

}

//从指定行复制内容到另一指定行

public void CopyRownumTo(introw, int num, inttoNum)

{

Excel.Range pRange = myExcel.Rows[row] as Excel.Range;

pRange.Copy();

pRange = myExcel.Rows[toNum] as Excel.Range;

pRange.Select();

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

{

pRange.Insert();

}

}

//insert第row1行到第row2行,//插入指定行区间到另一行区间

public void Insert(int row1,int row2)

{

Excel.Range pRange = myExcel.Rows[row1] as Excel.Range;

pRange.Select();

pRange.Copy();

Excel.Range pSelection = myExcel.Rows[row2] as Excel.Range;

pSelection.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlCutCopyMode.xlCopy);

}

//复制并插入单行多次

public void CopyRownumOneRow(introw, int num)

{

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

{

Excel.Range pRange = myExcel.Rows[row] as Excel.Range;

pRange.Select();

pRange.Copy();

Excel.Range pSelection = myExcel.Selection as Excel.Range;

pSelection.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlCutCopyMode.xlCopy);

}

}

//获取指定单元格区域

publicExcel.Range getRange(int row1, int col1, int row2, int col2)

{

Excel.Range pRange = myExcel.get_Range(myExcel.Cells[row1, col1], myExcel.Cells[row2, col2]);

returnpRange;

}

//设置指定区域的数据

public void SetRange(introw1, int col1, introw2, int col2, object[,] dataArray)

{

Excel.Range pRange = myExcel.get_Range(myExcel.Cells[row1, col1], myExcel.Cells[row2, col2]);

if(row2 > row1)

{

CellsDrawFrame(row1, col1, row2, col2);//true,true,true,true,true,true,true,true,

}

pRange.Value = dataArray;

//pExcel.Range.EntireColumn.AutoFit();

}

//合并行,相同值合并,//根据指定列中的数据值合并行

public void MergeRow(intbeginRow, int endRow, intcolidx)

{

inti = beginRow;

stringText1 = getCellValue(i, colidx);

stringText2 = "";

while(i < endRow)

{

intK = i;

for(int j = i + 1; j <= endRow; j++)

{

Text2 = getCellValue(j, colidx);

if(Text1 != Text2)

{

K = j;

break;

}

}

if(K > i + 1)

{

CellsUnite(i, colidx, K - 1, colidx);

WriteData(Text1, i, colidx);

}

i = K;

Text1 = Text2;

}

}

/**////<summary>

///合并工作表中指定行数和列数数据相同的单元格

/// </summary>

/// <param name="sheetIndex">工作表索引</param>

/// <param name="beginRowIndex">开始行索引</param>

/// <param name="beginColumnIndex">开始列索引</param>

/// <param name="rowCount">要合并的行数</param>

/// <param name="columnCount">要合并的列数</param>

public void MergeWorkSheet(intbeginRowIndex, int beginColumnIndex, int rowCount, intcolumnCount)

{

//检查参数

if(columnCount < 1 || rowCount < 1)

return;

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

{

intmark = 0;            //标记比较数据中第一条记录位置

intmergeCount = 1;        //相同记录数，即要合并的行数

stringtext = "";

for(int row = 0; row < rowCount; row++)

{

stringprvName = "";

stringnextName = "";

//最后一行不用比较

if(row + 1 < rowCount)

{

for (int n = 0; n <= col; n++)

{

Excel.Range range = (Excel.Range)myExcel.Cells[row + beginRowIndex, n + beginColumnIndex];

range = (Excel.Range)range.MergeArea.get_Item(1, 1);

text = range.Text.ToString();

prvName = prvName + text;

range = (Excel.Range)myExcel.Cells[row + 1 + beginRowIndex, n + beginColumnIndex];

range = (Excel.Range)range.MergeArea.get_Item(1, 1);

nextName = nextName + range.Text.ToString();

}

if (prvName == nextName)

{

mergeCount++;

if (row == rowCount - 2)

{

CellsUnite(beginRowIndex + mark, beginColumnIndex + col, beginRowIndex + mark + mergeCount - 1, beginColumnIndex + col);

WriteData(text, beginRowIndex + mark, beginColumnIndex + col);

}

}

else

{

CellsUnite(beginRowIndex + mark, beginColumnIndex + col, beginRowIndex + mark + mergeCount - 1, beginColumnIndex + col);

WriteData(text, beginRowIndex + mark, beginColumnIndex + col);

mergeCount = 1;

mark = row + 1;

}

}

}

}

}

//复制工作表并添加到Excel文件

public void AddSheeetCopy(intcopynum)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(1);//从1开始

excelWorksheet.UsedRange.Copy();

intnum = copynum - myExcel.Sheets.Count;

if(num > 0)

{

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

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(2);

Worksheet2.Select();

myWorkBook.Sheets.Add();

}

}

else

{

//删除多余的

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

{

intsheetnum = myExcel.Sheets.Count;

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(sheetnum);

Worksheet2.Delete();

}

}

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

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(i + 1);

Worksheet2.Name = "JGB" + (i + 1).ToString();

}

for(int i = 1; i < copynum; i++)

{

Excel.Worksheet addWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(i + 1);

//addWorksheet.get_Range("A1").Select();

addWorksheet.Paste();

}

((Excel._Worksheet)excelWorksheet).Activate();

}

//特殊的拷贝

public void AddSheeetCopy2(intcopynum)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(3);//从1开始           

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

{

Copy(excelWorksheet, excelWorksheet);

}

for(int i = 0; i < copynum + 1; i++)

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(i + 3);

Worksheet2.Name = "JGB" + (i + 1).ToString();

}

((Excel._Worksheet)excelWorksheet).Activate();

}

//特殊的拷贝,相邻信息

public void AddSheeetCopy(intstartidx, int copynum, stringSheetName)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(startidx);//从1开始           

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

{

Copy(excelWorksheet, excelWorksheet);

}

for(int i = 0; i < copynum + 1; i++)

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(i + startidx);

Worksheet2.Name = SheetName + (i + 1).ToString();

}

((Excel._Worksheet)excelWorksheet).Activate();

}

/// <summary>

/// 根据指定的索引复制工作表。

/// </summary>

/// <param name="startidx">开始复制的工作表索引（基于1）。</param>

/// <param name="copynum">需要复制的数量。</param>

public void AddSheeetCopy(intstartidx, int copynum)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(startidx);//从1开始           

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

{

Copy(excelWorksheet, excelWorksheet);

}

((Excel._Worksheet)excelWorksheet).Activate();

}

//重命名所有工作表

public void Renamesheet()

{

intnum = myExcel.Sheets.Count;

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

{

stringSheetName = "Sheet"+ (i + 1).ToString();

SetSheetName(i + 1, SheetName);

}

}

/// <summary>

/// 设置指定索引的工作表名称。

/// </summary>

/// <param name="idx">工作表的索引。</param>

/// <param name="SheetName">新的工作表名称。</param>

public void SetSheetName(intidx, string SheetName)

{

Excel.Worksheet Worksheet = (Excel.Worksheet)myWorkBook.Sheets.get_Item(idx);

if(Worksheet.Name != SheetName)

try

{

Worksheet.Name = SheetName;

}

catch

{ }

}

//湖南地块拷贝

public void AddSheeetCopyDK(intcopynum, string SheetName)

{

AddSheeet(1, copynum);

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

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myWorkBook.Sheets.get_Item(1);//从1开始  

((Excel._Worksheet)excelWorksheet).Activate();

excelWorksheet.Cells.Select();

excelWorksheet.Cells.Copy();

Excel.Worksheet pWorksheet = (Excel.Worksheet)myWorkBook.Sheets.get_Item(i + 2);

pWorksheet.Select();

Excel.Range r1 = (Excel.Range)(pWorksheet.Cells[1, 1]);

r1.Select();

pWorksheet.Paste();

//Copy(excelWorksheet, pWorksheet);

}

for(int i = 2; i < copynum + 2; i++)

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myWorkBook.Sheets.get_Item(i);

Worksheet2.Name = SheetName + "(" + (i).ToString() + ")";//名字是”地块信息(1)“

}

//((Excel._Worksheet)excelWorksheet).Activate();

}

//湖南界址点拷贝,拷贝最后一页

public void AddSheeetCopyJZD(intcopynum, string SheetName)

{

intstartidx = myExcel.Sheets.Count;

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(startidx);//从1开始           

myWorkBook.Sheets.Add(Missing.Value, excelWorksheet, copynum);

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

{

((Excel._Worksheet)excelWorksheet).Activate();

excelWorksheet.Cells.Select();

excelWorksheet.Cells.Copy();

Excel.Worksheet pWorksheet = (Excel.Worksheet)myWorkBook.Sheets.get_Item(startidx + i + 1);

pWorksheet.Select();

Excel.Range r1 = (Excel.Range)(pWorksheet.Cells[1, 1]);

r1.Select();

pWorksheet.Paste();

}

for(int i = 0; i < copynum + 1; i++)

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(i + startidx);

Worksheet2.Name = SheetName + "(" + (i + 1).ToString() + ")";//名字是”界址信息(1)“

}

((Excel._Worksheet)excelWorksheet).Activate();

}

//特殊的拷贝,相邻信息

public void AddSheeetCopyLX(intcopynum)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(2);//从1开始           

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

{

Copy(excelWorksheet, excelWorksheet);

}

for(int i = 0; i < copynum + 1; i++)

{

Excel.Worksheet Worksheet2 = (Excel.Worksheet)myExcel.Sheets.get_Item(i + 2);

Worksheet2.Name = "LX" + (i + 1).ToString();

}

((Excel._Worksheet)excelWorksheet).Activate();

}

//工作表的拷贝

public void Copy(Excel.Worksheet Source, Excel.Worksheet Dest)//把Dest拷贝到Source的后面

{

Source.Select();

Dest.Copy(Missing.Value, Source);

}

//工作表的拷贝

public void Copy(Excel.Worksheet Dest)//把Dest拷贝到最后面

{

intsheetnum = myExcel.Sheets.Count;

Excel.Worksheet LastWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(sheetnum);

Dest.Copy(Missing.Value, LastWorksheet);

}

//单元格移动到最后

public void MoveLast(Excel.Worksheet Worksheet)

{

intsheetnum = myExcel.Sheets.Count;

Excel.Worksheet LastWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(sheetnum);

Worksheet.Move(Missing.Value, LastWorksheet);

}

//特殊的拷贝,拷贝前三页

public void AddSheeetCopy3(intcopynum)

{

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

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(1);//从1开始

Copy(excelWorksheet);

excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(2);//从2开始

Copy(excelWorksheet);

excelWorksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(3);//从2开始

Copy(excelWorksheet);

}

}

/// <summary>

/// 显示Excel

/// </summary>

public void ShowExcel()

{

myExcel.Visible = true;

}

//打印Excel

public void Print()

{

myExcel.ActiveWorkbook.PrintOut(Type.Missing, Type.Missing, 1);

}

/// <summary>

/// 将数据写入Excel

/// </summary>

/// <param name="data">要写入的二维数组数据</param>

/// <param name="startRow">Excel中的起始行</param>

/// <param name="startColumn">Excel中的起始列</param>

public void WriteData(string[,] data, int startRow, intstartColumn)

{

if(data == null) return;

introwNumber = data.GetLength(0);

intcolumnNumber = data.GetLength(1);

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

{

for(int j = 0; j < columnNumber; j++)

{

//在Excel中，如果某单元格以单引号“'”开头，表示该单元格为纯文本，因此，我们在每个单元格前面加单引号。

myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j];

}

}

}

/// <summary>

/// 将数据写指定行和列

/// </summary>

/// <param name="data">要写入的字符串</param>

/// <param name="starRow">写入的行</param>

/// <param name="startColumn">写入的列</param>

public void WriteData(stringdata, int row, intcolumn)

{

myExcel.Cells[row, column] = data;

}

//将数据写指定行和列,自动换行

public void WriteData2(stringdata, int row, intcolumn)

{

myExcel.Cells[row, column] = data;

Excel.Range r = (Excel.Range)(myExcel.Cells[row, column]);

r.WrapText = true;

r.ShrinkToFit = false;

r.IndentLevel = 0;

r.AddIndent = false;

r.Orientation = 0;

}

//写当前单元格

public void WriteData(stringdata)

{

myExcel.ActiveCell.Value2 = data;

}

privatevoid TaoonePrint(Excel.Worksheet worksheet, int idx)

{

intRnum = worksheet.UsedRange.Rows.Count;

if(Rnum < 10)

{

Rnum = 10;

}

if(Rnum > 100)

{

MessageBox.Show("第" + idx + "页，超过100行，有"+ Rnum + "行，空行太多，请自行删除");

}

intCnum = this.GetColumnNum();

if(Cnum > 50)

{

MessageBox.Show("第" + idx + "页，超过50列，有"+ Cnum + "列，空列太多，请自行删除");

}

for(int i = 1; i <= Rnum; i++)

{

for(int j = 1; j <= Cnum; j++)

{

stringstr = getCellValue(i, j);

if(str.EndsWith("0001J"))

{

continue;

}

if(i == 10 && j == 2 && str.IndexOf("年") == 4) continue;//1998年9月30日至2028年9月30日 不替换

if(str != "")

{

//MessageBox.Show(str);

if (str.IndexOf("[") == -1)

{

WriteData("", i, j);

}

}

}

}

CellsDrawFrameNotLine(1, 1, Rnum, Cnum);

}

//套打

public void TaoPrint(int start)

{

intnum = myExcel.Sheets.Count;

for(int i = start; i <= num; i++)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(i);

if(worksheet != null)

{

((Excel._Worksheet)worksheet).Activate();

TaoonePrint(worksheet, i);

}

}

}

/// 将数据写入Excel

/// </summary>

/// <param name="data">要写入的字符串</param>

/// <param name="starRow">写入的行</param>

/// <param name="startColumn">写入的列</param>

public void WriteData(stringdata, int row, intcolumn, bool XLine)

{

if(XLine)

{

if(data == "")

{

myExcel.Cells[row, column] = "/";

return;

}

}

myExcel.Cells[row, column] = data;

}

//将数据写指定行和列,自动换行

public void WriteDataWrapText(stringdata, int row, intcolumn)

{

myExcel.Cells[row, column] = data;

Excel.Range r = (Excel.Range)(myExcel.Cells[row, column]);

r.WrapText = true;

}

/// <summary>

/// 获得单元格的值

/// </summary>

/// <param name="data">要写入的字符串</param>

/// <param name="starRow">写入的行</param>

/// <param name="startColumn">写入的列</param>

public string getValue(introw, int column)

{

Excel.Range r = (Excel.Range)(myExcel.Cells[row, column]);

returnr.Text.ToString();

}

/// <summary>

/// 将数字写到指定单元格

/// </summary>

/// <param name="data">要写入的字符串</param>

/// <param name="starRow">写入的行</param>

/// <param name="startColumn">写入的列</param>

public void WriteData0(stringdata, int row, intcolumn)

{

if(data == "0.00")

{

data = "";

}

myExcel.Cells[row, column] = data;

}

/// <summary>

/// 将数据写入Excel

/// </summary>

/// <param name="data">要写入的字符串</param>

/// <param name="starRow">写入的行</param>

/// <param name="startColumn">写入的列</param>

public void RangleWriteData(Excel.Range pRangle, object[,] dataArray)

{

pRangle.Value2 = dataArray;

}

/// <summary>

/// 将数据写入Excel

/// </summary>

/// <param name="data">要写入的数据表</param>

/// <param name="startRow">Excel中的起始行</param>

/// <param name="startColumn">Excel中的起始列</param>

public void WriteData(System.Data.DataTable data, int startRow, intstartColumn)

{

for(int i = 0; i <= data.Rows.Count - 1; i++)

{

for(int j = 0; j <= data.Columns.Count - 1; j++)

{

//在Excel中，如果某单元格以单引号“'”开头，表示该单元格为纯文本，因此，我们在每个单元格前面加单引号。

myExcel.Cells[startRow + i, startColumn + j] = "'" + data.Rows[i][j].ToString();

}

}

}

/// <summary>

/// 读取指定单元格数据

/// </summary>

/// <param name="row">行序号</param>

/// <param name="column">列序号</param>

/// <returns>该格的数据</returns>

public string ReadData(introw, int column)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);

returnRange.Text.ToString();

}

//替换对应oldtext字符为newtext

public bool Replace(stringoldtext, string newtext)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

object what = oldtext; //查找字符串

object retxt = newtext; //替换字符串

try

{

boolb = worksheet.Cells.Replace(what, retxt, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value);

//cbjyq.MySpeech.MyMessageBox.Show(myExcel.ActiveCell.Text.ToString());

//cbjyq.MySpeech.MyMessageBox.Show(worksheet.Cells.Address);

returnb;

}

catch

{

returnfalse;

}

}

//替换并下划线

public bool Replace划下线(string oldtext, string newtext)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

object what = oldtext; //查找字符串

myExcel.Application.ReplaceFormat.Font.Subscript = false;

myExcel.Application.ReplaceFormat.Font.TintAndShade = 0;

object retxt = newtext; //替换字符串

boolb = worksheet.Cells.Replace(what, retxt, Missing.Value,

Missing.Value, Missing.Value, false,

false, true);

//Excel.Range pRange = myExcel.Selection as Excel.Range;

//pRange.Font.Underline = true;// xlUnderlineStyleSingle;

//cbjyq.MySpeech.MyMessageBox.Show(myExcel.ActiveCell.Text.ToString());

//cbjyq.MySpeech.MyMessageBox.Show(worksheet.Cells.Address);

returnb;

}

//查找oldtext，返回是否找到

public bool Find(stringoldtext)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

object what = oldtext; //查找字符串

Excel.Range pRange = worksheet.Cells.Find(what, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Excel.XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);

//cbjyq.MySpeech.MyMessageBox.Show(worksheet.Cells.Address);

returnpRange != null;//找到

}

//替换所有单元格，oldtext为newtext

public void ReplaceAll(stringoldtext, string newtext)

{

intnum = myExcel.Sheets.Count;

for(int i = 1; i <= num; i++)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Sheets.get_Item(i);

object what = oldtext; //查找字符串

object retxt = newtext; //替换字符串

try

{

worksheet.Cells.Replace(what, retxt.ToString(), Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value);

}

catch//超长字符串操作

{

Excel.Range oRange;

oRange = worksheet.Cells.Find(what, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Excel.XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);

if(oRange != null)

{

int r = oRange.Row;

int c = oRange.Column;

WriteData(retxt.ToString(), r, c);

}

//cbjyq.MySpeech.MyMessageBox.Show("有错误，正在处理中");

}

}

}

/// <summary>

/// 向Excel中插入图片

/// </summary>

/// <param name="pictureName">图片的绝对路径加文件名</param>

/// <param name="left">左边距</param>

/// <param name="top">右边距</param>

/// <param name="width">宽</param>

/// <param name="heigth">高</param>

public void InsertPictures(stringpictureName, int left, inttop, int width, intheigth, double clip)

{

if(!File.Exists(pictureName))

{

MessageBox.Show("文件:" + pictureName + "不存在");

return;

}

try

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

//worksheet.Shapes.AddPicture(

Microsoft.Office.Interop.Excel.Shape pshape = worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, width, heigth);

pshape.PictureFormat.CropBottom = (float)clip;

}

catch(Exception ex)

{

MessageBox.Show("加文件:" + pictureName + "错误:" + ex.Message.ToString());

}

}

/// <summary>

/// 重命名工作表

/// </summary>

/// <param name="sheetNum">工作表序号，从左到右，从1开始</param>

/// <param name="newSheetName">新的工作表名</param>

public void ReNameSheet(intsheetNum, string newSheetName)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];

worksheet.Name = newSheetName;

}

/// <summary>

/// 重命名工作表

/// </summary>

/// <param name="oldSheetName">原有工作表名</param>

/// <param name="newSheetName">新的工作表名</param>

public void ReNameSheet(stringoldSheetName, string newSheetName)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[oldSheetName];

worksheet.Name = newSheetName;

}

/// <summary>

/// 新建工作表

/// </summary>

/// <param name="sheetName">工作表名</param>

public void CreateWorkSheet(stringsheetName)

{

Excel.Worksheet newWorksheet = (Excel.Worksheet)myExcel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

newWorksheet.Name = sheetName;

}

/// <summary>

/// 激活指定工作表

/// </summary>

/// <param name="sheetName">工作表名</param>

public int ActivateSheet(stringsheetName)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName];

if(worksheet != null)

{

((Excel._Worksheet)worksheet).Activate();

returnworksheet.Index;

}

else

{

return-1;

}

}

//获得当前工作表Index

public int getIDX()

{

try

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

returnworksheet.Index;

}

catch

{

return-1;

}

}

//获得工作表个数

public int getSheetnum()

{

returnmyExcel.Worksheets.Count;

}

/// <summary>

/// 激活工作表

/// </summary>

/// <param name="sheetNum">工作表序号</param>

public bool ActivateSheet(intsheetNum)

{

try

{

if(myExcel.Worksheets.Count >= sheetNum)

{

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];

if(worksheet != null)

{

((Excel._Worksheet)worksheet).Activate();

return true;

}

}

//else

//{

returnfalse;

//MessageBox.Show(""+ sheetNum + "大于目前已有页码");

//}

}

catch

{

returnfalse;

}

}

//设置表头

public void PrintTitleRows()

{

Excel.Worksheet sheet = (Excel.Worksheet)myExcel.ActiveSheet;

sheet.PageSetup.PrintTitleRows = "$1:$2";

}

/// <summary>

/// 删除一个工作表

/// </summary>

/// <param name="SheetName">删除的工作表名</param>

public void DeleteSheet(intsheetNum)

{

((Excel.Worksheet)myWorkBook.Worksheets[sheetNum]).Delete();

}

/// <summary>

/// 删除一个工作表

/// </summary>

/// <param name="SheetName">删除的工作表序号</param>

public void DeleteSheet(stringsheetName)

{

try

{

((Excel.Worksheet)myWorkBook.Worksheets[sheetName]).Delete();

}

catch

{ }

}

/// <summary>

/// 合并单元格

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

public void CellsUnite(intstartRow, int startColumn, int endRow, intendColumn)

{

Excel.Range Range = myExcel.Range[myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]];//myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.MergeCells = true;

}

//合并单元格,并写文本

public void CellsUnite(intstartRow, int startColumn, int endRow, intendColumn, string Text)

{

CellsUnite(startRow, startColumn, endRow, endColumn);

WriteData(Text);

}

/// <summary>

/// 合并单元格

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

public void CellsShrinkToFit(intstartRow, int startColumn, int endRow, intendColumn)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.WrapText = false;

Range.ShrinkToFit = true;

}

//获得当前工作表的行数

public int getRowsCount()

{

Excel.Worksheet pWorksheet = (Excel.Worksheet)(myExcel.ActiveSheet);

returnpWorksheet.UsedRange.Rows.Count;

}

//获得当前工作表的列数

public int getColsCount()

{

Excel.Worksheet pWorksheet = (Excel.Worksheet)(myExcel.ActiveSheet);

returnpWorksheet.UsedRange.Columns.Count;

}

/// <summary>

/// 合并单元格,获得合并单元的行数和列数

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

public void getCellsUnite(intRow, int Column, out intRowCount, out int ColumnCount)

{

Excel.Range Range = (Excel.Range)(myExcel.Cells[Row, Column]);

RowCount = Range.MergeArea.Rows.Count;

ColumnCount = Range.MergeArea.Columns.Count;

}

/// <summary>

/// 单元格文字对齐方式

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="hAlign">水平对齐</param>

/// <param name="vAlign">垂直对齐</param>

public void CellsAlignment(intstartRow, int startColumn, int endRow, intendColumn, ExcelHAlign hAlign, ExcelVAlign vAlign)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.HorizontalAlignment = hAlign;

Range.VerticalAlignment = vAlign;

}

/// <summary>

/// 绘制指定单元格的边框

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

public void CellsDrawFrame(intstartRow, int startColumn, int endRow, intendColumn)

{

CellsDrawFrame(startRow, startColumn, endRow, endColumn,

//true, true, true, true, true, true, false, false,

true, true, true, true, true, true, false, false,

LineStyle.连续直线, BorderWeight.细, ColorIndex.自动);

}

/// <summary>

/// 绘制指定单元格的边框

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

public void CellsDrawFrameNotLine(intstartRow, int startColumn, int endRow, intendColumn)

{

CellsDrawFrame(startRow, startColumn, endRow, endColumn,

false, false, false, false, false, false, false, false,

LineStyle.无, BorderWeight.细, ColorIndex.自动);

}

/// <summary>

/// 绘制指定单元格的边框

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="isDrawTop">是否画上外框</param>

/// <param name="isDrawBottom">是否画下外框</param>

/// <param name="isDrawLeft">是否画左外框</param>

/// <param name="isDrawRight">是否画右外框</param>

/// <param name="isDrawHInside">是否画水平内框</param>

/// <param name="isDrawVInside">是否画垂直内框</param>

/// <param name="isDrawDown">是否画斜向下线</param>

/// <param name="isDrawUp">是否画斜向上线</param>

/// <param name="lineStyle">线类型</param>

/// <param name="borderWeight">线粗细</param>

/// <param name="color">线颜色</param>

public void CellsDrawFrame(intstartRow, int startColumn, int endRow, intendColumn,

boolisDrawTop, bool isDrawBottom, bool isDrawLeft, boolisDrawRight,

boolisDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, boolisDrawDiagonalUp,

LineStyle lineStyle, BorderWeight borderWeight, ColorIndex color)

{

//获取画边框的单元格

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

//清除所有边框

Range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.无;

Range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.无;

//以下是按参数画边框

if(isDrawTop)

{

Range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = color;

}

if(isDrawBottom)

{

Range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = color;

}

if(isDrawLeft)

{

Range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = color;

}

if(isDrawRight)

{

Range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = color;

}

if(isDrawVInside)

{

Range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = color;

}

if(isDrawHInside)

{

Range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = color;

}

if(isDrawDiagonalDown)

{

Range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlDiagonalDown].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlDiagonalDown].ColorIndex = color;

}

if(isDrawDiagonalUp)

{

Range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;

Range.Borders[Excel.XlBordersIndex.xlDiagonalUp].Weight = borderWeight;

Range.Borders[Excel.XlBordersIndex.xlDiagonalUp].ColorIndex = color;

}

}

/// <summary>

/// 单元格背景色及填充方式

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="color">颜色索引</param>

public void CellsBackColor(intstartRow, int startColumn, int endRow, intendColumn, ColorIndex color)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.Interior.ColorIndex = color;

Range.Interior.Pattern = Pattern.Solid;

}

/// <summary>

/// 删除行

/// </summary>

/// <param name="SheetName">删除的工作表名</param>

public void DeleteRow(introw)

{

Excel.Range pRange = myExcel.Rows[row] as Excel.Range;

pRange.Delete();

}

/// <summary>

/// 删除行

/// </summary>

/// <param name="SheetName">删除的工作表名</param>

public void DeleteSelectRow()

{

Excel.Range pRange = myExcel.get_Range("A91", "E182") as Excel.Range;

pRange.Delete();

}

/// <summary>

/// 删除列，isdel为false隐藏

/// </summary>

/// <param name="SheetName">删除的工作表名</param>

public void DeleteColunm(intcol, bool isdel)

{

Excel.Range pRange = myExcel.Columns[col] as Excel.Range;

if(isdel)

{

pRange.Delete();

}

else

{

pRange.Hidden = true;

}

}

/// 删除列

/// </summary>

/// <param name="SheetName">删除的工作表名</param>

public void DeleteColunm(intcol)

{

Excel.Range pRange = myExcel.Columns[col] as Excel.Range;

pRange.Delete();

}

/// <summary>

/// 隐藏列

/// </summary>

/// <param name="SheetName">删除的工作表名</param>

public void HiddenColunm(intcol)

{

Excel.Range pRange = myExcel.Columns[col] as Excel.Range;

pRange.Hidden = true;

}

public int GetRowNum()

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.ActiveSheet;

returnexcelWorksheet.UsedRange.Rows.Count;

}

public void ScreenUpdating(boolb) //会提高速度

{

try

{

myExcel.ScreenUpdating = b;

}

catch

{ }

}

//打印设置

public void PrintPageSetup(string Left, stringRight)

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.ActiveSheet;

excelWorksheet.PageSetup.PrintTitleRows = "$1:$5";

excelWorksheet.PageSetup.PrintTitleColumns = "";

excelWorksheet.PageSetup.PrintArea = "";

excelWorksheet.PageSetup.LeftFooter = Left;

excelWorksheet.PageSetup.RightFooter = Right;

}

//获得字体大小

public double getFontNameSize(intstartRow, int startColumn, int endRow, intendColumn)

{

Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

returnConvert.ToDouble(range.Font.Size);

}

//设置字体大小

public void setFontNameSize(intstartRow, int startColumn, int endRow, intendColumn, double FontSize)

{

Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

range.Font.Size = FontSize;

}

//设置小数位数

public void SetXZNUM(intcolumn, int xznum)

{

try

{

stringstrStartColumn = GetColumnName(column);

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strStartColumn, System.Type.Missing];

stringstr = "";

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

{

str = str + "0";

}

range.NumberFormatLocal = "0." + str + "_);[红色](0." + str + ")";

}

catch(Exception e)

{

MessageBox.Show("设置小数位数，错误:" + e.Message.ToString());

}

}

//获得单元的值

public string getCellValue(introw, int colnum)

{

try

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range r1 = (Excel.Range)(excelWorksheet.Cells[row, colnum]);

returnr1.Text.ToString().Trim();

}

catch

{

return"";

}

}

//获得列数

public int GetColumnNum()

{

Excel.Worksheet excelWorksheet = (Excel.Worksheet)myExcel.ActiveSheet;

returnexcelWorksheet.UsedRange.Columns.Count;

}

/// <summary>

/// 单元格背景色及填充方式

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="color">颜色索引</param>

/// <param name="pattern">填充方式</param>

public void CellsBackColor(intstartRow, int startColumn, int endRow, intendColumn, ColorIndex color, Pattern pattern)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.Interior.ColorIndex = color;

Range.Interior.Pattern = pattern;

}

/// <summary>

/// 设置行高

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="endRow">结束行</param>

/// <param name="height">行高</param>

public void SetRowHeight(intstartRow, int endRow, double height)

{

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];

Range.RowHeight = height;

}

/// <summary>

/// 设置行高

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="endRow">结束行</param>

/// <param name="height">行高</param>

public double getRowHeight(intstartRow)

{

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + startRow.ToString(), System.Type.Missing];

returnConvert.ToDouble(Range.RowHeight);

}

/// <summary>

/// 自动调整行高

/// </summary>

/// <param name="columnNum">列号</param>

public void RowAutoFit(introwNum)

{

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];

//Range.EntireColumn.AutoFit();

Range.Rows.AutoFit();

//myExcel.Selection

//Selection.Rows.AutoFit;

}

/// <summary>

/// 设置列宽

/// </summary>

/// <param name="startColumn">起始列(列对应的字母)</param>

/// <param name="endColumn">结束列(列对应的字母)</param>

/// <param name="width"></param>

public void SetColumnWidth(stringstartColumn, string endColumn, double width)

{

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];

Range.ColumnWidth = width;

}

/// <summary>

/// 设置列宽

/// </summary>

/// <param name="startColumn">起始列</param>

/// <param name="endColumn">结束列</param>

/// <param name="width"></param>

public void SetColumnWidth(intstartColumn, int endColumn, double width)

{

stringstrStartColumn = GetColumnName(startColumn);

stringstrEndColumn = GetColumnName(endColumn);

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];

Range.ColumnWidth = width;

}

/// <summary>

/// 获得列宽

/// </summary>

/// <param name="startColumn">起始列</param>

/// <param name="endColumn">结束列</param>

/// <param name="width"></param>

public double getColumnWidth(intstartColumn)

{

stringstrStartColumn = GetColumnName(startColumn);

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strStartColumn, System.Type.Missing];

returnConvert.ToDouble(Range.ColumnWidth);

}

/// <summary>

/// 自动调整列宽

/// </summary>

/// <param name="columnNum">列号</param>

public void ColumnAutoFit(stringcolumn)

{

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];

Range.EntireColumn.AutoFit();

}

/// <summary>

/// 自动调整列宽

/// </summary>

/// <param name="columnNum">列号</param>

public void ColumnAutoFit(intcolumnNum)

{

stringstrcolumnNum = GetColumnName(columnNum);

//获取当前正在使用的工作表

Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;

Excel.Range Range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];

Range.EntireColumn.AutoFit();

}

/// <summary>

/// 字体颜色

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="color">颜色索引</param>

public void FontColor(intstartRow, int startColumn, int endRow, intendColumn, ColorIndex color)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.Font.ColorIndex = color;

}

/// <summary>

/// 字体样式(加粗,斜体,下划线)

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="isBold">是否加粗</param>

/// <param name="isItalic">是否斜体</param>

/// <param name="underline">下划线类型</param>

public void FontStyle(intstartRow, int startColumn, int endRow, intendColumn, bool isBold, bool isItalic, UnderlineStyle underline)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

Range.Font.Bold = isBold;

Range.Font.Underline = underline;

Range.Font.Italic = isItalic;

}

//设置下划线

public void FontStyle(intstart, int len)

{

myExcel.ActiveCell.Characters[start, len].Font.Underline = true;

}

/// <summary>

/// 单元格字体名称及大小

/// </summary>

/// <param name="startRow">起始行</param>

/// <param name="startColumn">起始列</param>

/// <param name="endRow">结束行</param>

/// <param name="endColumn">结束列</param>

/// <param name="fontName">字体名称</param>

/// <param name="fontSize">字体大小</param>

public void FontNameSize(intstartRow, int startColumn, int endRow, intendColumn, string fontName, int fontSize)

{

Excel.Range Range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);

MessageBox.Show(Range.Text.ToString());

Range.Font.Name = fontName;

Range.Font.Size = fontSize;

}

// 以下的方法用于在Excel中进行字体操作

// 设置指定单元格的字体名称和大小，并将该单元格内的某个字符更改为"Wingdings"字体。

public void FontNameSize(stringCellstr, string fontName, int fontSize, intstartidx)

{

Excel.Range Range = myExcel.get_Range(Cellstr);

Range.Select();

Range.Font.Name = fontName;

Range.Font.Size = fontSize;

myExcel.ActiveCell.Characters[startidx, 1].Font.Name = "Wingdings";

}

// 重载方法，通过行列索引来指定单元格，并设置其字体名称和大小，同时将其中的某个字符更改为"Wingdings"字体。

public void FontNameSize(intRowIdx, int Colidx, stringfontName, int fontSize, int startidx)

{

Excel.Range Range = (Excel.Range)myExcel.Cells[RowIdx, Colidx];

Range.Select();

Range.Font.Name = fontName;

Range.Font.Size = fontSize;

myExcel.ActiveCell.Characters[startidx, 1].Font.Name = "Wingdings";

}

// 重载方法，为指定单元格设置字体名称和大小，并将其中多个字符更改为"Wingdings"字体。

public void FontNameSize(stringCellstr, string fontName, int fontSize, List<int> startidx)

{

Excel.Range Range = myExcel.get_Range(Cellstr);

Range.Select();

Range.Font.Name = fontName;

Range.Font.Size = fontSize;

for(int i = 0; i < startidx.Count; i++)

{

myExcel.ActiveCell.Characters[startidx[i], 1].Font.Name = "Wingdings";

}

}

// 为指定单元格内的一段字符设置下划线。

public void FontUnderline(stringCellstr, int startidx, intlen)

{

Excel.Range Range = myExcel.get_Range(Cellstr);

Range.Select();

myExcel.ActiveCell.Characters[startidx, len].Font.Underline = true;

}

// 重载方法，为当前激活的单元格设置字体名称和大小，并将其中的某个字符更改为"Wingdings"字体。

public void FontNameSize(stringfontName, int fontSize, int startidx)

{

Excel.Range Range = myExcel.ActiveCell;

Range.Select();

Range.Font.Name = fontName;

Range.Font.Size = fontSize;

myExcel.ActiveCell.Characters[startidx, 1].Font.Name = "Wingdings";

}

// 为Excel中当前选中的范围设置字体名称和大小。

public void FontNameSize(stringfontName, int fontSize)

{

Excel.Range Range = myExcel.Selection as Excel.Range;

Range.Font.Name = fontName;

Range.Font.Size = fontSize;

}

/// <summary>

/// 打开一个存在的Excel文件

/// </summary>

/// <param name="fileName">Excel完整路径加文件名</param>

public void Open(stringfileName)

{

Opennum++;

if(!File.Exists(fileName))

{

MessageBox.Show("文件" + fileName + "不存在");

throw newException("文件"+ fileName + "不存在");

}

else

{

try

{

myWorkBook = myExcel.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

//myWorkBook = myExcel.Workbooks.Add(fileName);

myFileName = fileName;

//myExcel.Worksheets("sheet1").Activate;

//eole.Worksheets("sheet1").Activate

}

catch(Exception ex)

{

MessageBox.Show("打开:" + fileName + "失败"+ ex.Message.ToString());

}

}

}

/// <summary>

/// 保存Excel

/// </summary>

/// <returns>保存成功返回True</returns>

public bool Save()

{

if(myFileName == "")

{

returnfalse;

}

else

{

try

{

myWorkBook.Save();

//myWorkBook.Close();

returntrue;

}

catch// (Exceptionex)

{

returnfalse;

}

}

}

public void DrawFrame斜杠(int row, int column)

{

//获取画边框的单元格

Excel.Range Range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);

Range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlContinuous; //斜线，由Excel的宏代码翻译而来

}

privatestring Del反斜杠(string fileName)

{

fileName = fileName.Replace("//", "/");

fileName = fileName.Replace("//", "/");

fileName = fileName.Replace("/", "\\");

fileName = fileName.Replace("\\\\", "\\");

fileName = fileName.Replace("\\\\", "\\");

returnfileName;

}

//        object FileFormat=Word.WdSaveFormat.wdFormatDocument;//改变文档格式，不能设为wdFormatDocumentDefault

//doc.SaveAs(reffileName,ref FileFormat,ref optional,ref optional,ref optional,refoptional, ref optional, ref optional, ref optional,

//                    refoptional, ref optional, ref optional,

//                    refoptional, ref optional, ref optional, ref optional);

//        /// <summary>

/// Excel文档另存为

/// </summary>

/// <param name="fileName">保存完整路径加文件名</param>

/// <returns>保存成功返回True</returns>

public bool SaveAs(stringfileName)

{

fileName = Del反斜杠(fileName);

try

{

stringpath = Path.GetDirectoryName(fileName);

if(!Directory.Exists(path))

{

Directory.CreateDirectory(path);

}

if(File.Exists(fileName))

{

File.Delete(fileName);

}

if(myExcel.Visible)

{

ScrollTopLeft();

}

stringExtFile = Path.GetExtension(fileName).ToUpper();

if(ExtFile == ".XLSX")

{

object FileFormat = Excel.XlFileFormat.xlOpenXMLWorkbook;

object CreateBackup = false;

myWorkBook.SaveAs(fileName, FileFormat, Type.Missing,

Type.Missing, Type.Missing, CreateBackup, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing);

}

else