import export data from DataTable

 CSV

1. return a string

public string ToStringAsCSV (DataTable dt, string delimited)
        {
            string text = "";

            StringWriter sw = new StringWriter();

            // First we will write the headers.

            //DataTable dt = m_dsProducts.Tables[0];

            int iColCount = dt.Columns.Count;

            for (int i = 0; i < iColCount; i++)
            {
                sw.Write(dt.Columns[i]);

                if (i < iColCount - 1)
                {
                    sw.Write(delimited);
                }
            }

            sw.Write(sw.NewLine);

            // Now write all the rows.

            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    //if (!Convert.IsDBNull(dr[i]))
                    //{
                    string s = dr[i].ToString().Trim();
                    sw.Write(s);
                    //}

                    if (i < iColCount - 1)
                    {
                        sw.Write(delimited);
                    }
                }
                sw.Write(sw.NewLine);
            }

            text = sw.ToString();

            return text;
        }

2  create a CSV file

private void DataTableExportCSV(DataTable dt, string delimited, string strFilePath)
        {
            // Create the CSV file to which grid data will be exported.

            StreamWriter sw = new StreamWriter(strFilePath, false);

            // First we will write the headers.

            //DataTable dt = m_dsProducts.Tables[0];

            int iColCount = dt.Columns.Count;

            for (int i = 0; i < iColCount; i++)
            {
                sw.Write(dt.Columns[i]);

                if (i < iColCount - 1)
                {
                    sw.Write(delimited);
                }
            }

            sw.Write(sw.NewLine);

            // Now write all the rows.

            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    //if (!Convert.IsDBNull(dr[i]))
                    //{
                    string s = dr[i].ToString().Trim();
                    sw.Write(s);
                    //}

                    if (i < iColCount - 1)
                    {
                        sw.Write(delimited);
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }

 

XML

1. return a string

public string ToStringAsXml(DataTable dt)
        {
            StringWriter sw = new StringWriter();
            dt.WriteXml(sw, XmlWriteMode.IgnoreSchema);

            string s = sw.ToString();

            return s;
        }

 

File import export manager CSV, XML

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using riteweigh.core.interfaces;
using SimpleEventBroker;
using riteweigh.core.framework;
using System.Data;
using System.IO;

namespace riteweigh.data.importExport
{
    public class FileDataExportImportManager : IFileDataExportImportManager
    {
        #region IManager Members
        [Publishes(RWCommands.UserNotAuthorised)]
        public event EventHandler NotAuthorised;

        #region private fields
        private string[] _permissions = { };
        #endregion

        public string ModuleName
        {
            get { return "FileDataExportImport"; }
        }

        public string[] GetModulePermissions()
        {
            return _permissions;
        }

        #endregion

        #region IFileDataExportImportManager Members

        public void ExportToFile(DataSet dataSet, string filePath, string format, string delimiter)
        {
            var data = SerialiseDataSet(dataSet, format, delimiter);
            using (var fileStream = new StreamWriter(filePath, false))
            {
                fileStream.Write(data);
                fileStream.Close();
            }
        }


        public string SerialiseDataSet(DataSet dataSet, string format, string delimiter)
        {
            /// Code migrated from UserControl_ExportData.cs
            /// 
            var data = string.Empty;

            if (format.Equals("CSV", StringComparison.InvariantCultureIgnoreCase))
                data = GetCSV(dataSet, delimiter);
            else if (format.Equals("XML", StringComparison.InvariantCultureIgnoreCase))
                data = GetXML(dataSet);

            return data;
        }



        public DataSet ImportFromFile(string filePath, string format, string delimiter)
        {
            // Code migrated from ImportDataForm.cs

            DataSet ds = new DataSet();

            var dt = new DataTable();

            if (format.Equals("CSV", StringComparison.InvariantCultureIgnoreCase))
            {
                dt = GetDTFromCSV(filePath, delimiter, true);
            }
            else if (format.Equals("XML", StringComparison.InvariantCultureIgnoreCase))
            {
                dt = GetDTFromXML(filePath);
            }

            dt.TableName = "columns!";

            if (dt.Rows.Count == 0)
            {
                throw new Exception("file is empty!");
            }

            ds.Tables.Add(dt);

            return ds;
        }

        #endregion

        #region private methods

        private string GetCSV(DataSet dataSet, string delimiter)
        {
            // Create the CSV file to which grid data will be exported.
            string text = string.Empty;
            using (StringWriter sw = new StringWriter())
            {

                // First we will write the headers.
                var dt = dataSet.Tables[0];

                int iColCount = dt.Columns.Count;

                for (int i = 0; i < iColCount; i++)
                {
                    sw.Write(dt.Columns[i]);

                    if (i < iColCount - 1)
                    {
                        sw.Write(delimiter);
                    }
                }

                sw.Write(sw.NewLine);

                // Now write all the rows.

                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        string s = dr[i].ToString().Trim();
                        sw.Write(s);

                        if (i < iColCount - 1)
                        {
                            sw.Write(delimiter);
                        }
                    }
                    sw.Write(sw.NewLine);
                }

                text = sw.ToString();

                sw.Close();
            }

            return text;
        }

        private string GetXML(DataSet dataSet)
        {
            string text = string.Empty;
            using (StringWriter sw = new StringWriter())
            {
                dataSet.WriteXml(sw);
                text = sw.ToString();

                sw.Close();
            }

            return text;
        }

        private DataTable GetDTFromCSV(string filePath, string delimiter, bool isRowOneHeader)
        {
            DataTable dt = new DataTable();
            string[] spliters = new string[] { delimiter };

            String[] linesArray = File.ReadAllLines(filePath);

            //if no data in file ‘manually’ throw an exception
            if (linesArray.Length == 0)
            {
                throw new Exception("File is empty!");
            }

            List<string> lines = linesArray.ToList<string>();

            for (int i = lines.Count - 1; i >= 0; i--)
            {
                if (string.IsNullOrEmpty(lines[i].Trim()))
                {
                    lines.Remove(lines[i]);
                }
            }

            if (lines.Count == 0)
            {
                throw new Exception("File is empty lines!");
            }

            String[] lineValue = lines[0].Split(spliters, StringSplitOptions.None);

            int Cols = lineValue.Length;

            int index = 0;

            if (isRowOneHeader)
            {
                for (int i = 0; i < Cols; i++)
                    dt.Columns.Add(lineValue[i].ToLower(), typeof(string));

                index = 1;
            }

            DataRow Row;

            for (int i = index; i < lines.Count; i++)
            {
                lineValue = lines[i].Split(spliters, StringSplitOptions.None);
                Row = dt.NewRow();
                for (int f = 0; f < Cols; f++)
                    Row[f] = lineValue[f];

                dt.Rows.Add(Row);
            }

            return dt;
        }

        private DataTable GetDTFromXML(string filePath)
        {
            DataTable dt = new DataTable();

            DataSet ds = new DataSet();
            ds.ReadXml(filePath);

            if (ds.Tables[0].Rows.Count == 0)
            {
                throw new Exception("File is empty!");
            }

            dt = ds.Tables[0].Copy();
            return dt;
        }

        #endregion
    }
}

 

// Get DataSet from excel sheets

public static DataSet importExcelToDT(string fileName, bool firstRowIsHeader)
        {
            DataSet ds = new DataSet();
            try
            {
                Excel.Application xlApp = new Excel.ApplicationClass();
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                int rCnt = 0;
                int cCnt = 0;
                try
                {
                   
                    for (int i = 1; i <= xlWorkBook.Worksheets.Count; i++)                      // loop sheets
                    {

                        Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(i);

                        string sheetName = xlWorkSheet.Name;
                        //if (sheets.Contains("All"))
                        //{
                        //    // read all sheets
                        //}
                        //else
                        //{
                        //    if (!sheets.Contains(sheetName))
                        //    {
                        //        log.Info("Programm skip reading sheet: " + sheetName);
                        //        continue;
                        //    }
                        //}

                        //log.Info("start reading " + sheetName);


                        Excel.Range range = xlWorkSheet.UsedRange;
                        if (range.Rows.Count == 1 && range.Columns.Count == 1)                   // skip sheet which no data
                        {
                            //log.Info("Programm skip reading sheet: " + sheetName);
                            continue;
                        }

                        DataTable dt = new DataTable(sheetName);
                        try
                        {
                            

                            int lineNo = 0;

                            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt ++)                    // loop rows 
                            {
                                lineNo = rCnt;

                                 skip the some rows (comment) by first cell of each row
                                if ((range.Cells[rCnt, 1] as Excel.Range).Value2 == null)
                                {
                                    continue;
                                }

                                string firsCell = (range.Cells[rCnt, 1] as Excel.Range).Value2.ToString();
                                if (firsCell.StartsWith("#") )//|| (String.IsNullOrEmpty(firsCell)) )   
                                {
                                    continue;
                                }

                                set columns header
                                //if (firsCell.StartsWith("!"))       //if (rCnt == 1)
                                //{
                                //    for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                                //    {
                                //        string title = ((string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2).Trim('!', ' ');
                                //        dt.Columns.Add(new DataColumn(title));
                                //    }
                                //    continue;
                                //}

                                if (firstRowIsHeader)     // set columns header
                                {
                                    
                                    for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                                    {
                                        if (((range.Cells[rCnt, cCnt] as Excel.Range).Value2) == null)
                                        {
                                            break;
                                        }
                                        string title = ((string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2).Trim('!', ' ');
                                        dt.Columns.Add(new DataColumn(title));
                                    }
                                    firstRowIsHeader = false;
                                    continue;
                                }

                                //string[] array = new string[range.Columns.Count];       based on excel range
                                //for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) 

                                string[] array = new string[dt.Columns.Count];       // based on DataTable range
                                for (cCnt = 1; cCnt <= dt.Columns.Count; cCnt++)             // loop columns 
                                {
                                    object res;
                                    try
                                    {
                                        res = ((range.Cells[rCnt, cCnt] as Excel.Range).Value2);
                                        if (res != null)
                                        {
                                            array[cCnt - 1] = res.ToString().Trim(); 
                                        }
                                        else
                                        {
                                            array[cCnt - 1] = "";
                                        }

                                    }
                                    catch (Exception e)
                                    {
                                        //log.Error("Read EXCEL Error: " + sheetName + " line + " + lineNo);
                                        Console.WriteLine(e.Message);
                                    }
                                }

                                DataRow dr;
                                dt.BeginLoadData();
                                dr = dt.LoadDataRow(array, true);
                                dt.EndLoadData();

                            }
                        }
                        finally
                        {
                            releaseObject(xlWorkSheet);
                            //log.Info("finish reading " + sheetName + " sheet.");
                        }

                        ds.Tables.Add(dt);
                    }

                    return ds;
                }
                finally
                {

                    //***********
                    xlApp.Quit();
                    //************

                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);

                    //log.Info("Finish reading Excel");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                //log.Error(e.Message);
            }

            return null;
        }


        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }