如何使用ASP.NET的OleDb类库操作Excel文件
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、使用背景</a></li><li><a href="#_label1">二、操作流程</a></li><li><a href="#_label2">三、注意事项</a></li></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、使用背景</h2><p>Excel表格是一种体现数据直观，又能分析筛选数据的强大工具。比如说纳税申报表、财务报表、工资表、成绩排名表、数据采集表等等都是Excel，表格形式体现。</p>
<p>特别地，在进行数据库操作中，对于批量的数据的采集，单笔从UI层控件录入不仅效率低下，而且容易出错，Excel表格提供数据采集的方式跟高效又便捷。</p>
<p>但是，在代码的世界中要怎么，通过非视觉可视化操作，代码的形式去驱动excel工作表呢？<br />我们可以在Oledb数据源中找到答案，它提供了一系列接口规范，可以以Excel表格作为数据源操作，进行增删查改。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、操作流程</h2>
<p>1.引用OldDb类库命名空间，未安装需要右击项目-管理Nuget程序包搜索OleDb安装上。</p>
<div class="jb51code"><pre class="brush:csharp;">using System.Data.OleDb;</pre></div>
<p>2.设置Excel表格模版规范：命名表头，按需要采集数据；</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121210280994.jpg" /></p>
<p>2.后端代码编写：通过.ashx一般处理程序获取前段提交的Excel文件，建立OleDB数据连接。这里以导入商品数据为例，进行数据的预览。</p>
<div class="jb51code"><pre class="brush:csharp;">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)
{
//创建序列化工具
JavaScriptSerializer serializer = new JavaScriptSerializer();
//获取前端提交的JSON字符串
string jsonString = context.Request.Form["param"].ToString();
//反序列化映射JSON字符串
ReqParams req = serializer.Deserialize<ReqParams>(jsonString);
//需要返回前端的状态
object state = null;
switch (req.type)
{
case "init":
state = new
{
time = DateTime.Now.ToLongTimeString(),
result=req.payload+1,
};
break;
case "uploadFile":
//有文件上传负载到Request中，才下一步操作
if (context.Request.Files.Count > 0)
{
HttpPostedFile file = context.Request.Files["file"];
//制定文件保存路径
string savePath = context.Server.MapPath("~/Content/images/") + file.FileName;
try
{
//保存文件，记录状态信息
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数据源连接
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 ";
//提供容器盛放业务数据
List<GoodsInfo> list = new List<GoodsInfo>();
conn.Open();
//获取数据读取器
OleDbDataReader dr = new OleDbCommand(sql, conn).ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//循环读取数据，添加到容器中
while (dr.Read())
{
list.Add(new GoodsInfo()
{
GoodsId=Convert.ToString(dr),
GoodsName = Convert.ToString(dr),
GoodsPrice = Convert.ToDecimal(dr),
GoodsDesc = Convert.ToString(dr),
});
}
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";
//创建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);
//添加表格的单元格数据
table.AddCell(new PdfPCell(new Phrase("姓名", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("班级", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("准考证", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("语文", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("数学", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("英语", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("体育", chineseFont)));
table.AddCell(new PdfPCell(new Phrase("美术", 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;
}
}
}
class GoodsInfo
{
public string GoodsId { get; set; }
public string GoodsName { get; set; }
public decimal GoodsPrice { get; set; }
public string GoodsDesc { get; set; }
}
}</pre></div>
<p>3.前端代码编写：提供input文件上传控件获取Excel文件，依据接口提交网络请求给后端，并依据读取结果渲染数据进行预览。</p>
<div class="jb51code"><pre class="brush:csharp;"><%@ 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()">数据读取</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() {
//配置参数对接后端
const param = {
"type": "previewExcelData",
"payload":0
}
//获取负载的excel文件
const excel = $("#excel").files
const formData=new FormData()
formData.append("param", JSON.stringify(param))
formData.append("excel", excel)
//发送请求到后端
$.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></pre></div>
<p>4.编译运行页面：上传Excel文件后，可查看结果</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121210280985.png" /></p>
<p class="maodian"><a name="_label2"></a></p><h2>三、注意事项</h2>
<p>1.Excel模版设置需要和后台读取接口保持一致，例如上述商品管理的Excel导入模版的数据和后台读取表格的列是一一对应的；</p>
<p>2.数据库操作需要进行一定的异常处理（例如try-catch-finally），避免读取数据连接未关闭；</p>
<p>3.确保Excel文件来源的安全性，数据导入模版是事先设定好、经过视图加密的，防范宏病毒；</p>
頁:
[1]