Ejemplo n.º 1
0
        /// <summary>
        /// 文件参数
        /// </summary>
        /// <param name="fileClass">文件类型</param>
        /// <param name="folderIfFile">文件还是文件夹</param>
        public FileUtilities(string fileClass, string folderIfFile)
        {
            Dictionary <string, string> valuePairs = null;

            // 非空判断
            if (string.IsNullOrWhiteSpace(fileClass) || string.IsNullOrWhiteSpace(folderIfFile))
            {
                valuePairs = new Dictionary <string, string>();
                valuePairs.Add("fileClass", fileClass);
                valuePairs.Add("folderIfFile", folderIfFile);
                LogUtilities.IsNullErrorLog(valuePairs);
            }
            FileClass    = fileClass;
            FolderIfFile = folderIfFile;
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Excel 转换为 Datatable
        /// </summary>
        /// <param name="fileUrl">Excel文件路径</param>
        /// <param name="sheetNum">工作表索引</param>
        /// <param name="isFirstRowColumn">首行为列</param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string fileUrl, int sheetNum = 0, bool isFirstRowColumn = true)
        {
            Dictionary <string, string> valuePairs = null;

            if (string.IsNullOrWhiteSpace(fileUrl))
            {
                valuePairs = new Dictionary <string, string>();
                valuePairs.Add("fileUrl", fileUrl);
                LogUtilities.IsNullErrorLog(valuePairs);
            }
            IWorkbook workbook = null;
            ISheet    sheet    = null;
            DataTable myTable  = new DataTable();

            try
            {
                using (var fs = new FileStream(fileUrl, FileMode.Open, FileAccess.Read))
                {
                    if (fileUrl.IndexOf(".xlsx") > 0)
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else if (fileUrl.IndexOf(".xls") > 0)
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    sheet = workbook.GetSheetAt(sheetNum);
                }
                //工作表不能为空
                if (sheet == null)
                {
                    string str = "";
                    for (int i = 0; i < workbook.NumberOfSheets; i++)
                    {
                        str += workbook.GetSheetAt(i).SheetName + ",";
                    }
                    str = workbook.NumberOfSheets + str;
                    throw new Exception($"sheet不能为空!参数:{sheetNum} 工作簿信息:{str}");
                }

                //Excel最大列数
                int MaxColumnNum = 0;
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row.LastCellNum > MaxColumnNum)
                    {
                        MaxColumnNum = row.LastCellNum;
                    }
                }
                //Excel行数
                int MaxRowNum = sheet.LastRowNum;

                //table新增列
                for (int i = 0; i < MaxColumnNum; ++i)
                {
                    //首行为列
                    if (isFirstRowColumn)
                    {
                        bool  addEmptyCell = true;//是否添加空列
                        ICell cell         = sheet.GetRow(0).GetCell(i);
                        if (cell != null)
                        {
                            //table列赋值
                            string cellValue = "";//列名
                            if (cell.CellType == CellType.Numeric)
                            {
                                cellValue = cell.NumericCellValue.ToString();
                            }
                            else
                            {
                                cellValue = cell.StringCellValue;
                            }
                            if (!string.IsNullOrWhiteSpace(cellValue))
                            {
                                //列数据为Excel的数据
                                addEmptyCell = false;
                                myTable.Columns.Add(new DataColumn(cellValue));
                            }
                        }
                        if (addEmptyCell)
                        {
                            myTable.Columns.Add(new DataColumn(""));//列数据为空
                        }
                    }
                    else
                    {
                        myTable.Columns.Add(new DataColumn(i + ""));
                    }
                }

                //起始行
                int startRow = 0;
                if (isFirstRowColumn)
                {
                    startRow = 1;
                }

                //DataTable赋值
                for (int i = startRow; i <= MaxRowNum; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }

                    DataRow NewRow = myTable.NewRow();
                    for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
                    {
                        ICell  cell  = row.GetCell(j);
                        string value = "";
                        if (cell != null)
                        {
                            //table行赋值
                            if (cell.CellType == CellType.Numeric)
                            {
                                value = cell.NumericCellValue.ToString();
                                if ((j == 0) && ((i == 6) || (i == 12)))
                                {
                                    //特殊的几个单元格 转换为 日期格式
                                    value = ToDateTimeValue(cell.NumericCellValue.ToString());
                                }
                            }
                            else
                            {
                                //row.GetCell(j).SetCellType(CellType.String);
                                value = cell.StringCellValue;
                            }
                        }
                        NewRow[j] = value;
                    }
                    myTable.Rows.Add(NewRow);
                }
                return(myTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }