コード例 #1
0
            /// <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:
                    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);
                }
            }
コード例 #2
0
        public void AbsoluteDayYearTooLow()
        {
            DateTime calendar = new DateTime(1899, 1, 1);

            try
            {
                HSSFDateUtil.AbsoluteDay(calendar, false);
                Assert.Fail("Should fail here");
            }
            catch (ArgumentException)
            {
                // expected here
            }

            try
            {
                calendar = new DateTime(1903, 1, 1);
                HSSFDateUtil.AbsoluteDay(calendar, true);
                Assert.Fail("Should fail here");
            }
            catch (ArgumentException)
            {
                // expected here
            }
        }
コード例 #3
0
        //更具xml node节点生成对应的CellNode实例
        public Data.CellNode getCellNode()
        {
            GlobalData.DATA_TYPE dataType;
            double  num      = 0;
            Boolean boolData = false;
            string  str      = null;
            //获取单元格的数据类型
            CellType cellType = _cell.CellType == CellType.Formula ? _cell.CachedFormulaResultType : _cell.CellType;

            if (cellType == CellType.Blank)
            {
                return(null);
            }
            else if (cellType == CellType.Numeric && !HSSFDateUtil.IsCellDateFormatted(_cell))
            {
                num      = _cell.NumericCellValue;
                dataType = GlobalData.DATA_TYPE.DOUBLE;
            }
            else if (cellType == CellType.Boolean)
            {
                boolData = _cell.BooleanCellValue;
                dataType = GlobalData.DATA_TYPE.BOOLEAN;
            }
            else
            {
                str      = _cell.StringCellValue;
                dataType = GlobalData.DATA_TYPE.STRING;
            }
            return(new Data.CellNode(dataType, _key, num, boolData, str));
        }
コード例 #4
0
        public void TestBug54557()
        {
            string format       = ".0000";
            bool   isDateFormat = HSSFDateUtil.IsADateFormat(165, format);

            Assert.AreEqual(false, isDateFormat);
        }
コード例 #5
0
ファイル: ExcelHelper.cs プロジェクト: lovenets/PMS
        public DataTable ReadDataTable()
        {
            DataTable table     = new DataTable();
            IRow      headerRow = sheet.GetRow(0);

            if (headerRow == null)
            {
                return(new DataTable());
            }
            int        cellfirst = headerRow.FirstCellNum;
            int        celllast  = headerRow.LastCellNum;
            List <int> array     = new List <int>();

            for (int i = cellfirst; i < celllast; i++)
            {
                string str = headerRow.GetCell(i)._ToStrTrim();
                if (str != "")
                {
                    DataColumn column = new DataColumn(str);
                    table.Columns.Add(column);
                    array.Add(i);
                }
            }
            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                DataRow dataRow = table.NewRow();
                for (int j = 0; j < array.Count; j++)
                {
                    if (row.GetCell(array[j]) != null)
                    {
                        if (row.GetCell(array[j]).CellType == CellType.Numeric)
                        {
                            if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(array[j])))
                            {
                                dataRow[j] = row.GetCell(array[j]).DateCellValue;
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(array[j]).NumericCellValue;
                            }
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(array[j]).ToString();
                        }
                    }
                }
                table.Rows.Add(dataRow);
            }

            return(table);
        }
コード例 #6
0
ファイル: ExcelHelpers.cs プロジェクト: kobeycs/FramworkNET
        /// <summary>
        /// 导入Excel中的数据到DataSet
        /// </summary>
        /// <param name="filePath">路径</param>
        /// <returns>DataSet</returns>
        //public static DataSet ImportExcel(string filePath,string type=null)
        //{
        //    //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
        //    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 14.0'";

        //    using (OleDbConnection OleConn = new OleDbConnection(strConn))
        //    {
        //        OleConn.Open();
        //        string sql = "SELECT * FROM  [Sheet1$]";
        //        OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
        //        DataSet OleDsExcle = new DataSet();
        //        OleDaExcel.Fill(OleDsExcle, "Sheet1");
        //        OleConn.Close();
        //        return OleDsExcle;
        //    }
        //}
        #region 原导入,已注释
        ///// <summary>
        ///// 导入Excel数据
        ///// </summary>
        ///// <param name="fileName"></param>
        ///// <returns></returns>
        //public static DataSet ImportExcel(string fileName, string type)
        //{
        //    if (string.IsNullOrEmpty(type))
        //        return null;

        //    //把EXCEL导入到DataSet
        //    DataSet ds = new DataSet();
        //    string connStr = "";
        //    //if (type == "2003")
        //    //{
        //    //    connStr = " Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=Excel 8.0;";
        //    //}
        //    //if (type == "2007")
        //    //{
        //        connStr = " Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=Excel 12.0;";
        //    //}

        //    using (OleDbConnection conn = new OleDbConnection(connStr))
        //    {
        //        conn.Open();
        //        OleDbDataAdapter da;
        //        //for (int i = 1; i <= n; i++)
        //        //{
        //        string sql = "select * from [Sheet1$]";
        //        da = new OleDbDataAdapter(sql, conn);
        //        da.Fill(ds, "Sheet1");
        //        da.Dispose();
        //        //}
        //        conn.Close();
        //        conn.Dispose();
        //    }
        //    //删除空白行
        //    if (ds.Tables.Count > 0 && ds.Tables[0].Rows != null)
        //    {
        //        List<DataRow> NullRow = new List<DataRow>();
        //        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        //        {
        //            bool HasValue = false;
        //            foreach (var item in ds.Tables[0].Rows[i].ItemArray)
        //            {
        //                if (item != null && item.ToString().Trim() != "")
        //                {
        //                    HasValue = true;
        //                    continue;
        //                }
        //            }
        //            if (!HasValue)
        //            {
        //                NullRow.Add(ds.Tables[0].Rows[i]);
        //            }
        //        }
        //        if (NullRow.Count > 0)
        //        {
        //            NullRow.ForEach(x => ds.Tables[0].Rows.Remove(x));
        //        }
        //    }
        //    return ds;
        //}

        #endregion

        #region 使用NPOI导入
        /// <summary>
        /// 使用NPOI导入Excel数据
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static DataSet ImportExcel(Stream stream)
        {
            DataSet ds       = new DataSet();
            var     wookBook = new HSSFWorkbook(stream);

            for (int sheetIndex = 0; sheetIndex < wookBook.NumberOfSheets; sheetIndex++)
            {
                var sheet = wookBook.GetSheetAt(sheetIndex);
                var table = GenrateNPOITable(sheet.GetRow(sheet.FirstRowNum));
                for (int rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    var tabelRow = table.NewRow();
                    var excelRow = sheet.GetRow(rowIndex);
                    if (excelRow != null)
                    {
                        for (int colIndex = 0; colIndex < table.Columns.Count; colIndex++)
                        {
                            var cell = excelRow.GetCell(colIndex);
                            if (cell != null)
                            {
                                if (cell.CellType == CellType.NUMERIC && HSSFDateUtil.IsCellDateFormatted(cell))
                                {
                                    tabelRow[colIndex] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                else
                                {
                                    tabelRow[colIndex] = cell.ToString();
                                }
                            }
                        }
                    }
                    table.Rows.Add(tabelRow);
                }
                //删除空行
                for (int i = table.Rows.Count - 1; i >= 0; i--)
                {
                    var  row       = table.Rows[i];
                    bool IsNullRow = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (!string.IsNullOrWhiteSpace(row[j].ToString()))
                        {
                            IsNullRow = false;
                            break;
                        }
                    }
                    if (IsNullRow)
                    {
                        table.Rows.Remove(row);
                    }
                }

                ds.Tables.Add(table);
            }

            return(ds);
        }
コード例 #7
0
        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="rowIdx">行号</param>
        /// <param name="colIdx">列号</param>
        /// <returns></returns>
        public object GetCellValue(int rowIdx, int colIdx)
        {
            object obj = null;

            try
            {
                IRow  row  = currentSheet.GetRow(rowIdx);
                ICell cell = currentSheet.GetRow(rowIdx).GetCell(colIdx);
                if (cell == null)
                {
                    return(obj = "");
                }

                switch (cell.CellType)
                {
                case CellType.Boolean:
                    obj = cell.BooleanCellValue;
                    break;

                case CellType.Error:
                    obj = cell.ErrorCellValue;
                    break;

                case CellType.Numeric:
                    //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))    //日期类型
                    {
                        DateTime dateTime = cell.DateCellValue;
                        obj = dateTime.ToShortDateString(); //转换为短日期类型
                    }
                    else                                    //其他数字类型
                    {
                        obj = cell.NumericCellValue;
                    }
                    //obj = cell.NumericCellValue;
                    break;

                case CellType.String:
                    obj = cell.StringCellValue;
                    break;

                case CellType.Formula:
                    obj = cell.CellFormula;
                    break;

                default:
                    obj = "";
                    break;
                }
            }
            catch (Exception ex)
            {
                string message = string.Format("获取表格{0}行{1}列的出错,{2}", rowIdx, colIdx, ex.Message);
                //MapSoftLog.LogError(message, ex);
            }
            return(obj);
        }
コード例 #8
0
        //格式转换
        private static String parseExcel(ICell cell)
        {
            string result = "";

            switch (cell.CellType)
            {
            case CellType.Formula:
                HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
                result = e.Evaluate(cell).StringValue;
                break;

            case CellType.Numeric: // 数字类型
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {                  // 处理日期格式、时间格式
                    string sdf = "";
                    if (cell.CellStyle.DataFormat == HSSFDataFormat
                        .GetBuiltinFormat("h:mm"))
                    {
                        sdf = "HH:mm";
                    }
                    else
                    {    // 日期
                        sdf = "yyyy-MM-dd";
                    }
                    DateTime date = cell.DateCellValue;
                    result = date.ToString(sdf);
                }
                else if (cell.CellStyle.DataFormat == 58)
                {
                    // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
                    string   sdf   = "yyyy-MM-dd";
                    double   value = cell.NumericCellValue;
                    DateTime date  = new DateTime(1899, 12, 30);    // 起始时间
                    date   = date.AddDays(value);
                    result = date.ToString(sdf);
                }
                else
                {
                    result = cell.NumericCellValue.ToString();
                }
                break;

            case CellType.String:    // String类型
                result = cell.StringCellValue;
                break;

            case CellType.Blank:
                result = "";
                break;

            default:
                result = "";
                break;
            }
            return(result);
        }
コード例 #9
0
ファイル: NPOIHelper.cs プロジェクト: wpmyj/EicWebPlatform
        /// <summary>
        /// 复制单元格
        /// </summary>
        /// <param name="wb"></param>
        /// <param name="srcCell"></param>
        /// <param name="distCell"></param>
        /// <param name="copyValueFlag"></param>
        public static void CopyCell(IWorkbook wb, ICell srcCell, ICell distCell, bool copyValueFlag)
        {
            ICellStyle newstyle = wb.CreateCellStyle();

            CopyCellStyle(wb, srcCell.CellStyle, newstyle);

            //样式
            distCell.CellStyle = newstyle;
            //评论
            if (srcCell.CellComment != null)
            {
                distCell.CellComment = srcCell.CellComment;
            }
            // 不同数据类型处理
            CellType srcCellType = srcCell.CellType;

            distCell.SetCellType(srcCellType);
            if (copyValueFlag)
            {
                if (srcCellType == CellType.Numeric)
                {
                    if (HSSFDateUtil.IsCellDateFormatted(srcCell))
                    {
                        distCell.SetCellValue(srcCell.DateCellValue);
                    }
                    else
                    {
                        distCell.SetCellValue(srcCell.NumericCellValue);
                    }
                }
                else if (srcCellType == CellType.String)
                {
                    distCell.SetCellValue(srcCell.RichStringCellValue);
                }
                else if (srcCellType == CellType.Blank)
                {
                    // nothing21
                }
                else if (srcCellType == CellType.Boolean)
                {
                    distCell.SetCellValue(srcCell.BooleanCellValue);
                }
                else if (srcCellType == CellType.Error)
                {
                    distCell.SetCellErrorValue(srcCell.ErrorCellValue);
                }
                else if (srcCellType == CellType.Formula)
                {
                    distCell.SetCellFormula(srcCell.CellFormula);
                }
                else
                { // nothing29
                }
            }
        }
コード例 #10
0
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string strFileName)
        {
            DataTable    dt = new DataTable();
            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

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

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow    row     = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
                            if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                            {
                                dataRow[j] = cell.DateCellValue.ToString("yyyy/MM/dd");
                            }
                            else//其他数字类型
                            {
                                dataRow[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dataRow[j] = cell.ToString();
                        }
                    }
                }

                dt.Rows.Add(dataRow);
            }
            return(dt);
        }
コード例 #11
0
        /// <summary>
        /// 将excel中的cell的值做转换
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private object GetCellValue(ICell cell)
        {
            switch (cell.CellType)
            {
            case CellType.Formula:       //公式
                object result;
                try
                {
                    try
                    {
                        result = cell.StringCellValue;
                    }
                    catch
                    {
                        result = cell.NumericCellValue;
                    }
                }
                catch
                {
                    result = null;
                }
                return(result);

            case CellType.Blank:
                return(cell.StringCellValue);

            case CellType.Boolean:
                return(cell.BooleanCellValue);

            case CellType.Error:
                return(cell.ErrorCellValue);

            case CellType.Numeric:
            {
                // 解决日期类型值读取问题 liufeng 2016年11月28日15:42:45
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    return(cell.DateCellValue);
                }
                else
                {
                    return(cell.NumericCellValue);
                }
            }

            case CellType.String:
                // 排除首尾空格 liufeng 2016年11月29日17:01:11
                return(cell.StringCellValue.Trim());

            default:
                return(null);
            }
        }
コード例 #12
0
        /// <summary>
        /// 获取列的值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string GetCellValue(ICell cell)
        {
            //为空直接返回数据
            if (cell == null)
            {
                return(string.Empty);
            }
            string value = string.Empty;

            switch (cell.CellType)
            {
            case CellType.Unknown:    //未知类型
                value = cell.ToString();
                break;

            case CellType.Numeric:    //数字类型
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    value = cell.DateCellValue.ToShortDateString();
                }
                else
                {
                    value = cell.ToString();
                }
                break;

            case CellType.String:    //string类型
                value = cell.StringCellValue;
                break;

            case CellType.Formula:    //计算公式
                value = string.Empty;
                break;

            case CellType.Blank:    //空文本
                value = string.Empty;
                break;

            case CellType.Boolean:    //逻辑类型
                value = cell.ToString();
                break;

            case CellType.Error:    //错误内容
                value = string.Empty;
                break;

            default:
                value = string.Empty;
                break;
            }
            return(value);
        }
コード例 #13
0
        /// <summary>
                 /// 从工作表中生成DataTable
                 /// </summary>
                 /// <param name="sheet"></param>
                 /// <param name="headerRowIndex"></param>
                 /// <returns></returns>
        private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int  cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    // 如果遇到第一个空列,则不再继续向后读取
                    cellCount = i;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                //如果遇到某行的第一个单元格的值为空,则不再继续向下读取
                if (row != null && !string.IsNullOrEmpty(row.GetCell(0).ToString()))
                {
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j).CellType.Equals(CellType.Numeric))
                        {
                            if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                            {
                                dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy/M/d");
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    table.Rows.Add(dataRow);
                }
            }

            return(table);
        }
コード例 #14
0
        /// <summary>
        /// 根据Excel列类型获取列的值.
        /// </summary>
        /// <param name="cell">cell.</param>
        /// <returns>值.</returns>
        public static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return(string.Empty);
            }

            switch (cell.CellType)
            {
            case CellType.Blank:
                return(string.Empty);

            case CellType.Boolean:
                return(cell.BooleanCellValue.ToString());

            case CellType.Error:
                return(cell.ErrorCellValue.ToString());

            case CellType.Numeric:
                return(HSSFDateUtil.IsCellDateFormatted(cell) ? $"{cell.DateCellValue:G}" : cell.NumericCellValue.ToString());

            case CellType.Unknown:
            default:
                return(cell.ToString());

            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Formula:
                try
                {
                    var da = cell.Sheet.Workbook.GetType().Name;
                    if (da == "HSSFWorkbook")
                    {
                        var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return(cell.ToString());
                    }
                    else
                    {
                        var e = new XSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return(cell.ToString());
                    }
                }
                catch
                {
                    return(cell.NumericCellValue.ToString());
                }
            }
        }
コード例 #15
0
        public void Bug56269()
        {
            double   excelFraction = 41642.45833321759d;
            DateTime calNoRound    = HSSFDateUtil.GetJavaCalendar(excelFraction, false);

            Assert.AreEqual(10, calNoRound.Hour);
            Assert.AreEqual(59, calNoRound.Minute);
            Assert.AreEqual(59, calNoRound.Second);
            DateTime calRound = HSSFDateUtil.GetJavaCalendar(excelFraction, false, null, true);

            Assert.AreEqual(11, calRound.Hour);
            Assert.AreEqual(0, calRound.Minute);
            Assert.AreEqual(0, calRound.Second);
        }
コード例 #16
0
ファイル: TExcelHelper.cs プロジェクト: fflorat/IRAP
        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private object GetValueType(ICell cell)
        {
            if (cell == null)
            {
                return(null);
            }
            switch (cell.CellType)
            {
            case CellType.Blank:
                return("");

            case CellType.Boolean:
                return(cell.BooleanCellValue);

            case CellType.Numeric:
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    return(cell.DateCellValue);
                }
                else if (cell.CellStyle.DataFormat == 58)
                {
                    return(cell.DateCellValue);
                }
                else
                {
                    return(cell.NumericCellValue);
                }

            //short format = cell.CellStyle.DataFormat;
            //switch (format)
            //{
            //    case 14:
            //    case 177:
            //    case 178:
            //    case 179:
            //        return cell.DateCellValue;
            //    default:
            //        return cell.NumericCellValue;
            //}
            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Error:
                return(cell.ErrorCellValue);

            case CellType.Formula:
            default:
                return("=" + cell.CellFormula);
            }
        }
コード例 #17
0
        //对单元格进行判断取值
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return(string.Empty);
            }

            switch (cell.CellType)
            {
            case CellType.Blank:     //空数据类型 这里类型注意一下,不同版本NPOI大小写可能不一样,有的版本是Blank(首字母大写)
                return(string.Empty);

            case CellType.Boolean:     //bool类型
                return(cell.BooleanCellValue.ToString());

            case CellType.Error:
                return(cell.ErrorCellValue.ToString());

            case CellType.Numeric:                          //数字类型
                if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
                {
                    return(cell.DateCellValue.ToString());
                }
                else     //其它数字
                {
                    return(cell.NumericCellValue.ToString());
                }

            case CellType.Unknown:       //无法识别类型
            default:                     //默认类型
                return(cell.ToString()); //

            case CellType.String:        //string 类型
                return(cell.StringCellValue);

            case CellType.Formula:     //带公式类型
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return(cell.ToString());
                }
                catch
                {
                    return(cell.NumericCellValue.ToString());
                }
            }
        }
コード例 #18
0
ファイル: ExcelHelper.cs プロジェクト: shanda506365/MVC-EXTJS
        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return(string.Empty);
            }
            switch (cell.CellType)
            {
            case CellType.BLANK:
                return(string.Empty);

            case CellType.BOOLEAN:
                return(cell.BooleanCellValue.ToString());

            case CellType.ERROR:
                return(cell.ErrorCellValue.ToString());

            case CellType.NUMERIC:
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    return(cell.DateCellValue.ToString());
                }
                else
                {
                    return(cell.ToString());
                }

            case CellType.Unknown:
            default:
                return(cell.ToString());

            case CellType.STRING:
                return(cell.StringCellValue);

            case CellType.FORMULA:
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return(cell.ToString());
                }
                catch
                {
                    return(cell.NumericCellValue.ToString());
                }
            }
        }
コード例 #19
0
ファイル: NpoiHelper.cs プロジェクト: s290305915/SucFramework
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public DataTable Import(string strFileName, int index)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(index);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

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

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

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

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        if (row.GetCell(j).CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                        {
                            dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                }

                dt.Rows.Add(dataRow);
            }
            return(dt);
        }
コード例 #20
0
        private String getStringCellValue(XSSFFormulaEvaluator eva, ICell cell)
        {
            // 获取单元格数据内容为字符串类型的数据
            String strCell = "";

            switch (cell.CellType)
            {
            case CellType.Numeric:
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    if (cell.DateCellValue != null)
                    {
                        //lynn:因为有些数据是有时分秒的,并且vourcher需要这个去判断时间
                        strCell = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    else
                    {
                        strCell = null;
                    }
                }
                else
                {
                    //var kk = cell.ToString();
                    strCell = cell.NumericCellValue.ToString();
                }
                break;

            case CellType.Formula:
                var tempType = eva.Evaluate(cell);
                if (tempType.CellType == CellType.Numeric)
                {
                    strCell = tempType.NumberValue.ToString();
                }
                else
                {
                    strCell = tempType.StringValue;
                }

                break;

            default:
                strCell = cell.ToString();
                break;
            }
            return(strCell);
        }
コード例 #21
0
        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return(string.Empty);
            }
            switch (cell.CellType)
            {
            case CellType.Blank:
                return(string.Empty);

            case CellType.Boolean:
                return(cell.BooleanCellValue.ToString());

            case CellType.Error:
                return(cell.ErrorCellValue.ToString());

            case CellType.Numeric:
            case CellType.Unknown:
            default:
                if (HSSFDateUtil.IsCellDateFormatted(cell))    //日期类型
                {
                    return(cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else    //其他数字类型
                {
                    return(cell.NumericCellValue.ToString());
                }

            //return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Formula:
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return(cell.ToString());
                }
                catch
                {
                    return(cell.NumericCellValue.ToString());
                }
            }
        }
コード例 #22
0
        /// <summary>
        /// 根据Excel列类型获取列的值(一般用于读取)
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        public static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return(string.Empty);
            }
            if (cell.CellType.ToString() == "System.DBNull")
            {
                return(string.Empty);
            }
            switch (cell.CellType)
            {
            case CellType.Blank:
                return(string.Empty);

            case CellType.Boolean:
                return(cell.BooleanCellValue.ToString());

            case CellType.Error:
                return(cell.ErrorCellValue.ToString());

            case CellType.Numeric:
                //日期类型
                return(HSSFDateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString() : cell.NumericCellValue.ToString());

            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Formula:
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return(cell.ToString());
                }
                catch
                {
                    return(cell.NumericCellValue.ToString());
                }

            default:
                return(cell.ToString());
            }
        }
コード例 #23
0
ファイル: ExcelUtil.cs プロジェクト: flybalance/Core.Util
        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell">cell对象</param>
        /// <returns></returns>
        private static object GetCellValue(ICell cell)
        {
            if (cell == null || (cell.CellType == CellType.String &&
                                 string.IsNullOrWhiteSpace(cell.StringCellValue)))
            {
                return(null);
            }

            CellType cellType = cell.CellType;

            switch (cellType)
            {
            case CellType.Blank:
                return(null);

            case CellType.Boolean:
                return(cell.BooleanCellValue);

            case CellType.Error:
                return(cell.ErrorCellValue);

            case CellType.Formula:
                return(cell.NumericCellValue);

            case CellType.Numeric:
                // 区分日期格式类型
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    DateTime date = cell.DateCellValue;
                    return(date.ToString(DATE_FORMATTER_PATTERN));
                }
                else
                {
                    cell.SetCellType(CellType.String);
                    return(cell.StringCellValue);
                }

            case CellType.String:
                return(cell.StringCellValue);

            default:
                return(null);
            }
        }
コード例 #24
0
    public static object GetCellValue(ICell cell)
    {
        if (cell == null)
        {
            return("");
        }
        else
        {
            switch (cell.CellType)
            {
            case CellType.Blank:
                return(cell.StringCellValue);

            case CellType.Boolean:
                return(cell.BooleanCellValue);

            case CellType.Error:
                return(cell.ErrorCellValue);

            case CellType.Formula:
            case CellType.Numeric:
                if (HSSFDateUtil.IsCellDateFormatted(cell))
                {
                    return(cell.DateCellValue);
                }
                else
                {
                    return(cell.NumericCellValue);
                }

            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Unknown:
                return(cell.RichStringCellValue);

            default:
                return(cell.StringCellValue);
            }
        }
    }
コード例 #25
0
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return(string.Empty);
            }

            switch (cell.CellType)
            {
            case CellType.Blank:
                return(string.Empty);

            case CellType.Boolean:
                return(cell.BooleanCellValue.ToString());

            case CellType.Error:
                return(cell.ErrorCellValue.ToString());

            case CellType.Numeric:
                return(HSSFDateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd") : cell.NumericCellValue.ToString());

            case CellType.Unknown:
            default:
                return(cell.ToString());

            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Formula:
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return(cell.ToString());
                }
                catch
                {
                    return(cell.NumericCellValue.ToString());
                }
            }
        }
コード例 #26
0
        /// <summary>
        /// 获取Cell字符串
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string GetCellString(ICell cell)
        {
            string cellValue = "";

            if (null != cell)
            {
                // 以下是判断数据的类型
                switch (cell.CellType)
                {
                case CellType.Numeric: // 数字
                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {                  // 判断是否为日期类型
                        cellValue = cell.DateCellValue.ToString("yyyyMMdd HH:mm:ss");
                    }
                    else
                    {
                        // 有些数字过大,直接输出使用的是科学计数法: 2.67458622E8 要进行处理
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.Format(cell.NumericCellValue);
                        // cellValue = cell.getNumericCellValue() + "";
                    }
                    break;

                case CellType.String:     // 字符串
                    cellValue = cell.StringCellValue;
                    break;

                case CellType.Boolean:     // Boolean
                    cellValue = cell.BooleanCellValue ? "1" : "0";
                    break;

                default:
                    cellValue = "wuwuyaoyao";
                    break;
                }
            }
            return(cellValue);
        }
コード例 #27
0
        private string _ReadCellNodeData(ICell cell)
        {
            string   cellDataStr;
            CellType cellType = cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType;

            if (cellType == CellType.Blank)
            {
                cellDataStr = null;
            }
            else if (cellType == CellType.Numeric && !HSSFDateUtil.IsCellDateFormatted(cell))
            {
                cellDataStr = "" + cell.NumericCellValue;
            }
            else if (cellType == CellType.Boolean)
            {
                cellDataStr = cell.BooleanCellValue ? "true" : "false";
            }
            else
            {
                cellDataStr = cell.StringCellValue;
            }
            return(cellDataStr);
        }
コード例 #28
0
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
        {
            ISheet    sheet    = null;
            DataTable data     = new DataTable();
            int       startRow = 0;

            NpoiDataType[] ColumnDataType = null;
            try
            {
                using (FileStream stream = new FileStream(@fileName, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(stream);//使用接口,自动识别excel2003/2007格式
                    if (sheetName != null)
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    if (sheet != null)
                    {
                        IRow firstRow  = sheet.GetRow(0);
                        int  cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                        if (isFirstRowColumn)
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                                data.Columns.Add(column);
                            }
                            startRow = sheet.FirstRowNum + 1;
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;
                        }

                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null)
                            {
                                continue;              //没有数据的行默认是null       
                            }
                            if (ColumnDataType == null)
                            {
                                ColumnDataType = new NpoiDataType[cellCount];
                                for (int j = 0; j < cellCount; j++)
                                {
                                    ICell hs = row.GetCell(j);
                                    if (hs != null)
                                    {
                                        ColumnDataType[j] = GetCellDataType(hs);
                                    }
                                }
                            }

                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                ICell hs = row.GetCell(j);
                                if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
                                {
                                    if (hs.CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(hs))
                                    {
                                        dataRow[j] = HSSFDateUtil.GetJavaDate(hs.NumericCellValue).ToString("HH:mm:ss");
                                    }
                                    else
                                    {
                                        dataRow[j] = hs;
                                    }
                                    //dataRow[j] = row.GetCell(j).ToString();
                                }
                                //dataRow[j] = GetCellData(ColumnDataType[j], row, j);
                            }
                            data.Rows.Add(dataRow);
                        }
                    }
                }
                return(data);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return(null);
            }
        }
コード例 #29
0
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="headno">要跳过的行</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, int headno)
        {
            ISheet    sheet    = null;
            DataTable data     = new DataTable();
            int       startRow = 0;

            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    //  int i = workbook.NumberOfSheets;////获取个数
                    //    string sheetname = workbook.GetSheetName(0);//获取名字
                    //sheet = workbook.GetSheetAt(0); //获取指定的那一个
                    //  MessageBox.Show(i.ToString());
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }

                if (headno != 0)
                {
                    startRow = sheet.FirstRowNum + headno;//第一行
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }

                if (sheet != null)
                {
                    int rowCount     = sheet.LastRowNum; //获得行数
                    int maxcellCount = 0;                //最大列数量
                    int maxcellno    = 0;                //最大列的行号
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;                    //没有数据的行默认是null   continue 结束单次循环     
                        }
                        int cellCount = row.LastCellNum; //一行最后一个cell的编号 这一行的总的列数
                        if (cellCount > maxcellCount)
                        {
                            maxcellCount = cellCount;//最大列数
                            maxcellno    = i;
                        }
                    }

                    IRow firstRow = sheet.GetRow(maxcellno);
                    int  cellNo   = 0;
                    for (int i = firstRow.FirstCellNum; i < maxcellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            // string cellValue = "";

                            /**********
                             * //单元格的类型为公式,返回公式的值
                             * if (cell.CellType == CellType.Numeric)
                             * {
                             *  //是日期型
                             *  if (HSSFDateUtil.IsCellDateFormatted(cell))
                             *  {
                             *      cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                             *  }
                             *  //不是日期型
                             *  else
                             *  {
                             *      cellValue = Convert.ToString(cell);
                             *  }
                             * }
                             * else
                             * {
                             *  cellValue = Convert.ToString(cell);
                             * }
                             *
                             *****/
                            cellNo = cellNo + 1;
                            string cellValue = Convert.ToString(cell) + cellNo;

                            //   string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    //最后一列的标号
                    //int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null || row.Cells.Count == 0)
                        {
                            continue; //没有数据的行默认是null   continue 结束单次循环     
                        }
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < maxcellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
                            {
                                string datastr = "";
                                /**********/
                                //单元格的类型为公式,返回公式的值  CellType.Numeric
                                if (row.GetCell(j).CellType == CellType.Numeric)
                                {
                                    //是日期型
                                    if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                                    {
                                        datastr    = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
                                        dataRow[j] = datastr;
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).NumericCellValue.ToString();
                                    }
                                }
                                else
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                                /**********/
                                string va = dataRow[j].ToString();
                                bool   r  = IsDateTime(va);
                                if (r)
                                {
                                    va = va.Replace(".", "-");
                                    va = va.Replace("/", "-");
                                    va = va.Replace("年", "-");
                                    va = va.Replace("月", "-");
                                    va = va.Replace("日", "");
                                    va = va.Replace("\\", "-");
                                }

                                if (va.Length > 0 && va.Substring(va.Length - 1, 1) == "-")
                                {
                                    va = va.Substring(0, va.Length - 1);
                                }
                                dataRow[j] = va;
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return(data);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return(null);
            }
        }
コード例 #30
0
ファイル: ExportExcel.cs プロジェクト: Lincoln007/DotNet.ERP
        public DataTable ToDataTable(Stream stream, int minRow = 0, int maxRow = 0)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            //var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = null;
            try
            {
                workbook = new XSSFWorkbook(stream);//2007
            }
            catch (Exception)
            {
                try
                {
                    workbook = new HSSFWorkbook(stream);//2003
                }
                catch
                {
                    throw new Exception("读取Excel文件失败!");
                }
            }
            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                if (minRow != 0) startRow = minRow - 1;//真实序号

                IRow firstRow = sheet.GetRow(startRow);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                for (int i = 0; i < cellCount; ++i)
                {
                    DataColumn column = new DataColumn(i.ToString());
                    data.Columns.Add(column);
                }

                //最后一列的标号
                int rowCount = sheet.LastRowNum;
                //if (maxRow != 0 && rowCount > maxRow) rowCount = maxRow - 1;
                if (maxRow != 0) rowCount = maxRow - 1;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null       
                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理,没有数据的单元格都默认是null
                        if (row.GetCell(j) != null)
                        {
                            if (row.GetCell(j).CellType == CellType.Formula)
                            {
                                //公式类型
                                sheet.ForceFormulaRecalculation = true;
                                HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                dataRow[j] = eva.EvaluateInCell(row.GetCell(j));
                            }
                            else if (row.GetCell(j).CellType == CellType.Numeric)
                            {
                                if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                                {
                                    //日期类型
                                    dataRow[j] = row.GetCell(j).DateCellValue;
                                }
                                else
                                {
                                    //其他数字类型
                                    dataRow[j] = row.GetCell(j).NumericCellValue;
                                }
                            }
                            else if (row.GetCell(j).CellType == CellType.Blank)
                            {
                                //空数据类型
                                dataRow[j] = "";
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).StringCellValue;
                            }
                        }
                    }
                    data.Rows.Add(dataRow);
                }
                //fs.Close();
            }
            return data;
        }