导出任意形式Excel(共享心得)

Question

 /// <summary>
    /// 生成EXCEL文件
    /// _til是表格的标题，_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称
    /// </summary>
    public void SaveToExcel(string _til, int _row, int _col, DataTable _dt, string _filename)
    {
        Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
        //合并单元格
        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).set_MergeCells(true);
        // xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);
        xlsheet.ActiveSheet.Cells[1, 1] = _til;
        //字体加粗
        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Bold(true);
        //单元格文本水平居中对齐
        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
        //设置字体大小
        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Size(14);
        //设置列宽
        //xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50);        //画边框线
        xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);        //定义一个2维数组用来存储DATATABLE里的数据
        object[,] dataArray = new object[_row, _col];
        //添加列名
        for (int k = 0; k < _dt.Columns.Count; k++)
        {
            xlsheet.ActiveSheet.Cells[2, k + 1] = _dt.Columns[k].Caption.ToString();
        }
        //把DATATABLE里的数据导到2维数组中
        for (int i = 0; i < _row; i++)
        {
            for (int j = 0; j < _col; j++)
            {
                dataArray[i, j] = _dt.Rows[i][j];
            }
        }
        //把2维数组中的数据导到EXCEL中
        xlsheet.get_Range("A3", xlsheet.Cells[_row + 2, _col]).Value2 = dataArray;
        xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
    }
    /// <summary>
    /// 导入EXCEL表
    /// </summary>
    /// <param name="strFileName"></param>
    /// <returns></returns>
    public DataSet CreateDataSource(string strFileName, string Sqlstr)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";
        OleDbConnection olecon = new OleDbConnection(strCon);
        OleDbDataAdapter myda = new OleDbDataAdapter(Sqlstr, olecon);
        DataSet myds = new DataSet();
        myda.Fill(myds);
        return myds;
    }
有兴趣的.net爱好者请加入QQ群：84493831
让我们共同提高

Answer 1

沒看明白第二個function有什麽用？导入EXCEL表為什麽返回的確實dataset?

Answer 2

现在导出到excel都是手写的，以后能有个什么控件能简单地导出就好了，毕竟excel的格式非常复杂，导不同格式的报表每次都要重写一次代码，相当麻烦啊。

Answer 3

我拷下来试试，
因为这几天我也正好在写这个导出excel和导入excel的. 不过我写的那种，是response.Write()的方式。导出来的不是excel的标准格式。所以在导入时就会报错。

Answer 4

现在的CSDN真是不同往日了，这样的内容还有这么多朋友接分学习

Answer 5

Up，.Net操作COM会出现很多不可预测的异常。

Answer 6

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;namespace OutExcel
{
    public class OutExcelForDataGradView
    {
        /// <summary>
        /// 将DataGridView中的数据导出到Excel中，并加载显示出来(无加载模板)
        /// 只用于一般的导出Excel
        /// </summary>
        /// <param name="PageHead">要显示的页头</param>
        /// <param name="PrintDate">打印日期</param>
        /// <param name="datagridview1">要进行导出的DataGridView</param>
        public void ExportToExcel(string PageHead, string PrintDate, DataGridView datagridview1)
        {
            //DataGridView可见列数
            int visiblecolumncount = 0;
            for (int i = 0; i < datagridview1.Columns.Count; i++)
            {
                if (datagridview1.Columns[i].Visible == true && (datagridview1.Columns[i] is DataGridViewTextBoxColumn))
                {
                    visiblecolumncount++;
                }
            }            try
            {
                //当前操作列的索引
                int currentcolumnindex = 1;
                //当前操作行的索引
                Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Mylxls.Application.Workbooks.Add(true);
                //Mylxls.Cells.Font.Size = 10.5;   //设置默认字体大小
                //设置标头
                Mylxls.Caption = PageHead;
                //显示表头
                Mylxls.Cells[1, 1] = PageHead;
                //显示时间
                Mylxls.Cells[2, 1] = PrintDate;
                for (int i = 0; i < datagridview1.Columns.Count; i++)
                {
                    if (datagridview1.Columns[i].Visible == true && (datagridview1.Columns[i] is DataGridViewTextBoxColumn))   //如果显示
                    {
                        Mylxls.Cells[3, currentcolumnindex] = datagridview1.Columns[i].HeaderText;
                        Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                        Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).ColumnWidth = datagridview1.Columns[i].Width / 8;
                        //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体
                        //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中显示
                        currentcolumnindex++;
                    }
                }
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30;   //行高
                //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体";
                //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14;   //字体大小
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
                Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
                Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //左边显示
                //Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12;  //列宽度                object[,] dataArray = new object[datagridview1.Rows.Count, visiblecolumncount];                //当前操作列的索引
                //int currentcolumnindex = 1;
                //当前操作行的索引
                for (int i = 0; i < datagridview1.Rows.Count; i++)   //循环填充数据
                {
                    currentcolumnindex = 1;
                    for (int j = 0; j < datagridview1.Columns.Count; j++)
                    {
                        if (datagridview1.Columns[j].Visible == true && (datagridview1.Columns[j] is DataGridViewTextBoxColumn))
                        {
                            if (datagridview1[j, i].Value != null)  //如果单元格内容不为空
                            {
                                dataArray[i, currentcolumnindex - 1] = datagridview1[j, i].Value.ToString();
                            }
                            currentcolumnindex++;
                        }
                    }
                }
                Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[datagridview1.Rows.Count + 3, visiblecolumncount]).Value2 = dataArray; //设置边框
                Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[datagridview1.Rows.Count + 3, visiblecolumncount]).Cells.Borders.LineStyle = 1; //设置边框
                Mylxls.Visible = true;            }
            catch
            {
                MessageBox.Show("信息导出失败，请确认你的机子上装有Microsoft Office Excel 2003！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {            }
        }        /// <summary>
        /// 将DataGridView中的数据导出到Excel中，并加载显示出来(加载模板)
        /// 仅用于导出已定义好模版的Excel导出，主要如“旬报表”，“月报表”等
        /// 请注意：模板应放在应程序的PrintTemplate目录下        /// </summary>
        /// <param name="ModelName">模版的名称</param>
        /// <param name="PrintDate">打印日期</param>
        /// <param name="datagridview1">要进行导出的DataGridView</param>
        public void ExportToExcelByModel(string ModelName, string PrintDate, DataGridView datagridview1)
        {
            Microsoft.Office.Interop.Excel.Application m_objExcel = null;
            Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
            Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
            Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
            object m_objOpt = System.Reflection.Missing.Value;
            try
            {
                m_objExcel = new Microsoft.Office.Interop.Excel.Application();
                string path = System.Windows.Forms.Application.StartupPath.ToString().Replace("\\bin\\Debug", "") + "\\PrintTemplate\\";
                path = path + ModelName;
                m_objBook = m_objExcel.Workbooks.Open(path, 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);                m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
                m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));                //填充日期
                m_objExcel.Cells[2, 1] = PrintDate.ToString();                //当前操作列的索引
                int currentcolumnindex = 1;
                //当前操作行的索引
                int currentrowindex = 4;
                for (int i = 0; i < datagridview1.Rows.Count; i++)   //循环填充数据
                {
                    currentcolumnindex = 1;
                    currentrowindex = 4 + i;
                    for (int j = 0; j < datagridview1.Columns.Count; j++)
                    {
                        if (datagridview1.Columns[j].Visible == true)
                        {
                            if (datagridview1[j, i].Value != null)  //如果单元格内容不为空
                            {
                                m_objExcel.Cells[currentrowindex, currentcolumnindex] = datagridview1[j, i].Value.ToString();
                            }
                            m_objExcel.get_Range(m_objExcel.Cells[currentrowindex, currentcolumnindex], m_objExcel.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                            currentcolumnindex++;
                        }
                    }
                }
                m_objExcel.DisplayAlerts = false;
                m_objExcel.Visible = true;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
            }
            catch
            {
                MessageBox.Show("信息导出失败，请确认你的机子上装有Microsoft Office Excel 2003并且模版未被删除！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                GC.Collect();
            }
        }
     
//不知道怎么样这个类？O(∩_∩)O~

Answer 7

   /// <summary>
        /// 将DataGridView中的数据导出到Excel中，并加载显示出来(无加载模板)
        /// 只用于有合并项的导出，我这里所说的合并项是指如果有单元格的值为空时自动与上一行的单位格进行合并
        /// </summary>
        /// <param name="PageHead">要显示的页头</param>
        /// <param name="PrintPrintDate">打印日期</param>
        /// <param name="datagridview1">要进行导出的DataGridView</param>
        /// <param name="MergeCount">合并的列数,没有用到</param>
        public void ExportToExcelNullMerge(string PageHead, string PrintPrintDate, DataGridView datagridview1, int MergeCount)
        {
            //DataGridView可见列数
            int visiblecolumncount = 0;
            for (int i = 0; i < datagridview1.Columns.Count; i++)
            {
                if (datagridview1.Columns[i].Visible == true && (datagridview1.Columns[i] is DataGridViewTextBoxColumn))
                {
                    visiblecolumncount++;
                }
            }            try
            {
                //当前操作列的索引
                int currentcolumnindex = 1;
                //当前操作行的索引
                int currentrowindex = 4;
                Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Mylxls.Application.Workbooks.Add(true);
                //Mylxls.Cells.Font.Size = 10.5;   //设置默认字体大小
                //设置标头
                Mylxls.Caption = PageHead;
                //显示表头
                Mylxls.Cells[1, 1] = PageHead;
                //显示时间
                Mylxls.Cells[2, 1] = PrintPrintDate;
                for (int i = 0; i < datagridview1.Columns.Count; i++)
                {
                    if (datagridview1.Columns[i].Visible == true && (datagridview1.Columns[i] is DataGridViewTextBoxColumn))   //如果显示
                    {
                        Mylxls.Cells[3, currentcolumnindex] = datagridview1.Columns[i].HeaderText;
                        Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                        //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体
                        Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
                        currentcolumnindex++;
                    }
                }
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30;   //行高
                //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体";
                //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14;   //字体大小
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
                Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
                Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //左边显示
                //Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12;  //列宽度                for (int i = 0; i < datagridview1.Rows.Count; i++)
                {
                    currentcolumnindex = 1;
                    currentrowindex = 4 + i;
                    for (int j = 0; j < datagridview1.Columns.Count; j++)
                    {
                        if (datagridview1.Columns[j].Visible == true && (datagridview1.Columns[j] is DataGridViewTextBoxColumn))
                        {
                            if (datagridview1[j, i].Value != null)  //如果单元格内容不为空
                            {
                                Mylxls.Cells[currentrowindex, currentcolumnindex] = datagridview1[j, i].Value.ToString();
                            }
                            Mylxls.get_Range(Mylxls.Cells[1, currentcolumnindex], Mylxls.Cells[1, currentcolumnindex]).ColumnWidth = datagridview1.Columns[j].Width / 8;
                            Mylxls.get_Range(Mylxls.Cells[currentrowindex, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                            if (datagridview1.Rows[i].Cells[j].Value.ToString() == "")
                            {
                                Mylxls.get_Range(Mylxls.Cells[currentrowindex - 1, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).MergeCells = true;
                                //Mylxls.get_Range(Mylxls.Cells[i - 1, col + 2], Mylxls.Cells[rowIndex, col + 2]).MergeCells = true;
                                //Mylxls.get_Range(Mylxls.Cells[i - 1, col + 3], Mylxls.Cells[rowIndex, col + 3]).MergeCells = true;
                            }
                            currentcolumnindex++;
                        }
                    }
                }
                Mylxls.Visible = true;            }
            catch
            {
                MessageBox.Show("信息导出失败，请确认你的机子上装有Microsoft Office Excel 2003！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {            }
        }    

Answer 8

    /// <summary>
        /// 将DataGridView中的数据导出到Excel中，并加载显示出来(无加载模板)
        /// 只用于有合并项的导出,并且可根据前面的DataGridViewCheckBoxColumn是否选择进行导出
        /// </summary>
        /// <param name="PageHead">要显示的页头</param>
        /// <param name="PrintDate">打印日期</param>
        /// <param name="datagridview1">要进行导出的DataGridView</param>
        /// <param name="MergeCount">合并的列数</param>
        public void ExportToExcelNullMergeHasCheckBox(string PageHead, string PrintDate, DataGridView datagridview1, int MergeCount)
        {
            //DataGridView可见列数
            int visiblecolumncount = 0;
            for (int i = 0; i < datagridview1.Columns.Count; i++)
            {
                if (datagridview1.Columns[i].Visible == true && (datagridview1.Columns[i] is DataGridViewTextBoxColumn))
                {
                    visiblecolumncount++;
                }
            }            try
            {
                //当前操作列的索引
                int currentcolumnindex = 1;
                //当前操作行的索引
                int currentrowindex = 4;
                Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Mylxls.Application.Workbooks.Add(true);
                //Mylxls.Cells.Font.Size = 10.5;   //设置默认字体大小
                //设置标头
                Mylxls.Caption = PageHead;
                //显示表头
                Mylxls.Cells[1, 1] = PageHead;
                //显示时间
                Mylxls.Cells[2, 1] = PrintDate;
                for (int i = 0; i < datagridview1.Columns.Count; i++)
                {
                    if (datagridview1.Columns[i].Visible == true && (datagridview1.Columns[i] is DataGridViewTextBoxColumn))   //如果显示
                    {
                        Mylxls.Cells[3, currentcolumnindex] = datagridview1.Columns[i].HeaderText;
                        Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                        //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体
                        Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
                        currentcolumnindex++;
                    }
                }
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30;   //行高
                //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体";
                //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14;   //字体大小
                Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
                Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
                Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //左边显示
                //Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12;  //列宽度                currentrowindex = 3;
                for (int i = 0; i < datagridview1.Rows.Count; i++)
                {
                    DataGridViewCheckBoxCell datagridview1c = new DataGridViewCheckBoxCell();
                    datagridview1c = (DataGridViewCheckBoxCell)datagridview1[0, i];
                    if (datagridview1c.FormattedValue.ToString() == "True")
                    {
                        currentcolumnindex = 1;                        currentrowindex++;
                        for (int j = 0; j < datagridview1.Columns.Count; j++)
                        {
                            if (datagridview1.Columns[j].Visible == true && (datagridview1.Columns[j] is DataGridViewTextBoxColumn))
                            {
                                if (datagridview1[j, i].Value != null)  //如果单元格内容不为空
                                {
                                    Mylxls.Cells[currentrowindex, currentcolumnindex] = datagridview1[j, i].Value.ToString();
                                }
                                Mylxls.get_Range(Mylxls.Cells[1, currentcolumnindex], Mylxls.Cells[1, currentcolumnindex]).ColumnWidth = datagridview1.Columns[j].Width / 8;
                                Mylxls.get_Range(Mylxls.Cells[currentrowindex, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
                                if (datagridview1.Rows[i].Cells[j].Value.ToString() == "")
                                {
                                    for (int k = 0; k < MergeCount; k++)
                                    {
                                        Mylxls.get_Range(Mylxls.Cells[currentrowindex - 1, currentcolumnindex + k], Mylxls.Cells[currentrowindex, currentcolumnindex + k]).MergeCells = true;
                                    }
                                    //Mylxls.get_Range(Mylxls.Cells[i - 1, col + 2], Mylxls.Cells[rowIndex, col + 2]).MergeCells = true;
                                    //Mylxls.get_Range(Mylxls.Cells[i - 1, col + 3], Mylxls.Cells[rowIndex, col + 3]).MergeCells = true;
                                }
                                currentcolumnindex++;
                            }
                        }
                    }
                }
                Mylxls.Visible = true;            }
            catch
            {
                MessageBox.Show("信息导出失败，请确认你的机子上装有Microsoft Office Excel 2003！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {            }
        }
    }
}

Answer 9

提高了
这个也是引用COM组件的方法吗？

Answer 10

我也来晒晒一个封装好的导入导出EXCEL类，可以直接调用，使用于各种控件和表
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;namespace ImportExportToExcel
{
    public class ImportExportToExcel
    {
        private string strConn ;
        
        private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
        private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      
        
        public ImportExportToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.openFileDlg.DefaultExt = "xls";
            this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";            this.saveFileDlg.DefaultExt="xls";
            this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";        }        
        #region 从Excel文件导入到DataSet
        //        /// <summary>
        //        /// 从Excel导入文件
        //        /// </summary>
        //        /// <param name="strExcelFileName">Excel文件名</param>
        //        /// <returns>返回DataSet</returns>
        //        public DataSet ImportFromExcel(string strExcelFileName)
        //        {
        //            return doImport(strExcelFileName);
        //        }
        /**//// <summary>
        /// 从选择的Excel文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromExcel()
        {
            DataSet ds=new DataSet();
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                ds=doImport(openFileDlg.FileName);
            return ds;
        }
        /**//// <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        /// <returns></returns>
        public DataSet ImportFromExcel(string strFileName)
        {
            DataSet ds=new DataSet();
            ds=doImport(strFileName);
            return ds;
        }
        /**//// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private DataSet doImport(string strFileName)
        {
            if (strFileName=="") return null;
              
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + strFileName + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);            DataSet ExcelDs = new DataSet();
            try
            {
                ExcelDA.Fill(ExcelDs, "ExcelInfo");
                
            }
            catch(Exception err)
            {
                System.Console.WriteLine( err.ToString() );
            }
            return ExcelDs;
            
            
        
        }
        #endregion        #region 从DataSet到出到Excel
        /**//// <summary>
        /// 导出指定的Excel文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的Excel文件名</param>
        public void ExportToExcel(DataSet ds,string strExcelFileName)
        {
            if (ds.Tables.Count==0 || strExcelFileName=="") return;
            doExport(ds,strExcelFileName);
           }
        /**//// <summary>
        /// 导出用户选择的Excel文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToExcel(DataSet ds)
        {
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExport(ds,saveFileDlg.FileName);
            
        }
        /**//// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的文件名</param>
        private void doExport(DataSet ds,string strExcelFileName)
        {
            
            Excel.Application excel= new Excel.Application();
            
            //            Excel.Workbook obj=new Excel.WorkbookClass();
            //            obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);            int rowIndex=1;
            int colIndex=0;            excel.Application.Workbooks.Add(true);
            
    
            System.Data.DataTable table=ds.Tables[0] ;
            foreach(DataColumn col in table.Columns)
            {
                colIndex++;    
                excel.Cells[1,colIndex]=col.ColumnName;                
            }            foreach(DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex=0;
                foreach(DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
                }
            }
            excel.Visible=false;
            
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",
                Excel.XlFileFormat.xlExcel9795, null, null, false,
                false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);
            
            
            //wkbNew.SaveAs strBookName
            //excel.Save(strExcelFileName);
            excel.Quit();
            excel=null;
            
            GC.Collect();//垃圾回收
        }
        #endregion        #region 从XML导入到Dataset        /**//// <summary>
        /// 从选择的XML文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromXML()
        {
            DataSet ds=new DataSet();
            System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
            openFileDlg.DefaultExt="xml";
            openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
                catch{}
            return ds;
        }
        /**//// <summary>
        /// 从指定的XML文件导入
        /// </summary>
        /// <param name="strFileName">XML文件名</param>
        /// <returns></returns>
        public DataSet ImportFromXML(string strFileName)
        {
            if (strFileName=="")
                return null;
            DataSet ds=new DataSet();
            try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
            catch{}
            return ds;
        }
        
        #endregion        #region 从DataSet导出到XML
        /**//// <summary>
        /// 导出指定的XML文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strXMLFileName">要导出的XML文件名</param>
        public void ExportToXML(DataSet ds,string strXMLFileName)
        {
            if (ds.Tables.Count==0 || strXMLFileName=="") return;
            doExportXML(ds,strXMLFileName);
        }
        /**//// <summary>
        /// 导出用户选择的XML文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToXML(DataSet ds)
        {
            System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
            saveFileDlg.DefaultExt="xml";
            saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExportXML(ds,saveFileDlg.FileName);
        }        /**//// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的XML文件名</param>
        private void doExportXML(DataSet ds,string strXMLFileName)
        {
            try
            {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
            catch(Exception ex)
            {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
        }        #endregion 
    }}

Answer 11

以上是不是只适用于Excel 2003啊，如果导出到Excel 2007可以实现吗？请教了，我正在寻求这个答案。