C#导出Excel （界面上用Excel导出datatable）

你这个是导入Excel,不是导出Excel。
1.你的用一个OpenFileDialog类，用来打开窗口，上传Excel文件
2.写一个Excel操作类，用来读取Excel里面的数据，建议用ExcelHelper网上一搜就有。
3.在ExcelHelper类里面写一个方法ExchangeExcelToDataTable()
4.页面控件绑定DataTable.

楼上几位，我就是想将datagrid的数据导出到Excel

#endregion
namespace Helper
{
    #region using    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Reflection;
    using Excel;
    using System.Data;
    using System.Windows.Forms;
    #endregion    public class ExcelHelper
    {
        private static readonly object m_objOpt = Missing.Value;
        private static readonly  string[] W_Index ={ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
                private ExcelHelper()
        {

        }
        /// <summary>
        /// 将DataView中的数据导入Excel
        /// </summary>
        /// <param name="dv">DataView变量</param>
        /// <param name="filePath">要保存excel的路径</param>
        public static void DataViewToExcel(DataView dv, string filePath)
        {
            Excel.Application app = null ;
            Workbook book = null;
            Worksheet sheet = null;
            try
            {
                //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                int rowCount = dv.Count + 1;
                int colCount = dv.Table.Columns.Count;                object[,] objs = new object[dv.Count + 1, dv.Table.Columns.Count];                //将DataView中的列名拷贝到数组的第一行
                for (int k = 0; k < colCount; k++)
                {
                    objs[0, k] = dv.Table.Columns[k].ColumnName;
                }
                //将DataView中的数据拷贝到数组中
                for (int i = 1; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        objs[i, j] = dv[i - 1][j];
                    }
                }                app = new Excel.Application();
                //覆盖时不提示
                //app.AlertBeforeOverwriting = false;
                book = app.Workbooks.Add(m_objOpt);
                sheet = (Worksheet)book.ActiveSheet;
                SetRangeData(sheet, 1, 1, rowCount, colCount, objs);
                Save(book, filePath);
                app.Workbooks.Close();
                app.Quit();
                ReleaseObj(sheet);
                ReleaseObj(book);
                ReleaseObj(app);
                sheet = null;
                book = null;
                app = null;
                GcCollect();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ReleaseObj(sheet);
                ReleaseObj(book);
                ReleaseObj(app);
                sheet = null;
                book = null;
                app = null;
                GcCollect();
            }
        }
        /// <summary>
        /// 将DataGridView控件中的数据导入Excel
        /// </summary>
        /// <param name="gridView">DataGridView实例名</param>
        /// <param name="filePath">要保存excel的路径</param>
        public static void DataGridViewToExcel(DataGridView gridView, string filePath)
        {
            Excel.Application app = null;
            Workbook book = null;
            Worksheet sheet = null;
            try
            {                int rowCount = gridView.Rows.Count + 1;
                int colCount = gridView.Columns.Count;                object[,] objs = new object[rowCount, colCount];                //Grid的HeaderText
                for (int k = 0; k < colCount; k++)
                {
                    objs[0, k] = gridView.Columns[k].HeaderText;
                }
                //Grid数据
                for (int i = 1; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        objs[i, j] = gridView[j, i - 1].Value;
                    }
                }
                app = new Excel.Application();
                book = app.Workbooks.Add(m_objOpt);
                sheet = (Worksheet)book.ActiveSheet;
                SetRangeData(sheet, 1, 1, rowCount, colCount, objs);
                Save(book, filePath);
                app.Workbooks.Close();
                app.Quit();
                ReleaseObj(sheet);
                ReleaseObj(book);
                ReleaseObj(app);
                sheet = null;
                book = null;
                app = null;
                GcCollect();            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ReleaseObj(sheet);
                ReleaseObj(book);
                ReleaseObj(app);
                sheet = null;
                book = null;
                app = null;
                GcCollect();
            }
        }        public static void DataViewAppendToExcel(DataView dv,string filePath,string sheetName,bool isNewSheet)
        {
            Excel.Application app = null;
            Workbook book = null;
            Worksheet sheet = null;
            try
            {
                int rowCount = dv.Count + 1;
                int colCount = dv.Table.Columns.Count;                object[,] objs = new object[dv.Count + 1, dv.Table.Columns.Count];                //将DataView中的列名拷贝到数组的第一行
                for (int k = 0; k < colCount; k++)
                {
                    objs[0, k] = dv.Table.Columns[k].ColumnName;
                }
                //将DataView中的数据拷贝到数组中
                for (int i = 1; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        objs[i, j] = dv[i - 1][j];
                    }
                }                app = new Excel.Application();
                book = app.Workbooks.Open(filePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                int oldRowCount = 0;
                if (isNewSheet)//新建一页
                {
                    Worksheet lastSheet=(Worksheet)book.Sheets.get_Item(book.Sheets.Count);
                    sheet = (Worksheet)book.Sheets.Add(m_objOpt,lastSheet, m_objOpt, m_objOpt);
                    sheet.Name = sheetName;
                    oldRowCount = 1;
                    ReleaseObj(lastSheet);
                    lastSheet = null;

                }
                else
                {
                    sheet = (Worksheet)book.Sheets.get_Item(sheetName);
                    int useCount = sheet.UsedRange.Cells.Rows.Count;
                    if (useCount == sheet.Rows.Count)//如果已用行数等于最大行数,则新建一页
                    {
                        sheet = (Worksheet)book.Sheets.Add(m_objOpt, sheet, m_objOpt, m_objOpt);
                        sheet.Name = sheetName + "1";
                        oldRowCount = 1;
                    }
                    else
                    {
                        oldRowCount = sheet.UsedRange.Cells.Rows.Count+1;
                    }
                }                SetRangeData(sheet, oldRowCount,1, rowCount, colCount, objs);
                Save(book, filePath);
                app.Workbooks.Close();
                app.Quit();
                ReleaseObj(sheet);
                ReleaseObj(book);
                ReleaseObj(app);
                sheet = null;
                book = null;
                app = null;
                GcCollect();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ReleaseObj(sheet);
                ReleaseObj(book);
                ReleaseObj(app);
                sheet = null;
                book = null;
                app = null;
                GcCollect();
            }
        }         /// <summary>
        /// 在Excel中设置第rowIndex行的值
        /// </summary>
        /// <param name="st">Excel的Sheet页面</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="data">要插入的数据数组</param>
        private static void SetRowData(Worksheet st, int rowIndex, object[] data)
        {
            //Execl的列的索引为A,B,C,D,E...X,Y,Z,AA,AB,AC...等
            int colIndex_First = data.Length / W_Index.Length;
            int colIndex_Second = data.Length % W_Index.Length;            string colIndex_Start = "A";
            string colIndex_End = "";
            if (colIndex_First > 1)//索引由2个英文字母组成.
            {
                colIndex_End = W_Index[colIndex_First - 1] + W_Index[colIndex_Second - 1];
            }
            else
            {
                colIndex_End = W_Index[colIndex_Second - 1];
            }            Range range = st.get_Range(colIndex_Start + rowIndex.ToString(), colIndex_End + rowIndex.ToString());
            range.Value2 = data;

        }

/// <summary>
        /// 设置Excel一个区域内的值
        /// </summary>
        /// <param name="st">Excel的Sheet页面</param>
        /// <param name="rowStartIndex">第几行开始</param>
        /// <param name="colStartIndex">第几列开始</param>
        /// <param name="rowNum">记录行数</param>
        /// <param name="colNum">记录列数</param>
        /// <param name="data">要写入的数据,二维数组</param>
        /// <param name="filePath">要保存excel的路径</param>
        private static void SetRangeData(Worksheet st,int rowStartIndex,int colStartIndex, int rowNum, int colNum, object[,] data)
        {
            try
            {
                int allRowsCount = rowStartIndex - 1 + rowNum;//所有行数
                int allColumnCount = colStartIndex - 1 + colNum;//所有行数
                //Excel表列的索引由26个英文字母组成(或者说26进制),例如A,B,C,D...X,Y,Z,AA,AB,AC,AX,AY,AZ,BA,BB.......
                //考虑实际情况,列数一般不可能超过26的2次方,因此列的索引因该是由1个或2个英文字母组成.
                int colIndex_First = allColumnCount % W_Index.Length;//求模,
                int colIndex_Second = allColumnCount / W_Index.Length;//如果小于1,表示索引是1个英文字母,大于等于1,表示由2个字母组成
                string colIndex_Start = W_Index[colStartIndex - 1] + rowStartIndex.ToString();//Range的起点,列索引+行索引,如:A1
                string colIndex_End = "";//Range的结束点
                if (colIndex_Second > 1)
                {
                    colIndex_End = W_Index[colIndex_Second - 1] + W_Index[colIndex_First - 1];
                }
                else
                {
                    colIndex_End = W_Index[colIndex_First - 1];
                }                Range range = st.get_Range(colIndex_Start, colIndex_End + allRowsCount.ToString());
                range.WrapText = true;
                range.Value2 = data;
            }
            catch
            {
            }
            finally
            {
                GcCollect();
            }
        }
        /// <summary>
        /// 保存Excel
        /// </summary>
        /// <param name="book"></param>
        /// <param name="filePath"></param>
        private static void Save(Workbook book,string filePath)
        {
            book.SaveAs(filePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            book.Close(false, m_objOpt, m_objOpt);
        }        /// <summary>
        /// 释放对象，内部调用
        /// </summary>
        /// <param name="o"></param>
        private static void ReleaseObj(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally { o = null; }
        }
        private static void GcCollect()
        {
            try
            {
                System.GC.Collect();
                System.GC.WaitForPendingFinalizers();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }        }    }
}

public void ExportExcel(DataSet ds)        //以DataSet- 导出Excel文件
        {
            if (ds == null) return;
            Microsoft.office.Interop.Excel.Application xlApp = new Microsoft.office.Interop.Excel.Application();            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            Microsoft.office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.office.Interop.Excel.Worksheet worksheet = (Microsoft.office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得Sheet1
            Microsoft.office.Interop.Excel.Range range;
            long totalCount = ds.Tables[0].Rows.Count;            long rowRead = 0;
            float percent = 0;            //worksheet.Cells[1, 1] = "报表标题";            //写入字段
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                range = (Microsoft.office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }
            //写入数值
            for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            xlApp.Visible = true;
        }你的datagrid的数据可以是dataset吧

谢谢楼上可以转换成dataset ，但是是基于浏览器的，不是winform的！

把要输出的内容输出成
<table><tr><td></td></tr></table>性的字符，输出文件名称改成xx.xls即可，方便快捷！

已解决，共享下
    protected void importExcel()
    {
        VerifyRenderingInServerForm(GridView1);
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "gb2312";
        //下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开
        //filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc 　　 .xls 　　 .txt 　　.htm　　
        Response.AppendHeader("Content-Disposition", "attachment;filename=FileFlow.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        //Response.ContentType指定文件类型可以为application/ms-excel 　　 application/ms-word 　　 application/ms-txt 　　 application/ms-html 　　或其他浏览器可直接支持文档　
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        //定义一个输入流
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        //将目标数据绑定到输入流输出
        //this.RenderControl(oHtmlTextWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);
        //this 表示输出本页，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件　　
        Response.Write(oStringWriter.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {    }

可以试一下用NPOI来操作Excelc#导出Excel文件
C#导出Excel合并单元格

调试易

C#导出Excel （界面上用Excel导出datatable）

解决方案 »