Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem when read a file in C#

Posted on 2016-09-24
17
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 64

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 64

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 64

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 64

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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

704 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