Active Query Builder support area

How to export query results to Excel?

Last modified:

Dashboard Popular Articles

With Active Query Builder you get the plain SQL query text and execute SQL queries in a regular way. Thus, you can use any third-party library to Export data to any appropriate format. 

For exporting data to MS Excel, we found it convenient for us to use the NPOI library. It is free, doesn't require MS Excel installed, doesn't use OLE Automation, and works under Linux with .NET Core just fine. The following code generates an ".xlsx" file from a DataTable object.

private static ICellStyle _cellStyleDateTime;

public static bool ExportToExcel(DataTable dataTable, string pathToSave)
{
	try
	{
		if (dataTable == null || string.IsNullOrEmpty(pathToSave)) return false;

		var workbook = new XSSFWorkbook();
		var sheet = (XSSFSheet)workbook.CreateSheet("Result");

		var row = sheet.CreateRow(0);

		foreach (DataColumn column in dataTable.Columns)
		{
			var cell = row.CreateCell(dataTable.Columns.IndexOf(column));
			cell.SetCellValue(column.ColumnName);
		}

		for (var i = 0; i < dataTable.Rows.Count; i++)
		{
			var tableRow = dataTable.Rows[i];

			var currentRow = sheet.CreateRow(i + 1);

			var values = tableRow.ItemArray.ToList();

			foreach (var item in values)
			{
				var currentCell = currentRow.CreateCell(values.IndexOf(item));

				DateTime dateValue;
				double doubleValue;
				int intValue;

				if (DateTime.TryParse(item.ToString(), out dateValue))
				{
					if (_cellStyleDateTime == null)
					{
						var format = workbook.CreateDataFormat();
						_cellStyleDateTime = workbook.CreateCellStyle();
						_cellStyleDateTime.DataFormat = format.GetFormat("dd/MM/yyyy");
					}

					currentCell.SetCellValue(dateValue);
					currentCell.CellStyle = _cellStyleDateTime;
				}
				else if (double.TryParse(item.ToString(), out doubleValue))
					currentCell.SetCellValue(doubleValue);
				else if (int.TryParse(item.ToString(), out intValue))
					currentCell.SetCellValue(intValue);
				else if (item is byte[])
					// depends on the type of data
				else
					currentCell.SetCellValue(item.ToString());
			}
		}

		using (var fs = new FileStream(pathToSave, FileMode.Create, FileAccess.Write))
			workbook.Write(fs);

		return true;
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message, "Export to Excel", MessageBoxButtons.OK);
		return false;
	}
}

 


Is this article helpful for you?