How to read and write from and into Excel file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AleXmanFree - 17 Feb 2007 22:38 GMT

Hello ,

I have working sample of C# code that creates and writes into Excel
file simple text.

And this is not hard as there is a lot of samples at microsoft's msdn
site:
http://msdn2.microsoft.com/en-us/library/bbx26t8w(VS.80).aspx

But the problem is when you need to just ready existing Excel file.
The code I have writenn supposing that will actually work without
problems in fact works with a little problem , actually its not
working as should, because some of lines of codes return null value.

The part of code written for reading data or just text from Excel file
is following (the function is an addition to working code for creating
and filling new Excel file which is underneath of this post):

private void cmdOpenExcel_Click(object sender, EventArgs e)
{
//1. Create the Excel application object
EXCELIK.Application xlApp = new
EXCELIK.ApplicationClass();

//2. Open and take file
EXCELIK.Workbook wb =
xlApp.Workbooks.Open(this.txtFilePath.Text.ToString(),
Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing);

//3. Activate Workbook and Select the Sheet in excel file
by index , indexes begin from 1,not form 0.
//xlApp.Workbooks[1].Activate();

((EXCELIK.Worksheet)xlApp.ActiveWorkbook.Sheets[1]).Select(Type.Missing);

//4. Sheet object
EXCELIK.Worksheet __wSheet;
__wSheet =
(EXCELIK.Worksheet)xlApp.ActiveSheet;

//5.
//getting Values to cells
object obj = null;
string str;

xlApp.Visible = true;

obj = wSheet.Cells.get_Item(1, 3);
str = obj.ToString();
MessageBox.Show(str);
obj = __wSheet.Cells.get_Item(2, 2);
str = obj.ToString();
MessageBox.Show(str);
obj = __wSheet.Cells.get_Item(3, 1);
str = obj.ToString();
MessageBox.Show(str);
}

For creating and filling new Excel file used following code:

The code that is creating new Excel file is following (you must add
reference in project for Microsoft.Office.Interop.Excel which is on
COM tab):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using EXCELIK = Microsoft.Office.Interop.Excel;

public partial class Form1 : Form
{
EXCELIK.Worksheet wSheet;

public Form1()
{
InitializeComponent();
}

private void butExcel_Click(object sender, EventArgs e)
{
RunMyExcel();
}

private void RunMyExcel()
{
//1. Create the Excel application
EXCELIK.Application xlApp =
new EXCELIK.ApplicationClass();

//2. Add a Workbook
EXCELIK.Workbook wb = xlApp.Workbooks.Add(Type.Missing);

// deleting sheet number 2

((EXCELIK.Worksheet)xlApp.ActiveWorkbook.Sheets[2]).Delete();

//selecting of active Worksheet , giving the index of
worksheet, indexes begin from 1,not form 0.

((EXCELIK.Worksheet)xlApp.ActiveWorkbook.Sheets[2]).Select(Type.Missing);
//now is first sheet the active.
((EXCELIK.Worksheet)xlApp.ActiveWorkbook.Sheets[2]).Select(Type.Missing);

((EXCELIK.Worksheet)xlApp.ActiveWorkbook.Sheets[1]).Select(Type.Missing);

//EXCELIK.Worksheet wSheet =
(EXCELIK.Worksheet)xlApp.ActiveSheet;
wSheet = (EXCELIK.Worksheet)xlApp.ActiveSheet;
wSheet.Name = "Translations ONE!";

//adds two Worksheets to the Workbook. The Add arguments
are: Before, After, Count, Type.
////xlApp.ActiveWorkbook.Worksheets.Add(Type.Missing,
Type.Missing, 2, Type.Missing);

//Load the data
LoadMyData();

xlApp.Visible = true;
}

private void LoadMyData()
{
//Sets the all Worksheet columns width, also using Range
class :)
((EXCELIK.Range)wSheet.Columns).ColumnWidth = 20;

//setting Values to cells
wSheet.Cells.set_Item(1,3,"first row,third column VALUE");
wSheet.Cells.set_Item(2,2,"second row,second column
VALUE");
wSheet.Cells.set_Item(3,1,"third row,first column VALUE");
}

}

Waiting for your helpful answers ...

Reply to this Message

AleXmanFree - 22 Feb 2007 13:35 GMT

Well I have found and here is corrected code which is actually work
and able to read and write, hope this will help someone in future :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using EXCEL_OBJ = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

namespace Rose
{
public partial class Form1 : Form
{
EXCEL_OBJ.Application xlApp;
EXCEL_OBJ.Worksheet wSheet;
DataTable oTable;
string path;

int offset_Height, offset_Width;

public Form1()
{
InitializeComponent();
}

private void btnOpen_Click(object sender, EventArgs e)
{
try
{
xlApp = new EXCEL_OBJ.ApplicationClass();

oTable = new DataTable();
if (openDialog.ShowDialog() != DialogResult.OK)
return;
path = openDialog.FileName;

OpenExcelFile();

int i;
int col = wSheet.Cells.CurrentRegion.Columns.Count,
row = wSheet.Cells.CurrentRegion.Rows.Count;
//col = (int)wSheet.UsedRange.Value2;
//row = wSheet.Cells.CurrentRegion.Rows.Count;

//EXCEL_OBJ.Range range4;
//if (txtFirstRegion.Text == "" | txtSecondRegion.Text
== "")
//{
// range4 = wSheet.get_Range("A1", "D" +
row.ToString());
//}
//else
//{
// try
// {
// range4 =
wSheet.get_Range(txtFirstRegion.Text, txtSecondRegion.Text);
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message);
// return;
// }
//}

Object[,] array4;
//array4 = (Object[,])range4.Value2;
array4 = (Object[,])wSheet.UsedRange.Value2;

col = array4.GetLength(1) - 1; //
range4.Cells.Columns.Count;
row = array4.GetLength(0) - 1; //
range4.Cells.Rows.Count;

for (i = 0; i < col; i++)
{
oTable.Columns.Add();
}
for (i = 1; i <= row; i++)
{
DataRow dr = oTable.NewRow();
for (int j = 1; j <= col; j++)
{
//dr[j - 1] = (wSheet.Cells[i, j] as
EXCELIK.Range).FormulaR1C1.ToString();
dr[j - 1] = array4[i, j];
}
oTable.Rows.Add(dr);
}

dgView.DataSource = oTable;

xlApp.Workbooks.Close();
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("Failed to read file: \n" +
ex.Message);
}
}

private void OpenExcelFile()
{
EXCEL_OBJ.Workbook wb = xlApp.Workbooks.Open(path,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

wSheet = (EXCEL_OBJ.Worksheet)xlApp.ActiveSheet;

//wb.SaveAs("NewFile", ".xls", Type.Missing, Type.Missing,
Type.Missing,
// Type.Missing,
//
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
// Type.Missing, Type.Missing,
Type.Missing,
// Type.Missing, Type.Missing);

//xlApp.SaveWorkspace("NewFile");
//xlApp.Visible = true;
}

private void Form1_Load(object sender, EventArgs e)
{
txtFirstRegion.Text = "A1";
txtSecondRegion.Text = "D450";

offset_Height = this.Height - dgView.Height;
offset_Width = this.Width - dgView.Width;
dgView.Height = this.Height - offset_Height;
dgView.Width = this.Width - offset_Width;
}

private void Form1_Resize(object sender, System.EventArgs e)
{
dgView.Height = this.Height - offset_Height;
dgView.Width = this.Width - offset_Width;
}

private void btnSave_Click(object sender, EventArgs e)
{
//            user = Membership.CreateUser(sUserName,
//
((TextBox)FormView1.Row.FindControl("txtPass")).Text,
//
((TextBox)FormView1.Row.FindControl("EmailTextBox")).Text);
//
//            ViewState["aspNetUserID"] =
//                user.ProviderUserKey;

SqlConnection CON = new SqlConnection("Persist Security
Info=False;User ID=sa;Password=sa123;Initial
Catalog=TSGAndrew;Workstation ID=GHETTOGOSPEL;");//"Data
Source=GHETTOGOSPEL;Initial Catalog=TSGAndrew;Integrated
Security=True");
string connectionString = "";
CON.Open();

string FullAddress = "", FullName = "", name = "", surname
= "", secondname = "", city = "";
int ApartmentID = 20, HouseID = 11, UpdatedBy = 35,
UserType = 1, accountID = 27, main_user = 0;

int i, k = 2;

for (i = 0; i < oTable.Rows.Count; i++)
{
if (oTable.Columns[0].Table.Rows[i][k].ToString() !=
"<>" &
oTable.Columns[0].Table.Rows[i]
[k].ToString() != "" &
oTable.Columns[0].Table.Rows[i]
[k].ToString() != "ФИО")
{
FullName = oTable.Columns[0].Table.Rows[i]
[k].ToString();
FullAddress = oTable.Columns[0].Table.Rows[i][k +
1].ToString();

DivideNSS(FullName, ref name, ref surname, ref
secondname);
DivideAddress(FullAddress, ref city, ref HouseID,
ref ApartmentID);

connectionString = "SELECT [Name], [Surname],
[SecondName] FROM [TsgUsers] ";
connectionString += "WHERE (Name='" + name + "')
AND (Surname='" + surname + "') AND ";
connectionString += "(SecondName='" + secondname +
"')";
SqlCommand COM = new SqlCommand(connectionString,
CON);
SqlDataReader sql_DataReader =
COM.ExecuteReader();

//MUST FILL THIS VALUES
//+++++++++++++++++++++
string AspNetUserID = "", phone = "", email = "",
description = "";
DateTime dt = new DateTime();
dt = DateTime.Now;//02/05/2007
//---------------------

if (sql_DataReader.HasRows == false)
{
sql_DataReader.Close();

connectionString = "INSERT INTO [TSGAndrew].
[dbo].[TsgUsers] ";
connectionString += "([AspNetUserID], [Name],
[Surname], [SecondName], [ApartmentID], ";
connectionString += "[HouseID], [Updated],
[UpdatedBy], [UserType], [Phone], [Email], ";
connectionString += "[Description],
[AccountID], [MainUser]) ";
connectionString += "VALUES ('" + AspNetUserID
+ "', '" + name + "', '" + surname + "', ";
connectionString += "'" + secondname + "', " +
ApartmentID + ", " + HouseID + ", '" + dt + "', ";
connectionString += UpdatedBy + ", " +
UserType + ", '" + phone + "', '" + email + "', '" + description + "',
" + accountID + ", " + main_user + ")";

COM.CommandText = connectionString;
COM.Connection = CON;
COM.ExecuteNonQuery();
}
else
{
sql_DataReader.Close();
}
}
}
}

}
}

Reply to this Message

AleXmanFree - 22 Feb 2007 13:51 GMT

Reply to this Message

Rate this thread:

How to read and write from and into Excel file

Free Magazines