Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

Problem when read a file in C#

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
Gas
Asked:
Gas
  • 9
  • 7
1 Solution
 
Fernando SotoCommented:
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
 
GasAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
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!

 
GasAuthor Commented:
Any other solution which is not require any paid program?
0
 
GasAuthor Commented:
Can anybody help me in this issue? (without using tools which is needed to buy)
0
 
Fernando SotoCommented:
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
 
GasAuthor Commented:
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
 
Fernando SotoCommented:
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
 
GasAuthor Commented:
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
 
Fernando SotoCommented:
That depends on what information you want to retrieve?
0
 
GasAuthor Commented:
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
 
GasAuthor Commented:
Anyone to help me for this issue? Am i clear about the problem ?
0
 
Fernando SotoCommented:
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
 
GasAuthor Commented:
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
 
Fernando SotoCommented:
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
 
GasAuthor Commented:
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
 
Fernando SotoCommented:
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.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now