4

Does anyone know any simple NuGet package or other way to export data from database to Excel? I have a few tables and each one I'd like to export to other WorkSheet, but in same Excel file.

Also later I'd like to import data from file Excel to database. For both operations may be different library.

I tried already this But it doesn't work.


@EDIT - solved

Here are some useful links. I hope that someday would help someone :)

http://epplus.codeplex.com/

http://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus

7
  • 1
    Sad to see down vote on this question, this is a valid question. Let me up-vote so that it helps others in future. Commented Jun 17, 2016 at 10:45
  • @AbhimanyuKumarVatsa, This is not a valid question. It off-topic for SO and I suggest you read the help files to understand what is acceptable here. Commented Jun 17, 2016 at 10:52
  • 1
    @StephenMuecke i see two questions, but not off-topic. I feel, we should edit newbies post not just down-vote. Or, add some comment to give him chance to edit. I still think, a true community guy suggests, not ignores. Commented Jun 17, 2016 at 10:59
  • @AbhimanyuKumarVatsa. I repeat. The question is off-topic and will be closed by the community. (it cannot possibly be edited to make it on-topic) Commented Jun 17, 2016 at 11:02
  • 1
    Why is it off-topic? I think this post will be really useful for some people in feature. Commented Jun 17, 2016 at 11:04

2 Answers 2

2

This Code get from EPPlus and also helpful for your question ::

private void ExcelExport(DataTable table)
        {
            using (ExcelPackage packge = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = packge.Workbook.Worksheets.Add("Demo");

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(table, true);

                //Format the header for column 1-3
                using (ExcelRange range = ws.Cells["A1:C1"])
                {
                    range.Style.Font.Bold = true;
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
                    range.Style.Font.Color.SetColor(Color.White);
                }

               //Example how to Format Column 1 as numeric 
                using (ExcelRange col = ws.Cells[2, 1, 2 + table.Rows.Count, 1])
                {
                    col.Style.Numberformat.Format = "#,##0.00";
                    col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }

                //Write it back to the client
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=ExcelExport.xlsx");
                Response.BinaryWrite(packge.GetAsByteArray());
            }
        }
Sign up to request clarification or add additional context in comments.

2 Comments

Dont link to external resources but post the code/answer here. External links often die.
I found this lib just a few seconds before you. However I will mark as answer your post. Because of nice explanation of code. Btw. you forgot to write - this is EPPlus lib. So edit your answer :)
0

There are several options as paid, free or client side conversion solution, server side conversion solutions.

You need to take a decision, if you just want to export to excel client side without effecting server. Here is demo (running) solution for you.

http://www.itorian.com/2015/05/export-table-data-into-excel-file.html

Click on result tab and then click on button "Export table data into excel". Mark this as answer if this helps.

Edit

As you mentioned, you need to export tables into different excel pages (but in same file), this will work for you http://www.aspsnippets.com/Articles/Export-DataSet-DataTables-to-multiple-Excel-Sheets-Worksheets-in-ASPNet-using-C-and-VBNet.aspx

2 Comments

As I said, I need data from database not from view (DOM). And I prefer free option.
yes this is also easy, just go through this post and try demo before you look into code aspsnippets.com/Articles/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.