Reading Data from XLSX in C#

Question

Reading Data from XLSX in C# using ExcelDataReader v2.1 Library and writing to SQL Server . I need to write a console application in C# reading data from XLSX and writing to SQL server using ExcelDataReader v2.1 Library .   I need to read the data in Cell D17,D18,D19 and drop to buying column in SQL. And read Cell K17,K18,K19 and drop to /selling column in SQL This is the SQL table query and  database name is test1
SELECT TOP 1000 [id]
,[code]
,[buying]
,[selling]
FROM [test1].[dbo].[Currency] This is a code i tried but it is not using ExcelDataReader v2.1. i need help to do it with ExcelDataReader v2.1    Collapse  |  Copy Code using System;
using System.IO;
using Bytescout.Spreadsheet;
using System.Data.SqlClient;
namespace ExportToSQLServer
{
class Program
{
static void Main( string [] args)
{
try
{
string connectionString = " Data Source=192.168.1.215;Initial Catalog=RECIPES2;Persist Security Info=True;User ID=sa;Password=***********" ;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
ExecuteQueryWithoutResult(connection, " USE test1" );
// Load XLS document
using (Spreadsheet document = new Spreadsheet())
{
document.LoadFromFile( " SimpleReport.xls" );
Worksheet worksheet = document.Workbook.Worksheets[ 0 ];
for ( int row = 0 ; row String insertCommand = string .Format( " INSERT XlsTest VALUES('{0}','{1}')" ,
worksheet.Cell(row, 0 ).Value, worksheet.Cell(row, 1 ).Value);
ExecuteQueryWithoutResult(connection, insertCommand);
}
}
// Check the data successfully exported
using (SqlCommand command = new SqlCommand( " SELECT * from XlsTest" , connection))
{
SqlDataReader reader = command.ExecuteReader();
if (reader != null )
{
Console.WriteLine();
Console.WriteLine( " Exported XLS data:" );
Console.WriteLine();
while (reader.Read())
{
Console.WriteLine( String .Format( " {0} | {1}" , reader[ 0 ], reader[ 1 ]));
}
}
}
Console.WriteLine();
Console.WriteLine( " Press any key." );
Console.ReadKey();
}
}
catch (Exception ex)
{
Console.WriteLine( " Error: " + ex.Message);
Console.ReadKey();
}
}
static void ExecuteQueryWithoutResult(SqlConnection connection, string query)
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.ExecuteNonQuery();
}
}
}
}

Iftikar Hussain · Accepted Answer

try like this
FileStream stream = File.Open(@"C:\Test\SimpleReport.xls", FileMode.Open, FileAccess.Read);
//Reading from a binary Excel file ('97-2003 format; *.xls)             IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet();
var rows = result.Tables[0].Rows;
//Reading D17 cell value             string d17Cell = rows[16][3].ToString();
//Reading D18 cell value             string d18Cell = rows[17][3].ToString();
//Reading D19 cell value             string d19Cell = rows[18][3].ToString();
//Reading K17 cell value             string k17Cell = rows[16][10].ToString();
//Reading K18 cell value             string k18Cell = rows[17][10].ToString();
//Reading K19 cell value             string k19Cell = rows[18][10].ToString();
Regards,
Iftikar

Iftikar Hussain · Answer

try like this
DataRowCollection rows = result.Tables[0].Rows;             StringBuilder stringBuilder=new StringBuilder();             string buying = string.Empty;             string selling = string.Empty;             int j = 1;             for (int i = 16; i             {                 buying = rows[i][3].ToString();                 selling = rows[i][10].ToString();                 stringBuilder.AppendFormat("INSERT INTO Currency(id,buying,selling) VALUES({0},'{1}','{2}');", j, buying,                                            selling).Append(Environment.NewLine);                 j++;             }
Regards,
Iftikar

afshan dc · Answer

I Tried this but i got stuck!  DataTable tb = result.Tables[0];
foreach (DataRow r in tb.Rows)             {                 Console.WriteLine(r.ItemArray[1]);             }  The limit is D17 to D45 and K17 to K45... Please Help!
And how do i write this to the SQL database??? Sorry for the trouble! :(

afshan dc · Answer

Ok ill check and let u know!

Iftikar Hussain · Answer

you can do if you know the limit,
Regards,
Iftikar

afshan dc · Answer

I did it this way! Not like this,Can you do using a while Loop??

Iftikar Hussain · Answer

like this
DataRowCollection rows = result.Tables[0].Rows;
//Reading D17 cell value             string d17Cell = rows[16][3].ToString();
//Reading D18 cell value             string d18Cell = rows[17][3].ToString();
//Reading D19 cell value             string d19Cell = rows[18][3].ToString();
//Reading K17 cell value             string k17Cell = rows[16][10].ToString();
//Reading K18 cell value             string k18Cell = rows[17][10].ToString();
//Reading K19 cell value             string k19Cell = rows[18][10].ToString();
Regards,
Iftikar

afshan dc · Answer

Thanks @iftikar..
How do i loop this and read all the values in the column D starting from Cell 17

Iftikar Hussain · Answer

try like this
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); DataSet result = excelReader.AsDataSet(); DataRowCollection rows = result.Tables[0].Rows;
//Reading D17 cell value string d17Cell = rows[16][3].ToString();       Console.WriteLine(d17Cell );
Console.ReadLine();
Regards,
Iftikar

afshan dc · Answer

@ifthikar
I did it using this method! it reads the whole column...
I need help writing this to the sql database.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Excel; using System.IO; using System.Data;
namespace ConsoleApplication8 {     class Program     {         static void Main(string[] args)         {             FileStream stream = File.Open(@"c:\users\afshandc\documents\visual studio 2012\Projects\ConsoleApplication8\ConsoleApplication8\Test Excel File.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();                         while (excelReader.Read())             {                 Console.WriteLine(excelReader[0].ToString());                              }
excelReader.Close();
Console.ReadLine();
}     } }

Reading Data from XLSX in C#

Reading Data from XLSX in C# using ExcelDataReader v2.1 Library and writing to SQL Server.

12 Replies