NPOI与DataTable导出导入

 
转自：http://www.yongfa365.com/Item/NPOI-MyXls-DataTable-To-Excel-From-Excel.html

NPOI开源地址：http://npoi.codeplex.com/
NPOI中文文档：http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html
MyXls开源地址：http://sourceforge.net/projects/myxls/

NPOI 快速入门例子:

/// <summary>    
/// NPOI简单Demo，快速入门代码    
/// </summary>    
/// <param name="dtSource"></param>    
/// <param name="strFileName"></param>    
/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>    
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>    
publicstaticvoid ExportEasy(DataTable dtSource, string strFileName)    
{    
     HSSFWorkbook workbook = new HSSFWorkbook();    
     HSSFSheet sheet = workbook.CreateSheet();    
   
    //填充表头    
     HSSFRow dataRow = sheet.CreateRow(0);    
    foreach (DataColumn column in dtSource.Columns)    
     {    
         dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);    
     }    
   
   
    //填充内容    
    for (int i = 0; i < dtSource.Rows.Count; i++)    
     {    
         dataRow = sheet.CreateRow(i + 1);    
        for (int j = 0; j < dtSource.Columns.Count; j++)    
         {    
             dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());    
         }    
     }    
   
   
    //保存    
    using (MemoryStream ms = new MemoryStream())    
     {    
        using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))    
         {    
             workbook.Write(fs);    
         }    
     }    
     workbook.Dispose();    
}  

接下来是柳永法(yongfa365)'Blog封装的可以用在实际项目中的类，实现的功能有（仅NPOI）：

1. 支持web及winform从DataTable导出到Excel。
2. 生成速度很快。
3. 准确判断数据类型，不会出现身份证转数值等上面提到的一系列问题。
4. 如果单页条数大于65535时会新建工作表。
5. 列宽自适应。
6. 支持读取Excel。
7. 调用方便，只一调用一个静态类就OK了。

因为测试期间发现MyXls导出速度要比NPOI慢3倍，而NPOI既能满足我们的导出需求，又能很好的满足我们的导入需求，所以只针对NPOI进行全方位功能实现及优化。

NPOI导入导出相关类：

using System;   
using System.Collections.Generic;   
using System.Data;   
using System.IO;   
using System.Text;   
using System.Web;   
using NPOI;   
using NPOI.HPSF;   
using NPOI.HSSF;   
using NPOI.HSSF.UserModel;   
using NPOI.HSSF.Util;   
using NPOI.POIFS;   
using NPOI.Util;   


public class ExcelHelper   
{   
    /// <summary>   
    /// DataTable导出到Excel文件   
    /// </summary>   
    /// <param name="dtSource">源DataTable</param>   
    /// <param name="strHeaderText">表头文本</param>   
    /// <param name="strFileName">保存位置</param>   
    /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
    public static void Export(DataTable dtSource, string strHeaderText, string strFileName)   
    {   
        using (MemoryStream ms = Export(dtSource, strHeaderText))   
        {   
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))   
            {   
                byte[] data = ms.ToArray();   
                fs.Write(data, 0, data.Length);   
                fs.Flush();   
            }   
        }   
    }   

    /// <summary>   
    /// DataTable导出到Excel的MemoryStream   
    /// </summary>   
    /// <param name="dtSource">源DataTable</param>   
    /// <param name="strHeaderText">表头文本</param>   
    /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
    public static MemoryStream Export(DataTable dtSource, string strHeaderText)   
    {   
        HSSFWorkbook workbook = new HSSFWorkbook();   
        HSSFSheet sheet = workbook.CreateSheet(); 

        #region 右击文件 属性信息   
        {   
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();   
            dsi.Company = "http://www.yongfa365.com/";   
            workbook.DocumentSummaryInformation = dsi;   

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();   
            si.Author = "柳永法"; //填加xls文件作者信息   
            si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息   
            si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息   
            si.Comments = "说明信息"; //填加xls文件作者信息   
            si.Title = "NPOI测试"; //填加xls文件标题信息   
            si.Subject = "NPOI测试Demo";//填加文件主题信息   
            si.CreateDateTime = DateTime.Now;   
            workbook.SummaryInformation = si;   
        } 
        #endregion   

        HSSFCellStyle dateStyle = workbook.CreateCellStyle();   
        HSSFDataFormat format = workbook.CreateDataFormat();   
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");   

        //取得列宽   
        int[] arrColWidth = new int[dtSource.Columns.Count];   
        foreach (DataColumn item in dtSource.Columns)   
        {   
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;   
        }   
        for (int i = 0; i < dtSource.Rows.Count; i++)   
        {   
            for (int j = 0; j < dtSource.Columns.Count; j++)   
            {   
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;   
                if (intTemp > arrColWidth[j])   
                {   
                    arrColWidth[j] = intTemp;   
                }   
            }   
        }   



        int rowIndex = 0;   

        foreach (DataRow row in dtSource.Rows)   
        { 
            #region 新建表，填充表头，填充列头，样式   
            if (rowIndex == 65535 || rowIndex == 0)   
            {   
                if (rowIndex != 0)   
                {   
                    sheet = workbook.CreateSheet();   
                } 

                #region 表头及样式   
                {   
                    HSSFRow headerRow = sheet.CreateRow(0);   
                    headerRow.HeightInPoints = 25;   
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);   

                    HSSFCellStyle headStyle = workbook.CreateCellStyle();   
                    headStyle.Alignment = CellHorizontalAlignment.CENTER;   
                    HSSFFont font = workbook.CreateFont();   
                    font.FontHeightInPoints = 20;   
                    font.Boldweight = 700;   
                    headStyle.SetFont(font);   

                    headerRow.GetCell(0).CellStyle = headStyle;   

                    sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));   
                    headerRow.Dispose();   
                } 
                #endregion 


                #region 列头及样式   
                {   
                    HSSFRow headerRow = sheet.CreateRow(1);   


                    HSSFCellStyle headStyle = workbook.CreateCellStyle();   
                    headStyle.Alignment = CellHorizontalAlignment.CENTER;   
                    HSSFFont font = workbook.CreateFont();   
                    font.FontHeightInPoints = 10;   
                    font.Boldweight = 700;   
                    headStyle.SetFont(font);   


                    foreach (DataColumn column in dtSource.Columns)   
                    {   
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);   
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;   

                        //设置列宽   
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);   

                    }   
                    headerRow.Dispose();   
                } 
                #endregion   

                rowIndex = 2;   
            } 
            #endregion 


            #region 填充内容   
            HSSFRow dataRow = sheet.CreateRow(rowIndex);   
            foreach (DataColumn column in dtSource.Columns)   
            {   
                HSSFCell newCell = dataRow.CreateCell(column.Ordinal);   

                string drValue = row[column].ToString();   

                switch (column.DataType.ToString())   
                {   
                    case "System.String"://字符串类型   
                        newCell.SetCellValue(drValue);   
                        break;   
                    case "System.DateTime"://日期类型   
                        DateTime dateV;   
                        DateTime.TryParse(drValue, out dateV);   
                        newCell.SetCellValue(dateV);   

                        newCell.CellStyle = dateStyle;//格式化显示   
                        break;   
                    case "System.Boolean"://布尔型   
                        bool boolV = false;   
                        bool.TryParse(drValue, out boolV);   
                        newCell.SetCellValue(boolV);   
                        break;   
                    case "System.Int16"://整型   
                    case "System.Int32":   
                    case "System.Int64":   
                    case "System.Byte":   
                        int intV = 0;   
                        int.TryParse(drValue, out intV);   
                        newCell.SetCellValue(intV);   
                        break;   
                    case "System.Decimal"://浮点型   
                    case "System.Double":   
                        double doubV = 0;   
                        double.TryParse(drValue, out doubV);   
                        newCell.SetCellValue(doubV);   
                        break;   
                    case "System.DBNull"://空值处理   
                        newCell.SetCellValue("");   
                        break;   
                    default:   
                        newCell.SetCellValue("");   
                        break;   
                }   

            } 
            #endregion   

            rowIndex++;   
        }   


        using (MemoryStream ms = new MemoryStream())   
        {   
            workbook.Write(ms);   
            ms.Flush();   
            ms.Position = 0;   

            sheet.Dispose();   
           //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
            return ms;   
        }   

    }   


    /// <summary>   
    /// 用于Web导出   
    /// </summary>   
    /// <param name="dtSource">源DataTable</param>   
    /// <param name="strHeaderText">表头文本</param>   
    /// <param name="strFileName">文件名</param>   
    /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
    public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)   
    {   

        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(strFileName, Encoding.UTF8));   

        curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());   
        curContext.Response.End();   

    }   


    /// <summary>读取excel   
    /// 默认第一行为标头   
    /// </summary>   
    /// <param name="strFileName">excel文档路径</param>   
    /// <returns></returns>   
    public static DataTable Import(string strFileName)   
    {   
        DataTable dt = new DataTable();   

        HSSFWorkbook hssfworkbook;   
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))   
        {   
            hssfworkbook = new HSSFWorkbook(file);   
        }   
        HSSFSheet sheet = hssfworkbook.GetSheetAt(0);   
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();   

        HSSFRow headerRow = sheet.GetRow(0);   
        int cellCount = headerRow.LastCellNum;   

        for (int j = 0; j < cellCount; j++)   
        {   
            HSSFCell cell = headerRow.GetCell(j);   
            dt.Columns.Add(cell.ToString());   
        }   

        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)   
        {   
            HSSFRow row = sheet.GetRow(i);   
            DataRow dataRow = dt.NewRow();   

            for (int j = row.FirstCellNum; j < cellCount; j++)   
            {   
                if (row.GetCell(j) != null)   
                    dataRow[j] = row.GetCell(j).ToString();   
            }   

            dt.Rows.Add(dataRow);   
        }   
        return dt;   
    }   

} 

以上相关源码及测试用例下载地址：

http://download.csdn.net/source/2330821

参考地址：

NPOI导出Excel表功能实现(多个工作簿)：http://www.cnblogs.com/zhengjuzhuan/archive/2010/02/01/1661103.html
在 Server 端存取 Excel 檔案的利器：NPOI Library：http://msdn.microsoft.com/zh-tw/ee818993.aspx
ASP.NET使用NPOI类库导出Excel：http://www.cnblogs.com/niunan/archive/2010/03/30/1700706.html

总结：

　　通过以上分析，我们不难发现，用NPOI或MyXls代替是Excel是很明智的，在发文前，我看到NPOI及MyXls仍然在活跃的更新中。在使用过程中发现这两个组件极相似，以前看过文章说他们使用的内核是一样的。还有NPOI是国人开发的，且有相关中文文档，在很多地方有相关引用，下载量也很大。并且它支持Excel，看到MyXls相关问题基本上没人回答，所以推荐使用NPOI。MyXls可以直接Cell.Font.Bold操作，而NPOI得使用CellType多少感觉有点麻烦。