Asp.net程序中用NPOI生成标准Excel报表，导入导出一应俱全

上班第一份工作就是做程序数据的导入导出，听起来似乎挺Easy的，开始我也这样认为，但是在实际操作中却不同了...

　　以往的导出数据是用HTML标签拼接成Table，然后在一行一列的显示成Excel，其实不然，这种Excel是HTML版本的Excel，并不是标准的Excel，所以如果我们把刚导出的数据进行修改保存然后接着导入来更新数据库中的数据就no、no、no了，程序不识别这种格式，那怎么办呢，所以我找到了第三方控件NPOI，来分享一下....

　　首先我们回顾一下以往用导出HTML格式的Excel文件（如果不存在导入，这种方法完全可以，用NPOI就有点浪费）

　　

　  //普通形式的下载Excel（HTML格式的Excel）

　　//dt当然是你一些的要导出的数据返回一个DataTable
　　//fileName是自己定义的文件导出的名字
  　　 protected void CreateExcel(DataTable dt,string fileName)    
 　　 {    
  　　　　 StringBuilder strb = new StringBuilder();    
  　　　　 strb.Append(" <table align=\"center\" border='1px' style='border-collapse:collapse;table-layout:fixed;font-size:12px'> <tr>");    

   　　　　//写列标题    
   　　　　 int columncount = dt.Columns.Count;  
   　　　　for (int columi = 0; columi < columncount; columi++)    
   　　　　{    
   　　　　　　 strb.Append(" <td> <b>" + dt.Columns[columi] + " </b> </td>");    
   　　　　}    
  　　　　 strb.Append(" </tr>");    
   　　　　//写数据    
   　　　　 for (int i = 0; i < dt.Rows.Count; i++)    
   　　　　{    
    　　　　　　strb.Append(" <tr>");    
    　　　　　　for (int j = 0; j < dt.Columns.Count; j++)    
    　　　　　　{    
    　　　　　　　　 strb.Append(" <td>" + dt.Rows[i][j].ToString() + " </td>");    
    　　　　　　}    
    　　　　　　strb.Append(" </tr>");       }
  　　　　　　 strb.Append(" </table>"); 
  

   　　　　　　Response.Clear();     
   　　　　　　Response.Buffer=   true;     
   　　　　　　Response.Charset="GB2312";           
   　　　　　　Response.AppendHeader("Content-Disposition","attachment;filename=" + FileName + ".xls");     
   　　　　　　Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文   
    　　　　　　Response.ContentType   =   "application/vnd.xls";//设置输出文件类型为excel文件。     
    　　　　　　this.EnableViewState   =   false; 
   　　　　　　Response.Write(strb);    
   　　　　　　Response.End();
  　　　　} 

 

 

另外一种方法：

 

    /// <summary>
    /// 由DataTable导出Excel
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="fileName"></param>
    private void CreateExcel(DataTable dt, string fileName)
    {
        HttpResponse resp;
        resp = Page.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
        string colHeaders = "", ls_item = "";

        ////定义表对象与行对象
        //可以类似dt.Select("id>10")之形式达到数据筛选目的
        int i = 0;
        int cl = dt.Columns.Count;

        //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
        for (i = 0; i < cl; i++)
        {
            if (i == (cl - 1))//最后一列，加n
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\n";
            }
            else
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\t";
            }
        }
        resp.Write(colHeaders);
        //向HTTP输出流中写入取得的数据信息
        //逐行处理数据
        DataRow[] myRow = dt.Select();
        foreach (DataRow row in myRow)
        {
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据   
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    ls_item += row[i].ToString() + "\n";
                }
                else
                {
                    ls_item += row[i].ToString() + "\t";
                }
            }
            resp.Write(ls_item);
            ls_item = "";
        }
        resp.End();
    }

 

　　这样可以导出成Excel样式的文件，但它的扩张名并不是.xls,而是.xls.html,所以在导入的时候就做限制了。

　　如下代码，可以解决此类问题：

　　一、下载NPOI：http://down.gougou.com/down?cid=DAEA322D9D7F934B898077FB01C3A8CB02A746E6

　　二、项目添加引用；

　　三、首先把如下代码封装成一个ExcelHelper类；

　　四、调用方法。

　　

　　1、导出：分为DataSet多表导出，DataTable单表导出

　　首先解析一下由DataSet导出Excel　

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

public class ExcelHelper
{
    /// <summary>
    /// 由DataSet导出Excel
    /// </summary>
    /// <param name="sourceTable">要导出数据的DataTable</param>
    /// <param name="sheetName">工作表名称</param>
    /// <returns>Excel工作表</returns>
    private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream ms = new MemoryStream();
        string [] sheetNames = sheetName.Split(',');
        for(int i = 0;i< sheetNames.Length; i++)
        {
            HSSFSheet sheet = workbook.CreateSheet(sheetNames[i]);
            HSSFRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceDs.Tables[i].Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in sourceDs.Tables[i].Rows)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                {
    　　　　        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }
        }
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;
        
        workbook = null;
        return ms;
    }

    /// <summary>
    /// 由DataSet导出Excel
    /// </summary>
    /// <param name="sourceTable">要导出数据的DataTable</param>
    /// <param name="fileName">指定Excel工作表名称</param>
    /// <returns>Excel工作表</returns>
    public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
    {
        MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); 
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.End();
        ms.Close();
        ms = null;
    }

 

 

　　由DataTable导出Excel　　

/// <summary>
    /// 由DataTable导出Excel
    /// </summary>
    /// <param name="sourceTable">要导出数据的DataTable</param>
    /// <returns>Excel工作表</returns>
    private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream ms = new MemoryStream();
        HSSFSheet sheet = workbook.CreateSheet(sheetName);
        HSSFRow headerRow = sheet.CreateRow(0);
        // handling header.
        foreach (DataColumn column in sourceTable.Columns)
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

        // handling value.
        int rowIndex = 1;

        foreach (DataRow row in sourceTable.Rows)
        {
            HSSFRow dataRow = sheet.CreateRow(rowIndex);

            foreach (DataColumn column in sourceTable.Columns)
            {
    　　　　        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }

            rowIndex++;
        }

        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;

        sheet = null;
        headerRow = null;
        workbook = null;

        return ms;
    }
    /// <summary>
    /// 由DataTable导出Excel
    /// </summary>
    /// <param name="sourceTable">要导出数据的DataTable</param>
    /// <param name="fileName">指定Excel工作表名称</param>
    /// <returns>Excel工作表</returns>
    public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)
    {
        MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); 
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.End();
        ms.Close();
        ms = null;
    }

 

 

　　2、导入

　　由Excel导入DataTable

　　/// <summary>
    /// 由Excel导入DataTable
    /// </summary>
    /// <param name="excelFileStream">Excel文件流</param>
    /// <param name="sheetName">Excel工作表名称</param>
    /// <param name="headerRowIndex">Excel表头行索引</param>
    /// <returns>DataTable</returns>
    public static DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
    {
        HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
        HSSFSheet sheet = workbook.GetSheet(sheetName);

        DataTable table = new DataTable();

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

        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }

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

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

        excelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }

    /// <summary>
    /// 由Excel导入DataTable
    /// </summary>
    /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
    /// <param name="sheetName">Excel工作表名称</param>
    /// <param name="headerRowIndex">Excel表头行索引</param>
    /// <returns>DataTable</returns>
    public static DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
    {
        using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
        {
            return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);
        }
    }

    /// <summary>
    /// 由Excel导入DataTable
    /// </summary>
    /// <param name="excelFileStream">Excel文件流</param>
    /// <param name="sheetName">Excel工作表索引</param>
    /// <param name="headerRowIndex">Excel表头行索引</param>
    /// <returns>DataTable</returns>
    public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
    {
        HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
        HSSFSheet sheet = workbook.GetSheetAt(sheetIndex);

        DataTable table = new DataTable();

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

        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
            {
                // 如果遇到第一个空列，则不再继续向后读取
                cellCount = i + 1;
                break;
            }
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }

        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            HSSFRow row = sheet.GetRow(i);
            if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
            {
                // 如果遇到第一个空行，则不再继续向后读取
                break;
            }

            DataRow dataRow = table.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                dataRow[j] = row.GetCell(j);                
            }
            table.Rows.Add(dataRow);
        }
        excelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }
    /// <summary>
    /// 由Excel导入DataTable
    /// </summary>
    /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
    /// <param name="sheetName">Excel工作表索引</param>
    /// <param name="headerRowIndex">Excel表头行索引</param>
    /// <returns>DataTable</returns>
    public static DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex)
    {
        using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
        {
            return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex);
        }
    }

 

 

 　　有Excel导入DataSet　　

　　/// <summary>
    /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
    /// </summary>
    /// <param name="excelFileStream">Excel文件流</param>
    /// <param name="headerRowIndex">Excel表头行索引</param>
    /// <returns>DataSet</returns>
    public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
    {
        DataSet ds = new DataSet();
        HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
        for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
        {
            HSSFSheet sheet = workbook.GetSheetAt(a);
            DataTable table = new DataTable();

            HSSFRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    // 如果遇到第一个空列，则不再继续向后读取
                    cellCount = i + 1;
                    break;
                }

                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum ; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
                {
                    // 如果遇到第一个空行，则不再继续向后读取
                    break;
                }

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

                table.Rows.Add(dataRow);
            }
            ds.Tables.Add(table);
        }

        excelFileStream.Close();
        workbook = null;

        return ds;
    }

    /// <summary>
    /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
    /// </summary>
    /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
    /// <param name="headerRowIndex">Excel表头行索引</param>
    /// <returns>DataSet</returns>
    public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
    {
        using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
        {
            return ImportDataSetFromExcel(stream, headerRowIndex);
        }
    }

 

 

　　另外为导入补充一点知识：

　　1、将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...　　

/// <summary>
    /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
    /// </summary>
    /// <param name="index">列索引</param>
    /// <returns>列名，如第0列为A，第1列为B...</returns>
    public static string ConvertColumnIndexToColumnName(int index)
    {
        index = index + 1;
        int system = 26;
        char[] digArray = new char[100];
        int i = 0;
        while (index > 0)
        {
            int mod = index % system;
            if (mod == 0) mod = system;
            digArray[i++] = (char)(mod - 1 + 'A');
            index = (index - 1) / 26;
        }
        StringBuilder sb = new StringBuilder(i);
        for (int j = i - 1; j >= 0; j--) 
        {
            sb.Append(digArray[j]);
        }
        return sb.ToString(); 
    }

 

 　　2、当从Excel获取年月日时，会从在一定的问题，应该在一下代码中，可以想到存在的问题，所以我们可以写个方法封装一下：　

    /// <summary>
    /// 转化日期
    /// </summary>
    /// <param name="date">日期</param>
    /// <returns></returns>
    public static DateTime ConvertDate(string date)
    {
        DateTime dt = new DateTime();
        string[] time = date.Split('-');
        int year = Convert.ToInt32(time[2]);
        int month = Convert.ToInt32(time[0]);
        int day = Convert.ToInt32(time[1]);
        string years = Convert.ToString(year);
        string months = Convert.ToString(month);
        string days = Convert.ToString(day);
        if(months.Length == 4)
        {
            dt = Convert.ToDateTime(date);
        }
        else
        {
            string rq = "";
            if(years.Length == 1)
            {
                years = "0" + years;
            }
            if(months.Length == 1)
            {
                months = "0" + months;
            }
            if(days.Length == 1)
            {
                days = "0" + days;
            }
            rq = "20" + years + "-" + months  + "-" + days;
            dt = Convert.ToDateTime(rq);
        }
        return dt;
    }

}

 

　　分享一下体会：

　　其实有了DataSet的导入导出，DataTable就没有必要了，毕竟DataTable组成DataSet，dataSet自动分解为一个个的DataTable，所以一般我用只用DataSet

　　eg：

　　

//导出高低温类型产品信息
        private void btnExportCp_Click(object sender, System.EventArgs e)
        {
            string sql = @"
SELECT CPBM AS 产品编码,CPMC + GGXH AS 产品名称 FROM XS_CPBM WHERE CPLX = '03'AND ZT = '1'

SELECT CPBM AS 产品编码,CPMC + GGXH AS 产品名称 FROM XS_CPBM WHERE CPLX = '06'AND ZT = '1'
";
            DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text,sql);

            string fileName = "高低温产品档案导出.xls";
            ExcelHelper.ExportDataSetToExcel(ds,fileName,"高温,低温");
        
        }

 

 

　　OK啦，希望对大家有所帮助！