将excel中数据显示在页面上,然后再导入到SqlServer数据库中

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

 

// 自己建的...-----------

using NewExcenter.ExpoCentre.DataTransferObject;
using NewExcenter.ExpoCentre.Control;
using NewExcenter.WebUtil;
using NewExcenter.Util;

//-------------------------
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;
using System.Reflection;

 

// BasePage为自定义的基类

public partial class ExportExcelTest : BasePage
{

 

 =============================================

展会预付款DataSet _ds = new 展会预付款DataSet();

 =============================================

    protected void Page_Load(object sender, EventArgs e)
    {

    }

==============================================

// 点击预览按扭事件

protected void Preview_Click(object sender, EventArgs e)
    {       
        if (this.ExcelFileUpload.PostedFile.ContentType != "application/vnd.ms-excel")
        {
            _errorMessageId = ExpoConstant.MSG_W103;//页面显示错误信息(格式错误)
            return;
        }
        _errorMessage = string.Empty;//将错误信息置空

        // 保存文件
        string file = System.IO.Path.GetFileName(this.ExcelFileUpload.PostedFile.FileName);

 

       // 文件后缀名".xls"
        string fileExtension = System.IO.Path.GetExtension(file);
        string fileName = file.Substring(0, file.LastIndexOf("."));

 

      // 重新设置文件名为:原文件名+当前时间
        fileName += _nowTime.ToString("yyMMddHHmmssfff");

 

      // 文件暂时保存路径
        string serverPath = Server.MapPath("../upload/") + fileName + fileExtension;
        this.ExcelFileUpload.SaveAs(serverPath);

        string excelFilePath = serverPath;
        _ds = GetExcel(excelFilePath);// 取得excel中数据放到DataSet中
        if (_ds != null)
        {
            this.ExcelGridView.DataSource = _ds.T_展会预付款.DefaultView;
            this.ExcelGridView.DataBind();//绑定GridView

            if (_ds.T_展会预付款.Rows.Count > 0)
            {
                this.BtnExport.Enabled = true;
                _saveDataSet(_ds);//将DataSet保存到session中
            }
            else
            {
                this.BtnExport.Enabled = false;
            }
        }
        else
        {
            this.ExcelGridView.DataSource = null;
            this.ExcelGridView.DataBind();

            this.BtnExport.Enabled = false;
        }

        File.Delete(excelFilePath);
    }

 

==============================================

// 点击导入按扭事件

protected void Export_Click(object sender, EventArgs e)
    {
        _ds = (展会预付款DataSet)_loadDataSet(_ds);//从session中取得DataSet
        if (_ds != null)
        {
            UpdateDataSet(_ds);// 保存更新DataSet
            _releaseDataSet(_ds);// 将DataSet从session中清除
            this.BtnExport.Enabled = false;//导入按扭不可用
        }
    }

 

==========================================

 #region 读取Excel文件数据到DataSet

    Application oXL;
    private 展会预付款DataSet GetExcel(string filename)
    {

        Workbook oWB;
        Worksheet oSheet;
        Range oRng;

        展会预付款DataSet ds = new 展会预付款DataSet();

        try
        {
            oXL = new ApplicationClass();
            oWB = oXL.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                 Missing.Value, Missing.Value);

            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];

 

            // Excel文件格式判断(这两个if可选)
            oRng = GetExcelPoint("金额", oSheet);//判断金额字段是否在excel中存在
            if (oRng == null)
            {
                throw new Exception();
            }
            oRng = GetExcelPoint("支付方式", oSheet);
            if (oRng == null)
            {
                throw new Exception();
            }
            return GetFileData(ds, oSheet, 1);
        }
        catch (Exception ex)
        {
            _errorMessageId = ExpoConstant.MSG_W104;
            return null;
        }
        finally
        {
            oXL.Application.Workbooks.Close();
            oXL.Application.Quit();
            oXL.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
            System.GC.Collect();
        }
    }

 

==================================================

 

    private 展会预付款DataSet GetFileData(展会预付款DataSet ds, Worksheet oSheet, int i)
    {
        int xRow;
        int yColumn;
        Range oRng;

        展会预付款DataSet.T_展会预付款Row row = ds.T_展会预付款.NewT_展会预付款Row();

     

      // 循环 将excel中各字段的值设置到 展会预付款DataSet的T_展会预付款表 对应的各列

        foreach (DataColumn col in ds.T_展会预付款.Columns)
        {
            oRng = GetExcelPoint(col.ToString(), oSheet);
            if (oRng != null)
            {
                xRow = Convert.ToInt32(oRng.Rows.Row);
                yColumn = Convert.ToInt32(oRng.Columns.Column);
                oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[xRow + i, yColumn];
                if (col.ToString() == "金额" || col.ToString() == "支付方式")
                {
                    if (oRng.Text.ToString() == string.Empty)
                    {
                        return ds;
                    }
                }
                row[col.ToString()] = oRng.Text.ToString();
            }
        }

     

      // 展会预付款DataSet的T_展会预付款表 有的列在excel中不存在,但是又不可为空,所以要设值

        row.付款单位ID = 4;
        row.展会ID = 1;
        row.结算状态 = "0";
        row.做成日 = row.更新日 = _nowTime;
        row.做成者 = row.更新者 = _userId;
        row.逻辑删除 = "0";

        ds.T_展会预付款.AddT_展会预付款Row(row);

 

        // 读取Excel下一行数据
        i++;
        return GetFileData(ds, oSheet, i);//递归
    }

 

=================================================

//判断name字段是否在excel中存在

 

    private Microsoft.Office.Interop.Excel.Range GetExcelPoint(string name, Worksheet oSheet)
    {
        return oSheet.UsedRange.Find(name, Type.Missing,
                                            Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
                                            Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext,
                                        false, Type.Missing, Type.Missing);
    }
    #endregion

 

 

}