An easy way to view reports through Excel

Generally, we are in need to generate reports while handling financial or any other data. The statistics or Reports must be available for downloading. And reports must be in file format which is very easy to understand. So generally, we choose Excel file type to generate Report.

In this Article, We are going to see an easy way of handling an Excel file using StreamWriter & StreamReader for report generation only

Creation of an Excel Document

We can easily create an Excel document using StreamWriter class. We must specify the type of Encoding in the creation of file. And while writing we have to separate data in excel file using tab.

This tab will arrange data into cells correctly. So when we open, it will show you a perfect Excel document.

Ex:

We are just creating an excel file with customer details.

String[] id ={ "001", "002" };
String[] name ={ "abc", "edf" };
String[] address ={ "311,3rd street", "26,NH Road" };
StreamWriter sw = new StreamWriter("filename.xls", false, Encoding.ASCII);
sw.WriteLine("CustomerID"+"\t"+"Name" +"\t"+ "Address" );
for (int i = 0; i < id.Length; i++)
sw.WriteLine(id[i] +"\t"+ name[i] +"\t"+ address[i]);
sw.Close();

Comment for each line

//String Array creation "Customer id details"
// String Array creation "Customer name details"
// String Array creation "Customer address details"
//stream writer object creation. In this, we pass encoding type as Encoding.ASCII
// write the Headers of table i.e. Column Heading of Report
//for loop. It will run till array length
// we writes data into file. Here tab separate each cells.
//close file stream.

In the WriteLine statement, we can write our data by composing a string with tab separator. Arrays [id, name & address] containing data which are retrieved from database.

Output



If you are trying to save this Excel file it will ask a question as specified below because of Tab delimited i.e. we separate our columns by tab.



"Do you want to keep the workbook in this format?"

You have to choose yes, to keep this format. And then, save your file. If you choose a different format, then you can't read it through your C# application.

Reading an Excel Document

Using StreamReader, we can easily read the Excel Document which is created by us. It is possible, only if we keep that format [tab delimited]. Otherwise, we can't read an Excel Document using StreamReader.

We just read the lines and split data using same tab. after that, we can handle those data.

Ex:

we going to read same Excel file.

StreamReader sr = new StreamReader(file);
int count = 0;int rows = 0;
while (!sr.EndOfStream)
{
String s = sr.ReadLine();
rows++;
}
sr.Close();
sr = new StreamReader(file);
String[] id = new String[rows];
String[] name = new String[rows];
String[] address = new String[rows];
while (!sr.EndOfStream)
{
String s = sr.ReadLine();
String[] column = s.Split('\t');
id[count] = column[0];
name[count] = column[1];
address[count] = column[2];
count++;
}
sr.Close();

Comment for each Line

//Stream Reader Object creation
//count and rows variables are created and assigned with 0
3 to 7

//This while loop is used to find out the no of rows in excel file. It runs till EndOfStream. After end of while rows variable contains no of rows available in that file.

 //Close file stream.

//again assign file Stream
//String Array creation "Customer id details"
// String Array creation "Customer name details"
// String Array creation "Customer address details"
//while loop starts. and runs till EndOfStream
//while loop begins
//read a line and store it in a String
//Split the string using tabs and stores it in a string array "column".
//store data in id array
//store data in name array
//store data in address array
//increase row count
//end of loop
close file stream


I know, this is a well known way for programmers to handle excel files. But I hope it will help the beginners of C#.

After generating excel file using this format [tab delimited], you should not save it as Excel file format again. You must keep the format to read it back through C#.

Try this code for your applications. It may help you.