6 Replies - 303 Views - Last Post: 31 October 2017 - 12:59 AM Rate Topic: -----

#1 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Column Formatting not getting applied in Excel

Posted 30 October 2017 - 08:32 AM

For about a day now I have been trying every fix from Stack Overflow to try to understand why my excel data isn't being formatted as a number on the range I am indicating.

I have verified that the data in each index in the array is a number that doesn't contain apostrophes or text.

for (int i = 1; i < csvContentsList.Count + 1; i++)
                {
                    string[] csvLine = csvContentsList[i-1].Split(',');

                    //Populate by row...
                    Excel.Range range = (Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[i, 1], xlWorkSheet.Cells[i, csvLine.Length]];
                   
                    
                    range.set_Value(Type.Missing, csvLine);
                    range.NumberFormat = "@";
                    if (i > 5)
                    {
                        break;
                    }
                   // releaseObject(range);
                   // columnCount = csvLine.Length -1;
                }
xlWorkBookTemplate.Save();
xlWorkBookTemplate.Close(false, fileName, misValue);





I am setting the value of each row with an array and I checked all 5 iterations of the array and there are no values that are text. They are all numbers and the number format should be applying here. I am not understanding why the format doesn't take. In my .set_Value method I have also ran this same program with the other available enums as the first parameter.

This post has been edited by jphoc13: 30 October 2017 - 08:43 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Column Formatting not getting applied in Excel

#2 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,775
  • Joined: 12-December 12

Re: Column Formatting not getting applied in Excel

Posted 30 October 2017 - 09:04 AM

@ is a text placeholder. You probably want "#" or "General".
Was This Post Helpful? 1
  • +
  • -

#3 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Column Formatting not getting applied in Excel

Posted 30 October 2017 - 09:11 AM

Ok so I did solve this problem. But the formatting still doesn't work as it should.

Instead of writing it as a string to the excel sheet and formatting it as a number, I just converted it to a double before writing it to the excel sheet, thereby not relying on the formatting.

Not what I want to do but it works!
Was This Post Helpful? 0
  • +
  • -

#4 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Column Formatting not getting applied in Excel

Posted 30 October 2017 - 09:22 AM

View Postandrewsw, on 30 October 2017 - 09:04 AM, said:

@ is a text placeholder. You probably want "#" or "General".

Oh wow, that works. Simple things....
Was This Post Helpful? 0
  • +
  • -

#5 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Column Formatting not getting applied in Excel

Posted 30 October 2017 - 09:46 AM

Correction: The method Andrew gave me works great on a per cell basis, which is very expensive for the amount of data I am using.

If I try to do it like this to write an entire row:

 Excel.Range range = (Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[i, 1], xlWorkSheet.Cells[i, csvLine.Length]];

                    range.NumberFormat = "#";
                    range.Value2 = csvLine;


The format won't apply. I will keep investigating...

This post has been edited by jphoc13: 30 October 2017 - 09:50 AM

Was This Post Helpful? 0
  • +
  • -

#6 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Column Formatting not getting applied in Excel

Posted 30 October 2017 - 11:52 AM

I found a way to get by the formatting for populating an excel sheet by row.

I was using a string array to populate the row and attempting to format the row as a number, this wasn't working.

So I remove the string array and did an object array and it has worked the way I need it.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,775
  • Joined: 12-December 12

Re: Column Formatting not getting applied in Excel

Posted 31 October 2017 - 12:59 AM

In post #5 you are formatting before entering the values, putting the values in first is usually better. But you could also wait until all the values are submitted, and you know how many rows you've filled, and then format the whole area (rather than row by row).

(Of course, if you have a csv then you could just open the file, whether automated or not.)

(If you record importing a csv you can specify the format of each column during the import.)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1