Displaying CSV File in excel from within a .net application

[yoavyoavyoav]

Hi There ,

I have an application that creates a csv file and I want it to display it in excel after creation.

I've used to following code - which works well :

using ExcelInterop = Microsoft.Office.Interop.Excel;

  ExcelInterop._Worksheet ExcelWorkSheet; //represents the resulting worksheet.

            Microsoft.Office.Interop.Excel.Application excelApp = null;
            try
            {
                excelApp = new ExcelInterop.ApplicationClass();

               
                excelApp.Visible = true;

                string path = txtOutputPath.Text; //full file path is given.
                excelApp.Workbooks.Open(path, 0, false, 2, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                ExcelWorkSheet = (ExcelInterop._Worksheet)(excelApp.ActiveWorkbook.ActiveSheet);
                ExcelWorkSheet.Columns.AutoFit();
                ExcelWorkSheet.Columns.HorizontalAlignment = ExcelInterop.XlHAlign.xlHAlignLeft; //aligns values to the left.
                ExcelWorkSheet._DisplayRightToLeft = 0; //makes the excel to display as left-to-right.
            }
            catch (Exception ex) //Used to catch exception of type "System.Runtime.InteropServices.COMException"
            {
                if (excelApp != null)
                    excelApp.Quit();
                MessageBox.Show(ex.Message);
            }

(The code above is just a segment)

The thing is - my CSV file contains some fields with long numbers. I want this numbers to be displayed as text (WYSIWYG) - but Excel chagnes them - for eg. : 909490266168 ----> 9.0949E+11


I want to add something to my code that'll set the format of all cells to be "Text" (So everything will be displayed just as it is in the CSV file - not altering anything).


I tried : ExcelWorkSheet. Cells.NumberFor mat = "Text" but this did not work properly and messed up some Date cells.


I'm not so familier with Excel Programming , So help will be appreciated. Thanks in advance.

[camel]

I believe you need to go after Range.NumberFor mat, i.e., for a particular column set the Range("A1").Num berFormat = "0.0". You can set this after loading the data with your existing code. Alternatively you could preformat a template if it will always be the same column that needs the special formatting.

[yoavyoavyoav]

I believe you need to go after Range.NumberFor mat, i.e., for a particular column set the Range("A1").Num berFormat = "0.0". You can set this after loading the data with your existing code. Alternatively you could preformat a template if it will always be the same column that needs the special formatting.

Thanks for your reply.
Unfortunately, my CSV files are not constant , So I basiclly want Excel to just display what it sees in the CSV (not doing any formatting of its own). I can't tell which column resides in each range , since as I just said , the application generates many different CSV files.

[camel]

Any dynamic approach in terms of knowing nothing of the structure of the input data columns presents a challenge, which is why Excel's own algorithms of inspecting a few rows and making a guess on structure being similar for folowing records is not always perfect (though usually displays something!)

If you can't go for a specific range at the outset I would look at applying a filter expression to find the cells of interest and then apply the NumberFormat to the resulting cells (which can be handled via a Range object).

You could in principle iterate every populated cell in the active workbook with a switch statement checking its likely datatype and length, i.e., to avoid date patterns, though this would be fairly expensive compared to a filter approach.

Another option is to load the populated data into an ADO data structure where you would have opportunity to apply an appropriate datatype to columns containing the values, you could then populate Excel direct from the recordset (Excel.CopyReco rdset). For this to work you still need to come up a with rules to work out best datatypes based on data coming in, just using different tools

[poolboi]

hi,
may i know what code you are using?
'cos i also have an application that display my data as a csv file and i want to display it in excel without the commas?
any idea how i can achieve this?

[romcab]

Hi,

I think I have encountered that problem before. What i did was to change data I read from strings -> double or integer then set the cell numberformat. This works for me.

[poolboi]

ok so basically the commas u outsput as intergers instead of strings so that all the commas will be gone alright..
but u got any knowledge of how i might be able to get all the datas from csv file to an excel?
cos i got this csv file generated from an applications with columns of information and i need to put it in excel for easy referencing