In many situation we find our self that we need to get a stream object from string.
In this case we can utilize Encoding Class and Memory Stream class to generate Stream from a String.
Encoding.GetByte method helps to generate byte array from given string.
var stream = new MemoryStream(Encoding.UTF8.GetBytes("some text goes here!.."))
Seems very small, but useful 🙂
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.
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.
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();
}
}
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>
