Import Csv Files to Excel and replace comma that separates by Columns

Question

Hello i need to import some csv files populated from a linq query result using LinqToCsv liibrary into Excel workbook by changing the comma to columns between data?

`//Generate CSV Files for each item in the Listview
        CsvFileDescription outpCsvFileDescription = new CsvFileDescription
        {
            SeparatorChar = ',',
            FirstLineHasColumnNames = true
        };

        for (int i = 0; i < listView.Items.Count; i++)
        {
            dynamic currentItemInLoop = listView.Items[i];
            //On est obligé de cast cette variable en String pour qu'on puisse l'utiliser dans le LinQ
            //currentItemInLoop.nameAttribute => MainWindow.xaml -> ListView x:Name="listView" -> Columns...
            String frs = (String)currentItemInLoop.Name;
            DateTime myDate = (DateTime) currentItemInLoop.TransactionDate;
            var infoEcheances = from f in db.F_ECHEANCES
                                join c in db.F_LECRITURES on f.ECH_No equals c.ECH_No
                                where
                    f.ECH_Intitule == frs &&
                    EntityFunctions.TruncateTime(f.cbModification) ==
                    EntityFunctions.TruncateTime(DatePicker.SelectedDate)
                select
                    new 
                    {
                        f.ECH_DateEch,
                        f.ECH_RefPiece,
                        f.ECH_Libelle,
                        c.LEC_Montant,
                        f.ECH_Montant
                    };

            CsvContext cc = new CsvContext();
            string myPath = @"C:\Users\DefaultAccount\Desktop\Projet Top Of Travel\FichiersCSV\";
            string filename = string.Format(frs);
            filename = Regex.Replace(filename + " " + myDate, @"[^0-9a-zA-Z]", " ");
            string finalPath = System.IO.Path.Combine(myPath, filename + ".csv");
            cc.Write(infoEcheances, finalPath, outpCsvFileDescription);`

Possible duplicate of http://stackoverflow.com/questions/26648641/c-sharp-convert-csv-to-xls-using-existing-csv-file — Murray Foxcroft, May 25 '16 at 13:18
Excel doesn't open CSV files, it *imports* them using localized defaults. In some countries, the default separator is `;`. You can use different settings by going to the "Data" menu. To avoid this, Instead of creating a CSV, use EPPLus or a similar library to create an `xlsx` file. Another option is to simply create the file with the separator you want. — Panagiotis Kanavos, May 25 '16 at 13:21

AnthonyLambert · Answer 1 · 2016-05-27T09:49:29.370

You can do it directly into Excel from c# using COM.

using Excel = Microsoft.Office.Interop.Excel; 

public class ExcelReports
{
    public Excel.Application excelApp;
    public Excel.Workbook excelWorkbook;
    public Excel.Worksheet excelWorksheet;
    public int row = 1;
    public int col = 1;

    public ExcelReports( String fullName )
    {
        excelApp = new Excel.Application();

        Excel.Workbook newWorkbook = excelApp.Workbooks.Add();
        excelWorkbook = excelApp.ActiveWorkbook;

        excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets.Add();
        excelWorksheet.Cells.ClearContents();
        excelWorksheet.Cells[row, col]  = "hello";
  }

}

code to set a cell.... cut from my code... you would change this to output each line from yr array. It may need massaging to work. There are lots of extensive examples on the web. There are some optimizations to make this work fast.

Excel will also import CSV files and allow you some control over what the delimiters are etc. but this process is manual.

Import Csv Files to Excel and replace comma that separates by Columns

1 Answers1