Excel reporting with asp.net

I have Excel(.xlsx) files(template) and I need fill with data in some parts of Excel from SQL server database. Is it posibble? If it is not posibble what is best way to achieve it? I am also thinking about SQL server Reporting servi开发者_高级运维ces. Please let me know.

Thanks for any suggestions.

---

There are so many ways to do this, it really depends on what your more comfortable doing and how you want the solution to be integrated into your final solution.

Here are some approaches I use, to give you ideas on what can be done...

Report Services

The control is very easy to integrate into an existing ASP.NET solution, security is based on Windows Authentication so security is managed in SQL Server/Active Directory. Typically in my small apps I use impersonation to disable the security and set hidden report parameters to control the report. Reports are created in BIDS/VS using a GUI and feels very much like building reports in Access. The control also supports exporting in a ton of different formats (PDF, XLS, DOC, etc...).

Editing XLS files without Excel

I've been using NPOI for the past few years and it's great, it's easy to manage/manipulate XLS documents like templates. Here is an example of doing a template in NPOI.

Example

// Open Template
FileStream fs = new FileStream(Server.MapPath(@"\template\Template_EventBudget.xls"), FileMode.Open, FileAccess.Read);

// Load the template into a NPOI workbook
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

// Load the sheet you are going to use as a template into NPOI
HSSFSheet sheet = templateWorkbook.GetSheet("Event Budget");

// Insert data into template
sheet.GetRow(1).GetCell(1).SetCellValue(EventName.Value);  // Inserting a string value into Excel
sheet.GetRow(1).GetCell(5).SetCellValue(DateTime.Parse(EventDate.Value));  // Inserting a date value into Excel

sheet.GetRow(5).GetCell(2).SetCellValue(Double.Parse(Roomandhallfees.Value));  

Direct XLS/XLSX with OLE

Another solution to working directly with XLS and XLSX files is to using OleDbConnection which is installed with the 2007 Office System Driver.

Example

 using (
            OleDbConnection con =
                new OleDbConnection(
                    @"Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;Data Source=" + SourceFile +
                    @";Extended Properties=Excel 12.0;"))
        {
            con.Open();
            string sql = String.Format("SELECT * FROM [{0}$]", sheetName);
            OleDbDataAdapter da = new OleDbDataAdapter(sql, con);

            da.Fill(dt);
        }

Using SSIS to populate a Excel Template

This is all done in the SQL Server, the output could be mailed or dropped in a folder. I use this approach if I need to automate the sending of per-defined Excel files with special formatting/formulas/etc... This is setup with BIDS/VS in a SSIS package. You'll create a Data Flow with a Excel Destination. There are some limitations to this approach, use the link for details.

Again, the right solution is going to be based on your needs... Also take maintenance into consideration, since end consumers of reports seem to always need changes/updates made to reports!!!

---

This might give you some pointers:

http://www.codeproject.com/KB/aspnet/coolcode2_aspx.aspx

http://www.haneng.com/asp-forum/insert-data-into-excel-file-from-aspx_12640.html

This Google search

---

I used Spreadsheet gear. it's paid but a very good tool.