Reading from Excel File Using Closedxml

Reading from Excel File using ClosedXML

To access a row:

var row = ws1.Row(3);

To check if the row is empty:

bool empty = row.IsEmpty();

To access a cell (column) in a row:

var cell = row.Cell(3);

To get the value from a cell:

object value = cell.Value;
// or
string value = cell.GetValue<string>();

For more information see the documentation.

Reading from Excel File using ClosedXML

To access a row:

var row = ws1.Row(3);

To check if the row is empty:

bool empty = row.IsEmpty();

To access a cell (column) in a row:

var cell = row.Cell(3);

To get the value from a cell:

object value = cell.Value;
// or
string value = cell.GetValue<string>();

For more information see the documentation.

Importing Excel to DataGrid using ClosedXML and OpenFileDialog

You may want to re-think your approach to reading the excel file. One possible issue is the if (firstRow) { … … statement, which is odd and makes a dangerous assumption. The code “assumes” that each column of data “has” a header cell. In other words, the number of columns added to the DataTable will be determined by the number of cells found (with some text in the cell) on the “FIRST” row. What if a column of data does NOT have a header cell?

Therefore, if there are any rows that have data to the right of the first row’s cells with headers, then the DataTable will not have the correct number of columns… and, when the code gets to these cells in the else portion below… the code will most likely crash when i goes beyond dt’s column count.

The code needs to guarantee that dt has the correct number of columns to avoid the problem described above. One way to help is to find the two cells that define the “top-left” cell where the data begins (as it may not necessarily always be the first cell in the worksheet) and “bottom-right” cell where the “last” cell with data is located.

Once we have these two cells (top-left and bottom-right)… then, we can determine how many columns are needed in the DataTable… and… we can almost guarantee that all the data in the worksheet will fit in the DataTable.

Below is one possible solution using the ideas described above. Note, the code below does not use a particular worksheet name and simply uses the first worksheet in the given workbook.

private void Button_Click(object sender, RoutedEventArgs e) {
OpenFileDialog of = new OpenFileDialog();
of.Filter = "Excel Files | *.xlsx;";
of.Title = "Import Excel file.";
if (of.ShowDialog() == true) {
dataGrid.ItemsSource = ImportExceltoDataTable(of.FileName).DefaultView;
}
}

public static DataTable ImportExceltoDataTable(string filePath) {
using (XLWorkbook wb = new XLWorkbook(filePath)) {
IXLWorksheet ws = wb.Worksheet(1);
int tl_Row = ws.FirstCellUsed().Address.RowNumber;
int tl_Col = ws.FirstCellUsed().Address.ColumnNumber;
int br_Row = ws.LastCellUsed().Address.RowNumber;
int br_Col = ws.LastCellUsed().Address.ColumnNumber;
DataTable dt = new DataTable();
// add dt columns using the first row of data
for (int i = tl_Col; i <= br_Col; i++) {
dt.Columns.Add(ws.Cell(tl_Row, i).CachedValue.ToString());
}
IXLRow currentRow;
// add data from the worksheet to dt - we already used the first row of data for the columns
for (int dtRow = 0; dtRow < br_Row - tl_Row; dtRow++) {
currentRow = ws.Row(tl_Row + dtRow + 1);
dt.Rows.Add();
for (int dtCol = 0; dtCol < br_Col - tl_Col + 1; dtCol++) {
dt.Rows[dtRow][dtCol] = currentRow.Cell(tl_Col + dtCol).CachedValue;
}
}
return dt;
}
}

I hope this makes sense and helps.

Read Excel worksheet into DataTable using ClosedXML

This is example is not mine. I cannot remember where I got it from as it was in my archives. However, this works for me. The only issue I ran into was with blank cells. According to a dicussion on the ClosedXML GitHUb wiki page it has something to do with Excel not tracking empty cells that are not bounded by data. I found that if I added data to the cells and then removed the same data the process worked.

public static DataTable ImportExceltoDatatable(string filePath, string sheetName)
{
// Open the Excel file using ClosedXML.
// Keep in mind the Excel file cannot be open when trying to read it
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);

//Create a new DataTable.
DataTable dt = new DataTable();

//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;

foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}

return dt;
}
}

Need to add

using System.Data;
using ClosedXML.Excel;

As well as the ClosedXML nuget package

For other datetime data type... this could be helpful... reference

if (cell.Address.ColumnLetter=="J") // Column with date datatype
{
DateTime dtime = DateTime.FromOADate(double.Parse(cell.Value.ToString()));
dt.Rows[dt.Rows.Count - 1][i] = dtime;
}
else
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
}

reading Excel with ClosedXML

This gets the job done:

Dictionary<Tuple<int, int>, object> data = new Dictionary<Tuple<int, int>, object>();
using (XLWorkbook wb = new XLWorkbook(filePath))
{
var ws = wb.Worksheets.First();
var range = ws.RangeUsed();

for (int i = 1; i < range.RowCount() + 1; i++)
{
for (int j = 1; j < range.ColumnCount() + 1; j++)
{
data.Add(new Tuple<int, int>(i,j), ws.Cell(i,j).Value);
}
}
}


Related Topics



Leave a reply



Submit