Home > DeveloperSection > Blogs > Write to Excel Sheet

Write to Excel Sheet


C# C# 
Ratings:
1 Comment(s)
 1335  View(s)
Rate this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.OleDb;

namespace Excel_Writer
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
       

        private void button1_Click(object sender, RoutedEventArgs e)
        {
           
            OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='F:\\test\\test.xlsx';Extended Properties=Excel 8.0;");
            try
            {
                
                string sqlStquery = "Insert into [details$](SNo,Date,Address1,Address2,Address3,Address4,Address5,Address6,ClientName,Date1,LandSqfeet,Amt,ContractAmt,LandCost,RegistrationCost,TotalAmount,Sqfeet,Area,PlotNo,DocumentPreparationDate) Values('" + txtSNo.Text + "','" + txtDate.Text + "','" + txtAddress1.Text + "','" + txtAddress2.Text + "','" + txtAddress3.Text + "','" + txtAddress4.Text + "','" + txtAddress5.Text + "','" + txtAddress6.Text + "','" + txtClientName.Text + "','" + txtDate1.Text + "','" + txtLandSqFeet.Text + "','" + txtAmt.Text + "','" + txtContractAmt.Text + "','" + txtLandCost.Text + "','" + txtRegCost.Text + "','" + txtTotalAmt.Text + "','" + txtSqFeet.Text + "','" + txtArea.Text + "','" + txtPlotNo.Text + "','" + txtDocPreDate.Text + "')";

                // your connection string
                excelConnection.Open();
                OleDbDataAdapter dbadapter = new OleDbDataAdapter(sqlStquery, excelConnection);
                // insert data to excel
                dbadapter.SelectCommand.ExecuteNonQuery();
                dbadapter.Dispose();
                excelConnection.Close();
                excelConnection.Dispose();
                MessageBox.Show("Data Saved Successfully.");
            }
            catch (Exception ex)
            {
                excelConnection.Close();
                excelConnection.Dispose();
                MessageBox.Show("Error :" + ex.ToString());
            }
            // cmd = new OleDbCommand("Insert into [details$](SNo,Date,Address1,Address2,Address3,Address4,Address5,Address6,ClientName,Date1,LandSqfeet,Amt,ContractAmt,LandCost,RegistrationCost,TotalAmount,Sqfeet,Area,PlotNo,DocumentPreparationDate) values('"+txtSNo.Text+"','"+txtDate.Text+"','"+txtAddress1.Text+"','"+txtAddress2.Text+"','"+txtAddress3.Text+"','"+txtAddress4.Text+"','"+txtAddress5.Text+"','"+txtAddress6.Text+"','"+txtClientName.Text+"','"+txtDate1.Text+"','"+txtLandSqFeet.Text+"','"+txtAmt.Text+"','"+txtContractAmt.Text+"','"+txtLandCost.Text+"','"+txtRegCost.Text+"','"+txtTotalAmt.Text+"','"+txtSqFeet.Text+"','"+txtArea.Text+"','"+txtPlotNo.Text+"','"+txtDocPreDate.Text+"')", con);
            //con.Open();
            //int n = cmd.ExecuteNonQuery();
            //con.Close();
            //if (n > 0)
            //{
            //    MessageBox.Show("record inserted");
            //    //loaddata();
            //}
            //else
            //    MessageBox.Show("insertion failed");
        }

        
    }
}


Re: Write to Excel Sheet - alternative to OleDb

By Nibaal Bazzi on   3 years ago
Try this approach, it is really simple and straightforward way to write Excel file in C#:
It uses this C# Excel component.

ExcelFile file = new ExcelFile();
ExcelWorksheet sheet = file.Worksheets.Add("details");

int
 rowIndex = 0;
int columnIndex = 0;

sheet.Cells[rowIndex, columnIndex++].Value = txtSNo.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtDate.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAddress1.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAddress2.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAddress3.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAddress4.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAddress5.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAddress6.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtClientName.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtDate1.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtLandSqFeet.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtAmt.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtContractAmt.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtLandCost.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtRegCost.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtTotalAmt.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtSqFeet.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtArea.Text;
sheet.Cells[rowIndex, columnIndex++].Value = txtPlotNo.Text;
sheet.Cells[rowIndex, columnIndex].Value = txtDocPreDate.Text;

file.Save("F:\\test\\test.xlsx");


Don't want to miss updates? Please click the below button!

Follow MindStick