讀取ecxel中資料——NPOI.Excel和Aspose

1、使用  Aspose.Cells.dll

  using Aspose.Cells;   命名空間

  public static DataTable AsposeReadExcel(string path)
        {
            Workbook workbook1 = new Workbook(path);
            Worksheet sourceSheet1 = workbook1.Worksheets[0];
            Cells cells1 = sourceSheet1.Cells;
            DataTable DtExcel = cells1.ExportDataTableAsString(0, 0, cells1.MaxDataRow + 1, cells1.MaxDataColumn + 1, false);//起始行、起始列，結束行，結束列，是否有表頭
            return DtExcel;
        }

2、在NuGet中匯入NPOI.Excel管理器

（非原創不記得從哪個博主處看到的了，所以沒有寫轉載地址）

 /// </summary>
        /// 讀取Excel,放入DataTable中
        /// <param name="fileName">檔案路勁</param>
        /// <param name="sheetName">表名</param>
        /// <param name="isFirstRowColumn">是否有表頭</param>
        /// <returns></returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = false)
        {
            //定義要回傳的datatable物件
            DataTable data = https://www.cnblogs.com/my2020/p/new DataTable();
            NPOI.SS.UserModel.ISheet sheet = null;
            //資料開始行(排除標題行)
            int startRow = 0;

            if (!File.Exists(fileName))
            {
                return null;
            }
            //根據指定路徑讀取檔案
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
            //IWorkbook workbook = new HSSFWorkbook(fs);
            //如果有指定作業表名稱
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                //如果沒有找到指定的sheetName對應的sheet，則嘗試獲取第一個sheet
                if (sheet == null)
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                //如果沒有指定的sheetName，則嘗試獲取第一個sheet
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
                //一行最后一個cell的編號 即總的列數
                int cellCount = firstRow.LastCellNum;
                //如果第一行是標題列名

                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                {
                    NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
                    if (cell != null)             //遍歷Excel表
                    {

                        //     string cellValue = https://www.cnblogs.com/my2020/p/cell.StringCellValue;
                        if (cell != null)
                        {
                            DataColumn column = new DataColumn(cell.ToString());
                            data.Columns.Add(column);
                        }
                    }
                }
                if (isFirstRowColumn)
                {
                    startRow = sheet.FirstRowNum + 1;      //有表頭時不讀取表頭
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最后一列的標號
                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                    if (row == null) continue; //沒有資料的行默認是null　　　　　　　

                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        if (row.GetCell(j) != null) //同理，沒有資料的單元格都默認是null
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }
            }
            return data;

        }

標籤：C#

上一篇：IdentityServer4 介紹以及一些名詞解釋

下一篇：DataTable轉為TXT檔案