C#导出到Excel的严重问题,请高手赐教

要知道一个Excel文件里最多256个工作表
你循环超过256怎么办？其实你的难点是Excel的操作。
方法一：使用Excel的COM
方法二：把Excel文件当成数据库，进行表的添加和插入操作

下面是用数据库来做的        static void Main(string[] args)
        {
            string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\data.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();            OleDbCommand cmd = new OleDbCommand(GetSQLCmd_NewTable("Test2"),conn);
            int i= cmd.ExecuteNonQuery(); //Create a new sheet            cmd.CommandText = "insert into Test2 (SHEET1,SHEET2) values(\'uu1\',\'uu2\')";
            i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            cmd=null;
            conn.Close();
            conn.Dispose();
            conn = null;

        }        static string GetSQLCmd_NewTable(string new_sheet_name)
        {
            //假设每个Sheet里只有2列是有效的，且2列的名字分别为SHEET1和SHEET2
            string sqlcmd = "";
            sqlcmd="create table "+new_sheet_name+" (";
            sqlcmd = sqlcmd + "SHEET1  varchar(255),";
            sqlcmd = sqlcmd + "SHEET2  varchar(255))";
            return sqlcmd;
        }

需要用到Excel.dll，Excel.dll生成方法是将Office2003目录下的Excel.exe文件拷出来，把Excel.exe文件考到C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin里
然后运行VS2005命令提示输入TlbImp EXCEL.EXE Excel.dll
然后会在C:\Program Files\Microsoft Visual Studio 8\VC下找到Excel.dll #region
/*Author:wujunjie
*Date:2009-9-22
*Version：V1.0.0.1
*/
#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" };

        /// <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.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);
            }        }

调试易

C#导出到Excel的严重问题,请高手赐教

解决方案 »