阅读 Excel 文件

有各种方法来读取 excel 文件。我将提供文件路径参数的示例。你可以通过各种方式在此帖子中阅读文件。

public void ReadExcel(string path)
        {
            // Write data in workbook from xls document.
            XSSFWorkbook workbook = new XSSFWorkbook(path);
            // Read the current table data
            XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
            // Read the current row data
            XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
            // LastCellNum is the number of cells of current rows
            int cellCount = headerRow.LastCellNum;
            DataTable dt = new DataTable();
            bool isBlanKRow = false;

            try
            {
                if (dt.Rows.Count == 0)
                {
                    //Reading First Row as Header for Excel Sheet;
                    try
                    {
                        for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                        {
                            // get data as the column header of DataTable
                            DataColumn column = new DataColumn(headerRow.GetCell(j).StringCellValue);
                            dt.Columns.Add(column);
                        }
                    }
                    catch (Exception Ex)
                    { }
                }

                for (int sheetindex = 0; sheetindex < workbook.NumberOfSheets; sheetindex++)
                {
                    sheet = (XSSFSheet)workbook.GetSheetAt(sheetindex);
                    if (null != sheet)
                    {
                        // LastRowNum is the number of rows of current table
                        int rowCount = sheet.LastRowNum + 1;
                        //Reading Rows and Copying it to Data Table;
                        try
                        {
                            for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
                            {
                                XSSFRow row = (XSSFRow)sheet.GetRow(i);
                                DataRow dataRow = dt.NewRow();
                                isBlanKRow = true;
                                try
                                {
                                    for (int j = row.FirstCellNum; j < cellCount; j++)
                                    {
                                        if (null != row.GetCell(j) && !string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                                        {
                                            dataRow[j] = row.GetCell(j).ToString();
                                            isBlanKRow = false;
                                        }

                                    }
                                }
                                catch (Exception Ex)
                                { }
                                if (!isBlanKRow)
                                {
                                    dt.Rows.Add(dataRow);
                                }
                            }
                        }
                        catch (Exception Ex)
                        { }
                    }
                }
            }
            catch (Exception Ex)
            { }
            finally
            {
                workbook.UnlockStructure();
                workbook.UnlockRevision();
                workbook.UnlockWindows();
                workbook = null;
                sheet = null;
            }
        }