利用OpenXml读取、导出Excel
OpenXml是通过 XML 文档提供行集视图。由于OPENXML 是行集提供程序，因此可在会出现行集提供程序（如表、视图或 OPENROWSET 函数）的 Transact-SQL 语句中使用 OPENXML。
效果图：
使用它的时候，首选的下载安装这个程序集，下载地址：http://www.microsoft.com/en-us/download/details.aspx?id=30425
安装好了在项目当中引用如下2个
前台弹出框用的是 jBox这个js插件，我用了ajax请求的方式来上传js部分
function ImportExlDataGridRows() { var html = "<form enctype=\"multipart/form-data\" method=\"post\"> <div style='padding:10px;'>请选择导入的文件：（*.xlsx） <a href=\"download.aspx?ParamValue=1\" rel=\"external\" style=\"color:#000; background:#CCC; width:80px; border:1px solid #09F\" >下载模板</a></div>"; html += "<div style='padding:10px;'><input type=\"file\" name=\"uploadImg\" id=\"uploadImg\" style=\" width:320px; border:1px solid #09F\" /></div>"; html += "</form> "; var submit = function (v, h, f) { //判断是否有选择上传文件 var imgPath = $("#uploadImg").val(); if (imgPath == "") { alert("请选择导入的文件！"); return false; } //判断上传文件的后缀名 var strExtension = imgPath.substr(imgPath.lastIndexOf('.') + 1); if (strExtension != 'xlsx' && strExtension != 'xls') { alert("请选择导入的文件（*.xlsx）"); return false; } $.ajaxFileUpload( { url:window.location.href, secureuri: false, fileElementId: 'uploadImg', dataType: 'json', data:{ "method":"file"}, beforeSend: function () { $.jBox.tip("正在加载导入", "loading"); }, complete: function () { }, success: function (data, status) { //if (typeof (data.Success) != 'undefined') { if (data.Success != '') { $.jBox.tip(data.Msg); } // } }, error: function (data, status, e) { $.jBox.tip(e); } } ) return true; }; $.jBox(html, { title: "导入预防性维修派单", submit: submit }); }
后台方法
/// <summary>
/// 导入exl
/// </summary>
public void FilePlanImport()
{
string pathWan = "";
try
{
//Web站点下，附件存放的路径
string strFileFolerInWebServer = ConfigurationManager.AppSettings["FileFolerInWebServer"];
HttpFileCollection files = Request.Files;
if (files.Count <=0) {
ResponseWriteSuccessORFail(false, "文件导入");
return;
}
HttpPostedFile postedFile = files[0];
//context.Request.Files["Filedata"];
string savepath = "";
savepath = Server.MapPath(strFileFolerInWebServer) + "\\";//实际保存文件夹路径
string filename = postedFile.FileName;
string sNewFileName = "年度生产设备保养计划表_" + DateTime.Now.ToString("yyyyMMddhhmmss");
string sExtension = filename.Substring(filename.LastIndexOf('.'));
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
pathWan=savepath + @"\" + sNewFileName + sExtension;
postedFile.SaveAs(pathWan);
//保存到文件服务器上的名称
}
catch (Exception ex)
{
LogHelper.WriteLog(ex.Message + ex.StackTrace);
ResponseWriteSuccessORFail(false, "文件导入");
return;
// context.Response.Write("Error: " + ex.Message);
}
DataTable data = null;
int errRows = 0;//
try
{
using (var document = SpreadsheetDocument.Open(pathWan, false))
{
var worksheet = document.GetWorksheet();
var rows = worksheet.Descendants<Row>().ToList();
var sharedStringTable = document.GetSharedStringTable();
// 读取Excel中的数据
IEnumerable<string> rowskey =
new string[] { "OUGUID","AccessoriesCategories" ,"AccessoriesSubclass" ,"MaintenanceMethod",
"Cycle" ,"CycleUnit","EffectiveDate" ,"ClosingDate","EarlyDays","WorkPermit","RepairBusiness"};
ExcelOpenXMLHelper.SetRows = rowskey;//这部分是需要读取那些字段
data = ExcelOpenXMLHelper.ReadExcelData(rows, sharedStringTable);
foreach (DataRow item in data.Rows)
{
....数据插入部分
}
}
string msg = "文件导入成功：" + (data.Rows.Count - errRows) + "，错误：" + errRows;
ResponseWriteSuccessORFail(true, msg);
}
catch (Exception ex)
{
LogHelper.WriteLog(ex.Message + ex.StackTrace);
string msg = "文件导入成功：" + (data.Rows.Count - errRows) + "，错误：" + errRows;
ResponseWriteSuccessORFail(false, msg);
}
}
ExcelOpenXMLHelper这是对OpenXml的一些操作封装成了helper类。
导出部分比较简单
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="filePath">
/// The file path.
/// </param>
/// <param name="fileTemplatePath">
/// The file template path.
/// </param>
/// <exception cref="Exception">
/// </exception>
private void ExcelOut(string filePath, string fileTemplatePath)
{
try
{
System.IO.File.Copy(fileTemplatePath, filePath);
}
catch (Exception ex)
{
throw new Exception("复制Excel文件出错" + ex.Message);
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
var sheetData = document.GetFirstSheetData();
OpenXmlHelper.CellStyleIndex = 1;
////写标题相关信息
this.UpdateTitleText(sheetData);
//循环rows数据写入excl
IEnumerable<string> rowskey =
new string[] { "OUGUID","AccessoriesCategories" ,"AccessoriesSubclass" ,"MaintenanceMethod",
"Cycle" ,"CycleUnit","EffectiveDate" ,"ClosingDate","EarlyDays","WorkPermit","RepairBusiness"};
ExcelOpenXMLHelper.SetRows = rowskey;//这部分是需要读取那些字段
DataTable dt=BLL.BudgetBO();
foreach (DataRow dr in dt.Rows)
{
foreach (string item in rowskey)
{
sheetData.SetCellValue(item, dr[item]);
}
}
// var str = OpenXmlHelper.ValidateDocument(document);验证生成的Excel
}
}
/// <summary>
/// 修改标头
/// </summary>
/// <param name="sheetData">
/// The sheet data.
/// </param>
private void UpdateTitleText(SheetData sheetData)
{
sheetData.UpdateCellText("A1", "xx工信息");
sheetData.UpdateCellText("A2", "制表时间：" + DateTime.Now.ToString("yyyy年MM月dd日HH时"));
sheetData.UpdateCellText("G2", "制表人：admin");
}
以上就是利用OpenXml实现导出导入功能全部代码，Helper类需要的可以留下邮箱。
