Solved

Problem when read a file in C#

Posted on 2016-09-24
17
62 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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:Gas
Comment Utility
Any other solution which is not require any paid program?
0
 

Author Comment

by:Gas
Comment Utility
Can anybody help me in this issue? (without using tools which is needed to buy)
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Gas
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
That depends on what information you want to retrieve?
0
 

Author Comment

by:Gas
Comment Utility
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
Comment Utility
Anyone to help me for this issue? Am i clear about the problem ?
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now