Solved

Problem when read a file in C#

Posted on 2016-09-24
17
87 Views
Last Modified: 2016-10-01
I am trying to read the excel file (which I attached). When i tried to read a different file with no empty rows at the excel file everything is ok but for this file I can’t read it. How to correct the problem?
try.xls
0
Comment
Question by:Gas
  • 9
  • 7
17 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41814198
Hi Gas;

Please post the code you are using to open the excel file and what do you mean by "I can't read it"? Does it cause an error or what?
0
 

Author Comment

by:Gas
ID: 41814443
When i run the code i receive the message :
COMException was unhandled
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in openexcel.exe


The code which i used is :

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

 

namespace openexcel
{
    public class Program
    {
public const int CATEGORY_A = 112010;
public const int CATEGORY_B = 112020;
public const int CATEGORY_C = 112030;
Double categ_A_balance = 0;
Double categ_B_balance = 0;
Double categ_C_balance = 0;
        static void Main(string[] args)
        {
            // Reference to Excel Application.
            Excel.Application xlApp = new Excel.Application();

            // Open the Excel file.
            // You have pass the full path of the file.
            // In this case file is stored in the Bin/Debug application directory.
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(Path.GetFullPath("try.xls"));

            // Get the first worksheet.
            Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(1);

            // Get the range of cells which has data.
            Excel.Range xlRange = xlWorksheet.UsedRange;






            // Get an object array of all of the cells in the worksheet with their values.
            object[,] valueArray = (object[,])xlRange.get_Value(
                        Excel.XlRangeValueDataType.xlRangeValueDefault);

            // iterate through each cell and display the contents.
            for (int row = 1; row <= xlWorksheet.UsedRange.Rows.Count; ++row)
            {

                // Print value of the cell to Console.
                if (valueArray[row, 2] != null)
                {
                    int category;
                    //Console.WriteLine(valueArray[row, 2].ToString());
                    string k = Convert.ToString(valueArray[row, 2]);
                    bool res = int.TryParse(k, out category);
                    if (res == false)
                    {
                        // String is not a number.
                    }
                    else
                    {
                        //Console.WriteLine("CATEGORY:" + category);
                        if(CATEGORY_A==category)
                        {
                            Console.WriteLine("CATEGORY_A");
                        }
                    }


                }
                    if (valueArray[row, 16]!=null)
                    { 
                        int Debit=0;
                      //Console.WriteLine(valueArray[row, 16].ToString());
                      string t = Convert.ToString(valueArray[row, 16]);
                      //check = int.Parse(t);
                                      bool res = int.TryParse(t, out Debit);
                                      if (res == false)
                                      {
                                          // String is not a number.
                                      }
                                      else
                                      {
                                          Console.WriteLine("DEBIT:" + Debit);
                                      }
                    }

                    if ((valueArray[row, 17] != null)&& (row!=6))
                    {
                        int credit = 0;
                        int credit_minus = 0;
                        string r = null;
                        r=Convert.ToString(valueArray[row, 17]);
                                    bool res1 = int.TryParse(r, out credit);
                                    if (res1 == false)
                                    {
                                        // String is not a number.
                                    }
                                    else
                                    {
                                        Console.WriteLine("Credit:"+credit);
                                    }
                                    credit_minus = credit * (-1);
                                    Console.WriteLine("credit_minus:" +credit_minus);
                         }
/*public void check_category(int row2,int row16,int row17)
{
}
 */

                    
            }

            // Close the Workbook.
            xlWorkbook.Close(false);

            // Relase COM Object by decrementing the reference count.
            Marshal.ReleaseComObject(xlWorkbook);

            // Close Excel application.
            xlApp.Quit();

            // Release COM object.
            Marshal.FinalReleaseComObject(xlApp);

            Console.ReadLine();

        }
    }
}

Open in new window

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41814726
Office automation has always been a pain! That's why a long time ago, I have switched to a library that never gives me any issues: http://www.aspose.com/products/cells/net
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Gas
ID: 41814844
Any other solution which is not require any paid program?
0
 

Author Comment

by:Gas
ID: 41815782
Can anybody help me in this issue? (without using tools which is needed to buy)
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41816186
Hi Gas;

I dropped your code into a new console application and this was the results that your code returned in the console window. Please let me know if these were the results that you were expecting.Console Window Results.
0
 

Author Comment

by:Gas
ID: 41818601
Thanks  Fernando for your response,
 
Firstly I expect to read and write the characters :

name type      
Periods:      01/2015 to 2/2015
name1                  
Currency:      EUR
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41818741
Hi Gas;

The question you posted in part was, "but for this file I can’t read it.", so are you now saying you can read it?

I used the code you posted so the results should work as the excel file that first worked.
0
 

Author Comment

by:Gas
ID: 41818958
I had a problem with the location of the file.
Now I can read the file but not in the format which i  need.
Any suggestion how to read it correctly?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41818961
That depends on what information you want to retrieve?
0
 

Author Comment

by:Gas
ID: 41820891
Firstly i need to retrieve  the info :

name type      
Periods:      01/2015 to 2/2015
name1                  
Currency:      EUR

and then to read the  numbers at the columns ID, categA,categB,categ.C
0
 

Author Comment

by:Gas
ID: 41821446
Anyone to help me for this issue? Am i clear about the problem ?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41821546
Hi Gas;

Is the output you are looking for something like the following? If not then please show what you are looking for.
name type      
Periods:      01/2015 to 2/2015
name1                  
Currency:      EUR

categ A     categ. B      categ C
A           A             A
8,500.00    0.00          8,500.00
114.84      0.00          14.84
5.09        0.00          567.09
29.78       96000.00      3,878.78

Open in new window

0
 

Author Comment

by:Gas
ID: 41822425
Fernando ,

The output will be :

name type      
Periods:      01/2015 to 2/2015
name1                  
Currency:      EUR

for the rest results is needed to read the values for the column A,B,C and i will do some calculations.
Is it clear?
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41822904
Hi Gas;

Here is some code that you can used to get the data from the Excel Document. You may need to modify it to meet your needs, you can follow the code to figure that out.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Runtime.InteropServices;

namespace ConsoleApplication2 {
    public class Program {
        public const int CATEGORY_A = 112010;
        public const int CATEGORY_B = 112020;
        public const int CATEGORY_C = 112030;
        Double categ_A_balance = 0;
        Double categ_B_balance = 0;
        Double categ_C_balance = 0;

        static void Main ( string[] args ) {
            // Holds the data from the Excel Document in a class ExcelData
            List<ExcelDaata> excelData = new List<ExcelDaata>();

            // Reference to Excel Application.
            Excel.Application xlApp = new Excel.Application();

            // Open the Excel file.
            // You have pass the full path of the file.
            // In this case file is stored in the Bin/Debug application directory.
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("C:/Working Directory/Try.xls");

            // Get the first worksheet.
            Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(1);

            // Get the range of cells which has data.
            Excel.Range xlRange = xlWorksheet.UsedRange;

            // Get an object array of all of the cells in the worksheet with their values.
            object[,] valueArray = (object[,])xlRange.get_Value(
                         Excel.XlRangeValueDataType.xlRangeValueDefault);

            // iterate through each cell and display the contents.
            for ( int row = 1; row <= xlWorksheet.UsedRange.Rows.Count; row += 11 ) {
                // Make sure you are at the beginning of a new sequence of data
                if ( valueArray[row, 1] != null && valueArray[row, 1].ToString ( ) == "name type") {
                    // Create a class to hold the data for one sequence
                    var eData = new ExcelDaata();
                    // Get the data from the the Excel document
                    eData.NameType = valueArray[row, 1].ToString ( );
                    eData.Periods = valueArray[row + 1, 3].ToString ( );
                    eData.Name = valueArray[row + 2, 1].ToString ( );
                    eData.Currency = valueArray[row + 3, 3].ToString ( );
                    // Get numeric data from the rows
                    for ( int catRow = row + 6; catRow < row + 11; ++catRow ) {
                        // This class holds all the numeric data
                        var cat = new Category();
                        cat.ID = valueArray[catRow, 2] != null ? ( double ) valueArray[catRow, 2] : 0.0;
                        cat.Name = valueArray[catRow, 5] != null ? valueArray[catRow, 5].ToString ( ) : "";
                        cat.CategA = valueArray[catRow, 10] != null ? ( double ) valueArray[catRow, 10] : 0.0;
                        cat.CategB = valueArray[catRow, 13] != null ? ( double ) valueArray[catRow, 13] : 0.0;
                        cat.CategC = valueArray[catRow, 16] != null ? ( double ) valueArray[catRow, 16] : 0.0;
                        // Add the numeric data to the ExcelData class object
                        eData.Cat.Add ( cat );
                    }
                    // Put the data into the List and then go to the top and do the next sequence
                    excelData.Add ( eData );
                }
            }

                // Close the Workbook.
                xlWorkbook.Close ( false );

            // Relase COM Object by decrementing the reference count.
            Marshal.ReleaseComObject ( xlWorkbook );

            // Close Excel application.
            xlApp.Quit ( );

            // Release COM object.
            Marshal.FinalReleaseComObject ( xlApp );

            Console.ReadLine ( );

        }
    }

    public class ExcelDaata {

        private List<Category> cat = new List<Category>();

        public string NameType { get; set; }
        public string Periods { get; set; }
        public string Name { get; set; }
        public string Currency { get; set; }
        public List<Category> Cat {
            get { return cat; }
            set { cat = value; }
        }
    }

    public class Category {
        public double ID { get; set; }
        public string Name { get; set; }
        public double CategA { get; set; }
        public double CategB { get; set; }
        public double CategC { get; set; }
    }
}

Open in new window

0
 

Author Comment

by:Gas
ID: 41824772
I tested the code and is look like that is ok. At the end I need a function to print what is read to a file. (preferable to create a pdf file and write it there).
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41824872
Hi Gas;

Please close this question by awarding points to my last post. Then please open a new question for your last post. In that new post please be specific of what you need.

Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question