CODEWITHSUNDEEP
c#exceldatatableoledb
Arpit Shukla
Arpit Shukla

Reputation: 33

exporting data set to excelsheet

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

Upvotes: 0

Views: 93

Answers (2)

Arpit Shukla
Arpit Shukla

Reputation: 33

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;
}

Upvotes: 1

Git
Git

Reputation: 314

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 =)

Upvotes: 0

Related Questions