Camstar关于Excel的上传并加载

---

Camstar关于Excel的上传并加载 Camstar Excel Upload

---

本文内容仅做参考并不完全通用，测试环境为OpcenterEXCR2410，其中数据均为演示所用无实际意义

在VP页面中添加File Input控件

创建VP页面，加入文件上传控件，加载按钮，展示表格

页面绑定CS代码逻辑

using System;
using System.Collections.Generic;
using System.Data;
using System.Web.UI.WebControls;
using Camstar.WCF.Services;
using Camstar.WCF.ObjectStack;
using Camstar.WebPortal.Utilities;
using Camstar.WebPortal.FormsFramework.WebGridControls;
using Camstar.WebPortal.FormsFramework;
using Camstar.WebPortal.FormsFramework.Utilities;
using Camstar.WebPortal.Constants;
using Camstar.WebPortal.WCFUtilities;
using Camstar.WebPortal.Personalization;
using CWC = Camstar.WebPortal.FormsFramework.WebControls;
using CGC = Camstar.WebPortal.FormsFramework.WebGridControls;
using OM = Camstar.WCF.ObjectStack;
using SEMI.AppCode;
using System.IO;
using System.Data.Common;
using Avary.Utils.DB;
using System.Configuration;
using System.Linq;

/// <summary>
/// 钻孔外发
/// </summary>

namespace Camstar.WebPortal.WebPortlets.Shopfloor
{
    public class ahDrillingOutsource : MatrixWebPart
    {
        /// <summary>
        /// 详情
        /// </summary>
        protected JQDataGrid _GridServiceDetails { get { return Page.FindCamstarControl("ahDrillingOutsource_ServiceDetails") as JQDataGrid; } }

        /// <summary>
        /// 导入数据
        /// </summary>
        protected CWC.Button _ButtonImport { get { return Page.FindCamstarControl("ButtonImport") as CWC.Button; } }

        LabelCache labelCache = LabelCache.GetRuntimeCacheInstance();

        //---------------------------------------------------
        // Override OnLoad event 加载事件
        //---------------------------------------------------
        protected override void OnLoad(EventArgs e)
        {
            try
            {
                base.OnLoad(e);

                if (!Page.IsPostBack)
                {

                }
                _ButtonImport.Click += new EventHandler(GetInputData);
            }
            catch (Exception ex)
            {
                Page.StatusBar.WriteError(ex.Message.ToString());
            }
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void GetInputData(object sender, EventArgs e)
        {
            try
            {
                string strfilePath = CWC.FileInput.UploadFilePath;    //获取上传文路径
                if (!string.IsNullOrEmpty(strfilePath))
                {
                    FileStream fs = new FileStream(strfilePath, FileMode.Open, FileAccess.Read);
                    DataTable inputDt = ahFileUtil.ReadExcel(fs, strfilePath);
                    if (inputDt != null && inputDt.Rows.Count > 0)
                    {
                        inputDt.Columns[0].ColumnName = "DATE";
                        inputDt.Columns[1].ColumnName = "MFGORDER";
                        inputDt.Columns[2].ColumnName = "PRODUCTNAME";
                        inputDt.Columns[3].ColumnName = "PRODUCTREV";
                        inputDt.Columns[4].ColumnName = "CONTAINER";
                        inputDt.Columns[5].ColumnName = "SPEC";
                        inputDt.Columns[6].ColumnName = "QTY";
                        inputDt.Columns[7].ColumnName = "ISCANCEL";

                        List<ahDrillingOutsourceDetails> Details = new List<OM.ahDrillingOutsourceDetails>();
                        for (int i = 0; i < inputDt.Rows.Count; i++)
                        {
                            #region 数据校验

                            string containerName = inputDt.Rows[i][4]?.ToString()?.Trim();
                            string mfgOrder = inputDt.Rows[i][1]?.ToString()?.Trim();
                            string productName = inputDt.Rows[i][2]?.ToString()?.Trim();
                            string productRev = inputDt.Rows[i][3]?.ToString()?.Trim();
                            string specName = inputDt.Rows[i][5]?.ToString()?.Trim();
                            if (string.IsNullOrEmpty(containerName) || string.IsNullOrEmpty(specName))
                            {
                                //第 {i + 2} 行：批次号或工站名称为空，请检查上传数据
                                Page.StatusBar.WriteWarning(string.Format(labelCache.GetLabelByName("ahDrillingOutsource_E0001").Value, i+2));
                                return;
                            }
                            string sql = @"
                            SELECT
                            C.CONTAINERNAME,
                            MO.MFGORDERNAME,
                            PB.PRODUCTNAME,
                            P.PRODUCTREVISION,
                            SB.SPECNAME
                            FROM CONTAINER C
                            INNER JOIN MFGORDER MO ON C.MFGORDERID = MO.MFGORDERID
                            INNER JOIN PRODUCT P ON P.PRODUCTID = C.PRODUCTID
                            INNER JOIN PRODUCTBASE PB ON P.PRODUCTBASEID = PB.PRODUCTBASEID
                            INNER JOIN CURRENTSTATUS CSS ON C.CURRENTSTATUSID = CSS.CURRENTSTATUSID
                            INNER JOIN SPEC S ON S.SPECID = CSS.SPECID
                            INNER JOIN SPECBASE SB ON S.SPECBASEID = SB.SPECBASEID
                            WHERE C.CONTAINERNAME = :Lot
                            ";
                            DataTable dt = PubCamstar.GetDataTable(sql, new string[] { containerName });
                            if (dt == null || dt.Rows.Count == 0)
                            {
                                //第 {i + 2} 行：批次 {containerName} 信息在系统中不存在
                                Page.StatusBar.WriteWarning(string.Format(labelCache.GetLabelByName("ahDrillingOutsource_E0002").Value, i + 2, containerName));
                                return;
                            }
                            DataRow dr = dt.Rows[0];
                            if (!string.Equals(dr["MFGORDERNAME"]?.ToString(), mfgOrder)
                            || !string.Equals(dr["PRODUCTNAME"]?.ToString(), productName)
                            || !string.Equals(dr["PRODUCTREVISION"]?.ToString(), productRev))
                            {
                                //第 {i + 2} 行：批次 {containerName} 的工单 / 料号 / 版本 与系统信息不一致，请检查上传数据
                                Page.StatusBar.WriteWarning(string.Format(labelCache.GetLabelByName("ahDrillingOutsource_E0003").Value, i + 2, containerName));
                                return;
                            }

                            #endregion

                            double qty;
                            string qtyStr = inputDt.Rows[i][6]?.ToString()?.Trim();

                            if (!double.TryParse(qtyStr, out qty))
                            {
                                //第 {i + 2} 行：数量 QTY 格式错误，当前值：{qtyStr}
                                Page.StatusBar.WriteWarning(string.Format(labelCache.GetLabelByName("ahDrillingOutsource_E0004").Value, i + 2, qtyStr));
                                return;
                            }
                            ahDrillingOutsourceDetails Detail = new ahDrillingOutsourceDetails()
                            {
                                ahDate = inputDt.Rows[i][0]?.ToString(),
                                ahMfgOrder = new NamedObjectRef(mfgOrder),
                                ahProduct = new RevisionedObjectRef(productName, productRev),
                                ahContainer = new ContainerRef(containerName),
                                ahSpec = new RevisionedObjectRef(specName, "1"),
                                ahQty = new Primitive<double>(qty),
                                ahIsCancel = inputDt.Rows[i][7]?.ToString()
                            };
                            Details.Add(Detail);
                        }
                        _GridServiceDetails.Data = Details.ToArray();
                    }
                    else
                    {
                        //识别数据文件内容失败，请检查导入模板正确性
                        Page.StatusBar.WriteWarning(labelCache.GetLabelByName("ahDrillingOutsource_E0005").Value);
                    }
                }
                else
                {
                    //请选择导入的数据文件
                    Page.StatusBar.WriteWarning(labelCache.GetLabelByName("ahDrillingOutsource_E0006").Value);
                }
            }
            catch (Exception ex)
            {
                Page.StatusBar.WriteError(ex.Message);
            }
        }

        public override bool PreExecute(Info serviceInfo, Service serviceData)
        {
            bool isSuccess = base.PreExecute(serviceInfo, serviceData);

            var details = _GridServiceDetails.Data as ahDrillingOutsourceDetails[];
            if (details == null || details.Length == 0)
            {
                //提交数据为空，请先导入Excel数据
                isSuccess = false;
                Page.StatusBar.WriteWarning(labelCache.GetLabelByName("ahDrillingOutsource_E0007").Value);
                return isSuccess;
            }

            var duplicateContainers = details
                .Where(d =>
                    !string.IsNullOrWhiteSpace(d.ahContainer?.Name) &&
                    !string.IsNullOrWhiteSpace(d.ahSpec?.Name))
                .GroupBy(d => new
                {
                    Container = d.ahContainer.Name.Trim(),
                    Spec = d.ahSpec.Name.Trim()
                })
                .Where(g => g.Count() > 1)
                .Select(g => g.Key)
                .ToList();

            if (duplicateContainers.Any())
            {
                //提交数据中存在重复的批号工序组合，请检查数据内容
                isSuccess = false;
                Page.StatusBar.WriteWarning(labelCache.GetLabelByName("ahDrillingOutsource_E0008").Value);
                return isSuccess;
            }

            return isSuccess;
        }
    }
}

文件加载工具类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data.Common;
using Camstar.WebPortal.FormsFramework.Utilities;
using Camstar.WCF.ObjectStack;

namespace Camstar.WebPortal.WebPortlets.Shopfloor
{
    public static class ahFileUtil
    {
        static LabelCache labelCache = LabelCache.GetRuntimeCacheInstance();
        static Label validateLbl;

        #region 讀取上傳的Excl檔，並轉成DataTable
        /// <summary>
        /// 讀取上傳的Excl檔，並轉成DataTable
        /// </summary>
        /// <param name="file">上傳的檔案流</param>
        /// <returns></returns>
        public static DataTable ReadExcel(this HttpPostedFileBase file)   
        {
            string FileName = file.FileName;
            string[] sp = FileName.Split('.');

            if (sp[1] == "xls")
            {
                return file.InputStream.ReadExcel2003();
            }
            else if (sp[1] == "xlsx")
            {
                return file.InputStream.ReadExcel2007();
            }
            else
            {
                //throw new Exception("附檔名不為Excel格式");
                validateLbl = labelCache.GetLabelByName("ahFileUtil_E0011");
                throw new Exception(validateLbl.Value);
            }
            ;
        }
        #endregion

        #region 讀取上傳的Excl檔，並轉成DataTable
        /// <summary>
        /// 讀取上傳的Excl檔，並轉成DataTable
        /// </summary>
        /// <param name="file">上傳的檔案流</param>
        /// <returns></returns>
        public static DataTable ReadExcel(FileStream fs, string FileName)
        {
            string[] sp = FileName.Split('.');
            if (sp[1] == "xls")
            {
                return ReadExcel2003(fs);
            }
            else if (sp[1] == "xlsx")
            {
                return ReadExcel2007(fs);
            }else if (sp[1] == "csv")
            {
                return ReadCSV(fs);
            }
            else
            {
                //throw new Exception("附檔名不為Excel格式");
                validateLbl = labelCache.GetLabelByName("ahFileUtil_E0011");
                throw new Exception(validateLbl.Value);
            }
        }
        #endregion


        public static DataTable ReadCSV(this System.IO.Stream file)
        {
            DataTable dt = new DataTable();
            using (StreamReader sr = new StreamReader(file, System.Text.Encoding.UTF8))
            {
                string str = "";
                int index = 0;
                while (str != null)
                {
                    if (index == 0)
                    {
                        str = sr.ReadLine();
                        if (str == null) break;
                        string[] eachLine = str.Split(',');
                        for (int i = 0; i < eachLine.Length; i++)
                        {
                            dt.Columns.Add(eachLine[i],typeof(string));
                        }
                    }
                    else
                    {
                        str = sr.ReadLine();
                        if (str == null) break;
                        DataRow dr = dt.NewRow();
                        string[] eachLine = str.Split(',');
                        for (int i = 0; i < eachLine.Length; i++)
                        {
                            dr[i] = eachLine[i];
                        }
                        dt.Rows.Add(dr);
                    }
                    index++;
                }
                sr.Close();
            }
            return dt;
        }

        #region 将Excel文件中的数据读出到DataTable中(xls)
        public static DataTable ReadExcel2003(this System.IO.Stream file)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            ISheet sheet = hssfworkbook.GetSheetAt(0);

            //表头
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            List<int> columns = new List<int>();
            for (int i = 0; i < header.LastCellNum; i++)
            {
                object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString(), typeof(string)));
                    //continue;
                }
                else
                    dt.Columns.Add(new DataColumn(obj.ToString(), typeof(string)));
                columns.Add(i);
            }
            //数据
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();
                bool hasValue = false;
                foreach (int j in columns)
                {
                    var aaa = sheet.GetRow(i).GetCell(j);
                    dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                    if (dr[j] != null && dr[j].ToString() != string.Empty)
                    {
                        hasValue = true;
                    }
                }
                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
        #endregion

        

        #region 将Excel文件中的数据读出到DataTable中(xlsx)
        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xlsx)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ReadExcel2007(this System.IO.Stream file)
        {
            DataTable dt = new DataTable();
            //using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            //{
            XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
            ISheet sheet = xssfworkbook.GetSheetAt(0);

            //表头
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            List<int> columns = new List<int>();
            for (int i = 0; i < header.LastCellNum; i++)
            {
                object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    //continue;
                }
                else
                    dt.Columns.Add(new DataColumn(obj.ToString()));
                columns.Add(i);
            }
            //数据
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();
                bool hasValue = false;
                foreach (int j in columns)
                {
                    dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                    if (dr[j] != null && dr[j].ToString() != string.Empty)
                    {
                        hasValue = true;
                    }
                }


                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
            }
            //}
            return dt;
        }
        #endregion

        #region 获取单元格类型(xlsx)
        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;

                case CellType.Numeric: //NUMERIC:
                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    else
                    {
                        return cell.NumericCellValue;
                    }
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion

        #region 获取单元格类型(xls)
        /// <summary>
        /// 获取单元格类型(xls)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLS(HSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    short format = cell.CellStyle.DataFormat;
                    if (format==14|| format ==31|| format ==57|| format ==58|| format ==20 || format == 22)
                    {
                        return cell.DateCellValue;
                    }
                    else if (format==0)
                    {
                        return cell.NumericCellValue;
                    }
                    else if (cell.DateCellValue != null)
                    {
                        return cell.DateCellValue;
                    }
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion

        #region 将DataTable数据导出到Excel文件中
        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xls)
        /// </summary>
        /// <param name="dt"></param>
        public static byte[] ToExcelStream(this DataTable dt)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet = hssfworkbook.CreateSheet("Sheet1");

            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            hssfworkbook.Write(stream);

            return stream.ToArray();
        }
        #endregion

        #region 将DataTable数据导出到Excel文件中(xls)
        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xls)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="Column">標題名稱(以逗號分隔)</param>
        /// <returns></returns>
        public static byte[] ToExcelStream(this DataTable dt, string Column)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet = hssfworkbook.CreateSheet("Sheet1");

            //表头
            IRow row = sheet.CreateRow(0);


            string[] Columns = Column.Split(',');
            int c = 0;
            foreach (string col in Columns)
            {
                ICell cell = row.CreateCell(c);
                cell.SetCellValue(col);
                c++;
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            hssfworkbook.Write(stream);

            return stream.ToArray();
        }
        #endregion
    }
}

---