C# code write only first column data in excel

Question

2.21/5 (5 votes)

See more:

Hello Friends...

I m involved in project in which i need to code for reading excel file and save the data in another excel file. But Source excel file can contain any number of columns i.e Somtetimes it may contain 3 columns in file or sometimes it may contain 8 columns in file etc such as folows

Col-A Col-B Col-C
1 3 5
2 4 6

The columns of Source Excel file are dynamic. But I know their datatypes. All are string. And these columns can contain any number of values. All columns have a fixed number of rows to each other. Suppose All columns may have a 2000 rows. I need to save columns in destination file as one below other with values such as follows.

Columns Values
Col-A 1
Col-A 2
Col-B 3
Col-B 4
Col-C 5
Col-C 6

for this i refer the link as below
Generate Excel files without using Microsoft Excel[^]

As per the link i have created following class

C#

class ExcelWriter
    {
        private Stream stream;
        private BinaryWriter writer;

        private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
        private ushort[] clEnd = { 0x0A, 00 };


        private void WriteUshortArray(ushort[] value)
        {
            for (int i = 0; i < value.Length; i++)
                writer.Write(value[i]);
        }

        
        public ExcelWriter(Stream stream)
        {
            this.stream = stream;
            writer = new BinaryWriter(stream);
        }

        public void WriteCell(int row, int col, string value)
        {
            ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
            int iLen = value.Length;
            byte[] plainText = Encoding.ASCII.GetBytes(value);
            clData[1] = (ushort)(8 + iLen);
            clData[2] = (ushort)row;
            clData[3] = (ushort)col;
            clData[5] = (ushort)iLen;
            WriteUshortArray(clData);
            writer.Write(plainText);
        }

        public void WriteCell(int row, int col)
        {
            ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
            clData[2] = (ushort)row;
            clData[3] = (ushort)col;
            WriteUshortArray(clData);
        }

        /// <summary>
        /// Must be called once for creating XLS file header
        /// </summary>
        public void BeginWrite()
        {
            WriteUshortArray(clBegin);
        }

        /// <summary>
        /// Ends the writing operation, but do not close the stream
        /// </summary>
        public void EndWrite()
        {
            WriteUshortArray(clEnd);
            writer.Flush();
        }


    }

private void button1_Click(object sender, EventArgs e)
        {
            string _filename = @"E:\Import File\Working\t_C15.csv";
            StreamReader ObjStreamReader = new StreamReader(File.OpenRead(_filename));
            string[] line = ObjStreamReader.ReadLine().Split(';');
            string[] machinename = _filename.Split('_');
            for (int i = 2; i < line.Length; i++)
            {
                line[i] = string.Concat(line[i].Trim('"'), "_" ,machinename[machinename.Length - 1].Remove(machinename[machinename.Length - 1].IndexOf(".csv")));
            }
            
            List<system.collections.generic.list><string>> ObjListColumns = new List<list><string>>();
            //here 
            for (int i = 1; i < line.Length; i++)
            {
                ObjListColumns.Add(new List<string>());
            }

            string[] values = null;
            while (!ObjStreamReader.EndOfStream)
            {
                values = null;
                values = ObjStreamReader.ReadLine().Split(';');
                for (int i = 0; i < ObjListColumns.Count; i++)
                {
                    ObjListColumns[i].Add(values[i + 1]);
                }
            }
            ObjStreamReader.Close();

            for (int i = 0; i < ObjListColumns[0].Count; i++)
            {
                ObjListColumns[0][i] = ObjListColumns[0][i].Trim('"').Trim('\\').Trim('"');
            }
            for (int i = 0; i < ObjListColumns.Count; i++)
            {
                for (int j = 0; j < ObjListColumns[i].Count; j++)
                {
                    ObjListColumns[i][j] = ObjListColumns[i][j].Trim('"');
                }
            }
            FileStream stream = new FileStream(@"E:\Import File\Working\abc.csv", FileMode.OpenOrCreate);
            ExcelWriter writer = new ExcelWriter(stream);
            writer.BeginWrite();
            writer.WriteCell(0, 0, "[Data]");
            writer.WriteCell(1, 0, "Tagname");
            writer.WriteCell(1, 1, "TimeStamp");
            writer.WriteCell(1, 2, "Value");
            writer.WriteCell(1, 3, "DataQuality");
            DateTime starttime = DateTime.Now;
            for (int i = 2; i < line.Length; i++)
            {
                
                for (int j = 0; j < ObjListColumns[i-1].Count; j++)
                {

                    writer.WriteCell(j + 2, 0, line[i].ToString());
                    writer.WriteCell(j + 2, 1, ObjListColumns[0][j].ToString());
                    writer.WriteCell(j + 2, 2, ObjListColumns[i - 1][j].ToString());
                    writer.WriteCell(j + 2, 3, "Good");
                }
            }
            writer.EndWrite();
            stream.Close();
            DateTime endtime = DateTime.Now;
            MessageBox.Show("Compeleted in ");

        }

But It writes only first column data as follows,

Columns Values
Col-A 1
Col-A 2

further columns data are not being written.Please help me

Posted 27-Apr-15 1:18am

TAUSEEF KALDANE

Updated 3-May-15 20:42pm

Sushil Mate

v2

Add a Solution

Comments

Joan Magnet 27-Apr-15 9:55am

have you debug which is the ObjListColumns[i-1].Count and line.Length value?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shashank Bisen · Answer 1 · 2015-07-02T20:32:00

Solution 3

I hope this helps

http://www.codeproject.com/Tips/613782/Read-and-Write-Excel-File-Dynamically

Posted 2-Jul-15 20:32pm

Shashank Bisen

Alek Massey · Answer 2 · 2015-04-27T16:14:00

Solution 1

You are only reading the first line in the file.

string[] line = ObjStreamReader.ReadLine().Split(';');

Issue ReadLine in a loop(assigning it to line) and processing the result, till it returns an empty sting[] array.

Posted 27-Apr-15 16:14pm

Alek Massey

Thomas Nielsen - getCore · Answer 3 · 2015-05-07T21:11:00

Appears you're overcomplicating the matter. In fact .csv is not an excel thing, it's a windows convention meaning something like 'comma seperated verdices' or something along those linces and means that it is a file containing rows with column values seperated by commas.
Now you're using semicolon and that's because windows use the ListSeperator from the Environment table which is culture specific, like say in DK we'd use semicolon because we have already spent our comma on numbers with digits, whilst in UK the use the dot between digits and therefore can actually use a comma.

Anyway, if you consider that you don't have to do any voodoo and only must make a file in that format which incidentally is 'stolen' by excel file association which will give you an Excel icon in windows and therefore will work in your setup, consider a simper approach like thisone.

C#

public class ExcelBot
    {
        public static void Reorganize(string source, string destination, Encoding encoding, char seperator = ';')
        {
            string[] columns;
            List<StringBuilder> columnData;
            string content;
            using (var fs = new FileStream(source, FileMode.Open, FileAccess.Read))
            {
                var reader = new StreamReader(fs, encoding);

                string header = reader.ReadLine();
                if (string.IsNullOrEmpty(header))
                    throw new ArgumentException("Source doesn't appear to have the expected format: " + source);

                columns = header.Split(seperator);
                columnData = new List<StringBuilder>();
                foreach (var clmn in columns)
                {
                    columnData.Add(new StringBuilder());
                }
                content = reader.ReadToEnd();
            }
            
            string[] rows = content.Split("\n".ToCharArray());
            for (int i = 0; i < rows.Length; i++)
            {
                string[] rowColumns = rows[i].TrimEnd().Split(seperator);
                if (rowColumns.Length < columns.Length)
                    continue;
                for (int j = 0; j < columns.Length; j++)
                {
                    columnData[j].AppendLine(columns[j] + seperator + rowColumns[j]);
                }
            }

            var sb = new StringBuilder();
            sb.AppendLine("Column" + seperator + "Value");
            foreach (var builder in columnData)
                sb.Append(builder.ToString());

            using (var fs = new FileStream(destination, FileMode.Create))
            {
                var writer = new StreamWriter(fs, encoding);
                writer.Write(sb.ToString());
                writer.Flush();
            }
            
        }
    }

Ravi Prakash the Code-Player · Answer 4 · 2015-07-07T20:17:00

Hey its very simple, you can use excel dll which provide you the feature to convert your excel or csv into dataset directly. Follow the below code:

C#

string ext = Path.GetExtension(FileExport.FileName).ToLower();// get the extension of file name i.e. csv or xls

                                Stream fileStream = new MemoryStream(FileExport.FileBytes); // converting the excel file into binary

                                //1. Reading Excel file
                                IExcelDataReader excelReader;

                                if (ext.Trim() == ".xls")
                                {
                                    excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream);
                                }
                                else //Added support for Excel 2007 files.
                                {
                                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
                                }
                               
                                //2. DataSet - Create column names from first row. Use this if you want to make your first row as a data column.
                                excelReader.IsFirstRowAsColumnNames = true;

                                //3. The result of each spreadsheet will be created in the resultDS.Tables
                                DataSet excelDataSet = excelReader.AsDataSet(); // here it store the excel data in a dataset.

                                //4. DataTable - Get the DataTable from DataSet
                                ExcelImportData = excelDataSet.Tables[0];

                                //5. Free resources (IExcelDataReader is IDisposable)
                                excelReader.Close();

Once the data came in dataset you can use it as the way you want. You can use a loop to extract the data row by row or column by column or you can directly save to another excel file.
you can download the excel dll fro here http://www.dllme.com/dll/files/microsoft_office_tools_excel_dll.html[^]
In case of any issue please let me know.

C# code write only first column data in excel

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0