Exporting Excel to C#

I have to port excel file which business guys are using to calculate final price to C# so I can later use this algorithm in Asp.Net application. This is something I will be doing not for the first time, but today it hit me. Maybe Excel can export to C#. I don't care if it will look ugly, but this is still something to start with.

Is there some Excel plugin where I could mark some input and output cells and this add-it would export it to C#?

Edit

Our client have Excel file where employes can modify some fields and see final price in another field. Excel file doesn't have any VB, but uses standard mathematical functions functions to calculate the price.

Imagine excel file used to calculate live insurance price.

• Excel file has empty cells company employer has to fill: client age, bad habits, insurance price, insura开发者_运维百科nce period, discount and so on
• Excel file has many cells with constants. Constants are most likely coeficient (like price coefficient for number of years insurance contract is signed for for. The more years the less is the price)
• Excel file has many cells with intermediate values
• Excel file have one output cell with final insurance price for the whole period

What I have to do is to port calculations made with excel file to C#.

Strictly speaking, it doesn't make a lot of sense to "export Excel to C#." This is because what you generally wish to export from Excel is data and C# is a programming language. Perhaps the question you're looking for is, "How can I make Excel data available to a program written in C#?"

I used this write-up on C# Excel Interop Use as a starting point for reading Excel data from an application written in C#. That might be a good place to start to figure out how to make Excel data visible in C#.

The other way to interpret your question is that you want to convert the Visual Basic for Applications (VBA) code within an Excel workbook to C#. If this is this case, you have a few options:

• If it's not a lot of code, it probably would be easiest to simply convert the algorithms and procedures one at a time from VBA to C#.
• This is a pretty ugly way, but you could import the VBA code into Visual Studio and upconvert it to VB.NET. Then you could use reflector to reflect the code to C#. Granted, this assumes that the VBA code is not tightly coupled to individual Excel objects (worksheets, rows, cells, ranges, etc.); if it's anything like the Excel "business applications" I've seen (and written), that's not likely to be the case.
• For absolutely huge applications with decoupled code, you might consider VB6 to .NET migration solutions, like VB Migration Partner. I'm not sure how well these solutions will work if you have tightly coupled code, but if the workbook is huge, it might be something to look at.

If you mean:

• I have an Excel VBA application that I wish to port to C#

Then this question may be of some assistance:

• Is it possible to convert VBA to C#?

You can't really create code from a spead-sheet. You rather want a .NET API that reads excel files. Of which there are a few. This is the one I use: http://exceldatareader.codeplex.com/

I've never used it, but you might want to look at this:

http://www.calc4web.com/

It seems to do what you want, only with C++ instead of C#, but the point is that you get some code you can then compile and use (or translate if you really need it to be C#).

In the answers to this question:

Understanding / Modeling formulas from Excel

Joe Erickson gave a link to his product, Spreadsheet Gear, but I've never used that either.

If your company has SharePoint 2007 or 2010, you may want to take a look at Excel Services, which lets you use calculations in Excel workbooks via a SharePoint web part. Not sure if you would be able to expose that web part to other ASP.NET applications or not, but it might be worth investigating

Try to use this code, may it ll help

    public static void DataSetsToExcel(DataSet dataSet, string filepath)
    {
        try
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
            string tablename = "";
            DataTable dt = new DataTable();
            foreach (System.Data.DataTable dataTable in dataSet.Tables)
            {
                dt = dataTable;
                tablename = dataTable.TableName;
                using (OleDbConnection con = new OleDbConnection(connString))
                {
                    con.Open();
                    StringBuilder strSQL = new StringBuilder();
                    strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]");
                    strSQL.Append("(");
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
                    }
                    strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                    strSQL.Append(")");

                    OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
                    cmd.ExecuteNonQuery();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        strSQL.Clear();
                        StringBuilder strfield = new StringBuilder();
                        StringBuilder strvalue = new StringBuilder();
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            strfield.Append("[" + dt.Columns[j].ColumnName + "]");
                            strvalue.Append("'" + dt.Rows[i][j].ToString().Replace("'", "''") + "'");
                            if (j != dt.Columns.Count - 1)
                            {
                                strfield.Append(",");
                                strvalue.Append(",");
                            }
                            else
                            {
                            }
                        }
                        if (strvalue.ToString().Contains("<br/>"))
                        {
                            strvalue = strvalue.Replace("<br/>", Environment.NewLine);
                        }
                        cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ")
                            .Append(strfield.ToString())
                            .Append(") values (").Append(strvalue).Append(")").ToString();
                        cmd.ExecuteNonQuery();
                    }
                    con.Close();
                }
            }
        }
        catch (Exception ex)
        {                
        }
    }