Asp.Net C# 哪裡好?

這功能很實用，學校單位都很喜歡，例如什麼國科會XX表

可以下載老師所有發展著作的EXCEL，然後就說要把這檔案匯入到學校系網站，對於使用者的操作，你看看，這多簡單

早期寫ASP還真覺得麻煩多了，自從學了Asp.Net 還真方便

來吧!!就來看看如何匯入匯出!!

前置作業

首先要在 NuGet 安裝 NPOI

或者 套件管理器主控台  Install-Package NPOI

匯出

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

//自己加的
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

public partial class NPOI_NPOI_Export : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string docupath = Request.PhysicalApplicationPath + "NPOI\\";

        //建立資料夾
        if (!Directory.Exists(docupath))
        {
            Directory.CreateDirectory(docupath);
        }

        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
        SqlDataReader dr = null;
        SqlCommand cmd = new SqlCommand("select * from Employees", Conn);

        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet1 = workbook.CreateSheet("Sheet1");
        XSSFCellStyle cs = (XSSFCellStyle)workbook.CreateCellStyle();

        //資料起始的列數 0為第1列
        int k = 1;
        try
        {
            Conn.Open();
            dr = cmd.ExecuteReader();

            //手動加入
            //sheet1.CreateRow(0).CreateCell(0).SetCellValue("手動加入資料欄位");
            //合併儲存格
            //sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, dr.FieldCount - 5));

            while (dr.Read())
            {
                //取得欄位名稱
                IRow u_RowTitle = sheet1.CreateRow(0);
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    u_RowTitle.CreateCell(i).SetCellValue(dr.GetName(i).ToString());
                }

                //取得資料
                IRow u_Row = sheet1.CreateRow(k);
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    u_Row.CreateCell(i).SetCellValue(dr.GetValue(i).ToString());
                }
                k++;
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
        finally
        {
            if (dr != null)
            {
                cmd.Cancel();
                dr.Close();
            }
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }

        FileStream sw = File.Create(docupath + "Employees.xlsx");
        workbook.Write(sw); sw.Close();

        //檔案下載
        if (!xDownload(docupath + "Employees.xlsx", "Employees.xlsx"))
        {
            Response.Write("檔案下載失敗");
        }
    }

    //檔案下載
    private bool xDownload(string xFile, string out_file)
    {
        {
            if (File.Exists(xFile))
            {
                try
                {
                    FileInfo xpath_file = new FileInfo(xFile);
                    System.Web.HttpContext.Current.Response.Clear();
                    System.Web.HttpContext.Current.Response.ClearHeaders();
                    System.Web.HttpContext.Current.Response.Buffer = false;
                    System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
                    System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(out_file, System.Text.Encoding.UTF8));
                    System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", xpath_file.Length.ToString());
                    System.Web.HttpContext.Current.Response.WriteFile(xpath_file.FullName);
                    System.Web.HttpContext.Current.Response.Flush();
                    System.Web.HttpContext.Current.Response.End();
                    return true;

                }
                catch (Exception)
                { return false; }

            }
            else
                return false;
        }
    }
}

匯入

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

//自己加的
using System.Data.SqlClient;
using System.Web.Configuration;
using NPOI.XSSF.UserModel;
using System.IO;

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

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        String savePath = Request.PhysicalApplicationPath + "NPOI\\";

        //建立資料夾
        if (!Directory.Exists(savePath))
        {
            Directory.CreateDirectory(savePath);
        }

        if (FileUpload1.HasFile)
        {
            SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
            Conn.Open();

            String fileName = FileUpload1.FileName;
            savePath = savePath + fileName;
            FileUpload1.SaveAs(savePath);
            Label1.Text = "上傳成功，檔名---- " + fileName;
            XSSFWorkbook workbook = new XSSFWorkbook(FileUpload1.FileContent);
            XSSFSheet u_sheet = (XSSFSheet)workbook.GetSheetAt(0);
            for (int i = (u_sheet.FirstRowNum + 1); i <= u_sheet.LastRowNum; i++)
            {
                XSSFRow row = (XSSFRow)u_sheet.GetRow(i);
                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        SqlCommand cmd = new SqlCommand("UPDATE Employees SET LastName=@LastName,FirstName=@FirstName where (EmployeeID=@EmployeeID)", Conn);
                        cmd.Parameters.AddWithValue("@EmployeeID", row.GetCell(0).ToString());
                        cmd.Parameters.AddWithValue("@LastName", row.GetCell(1).ToString());
                        cmd.Parameters.AddWithValue("@FirstName", row.GetCell(2).ToString());
                        cmd.ExecuteNonQuery();
                        cmd.Cancel();
                    }
                }
            }
            workbook = null;
            u_sheet = null;
            Conn.Close();
        }
        else
        {
            Label1.Text = "????  ...... 請先挑選檔案之後，再來上傳";
        }
    }
}

範例是微軟北風資料庫的Employees資料表，這樣大家比較容易解讀

北風資料表 下載

各位客官看看，ASP.NET 不難的碑

我是 ASP.NET 新手，程式有問題再請告知喔!!