Output is not in human readable format when writing content of an Excel sheet to console in C#

Question

I'm trying to read an excel sheet and displaying contents to console. the code is here

  FileStream fileStream = new FileStream(@"E:\USERS\MyWorkbook.xlsx", FileMode.Open, FileAccess.Read);
  byte[] byteCode = new byte[fileStream.Length];
  fileStream.Read(byteCode, 0, (int)fileStream.Length);            
  foreach (var byteValue in byteCode)
  {
      Console.Write(Convert.ToChar(byteValue));
  }
  Console.ReadLine();

the output is

PK♥♦¶ ♠ ! bî?h^☺ ?♦ ‼☻[Content_Types].xml ¢♦☻( ☻

¬?ENA0►E÷HüCä-JÜ²5í?Ç↕*Q>AÄ?Æªc[?iiÿ??û►B¡§j7±↕IÜ{2ñIh²nm¶??Æ»R♀??EAU^←7/>ÅÇ ì%¿↨↓?rZYï ¶←@1↓__?f? ?q·AR4DáAJ¬→h§▬>?a?UÇV◄ßÆ1♀ªZ"9EUAàNV_◄8EcO►aÑ‼Oji){^óa-I♦ ?"{Ü▬v^¥P!XS)bR1rú?K_s(,3O`▬_§ïßeÖ↔♦»ß1æ►@(?a?sí?►[í?☼öB·LA?F←I↔"fKlk↑--¿$♣?A pN$å

What's wrong with it?

just follow this tutorial that will be help you https://bytescout.com/products/developer/spreadsheetsdk/read-write-excel.html — KARAN, Mar 18 '16 at 06:29
you read file as binary, you must use xcel object instead, there's plenty of exmaple. — , Mar 18 '16 at 06:30
the link above is a bad practice ( call GC and set obejct to null) — , Mar 18 '16 at 06:34
Because that's not the way to read a excel file pro-grammatically .. some good solution already given in your question already .. — Moumit, Mar 18 '16 at 08:53

score 6 · Accepted Answer · edited May 23 '17 at 12:34

With the help of Oledb (Object Linking and Embedding Database). OLE DB is Microsoft's strategic low-level application program interface (API) for access to different data sources.

The objects in OLE DB consist mainly of a data source object, a session object, a command object, and a rowset object. An application using OLE DB would use this request sequence:

Initialize OLE.
Connect to a data source.
Issue a command.
Process the results.
Release the data source object and uninitialize OLE.

here is a link of previous stackoverflow answer how to use olddb to access data from excel.

How to read data from excel file using c#

Here is Sample Code

string path = @"E:\USERS\MyWorkbook.xlsx";
//Create connection string to Excel work book
string excelConString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

OleDbConnection excelCon = new OleDbConnection(excelConString);

excelCon.Open();

DataTable dtsheet = new DataTable();

dtsheet = excelCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });    

foreach (DataRow row in dtExcelSheet.Rows)
{ 
    Query = string.Format("Select * from [{0}]", row["TABLE_NAME"].ToString());
    //Create OleDbCommand to fetch data from Excel
    OleDbCommand cmd = new OleDbCommand(Query, excelCon);
    DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter(Query, excelCon);
    excelCon.Close();
    oda.Fill(ds);
    DataTable Exceldt = ds.Tables[0];

   foreach (DataRow dr in Exceldt.Rows)
   {
      //code to display
   }
}

Can you please provide some demo code, showing how it's a solution to the question .. — Moumit, Mar 18 '16 at 07:32
@Moumit I have edited post with sample code. You can have a look. — Fanjo Lama, Mar 18 '16 at 08:38

score 4 · Answer 2 · edited May 23 '17 at 10:27

You should probably be looking into the office interop object model or using an oledb adapter.

Codeproject has some good resources on the object model here http://www.codeproject.com/Tips/696864/Working-with-Excel-using-Csharp

and there's a previous stackoverflow answer on how to use an oledb connection to query the data here https://stackoverflow.com/a/16051/6080982

Some sample code with office objects:

using Excel = Microsoft.Office.Interop.Excel;

        Excel.Application app = new Excel.Application();
        Excel.Workbook wb = app.Workbooks.Open("path to a workbook");
        Excel.Worksheet sheet = (Excel.Worksheet)wb.Sheets[1];
        int lastrow = sheet.UsedRange.Rows.Count;
        int lastcol = sheet.UsedRange.Rows.Count;

        Excel.Range c1;
        Excel.Range c2;

        for (int i = 1; i <= lastrow; i++)
        {
            c1 = (Excel.Range)sheet.Cells[i, 1];
            c2 = (Excel.Range)sheet.Cells[i, lastcol];
            Excel.Range range = sheet.Range[c1, c2];

            foreach(Object o in (System.Array)range.Value)
            {
                if (o != null)
                {
                    Console.Write(o.ToString());
                }
            }
            Console.WriteLine();
        }
        Console.ReadLine();

plus 1 and xlm read version https://msdn.microsoft.com/en-us/library/office/cc823095.aspx — , Mar 18 '16 at 06:37
Can you please provide some demo code, showing how it's a solution to the question — Moumit, Mar 18 '16 at 08:51

score 3 · Answer 3 · answered Mar 18 '16 at 08:02

Like others have mentioned, you should look into office interop object. Add the Microsoft.Office.Interop.Excel as reference into your project, you can usually find this dll at

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15

Here's a code that might help you with what you want to do, I have added comments to help you understand each line better:

Excel.Application xlApp = new Excel.Application();
//Points to the excel path
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\USERS\MyWorkbook.xlsx");    
//Sheets[1] = First worksheet, modify this according to your need
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];    
//UsedRange means the range of cells that has contents (are being used).
Excel.Range xlRange = xlWorksheet.UsedRange;

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            var cellContent = xlWorksheet.Cells[i, j].Value;
            //To prevent exceptions due to null reference         
            if (cellContent != null)
            {
                Console.WriteLine(cellContent.ToString());
            }                  
        }
    }

First you use .UsedRange to figure out the maximum number of row and column that contains stuff. Then you simply do a nested loop and display the content in each cell using Console.WriteLine (Like what you intended).

Output is not in human readable format when writing content of an Excel sheet to console in C#

3 Answers3