C#调用EPPlus.dll读取Excel去掉空列

最新推荐文章于 2024-07-11 00:41:48 发布

MagiAladdin

最新推荐文章于 2024-07-11 00:41:48 发布

阅读量676

点赞数

文章标签：开发语言 c#

本文链接：https://blog.csdn.net/zhq13630354727/article/details/121187011

版权

C#调用EPPlus.dll读取Excel去掉空列

		/// <summary>
        /// 获取excel数据
        /// </summary>
        /// <param name="file"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable GetExcelSheet(string file,string sheetName)
        {
            DataTable dt = new DataTable();
            try
            {
                using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
                {
                    ExcelWorksheet worksheet = null;
                    if (!string.IsNullOrWhiteSpace(sheetName))
                    {
                        worksheet = package.Workbook.Worksheets[sheetName];
                        dt.TableName = sheetName;
                    }
                    else
                    {
                        worksheet = package.Workbook.Worksheets[1];
                        dt.TableName = worksheet.Name;
                    }
                    //判断空列
                    int colCount = 0;
                    for (int a = worksheet.Dimension.Start.Column; a <= worksheet.Dimension.End.Column; a++)
                    {
                        string colName = worksheet.GetValue<string>(worksheet.Dimension.Start.Row, a);
                        if (string.IsNullOrWhiteSpace(colName))
                        {
                            colCount = a;
                            break;
                        }
                    }
                    //设置列名 
                    for (int c = worksheet.Dimension.Start.Column; c <= colCount; c++)
                    {
                        dt.Columns.Add(worksheet.GetValue<string>(worksheet.Dimension.Start.Row, c), typeof(System.String));
                    }
                    for (int r = worksheet.Dimension.Start.Row + 1; r < worksheet.Dimension.End.Row + 1; r++)
                    {
                        DataRow row = dt.NewRow();
                        for (int c = worksheet.Dimension.Start.Column; c <= colCount; c++)
                        {
                            row[c - 1] = worksheet.GetValue<string>(r, c);
                        }
                        dt.Rows.Add(row);
                    }
                    package.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;
        }