As we all know, npoi components can read and create Excel files when you don’t have Office installed locally. But we usually read the first sheet of C # tutorial of an excel file by default. So if you want to read all the sheets of an excel, what should you do?
Now let’s tell you how to operate npoi to read all sheets of Excel.
First of all, let’s explain how to operate a class created by Excel, which I named execlhelp
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
public class ExcelHelper : IDisposable
{
private string fileName = null; //file name
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper(string fileName)
{
this.fileName = fileName;
disposed = false;
}
/// <summary>
/// Import DataTable data into excel
/// </summary>
/// <param name="data">Data to be imported</param>
/// <param name="isColumnWritten">The column name of the DataTable to import or not</param>
/// <param name="sheetName">The name of the excel sheet to import</param>
/// <returns>the number of rows of data to import (the row containing the column name)</returns>
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // v-2007
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // v-2003
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //Write the column names to the DataTable
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //write to the excel
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// <summary>
/// Import data from excel to DataTable
/// </summary>
/// <param name="sheetName">The name of the excel workbook sheet</param>
/// <param name="isFirstRowColumn">whether the first row is the column name of the DataTable</param>
/// <returns>returnedDataTable</returns>
///
public Dictionary<int,string> ReturnSheetList()
{
Dictionary<int, string> t = new Dictionary<int, string>();
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003
workbook = new HSSFWorkbook(fs);
int count = workbook.NumberOfSheets; //get all SheetName
for(int i=0;i<count;i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet.LastRowNum > 0)
{
t.Add(i, workbook.GetSheetAt(i).SheetName);
}
}
return t;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}<br> ///index excel
public DataTable ExcelToDataTable(int index)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003
workbook = new HSSFWorkbook(fs);
//int coutnts = workbook.NumberOfSheets;
sheet = workbook.GetSheetAt(index);
//string names= sheet.SheetName;
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //The number of the last cell in a row is the total number of columns
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
CellType c = cell.CellType;
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
//The last column of the marker
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //Rows with no data are null by default
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) ! = null) // Similarly, cells with no data are null by default
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
return null;
throw new Exception(ex.Message);
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
fs = null;
disposed = true;
}
}
}<br><br>
The datatabletoexcel method is to export data to excel, and the parameters are annotated in the code, which can be applied directly. Exceltodatatable mainly imports Excel data into databtable. Similarly, parameters are also included in comments. We mainly talk about the returnsheetlist method. Before reading, we need to judge whether the imported excel version is a high version or a low version. This is because npoi provides different operation classes of high and low versions. Versions greater than 03 and less than 07 provide hssfworkbook, and versions less than 07 provide xssfworkbook. then workbook.NumberOfSheets This is mainly to get how many sheets there are in an excel file. We read the sheet according to the loop traversal, and then transfer the name of the sheet name and the corresponding index to a data dictionary for saving. So the data dictionary contains all the content sheets and corresponding indexes of the excel file you imported. With the use of exceltodatatable, you can switch to read different sheets of an excel.
The above is the details of reading multiple sheets of an excel file according to npoi
Read More:
- [Python] pandas Library pd.to_ Parameter arrangement and example of Excel operation writing into excel file
- Java – read all the files and folders in a certain directory and three methods to get the file name from the file path
- Panda was unable to open the. Xlsx file, xlrd.biffh.XLRDError : Excel xlsx file； not supported
- Call Excel to report an error (the exception comes from HResult: 0x80010105 (RPC)_ SERVERFAULT))
- Error reading file cow.osg: read error (Could not find plugin to read objects from file “cow.osg“.)
- Mybatis passes in multiple parameters to mapper. And uses @param details to report an error
- Error loading password’s fault file (MySQL for Excel)
- To solve the problem of C # calling excel interface error, prompt: the COM object of Microsoft. Office. Interop. Excel. Applicationclass is forcibly converted to the interface type “Microsoft. Offi”
- XLRDError: Excel xlsx file； Not supported error reporting solutions
- [unable to read project file xxxxx, XXX failed to load project file, name cannot start with “<" character (hex value 0x3c)] exception handling method
- Solution to the problem that SQL database query result field contains new line character, which leads to copy to excel dislocation
- Python failed to read TIF file exported by envi.
- Docker compose reports an error and multiple containers conflict
- C ා use math.net Read. Mat file, file content is complex
- Python read / write file error valueerror: I/O operation on closed file
- Java 8 Stream – Read a file line by line
- Solutions to Excel 2007 “cannot shift object off sheet”
- Cannot read configuration file due to insufficient permissions
- How to read JS file app.ux Global variables in
- Vue uses this. $refs. Subcomponent Ref. method to report an error: cannot read property ‘resetfields’ of undefined problem