exporting data set to excelsheet

Question

I am new to excel automation in C# so I am confused about this. I have imported an excel in a dataset and I have done some updates in the dataset as per my requirement. now I want to export that dataset to that input sheet so that I can see the updates done in the dataset reflected in the datasheet. what will be the best approach for exporting dataset to excel. below is the code of how I am opening the excel sheet:

string sConnection = null;
OleDbConnection oleExcelConnection = default(OleDbConnection);
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\input.xls;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();

string sqlquery = "Select * From [c:\input.xls]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sqlquery, oleExcelConnection);
da.Fill(ds);
System.Data.DataTable dt = ds.Tables[0];

/* 10 to 12 linq queries on dt*/

-> now here I want to export the updated dt to input.xls

The best approach would be searching for examples on the Internet and trying to tackle it yourself. If you have any particular problems then, feel free to ask a question on SO. Thank you. — Alex
– Alex, Commented Jul 13, 2016 at 9:08
I tried to search on google as well as on this site. but the solutions that are available are all third-party. I cannot use them in my code which is my limitation for some reason. I am looking for a solution which doesn't require any third party component. — Arpit Shukla
– Arpit Shukla, Commented Jul 13, 2016 at 10:32
Microsoft.Office.Interop.Excel is the only assembly you need to achive this. — Alex
– Alex, Commented Jul 13, 2016 at 10:35
I have tried doing it with interop but my input file will have 25k to 30k rows in an excel and it took me more than 20 minutes to complete all the update operations. that's why I moved to OleDB and datatable approach. And honestly, it works REALLY fast. The only problem is, I couldn't find proper approach for dumping my datatable back to my input excel. — Arpit Shukla
– Arpit Shukla, Commented Jul 13, 2016 at 10:48

Arpit Shukla · Accepted Answer · 2016-07-14 11:43:39Z

after research of many hours, I found a way to write an excel using datatable. Although, my original requirement was to update the original sheet, I guess I will have to be happy with creating a new output sheet from scratch. the solution is given below:

//open file
StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");
// dt is the DataTable needed to be dumped in an excel sheet.
try
{

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
    }

    wr.WriteLine();

    //write rows to excel file
    for (int i = 0; i < (dt.Rows.Count); i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            if (dt.Rows[i][j] != null)
            {
                wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
            }
            else
            {
                wr.Write("\t");
            }
        }
        //go to next line
        wr.WriteLine();
    }
    //close file
    wr.Close();
}
catch (Exception ex)
{
    throw ex;
}

Git · Accepted Answer · 2016-07-13 09:13:58Z

0

http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB

private void WriteExcelFile()
{
    string connectionString = GetConnectionString();

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;

    cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";
    cmd.ExecuteNonQuery();

    conn.Close();
}
}

Google is your friend =)

answered Jul 13, 2016 at 9:13

Git

3145 silver badges16 bronze badges

2 Comments

Arpit Shukla Over a year ago

I tried searching it on google as well as this site but found no satisfactory answer. Actually I have created a dataset 'dt' from 'input.xls' and made some updates in it. so, how will I export updated dataset to the original input excel e.g. 'input.xls'. I have updated my original question for better explanation.

Git Over a year ago

you dont change your file via dateset, change it directly. There is no way, to change it via dataset, that i lnow of. You could try, to find a way to write your dataset to a new File. But I have no idea how.

Collectives™ on Stack Overflow

exporting data set to excelsheet

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related