In our work, sometimes we need to save data from SQL Server into an Excel file, maybe most people immediately think of Microsoft.Office.Introp.Excel Object, but it has the disadvantage that you need to install Microsoft Excel or Office on the machine. In this article we will see how to save data into the Excel and convert the Excel to HTML in a pretty easy method to use two free libraries, which doesn't need to install Microsoft Excel or Office.

To demonstrate how to save data from SQL to Excel file, I have created a sample SQL table and here is how it looks like.

SQL table

Now, the next step is to read the SQL data into a Datatable.

  1. private static DataTable exportedData()
  2. {
  3. DataTable dt = new DataTable();
  4. using (SqlConnection connection = new SqlConnection("Data Source=MICROSOF- 8A1237\\SQLEXPRESS;Initial Catalog=test;Integrated Security=SSPI;"))
  5. {
  6. using (SqlCommand command = new SqlCommand("select * from PivotTable"))
  7. {
  8. using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, connection))
  9. {
  10. dataAdapter.Fill(dt);
  11. }
  12. }
  14. }
  15. return dt;
  16. }
Next is to export the Datatable to the Excel file with the third-party library Epplus; the namespace to be used:
  1. using OfficeOpenXml;
The following is the actual code:
  1. DataTable dt = exportedData();
  2. ExcelPackage elPackage = new ExcelPackage();
  3. ExcelWorksheet worksheet = elPackage.Workbook.Worksheets.Add("SqlData");
  4. worksheet.Cells["A1"].LoadFromDataTable(dt, true);
As you can see, it is pretty easy that you don't need to create a new Excel and there is only LoadFromDataTable method called.

Next is to convert the Excel file to HTML, Epplus doesn't support the feature, so you need to use the another component: Spire.xls, which seems to be a community sponsored version of Epplus.

The following is the namespace to be used:
  1. using Spire.Xls;
The following is the actual code:
  1. Workbook book = new Workbook();
  2. book.LoadFromStream(ms,ExcelVersion.Version2010);
  3. Worksheet sheet = book.Worksheets["SqlData"];
  4. sheet.SaveToHtml("result.html");
It is also quite easy. To learn more about the component that also supports conversion of the Excel file to a PDF, image and so on.

The following is the entire code.
  1. class Program
  2. {
  3. static void Main(string[] args)
  4. {
  5. DataTable dt = exportedData();
  6. ExcelPackage elPackage = new ExcelPackage();
  7. ExcelWorksheet worksheet = elPackage.Workbook.Worksheets.Add("SqlData");
  8. worksheet.Cells["A1"].LoadFromDataTable(dt, true);
  9. using (MemoryStream ms = new MemoryStream())
  10. {
  11. elPackage.SaveAs(ms);
  12. Workbook book = new Workbook();
  13. book.LoadFromStream(ms, ExcelVersion.Version2010);
  14. Worksheet sheet = book.Worksheets["SqlData"];
  15. sheet.SaveToHtml("result.html");
  16. }
  17. System.Diagnostics.Process.Start("result.html");
  18. }
  19. private static DataTable exportedData()
  20. {
  21. DataTable dt = new DataTable();
  22. using (SqlConnection connection = new SqlConnection("Data Source=MICROSOF- 8A1237\\SQLEXPRESS;Initial Catalog=test;Integrated Sec urity=SSPI;"))
  23. {
  24. using (SqlCommand command = new SqlCommand("select * from PivotTable"))
  25. {
  26. using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, connection))
  27. {
  28. dataAdapter.Fill(dt);
  29. }
  30. }
  31. }
  32. return dt;
  33. }
  34. }