使用Aspose将DataTable转Excel

 

0.准备工作

 

1.下载并引入Aspose.Cells

下载Aspose Cells并引入using Aspose.Cells 下面示例中用的是.net 3.0版本的Aspose Cells，编译环境VS2013

具体下载和引入方法见：http://www.cnblogs.com/moonache/p/4991459.html

1.使用Aspose将DataTable转为Excel

 

1.代码

下面代码用于将DataTable dt 转为Excel文件并存在path目录下

/// <summary>
/// DataTable转Excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="path"></param>
/// <returns></returns>
public static bool ExportExcelWithAspose(DataTable dt, string path)
{

    if (dt != null)
    {
        try
        {
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
            //为head添加样式      
            Aspose.Cells.Style headStyle = workbook.Styles[workbook.Styles.Add()];
            //设置居中  
            headStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            //设置背景颜色  
            headStyle.ForegroundColor = System.Drawing.Color.FromArgb(215, 236, 241);
            headStyle.Pattern = BackgroundType.Solid;
            headStyle.Font.Size = 12;
            headStyle.Font.Name = "宋体";
            headStyle.Font.IsBold = true;

            //为单元格添加样式      
            Aspose.Cells.Style cellStyle = workbook.Styles[workbook.Styles.Add()];
            //设置居中
            cellStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            cellStyle.Pattern = BackgroundType.Solid;
            cellStyle.Font.Size = 12;
            cellStyle.Font.Name = "宋体";

            //设置列宽 从0开始 列宽单位是字符
            cellSheet.Cells.SetColumnWidth(1, 43);
            cellSheet.Cells.SetColumnWidth(5, 12);
            cellSheet.Cells.SetColumnWidth(7, 10);
            cellSheet.Cells.SetColumnWidth(8, 14);
            cellSheet.Cells.SetColumnWidth(9, 14);


            int rowIndex = 0;
            int colIndex = 0;
            int colCount = dt.Columns.Count;
            int rowCount = dt.Rows.Count;
            //Head 列名处理
            for (int i = 0; i < colCount; i++)
            {
                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
                cellSheet.Cells[rowIndex, colIndex].SetStyle(headStyle);
                colIndex++;
            }
            rowIndex++;
            //Cell 其它单元格处理
            for (int i = 0; i < rowCount; i++)
            {
                colIndex = 0;
                for (int j = 0; j < colCount; j++)
                {
                    cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                    cellSheet.Cells[rowIndex, colIndex].SetStyle(cellStyle);
                    colIndex++;
                }
                rowIndex++;
            }
            cellSheet.AutoFitColumns();  //列宽自动匹配，当列宽过长是收缩
            path = Path.GetFullPath(path);
            //workbook.Save(path,SaveFormat.CSV);  
            workbook.Save(path);
            return true;
        }
        catch (Exception e)
        {
            throw new Exception("导出Excel失败" + e.Message);
        }
    }
    else
    {
        return false;
    }

}

下面代码用于直接在页面输出Excel文件，供用户下载

WonderTools.ExportExcelWithAspose(templateDt,filePath);

//提供excel的下载
HttpResponse _Response = HttpContext.Current.Response;
_Response.Clear();
_Response.ClearHeaders();
_Response.Buffer = false;
_Response.ContentType = "application/x-excel";
_Response.AppendHeader("Content-Disposition", "attachment;filename=Template.xlsx");
_Response.WriteFile(fileInfo.FullName);
_Response.Flush();
_Response.End();

PS:

更多Excel格式设置 ：http://blog.sina.com.cn/s/blog_6438b0e50101cfer.html

官方手册：http://www.aspose.com/docs/display/cellsnet/Introduction+of+Aspose.Cells+for+.NET

 

PPS:

直接通过 ashx 获取数据库中的数据并下载 Excel

public void ProcessRequest(HttpContext context)
{
    context.Response.ContentType = "text/plain";
    context.Response.AddHeader("Content-Disposition",
                               "attachment;filename=" + context.Server.UrlEncode("数据.xlsx"));

    using (var dt = SqlHelper.ExecuteQuery("SELECT * FROM T_Users"))
    {
        var workbook = new Workbook();
        var sheet = workbook.Worksheets[0];

        // Header
        for (var i = 0; i < dt.Columns.Count; i++)
        {
            sheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
        }
        // Content
        for (var i = 0; i < dt.Rows.Count; i++)
        {
            for (var j = 0; j < dt.Columns.Count; j++)
            {
                sheet.Cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());
            }
        }
        workbook.Save(context.Response.OutputStream,SaveFormat.Xlsx);
    }
}