.Net操作Excel

先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。

.Net导出代码：

 

 /// <summary>
    /// 导出
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void But_Excel_Click(object sender, EventArgs e)
    {
        string[] fileName = { "State_title", "Last_url", "State_url", "IsNewUser", "State_time", "IpAddress", "ExtStr1", "ExtStr2", "VisitTime", "VisitWay", "KeyWords", "UserAction", "Remark" };
        string[] title = { "受访网页", "来源网页", "受访网页URL", "新老访客", "访问时长", "IP地址", "地域", "访客标识码", "访问时间", "浏览方式", "关键字", "用户行为", "备注" };
        DataToExcel.ExportSiteState(fileName, title, "tb_SiteStats", "列表_" + DateTime.Now.ToString("yyy-MM-dd"), strWhere, " VisitTime desc ");
    }

 

/// <summary>
        /// add by lwf 2016-07-13 16:22:19
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="title"></param>
        /// <param name="tableName"></param>
        /// <param name="sheetName"></param>
        /// <param name="strWhere"></param>
        /// <param name="sort"></param>
        public static void ExportSiteState(string[] fileName, string[] title, string tableName, string sheetName, string strWhere, string sort)
        {
            string sql = "select ";
            if (fileName == null || fileName.Length < 1)
            {
                sql += "* ";
            }
            else
            {
                foreach (string key in fileName)
                {
                    sql += key + ",";
                }
                sql = System.Text.RegularExpressions.Regex.Replace(sql, ",$", " ");
            }
            sql += "from " + tableName;
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += " where " + strWhere;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sql += " order by " + sort;
            }

            //获取需要导出的数据
            DataTable dt = DbHelperSQLServer.Query(sql).Tables[0];
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.Row row = sheet.CreateRow(0);

            //设置列名称和数据
            if (title == null || title.Length < 1)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            else
            {
                for (int i = 0; i < title.Length; i++)
                {
                    row.CreateCell(i).SetCellValue(title[i].ToString());
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < fileName.Length; j++)
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
                }
            }
            //写入到客户端   
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Export_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
            System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }

DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)：

using System;
using System.Collections.Generic;
using System.Text;
using Maticsoft.DBUtility;
using System.Collections;
using System.Data;
using System.Web;
using DAL;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
namespace DAL
{
   public class DataToExcel
    {/// <summary>
     /// DataTable转换成Excel文档流，并输出到客户端
     /// </summary>
     /// <param name="table"></param>
     /// <param name="response"></param>
     /// <param name="fileName">输出的文件名</param>
        public static void RenderToDataTableToExcel(DataTable table,HttpContext context, string fileName)
        {
            using (MemoryStream ms = ExportDataTableToExcel(table))
            {
                RenderToBrowser(ms, context, fileName);
            }
        }

        /// <summary>
        /// DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static MemoryStream ExportDataTableToExcel(DataTable sourceTable)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            int dtRowsCount = sourceTable.Rows.Count;
            int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536));
            int SheetNum = 1;
            int rowIndex = 1;
            int tempIndex = 1; //标示 
            ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum);
            for (int i = 0; i < dtRowsCount; i++)
            {
                if (i == 0 || tempIndex == 1)
                {
                    IRow headerRow = sheet.CreateRow(0);
                    foreach (DataColumn column in sourceTable.Columns)
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());
                }
                if (tempIndex == 65535)
                {
                    SheetNum++;
                    sheet = workbook.CreateSheet("sheet" + SheetNum);//
                    tempIndex = 0;
                }
                rowIndex++;
                tempIndex++;
                //AutoSizeColumns(sheet);
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            // headerRow = null;
            workbook = null;
            return ms;
        }

        /// <summary>
        /// 输出文件到浏览器
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">文件名</param>
        private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.BinaryWrite(ms.ToArray());
        }
    }
}

 

.NET导入代码：

HSSFWorkbook hssfworkbook;  
#region  
public DataTable ImportExcelFile(string filePath)  
{  
    #region//初始化信息  
    try  
    {  
        using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))  
        {  
            hssfworkbook = new HSSFWorkbook(file);  
        }  
    }  
    catch (Exception e)  
    {  
        throw e;  
    }  
    #endregion  
  
    NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0);  
    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();  
    DataTable dt = new DataTable();  
    for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)  
    {  
        dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());  
    }  
    while (rows.MoveNext())  
    {  
        HSSFRow row = (HSSFRow)rows.Current;  
        DataRow dr = dt.NewRow();  
        for (int i = 0; i < row.LastCellNum; i++)  
        {  
            NPOI.SS.UserModel.Cell cell = row.GetCell(i);  
            if (cell == null)  
            {  
                dr[i] = null;  
            }  
            else  
            {  
                dr[i] = cell.ToString();  
            }  
        }  
        dt.Rows.Add(dr);  
    }  
    return dt;  
}  
#endregion  

NPOIbinary2.1.3.1.zip下载