网页上传excel文件到服务器，服务端用NPOI解析excel

最新推荐文章于 2024-06-19 16:18:39 发布

壁立千仞无欲则刚

最新推荐文章于 2024-06-19 16:18:39 发布

阅读量3k

点赞数

分类专栏：读取excel 文章标签： NPOI excel aspx c#

本文链接：https://blog.csdn.net/wzj0808/article/details/51372218

版权

读取excel 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

aspx：

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

<!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>

        <table style="width: 343px">
            <tr>
                <td style="width: 100px">单文件上传</td>
                <td style="width: 100px"></td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:FileUpload ID="FileUpload1" runat="server" Width="475px" />
                </td>
                <td style="width: 100px">
                    <asp:Button ID="bt_upload" runat="server" OnClick="bt_upload_Click" Text="上传" />
                </td>
            </tr>
            <tr>
                <td style="width: 100px; height: 21px;">
                    <asp:TextBox TextMode="MultiLine" ID="lb_info" runat="server" ForeColor="Red" Width="1000px" Height="1000px"></asp:TextBox>
                </td>
                <td style="width: 100px; height: 21px">
                </td>
            </tr>
        </table>

    </div>
    </form>
</body>
</html>

aspx.cs：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;

using Agiso.DBAccess;

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

public partial class UpdateAddi_MyLoad : System.Web.UI.Page
{
    string serverpath = "";
    DataTable data = null;
    DataTable oldData = null;

    string cellValue = "";
    string dataLabel = "";

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void bt_upload_Click(object sender, EventArgs e)
    {
        try
        {
            if (FileUpload1.PostedFile.FileName == "")
            {
                this.lb_info.Text = "请选择文件！";
            }
            else
            {
                string filepath = FileUpload1.PostedFile.FileName;
                string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1);
                serverpath = Server.MapPath("../files/UploadFile/") + filename;
                FileUpload1.PostedFile.SaveAs(serverpath);
                this.lb_info.Text += "文件路径=" + filepath;
                this.lb_info.Text += "上传成功！";

                data = GetDataFromExcelByNPOI();

                this.lb_info.Text = "";
                

                //取出所有标题
                for (int i = 0; i < data.Columns.Count; i++)
                {
                    lb_info.Text += ("{" + data.Columns[i] + "}");
                }

                lb_info.Text += "\r\n";
                lb_info.Text += "//---------以上是标题，以下是单元格---------------------------";
                lb_info.Text += "\r\n";
                //取出所有标题以外的单元格
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    for (int j = 0; j < data.Columns.Count; j++)
                    {
                        if (data.Rows[i][j].ToString() == "")
                        {
                            cellValue = "------";
                        }
                        else
                        {
                            cellValue = data.Rows[i][j].ToString();
                        }
                        lb_info.Text += ("{" + cellValue + "}");
                    }
                    lb_info.Text += "\r\n";
                }
            }
            //读取数据库
            oldData = GetOrderData();
            for (int k = 0; k < oldData.Rows.Count; k++) 
            {
                for (int v = 0; v < oldData.Columns.Count; v++) 
                {
                    switch(v)
                    {
                        case 0:
                            dataLabel="NumIid=";
                            break;
                        case 1:
                            dataLabel="Title=";
                            break;
                        case 2: 
                            dataLabel="PicUrl=";
                            break;
                        case 3:
                            dataLabel="OuterId=";
                            break;
                        case 4:
                            dataLabel="Additional=";
                            break;
                        default:
                            break;
                    }
                    lb_info.Text+=dataLabel;
                    lb_info.Text += oldData.Rows[k][v].ToString();
                    lb_info.Text += "\r\n";
                }
            }
        }
        catch (Exception ex)
        {
            this.lb_info.Text = "上传发生错误！原因是：" + ex.ToString();
        }
    }

    DataTable GetOrderData()
    {
        IDbAccess db = DbAccessDAL.CreateDbAccess();
        return db.ExecuteTable(string.Format("SELECT NumIid, Title, PicUrl,OuterId, Additional FROM AutoDummySendItemDefine WHERE Nick={0}", DbUtil.ToSqlString("***")));
    }

    DataTable GetDataFromExcelByNPOI()
    {
        IWorkbook workbook;

        if (serverpath == "") return null;

        string fileType = Path.GetExtension(serverpath);

        using (FileStream file = new FileStream(serverpath, FileMode.Open, FileAccess.Read))
        {
            if (fileType == ".xls")
            {
                workbook = new HSSFWorkbook(file);
            }
            else if (fileType == ".xlsx")
            {
                workbook = new XSSFWorkbook(file);
            }
            else
            {
                return null;
            }


            ISheet sheet = workbook.GetSheetAt(0);//取第一个表
            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

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

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

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

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

    }

    private object GetCellValue(ICell cell)
    {
        object value = null;
        try
        {
            if (cell.CellType != CellType.Blank)
            {
                switch (cell.CellType)
                {
                    case CellType.Numeric:
                        // Date comes here
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            value = cell.DateCellValue;
                        }
                        else
                        {
                            // Numeric type
                            value = cell.NumericCellValue;
                        }
                        break;
                    case CellType.Boolean:
                        // Boolean type
                        value = cell.BooleanCellValue;
                        break;
                    case CellType.Formula:
                        value = cell.CellFormula;
                        break;
                    default:
                        // String type
                        value = cell.StringCellValue;
                        break;
                }
            }
        }
        catch (Exception)
        {
            value = "";
        }

        return value;
    }
}