利用OpenXml讀取、匯出Excel

標籤：des   style   blog   http   color   使用   os   io   

     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類需要的可以留下郵箱。