文件流操作(2)——.NET开发不可不知、不可不用的辅助类（三）（报表导出---终结版）_综合

.NET导出报表一般是采用导出Excel报表的方式输出内容。而这又分为两种方式：使用Excel模板方式和使用网页输出Excel格式两种。
首先介绍简单的一种，网页输出Excel内容，这种不需要引用Excel的程序集。

    /// <summary>/// 报表导出辅助类/// </summary>public class ExportToExcel{#region 字段信息private const string C_HTTP_HEADER_CONTENT = "Content-Disposition";private const string C_HTTP_ATTACHMENT = "attachment;filename=";private const string C_HTTP_CONTENT_TYPE_EXCEL = "application/ms-excel";private string charSet = "utf-8";private string fileName = "Report";private string title = "";private DataTable sourceTable;/// <summary>/// 输出的字符集，默认为gb2312/// </summary>public string CharSet{get { return charSet; }set { charSet = value; }}/// <summary>/// 输出的Excel报表文件名称/// </summary>public string FileName{get { return fileName; }set { fileName = value; }}/// <summary>/// 报表内容的抬头/// </summary>public string Title{get { return title; }set { title = value; }}/// <summary>/// 报表数据的DataTable/// </summary>public DataTable SourceTable{get { return sourceTable; }set { sourceTable = value; }}#endregion        public ExportToExcel(){}/// <summary>/// 带参数的构造函数/// </summary>/// <param name="fileName">导出的Excel文件名</param>/// <param name="sourceTable">源数据DataTable</param>/// <param name="title">报表的抬头</param>public ExportToExcel(string fileName, DataTable sourceTable, string title){this.fileName = fileName;this.sourceTable = sourceTable;this.title = title;}public void ExportReport(){if (SourceTable == null || SourceTable.Rows.Count == 0){return;}DataGrid dataGrid = new DataGrid();dataGrid.DataSource = sourceTable;dataGrid.DataBind();HttpResponse Response = HttpContext.Current.Response;Response.Clear();Response.Buffer = true;Response.AddHeader(C_HTTP_HEADER_CONTENT, C_HTTP_ATTACHMENT + HttpUtility.UrlEncode(fileName + ".xls"));Response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL;Response.ContentEncoding = Encoding.GetEncoding("gb2312");Response.Charset = charSet;StringWriter oStringWriter = new StringWriter();HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);dataGrid.RenderControl(oHtmlTextWriter);string str = oStringWriter.ToString();int trPosition = str.IndexOf("<tr>", 0);string str1 = str.Substring(0, trPosition - 1);string str2 = str.Substring(trPosition, str.Length - trPosition);string str3 = "\r\n\t<tr>";str3 += "\r\n\t\t<td align=\"center\" colspan=\"" + sourceTable.Rows.Count +"\" style=\"font-size:14pt;    font-weight:bolder;height:30px;\">" + title + "</td>";str3 += "\r\n\t</tr>";Response.Write(str1 + str3 + str2);Response.End();}}

使用时候代码如下：

        private void btnExport2_Click(object sender, EventArgs e){DataTable table = SelectAll().Tables[0];ExportToExcel export = new ExportToExcel("TestExport", table, "TestExport");export.ExportReport();}public static DataSet SelectAll(){string sqlCommand = " Select ID, Name, Age, Man, CONVERT(CHAR(10), Birthday ,120) as Birthday from Test";DataSet ds = new DataSet();string connectionString = "Server=localhost;Database=Test;uid=sa;pwd=123456";SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand, connectionString);adapter.Fill(ds);return ds;}

另外一种就是先定义好Excel模板，然后输出指定格式的内容，这些内容通过开始单元格名称定位，然后写入内容，但是这种功能比较强大，输出的Excel内容也比较整齐。
1. 首先在Web.Config中配置下
<system.web>
<identity impersonate="true"></identity>
</system.web>
2. 创建一个Excel模板文件，如下图所示，当然这个是简单的Excel模板，你可以定义很复杂

3. 在网站的根目录中创建一个Temp目录，给EveryOne读写权限，当然你也可以给AuthenticatedUsers
4. 辅助类代码

    /// <summary>/// 报表导出基类/// </summary>public abstract class BaseReport{#region 变量及属性protected const string C_HTTP_HEADER_CONTENT = "Content-Disposition";protected const string C_HTTP_ATTACHMENT = "attachment;filename=";protected const string C_HTTP_INLINE = "inline;filename=";protected const string C_HTTP_CONTENT_TYPE_EXCEL = "application/ms-excel";protected const string C_HTTP_CONTENT_LENGTH = "Content-Length";protected const string C_ERROR_NO_RESULT = "Data not found.";protected string CharSet = "utf-8";protected string fileName;protected string sheetName; //表名称private ExcelHelper excelHelper;#endregionpublic BaseReport(){excelHelper = new ExcelHelper(false);}/// <summary>/// 打开Excel文件和关闭Excel/// </summary>        /// <returns>返回OK表示成功</returns>protected virtual bool OpenFile(){return excelHelper.OpenFile(fileName);}/// <summary>/// 关闭工作薄和excel文件/// </summary>protected virtual void CloseFile(){excelHelper.stopExcel();}/// <summary>/// 导出EXCEL文件/// </summary>protected virtual void ExportFile(){string tempFileName = HttpContext.Current.Request.PhysicalApplicationPath + @"Temp\" + sheetName.Replace(".xls", "");string SaveFileName = tempFileName + DateTime.Now.ToLongDateString() +DateTime.Now.ToLongTimeString().Replace(":", "-") + ".xls";excelHelper.SaveAsFile(SaveFileName);CloseFile();HttpResponse Response = HttpContext.Current.Response;Response.Clear();Response.Buffer = true;Response.AddHeader(C_HTTP_HEADER_CONTENT,C_HTTP_ATTACHMENT + HttpUtility.UrlEncode(DateTime.Now.ToLongDateString() + sheetName));Response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL;Response.ContentEncoding = Encoding.GetEncoding("gb2312");Response.Charset = CharSet;Response.WriteFile(SaveFileName);Response.Flush();Response.Clear();File.Delete(SaveFileName);}/// <summary>/// 填充表单数据到excel中/// </summary>/// <param name="GotoCell">定义的首个Cell名称</param>/// <param name="dt">数据表Datatable</param>protected virtual void FillCell(string GotoCell, DataTable dt){int BeginRow = 2;int RowCount = dt.Rows.Count;Range rgFill = excelHelper.GotoCell(GotoCell);if (RowCount > BeginRow){excelHelper.InsertRows(rgFill.Row + 1, RowCount - BeginRow); //从定位处的下一行的上面插入新行}//Fillif (RowCount > 0){excelHelper.DataTableToExcelofObj(dt, excelHelper.IntToLetter(rgFill.Column) + rgFill.Row.ToString(), false);}}private void AppendTitle(string titleAppendix){if (titleAppendix != null && titleAppendix != string.Empty){try{excelHelper.AppendToExcel(titleAppendix, "Title");}catch (Exception ex){throw new Exception("您没有指定一个Title的单元格", ex);}}}/// <summary>/// 写入内容/// </summary>public virtual void ExportExcelFile(){ExportExcelFile(string.Empty);}/// <summary>/// 写入内容并追加标题内容/// </summary>/// <param name="titleAppendix">追加在Title后面的内容（一般如年月份）</param>public virtual void ExportExcelFile(string titleAppendix){try{OpenFile();AppendTitle(titleAppendix);FillFile();ExportFile();}catch //(Exception ex){CloseFile();throw;}}protected virtual void FillFile(){}}

    /// <summary>///通用的报表导出类/// </summary>/// <example>/// <code>/// DataTable dt = InitTableData(); //InitTableData为自定义获取数据表的函数///    CommonExport report = new CommonExport(dt, "架空线.xls", "Start"); //Start是Excel一个单元格名称/// report.ExportExcelFile();/// </code>/// </example>public class CommonExport : BaseReport{private DataTable sourceTable;private string startCellName;/// <summary>/// 构造函数/// </summary>/// <param name="sourceTable">要导出的DataTable对象</param>/// <param name="excelFileName">相对于根目录的文件路径，如Model/Test.xls</param>/// <param name="startCellName">开始的单元格名称</param>public CommonExport(DataTable sourceTable, string excelFileName, string startCellName){fileName = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, excelFileName);sheetName = Path.GetFileName(fileName);this.sourceTable = sourceTable;this.startCellName = startCellName;}/// <summary>/// 填写文件/// </summary>protected override void FillFile(){FillCell(startCellName, sourceTable);}

    /// <summary>/// Excel帮助类/// </summary>internal class ExcelHelper : IDisposable{#region 一般的属性变量private Application excelApp = null;private Windows excelWindows = null;private Window excelActiveWindow = null;private Workbooks excelWorkbooks = null;private Workbook excelWorkbook = null;private Sheets excelSheets = null;private Worksheet excelWorksheet = null;private static object m_missing = Missing.Value;private static object m_visible = true;private static object m_false = false;private static object m_true = true;private bool m_app_visible = false;private object m_filename;#endregion#region 打开工作薄变量private object _update_links = 0;private object _read_only = m_false;private object _format = 1;private object _password = m_missing;private object _write_res_password = m_missing;private object _ignore_read_only_recommend = m_true;private object _origin = m_missing;private object _delimiter = m_missing;private object _editable = m_false;private object _notify = m_false;private object _converter = m_missing;private object _add_to_mru = m_false;private object _local = m_false;private object _corrupt_load = m_false;#endregion#region 关闭工作薄变量private object _save_changes = m_false;private object _route_workbook = m_false;#endregion/// <summary>/// 当前工作薄/// </summary>public Workbook CurrentExcelWorkBook{get { return excelWorkbook; }set { excelWorkbook = value; }}/// <summary>/// 释放对象内存，推出进程/// </summary>/// <param name="obj"></param>private void NAR(object obj){try{Marshal.ReleaseComObject(obj);}catch{}finally{obj = null;}}public ExcelHelper(){StartExcel();}/// <summary>/// 确定Excel打开是否可见/// </summary>/// <param name="visible">true为可见</param>public ExcelHelper(bool visible){m_app_visible = visible;StartExcel();}/// <summary>/// 开始Excel应用程序/// </summary>private void StartExcel(){if (excelApp == null){excelApp = new ApplicationClass();}// Excel是否可见excelApp.Visible = m_app_visible;}public void Dispose(){stopExcel();GC.SuppressFinalize(this);}#region 打开、保存、关闭Excel文件/// <summary>/// 打开Excel文件和关闭Excel/// </summary>/// <param name="fileName">文件名</param>/// <returns>返回OK表示成功</returns>public bool OpenFile(string fileName){return OpenFile(fileName, string.Empty);}/// <summary>/// 打开Excel文件/// </summary>/// <param name="fileName">文件名</param>/// <param name="password">密码</param>/// <returns>返回OK表示成功</returns>public bool OpenFile(string fileName, string password){m_filename = fileName;if (password.Length > 0){_password = password;}try{// 打开工作薄 excelWorkbook = excelApp.Workbooks.Open(fileName,_update_links, _read_only, _format, _password, _write_res_password,_ignore_read_only_recommend, _origin, _delimiter, _editable, _notify,_converter, _add_to_mru, _local, _corrupt_load);excelSheets = excelWorkbook.Worksheets;excelWorksheet = (Worksheet) excelSheets.get_Item(1);}catch{CloseFile();return false;}return true;}/// <summary>/// 关闭工作薄/// </summary>public void CloseFile(){foreach (Workbook workbook in excelWorkbooks){workbook.Close(_save_changes, m_filename, _route_workbook);NAR(workbook);}}public void SaveFile(string workbook){FindExcelWorkbook(workbook);excelWorkbook.Save();}/// <summary>/// 保存文件/// </summary>/// <param name="outputFile">输出的文件名</param>public void SaveAsFile(string outputFile){SaveAsFile(string.Empty, outputFile);}/// <summary>/// 保存指定工作薄的文件/// </summary>/// <param name="workbook">工作薄</param>/// <param name="outputFile">输出的文件名</param>public void SaveAsFile(string workbook, string outputFile){if (File.Exists(outputFile)){try{File.Delete(outputFile);}catch{return;}}if (workbook != string.Empty){FindExcelWorkbook(workbook);}excelWorkbook.SaveAs(outputFile,Type.Missing, _password, _write_res_password, Type.Missing, Type.Missing,XlSaveAsAccessMode.xlExclusive,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);}/// <summary>/// 杀掉Excel进程.退出Excel应用程序./// </summary>public void stopExcel(){excelApp.Quit();NAR(excelSheets);NAR(excelWorksheet);NAR(excelWorkbooks);NAR(excelWorkbook);NAR(excelWindows);NAR(excelActiveWindow);NAR(excelApp);GC.Collect();if (excelApp != null){Process[] pProcess;pProcess = Process.GetProcessesByName("EXCEL");pProcess[0].Kill();}}#endregion        #region windows窗口，workbook工作薄，worksheet工作区操作/// <summary>/// 得到工作薄的工作区集合/// </summary>public void GetExcelSheets(){if (excelWorkbook != null){excelSheets = excelWorkbook.Worksheets;}}/// <summary>/// 找到活动的excel window/// </summary>/// <param name="workWindowName">窗口名称</param>/// <returns></returns>public bool FindExcelWindow(string workWindowName){bool WINDOW_FOUND = false;excelWindows = excelApp.Windows;if (excelWindows != null){for (int i = 1; i < excelWindows.Count; i++){excelActiveWindow = excelWindows.get_Item(i);if (excelActiveWindow.Caption.ToString().Equals(workWindowName)){excelActiveWindow.Activate();WINDOW_FOUND = true;break;}}}return WINDOW_FOUND;}/// <summary>/// 查找工作薄/// </summary>/// <param name="workbookName">工作薄名</param>/// <returns>true为发现</returns>public bool FindExcelWorkbook(string workbookName){bool WORKBOOK_FOUND = false;excelWorkbooks = excelApp.Workbooks;if (excelWorkbooks != null){for (int i = 1; i < excelWorkbooks.Count; i++){excelWorkbook = excelWorkbooks.get_Item(i);if (excelWorkbook.Name.Equals(workbookName)){excelWorkbook.Activate();WORKBOOK_FOUND = true;break;}}}return WORKBOOK_FOUND;}/// <summary>/// 查找工作区/// </summary>/// <param name="worksheetName"></param>/// <returns>true为发现</returns>public bool FindExcelWorksheet(string worksheetName){bool SHEET_FOUND = false;excelSheets = excelWorkbook.Worksheets;if (excelSheets != null){for (int i = 1; i <= excelSheets.Count; i++){excelWorksheet = (Worksheet) excelSheets.get_Item((object) i);if (excelWorksheet.Name.Equals(worksheetName)){excelWorksheet.Activate();SHEET_FOUND = true;break;}}}return SHEET_FOUND;}#endregion#region 行列操作/// <summary>/// 得到工作区的选择范围的数组/// </summary>public string[] GetRange(string startCell, string endCell){Range workingRangeCells = excelWorksheet.get_Range(startCell, endCell);workingRangeCells.Select();Array array = (Array) workingRangeCells.Cells.Value2;string[] arrayS = ConvertToStringArray(array);return arrayS;}/// <summary>/// 将二维数组数据写入Excel文件（不分页）/// </summary>public void ArrayToExcel(string[,] arr, string getCell){int rowCount = arr.GetLength(0); //二维数组行数（一维长度）int colCount = arr.GetLength(1); //二维数据列数（二维长度）Range range = excelWorksheet.get_Range(getCell, Type.Missing);range = range.get_Resize(rowCount, colCount);range.HorizontalAlignment = XlHAlign.xlHAlignCenter;range.VerticalAlignment = XlVAlign.xlVAlignCenter;range.set_Value(Missing.Value, arr);}public void ArrayToExcel(object[,] arr, string getCell){int rowCount = arr.GetLength(0); //二维数组行数（一维长度）int colCount = arr.GetLength(1); //二维数据列数（二维长度）Range range = excelWorksheet.get_Range(getCell, Type.Missing);range = range.get_Resize(rowCount, colCount);range.HorizontalAlignment = XlHAlign.xlHAlignCenter;range.VerticalAlignment = XlVAlign.xlVAlignCenter;range.Value2 = arr;//range.set_Value(System.Reflection.Missing.Value,arr);}/// <summary>/// 合并单元格/// </summary>/// <param name="startCell">开始Cell</param>/// <param name="endCell">结束Cell</param>/// <param name="text">填写文字</param>public void MergeCell(string startCell, string endCell, string text){MergeCell(string.Empty, startCell, endCell, text);}/// <summary>/// 合并单元格/// </summary>/// <param name="workbookName"></param>/// <param name="startCell"></param>/// <param name="endCell"></param>/// <param name="text"></param>public void MergeCell(string workbookName, string startCell, string endCell, string text){if (workbookName != string.Empty)FindExcelWorkbook(workbookName);Range range = excelWorksheet.get_Range(startCell, endCell);range.ClearContents();range.MergeCells = true;range.Value2 = text;range.HorizontalAlignment = XlHAlign.xlHAlignCenter;range.VerticalAlignment = XlVAlign.xlVAlignCenter;}/// <summary>/// 添加样式/// </summary>/// <param name="styleName">样式名</param>/// <param name="fontName">字体名</param>/// <param name="fontSize">字体大小</param>/// <param name="fontColor">字体Color(0-255)</param>/// <param name="interiorColor">Range的填充Color(0-255)</param>public void AddStyle(string styleName, string fontName, int fontSize, int fontColor, int interiorColor){try{Style existStyle = excelWorkbook.Styles[styleName];return;}catch{}Style style = excelWorkbook.Styles.Add(styleName, Type.Missing);style.Font.Name = fontName;style.Font.Size = fontSize;if (fontColor >= 0 && fontColor <= 255){style.Font.Color = fontColor;}if (fontColor >= 0 && fontColor <= 255){style.Interior.Color = fontColor;}style.Interior.Pattern = XlPattern.xlPatternSolid;}/// <summary>/// 应用样式/// </summary>/// <param name="startCell">Range的开始</param>/// <param name="endCell">Range的结束</param>/// <param name="styleName">样式名</param>public void ApplyStyle(string startCell, string endCell, string styleName){Style style;try{style = excelWorkbook.Styles[styleName];}catch{return;}Range workingRangeCells = excelWorksheet.get_Range(startCell, endCell);workingRangeCells.Style = style;}/// <summary>/// 插行（在指定行上面插入指定数量行）/// </summary>/// <param name="rowIndex">行开始Index</param>public void InsertRows(int rowIndex){try{Range range = (Range) excelWorksheet.Rows[rowIndex, Type.Missing];range.Insert(XlDirection.xlDown, Type.Missing);}catch{return;}}/// <summary>/// 插行（在指定行上面插入指定数量行）/// </summary>/// <param name="rowIndex">行开始Index</param>/// <param name="count">插入的行数 </param>    public void InsertRows(int rowIndex, int count){try{for (int i = 0; i < count; i++){Range range = (Range) excelWorksheet.Rows[rowIndex, Type.Missing];range.Insert(XlDirection.xlDown, Type.Missing);}}catch{return;}}/// <summary>/// 插列（在指定列右边插入指定数量列）/// </summary>/// <param name="columnIndex">列开始Index</param>public void InsertColumns(int columnIndex){try{Range range = (Range) excelWorksheet.Columns[IntToLetter(columnIndex), Type.Missing];range.Insert(XlDirection.xlToLeft, Type.Missing);}catch{return;}}/// <summary>/// 指定Cell格填充/// </summary>/// <param name="text">填充内容</param>/// <param name="getCell">Cell位置</param>public void InsertToExcel(string text, string getCell){Range range = excelWorksheet.get_Range(getCell, Type.Missing);range.Value2 = text;}public void InsertToExcel(object text, string getCell){Range range = excelWorksheet.get_Range(getCell, Type.Missing);range.Value2 = text;}/// <summary>/// 往指定Cell格后面追加填充/// </summary>/// <param name="text">追加填充的内容</param>/// <param name="getCell">Cell位置</param>public void AppendToExcel(string text, string getCell){Range range = excelWorksheet.get_Range(getCell, Type.Missing);range.Value2 = range.Value2 + text;}/// <summary>/// 删除行/// </summary>/// <param name="rowIndex">行Index</param>/// <param name="count">行数</param>public void DeleteRows(int rowIndex, int count){try{Range range = (Range) excelWorksheet.Rows[rowIndex + ":" + (rowIndex + count - 1), Type.Missing];range.Delete(XlDirection.xlUp);}catch{return;}}/// <summary>/// 删除列/// </summary>/// <param name="columnIndex">列Index</param>/// <param name="count">列数</param>public void DeleteColumns(int columnIndex, int count){try{string cells = IntToLetter(columnIndex) + ":" + IntToLetter(columnIndex + count - 1);Range range = (Range) excelWorksheet.Columns[cells, Type.Missing];range.Delete(XlDirection.xlDown);}catch{return;}}/// <summary>/// 将Excel列的整数索引值转换为字符索引值/// </summary>/// <param name="n"></param>/// <returns></returns>public string IntToLetter(int n){if (n > 256){throw new Exception("索引超出范围，Excel的列索引不能超过256！");}int i = Convert.ToInt32(n / 26);int j = n % 26;char c1 = Convert.ToChar(i + 64);char c2 = Convert.ToChar(j + 64);if (n > 26){return c1.ToString() + c2.ToString();}else if (n == 26){return "Z";}else{return c2.ToString();}}/// <summary>/// 将Excel列的字母索引值转换成整数索引值/// </summary>/// <param name="letter"></param>/// <returns></returns>public int LetterToInt(string letter){if (letter.Trim().Length == 0){throw new Exception("不接受空字符串！");}int n = 0;if (letter.Length >= 2){char c1 = letter.ToCharArray(0, 2)[0];char c2 = letter.ToCharArray(0, 2)[1];if (!char.IsLetter(c1) || !char.IsLetter(c2)){throw new Exception("格式不正确，必须是字母！");}c1 = char.ToUpper(c1);c2 = char.ToUpper(c2);int i = Convert.ToInt32(c1) - 64;int j = Convert.ToInt32(c2) - 64;n = i*26 + j;}if (letter.Length == 1){char c1 = letter.ToCharArray()[0];if (!char.IsLetter(c1)){throw new Exception("格式不正确，必须是字母！");}c1 = char.ToUpper(c1);n = Convert.ToInt32(c1) - 64;}if (n > 256){throw new Exception("索引超出范围，Excel的列索引不能超过256！");}return n;}/// <summary>/// DataTable填充Excel/// </summary>/// <param name="dt">DataTable表</param>/// <param name="getCell">Cell位置</param>/// <param name="showHeader">是否显示表头</param>public void DataTableToExcel(DataTable dt, string getCell, bool showHeader){int rowCount = dt.Rows.Count; //DataTable行数int colCount = dt.Columns.Count; //DataTable列数string[,] array;if (showHeader){array = new string[rowCount + 1,colCount];}else{array = new string[rowCount,colCount];}if (showHeader) //添加行字段{for (int i = 0; i < colCount; i ++){array[0, i] = dt.Columns[i].ColumnName;}}for (int j = 0; j < rowCount; j++){for (int k = 0; k < colCount; k++){array[j + (showHeader ? 1 : 0), k] = dt.Rows[j][k].ToString();}}ArrayToExcel(array, getCell);}/// <summary>/// DataTable填充Excel  以object方式填充/// </summary>/// <param name="dt">DataTable表</param>/// <param name="getCell">Cell位置</param>/// <param name="showHeader">是否显示表头</param>public void DataTableToExcelofObj(DataTable dt, string getCell, bool showHeader){int rowCount = dt.Rows.Count; //DataTable行数int colCount = dt.Columns.Count; //DataTable列数object[,] array;if (showHeader){array = new object[rowCount + 1, colCount];}else{array = new object[rowCount, colCount];}if (showHeader) //添加行字段{for (int i = 0; i < colCount; i ++){array[0, i] = dt.Columns[i].ColumnName;}}for (int j = 0; j < rowCount; j++){for (int k = 0; k < colCount; k++){array[j + (showHeader ? 1 : 0), k] = dt.Rows[j][k];}}ArrayToExcel(array, getCell);}/// <summary>/// DataRow填充Excel 以object方式填充/// </summary>/// <param name="dr">DataRow</param>/// <param name="getCell">Cell位置</param>/// <param name="showHeader">是否显示表头</param>public void DataRowToExcel(DataRow[] dr, string getCell, bool showHeader){int rowCount = dr.GetLength(0); //DataRow行数int colCount = dr[0].Table.Columns.Count; //DataRow列数object[,] array;if (showHeader){array = new object[rowCount + 1,colCount];}else{array = new object[rowCount,colCount];}if (showHeader) //添加行字段{for (int i = 0; i < colCount; i ++){array[0, i] = dr[0].Table.Columns[i].ColumnName;}}for (int j = 0; j < rowCount; j++){for (int k = 0; k < colCount; k++){array[j + (showHeader ? 1 : 0), k] = dr[j][k];}}ArrayToExcel(array, getCell);}private Range SelectRange(string range){return excelWorksheet.get_Range(range, Type.Missing);}public void RangeCopy(string startCell, string endCell, string targetCell){RangeCopy(string.Empty, string.Empty, startCell, endCell, string.Empty, string.Empty, targetCell);}public void RangeCopy(string worksheetName, string startCell, string endCell, string targetCell){RangeCopy(string.Empty, worksheetName, startCell, endCell, string.Empty, string.Empty, targetCell);}public void RangeCopy(string worksheetName, string startCell, string endCell, string targetWorksheetName,string targetCell){RangeCopy(string.Empty, worksheetName, startCell, endCell, string.Empty, targetWorksheetName, targetCell);}public void RangeCopy(string workbookName, string worksheetName, string startCell, string endCell,string targetWorksheetName, string targetCell){RangeCopy(workbookName, worksheetName, startCell, endCell, string.Empty, targetWorksheetName, targetCell);}/// <summary>/// 区域复制粘贴/// </summary>/// <param name="workbookName">工作薄名</param>/// <param name="worksheetName">工作区名</param>/// <param name="startCell">开始Cell</param>/// <param name="endCell">结束Cell</param>/// <param name="targetWorkbookName">目标工作薄名</param>/// <param name="targetWorksheetName">目标工作区名</param>/// <param name="targetCell">目标Cell</param>public void RangeCopy(string workbookName, string worksheetName, string startCell, string endCell,string targetWorkbookName, string targetWorksheetName, string targetCell){if (workbookName != string.Empty && !FindExcelWorkbook(workbookName))return;if (worksheetName != string.Empty && !FindExcelWorksheet(worksheetName))return;Range workingRangeCells = excelWorksheet.get_Range(startCell, endCell);if (workingRangeCells == null)return;if (targetWorkbookName != string.Empty && !FindExcelWorkbook(targetWorkbookName))return;if (targetWorksheetName != string.Empty && !FindExcelWorksheet(targetWorksheetName))return;Range targetRange = excelWorksheet.get_Range(targetCell, Type.Missing);workingRangeCells.Copy(targetRange);}/// <summary>/// 转换Array为字符串数组/// </summary>/// <param name="values">Array</param>/// <returns>String[]</returns>private string[] ConvertToStringArray(Array values){string[] newArray = new string[values.Length];int index = 0;for (int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); i++){for (int j = values.GetLowerBound(1); j <= values.GetUpperBound(1); j++){if (values.GetValue(i, j) == null){newArray[index] = "";}else{newArray[index] = values.GetValue(i, j).ToString();}index++;}}return newArray;}public Range GotoCell(string Key){excelApp.Goto(Key, 0);return excelApp.ActiveCell;}#endregion        }