Simple Template
You can apply to cells any formatting including conditional formats.
The template: Simple.xlsx
The result file: Simple.xlsx
Sorting the Collection
You can sort the collection by columns. Specify the tag <<sort>> in the options row of the corresponding columns. Add option desc to the tag if you wish the list to be sorted in the descending order (<<sort desc>>).
For more details look to the Sorting
The template: tLists1_sort.xlsx
The result file: tLists1_sort.xlsx
Totals
You can get the totals for the column in the ranges by specifying the tag in the options row of the corresponding column. In the example above we used tag <<sum>> in the column Amount paid.
For more details look to the Totals in a Column.
The template: tlists2_sum.xlsx
The result file: tlists2_sum.xlsx
Range and Column Options
Besides specifying the data for the range ClosedXML.Report allows you to sort the data in the range, calculate totals, group values, etc. ClosedXML.Report performs these actions if it founds the range or column tags in the service row of the range.
For more details look to the Flat Tables
In the example above example we applied auto filters, specified that columns must be resized to fit contents, replaced Excel formulas with the static text and protected the “Amount paid” against the modification. For this, we used tags <<AutoFilter>>, <<ColsFit>>, <<OnlyValues>> and <<Protected>>.
The template: tLists3_options.xlsx
The result file: tLists3_options.xlsx
Complex Range
ClosedXML.Report can use multi-row templates for the table rows. You may apply any format you wish to the cells, merge them, use conditional formats, Excel formulas.
For more details look to the Flat Tables
The template: tLists4_complexRange.xlsx
The result file: tLists4_complexRange.xlsx
Grouping
The <<group>> tag may be used along with any of the aggregating tags. Put the tag <<group>> into the service row of those columns which you wish to use for aggregation.
For more details look to the Grouping
The template: GroupTagTests_Simple.xlsx
The result file: GroupTagTests_Simple.xlsx
Collapsed Groups
Use the parameter collapse of the group tag (<<group collapse>>) if you want to display only those rows that contain totals or captions of data sections.
For more details look to the Grouping
The template: GroupTagTests_Collapse.xlsx
The result file: GroupTagTests_Collapse.xlsx
Summary Above the Data
ClosedXML.Report implements the tag summaryabove that put the summary row above the grouped rows.
For more details look to the Grouping
The template: GroupTagTests_SummaryAbove.xlsx
The result file: GroupTagTests_SummaryAbove.xlsx
Merged Cells in Groups (option 1)
The <<group>> tag has options making it possible merge cells in the grouped column. To achieve this specify the parameter mergelabels in the group tag (<<group mergelabels>>).
For more details look to the Grouping
The template: GroupTagTests_MergeLabels.xlsx
The result file: GroupTagTests_MergeLabels.xlsx
Merged Cells in Groups (option 2)
Tag <<group>> allows to group cells without adding the group title. This function may be enabled by using parameter MergeLabels=Merge2 in the group tag (<<group MergeLabels=Merge2>>). Cells containing the grouped data are merged and filled with the group caption.
For more details look to the Grouping
The template: GroupTagTests_MergeLabels2.xlsx
The result file: GroupTagTests_MergeLabels2.xlsx
Nested Groups
Ranges may be nested with no limitation on the depth of nesting.
For more details look to the Grouping
The template: GroupTagTests_NestedGroups.xlsx
The result file: GroupTagTests_NestedGroups.xlsx
Disable Groups Collapsing
Use the option disableoutline of the group tag (<<group disableoutline>>) to prevent them from collapsing. In the example above the range is grouped by both Company and Payment method columns. Collapsing of groups for the Payment method column is disabled.
For more details look to the Grouping
The template: GroupTagTests_DisableOutline.xlsx
The result file: GroupTagTests_DisableOutline.xlsx
Specifying the Location of Group Captions
The <<group>> tag has a possibility to put the group caption in any column of the grouped range by using the parameter PLACETOCOLUMN=n where n defines the column number in the range. (starting from 1). Besides, ClosedXML.Report supports the <<delete>> tag that aims to specify columns to delete. In the example above the Company column is grouped with the option mergelabels. The group caption is placed to the second column (PLACETOCOLUMN=2). Finally, the Company column is removed.
For more details look to the Grouping
The template: GroupTagTests_PlaceToColumn.xlsx
The result file: GroupTagTests_PlaceToColumn.xlsx
Formulas in Group Line
ClosedXML.Report saves the full text of cells in the service row, except tags. You can use this feature to specify Excel formulas in group captions. In the example above there is grouping by columns Company and Payment method. The Amount Paid column contains an Excel formula in the service row.
For more details look to the Grouping
The template: GroupTagTests_FormulasInGroupRow.xlsx
The result file: GroupTagTests_FormulasInGroupRow.xlsx
Groups with Captions
You can configure the appearance of the group caption by using the WITHHEADER parameter of the <<group>> tag. With this, the group caption is placed over the grouped rows. The SUMMARYABOVE does not change this behavior.
For more details look to the Grouping
The template: GroupTagTests_WithHeader.xlsx
The result file: GroupTagTests_WithHeader.xlsx
Nested Ranges
You can place one ranges inside the others in order to reflect the parent-child relation between entities. In the example above the Items range is nested into the Orders range which, in turn, is nested to the Customers range. Each of three ranges has its own header, and all have the same left and right boundary.
For more details look to the Nested ranges: Master-detail reports.
The template: Subranges_Simple_tMD1.xlsx
The result file: Subranges_Simple_tMD1.xlsx
Nested Ranges with Subtotals
You may use aggregation tags at any level of your master-detail report. In the example above the <<sum>> tag in the I9 cell will summarize columns « in the scope of an order, while the same tag in the I10 cell will summarize all the data for each Customer.
For more details look to the Nested ranges: Master-detail reports.
The template: Subranges_WithSubtotals_tMD2.xlsx
The result file: Subranges_WithSubtotals_tMD2.xlsx
Nested Ranges with Sorting
You can use the <<sort>> for the nested ranges as well.
For more details look to the Nested ranges: Master-detail reports.
The template: Subranges_WithSort_tMD3.xlsx
The result file: Subranges_WithSort_tMD3.xlsx
Pivot Tables
ClosedXML.Report support such a powerful tool for data analysis as pivot tables. You can define one or many pivot tables directly in the report template to benefit the power of the Excel pivot table constructor and nearly all the available features for they configuring and designing.
For more details look to the Pivot Tables.
The template: tPivot5_Static.xlsx
The result file: tPivot5_Static.xlsx
In this article I will explain with an example, how to read and import Excel file (Excel sheet) data to GridView using ClosedXml library in ASP.Net using C# and VB.Net.
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Note: The DLL files of both OpenXml and ClosedXml are present in the attached sample.
HTML Markup
The following HTML Markup consists of an ASP.Net FileUpload control, a Button and a GridView.
<asp:FileUpload ID=»FileUpload1″ runat=»server» />
<asp:Button ID=»btnImport» runat=»server» Text=»Import» OnClick=»ImportExcel» />
<hr />
<asp:GridView ID=»GridView1″ runat=»server»>
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Collections.Generic;
using ClosedXML.Excel;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports ClosedXML.Excel
Read and Import Excel data to DataTable using ClosedXml
When the Import Button is clicked, the following event handler is executed. First the uploaded Excel file is saved to a folder named Files and then it is opened and read using ClosedXml WorkBook class object.
Then the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Finally a loop is executed over the fetched rows and a DataTable is populated which is then bound to the GridView.
C#
protected void ImportExcel(object sender, EventArgs e)
{
//Save the uploaded Excel file.
string filePath = Server.MapPath(«~/Files/») + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filePath);
//Open the Excel file using ClosedXML.
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())
{
dt.Rows[dt.Rows.Count — 1][i] = cell.Value.ToString();
i++;
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
VB.Net
Protected Sub ImportExcel(sender As Object, e As EventArgs)
‘Save the uploaded Excel file.
Dim filePath As String = Server.MapPath(«~/Files/») + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(filePath)
‘Open the Excel file using ClosedXML.
Using workBook As New XLWorkbook(filePath)
‘Read the first Sheet from Excel file.
Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
‘Create a new DataTable.
Dim dt As New DataTable()
‘Loop through the Worksheet rows.
Dim firstRow As Boolean = True
For Each row As IXLRow In workSheet.Rows()
‘Use the first row to add columns to DataTable.
If firstRow Then
For Each cell As IXLCell In row.Cells()
dt.Columns.Add(cell.Value.ToString())
Next
firstRow = False
Else
‘Add rows to DataTable.
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As IXLCell In row.Cells()
dt.Rows(dt.Rows.Count — 1)(i) = cell.Value.ToString()
i += 1
Next
End If
GridView1.DataSource = dt
GridView1.DataBind()
Next
End Using
End Sub
Screenshots
The Excel File
GridView displaying Excel data
Downloads
Содержание
- Примеры шаблонов
- Простой шаблон
- Сортировка списка
- Итоги по столбцам
- Опции списка и колонок
- Сложный диапазон
- Группировка списка
- Схлопнутые группы
- Заголовки над данными
- Объединение заголовков (вариант 1)
- Объединение заголовков (вариант 2)
- Вложенные группы
- Отключенное схлопывание групп
- Размещение заголовка группы
- Формулы в строке группы
- Группировка с заголовками
- Вложенные области
- Вложенные области с итогами
- Вложенные области с внутренней сортировкой
- Быстрый старт
- Установка ClosedXML.Report через NuGet
- Особенности
- Как использовать?
- C# Excel
- Advertisements Excel xlsx
- ClosedXML
- Advertisements C# Excel simple example
- C# Excel cell
- Advertisements C# read Excel file
- C# Excel apply style
- Advertisements C# Excel ranges
- C# Excel merge cells
- Advertisements C# Excel sorting
- Advertisements C# Excel CellsUsed
- Advertisements C# Excel expression evaluation
- Advertisements C# Excel formula
- Report examples
- Simple Template
- Sorting the Collection
- Totals
- Range and Column Options
- Complex Range
- Grouping
- Collapsed Groups
- Summary Above the Data
- Merged Cells in Groups (option 1)
- Merged Cells in Groups (option 2)
- Nested Groups
- Disable Groups Collapsing
- Specifying the Location of Group Captions
- Formulas in Group Line
- Groups with Captions
- Nested Ranges
- Nested Ranges with Subtotals
- Nested Ranges with Sorting
Примеры шаблонов
Простой шаблон
Вы можете применять к ячейкам любое форматирование, включая условные форматы.
Сортировка списка
Вы можете отсортировать список по столбцам. Просто укажите тэг > в ячейках опций соответствующих столбцов. Чтобы отсортировать в порядке убывания, добавьте опцию «Desc» в параметр тэга сортировки ( > ).
Подробнее смотрите раздел Сортировка данных
Итоги по столбцам
Вы можете получить итоговые значения для столбца диапазона, указав соответствующий параметр в ячейке опции столбца. В этом примере мы использовали тэг > в строке опций списка для колонки Amount paid.
Подробнее смотрите раздел Подитоги по столбцам.
Опции списка и колонок
Помимо передачи данных из набора данных в диапазон, ClosedXML.Report может сортировать диапазон, составлять итоговые значения, группировать диапазон и так далее. Эти действия выполняются ClosedXML.Report, если он находит тэги диапазона и тэги столбца в соответствующих ячейках.
Подробнее смотрите раздел Плоские таблицы
В этом примере мы выравняли колонки по содержимому, добавили фильтры в заголовок таблицы, заменили формулы на значения и защитили колонку Amount paid от изменения. Для этого мы использовали тэги: , > , > и .
Сложный диапазон
ClosedXML.Report способен использовать многострочные шаблоны для строки таблицы. Вы можете произвольным образом форматировать ячейки, объединять их, использовать условное форматирование, формулы Excel.
Подробнее смотрите раздел Плоские таблицы
Группировка списка
С тэгом > могут использоваться все тэги суммирования. Укажите опцию > в ячейках опций столбцов, где вы хотите получить промежуточные итоги.
Подробнее смотрите раздел Группировка
Схлопнутые группы
Используйте параметр collapse тэга group ( > ), если вы хотите быстро отображать только строки, которые содержат сводки или заголовки для разделов вашего листа.
Подробнее смотрите раздел Группировка
Заголовки над данными
ClosedXML.Report реализует тэг SUMMARYABOVE. Он помещает строку итогов над сгруппированными данными.
Подробнее смотрите раздел Группировка
Объединение заголовков (вариант 1)
Тэг > позволяет объединять ячейки в сгруппированном столбце. Эта функция доступна с помощью параметра ‘mergelabels’ тэга > .
Подробнее смотрите раздел Группировка
Объединение заголовков (вариант 2)
Тэг > позволяет группировать данные без вставки строки заголовка группы. Эта функция доступна с помощью параметра ‘MergeLabels=Merge2’ тэга > . Ячейки, содержащие сгруппированные данные, объединяются, а ячейка результата содержит сгруппированное значение.
Подробнее смотрите раздел Группировка
Вложенные группы
Списки могут быть сгруппированы с любым уровнем вложенности.
Подробнее смотрите раздел Группировка
Отключенное схлопывание групп
Используйте параметр disableoutline тэга > чтобы отключить схлопывание групп. В этом примере диапазон группируется по колонке Company и Payment method. Схлопывание групп столбца Payment method отключено.
Подробнее смотрите раздел Группировка
Размещение заголовка группы
Тэг > позволяет размещать заголовок группы в любой столбец сгруппированного диапазона с помощью параметра PLACETOCOLUMN=n , где n — номер столбца в диапазоне (начиная с 1). Так же ClosedXML.Report содержит тэг > , позволяющий удалить столбец или строку. В примере группировка выполняется по колонке Company с использованием параметра mergelabels . Заголовок группы столбца Company помещается во второй столбец диапазона (параметр PLACETOCOLUMN=2 ). Затем удаляется столбец Company.
Подробнее смотрите раздел Группировка
Формулы в строке группы
ClosedXML.Report сохраняет весь текст в ячейках строки параметров, если он не распознаёт его тэгом. Используйте эту возможность для размещения формул Excel в строке параметров. В примере выполняется группировка по полю Company и Payment method. В ячейке опций столбца Amount Paid есть формула Excel.
Подробнее смотрите раздел Группировка
Группировка с заголовками
Вы можете настроить отображение заголовка группы с помощью параметра WITHHEADER тэга > . В этом случае заголовок группы помещается над сгруппированными данными и сводной строкой ниже данных. Параметр SUMMARYABOVE не влияет на это размещение.
Подробнее смотрите раздел Группировка
Вложенные области
Вы можете вложить диапазон в другой диапазон, отражающий таким образом подчиненные отношения ваших данных. В примере диапазон Items вложен в Orders, а последний — в Customers. Все три диапазона имеют собственную строку параметров плюс ту же левую границу и ту же ширину.
Вложенные области с итогами
Вы можете использовать тэги суммирования на каждом уровне вложенности в отчете master-detail. В примере тэг > в ячейке I9 будет суммировать ячейки « по заказу, в то время как тот же тэг в ячейке I10 суммирует эти ячейки по поставщику.
Вложенные области с внутренней сортировкой
Вы можете использовать тэг > в самом внутреннем диапазоне.
Источник
Быстрый старт
ClosedXML.Report — это инструмент для создания отчетов и анализа данных в приложениях .NET с использованием Microsoft Excel. Это .NET-библиотека для создания отчетов Microsoft Excel, не требующая установки Excel на машине, на которой выполняется код. С помощью ClosedXML.Report вы можете легко экспортировать любые данные из ваших классов .NET в Excel с помощью XLSX-шаблона.
Установка ClosedXML.Report через NuGet
Если вы хотите включить ClosedXML.Report в свой проект, вы можете установить его непосредственно из NuGet
Чтобы установить ClosedXML.Report, запустите следующую команду в консоли диспетчера пакетов
или если у вас подписанная сборка то:
Особенности
- Копирование форматирования ячеек
- Копирование условного форматирования
- Вертикальные и горизонтальные таблицы
- Возможность применения формул Excel
- Использование динамически рассчитываемых формул с синтаксисом C# и Linq
- Операции с табличными данными: сортировка, группировка, общие функции.
- Сводные таблицы
- Контроль за построением отчёта с помощью управляющих тэгов
- Вложенные области (master-detail)
Как использовать?
Чтобы создать отчет, вы должны сначала создать шаблон отчета. Вы можете применить любое форматирование к ячейкам рабочей книги, вставить изображения и изменить любой из параметров самой книги. В этом примере мы отключили отображение нулевых значений и спрятали линии сетки. ClosedXML.Report сохранит все изменения в шаблоне.
Template
Code
Result
Для получения дополнительной информации см. Документация и Тесты
Источник
C# Excel
last modified January 4, 2023
C# Excel tutorial shows how to work with Excel files in C# with ClosedXML library.
Advertisements Excel xlsx
In this article we work with xlsx files. The xlsx is a file extension for an open XML spreadsheet file format used by Microsoft Excel. The xlsm files support macros. The xltm are macro-enabled template files. The xls format is a proprietary binary format while xlsx is based on Office Open XML format.
ClosedXML
ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files.
We add the package to the project.
Advertisements C# Excel simple example
In the first example, we create a new xlsx file with ClosedXML.
We create a new Excel file and write a value to a cell.
A new XLWorkbook is created.
We add a new sheet to the workbook.
A value is written to cell with A1 address.
The workbook is saved with the SaveAs method.
C# Excel cell
A cell is an intersection of a row and a column. Each cell has a unique address made up of its column letter and row number. For instance, the first cell located in the upper-left corner of a sheet has address A1.
In the example, we work with cells.
The FirstCell retrieves the reference to the first cell in the sheet. We set its value to 150 using the Value property.
Another way to reference a cell is used an overloaded Cell method, which takes the row and column numbers as parameters.
Here, we get a cell via its address (A6), and use the SetValue method to write a string to the cell. The SetActive method makes the cell active.
We adjust the width of the second column to the contents; the Hello there! value is fully visible.
Advertisements C# read Excel file
In the next example, we read from the previously created Excel file.
The example reads a value from a cell.
We open a workbook.
We navigate to the first sheet.
Using the GetValue method, we read a value from cell A1.
C# Excel apply style
A style can be applied via the Style property.
In the example, apply styles to cells.
We set the widths of A and B columns.
In the A3 cell, we center the text horizontally and vertically and choose a cursive font.
We set a thin border for B2 cell.
In cell B5, the font color is set to red.
Advertisements C# Excel ranges
A range is a group of one or more cells. A range address is specified with a upper-left cell address and a lower-bottom address, separated by a colon.
A single range is created with Range ; multiple ranges are created with Ranges .
In the example, we work with ranges.
We change the background colour for D2:E2 range.
Here, we change the background colour for two ranges.
In each cell of the C10:E15 range, we set a random value. We use the Cells method to get the cells of the range.
We adjust the width of the column C to the written contents so that the whole value is visible.
C# Excel merge cells
To merge cells, we use the Merge method.
We merge four cells into one.
We call the Merge method on a range of four cells; A1 through B2.
Advertisements C# Excel sorting
A column can be sorted with the Sort method.
We add fifteen random values to the A column. Then we sort the column with Sort .
Advertisements C# Excel CellsUsed
The CellsUsed method returns a collection of cells that have a value.
In the example, we write twelve words to two columns of a sheet.
We define a range that is larger than the cells we have written to. With the help of the CellsUsed method, we get all non-empty cells. The Count method counts the number of non-empty cells.
Here we filter all words that have three latin letters.
Advertisements C# Excel expression evaluation
With Evaluate , we can evaluate an expression.
In the example, we have values in the A column. We evaluate SUM and MAX expressions on the values.
Advertisements C# Excel formula
With the FormulaA1 property, we can insert a formula in the cell.
In the example, we insert the SUM formula in the cell below the values.
In this article, we have demonstrated how to work with Excel files with C# using ClosedXMLlibrary.
Источник
Report examples
Simple Template
You can apply to cells any formatting including conditional formats.
Sorting the Collection
You can sort the collection by columns. Specify the tag > in the options row of the corresponding columns. Add option desc to the tag if you wish the list to be sorted in the descending order ( > ).
For more details look to the Sorting
Totals
You can get the totals for the column in the ranges by specifying the tag in the options row of the corresponding column. In the example above we used tag > in the column Amount paid.
For more details look to the Totals in a Column.
Range and Column Options
Besides specifying the data for the range ClosedXML.Report allows you to sort the data in the range, calculate totals, group values, etc. ClosedXML.Report performs these actions if it founds the range or column tags in the service row of the range.
For more details look to the Flat Tables
In the example above example we applied auto filters, specified that columns must be resized to fit contents, replaced Excel formulas with the static text and protected the “Amount paid” against the modification. For this, we used tags , > , > and .
Complex Range
ClosedXML.Report can use multi-row templates for the table rows. You may apply any format you wish to the cells, merge them, use conditional formats, Excel formulas.
For more details look to the Flat Tables
Grouping
The > tag may be used along with any of the aggregating tags. Put the tag > into the service row of those columns which you wish to use for aggregation.
For more details look to the Grouping
Collapsed Groups
Use the parameter collapse of the group tag ( > ) if you want to display only those rows that contain totals or captions of data sections.
For more details look to the Grouping
Summary Above the Data
ClosedXML.Report implements the tag summaryabove that put the summary row above the grouped rows.
For more details look to the Grouping
Merged Cells in Groups (option 1)
The > tag has options making it possible merge cells in the grouped column. To achieve this specify the parameter mergelabels in the group tag ( > ).
For more details look to the Grouping
Merged Cells in Groups (option 2)
Tag > allows to group cells without adding the group title. This function may be enabled by using parameter MergeLabels=Merge2 in the group tag ( > ). Cells containing the grouped data are merged and filled with the group caption.
For more details look to the Grouping
Nested Groups
Ranges may be nested with no limitation on the depth of nesting.
For more details look to the Grouping
Disable Groups Collapsing
Use the option disableoutline of the group tag ( > ) to prevent them from collapsing. In the example above the range is grouped by both Company and Payment method columns. Collapsing of groups for the Payment method column is disabled.
For more details look to the Grouping
Specifying the Location of Group Captions
The > tag has a possibility to put the group caption in any column of the grouped range by using the parameter PLACETOCOLUMN=n where n defines the column number in the range. (starting from 1). Besides, ClosedXML.Report supports the > tag that aims to specify columns to delete. In the example above the Company column is grouped with the option mergelabels . The group caption is placed to the second column ( PLACETOCOLUMN=2 ). Finally, the Company column is removed.
For more details look to the Grouping
Formulas in Group Line
ClosedXML.Report saves the full text of cells in the service row, except tags. You can use this feature to specify Excel formulas in group captions. In the example above there is grouping by columns Company and Payment method. The Amount Paid column contains an Excel formula in the service row.
For more details look to the Grouping
Groups with Captions
You can configure the appearance of the group caption by using the WITHHEADER parameter of the > tag. With this, the group caption is placed over the grouped rows. The SUMMARYABOVE does not change this behavior.
For more details look to the Grouping
Nested Ranges
You can place one ranges inside the others in order to reflect the parent-child relation between entities. In the example above the Items range is nested into the Orders range which, in turn, is nested to the Customers range. Each of three ranges has its own header, and all have the same left and right boundary.
Nested Ranges with Subtotals
You may use aggregation tags at any level of your master-detail report. In the example above the > tag in the I9 cell will summarize columns « in the scope of an order, while the same tag in the I10 cell will summarize all the data for each Customer.
Nested Ranges with Sorting
You can use the > for the nested ranges as well.
Источник
In this article we will discuss how to Read or Import Excel file data into C# DataTable. For this example we will be using the ClosedXML .NET library.
What is ClosedXML?
ClosedXML is .NET library which is used for manipulating and writing the 2007+ Excel files. The ClosedXML library is available for free on GitHub.
Read and Import Excel Data to DataTable using ClosedXML
I will be using a windows forms application for this example. On button click the excel file data will be read, imported to DataTable and will bind the same DataTable to GridView. I have a sample excel file with dummy data at location “D:ClosedXML”
Add ClosedXML NuGet Package
Got to Tools > NuGet Package Manager > Package Manager Console and run the below command
Install-Package ClosedXML -Version 0.96.0
C# Code
Import below namespace
using ClosedXML.Excel;
private void btnImport_Click(object sender, EventArgs e) { using (XLWorkbook workBook = new XLWorkbook(@"D:ClosedXMLSampleData.xlsx")) { //Read the first Sheet from Excel file. //Here SalesOrders is the Work Sheet Name. IXLWorksheet workSheet = workBook.Worksheet("SalesOrders"); //Creating a new datatable DataTable dt = new DataTable(); //Looping through the Worksheet rows. bool firstRow = true; foreach (IXLRow row in workSheet.Rows()) { //Using the first row to add columns names to DataTable. if (firstRow) { foreach (IXLCell cell in row.Cells()) { dt.Columns.Add(cell.Value.ToString()); } firstRow = false; } else { //Adding rows to DataTable. dt.Rows.Add(); int i = 0; foreach (IXLCell cell in row.Cells()) { dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString(); i++; } } } //Bind DataTable to DataGridView dgvData.DataSource = dt; } }Demo
Wrapping Up
I hope, this blog post has helped you in learning and understanding how to read or import excel file data in C# datatable.
Don’t forget to check out: Generate QR Code in Asp.net Core with 4 Simple Steps
Thank You.
FAQs
How to use ClosedXML in Excel in C#? ›
AdvertisementsC# Excel formula
With the FormulaA1 property, we can insert a formula in the cell. using ClosedXML. Excel; using var wbook = new XLWorkbook("data. xlsx"); var ws = wbook.
xlsx is based on Office OpenXML format. ClosedXML is a . NET library for report generation in Microsoft Excel without requiring Excel to be installed on the machine that's running the code.
How to export DataTable to Excel in C# using ClosedXML? ›- public DataTable getData() {
- //Creating DataTable.
- DataTable dt = new DataTable();
- //Setiing Table Name.
- dt.TableName = "EmployeeData";
- //Add Columns.
- dt.Columns.Add("ID", typeof(int));
- dt.Columns.Add("Name", typeof(string));
ClosedXML is a . NET library for reading, manipulating and writing Excel 2007+ (. xlsx, . xlsm) files.
How to read data from Excel file in C#? ›- Download the C# Library to read Excel files.
- Load and read an Excel file (workbook)
- Create an Excel workbook in CSV or XLSX.
- Edit cell values in a range of cells.
- Validate spreadsheet data.
- Export data using Entity Framework.
First of all, open a new Excel Sheet and enter the information that you want to add. Now start Visual Studio and create a Web Application. Right-click on this application and add the Excel File to this application. Now you need to add a Drop Down List, a List Item, a Label, and a Grid View to your application.
Do you need Excel for coding? ›You don't need to excel at math to learn to code
And it was more than enough to get me into the programming world. Even today, I don't use anything more complex than powers or square roots. If you have ever programmed any line of code, you have hopefully realized it has almost nothing to do with math.
Excel works primarily with two types of XML files: XML data files (. xml), which contain the custom tags and structured data.
What is the difference between Excel and XML file? ›XML is text based while XLS is a binary format that cannot be opened with text editors. If you try to open XLSX with a text editor, you will get garbage. That is because the XML files are archived using the ZIP format.
How do I install ClosedXML in Excel? ›- Click on the Tools Option on the top menu bar.
- Expand the option NuGet Package Manager.
- Click Package Manager Console.
- You will see the Console open at the bottom of the main Window of Visual Studio.
- At the PM> command line, type:
- Install-Package ClosedXML.
How to export collection of objects to Excel using ClosedXML in C#? ›
Install ClosedXML using NuGet Package Manager. Using ClosedXML you can set specific cell values, you can insert data from DataTable or from some other collection. Here, I am going to export a collection of student data to excel. When the Import Button is clicked, ExportToExcel Action Method will execute.
How to convert Excel to DataTable in C#? ›- Use Workbook class to load the Excel file.
- Get the worksheet you want to export in a Worksheet object.
- Use Worksheet. Cells. ExportDataTable(int, int, int, int, bool) method to export data to a DataTable object.
- Use DataTable as the data source.
Macros – ClosedXml doesn't support macros as its base library OpenXml also doesn't support it. Embedding – We cannot embed any file into Excel using ClosedXml, no APIs built for that, so some features of OpenXml still need to be implemented. Charts – No functionality related to charting is present.
What is ClosedXML? ›ClosedXML is a popular open-source library for library for reading, manipulating and writing Excel 2007+ (. xlsx, . xlsm) files in . NET applications. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.
What is the default font for ClosedXML? ›Instance used by ClosedXML by default. It uses “Microsoft Sans Serif” as a fallback font and uses any system font available to SixLabor.
How to validate excel sheet columns in C#? ›- private bool ValidateExcelSchema(string schema)
- {
- //XmlSchemaSet schemas;
- schemas = new XmlSchemaSet();
- settings = new XmlReaderSettings();
- settings.ProhibitDtd = false;
- settings.CheckCharacters = true;
- settings.ValidationType = ValidationType.DTD;
- Open Visual studio -> New Project -> Console App. ...
- Step 2 - Add open XML package. ...
- I have created one method, in which we open and read the data from excel, and load the data in DataTable.
- My File Path:
- File Data.
- Execute code and check data is loaded in Datatable.
- Using NuGet package manager, add Aspose.Cells for .NET to the project.
- Add a reference to Aspose.Cells namespace.
- Load the workbook containing data and formula.
- Get the references of the cells whose data and formulas are to be updated.
- Update data and formula in the target cells.
- private void InsertExcelRecords(string FilePath)
- {
- ExcelConn(FilePath);
- Query = string. ...
- OleDbCommand Ecom = new OleDbCommand(Query, Econ);
- Econ. ...
- DataSet ds=new DataSet();
- OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
In C#, StreamReader class is used to deal with the files. It opens, reads and helps in performing other functions to different types of files. We can also perform different operations on a CSV file while using this class. OpenRead() method is used to open a CSV file and ReadLine() method is used to read its contents.
How to convert Excel to text file using C# net? ›
- Step 1: Create an instance of Spire. Xls. Document.
- Step 2: Load the file base on a specified file name.
- Step 3: Get the first worksheet of the Worksheet.
- Step 4: Save the document object to a Txt file.
- Effect Screenshot:
- Sample code:
Although it takes only a few hours to learn Excel's basic features and become familiar with its interface, most learners study this application for 18-20 hours to fully understand its many capabilities.
Can I learn Excel in a week? ›It's impossible to learn Excel in a day or a week, but if you set your mind to understanding individual processes one by one, you'll soon find that you have a working knowledge of the software. Make your way through these techniques, and it won't be long before you're comfortable with the fundamentals of Excel.
Is Excel easier than Python? ›Python is easier to learn and master, unlike Excel, which includes a personalized language known as VBA that is complex to master and execute. Transitioning from Excel to Python enables users to enjoy various benefits, such as an open-source coding platform, many volunteer contributors, and free libraries.
How to read XML file in C#? ›- Copy the Books. ...
- Open Visual Studio.
- Create a new Visual C# Console Application. ...
- Specify the using directive on the System. ...
- Create an instance of the XmlTextReader class, and specify the URL. ...
- Read through the XML. ...
- Inspect the nodes. ...
- Inspect the attributes.
The additional “x” in Excel file extensions stands for XML. XML is Extensible Markup Language, which is a markup language useful for data, databases, and data-related applications. The file type . xlsx is a combination of XML architecture and ZIP compression for size reduction.
What is the difference between CSV and XML? ›XML is a technology that you may use to connect systems and applications. CSV, on the other hand, may be used to store enormous amounts of data that must always be concatenated, such as log files, as well as for data streaming.
What is the advantage of XML over Excel? ›- XML is a mark-up language while XLS is a file format for Microsoft Excel.
- XML is an open format while XLS is a proprietary format.
- XML is user-readable while XLS is not.
- XLS is being replaced with a newer format based on XML.
However, XML files are very handy for storing data without the overhead of a database. Using XML files to cache often-used, but seldom changed data such as US state codes, employee types and other validation tables can avoid network roundtrips and speed up your application.
Why does Microsoft use XML? ›Better integration and interoperability of business data Using Open XML Formats as the data interoperability framework for the Office set of products means that documents, worksheets, presentations, and forms can be saved in an XML file format that is freely available for anyone to use and to license, royalty free.
What is the function of closed XML? ›
ClosedXML allows you to create Excel files without the Excel application. The typical example is creating Excel reports on a web server. These docs are very much a work in progress.
Is ClosedXML open source? ›ClosedXML is an open source library created based on OpenXmlSdk. It's more user friendly. ClosedXML is a . NET library for reading, manipulating and writing Excel 2007+ (.
How to export huge data from Datatable to Excel in C# MVC? ›- Create a table in the database with the following code: ...
- Enter records into the table.
- Create a Stored Procedure to select the data using SQL Paging with the following code: ...
- We can also execute this procedure to show the results. ...
- Add an Excel file to the Solution.
- using DocumentFormat. OpenXml;
- using DocumentFormat. OpenXml. Packaging;
- using DocumentFormat. OpenXml. Spreadsheet;
- using X14 = DocumentFormat. OpenXml. Office2010. Excel;
- using X15 = DocumentFormat. OpenXml. Office2013. Excel;
- Create or use an existing C# or VB.NET project.
- Add Json.NET Nuget package to your C# or VB.NET project.
- Add GemBox. Spreadsheet Nuget package to your C# or VB.NET project.
- Execute the C#/VB.NET code below.
- Create your form for the upload.
- Create your DB Structure.
- Create your connection string to communicate with the DB. ...
- Create the Form structure to handle the excel reading as well sending the data to the DAO class for the bulk Upload. ...
- Create your DAO class to save the data into the DB.
- OpenXML 2.0 (free library from MS) can be used to read/modify the content of an . ...
- some (commercial) 3rd-party libraries come with grid controls allowing you to do much more with excel files in your application (be it Winforms/WPF/ASP.NET...)
- Problem. ...
- Solution. ...
- using Excel = Microsoft. ...
- string fileName = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + ...
- xlsApp = new Excel.Application(); ...
- using SqlConnection conn = new SqlConnection(connectionString);
| The OOXML Document icon, as appears on the Microsoft OneDrive web service | |
|---|---|
| Filename extension | .docx, .docm |
| Type of format | Document file format |
| Extended from | XML, DOC, WordProcessingML |
| Standard | ECMA-376, ISO/IEC 29500 |
XQUERY is a facility used to perform queries off XML data (whether it's stored in SQL Server, a free-standing file, XML stream, etc...) OPENXML allows for the opening of a XML file and use it as a data set in SQL Server.
What is the difference between Openxml and EPPlus? ›
EPPlus is a . net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). The Open XML SDK provides open-source libraries for working with Open XML Documents (DOCX, XLSX, and PPTX).
How to merge cells in Excel using ClosedXML in C#? ›To merge cells, we use the Merge method. using ClosedXML. Excel; using var wbook = new XLWorkbook(); var ws = wbook. Worksheets.
How to use Npoi in C#? ›- Install C# library to read Excel with NPOI.
- Instantiate XSSFWorkbook object to store Excel file.
- Get specific worksheet with GetSheetAt method.
- Access each row by passing row index to GetRow method.
- Access each cell in the row with GetCell method.
We use monospace fonts to keep code aligned. Courier is just one of many monospace fonts. They are also called fixed-width fonts.
What is default font size for coding? ›The default text size in browsers is 16px. So, the default size of 1em is 16px.
What is the most default font? ›Helvetica remains the world's most popular font. It's best known for signage and when designing business forms, like invoices or receipts. It's effortless to read because its large x-height makes it look larger than it is.
How to insert data into Excel using C#? ›- Download C# library to insert new row and column in Excel.
- Use InsertRow and InsertRows methods to insert new rows.
- Use InsertColumn and InsertColumns methods to insert new columns.
- Add data into the new row and column.
- Export the edited Excel file to various types.
ClosedXML is a . NET library for reading, manipulating and writing Excel 2007+ (. xlsx, . xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.
How to read data from Excel in C# using OpenXML? ›- Open Visual studio -> New Project -> Console App. ...
- Step 2 - Add open XML package. ...
- I have created one method, in which we open and read the data from excel, and load the data in DataTable.
- My File Path:
- File Data.
- Execute code and check data is loaded in Datatable.
- using DocumentFormat. OpenXml;
- using DocumentFormat. OpenXml. Packaging;
- using DocumentFormat. OpenXml. Spreadsheet;
- using X14 = DocumentFormat. OpenXml. Office2010. Excel;
- using X15 = DocumentFormat. OpenXml. Office2013. Excel;
How to import Excel database in Python? ›
- Step 1: Capture the file path. First, capture the full path where the Excel file is stored on your computer. ...
- Step 2: Apply the Python code. ...
- Step 3: Run the Python code to import the Excel file.
The Excel API available to the user is the same as in VBA, so anything you can do with VBA, you can do with C#. In addition, a set of handy extension methods is also available, like the WriteTable() method shown in the example above.
What are the advantages of ClosedXML? ›With ClosedXml, you can work with Excel without having to open it, this helps in backed automation, where you don't have to deal with open excel instance which can be problematic.
How to read a CSV file from Excel in C#? ›- Download and install the C# CSV reading library.
- Create a C# or VB project.
- Add the code example from this page to your project.
- In the code, specify the path to the CSV, and the output name & file format.
- Run the C# project to view the document.
- Copy the Books. ...
- Open Visual Studio.
- Create a new Visual C# Console Application. ...
- Specify the using directive on the System. ...
- Create an instance of the XmlTextReader class, and specify the URL. ...
- Read through the XML. ...
- Inspect the nodes. ...
- Inspect the attributes.
Look for GSpread.NET. It's also an OpenSource project and it doesn't require Office installed. You can work with Google Spreadsheets by using API from Microsoft Excel. If you want to re-use the old code to get access to Google Spreadsheets, GSpread.NET is the best way.