asp.net 下载EXCEL文件

一、需要导入NPOI 库文件

打开VS2012 工具》》库程序包管理器》》管理解决方案的NuGet程序包，搜索NPOI，如下图

安装完成；

添加

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

方法一： LIst到处Excel文件

public void ListToExcelByNPOI(List<T>data)
{
    string pasthname = "结果-" + DateTime.Now.ToString("yyyy-MM-dd") + "导出" + ".xls";
    HSSFWorkbook workbook = new HSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("Sheet1");
     //获取公共属性由于做表头                   
　　var propertys = typeof(UploadTestResultInfos).GetProperties();
    List<string> title = new List<string>();
    foreach (PropertyInfo item in typeof(UploadTestResultInfos).GetProperties())
    {
             //if (!Ignore.IgnoreField(item.Name))
             //    continue;

             title.Add(item.Name);
    }

   var rowtitle = sheet.CreateRow(0);

    for (var i = 0; i < title.Count; i++)
     {
           rowtitle.CreateCell(i).SetCellValue(title[i]);
    }


                        for (var i = 0; i < data.Count; i++)
                        {
                            var row = sheet.CreateRow(i + 1);                     //因为表头名称占了一行，所以加1
                            for (var j = 0; j < propertys.Length; j++)
                            {
                                //if (!Ignore.IgnoreField(propertys[j].Name))
                                //    continue;

                                var obj = propertys[j].GetValue(data[i], null);
                                row.CreateCell(j).SetCellValue(obj.ToString().Trim());
                            }
                        }

                        using (MemoryStream ms = new MemoryStream())
                        {
                            workbook.Write(ms);
                            //Web导出
                            HttpContext curContext = HttpContext.Current;
                            curContext.Response.ContentType = "application/vnd.ms-excel";
                            curContext.Response.ContentEncoding = Encoding.UTF8;
                            curContext.Response.Charset = "";
                            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(pasthname, Encoding.UTF8));
                            curContext.Response.BinaryWrite(ms.GetBuffer());
                            curContext.Response.End();
                        }
}

方法二：Datatable 到处Excel表

private static void TableToExcelByNPOI(DataTable dt)
        {
            string strExcelFileName = "MCS测试结果-" + DateTime.Now.ToString("yyyy-MM-dd") + "导出" + ".xls";
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in dt.Columns)
                    {

                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                //FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
                //workbook.Write(file);
                //file.Flush();
                //file.Close();

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    //Web导出
                    HttpContext curContext = HttpContext.Current;
                    curContext.Response.BufferOutput = true;
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strExcelFileName, Encoding.UTF8));
                    curContext.Response.BinaryWrite(ms.GetBuffer());
                    curContext.Response.End();


                    //StringWriter sw = new StringWriter();
                    //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter();
                }

                //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger("Exception Log");
                //log.Error(ex.Message + Environment.NewLine + ex.StackTrace);
                ////记录AuditTrail
                //CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);

                //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally {  }

        }

 

另外导出方式使用using System.Data.OleDb;

类似操作数据库操作EXCEL文件

DataTable data = new DataTable();
            OleDbConnection connection = null;
            string strConn = null;
            if (filePath.IndexOf(".xlsx") > 0 || filePath.IndexOf(".XLSX") > 0) // 2007版本
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
            }
            else if (filePath.IndexOf(".xls") > 0 || filePath.IndexOf(".XLS") > 0) // 2003版本
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
            }

connection = new OleDbConnection(strConn);
connection.Open();
//获取Excel中所有Sheet表的信息
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet表名
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, connection);
myData.Fill(data);
connection.Close();
return data;