Here i can able to get read and write data using oledb in C# , but i have try to read data from two excel sheet and write in another sheet.
for example my sheet 1 contain coloum |code |descriptin -- sheet 2 contain code |description (empty i have try to get from sheet 1)
I have to get description from sheet 1 for sheet description
using System.Collections.Generic;
using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Data.SqlClient;
namespace Train1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Subbut_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection();
string Import_FileName = txtFileName.Text;
string fileExtension = Path.GetExtension(Import_FileName);
if (fileExtension == ".xls")
{
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + Import_FileName + ";Extended Properties= \"Excel 12.0;HDR=yes\"";
}
else if (fileExtension == ".xlsx")
{
conn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " + Import_FileName + ";" + "Extended Properties ='Excel 12.0 Xml;HDR=YES;'";
}
else
{
MessageBox.Show("PLese choose correct excel file format");
}
OleDbCommand comm = new OleDbCommand("Select * from [Sheet1$]");
comm.Connection = conn;
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = comm;
da.Fill(ds);
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.Application excelApp = new Excel.Application();
excelApp.Application.Workbooks.Add(true);
string timeMark = DateTime.Now.ToString(" - yyyyMMddHHmmss");
string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Output" + timeMark + ".xlsx");
Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks;
Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss);
foreach (System.Data.DataTable table in ds.Tables)
{
Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
excelWorkBook.Close(false, miss, miss);
books.Close();
excelApp.Quit();
}
}
private void butbrow_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
txtFileName.Text = openFileDialog1.FileName;
}
}
}
}
What I have tried:
I have tried to get read and write data from single excel sheet. but i have try to get data from sheet to sheet and creat output sheet to another sheet.
Thanks advance....