C#实现Excel导出

Excel导出广泛应用于管理、统计、金融等众多领域。

C#实现Excel导出需要引用Aspose.Cells。

Excel导出方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Aspose.Cells;
using System.Data;

/// <summary>
/// Excel 文件操作
/// </summary>
public class ExcelFile
{

    /// <summary>
    /// 获取工作本
    /// </summary>
    /// <returns>Workbook</returns>
    public static Workbook GetWorkBook()
    {
        Workbook workbook = new Workbook(); //工作簿 
        return workbook;
    }


    /// <summary>
    /// DataTable转Excel
    /// </summary>
    /// <param name="workbook">工作薄</param>
    /// <param name="dataTable">datatable 数据源</param>
    /// <param name="tableName">表格名称</param>
    /// <param name="exStyle">excel 整体样式</param>
    /// <returns>MemoryStream</returns>
    public static System.IO.MemoryStream OutStream(Workbook workbook, DataTable dataTable, string tableName, ExcelStyle exStyle)
    {
        Worksheet sheet = workbook.Worksheets[0]; //工作表 
        Cells cells = sheet.Cells;//单元格 
        int Colnum = dataTable.Columns.Count;//表格列数 
        int Rownum = dataTable.Rows.Count;//表格行数 
        int index = 0;

        if (tableName != null)
        {
            //生成行1 标题行    
            cells.Merge(0, 0, 1, Colnum);//合并单元格 
            cells[index, 0].PutValue(tableName);//填写内容 
            if (exStyle.TableNameStyle != null)
            {
                cells[index, 0].SetStyle(exStyle.TableNameStyle);
            }
            index++;
            cells.SetRowHeight(0, 38);
        }

        //生成行2 列名行 
        for (int i = 0; i < Colnum; i++)
        {
            cells[index, i].PutValue(dataTable.Columns[i].ColumnName);
            if (exStyle.TitleStyle != null)
            {
                cells[index, i].SetStyle(exStyle.TitleStyle);
            }
            cells.SetRowHeight(index, 25);
        }

        index++;
        //生成数据行 
        for (int i = 0; i < Rownum; i++)
        {
            for (int k = 0; k < Colnum; k++)
            {
                cells[index + i, k].PutValue(dataTable.Rows[i][k].ToString());
                if (exStyle.ContentStyle != null)
                {
                    cells[index + i, k].SetStyle(exStyle.ContentStyle);
                }
            }
            cells.SetRowHeight(index + i, 24);
        }
        int columnCount = cells.MaxColumn;  //获取表页的最大列数
        int rowCount = cells.MaxRow;        //获取表页的最大行数

        for (int col = 0; col < columnCount; col++)
        {
            sheet.AutoFitColumn(col, 0, rowCount);
        }
        //for (int col = 0; col < columnCount; col++)
        //{
        //    cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
        //}

        System.IO.MemoryStream ms = workbook.SaveToStream();
        return ms;
    }


    /// <summary>
    /// DataTable转Excel
    /// </summary>
    /// <param name="workbook">工作薄</param>
    /// <param name="dataTable">datatable 数据源</param>
    /// <param name="tableName">表格名称</param>
    /// <param name="reportInfo">报表信息</param>
    /// <param name="exStyle">excel 整体样式</param>
    /// <returns>MemoryStream</returns>
    public static System.IO.MemoryStream OutStream(Workbook workbook, DataTable dataTable, string tableName, string reportInfo, ExcelStyle exStyle)
    {
        Worksheet sheet = workbook.Worksheets[0]; //工作表 
        Cells cells = sheet.Cells;//单元格 
        int Colnum = dataTable.Columns.Count;//表格列数 
        int Rownum = dataTable.Rows.Count;//表格行数 
        int index = 0;

        if (tableName != null)
        {
            //生成行1 标题行    
            cells.Merge(0, 0, 1, Colnum);//合并单元格 
            cells[index, 0].PutValue(tableName);//填写内容 
            if (exStyle.TableNameStyle != null)
            {
                cells[index, 0].SetStyle(exStyle.TableNameStyle);
            }
            index++;
            cells.SetRowHeight(0, 38);
        }


        if (reportInfo != null)
        {
            //生成行2 标题行    
            cells.Merge(1, 0, 1, Colnum);//合并单元格 
            cells[index, 0].PutValue(reportInfo);//填写内容 
            if (exStyle.TableNameStyle != null)
            {
                cells[index, 0].SetStyle(exStyle.ReprotInfoStyle);
            }
            index++;
            cells.SetRowHeight(0, 38);
        }

        //生成行1/2/3 列名行 
        for (int i = 0; i < Colnum; i++)
        {
            cells[index, i].PutValue(dataTable.Columns[i].ColumnName);
            if (exStyle.TitleStyle != null)
            {
                cells[index, i].SetStyle(exStyle.TitleStyle);
            }
            cells.SetRowHeight(index, 25);
        }

        index++;
        //生成数据行 
        for (int i = 0; i < Rownum; i++)
        {
            for (int k = 0; k < Colnum; k++)
            {
                cells[index + i, k].PutValue(dataTable.Rows[i][k].ToString());
                if (exStyle.ContentStyle != null)
                {
                    cells[index + i, k].SetStyle(exStyle.ContentStyle);
                }
            }
            cells.SetRowHeight(index + i, 24);
        }
        int columnCount = cells.MaxColumn;  //获取表页的最大列数
        int rowCount = cells.MaxRow;        //获取表页的最大行数

        for (int col = 0; col < columnCount; col++)
        {
            sheet.AutoFitColumn(col, 0, rowCount);
        }
        for (int col = 0; col < columnCount; col++)
        {
            cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
        }

        System.IO.MemoryStream ms = workbook.SaveToStream();
        return ms;
    }
}


/// <summary>
/// Excle样式定义类
/// </summary>
public class ExcelStyle
{
    private Style tableNameStyle;
    /// <summary>
    /// 表名称样式
    /// </summary>
    public Style TableNameStyle
    {
        get { return tableNameStyle; }
        set { tableNameStyle = value; }
    }

    private Style reprotInfoStyle;
    /// <summary>
    /// 统计报表信息样式
    /// </summary>
    public Style ReprotInfoStyle
    {
        get { return reprotInfoStyle; }
        set { reprotInfoStyle = value; }
    }


    private Style titleStyle;
    /// <summary>
    /// 表头样式
    /// </summary>
    public Style TitleStyle
    {
        get { return titleStyle; }
        set { titleStyle = value; }
    }
    private Style contentStyle;
    /// <summary>
    /// 表格类容样式
    /// </summary>
    public Style ContentStyle
    {
        get { return contentStyle; }
        set { contentStyle = value; }
    }
}

调用方法

public void Excel(){
	DataTable dataTable = dataTable ();//执行SQL语句返回DataTable
	
	Aspose.Cells.Workbook workbook = ExcelFile.GetWorkBook();
	//为标题设置样式
	Aspose.Cells.Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式
	styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
	styleTitle.Font.Name = "宋体";//文字字体
	styleTitle.Font.Size = 18;//文字大小
	styleTitle.Font.IsBold = true;//粗体
	//样式2
	Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式
	style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中
	style2.Font.Name = "宋体";//文字字体
	style2.Font.Size = 14;//文字大小
	style2.Font.IsBold = true;//粗体
	style2.IsTextWrapped = false;//单元格内容自动换行
	style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
	//样式3
	Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()];//新增样式
	style3.HorizontalAlignment = TextAlignmentType.Center;//文字居中
	style3.Font.Name = "宋体";//文字字体
	style3.Font.Size = 12;//文字大小
	style3.IsTextWrapped = false;
	style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
	style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
	style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
	style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
	ExcelStyle exStyle = new ExcelStyle();
	exStyle.ContentStyle = style3;
	exStyle.TitleStyle = style2;
	exStyle.TableNameStyle = styleTitle;
	System.IO.MemoryStream ms = ExcelFile.OutStream(workbook, dataTable, "Excel文件名", exStyle);
	HttpResponse response = HttpContext.Current.Response;
	response.Clear();
	response.Buffer = true;
	response.Charset = "utf-8";
	response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode("Excel文件名.xls"));
	response.ContentEncoding = System.Text.Encoding.UTF8;
	response.ContentType = "application/ms-excel; charset=UTF-8 ";
	response.BinaryWrite(ms.ToArray());
	response.End();
}