c#

Reading Excel files easily in c# using Excel Data Reader

So I found a useful Package to read excel files easily. No more cumbersome codes needed.

I have written post on How to read Excel file in c# in here . But lot of codes.

So I found ExcelDataReader package in github.

Excel Data Reader

It is awesome.

Just few lines of codes to read files as below.


string filePath = "Book1.xlsx";
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

excelReader.Close();

done.

Download sample project file from github

0 CommentsLeave a comment

Reading excel file in c#

Some times Reading Excel files can be bit cumbersome. I needed to allow user to upload excel document and read data from excel file. So I followed following way to get it done.


private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath + "\\sample.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

range = xlWorkSheet.UsedRange;

// read data and bind to grid view
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = xlWorkSheet.UsedRange.Cells.Columns.Count;
int iValue = xlWorkSheet.UsedRange.Cells.Rows.Count;
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));

}
for (int i = 1; i <= iValue; i++)
{
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
{
range = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i, j];
string strValue = range.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}

dataGridView1.DataSource = ds.Tables[0];
xlWorkBook.Close(true, null, null);
xlApp.Quit();

// release object
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

Download source files

Video tutorial

1 CommentLeave a comment

Serializing Lists of Classes to XML

So some times we want to convert object to xml. We can follow severel cumbersome methods. So I am using Utlity methods to convert c# object to xml and xml to c# object.

Suppose we have class like below


public class Student
 {
 public int Id { get; set; }
 public string FirstName { get; set; }
 public string LastName { get; set; }
 }

And we want serialize List<Student> to xml. So we can use this utlity function


/// <summary>
/// Serialize object to xml
/// </summary>
/// <typeparam name="T">Object type</typeparam>
/// <param name="obj">Object</param>
/// <returns>XML</returns>
public static string ToXml<T>(T obj)
{
using (StringWriter stringWriter = new StringWriter(new StringBuilder(), CultureInfo.InvariantCulture))
{
XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
xmlSerializer.Serialize(stringWriter, obj);
return stringWriter.ToString();
}
}

As a example we populate List<student>  with dummy data


 List <Student> studentList = new List<Student>();

studentList.Add(new Student {
 FirstName = "jeevan",
 LastName = "JLast name",
 Id = 1
 });

studentList.Add(new Student
 {
 FirstName = "john",
 LastName = "johnLast name",
 Id = 2
 });

Then call out utility function


string xml = Utility.ToXml(studentList);

then out xml variable look like as below


<?xml version="1.0" encoding="utf-16"?>
<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Student>
<Id>1</Id>
<FirstName>jeevan</FirstName>
<LastName>JLast name</LastName>
</Student>
<Student>
<Id>2</Id>
<FirstName>john</FirstName>
<LastName>johnLast name</LastName>
</Student>
</ArrayOfStudent>

 

0 CommentsLeave a comment