2 dailiboy dailiboy 于 2018.01.03 21:38 提问

NPOI导入EXCEL,请解决下错误问题!!!

.aspx文件

 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="NPOIcheshi.aspx.cs" Inherits="NPOIcheshi" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="ExcelFileUpload" runat="server" />&nbsp;&nbsp;&nbsp;&nbsp;<asp:Button runat="server" ID="scBtn" Text="上传" OnClick="scBtn_Click" />&nbsp;&nbsp;&nbsp;&nbsp;<asp:Label runat="server" ID="labelrow" Text="5"></asp:Label></div>
    <div>
    <br />
    <br />
    <br />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

.CS文件

 using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;

public partial class NPOIcheshi : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    private string fileName = null; //文件名
    private IWorkbook workbook = null;
    private FileStream fs = null;
    private bool disposed;

    /// <summary>
    /// 将excel中的数据导入到DataTable中
    /// </summary>
    /// <param name="sheetName">excel工作薄sheet的名称</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns>返回的DataTable</returns>
    public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
    {
        ISheet sheet = null;
        DataTable data = new DataTable();
        int startRow = 0;
        try
        {
            fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            //if (fileName.IndexOf(".xlsx") > 0) // 2007版本
            //    workbook = new XSSFWorkbook(fs);
            //else if (fileName.IndexOf(".xls") > 0) // 2003版本
            workbook = new HSSFWorkbook(fs);

            if (sheetName != null)
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }

                //最后一列的标号
                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null       

                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception: " + ex.Message);
            return null;
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (!this.disposed)
        {
            if (disposing)
            {
                if (fs != null)
                    fs.Close();
            }

            fs = null;
            disposed = true;
        }
    }

    protected void scBtn_Click(object sender, EventArgs e)
    {
        fileName = ExcelFileUpload.FileName.ToString();
        disposed = true;
        DataTable dataTable = new DataTable();
        dataTable = ExcelToDataTable("sheet1", true);
        labelrow.Text = dataTable.Rows.Count.ToString();
        GridView1.DataSource = dataTable;
        GridView1.DataBind();
    }
}

现在的错误是:
未将对象引用设置到对象的实例
行 138: labelrow.Text = dataTable.Rows.Count.ToString();

5个回答

hanyilong316
hanyilong316   2018.01.04 08:52
已采纳

肯定是这一步方法里已经出了问题了 dataTable = ExcelToDataTable("sheet1", true);

你把断点打在ExcelToDataTable这个方法里边,一步一步F10 ,看哪一行报的错;

caozhy
caozhy   Ds   Rxr 2018.01.03 23:13

catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
输出了什么错误?你丢出异常的话,返回null,所以labelrow.Text = dataTable.Rows.Count.ToString()的dataTable是null,也就是“未将对象引用设置到对象的实例”。但是究竟什么问题,要看你错误的输出。

Leaderxin
Leaderxin   2018.01.04 09:47

应该是datatable为Null,要进ExcelToDataTable方法一步一步调试看呢,看为什么返回结果为空

qq_36481052
qq_36481052   2018.01.04 11:06
dailiboy
dailiboy 你好,请问对NPOI熟悉吗?我在网上找的代码,对网上的代码进行了一定更改。如果熟悉的话,我想请你帮我看下,我是不是更改上出现了错误
大约 2 个月之前 回复
dailiboy
dailiboy   2018.01.04 20:05

问题解决了。。。非常低级的错误。文件都没有上传就开始读文件。。。。谢谢大家的回答。。。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!