How can I write a string array to Excel file, with a tab delimiter?

Question

I'm creating a small app that reads a tab delimited text file, makes a few changes, and then creates an Excel 2007 .xlsx file. I'm having trouble figuring out how to take the lines from a string array and write them to the Excel file, using the tabs to break the line up into columns. I hope that made sense.

I have string Lines[] that contains something like this:

Item1\tItem2\tItem3\tItem4
ItemA\tItemB\tItemC\tItemD
Item5\tItem6\tItem7\tItem8

I'd like to create an Excel file that looks like this:

A      B      C      D
Item1  Item2  Item3  Item4
ItemA  ItemB  ItemC  ItemD
Item5  Item6  Item7  Item8

I tried the following, but it just puts the first line from Lines[] into each row, and doesn't separate into columns:

string Lines[] = GetLines();

Excel.Application xlApp;
Excel.Workbook xlWb;
Excel.Worksheet xlWs;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlWb = xlApp.Workbooks.Add(misValue);
xlWs = (Excel.Worksheet)xlWb.Worksheets.get_Item(1);

Excel.Range c1 = (Excel.Range)xlWs.Cells[2, 1];
Excel.Range c2 = (Excel.Range)xlWs.Cells[2 + lines.Length, 1];

Excel.Range range = xlWs.get_Range(c1, c2);

range.Value = lines;
range.TextToColumns(                 
    range,
    Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
    Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone,
    false,
    true    // This is flag to say it is tab delimited
);

xlApp.Visible = true;

Any advice would be appreciated! Thank you!

Here's the output I'm currently getting:

A                           B    C    D
Item1\tItem2\tItem3\tItem4
Item1\tItem2\tItem3\tItem4
Item1\tItem2\tItem3\tItem4

EDIT: I've updated my code with @jiverson's suggestion and the line is now separated into columns in Excel, but the first line from Lines[] still appears in every row in Excel. Why?

EDIT #2: Here's the updated working code:

Excel.Application xlApp;
Excel.Workbook xlWb;
Excel.Worksheet xlWs;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlWb = xlApp.Workbooks.Add(misValue);
xlWs = (Excel.Worksheet)xlWb.Worksheets.get_Item(1);

int currentRow = 2;

string[] lines = GetLines();

for (int i = 0; i < lines.Length; i++)
{
    string line = lines[i]; //get the current line

    string[] values = line.Split('\t'); //split the line at the tabs

    //
    // .. i do some things to specific values here ..
    //

    lines[i] = String.Join("\t", values); //put the updated line back together

    Excel.Range currentRange = (Excel.Range)xlWs.Cells[currentRow, 1]; //get the next row

    currentRange.Value = lines[i];  //write the line to Excel

    currentRow++;
}

Excel.Range c1 = (Excel.Range)xlWs.Cells[2, 1]; //get the first cell
Excel.Range c2 = (Excel.Range)xlWs.Cells[2 + lines.Length, 1]; //get the last cell

Excel.Range range = xlWs.get_Range(c1, c2);  //set the range as the used area

range.TextToColumns( //split the row into columns
    range,
    Excel.XlTextParsingType.xlDelimited,
    Excel.XlTextQualifier.xlTextQualifierNone,
    false,
    true    // This is flag to say it is tab delimited
);

Split the string using '\t' and write each value to each corresponding cell — chancea
– chancea, Commented Jul 25, 2013 at 16:49

jiverson · Accepted Answer · 2013-07-25 17:41:28Z

2

Loop through to add each line and then use text to columns after you set the range value:

    for (int i = 0; i < range.Rows.Count; i++) {
        range.Rows[i].Value = lines[i];
        range.Rows[i].TextToColumns(
            range.Rows[i],
            Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
            Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone,
            false,
            true
        );          
    }

MSDN Reference

edited Jul 25, 2013 at 17:41

answered Jul 25, 2013 at 17:03

jiverson

1,24412 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Andy G Over a year ago

I tried with tab-delimited but it didn't recognise \t. This could first be replaced with CHR(9) (Excel) but in my example I just used ~.

Jeff Brady Over a year ago

ah nice .. that did split the line up into columns! however, I'm still only getting the first line from Lines[] on each row. I'm not sure what I'm doing wrong, but I'll update my original question. Thanks!

jiverson Over a year ago

I didn't even notice that. I will take a look to see if I can get the other rows to work instead of just repeating the first line.

Jeff Brady Over a year ago

I think I got it working .. part of my code (not listed in the sample) reads the initial .txt file and goes thru it line by line and makes some changes to the line, and writes it back to the array. I just had it also write the current line to the Excel file, added a counter to keep track of what row I'm on, and then at the end used @jiverson's TextToColumns bit to split the line into columns. Thanks guys!

jiverson Over a year ago

I modified the answer to show that you need to loop to add each line and then use the TextToColumns method.

|

gmail user · Accepted Answer · 2013-07-25 16:55:43Z

1

This may help you. The way I created excel file is to read the string from the file and message the data and separated with , and save as .csv file.

May be in your case try to replace the \t with the , and then try to create the file.

This code may give you an idea. I haven't tested it though.

1               string filePath = @"C:\test.csv";  
2               string delimiter = ",";  
3    
4               string[][] output = new string[][]{  
5                   new string[]{"Col 1 Row 1", "Col 2 Row 1", "Col 3 Row 1"},  
6                   new string[]{"Col1 Row 2", "Col2 Row 2", "Col3 Row 2"}  
7               };  
8               int length = output.GetLength(0);  
9               StringBuilder sb = new StringBuilder();  
10              for (int index = 0; index < length; index++)  
11                  sb.AppendLine(string.Join(delimiter, output[index]));  
12   
13              File.WriteAllText(filePath, sb.ToString());

edited Jul 25, 2013 at 16:55

answered Jul 25, 2013 at 16:44

gmail user

2,7914 gold badges33 silver badges43 bronze badges

5 Comments

Jeff Brady Over a year ago

I just gave that a try, and it just put a , instead of a \t .. still didn't split the string up into columns.

gmail user Over a year ago

Then may be try as @chancea said

Jeff Brady Over a year ago

I was previously saving the string array to a .txt file, and then manually importing that file into Excel which works great .. I just want to avoid that manual step and create the Excel file programmatically.

Andy G Over a year ago

@JeffBrady I would probably do that ;)

Jeff Brady Over a year ago

yeah I probably need to loop thru the array line by line

chancea · Accepted Answer · 2013-07-25 17:02:49Z

1

You can split each string in the lines[] using the tab '\t' and then just write each value out to the corresponding cell. Here is an example which should get you started:

 for(int i = 0; i < lines.Length; i++)
   {
       String line = lines[i];

       Excel.Range c1 = (Excel.Range)xlWs.Cells[i+1, 1];
       Excel.Range c2 = (Excel.Range)xlWs.Cells[i+1, 1 + line.Length];
       Excel.Range range = xlWs.get_Range(c1, c2);
       string[] split = line.Split('\t');
       for (int c = 1; c <= split.Length; c++)
       {
          range.Cells[1, c] = split[c-1];
       }
   }

answered Jul 25, 2013 at 17:02

chancea

5,9683 gold badges31 silver badges40 bronze badges

1 Comment

Jeff Brady Over a year ago

Thanks .. this does look like it would work. However, I opted to write line by line instead of cell by cell. But thanks for showing me how to do it this way as well!

Andy G · Accepted Answer · 2013-07-25 17:06:10Z

1

You might be interested in using Excel's Text to Columns feature which will be more efficient for a large dataset, rather than looping through cells.

The following is an Excel recorded macro which you might adapt to run from C#. I first converted \t to ~, but it could be some other character.

Sub Macro1()
    Selection.Replace What:="\t", Replacement:="~", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="~", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
End Sub

It may be slightly tricky to modify to work from C# but it is useful to be aware of this option.

answered Jul 25, 2013 at 17:06

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

1 Comment

Jeff Brady Over a year ago

thanks .. Text to Columns did split the row up into columns, but I still only get the first line from Lines[] appearing on every row.

Ralt · Accepted Answer · 2018-10-19 12:31:53Z

0

Something that I found way easier, and less bugy was to simply write the values to a .csv file and then open it in Excel.

 public static void WriteToCvs<T>(this T[,] array, string fileName)
 {
     using (var stream = File.CreateText(fileName))
     {
         for (int i = 0; i < array.GetLength(0); i++)
         {
             var values = new List<T>();

             for (int j = 0; j < array.GetLength(1); j++)
             {
                 values.Add(array[i, j]);
             }

             stream.WriteLine(string.Join(",", values));
         }
     }
 }

answered Oct 19, 2018 at 12:31

Ralt

2,2261 gold badge28 silver badges41 bronze badges

Collectives™ on Stack Overflow

How can I write a string array to Excel file, with a tab delimiter?

5 Answers 5

8 Comments

5 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

5 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related