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

来源：互联网发布：淘宝助理模板数据包编辑：程序博客网时间：2024/06/09 18:07

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;    }}

0 0