快捷導(dǎo)航

如何使用ASP.NET的OleDb類庫操作Excel文件

更新時間：2025年12月12日 10:29:56 作者：#清詞#

文章介紹了如何使用OleDb數(shù)據(jù)源通過代碼驅(qū)動Excel工作表進(jìn)行數(shù)據(jù)采集,包括操作流程、注意事項(xiàng)和安全措施,感興趣的朋友跟隨小編一起看看吧

一、使用背景

Excel表格是一種體現(xiàn)數(shù)據(jù)直觀，又能分析篩選數(shù)據(jù)的強(qiáng)大工具。比如說納稅申報(bào)表、財(cái)務(wù)報(bào)表、工資表、成績排名表、數(shù)據(jù)采集表等等都是Excel，表格形式體現(xiàn)。

特別地，在進(jìn)行數(shù)據(jù)庫操作中，對于批量的數(shù)據(jù)的采集，單筆從UI層控件錄入不僅效率低下，而且容易出錯，Excel表格提供數(shù)據(jù)采集的方式跟高效又便捷。

但是，在代碼的世界中要怎么，通過非視覺可視化操作，代碼的形式去驅(qū)動excel工作表呢？
我們可以在Oledb數(shù)據(jù)源中找到答案，它提供了一系列接口規(guī)范，可以以Excel表格作為數(shù)據(jù)源操作，進(jìn)行增刪查改。

二、操作流程

1.引用OldDb類庫命名空間，未安裝需要右擊項(xiàng)目-管理Nuget程序包搜索OleDb安裝上。

using System.Data.OleDb;

2.設(shè)置Excel表格模版規(guī)范：命名表頭，按需要采集數(shù)據(jù)；

2.后端代碼編寫：通過.ashx一般處理程序獲取前段提交的Excel文件，建立OleDB數(shù)據(jù)連接。這里以導(dǎo)入商品數(shù)據(jù)為例，進(jìn)行數(shù)據(jù)的預(yù)覽。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using iTextSharp;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Data.OleDb;
namespace MySolution1
{
    /// <summary>
    /// 建立前端適配的映射類
    /// </summary>
    class ReqParams
    {
        public string type { get; set; }
        public int payload { get; set; }
    }
    /// <summary>
    /// Handler1 的摘要說明
    /// </summary>
    public class Handler1 : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            //創(chuàng)建序列化工具
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            //獲取前端提交的JSON字符串
            string jsonString = context.Request.Form["param"].ToString();
            //反序列化映射JSON字符串
            ReqParams req = serializer.Deserialize<ReqParams>(jsonString);
            //需要返回前端的狀態(tài)
            object state = null;
            switch (req.type)
            {
                case "init":
                    state = new
                    {
                        time = DateTime.Now.ToLongTimeString(),
                        result=req.payload+1,
                    };
                    break;
                case "uploadFile":
                    //有文件上傳負(fù)載到Request中，才下一步操作
                    if (context.Request.Files.Count > 0)
                    {
                        HttpPostedFile file = context.Request.Files["file"];
                        //制定文件保存路徑
                        string savePath = context.Server.MapPath("~/Content/images/") + file.FileName;
                        try
                        {
                            //保存文件，記錄狀態(tài)信息
                            file.SaveAs(savePath);
                            state = new
                            {
                                time = DateTime.Now.ToLongTimeString(),
                                result = "上傳成功",
                                payload = req.payload + 1,
                                url = "/Content/images/" + file.FileName,
                            };
                        }
                        catch(Exception e)
                        {
                            //保存失敗，拋出錯誤信息
                            state = new
                            {
                                time = DateTime.Now.ToLongTimeString(),
                                result = "上傳失??！" + e.Message.ToString(),
                                payload=-1
                            };
                        }
                    }
                    break;
                case "getScorePDF":
                    state = CreatePDF(context);
                    break;
                case "previewExcelData":
                    state = PreviewExcelData(context);
                    break;
            }
            context.Response.Write(serializer.Serialize(state));
        }
        object PreviewExcelData(HttpContext context)
        {
            if(context.Request.Files.Count>0)
            {
                //獲取客戶端提交的excel文件
                HttpPostedFile excel = context.Request.Files["excel"];
                //保存到本地目錄
                string path = context.Server.MapPath("~/Content/") + excel.FileName;
                excel.SaveAs(path);
                //建立excel數(shù)據(jù)源連接
                string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    try
                    {
                        string sql = "select * from [sheet1$]";
                        //提供容器盛放業(yè)務(wù)數(shù)據(jù)
                        List<GoodsInfo> list = new List<GoodsInfo>();
                        conn.Open();
                        //獲取數(shù)據(jù)讀取器
                        OleDbDataReader dr = new OleDbCommand(sql, conn).ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                        //循環(huán)讀取數(shù)據(jù)，添加到容器中
                        while (dr.Read())
                        {
                            list.Add(new GoodsInfo()
                            {
                                GoodsId=Convert.ToString(dr[0]),
                                GoodsName = Convert.ToString(dr[1]),
                                GoodsPrice = Convert.ToDecimal(dr[2]),
                                GoodsDesc = Convert.ToString(dr[3]),
                            });
                        }
                        return new
                        {
                            data=list,
                            time = DateTime.Now.ToString(),
                            success = true,
                            payload = 1,
                        };
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            else
            {
                return new
                {
                    errorMsg="未獲取到文件信息！",
                    time=DateTime.Now.ToString(),
                    success=false,
                    payload=-1,
                };
            }
        }
        object CreatePDF(HttpContext context)
        {
            Random ran = new Random(60);
            //指定pdf文件目錄
            string path = context.Server.MapPath("~/Content/") + "scorePDF.pdf";
            //創(chuàng)建pdf文檔、pdf寫入器
            Document pdf = new Document(PageSize.A4, 10, 10, 40, 30);
            PdfWriter writer = PdfWriter.GetInstance(pdf, new FileStream(path, FileMode.Create)); 
            // 指定中文字體（如微軟雅黑）
            string fontPath = @"C:\Windows\Fonts\msyh.ttc,0"; // 微軟雅黑
            BaseFont baseFont = BaseFont.CreateFont(fontPath,
            BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
            Font chineseFont = new Font(baseFont, 8);
            try
            {
                pdf.Open();
                //建立表格對象
                PdfPTable table = new PdfPTable(3 + 3 + 3);
                //添加表格的單元格數(shù)據(jù)
                table.AddCell(new PdfPCell(new Phrase("姓名", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("班級", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("準(zhǔn)考證", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("語文", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("數(shù)學(xué)", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("英語", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("體育", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("美術(shù)", chineseFont)));
                table.AddCell(new PdfPCell(new Phrase("勞動", chineseFont)));
                //也可以添加表格的行
                PdfPRow row = new PdfPRow(new PdfPCell[]
                {
                new PdfPCell(new Phrase("李明",chineseFont)),
                new PdfPCell(new Phrase("一年級二班",chineseFont)),
                new PdfPCell(new Phrase("0500090901",chineseFont)),
                new PdfPCell(new Phrase(ran.Next(60,100).ToString(),chineseFont)),
                new PdfPCell(new Phrase(ran.Next(60,100).ToString(),chineseFont)),
                new PdfPCell(new Phrase(ran.Next(60,100).ToString(),chineseFont)),
                new PdfPCell(new Phrase(ran.Next(60,100).ToString(),chineseFont)),
                new PdfPCell(new Phrase(ran.Next(60,100).ToString(),chineseFont)),
                new PdfPCell(new Phrase(ran.Next(60,100).ToString(),chineseFont)),
                });
                table.Rows.Add(row);
                //把表格放入pdf文檔
                pdf.Add(table);
                return new
                {
                    time = DateTime.Now.ToLongTimeString(),
                    result = "操作成功！",
                    url = "/Content/scorePDF.pdf",
                    success = true
                };
            }
            catch(Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            finally
            {
                pdf.Close();
            }
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
    [Serializable]
    class GoodsInfo
    {
        public string GoodsId { get; set; }
        public string GoodsName { get; set; }
        public decimal GoodsPrice { get; set; }
        public string GoodsDesc { get; set; }
    }
}

3.前端代碼編寫：提供input文件上傳控件獲取Excel文件，依據(jù)接口提交網(wǎng)絡(luò)請求給后端，并依據(jù)讀取結(jié)果渲染數(shù)據(jù)進(jìn)行預(yù)覽。

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="MySolution1.WebForm3" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>使用ASP.NET讀取Excel表格</title>
    <script src="Scripts/jquery-1.10.2.min.js"></script>
</head>
<body>
    <div>
        <input type="file" id="excel" />
        <button onclick="readExcel()">數(shù)據(jù)讀取</button>
    </div>
    <div >
        <table id="data-table" border="1" cellspacing="0" cellpadding="0">
            <thead>
                <tr>
                    <td>商品代碼</td>
                    <td>商品名稱</td>
                    <td>商品價格</td>
                    <td>商品備注</td>
                </tr>
            </thead>
            <tbody>
            </tbody>
        </table>
    </div>
</body>
</html>
<script type="text/javascript">
    function readExcel() {
        //配置參數(shù)對接后端
        const param = {
            "type": "previewExcelData",
            "payload":0
        }
        //獲取負(fù)載的excel文件
        const excel = $("#excel")[0].files
        const formData=new FormData()
        formData.append("param", JSON.stringify(param))
        formData.append("excel", excel[0])
        //發(fā)送請求到后端
        $.ajax({
            "url":"/Handler1.ashx",
            "type":"post",
            "data":formData,
            "processData":false,
            "contentType":false,
            "success":res=>{
                const data = JSON.parse(res)
                if (data.success) {
                    alert("讀取成功！")
                    $("#data-table tbody").html(null)
                    data.data.map(i=> {
                        $("#data-table tbody").append("<tr>" + "<td>" + i.GoodsId + "</td>" + "<td>" + i.GoodsName + "</td>" + "<td>" + i.GoodsPrice + "</td>" + "<td>" + i.GoodsDesc + "</td>" + "</tr>")
                    })
                }else{
                    alert("讀取失敗！"+data.errorMsg)
                }
            }
        })
    }
</script>

4.編譯運(yùn)行頁面：上傳Excel文件后，可查看結(jié)果