Using c# How to convert csv file into excel with templates ?

Using c# How to convert csv file into excel with excel template ?
Bharat GuruAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amilie wilsonCommented:
You can try this code to import CSV file into Excel using CSV may be it will resolve your problem:

// Load CSV file.
var csvFile = new ExcelFile();
csvFile.LoadCsv("Database.csv", CsvType.CommaDelimited);

// Load XLS file.
var excelTemplate = new ExcelFile();
excelTemplate.LoadXls("Template.xls");

// Extract data from CSV file to DataTable.
var dataTable = csvFile.Worksheets[0].CreateDataTable(ColumnTypeResolution.Auto);
// Insert DataTable to XLS file at position A1.
excelTemplate.Worksheets[0].InsertDataTable(dataTable, "A1", false);

// Save to new XLS file.
excelTemplate.SaveXls("Database.xls");
0
Bharat GuruAuthor Commented:
What reference I haves to add
0
Bharat GuruAuthor Commented:
I'm getting namespace ExcelFile is missing
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Bharat GuruAuthor Commented:
Hi Amilie
   I am getting namespace ExcelFile is missing  error  please post whole file
0
amilie wilsonCommented:
Sorry to say dear but currently I don’t have that file from where I have provide you the above mentioned code try this one may be your problem will be resolved.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Globalization;

namespace ExcelTemplateFiller
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excelApp = null;
            Excel.Worksheet xlSheet;
            Excel.Workbook xlBook;
            try
            {
                excelApp = new Excel.Application();
                string templatePath = @"..\..\ExcelTemplate\FTPLogReport.xltx";
                string CSVPath = @"..\..\Input\08Oct2013_FTPLogReport.csv";
                string OutputPath = @"..\..\Output\08Oct2013_FTPLogReport.xlsx";

                templatePath = Path.GetFullPath(templatePath);
                CSVPath = Path.GetFullPath(CSVPath);
                OutputPath = Path.GetFullPath(OutputPath);

                xlBook = (Excel.Workbook)excelApp.Workbooks.Open(templatePath, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);

                int row = 2;

                DataTable dtCSV = CsvFileToDatatable(CSVPath, true);

                foreach (DataRow dr in dtCSV.Rows)
                {
                    excelApp.Cells[row, 1] = dr[0].ToString();
                    excelApp.Cells[row, 2] = dr[1].ToString();
                    excelApp.Cells[row, 3] = dr[2].ToString();
                    excelApp.Cells[row, 4] = dr[3].ToString();
                    excelApp.Cells[row, 5] = dr[4].ToString();

                    row++;
                }



                xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
                xlSheet.Name = "08Oct2013";

                object oFilename = OutputPath;
                object oFileFormat = Excel.XlFileFormat.xlWorkbookDefault;
                object oPassword = Missing.Value;
                object oWriteResPassword = Missing.Value;
                object oReadOnlyRecommended = false;
                object oCreateBackup = false;

                Excel.XlSaveAsAccessMode AccessMode = Excel.XlSaveAsAccessMode.xlNoChange;
                object oConflictResolution = false;
                object oAddToMru = true;
                object oTextCodepage = Missing.Value;
                object oTextVisualLayout = Missing.Value;
                object oSaveChanges = true;
                object oRouteWorkbook = Missing.Value;

                excelApp.DisplayAlerts = false;
                xlBook.SaveAs(oFilename, oFileFormat, oPassword, oWriteResPassword, oReadOnlyRecommended, oCreateBackup, AccessMode, oConflictResolution, oAddToMru, oTextCodepage, oTextVisualLayout);
                xlBook.Close(oSaveChanges, oFilename, oRouteWorkbook);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                System.Threading.Thread.Sleep(1000);
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                System.Threading.Thread.Sleep(30000);
            }

        }

        private static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)
        {
            string header = "No";
            string sql = string.Empty;
            DataTable dtCSV = null;
            string pathOnly = string.Empty;
            string fileName = string.Empty;

            try
            {
                pathOnly = Path.GetDirectoryName(path);
                fileName = Path.GetFileName(path);

                sql = @"SELECT * FROM [" + fileName + "]";

                if (IsFirstRowHeader)
                {
                    header = "Yes";
                }

                using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + header + "\""))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                        {
                            dtCSV = new DataTable();
                            dtCSV.Locale = CultureInfo.CurrentCulture;
                            adapter.Fill(dtCSV);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dtCSV;
        }
    }
}
reference from: http://stackoverflow.com/questions/11454392/externally-create-an-excel-graph-from-a-csv-file

you can go through this also if you want to convert csv to excel without using c#- http://www.filesculptor.com/convert-csv-file-excel-xls/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bharat GuruAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.