C# ExcelPackage.Load方法代码示例

 public static DataTable GetDataTableFromExcel(string path)
 {
     using (var pck = new ExcelPackage())
     {
         using (var stream = File.OpenRead(path))
         {
             pck.Load(stream);
         }
         var ws = pck.Workbook.Worksheets[0];
         var tbl = new DataTable();
         var cells = ws.Cells[1, 1, 1, ws.Dimension.End.Column];
         for (var i = cells.Start.Column; i <= cells.End.Column; i++)
         {
             tbl.Columns.Add(cells[1, i].Value.ToString());
         }
         for (var rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)
         {
             var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
             var row = tbl.NewRow();
             for (var cellIndex = 1; cellIndex <= ws.Dimension.End.Column; cellIndex++)
             {
                 row[cellIndex - 1] = wsRow[rowNum, cellIndex].Value;
             }
             tbl.Rows.Add(row);
         }
         return tbl;
     }
 }

        protected override void BeginProcessing()
        {
            base.BeginProcessing();

            _ExcelPck = new ExcelPackage();

            var finalPath = this.CompileFinalPath();

            using (var stream = new FileStream(finalPath, FileMode.Open))
            {
                _ExcelPck.Load(stream);
            }
        }

        public JsonResult GetVendorUploadDetails(string fileName, string type,string file)
        {
            System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
            System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
            log.Debug("Start: " + methodBase.Name);

            string path = Path.Combine(Server.MapPath("~/VendorReportsExcelTemplate"), fileName);

            //var existingFile = new FileInfo(path);
            DataTable dtVendor = new DataTable();
            DataTable dtErrorRec = new DataTable();
            Vendor ObjVd = new Vendor();
            var VendorInfo = new VendorInfo();
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                try
                {
                    var workBook = pck.Workbook;
                    if (workBook != null)
                    {
                        using (var stream = System.IO.File.OpenRead(path))
                        {
                            pck.Load(stream);
                        }
                        var currentWorksheet = pck.Workbook.Worksheets.First();

                        int Count=0; DataSet ds = new DataSet();
                        Count = ValidateExcel(type, currentWorksheet, Count, ds);
                        if (Count > 0)
                        {
                            string data = JsonConvert.SerializeObject(ds, Formatting.Indented);
                            return Json(data);
                        }

                        dtVendor = CreateVendorDataTable();
                        dtErrorRec = CreateErrorDataTable();
                        for (int rowNumber = ExcelStartRow + 1; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
                        // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                        {
                            //Column 4 : VendorRefNo    And     Column 5 : Amount    ARE MANDITORY
                            if (currentWorksheet.Cells[rowNumber, 4].Value != null && currentWorksheet.Cells[rowNumber, 5].Value != null)
                            {
                                try
                                {
                                    DataRow dr = dtVendor.NewRow();
                                    currentWorksheet.Cells[rowNumber, 1].Style.Numberformat.Format = "mm/dd/yyyy";
                                    dr["Date"] = (currentWorksheet.Cells[rowNumber, 1].Value == null ? DBNull.Value.ToString() : currentWorksheet.Cells[rowNumber, 1].Text);
                                    dr["UsedBy"] = (currentWorksheet.Cells[rowNumber, 2].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 2].Value);
                                    dr["Description"] = (currentWorksheet.Cells[rowNumber, 3].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 3].Value);
                                    dr["ReferenceNo"] = (currentWorksheet.Cells[rowNumber, 4].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 4].Value);
                                    dr["Amount"] = (currentWorksheet.Cells[rowNumber, 5].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 5].Value);
                                    dtVendor.Rows.Add(dr);
                                }
                                catch (Exception)
                                {
                                    DataRow dr = dtErrorRec.NewRow();
                                    currentWorksheet.Cells[rowNumber, 1].Style.Numberformat.Format = "mm/dd/yyyy";
                                    dr["Date"] = (currentWorksheet.Cells[rowNumber, 1].Value == null ? DBNull.Value.ToString() : currentWorksheet.Cells[rowNumber, 1].Text);
                                    dr["UsedBy"] = (currentWorksheet.Cells[rowNumber, 2].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 2].Value);
                                    dr["Description"] = (currentWorksheet.Cells[rowNumber, 3].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 3].Value);
                                    dr["ReferenceNo"] = (currentWorksheet.Cells[rowNumber, 4].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 4].Value);
                                    dr["Amount"] = (currentWorksheet.Cells[rowNumber, 5].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 5].Value);
                                    dtErrorRec.Rows.Add(dr);
                                }
                            }

                        }

                    }

                    ObjVd.dtvendor = dtVendor;
                    ObjVd.dtErrorRec = dtErrorRec;
                    ObjVd.RecordCount = dtVendor.Rows.Count;
                    ObjVd.Type = type;
                    ObjVd.Name = file;
                    string UserIDSession = Convert.ToString(System.Web.HttpContext.Current.Session["UserID"]);
                    bool checkForEmptyExcel = (dtVendor.Rows.Count == 0 && dtErrorRec.Rows.Count == 0) ? false : true;
                    if (!string.IsNullOrEmpty(UserIDSession))
                    {
                        if (checkForEmptyExcel)
                        {
                            ObjVd.UploadedBy = Convert.ToInt32(UserIDSession);
                            VendorInfo = ObjVd.InsertVendorRecords(ObjVd);
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Error("Error: " + ex);
                    VendorInfo.ExceptionMessage = "exception";
                }
                finally
                {
                    log.Debug("End: " + methodBase.Name);

                    if (System.IO.File.Exists(path))
                        System.IO.File.Delete(path);
                }
            }
            return Json(VendorInfo);
//.........这里部分代码省略.........

 public static ExcelWorkbook LoadWorkbookFromBytes(ExcelPackage package, byte[] bytes)
 {
     using (var stream = new MemoryStream(bytes)) {
         package.Load(stream);
         return package.Workbook;
     }
 }

 private void Read_2007or2010()
 {
     try
     {
         FileStream stream;
         try
         {
             stream = new FileStream(txtTenFile.Text, FileMode.Open);
         }
         catch (Exception)
         {
             MessageBox.Show(FormResource.msgKiemTraFile, FormResource.MsgCaption, MessageBoxButtons.OK,
                 MessageBoxIcon.Information);
             ResultValue = null;
             return;
         }
         var excelPkg = new ExcelPackage();
         excelPkg.Load(stream);
         stream.Close();
         var oSheet = excelPkg.Workbook.Worksheets[1];
         var startRows = oSheet.Dimension.Start.Row + ViTriHeader;
         var endRows = oSheet.Dimension.End.Row;
         var maximum = (endRows - startRows + 1) > 100 ? (endRows - startRows + 1) : 200;
         upsbLoading.SetPropertyThreadSafe(p => p.Maximum, maximum);
         var donvi = (endRows - startRows + 1) == 0 ? maximum : maximum / (endRows - startRows + 1);
         for (var i = startRows; i <= endRows; i++)
         {
             _result.Rows.Add(
                 oSheet.Cells[i, 1].GetValue<string>(),
                 oSheet.Cells[i, 2].GetValue<string>(),
                 oSheet.Cells[i, 3].GetValue<string>(),
                 oSheet.Cells[i, 4].GetValue<string>(),
                 oSheet.Cells[i, 5].GetValue<string>()
                 );
            upsbLoading.SetPropertyThreadSafe(c => c.Value, (i - startRows + 1) * donvi);
         }
         upsbLoading.SetPropertyThreadSafe(c => c.Value, maximum);
         ResultValue = _result;
     }
     catch (Exception ex)
     {
        Log2File.LogExceptionToFile(ex);
        ResultValue = null;
     }
 }

        public void DeleteColumnAfterRangeLimitThrowsArgumentException()
        {
            // Arrange
            ExcelPackage pck = new ExcelPackage();
            using (
                Stream file =
                    Assembly.GetExecutingAssembly()
                        .GetManifestResourceStream("EPPlusTest.TestWorkbooks.PreDeleteColumn.xls"))
            {
                pck.Load(file);
            }
            var wsData = pck.Workbook.Worksheets[1];

            // Act
            wsData.DeleteColumn(16385);

            // Assert
            Assert.Fail();

        }

        protected override List<ExcelProject> GetListRows(string fileName)
        {
            var stream = File.OpenRead(fileName);
            var excelPack = new ExcelPackage();
            excelPack.Load(stream);
            stream.Close();

            var listRows = new List<ExcelProject>();

            for (int i = 1; i <= excelPack.Workbook.Worksheets[1].Dimension.Rows; i++)
            {
                var structure = new ExcelProject();

                var dataRow = excelPack.Workbook.Worksheets[1].Cells[i, 1, i, 18];
                structure.Row = new object[18];
                for (int j = 0; j < 18; j++)
                {
                    structure.Row[j] = ((object[,])dataRow.Value)[0, j];
                }

                listRows.Add(structure);
            }

            return listRows;
        }

        public ActionResult InvoiceFromCSS2(string command)
        {
            try
            {
                if (command == "Sent To ACCPAC")
                {
                    #region Download

                    DataSet dsInvoice = Billing.GetCABPrepareInvoiceFromCSS2();
                    if (dsInvoice.Tables[0].Rows.Count > 0)
                    {
                        var fileName = "INVOICE_FROM_CSS2_" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";

                        //var outputDir = HttpContext.Server.MapPath("~/CSS2ACCPACINTEGRATION/FromCSS2/");
                        //System.IO.FileInfo file = new FileInfo(outputDir + fileName);

                        string path = ConfigurationManager.AppSettings["AccpacFolderPath"].ToString() + "FromCSS2/";
                        System.IO.FileInfo file = new FileInfo(path + fileName);

                        using (var excely = new ExcelPackage(file))
                        {

                            #region WorkSheet 1

                            ExcelWorksheet worksheet = excely.Workbook.Worksheets.Add("INVOICE_FROM_CSS2");
                            //for Columns Names
                            worksheet.Cells[1, 1].LoadFromDataTable(dsInvoice.Tables[0], true);
                            //For Data
                            for (int i = 0; i < dsInvoice.Tables[0].Rows.Count; i++)
                            {
                                for (int j = 0; j < dsInvoice.Tables[0].Columns.Count; j++)
                                {
                                    string cellvalue = dsInvoice.Tables[0].Rows[i][j].ToString();
                                    worksheet.Cells[i + 2, j + 1].Value = cellvalue;
                                }
                            }

                            #endregion

                            #region Work Sheet 2

                            ExcelWorksheet worksheet1 = excely.Workbook.Worksheets.Add("INVOICE_DETAILS_FROM_CSS2");

                            //for Columns Names
                            worksheet1.Cells[1, 1].LoadFromDataTable(dsInvoice.Tables[1], true);
                            //For Data
                            for (int i = 0; i < dsInvoice.Tables[1].Rows.Count; i++)
                            {
                                for (int j = 0; j < dsInvoice.Tables[1].Columns.Count; j++)
                                {
                                    string cellvalue = dsInvoice.Tables[1].Rows[i][j].ToString();
                                    worksheet1.Cells[i + 2, j + 1].Value = cellvalue;
                                }
                            }

                            #endregion

                            #region Update SentStatus

                            string CABMasterIDs = string.Empty;

                            foreach (DataRow dr in dsInvoice.Tables[0].Rows)
                            {
                                CABMasterIDs += dr["CNTITEM"].ToString() + ",";
                            }

                            Billing.CABUpdateSentStatus(CABMasterIDs.TrimEnd(','));

                            #endregion

                            #region Invoice Log

                            string Direction = "InvoiceFromCSS2";
                            int InvoiceCount = dsInvoice.Tables[0].Rows.Count;
                            int DetailsCount = dsInvoice.Tables[1].Rows.Count;
                            int Result = Billing.InsertCABInvoiceLog(Direction, InvoiceCount, DetailsCount, fileName);

                            #endregion

                            excely.Save();
                            worksheet.Protection.IsProtected = true;
                            worksheet1.Protection.IsProtected = true;

                        }
                    }

                    #endregion
                }
                else if (command == "Read From ACCPAC")
                {
                    #region Read Excel

                    //var ExcelFiles = Directory.EnumerateFiles(HttpContext.Server.MapPath("~/CSS2ACCPACINTEGRATION/FromACCPAC/"), "*.xlsx");

                    string path = ConfigurationManager.AppSettings["AccpacFolderPath"].ToString() + "FromACCPAC/";
                    var ExcelFiles = Directory.EnumerateFiles(path, "*.xlsx");

                    foreach (string currentFile in ExcelFiles)
                    {
                        using (var pck = new ExcelPackage())
//.........这里部分代码省略.........

        /// <summary>
        /// Load an xlsx Excel file into a datatable
        /// </summary>
        public static DataTable ParseExcelStream2DataTable(MemoryStream AStream,
            bool AHasHeader = false,
            int AWorksheetID = 0,
            List <string>AColumnsToImport = null)
        {
            ExcelPackage pck = new ExcelPackage();

            pck.Load(AStream);

            int countWorksheets = 0;
            ExcelWorksheet worksheet = null;

            foreach (ExcelWorksheet worksheetLoop in pck.Workbook.Worksheets)
            {
                if (countWorksheets == AWorksheetID)
                {
                    worksheet = worksheetLoop;
                }

                countWorksheets++;
            }

            DataTable result = new DataTable();

            if (worksheet == null)
            {
                return result;
            }

            List <string>ColumnNames = new List <string>();

            foreach (ExcelRangeBase firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
            {
                string ColumnName = (AHasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                ColumnNames.Add(ColumnName);

                if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnName))
                {
                    continue;
                }

                result.Columns.Add(ColumnName);
            }

            int firstDataRow = AHasHeader ? 2 : 1;

            for (int countRow = firstDataRow; countRow <= worksheet.Dimension.End.Row; countRow++)
            {
                ExcelRangeBase ExcelRow = worksheet.Cells[countRow, 1, countRow, worksheet.Dimension.End.Column];
                DataRow NewRow = result.NewRow();

                foreach (ExcelRangeBase cell in ExcelRow)
                {
                    if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnNames[cell.Start.Column - 1]))
                    {
                        continue;
                    }

                    NewRow[ColumnNames[cell.Start.Column - 1]] = cell.Value;
                }

                result.Rows.Add(NewRow);
            }

            return result;
        }

        private ExcelPackage OpenExcelFromByteArray()
        {
            try
            {
                ExcelPackage pck = new ExcelPackage();

                using (Stream stream = new MemoryStream(excelPackegInBytes))
                {
                    pck.Load(stream);
                    return pck;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteFirstTwoColumnsFromRangeColumnsShouldBeDeleted()
        {
            // Arrange
            ExcelPackage pck = new ExcelPackage();
            using (
                Stream file =
                    Assembly.GetExecutingAssembly()
                        .GetManifestResourceStream("EPPlusTest.TestWorkbooks.PreDeleteColumn.xls"))
            {
                pck.Load(file);
            }
            var wsData = pck.Workbook.Worksheets[1];

            // Act
            wsData.DeleteColumn(1, 2);
            pck.SaveAs(new FileInfo(OutputDirectory + "AfterDeleteColumn.xlsx"));

            // Assert
            Assert.AreEqual("First Name", wsData.Cells["A1"].Text);
            Assert.AreEqual("Family Name", wsData.Cells["B1"].Text);

        }

        // This function will parse all data from a DB report worksheet to a DataTable object
        public static DataTable ParseDataWorkSheet(string Entry_id)
        {
            string fileName = null;
            using (spark1Entities db = new spark1Entities())
            {
                ReportEntries entry = db
                    .ReportEntries
                    .Where(re => re.EntryId == Entry_id)
                    .FirstOrDefault();

                fileName = Path.Combine(
                    HttpContext.Current.Server.MapPath("~/xls_reports"),
                    entry.EntryUser,
                    entry.EntryId + ".xlsx");
            }

            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(fileName))
                {
                    pck.Load(stream);
                }
                var ws = pck.Workbook.Worksheets["Data"];
                DataTable tbl = new DataTable();
                bool hasHeader = true;
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }
                var startRow = hasHeader ? 2 : 1;
                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }
                return tbl;
            }
        }

        public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
        {
            request.CheckNotNull();
            Check.NotNullOrWhiteSpace(request.FileName, "filename");

            UploadHelper.CheckFileNameSecurity(request.FileName);

            if (!request.FileName.StartsWith("temporary/"))
                throw new ArgumentOutOfRangeException("filename");

            ExcelPackage ep = new ExcelPackage();
            using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
                ep.Load(fs);

            var p = ProductRow.Fields;
            var s = SupplierRow.Fields;
            var c = CategoryRow.Fields;

            var response = new ExcelImportResponse();
            response.ErrorList = new List<string>();

            var worksheet = ep.Workbook.Worksheets[1];
            for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
            {
                try
                {
                    var productName = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
                    if (productName.IsTrimmedEmpty())
                        continue;

                    var product = uow.Connection.TryFirst<ProductRow>(q => q
                        .Select(p.ProductID)
                        .Where(p.ProductName == productName));

                    if (product == null)
                        product = new ProductRow
                        {
                            ProductName = productName
                        };
                    else
                    {
                        // avoid assignment errors
                        product.TrackWithChecks = false;
                    }

                    var supplierName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
                    if (!string.IsNullOrWhiteSpace(supplierName))
                    {
                        var supplier = uow.Connection.TryFirst<SupplierRow>(q => q
                            .Select(s.SupplierID)
                            .Where(s.CompanyName == supplierName));

                        if (supplier == null)
                        {
                            response.ErrorList.Add("Error On Row " + row + ": Supplier with name '" +
                                supplierName + "' is not found!");
                            continue;
                        }

                        product.SupplierID = supplier.SupplierID.Value;
                    }
                    else
                        product.SupplierID = null;

                    var categoryName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
                    if (!string.IsNullOrWhiteSpace(categoryName))
                    {
                        var category = uow.Connection.TryFirst<CategoryRow>(q => q
                            .Select(c.CategoryID)
                            .Where(c.CategoryName == categoryName));

                        if (category == null)
                        {
                            response.ErrorList.Add("Error On Row " + row + ": Category with name '" +
                                categoryName + "' is not found!");
                            continue;
                        }

                        product.CategoryID = category.CategoryID.Value;
                    }
                    else
                        product.CategoryID = null;

                    product.QuantityPerUnit = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
                    product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 5].Value ?? 0);
                    product.UnitsInStock = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
                    product.UnitsOnOrder = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
                    product.ReorderLevel = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);

                    if (product.ProductID == null)
                    {
                        new ProductRepository().Create(uow, new SaveWithLocalizationRequest<MyRow>
                        {
                            Entity = product
                        });

                        response.Inserted = response.Inserted + 1;
                    }
                    else
                    {
//.........这里部分代码省略.........

        /// <summary>
        /// 指定されたExcelを読み込み、Databaseに登録する
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <param name="start_row"></param>
        /// <returns>Excel読み込みデータ</returns>
        public List<XlsData> LoadExcel(string path, string sheetName, int start_row)
        {
            log.Debug(string.Format("target file= {0}; sheet= {1}", path, sheetName));

            _start_row = start_row; //開始位置 (ログ出力に使用)
            int rowIndex = start_row;
            List<XlsData> records = new List<XlsData>();

            try
            {
                using (ExcelPackage xls = new ExcelPackage())
                {
                    using (FileStream fs = new FileStream(path, FileMode.Open))
                    {
                        xls.Load(fs);
                        ExcelWorksheet sheet = xls.Workbook.Worksheets[sheetName];

                        while (true)
                        {
                            XlsData data = new XlsData();

                            data.id = rowIndex - start_row;
                            // read row data
                            data.drcode = Utils.ParseString(sheet.Cells[rowIndex, 1].Value);
                            data.drname = Utils.ParseString(sheet.Cells[rowIndex, 2].Value);
                            if (string.IsNullOrEmpty(data.drname))
                            {
                                break; //Dr名が無ければ終了
                            }
                            data.ncc_cd = Utils.ParseString(sheet.Cells[rowIndex, 3].Value);
                            data.ncc_name = Utils.ParseString(sheet.Cells[rowIndex, 4].Value);
                            data.ncc_dept = Utils.ParseString(sheet.Cells[rowIndex, 5].Value);
                            data.title = Utils.ParseString(sheet.Cells[rowIndex, 6].Value);
                            data.category = Utils.ParseString(sheet.Cells[rowIndex, 7].Value);
                            data.kingaku = Utils.ParseLong(sheet.Cells[rowIndex, 8].Value);
                            data.kaisu = Utils.ParseInt(sheet.Cells[rowIndex, 9].Value);

                            // 頭 '0' 埋め
                            data.drcode = data.drcode.PadLeft(6, '0');
                            if (data.drcode == "000000")
                            {
                                data.drcode = string.Empty;
                            }

                            records.Add(data);
                            rowIndex++;
                        }

                    }
                }

                //読み込んだデータをDatabaseに登録
                int registed = BulkInsert(records);

                log.Info(string.Format("登録件数= {0}", registed));
            }
            catch (Exception exp)
            {
                log.Error(string.Format(@"Fatal Error.: {0}", rowIndex), exp);
            }

            return records;
        }

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        if (Extension == ".xls")
        {
            //string notxlsx = ("This is not an xlsx file");
            //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);

            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))

                hssfworkbook = new HSSFWorkbook(file);

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

            DataTable dt = new DataTable();

            //Counts the number of cells in a row and determines the columns from that.
            int counter = sheet.GetRow(0).Cells.Count;
            // J < number of columns needs to be exact at this moment
            for (int j = 0; j < counter; j++)
            {

                // set each column to a - ** letters
                // dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());

                //Get first row and set the headers for each cell
                //dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
                //Get each cell value in row 0 and return its string for a column name.
                dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
            }

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

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

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);

            }
            //Hackish way to remove the bad first row made by getting column names
            dt.Rows.RemoveAt(0);
            GridView1.Caption = Path.GetFileName(FilePath);
            GridView1.DataSource = dt;
            //Bind the data
            GridView1.DataBind();
            sheet.Dispose();
            hssfworkbook.Dispose();

        }
        else
        {
            //Create a new epplus package using openxml
            var pck = new OfficeOpenXml.ExcelPackage();

            //load the package with the filepath I got from my fileuploader above on the button
            //pck.Load(new System.IO.FileInfo(FilePath).OpenRead());

            //stream the package
            FileStream stream = new FileStream(FilePath, FileMode.Open);
            pck.Load(stream);

            //So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
            //Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
            var ws = pck.Workbook.Worksheets[1];

            //This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
            if (ws == null)
            {
                ws = pck.Workbook.Worksheets.Add("Sheet1");
                // Obiviously I didn't add anything to the sheet so probably can count on it being blank.
            }

            //I created this datatable for below.
            DataTable tbl = new DataTable();

            //My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
            var hdr = bool.Parse(isHDR);
            Console.WriteLine(hdr);

            //Set the bool value for from above.
            var hasHeader = hdr;

            //Setup the table based on the value from my bool
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
//.........这里部分代码省略.........