Solved

Problem when read a file in C#

Posted on 2016-09-24
17
72 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
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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 62

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 62

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 62

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 62

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 62

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 62

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 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

8 Experts available now in Live!

Get 1:1 Help Now