everlight.eap/backend/DllEapCommon/NPOI/DataImportHelper.cs

using Cksoft.Data;
using DllEapEntity;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Cksoft.Unity;

namespace DllEapCommon.NPOI
{
    public class DataImportHelper
    {
        private IDatabase CurrDb = null;
        public DataImportHelper()
        {

        }
        public DataImportHelper(IDatabase db)
        {
            CurrDb = db;
        }

        public DataTable GetImportData(Stream fileStream, string fileExt, int sheetIndex, int headerRowIndex, ref string errorinfo)
        {
            IWorkbook workbook = null;
            if (fileExt.ToLower() == ".xls")
            {
                workbook = new HSSFWorkbook(fileStream);
            }
            else
            {
                workbook = new XSSFWorkbook(fileStream);
            }

            if (workbook == null)
            {
                errorinfo = "Excel内容为空";
                return null;
            }
            var sheet = workbook.GetSheetAt(sheetIndex);
            DataTable dt = new DataTable();
            var headerRow = sheet.GetRow(headerRowIndex);
            var headerCellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < headerCellCount; i++)
            {
                var cell = headerRow.GetCell(i);
                if (cell == null || string.IsNullOrEmpty(cell.ToString()))
                {
                    headerCellCount++;
                    break;
                }
                DataColumn column = new DataColumn();
                column.ColumnName = cell.ToString();
                dt.Columns.Add(column);
            }
            var rowCount = sheet.LastRowNum;
            for (int j = sheet.FirstRowNum + 1; j < rowCount + 1; j++)
            {
                DataRow row = dt.NewRow();
                var bodyRow = sheet.GetRow(j);
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    var cell = bodyRow.GetCell(k);
                    var field = dt.Columns[k].ColumnName;
                    if (cell == null)
                    {
                        row[field] = string.Empty;
                    }
                    else
                    {
                        if (cell.CellType == CellType.Formula)
                        {
                            cell.SetCellType(CellType.String);
                            row[field] = cell.StringCellValue;
                        }
                        else
                        {
                            row[field] = cell.ToString();
                        }
                    }
                }
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>
        /// 导入wafersource信息
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int ImportWaferSource(Stream stream, string fileExt, ref string errorinfo)
        {
            var fields = new string[] { "Pkg Name", "Palcode", "Test Target", "Bom Component3",
                "Wfr source","Saw blade","Cut mode","Devc Desc","Die size","Pkg Code Desc","Recipe name",
                "M/C type" };
            var dataTable = this.GetImportData(stream, fileExt, 0, 0, ref errorinfo);
            if (dataTable == null)
                return -1;
            var columns = dataTable.Columns;
            foreach (var item in fields)
            {
                if (!columns.Contains(item))
                {
                    errorinfo = $"Excel文件缺少[{item}]字段信息";
                    return -1;
                }
            }
            var sql = "TRUNCATE TABLE onsemiprogram";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                errorinfo = "清空原数据失败";
                return -1;
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO onsemiprogram (PkgName,PalCode,TestTarget,BomComponent3,WfrSource," +
                "SawBlade,CutMode,DevcDesc,DieSize,PkgCodeDesc,MCType,RecipeName) VALUES ");
            foreach (DataRow row in dataTable.Rows)
            {
                sb.Append($"('{row["Pkg Name"].ToString()}','{row["Palcode"].ToString()}'," +
                    $"'{row["Test Target"].ToString()}','{row["Bom Component3"].ToString()}','{row["Wfr source"].ToString()}'," +
                    $"'{row["Saw blade"].ToString()}','{row["Cut mode"].ToString()}',''," +
                    $"'{row["Die size"].ToString()}','','{row["M/C type"].ToString()}'," +
                    $"'{row["Recipe name"].ToString()}'),");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(";");
            if (CurrDb.ExecuteBySql(sb.ToString()) < 0)
            {
                errorinfo = "批量插入数据库失败";
                return -1;
            }

            //var list = new List<OnsemiProgram>();
            //foreach (DataRow row in dataTable.Rows)
            //{
            //    var temp = new OnsemiProgram
            //    {
            //        PkgName = row["Pkg Name"].ToString(),
            //        PalCode = row["Palcode"].ToString(),
            //        TestTarget = row["Test Target"].ToString(),
            //        BomComponent3 = row["Bom Component3"].ToString(),
            //        WfrSource = row["Wfr source"].ToString(),
            //        SawBlade = row["Saw blade"].ToString(),
            //        CutMode = row["Cut mode"].ToString(),
            //        DevcDesc = row["Devc Desc"].ToString(),
            //        DieSize = row["Die size"].ToString(),
            //        PkgCodeDesc = row["Pkg Code Desc"].ToString(),
            //        MCType = row["M/C type"].ToString(),
            //        RecipeName = row["Recipe name"].ToString()
            //    };
            //    list.Add(temp);
            //}
            //if (CurrDb.Insert<OnsemiProgram>(list) < 0)
            //{
            //    errorinfo = "批量插入数据库失败";
            //    return -1;
            //}
            return 1;
        }
    }
}