How to import datas to excel sheet

Question

I have datatable . I need to import those datatable values to Excel Template.How to achieve this

Answer 1

There are a number of options

1. Write the data out as an CSV file
2. Write the data out as an HTML table
3. Use Automation to manipulate a running Excel instance
4. Use OleDB Driver to create a Excel file. Another link from Microsoft.
5. Use a library like NPOI to write out an Excel file
6. Use a library like ExcelPackage to write out an Excel file
7. Use Office Open XML

Of the options, I like option 5 for performance and simplicity, especially when this is needed on the server side, option 6 is good if you require XLSX files rather than XLS, option 7 has a steep learning curve in comparison to 5 and 6.

Answer 2

Try this one -

// TO USE:
            // 1) include COM reference to Microsoft Excel Object library
            // add namespace...
            // 2) using Excel = Microsoft.Office.Interop.Excel;
            private static void Excel_FromDataTable(DataTable dt)
            {
                // Create an Excel object and add workbook...
                Excel.ApplicationClass excel = new Excel.ApplicationClass();
                 // true for object template???
                Excel.Workbook workbook = excel.Application.Workbooks.Add(true); 


                // Add column headings...
                int iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[1, iCol] = c.ColumnName;
                }
                // for each row of data...
                int iRow = 0;
                foreach (DataRow r in dt.Rows)
                {
                    iRow++;

                    // add each row's cell data...
                    iCol = 0;
                    foreach (DataColumn c in dt.Columns)
                    {
                        iCol++;
                        excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                    }
                }

                // Global missing reference for objects we are not defining...
                object missing = System.Reflection.Missing.Value;

                // If wanting to Save the workbook...
                workbook.SaveAs("MyExcelWorkBook.xls",
                    Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                    false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                    missing, missing, missing, missing, missing);

                // If wanting to make Excel visible and activate the worksheet...
                excel.Visible = true;
                Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                ((Excel._Worksheet)worksheet).Activate();

                // If wanting excel to shutdown...
                ((Excel._Application)excel).Quit();
            }