本文介绍使用.NET进行报表导出的方法，包括直接网页输出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; #endregion public 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); //从定位处的下一行的上面插入新行 } //Fill if (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); } 打开、保存、关闭Excel文件#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 windows窗口，workbook工作薄，worksheet工作区操作#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 }终于写完了，收工
扫一扫
2929
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
