.NET----导出EXCEL

 

注意：应先在COM组件中，引用Microsoft Excel 11.0 Object Library。 版本：1.5

using System;
using System.Collections.Generic;
using System.Text;
using Book.BookDataAccess;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using Book.BookModel;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Web;

namespace Book.BookLogic
{
    public class CategoryLogic
    {  
        /**/
        /// <summary>
        /// ASP.NET，分Sheet导出Excel文件
        /// </summary>
        /// <param name="dv">用于导出的DataView</param>
        /// <param name="tmpExpDir">导出的文件夹，例如~/ExcelDownload/</param>
        /// <param name="refFileName">文件名，例如test.xls</param>
        /// <param name="sheetName">Sheet的名称，如果导出多个Sheet，会自动在名称后面加1、2、3</param>
        /// <param name="sheetSize">每个Sheet包含的数据行数，此数值不包括标题行。所以，对于65536行数据，请将此值设置为65535</param>
        /// <param name="setBorderLine">导出完成后，是否给数据加上边框线</param>
        public static void WebExportToExcel(DataView dv, string tmpExpDir, string refFileName, string sheetName, int sheetSize, bool setBorderLine)
        {
            //设置多少行为一个Sheet
            int RowsToDivideSheet = sheetSize;
            //计算Sheet数
            int sheetCount = (dv.Table.Rows.Count - 1) / RowsToDivideSheet + 1;
            GC.Collect();
            Application excel;
            _Workbook xBk;
            _Worksheet xSt = null;
            excel = new ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            //申明循环中要使用的变量
            int dvRowStart;
            int dvRowEnd;
            int rowIndex = 0;
            int colIndex = 0;
            //对全部Sheet进行操作
            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
            {
                //初始化Sheet中的变量
                rowIndex = 1;
                colIndex = 1;
                //计算起始行
                dvRowStart = sheetIndex * RowsToDivideSheet;
                dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
                if (dvRowEnd > dv.Table.Rows.Count - 1)
                {
                    dvRowEnd = dv.Table.Rows.Count - 1;
                }
                //创建一个Sheet
                if (null == xSt)
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                }
                else
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
                }
                //设置SheetName
                xSt.Name = sheetName;
                if (sheetCount > 1)
                {
                    xSt.Name += ((int)(sheetIndex + 1)).ToString();
                }
                //取得标题
                foreach (DataColumn col in dv.Table.Columns)
                {
                    //设置标题格式
                    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐
                    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
                    //填值，并进行下一列
                    excel.Cells[rowIndex, colIndex++] = col.ColumnName;
                }
                //取得表格中数量
                int drvIndex;
                for (drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
                {
                    DataRowView row = dv[drvIndex];
                    //新起一行，当前单元格移至行首
                    rowIndex++;
                    colIndex = 1;
                    foreach (DataColumn col in dv.Table.Columns)
                    {
                        if (col.DataType == System.Type.GetType("System.DateTime"))
                        {
                            excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        }
                        else if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                        colIndex++;
                    }
                }
                //使用最佳宽度
                Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]);
                allDataWithTitleRange.Select();
                allDataWithTitleRange.Columns.AutoFit();
                //xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex-1]).Columns.AutoFit();
                if (setBorderLine)
                {
                    allDataWithTitleRange.Borders.LineStyle = 1;
                }
            }
            //excel.Visible = true;
            string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(tmpExpDir, refFileName));
            xBk.SaveCopyAs(absFileName);
            xBk.Close(false, null, null);
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
            xBk = null;
            excel = null;
            xSt = null;
            GC.Collect();
        }
    }
}