导出Excel（一）

快速导出Excel其实比较简单，主要是给Excel.Range的Value2属性赋值。该属性的值是一个对象类型的二维数组。第一秩表示的是行，第二秩表示的是列。

      本方法是基于已经存在Excel模板的情况下使用的，可以对方法进行一些扩展或者修改。比如不基于模板的导出，只需修改appExcel.Workbooks.Open处为appExcel.Workbooks.Add即可；

      方法中MaxRow表示每次导出到Excel的Sheet的最大行数。一般情况下，一个sheet可以容纳的最大行数为65536行，所以MaxRow应该小于该值。由于方法中导出是一次导出一定数量的数据（iEachSize来确定），所以MaxRow的值应该是65536减去iEachSize。

       该方法可以直接使用，我会在适当的地方加以注释。

        /// <summary>
        /// DataTable 导出Excel
        /// </summary>
        /// <param name="strFileName">保存的文件名称</param>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTempFileName">模版名称</param>
        /// <returns>true:成功 false:失败</returns>
        public static bool ExportExcel(string strFileName, DataTable dt, string strTempFileName)
        {
            if (dt == null || dt.Rows.Count == 0)
                return false;

            Excel.Application appExcel = new Excel.Application();
            Excel.Workbook workbookData;
            Excel.Worksheet worksheetData;

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            workbookData = appExcel.Workbooks.Open(strTempFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            worksheetData = (Excel.Worksheet)workbookData.Sheets[1];

            //DataTable的ColumnName作为Excel的列名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheetData.Cells[1, i + 1] = dt.Columns[i].ColumnName.Trim();
            }

            Excel.Range xlRang = null;
            int iRowCount = dt.Rows.Count;//导出的数据总数量
            int iParstedRow = 0;//已经到处的数据总数量

            int iCurrSize = 0;//每次实际导出的数量，大多数情况下和iEachSize相同，最后一批可能不同
            int iEachSize = 1000;   // 设定的每次导出的数量
            int iColumnAccount = dt.Columns.Count;
            int iCurrentRow = 0;//当前已经导出的数量，如果大于MaxRow，则新增一个Sheet再导出
            int iSheet = 1;//当前的sheet

            object[,] objVal = new object[iEachSize, iColumnAccount];
            try
            {
                iCurrSize = iEachSize;
                while (iParstedRow < iRowCount)
                {
                    if ((iRowCount - iParstedRow) < iEachSize)
                        iCurrSize = iRowCount - iParstedRow;
                    for (int i = 0; i < iCurrSize; i++)
                    {
                        for (int j = 0; j < iColumnAccount; j++)
                            objVal[i, j] = dt.Rows[iParstedRow + i][j].ToString();
                    }
                    // 比如  A1 H10, 表示从 A 到 H 列,  1 到 10 行
                    xlRang = worksheetData.get_Range("A" + ((int)(iCurrentRow + 1)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString()
                        + (((int)(iCurrentRow + iCurrSize + 1)).ToString()));

                    xlRang.Value2 = objVal;

                    iParstedRow = iParstedRow + iCurrSize;
                    iCurrentRow = iCurrentRow + iCurrSize;

                    if (iCurrentRow > MaxRow)
                    {
                        worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, workbookData.Worksheets.get_Item(iSheet), Missing.Value, Missing.Value);
                        iSheet += 1;

                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            worksheetData.Cells[1, i + 1] = dt.Columns[i].ColumnName.Trim();
                        }

                        iCurrentRow = 0;
                    }
                }

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
                xlRang = null;

            }
            catch
            {
                appExcel.Quit();
                return false;
            }

            System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;

            workbookData.Saved = false;
            workbookData.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            appExcel.Quit();

            return true;
        }

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lsj_zrp/archive/2009/07/29/4390944.aspx