示例#1
0
        public void TestClassCast_bug44861()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls");

            // Check direct
            HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);

            // And via calls
            int numSheets = wb.NumberOfSheets;

            for (int i = 0; i < numSheets; i++)
            {
                NPOI.SS.UserModel.ISheet s    = wb.GetSheetAt(i);
                HSSFFormulaEvaluator     eval = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext();)
                {
                    IRow r = (IRow)rows.Current;

                    for (IEnumerator cells = r.GetEnumerator(); cells.MoveNext();)
                    {
                        ICell c = (ICell)cells.Current;
                        eval.EvaluateFormulaCell(c);
                    }
                }
            }

            wb.Close();
        }
示例#2
0
        private static DataTable ReadFromFile(string filePath)
        {
            IWorkbook hssfworkbook;

            #region 初始化信息

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = WorkbookFactory.Create(file) as IWorkbook;
            }

            #endregion

            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            IRow row = rows.Current as IRow;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //将第一列作为列表头
                DataColumn dtc = new DataColumn(row.GetCell(j).ToString(), typeof(string));
                dt.Columns.Add(dtc);
            }

            while (rows.MoveNext())
            {
                try
                {
                    row = (IRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                        Console.WriteLine(cell == null ? "" : cell + "\t\t");
                        Console.WriteLine();

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                catch (Exception ex)
                {
                    continue;
                    //  throw;
                }
            }
            return(dt);
        }
示例#3
0
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            // HSSFWorkbook hssfworkbook;

            IWorkbook hssfworkbook;

            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                string fileExt = Path.GetExtension(strFileName);
                if (fileExt == ".xls")
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                else if (fileExt == ".xlsx")
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }
                else
                {
                    return(new DataTable());
                }
            }

            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();

            NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                DataRow dataRow            = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }

                dt.Rows.Add(dataRow);
            }
            return(dt);
        }
示例#4
0
        /// <summary>
        /// xlsx
        /// </summary>
        /// <param name="Postfile"></param>
        /// <param name="dt"></param>
        /// <param name="iStartRow"></param>
        /// <param name="iStartColumn"></param>
        /// <returns></returns>
        private static string ImportExcelFileXSSF(HttpPostedFileBase Postfile, DataTable dt, int iStartRow, int iStartColumn)
        {
            XSSFWorkbook Xssfworkbook;

            #region//初始化信息
            try
            {
                //.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file);
                //而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);
                Stream file = Postfile.InputStream;
                Xssfworkbook = new XSSFWorkbook(file);
                //HSSFWorkbook workbook = new HSSFWorkbook(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            NPOI.SS.UserModel.ISheet       sheet = Xssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            for (int i = 0; i < iStartRow; i++)
            {
                rows.MoveNext();
            }
            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                DataRow dr  = dt.NewRow();
                int     j   = 0;
                for (int i = iStartColumn; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[j] = null;
                    }
                    else
                    {
                        if (dt.Columns[j].DataType.FullName == "System.Int32")
                        {
                            dr[j] = Convert.ToInt32(cell.ToString());
                        }
                        else
                        {
                            dr[j] = cell;
                        }
                    }
                    j++;
                }
                dt.Rows.Add(dr);
            }
            return(JsonHelper.DataTable2Json(dt));
        }
示例#5
0
        public static DataTable GetData(string filePath)
        {
            IWorkbook workbook;

            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    //hssfworkbook = new HSSFWorkbook(file);
                    workbook = WorkbookFactory.Create(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            NPOI.SS.UserModel.ISheet       sheet = workbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            //HSSFRow row = (HSSFRow)rows.Current;
            IRow row = (IRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                //将第一列作为列表头
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            while (rows.MoveNext())
            {
                row = (IRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }

            return(dt);
        }
示例#6
0
        public static DataTable ImportExcelFileXSSF_Org(HttpFileCollectionBase files)
        {
            XSSFWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                //.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file);
                //而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);
                Stream file = files[0].InputStream;
                hssfworkbook = new XSSFWorkbook(file);
                //HSSFWorkbook workbook = new HSSFWorkbook(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            XSSFRow row = (XSSFRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //将第一列作为列表头
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            while (rows.MoveNext())
            {
                row = (XSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return(dt);
        }
示例#7
0
        private static void Process(HSSFWorkbook wb)
        {
            HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);

            for (int i = 0; i < wb.NumberOfSheets; i++)
            {
                NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i);

                IEnumerator it = s.GetRowEnumerator();
                while (it.MoveNext())
                {
                    IRow r = (IRow)it.Current;
                    Process(r, eval);
                }
            }
        }
示例#8
0
        /// <summary>
        /// 将excel的数据加载到datatable中
        /// </summary>
        /// <param name="path"></param>
        /// <author>wangwei</author>
        /// <returns></returns>
        public static DataTable ExcelToTable(string path)
        {
            HSSFWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dtss = new DataTable();
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                dtss.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
            }
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr  = dtss.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dtss.Rows.Add(dr.ItemArray);
            }
            return(dtss);
        }
示例#9
0
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportDataTableFromExcel(string excelFilePath)
        {
            HSSFWorkbook hssfworkbook;

            //excelFilePath = excelFilePath.Replace("http://","").Replace("/","\\");
            using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);

                NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();


                DataTable dt = new DataTable();
                for (int j = 0; j < 4; j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }

                while (rows.MoveNext())
                {
                    NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);


                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return(dt);
            }
        }
示例#10
0
        private static DataTable GetDataTableByXls(string excelFilePath, DataTable dt)
        {
            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);

                NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();

                int r = 0;
                while (rows.MoveNext())
                {
                    r++;
                    if (r == 1)
                    {
                        continue;
                    }

                    NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current;
                    DataRow rw = dt.NewRow();
                    for (int i = 1; i <= row.LastCellNum - 1; i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);

                        if (cell == null)
                        {
                            continue;
                        }
                        else
                        {
                            rw[i - 1] = cell.ToString();
                        }
                    }

                    dt.Rows.Add(rw);
                }
                return(dt);
            }
        }
示例#11
0
        /// <summary>
        /// 导入老师表格
        /// </summary>
        /// <param name="path"></param>
        /// <param name="exam"></param>
        /// <returns></returns>
        public static string ImportTchExcel(string path, E_Info exam, string SchoolID, out int errnum)
        {
            HSSFWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(fs);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            HSSFRow       row  = (HSSFRow)rows.Current;
            List <string> cols = new List <string>();
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                cols.Add(row.GetCell(j).ToString().Trim());
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            var colHead = GetTchColumn();
            if (!(colHead.All(cols.Contains) && cols.All(colHead.Contains)))
            {
                errnum = 0;
                return("表格格式错误,请重新下载模板");
            }
            cols.Add("错误原因");
            dt.Columns.Add("错误原因");
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                if (!IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2))
                {
                    continue;
                }
                var school = MongoDbHelper.QueryOne <U_Info>(DbName.U_Info, w => w._id == row.GetCell(0).ToString());
                var ifTrue = false;
                if (SchoolID != "SchoolID" && school != null)
                {
                    ifTrue = !(SchoolID == school._id);
                }
                var sbsnm = exam.sbs.Select(s => s.sbnm).ToList();
                if (!sbsnm.Contains(row.GetCell(2).ToString()) || school == null || ifTrue || !IsNull(row, 4) || !IsNull(row, 5) || !IsNull(row, 6) || !Function.MathPhone(row.GetCell(6).ToString()))
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    if (school == null)
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "学校不存在;";
                    }
                    if (ifTrue)
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "学校代号错误;";
                    }
                    if (!sbsnm.Contains(row.GetCell(2).ToString()))
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "学科不存在;";
                    }
                    if (!Function.MathPhone(row.GetCell(6).ToString()))
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "手机号不正确;";
                    }
                    dt.Rows.Add(dr);
                    continue;
                }

                Tch_Info tch = new Tch_Info();
                tch.eid = exam._id;
                tch.sid = row.GetCell(0).ToString();
                tch.snm = school.snm;
                tch.sb  = row.GetCell(2).ToString().Replace(" ", "");
                tch.nm  = row.GetCell(3).ToString();
                tch.sx  = row.GetCell(4).ToString() == "男" ? 1 : 0;
                tch.zc  = row.GetCell(5).ToString();
                tch.ph  = Convert.ToInt64(row.GetCell(6).ToString());
                var sbinfo = exam.sbs.Where(w => w.sbnm == tch.sb).FirstOrDefault();
                if (sbinfo != null)
                {
                    sbinfo.tchct++;
                }
                MongoDbHelper.Insert(tch, DbName.Tch_Info);
            }
            MongoDbHelper.ReplaceOne(exam._id.ToString(), exam, DbName.E_Info);
            errnum = dt.Rows.Count;
            if (dt.Rows.Count > 0)
            {
                return(BuildExcel1(cols.ToArray(), dt));
            }
            return("");
        }
示例#12
0
        /// <summary>
        /// 导入excel文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static string ImportExcel(string filePath)
        {
            string result = "";

            NPOI.SS.UserModel.ISheet sheet = null;
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                    sheet = hssfworkbook.GetSheetAt(0);
                }
            }
            catch (Exception)
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook2 = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
                    sheet = hssfworkbook2.GetSheetAt(0);
                }
            }
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();

            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
            }
            while (rows.MoveNext())
            {
                NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                if (row.LastCellNum != -1)
                {
                    if ((dr[0] == null | dr[0].ToString() == "") && (dr[1] == null | dr[1].ToString() == "") && (dr[2] == null | dr[2].ToString() == ""))
                    {
                    }
                    else
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            dt = Distinct(dt, new string[] { "A", "B", "C" });

            DataView myDataView = new DataView(dt);

            string[] strComuns = { "C" };
            if (myDataView.ToTable(true, strComuns).Rows.Count < dt.Rows.Count)
            {
                result = "用户表存在相同的账号数据";
                return(result);
            }
            DbHelper dp = new DbHelper();

            foreach (DataRow dr in dt.Rows)
            {
                string strsql = "insert into hs_user1 values ('" + dr["B"] + "','" + dr["C"] + "')";
                dp.RunTxt(strsql);
            }
            result = "导入成功";
            return(result);
        }
示例#13
0
        public static string ImportScoreExcel(string path, E_Info exam, out int errnum)
        {
            HSSFWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(fs);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            HSSFRow       row  = (HSSFRow)rows.Current;
            List <string> cols = new List <string>();
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                cols.Add(row.GetCell(j).ToString());
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            cols.Add("错误原因");
            dt.Columns.Add("错误原因");
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                if (!IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2))
                {
                    continue;
                }
                St_Sc stsc = new St_Sc();
                stsc.eid  = exam._id;
                stsc.stid = row.GetCell(0).ToString();
                var st = MongoDbHelper.QueryOne <St_Info>(DbName.St_Info, w => w.eid == stsc.eid && w.stid == stsc.stid);
                if (st == null || !IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2) || !IsNull(row, 3))
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    if (st == null)
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "考号不存在;";
                    }
                    else
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "考生信息不能为空;";
                    }
                    dt.Rows.Add(dr);
                    continue;
                }
                stsc.cls  = st.cls;
                stsc.nm   = st.nm;
                stsc.idcd = st.idcd;
                stsc.sid  = st.sid;
                stsc.s1   = IsNull(row, 3) ? Convert.ToDouble(row.GetCell(3).ToString()) : -1; //语文
                stsc.s2   = IsNull(row, 4) ? Convert.ToDouble(row.GetCell(4).ToString()) : -1; //文科数学
                stsc.s3   = IsNull(row, 5) ? Convert.ToDouble(row.GetCell(5).ToString()) : -1; //理科数学
                stsc.s4   = IsNull(row, 6) ? Convert.ToDouble(row.GetCell(6).ToString()) : -1;
                stsc.s5   = IsNull(row, 7) ? Convert.ToDouble(row.GetCell(7).ToString()) : -1;
                stsc.s6   = IsNull(row, 8) ? Convert.ToDouble(row.GetCell(8).ToString()) : -1;
                stsc.s7   = IsNull(row, 9) ? Convert.ToDouble(row.GetCell(9).ToString()) : -1;
                stsc.s8   = IsNull(row, 10) ? Convert.ToDouble(row.GetCell(10).ToString()) : -1;
                stsc.s9   = IsNull(row, 11) ? Convert.ToDouble(row.GetCell(11).ToString()) : -1;
                stsc.s10  = IsNull(row, 12) ? Convert.ToDouble(row.GetCell(12).ToString()) : -1;
                stsc.sc   = IsNull(row, 13) ? Convert.ToDouble(row.GetCell(13).ToString()) : 0;
                MongoDbHelper.Insert(stsc, DbName.St_Sc);
            }
            var stus   = MongoDbHelper.QueryBy <St_Info>(DbName.St_Info, w => w.eid == exam._id);
            var stscs  = MongoDbHelper.QueryBy <St_Sc>(DbName.St_Sc, w => w.eid == exam._id).Select(s => s.stid).ToList();
            var errStu = stus.Where(w => !stscs.Contains(w.stid)).ToList();
            for (int st = 0; st < errStu.Count; st++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = errStu[st].stid;
                dr[1] = errStu[st].cls;
                dr[2] = errStu[st].nm;
                for (int i = 3; i < (sheet.GetRow(0).LastCellNum); i++)
                {
                    dr[i] = "0";
                }
                dr[sheet.GetRow(0).LastCellNum] += "缺少该考生成绩;";
                dt.Rows.Add(dr);
            }
            errnum = dt.Rows.Count;
            if (dt.Rows.Count > 0)
            {
                return(BuildExcel1(cols.ToArray(), dt));
            }
            return("");
        }
示例#14
0
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="headIndex">表头开始的行索引</param>
        /// <returns></returns>
        public static DataSet Import2(string strFileName, int[] headIndexArr, int sheetCount)
        {
            string extName = Path.GetExtension(strFileName);

            if (extName != ".xlsx")
            {
                DataSet ds = new DataSet();

                for (int ii = 0; ii < sheetCount; ii++)
                {
                    DataTable    dt        = new DataTable();
                    int          headIndex = headIndexArr[ii];
                    HSSFWorkbook hssfworkbook;
                    using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                    NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(ii);
                    System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
                    //sheet.FirstRowNum:获取第一行(表头通常是第一行)
                    NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(headIndex);
                    int cellCount = headerRow.LastCellNum;
                    //表头
                    for (int j = 0; j < cellCount; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
                        if (cell == null || cell.ToString() == string.Empty)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
                            //continue;
                        }
                        else
                        {
                            if (dt.Columns.Contains(cell.ToString()))//说明重复了
                            {
                                dt.Columns.Add(new DataColumn(Guid.NewGuid() + cell.ToString()));
                            }
                            else
                            {
                                dt.Columns.Add(new DataColumn(cell.ToString()));
                            }
                        }
                    }
                    //数据
                    for (int i = (headIndex + 1); i <= sheet.LastRowNum; i++)
                    {
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                        DataRow dataRow            = dt.NewRow();

                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }

                        dt.Rows.Add(dataRow);
                    }
                    ds.Tables.Add(dt);
                }

                return(ds);
            }
            else
            {
                return(ExcelToTableForXLSX2(strFileName, headIndexArr, sheetCount));
            }
        }
示例#15
0
        public static string ImportStuExcel(string path, E_Info exam, string SchoolID, out int errnum)
        {
            HSSFWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(fs);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            HSSFRow       row  = (HSSFRow)rows.Current;
            List <string> cols = new List <string>();
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                cols.Add(row.GetCell(j).ToString().Trim());
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            var colHead = GetStuColumn(exam);
            if (!(colHead.All(cols.Contains) && cols.All(colHead.Contains)))
            {
                errnum = 0;
                return("表格格式错误,请重新下载模板");
            }
            cols.Add("错误原因");
            dt.Columns.Add("错误原因");
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                St_Info st = new St_Info();
                if (!IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2))
                {
                    continue;
                }
                var stInfo = MongoDbHelper.QueryOne <St_Info>(DbName.St_Info, w => w.eid == exam._id && (w.idcd == row.GetCell(row.LastCellNum - 2).ToString() || w.stid == row.GetCell(2).ToString()));
                var school = MongoDbHelper.QueryOne <U_Info>(DbName.U_Info, w => w._id == row.GetCell(0).ToString());
                var ifTrue = false;
                if (SchoolID != "SchoolID" && school != null)
                {
                    ifTrue = !(SchoolID == school._id);
                }
                if (school == null || ifTrue || stInfo != null || !IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2) || !IsNull(row, 3) || !IsNull(row, 4) || !IsNull(row, row.LastCellNum - 2) || !IsNull(row, row.LastCellNum - 1) || !CommonHelper.Function.MathIdCard(row.GetCell(row.LastCellNum - 2).ToString()))
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    if (school == null)
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "学校不存在;";
                    }
                    if (ifTrue)
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "学校代号错误;";
                    }
                    if (stInfo != null)
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "考号或身份证号已存在;";
                    }
                    if (!CommonHelper.Function.MathIdCard(row.GetCell(row.LastCellNum - 2).ToString()))
                    {
                        dr[sheet.GetRow(0).LastCellNum] += "身份证号不正确;";
                    }
                    dt.Rows.Add(dr);
                    continue;
                }
                st.eid  = exam._id;
                st.stid = row.GetCell(2).ToString();
                st.sid  = row.GetCell(0).ToString();
                st.snm  = row.GetCell(1).ToString();
                st.cls  = row.GetCell(3).ToString();
                st.nm   = row.GetCell(4).ToString();
                st.idcd = row.GetCell(row.LastCellNum - 2).ToString();
                st.tp   = row.GetCell(row.LastCellNum - 1).ToString();
                var schPp   = MongoDbHelper.QueryOne <Pp_Nm>(DbName.Pp_Nm, w => w.sid == st.sid && w.eid == exam._id); //查询该学校这次考试试卷数量
                int firstpp = 0;                                                                                       //0代表不是新建
                if (schPp == null)
                {
                    firstpp   = 1;
                    schPp     = new Pp_Nm();
                    schPp.sid = st.sid;
                    schPp.snm = st.snm;
                    schPp.eid = exam._id;
                    schPp.ct  = 0;
                }
                schPp.ct++;//学校考试人数加1
                for (int i = 0; i < exam.sbs.Count; i++)
                {
                    if (IsNull(row, 5 + i * 3) && IsNull(row, 6 + i * 3) && IsNull(row, 7 + i * 3))
                    {
                        SubE sube = new SubE();
                        sube.sbid  = exam.sbs[i]._id;
                        sube.sbnm  = exam.sbs[i].sbnm;
                        sube.sbrm  = row.GetCell(5 + i * 3).ToString();
                        sube.sbst  = row.GetCell(6 + i * 3).ToString();
                        sube.sbtch = row.GetCell(7 + i * 3).ToString();
                        st.subEs.Add(sube);
                        exam.sbs[i].stct++;//考试人数加1
                        var Sbnm = schPp.sbnms.Where(w => w.sbid == sube.sbid).FirstOrDefault();
                        if (Sbnm == null)
                        {
                            Sbnm      = new Sbnm();
                            Sbnm.sbid = sube.sbid;
                            Sbnm.sbnm = sube.sbnm;
                            Sbnm.sct  = 1;
                            Sbnm.ac   = 0;
                            schPp.sbnms.Add(Sbnm);
                        }
                        else
                        {
                            Sbnm.sct++;
                        }
                    }
                }
                if (firstpp == 0)
                {
                    MongoDbHelper.ReplaceOne(schPp._id.ToString(), schPp, DbName.Pp_Nm);
                }
                else
                {
                    MongoDbHelper.Insert(schPp, DbName.Pp_Nm);
                }
                MongoDbHelper.Insert(st, DbName.St_Info);
            }
            MongoDbHelper.ReplaceOne(exam._id.ToString(), exam, DbName.E_Info);
            errnum = dt.Rows.Count;
            if (dt.Rows.Count > 0)
            {
                return(BuildExcel1(cols.ToArray(), dt));
            }
            return("");
        }
        ///<summary>
        /// #region  少量excel数据导入数据库
        /// </summary>
        public static async Task <DataTable> ImExport(DataTable dt, IWorkbook hssfworkbook)
        {
            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
            }
            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                DataRow dr  = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            dt.Rows.RemoveAt(0);

            #region 往数据库表添加数据
            using (WuLinEntities1 db = new WuLinEntities1())
            {
                if (dt != null && dt.Rows.Count != 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string 时间    = dt.Rows[i]["时间"].ToString();
                        string 国产进口  = dt.Rows[i]["国产/进口"].ToString();
                        string 省     = dt.Rows[i]["省"].ToString();
                        string 市     = dt.Rows[i]["市"].ToString();
                        string 县     = dt.Rows[i]["县"].ToString();
                        string 制造商   = dt.Rows[i]["制造商"].ToString();
                        string 车辆型号  = dt.Rows[i]["车辆型号"].ToString();
                        string 品牌    = dt.Rows[i]["品牌"].ToString();
                        string 车型    = dt.Rows[i]["车型"].ToString();
                        string 排量    = dt.Rows[i]["排量"].ToString();
                        string 变速器   = dt.Rows[i]["变速器"].ToString();
                        string 车辆类型  = dt.Rows[i]["车辆类型"].ToString();
                        string 车身型式  = dt.Rows[i]["车身型式"].ToString();
                        string 燃油类型  = dt.Rows[i]["燃油类型"].ToString();
                        string 使用性质  = dt.Rows[i]["使用性质"].ToString();
                        string 所有权   = dt.Rows[i]["所有权"].ToString();
                        string 抵押标记  = dt.Rows[i]["抵押标记"].ToString();
                        string 性别    = dt.Rows[i]["性别"].ToString();
                        string 年龄    = dt.Rows[i]["年龄"].ToString();
                        string 车身颜色  = dt.Rows[i]["车身颜色"].ToString();
                        string 发动机型号 = dt.Rows[i]["发动机型号"].ToString();
                        string 功率    = dt.Rows[i]["功率"].ToString();
                        string 排放标准  = dt.Rows[i]["排放标准"].ToString();
                        string 轴距    = dt.Rows[i]["轴距"].ToString();
                        string 轮胎规格  = dt.Rows[i]["轮胎规格"].ToString();
                        string 车外廓长  = dt.Rows[i]["车外廓长"].ToString();
                        string 车外廓宽  = dt.Rows[i]["车外廓宽"].ToString();
                        string 车外廓高  = dt.Rows[i]["车外廓高"].ToString();
                        string 准确排量  = dt.Rows[i]["准确排量"].ToString();
                        string 核定载客  = dt.Rows[i]["核定载客"].ToString();
                        string 总质量   = dt.Rows[i]["总质量"].ToString();
                        string 整备质量  = dt.Rows[i]["整备质量"].ToString();
                        string 轴数    = dt.Rows[i]["轴数"].ToString();
                        string 前轮距   = dt.Rows[i]["前轮距"].ToString();
                        string 后轮距   = dt.Rows[i]["后轮距"].ToString();
                        string 保有量   = dt.Rows[i]["保有量"].ToString();;
                        //int.TryParse(dt.Rows[i]["保有量"].ToString(), out 保有量);

                        PassengerVehicle pv = new PassengerVehicle();
                        pv.Id    = Guid.NewGuid();
                        pv.使用性质  = 使用性质;
                        pv.保有量   = Convert.ToInt32(保有量);
                        pv.准确排量  = (准确排量);
                        pv.制造商   = 制造商;
                        pv.前轮距   = (前轮距);
                        pv.功率    = (功率);
                        pv.县     = 县;
                        pv.发动机型号 = 发动机型号;
                        pv.变速器   = 变速器;
                        pv.后轮距   = (后轮距);
                        pv.品牌    = 品牌;
                        pv.国产_进口 = 国产进口;
                        pv.市     = 市;
                        pv.年龄    = (年龄);
                        pv.性别    = 性别;
                        pv.总质量   = (总质量);
                        pv.所有权   = 所有权;
                        pv.抵押标记  = 抵押标记;
                        pv.排放标准  = 排放标准;
                        pv.排量    = (排量);
                        pv.整备质量  = (整备质量);
                        pv.时间    = 时间;
                        pv.核定载客  = (核定载客);
                        pv.燃油类型  = 燃油类型;
                        pv.省     = 省;
                        pv.车型    = 车型;
                        pv.车外廓宽  = (车外廓宽);
                        pv.车外廓长  = (车外廓长);
                        pv.车外廓高  = (车外廓高);
                        pv.车身型式  = 车身型式;
                        pv.车身颜色  = 车身颜色;
                        pv.车辆型号  = 车辆型号;
                        pv.车辆类型  = 车辆类型;
                        pv.轮胎规格  = 轮胎规格;
                        pv.轴数    = (轴数);
                        pv.轴距    = (轴距);

                        db.PassengerVehicles.Add(pv);
                        try
                        {
                            await db.SaveChangesAsync();
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("第" + i + "条:" + e.Message);
                        }
                    }
                }
                #endregion
            }

            return(dt);
        }
        //  static HSSFWorkbook hssfworkbook;

        //     static XSSFWorkbook xssfworkbook;


        public static DataTable GetData(string filePath)
        {
            IWorkbook wk    = null;
            bool      isHss = false;

            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (filePath.ToLower().EndsWith(".xls"))
                    {
                        wk    = new HSSFWorkbook(file);
                        isHss = true;
                    }
                    else
                    {
                        wk = new XSSFWorkbook(file);
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            DataTable dt = new DataTable();

            NPOI.SS.UserModel.ISheet sheet = wk.GetSheetAt(0);
            try
            {
                //获取标题
                var    row1      = sheet.GetRow(0);  //获取第一行即标头
                int    cellCount = row1.LastCellNum; //第一行的列数
                string excelColName;
                for (int j = 0; j < cellCount; j++)
                {
                    excelColName = row1.GetCell(j).StringCellValue.ToUpper().Trim();
                    // if (!string.IsNullOrEmpty(excelColName))
                    //  {

                    DataColumn column = new DataColumn(excelColName);

                    dt.Columns.Add(column);
                    // }

                    // dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                rows.MoveNext();
                while (rows.MoveNext())
                {
                    IRow row = null;
                    if (isHss)
                    {
                        row = (HSSFRow)rows.Current;
                    }
                    else
                    {
                        row = (XSSFRow)rows.Current;
                    }
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < cellCount; i++)
                    {
                        ICell cell = row.GetCell(i);

                        if (cell == null || cell.ToString().ToUpper() == "NULL")
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                            {
                                dr[i] = cell.DateCellValue;
                            }
                            else if (cell.CellType == CellType.Formula)
                            {
                                dr[i] = cell.NumericCellValue.ToString();
                            }
                            else
                            {
                                dr[i] = cell.ToString().Trim();
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                wk    = null;
                sheet = null;
            }
            return(dt);
        }
示例#18
0
        public static DataTable ImportExcelFile(string filePath, string sheetName)
        {
            IWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            #endregion

            NPOI.SS.UserModel.ISheet sheet = !string.IsNullOrEmpty(sheetName) ? hssfworkbook.GetSheet(sheetName) : hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            IRow row = null;
            try
            {
                row = (HSSFRow)rows.Current;
            }
            catch
            {
                row = (XSSFRow)rows.Current;
            }
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                //将第一列作为列表头
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            while (rows.MoveNext())
            {
                try
                {
                    row = (HSSFRow)rows.Current;
                }
                catch
                {
                    row = (XSSFRow)rows.Current;
                }
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        switch (cell.CellType)
                        {
                        case CellType.NUMERIC:
                            if (HSSFDateUtil.IsCellDateFormatted(cell))
                            {
                                dr[i] = cell.DateCellValue.ToString();
                            }
                            else
                            {
                                dr[i] = cell.ToString();
                            }
                            break;

                        default:
                            dr[i] = cell.ToString();
                            break;
                        }
                    }
                }
                dt.Rows.Add(dr);
            }
            return(dt);
        }
示例#19
0
        public void TestEvaluateAll()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet();
            NPOI.SS.UserModel.ISheet s2 = wb.CreateSheet();
            wb.SetSheetName(0, "S1");
            wb.SetSheetName(1, "S2");

            IRow s1r1 = s1.CreateRow(0);
            IRow s1r2 = s1.CreateRow(1);
            IRow s2r1 = s2.CreateRow(0);

            ICell s1r1c1 = s1r1.CreateCell(0);
            ICell s1r1c2 = s1r1.CreateCell(1);
            ICell s1r1c3 = s1r1.CreateCell(2);

            s1r1c1.SetCellValue(22.3);
            s1r1c2.SetCellValue(33.4);
            s1r1c3.CellFormula = ("SUM(A1:B1)");

            ICell s1r2c1 = s1r2.CreateCell(0);
            ICell s1r2c2 = s1r2.CreateCell(1);
            ICell s1r2c3 = s1r2.CreateCell(2);

            s1r2c1.SetCellValue(-1.2);
            s1r2c2.SetCellValue(-3.4);
            s1r2c3.CellFormula = ("SUM(A2:B2)");

            ICell s2r1c1 = s2r1.CreateCell(0);

            s2r1c1.CellFormula = ("S1!A1");

            // Not Evaluated yet
            Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);

            // Do a full Evaluate, as per our docs
            // uses EvaluateFormulaCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet sheet     = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator     evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateFormulaCell(c);

                            // For Testing - all should be numeric
                            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
                        }
                    }
                }
            }

            // Check now as expected
            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);

            Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
            Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);


            // Now do the alternate call, which zaps the formulas
            // uses EvaluateInCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet sheet     = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator     evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateInCell(c);
                        }
                    }
                }
            }

            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);

            Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
        }
示例#20
0
        public static Dictionary <string, DataTable> ExcelToDataTable(string filePath)
        {
            Dictionary <string, DataTable> result = new Dictionary <string, DataTable>();
            HSSFWorkbook hssfworkbook;
            int          ColumnDataNum = 0;

            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
            {
                NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(i);
                System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
                DataTable dt = new DataTable();
                rows.MoveNext();
                HSSFRow row = (HSSFRow)rows.Current;
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                    //将第一列作为列表头
                    dt.Columns.Add(row.GetCell(j).ToString());
                }
                while (rows.MoveNext())
                {
                    row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dr[j] = null;
                        }
                        else
                        {
                            dr[j] = cell.ToString();
                            if (cell.ToString() == "")
                            {
                                ColumnDataNum = ColumnDataNum + 1;
                            }
                        }
                    }
                    if (ColumnDataNum != row.LastCellNum)
                    {
                        dt.Rows.Add(dr);
                    }

                    ColumnDataNum = 0;
                }
                result.Add(sheet.SheetName, dt);
            }


            //文件是否存在
            if (System.IO.File.Exists(filePath))
            {
            }
            return(result);
        }
示例#21
0
        /// <summary>
        /// Goes through the Workbook, optimising the fonts by
        /// removing duplicate ones.
        /// For now, only works on fonts used in HSSFCellStyle
        /// and HSSFRichTextString. Any other font uses
        /// (eg charts, pictures) may well end up broken!
        /// This can be a slow operation, especially if you have
        /// lots of cells, cell styles or rich text strings
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the fonts</param>
        public static void OptimiseFonts(HSSFWorkbook workbook)
        {
            // Where each font has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumberOfFontRecords + 1];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each font record, so we can do deletes
            //  without Getting confused
            FontRecord[] frecs = new FontRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                // There is no 4!
                if (i == 4)
                {
                    continue;
                }

                frecs[i] = workbook.Workbook.GetFontRecordAt(i);
            }

            // Loop over each font, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and
            //  mark the later one as needing deleting
            // Note - don't change built in fonts (those before 5)
            for (int i = 5; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    if (j == 4)
                    {
                        continue;
                    }

                    FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
                    if (frCheck.SameProperties(frecs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only need to worry about user fonts
            for (int i = 5; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition  = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j])
                    {
                        newPosition--;
                    }
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user font records
            for (int i = 5; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveFontRecord(
                        frecs[i]
                        );
                }
            }

            // Tell HSSFWorkbook that it needs to
            //  re-start its HSSFFontCache
            workbook.ResetFontCache();

            // Update the cell styles to point at the
            //  new locations of the fonts
            for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
            {
                ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
                xfr.FontIndex = (
                    newPos[xfr.FontIndex]
                    );
            }

            // Update the rich text strings to point at
            //  the new locations of the fonts
            // Remember that one underlying unicode string
            //  may be shared by multiple RichTextStrings!
            ArrayList doneUnicodeStrings = new ArrayList();

            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
                IEnumerator rIt            = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell = (HSSFCell)cIt.Current;
                        if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
                        {
                            HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
                            UnicodeString      u   = rtr.RawUnicodeString;

                            // Have we done this string already?
                            if (!doneUnicodeStrings.Contains(u))
                            {
                                // Update for each new position
                                for (short i = 5; i < newPos.Length; i++)
                                {
                                    if (i != newPos[i])
                                    {
                                        u.SwapFontUse(i, newPos[i]);
                                    }
                                }

                                // Mark as done
                                doneUnicodeStrings.Add(u);
                            }
                        }
                    }
                }
            }
        }
        public static List <DataTable> ImportExcelFile(string filePath, MyDataGridView dgv1, MyDataGridView dgv2, MyDataGridView dgv3, MyDataGridView dgv4, MyDataGridView dgv5)
        {
            List <DataTable> dts = new List <DataTable>();
            HSSFWorkbook     hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt1  = (DataTable)dgv1.DataSource;
            DataTable dt2  = (DataTable)dgv2.DataSource;
            DataTable dt3  = (DataTable)dgv3.DataSource;
            DataTable dt4  = (DataTable)dgv4.DataSource;
            DataTable dt5  = (DataTable)dgv5.DataSource;
            int       cols = sheet.GetRow(0).LastCellNum;
            if (cols != 15 && cols != 13)
            {
                return(dts);
            }
            dt1.Clear();
            dt2.Clear();
            dt3.Clear();
            dt4.Clear();
            dt5.Clear();
            //前两行为标题
            rows.MoveNext();
            rows.MoveNext();

            int count = 0;
            while (rows.MoveNext())
            {
                count++;
                if (count > 10)
                {
                    break;
                }

                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr1 = dt1.NewRow();
                DataRow dr2 = dt2.NewRow();
                DataRow dr3 = dt3.NewRow();
                DataRow dr4 = dt4.NewRow();
                DataRow dr5 = dt5.NewRow();

                NPOI.SS.UserModel.ICell cell = null;
                createColumn(dt1, cell, row, dr1, 0, 1);  //输入功率标定
                createColumn(dt2, cell, row, dr2, 3, 4);  //输出功率标定
                createColumn(dt3, cell, row, dr3, 6, 7);  //反射功率标定
                createColumn(dt4, cell, row, dr4, 9, 10); //ALC功率标定

                //衰减补偿
                if (count > 3)
                {
                    continue;
                }
                createColumn(dt5, cell, row, dr5, 12, 14);
            }
            dts.Add(dt1);
            dts.Add(dt2);
            dts.Add(dt3);
            dts.Add(dt4);
            dts.Add(dt5);
            return(dts);
        }