ASP.NET----ソース優秀類導入Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.IO;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace LoveKaoExam.Data
{
    public class   Excel
    {

        /// <summary>
        ///           
        /// </summary>
        /// <param name="fs">Excel   </param>
        /// <param name="type">0  ，1  </param>
        /// <returns></returns>
        public static List<string>   excel(Stream stream, int type)
        {
            try
            {
                HSSFWorkbook workBook = new HSSFWorkbook(stream);
                HSSFSheet sheet = (HSSFSheet)workBook.GetSheetAt(0);
                //        
                Cell cell0 = sheet.GetRow(0).GetCell(0);
                if (cell0 == null)
                {
                            ();
                }
                string cell0Value =       (cell0.CellType, sheet, 0, 0);
                if (cell0Value != "  " && cell0Value != "  ")
                {
                            ();
                }
                Cell cell1 = sheet.GetRow(0).GetCell(1);
                if (cell1 == null)
                {
                            ();
                }
                string cell1Value =       (cell1.CellType, sheet, 0, 1);
                if (cell1Value != "  ")
                {
                            ();
                }
                Cell cell2 = sheet.GetRow(0).GetCell(2);
                if (cell2 == null)
                {
                            ();
                }
                string cell2Value =       (cell2.CellType, sheet, 0, 2);
                if (cell2Value != "  ")
                {
                            ();
                }
                Cell cell3 = sheet.GetRow(0).GetCell(3);
                if (cell3 == null)
                {
                            ();
                }
                string cell3Value =       (cell1.CellType, sheet, 0, 3);
                if (cell3Value != "  "&&cell3Value!="  ")
                {
                            ();
                }
                LoveKaoExamDataContext db = new LoveKaoExamDataContext();
                //    ,  
                List<string> listClassName = new List<string>();
                List<string> listNum = new List<string>();
                for (int k = sheet.FirstRowNum + 1; k < sheet.LastRowNum + 1; k++)
                {
                    string className = string.Empty;
                    string classType = string.Empty;
                    if (type == 0)
                    {
                        classType = "  ";
                    }
                    else
                    {
                        classType = "  ";
                    }
                    Cell cellClass = sheet.GetRow(k).GetCell(3);
                    if (cellClass == null)
                    {
                        throw new Exception(" " + k + " " + classType + "   ，    ，     ！");
                    }
                    CellType classCellType = cellClass.CellType;
                    className =       (classCellType, sheet, k, 3);                                      
                    if (className.Length > 16)
                    {
                        throw new Exception(" " + k + " " + classType + "      16，   16     ！");
                    }
                    string num = string.Empty;
                    string numType = string.Empty;
                    if (type == 0)
                    {
                        numType = "  ";
                    }
                    else
                    {
                        numType = "  ";
                    }
                    Cell cellNum=sheet.GetRow(k).GetCell(0);
                    if (cellNum == null)
                    {
                        throw new Exception(" " + k + " " + numType + "   ，    ，     ！");
                    }
                    CellType numCellType = cellNum.CellType;
                    num =       (numCellType, sheet, k, 0);                                       
                    if (num.Length < 4 || num.Length > 16)
                    {
                        throw new Exception(" " + k + " " + numType + "    ，   4 16 ！");
                    }
                    else if (Regex.IsMatch(num, @"[^a-zA-Z0-9]"))
                    {
                        throw new Exception(" " + k + " " + numType + "    ，" + numType + "         ！");
                    }
                    listClassName.Add(className);
                    listNum.Add(num);
                }
                //       ，    
                listClassName = listClassName.Distinct().ToList();
                List<string> listExistClassName = db.   .Where(a => listClassName.Contains(a.  ))
                    .Select(a => a.  ).ToList();
                List<string> listNotExistClassName = listClassName.Except(listExistClassName).ToList();
                foreach (string className in listNotExistClassName)
                {
                        department = new    ();
                    department.Id = Guid.NewGuid();
                    department.   = className;
                    department.   id =     .CurrentUser.  ID;
                    department.     = DateTime.Now;
                    db.   .InsertOnSubmit(department);
                }
                db.SubmitChanges();
                //           
                List<string> listExistNum = db.   .Where(a => listNum.Contains(a.  )).Select(a => a.  ).ToList();
                List<string> listNotExistNum = listNum.Except(listExistNum).ToList();
                List<   > listClass = db.   .Where(a => listClassName.Contains(a.  )).ToList();
                for (int i = sheet.FirstRowNum+1; i < sheet.LastRowNum+1; i++)
                {
                    CellType numCellType = sheet.GetRow(i).GetCell(0).CellType;
                    string stuNum =       (numCellType, sheet, i, 0);
                   
                    if (listNotExistNum.Contains(stuNum))
                    {
                        Cell cellName=sheet.GetRow(i).GetCell(1);
                        if (cellName == null)
                        {
                            throw new Exception(" " + i + "      ，    ，     ！");
                        }
                        CellType nameCellType = cellName.CellType;
                        string name =       (nameCellType, sheet, i, 1);                      
                        if (name.Length < 2 || name.Length > 8)
                        {
                            throw new Exception(" " + i + "       ，   2 8 ！");
                        }
                        else if (Regex.IsMatch(name, @"[^a-zA-Z\u4e00-\u9fa5]"))
                        {
                            throw new Exception(" " + i + "       ，           ！");
                        }
                            user = new    ();
                        user.Id = Guid.NewGuid();
                        user.   = stuNum;
                        user.   = name;
                        user.   = stuNum;
                        Cell cellSex=sheet.GetRow(i).GetCell(2);
                        if (cellSex == null)
                        {
                            throw new Exception(" " + i + "      ，    ，     ！");
                        }
                        CellType sexCellType = cellSex.CellType;
                        string sex =       (sexCellType, sheet, i, 2);                       
                        if (sex == " ")
                        {
                            user.   = 1;
                        }
                        else
                        {
                            user.   = 2;
                        }
                        CellType classCellType = sheet.GetRow(i).GetCell(3).CellType;
                        string className =       (classCellType, sheet, i, 3);                    
                        user.  id = listClass.Where(a => a.   == className).First().Id;
                        user.   = 0;
                        user.   = "";
                        user.     = DateTime.Now;
                        db.   .InsertOnSubmit(user);
                    }
                }
                db.SubmitChanges();
                return listExistNum;
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("Table"))
                {
                    throw new Exception("    ，         ！");
                }
                else
                {
                    throw ex;
                }
            }
        }



        private static string       (CellType cellType,Sheet sheet,int rowIndex,int cellIndex)
        {
            string value = string.Empty;
            Cell cell=sheet.GetRow(rowIndex).GetCell(cellIndex);
            switch (cell.CellType)
            {
                case CellType.STRING:
                case CellType.BLANK:
                    {
                        value = cell.StringCellValue;
                        break;
                    }
                case CellType.BOOLEAN:
                    {
                        value = cell.BooleanCellValue.ToString();
                        break;
                    }
                case CellType.NUMERIC:
                    {
                        value = cell.NumericCellValue.ToString();
                        break;
                    }
            }
            return value;
        }



        private static void         ()
        {
            throw new Exception("     ，             ！");
        }

    }
}