word to excel data export with character count

First of all, I've found a way to get lines rather than sentences from a Word file. Basically, you select each line, read it and save it:

http://www.excelforum.com/excel-programming-vba-macros/514832-how-to-read-a-line-from-ms-word.html

So I've translated the VB Script to C# and included it in the program.

I've also dealt with the other aspects you described in your latest post:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text.RegularExpressions;

using System.Runtime.InteropServices;

using Word = Microsoft.Office.Interop.Word;

using Excel = Microsoft.Office.Interop.Excel;

namespace WordToExcel

{

static class Roman

{

static string[] roman1 = { "MMM", "MM", "M" };

static string[] roman2 = { "CM", "DCCC", "DCC", "DC", "D", "CD", "CCC", "CC", "C" };

static string[] roman3 = { "XC", "LXXX", "LXX", "LX", "L", "XL", "XXX", "XX", "X" };

static string[] roman4 = { "IX", "VIII", "VII", "VI", "V", "IV", "III", "II", "I" };

public static bool TryParse(string text, out int value)

{

value = 0;

if (String.IsNullOrEmpty(text)) return false;

text = text.ToUpper();

int len = 0;

for (int i = 0; i < 3; i++)

{

if (text.StartsWith(roman1[i]))

{

value += 1000 * (3 - i);

len = roman1[i].Length;

break;

}

if (len > 0)

{

text = text.Substring(len);

len = 0;

}

for (int i = 0; i < 9; i++)

{

if (text.StartsWith(roman2[i]))

{

value += 100 * (9 - i);

len = roman2[i].Length;

break;

}

if (len > 0)

{

text = text.Substring(len);

len = 0;

}

for (int i = 0; i < 9; i++)

{

if (text.StartsWith(roman3[i]))

{

value += 10 * (9 - i);

len = roman3[i].Length;

break;

}

if (len > 0)

{

text = text.Substring(len);

len = 0;

}

for (int i = 0; i < 9; i++)

{

if (text.StartsWith(roman4[i]))

{

value += 9 - i;

len = roman4[i].Length;

break;

}

if (text.Length > len)

{

value = 0;

return false;

}

return true;

}

class Program

{

static void Main(string[] args)

{

// open word document, get lines and break them down into words

Word._Application word = new Word.Application();

string path = @"C:\users\alan\documents\ProblemFile.docx";

Word._Document doc = word.Documents.Open(FileName: path, ReadOnly: true);

var lines = new List();

bool bolEOF = false;

doc.Characters[1].Select();

{

word.Selection.MoveEnd(Unit: Word.WdUnits.wdLine, Count: 1);

lines.Add(word.Selection.Text);

word.Selection.Collapse(Direction: Word.WdCollapseDirection.wdCollapseEnd);

if (word.Selection.Bookmarks.Exists(@"\EndOfDoc")) bolEOF = true;

}

while (!bolEOF);

doc.Close();

word.Quit();

Marshal.ReleaseComObject(word);

// open a new excel file and write the words in each line to a separate row

Excel._Application excel = new Excel.Application();

excel.Visible = true;

string path2 = @"C:\users\alan\documents\ProblemFile2.xlsx";

Excel._Workbook workbook = excel.Workbooks.Add();

Excel._Worksheet worksheet = workbook.ActiveSheet;

int row = 1;

string temp;

char[] space = { ' ' };

StringSplitOptions ree = StringSplitOptions.RemoveEmptyEntries;

foreach (string line in lines)

{

// make sure (, ) and ? will be treated as separate words

temp = line.Replace("(", " ( ").Replace(")", " ) ").Replace("?", " ? ");

string[] items = temp.Trim().Split(space, ree);

for (int i = 0; i < items.Length; i++)

{

int count = 0, first = -1, last = -1;

if (items[i] == "(" || items[i] == ")" || items[i] == "?")

{

count = 1;

}

else

{

int len = items[i].Length;

string temp2 = items[i];

for (int j = 0; j < len; j++)

{

if (Char.IsLetterOrDigit(items[i][j]))

{

count++;

if (first == -1) first = j;

last = j;

}

if (first > -1 && !(first == 0 && last == len - 1))

{

temp2 = temp2.Substring(first, last - first + 1);

}

int value = 0;

if (Roman.TryParse(temp2, out value)) count = value.ToString().Length;

}

items[i] = String.Format("{0} {1}", items[i], count);

}

int c;

for (c = 0; c < items.Length; c++)

{

worksheet.Cells[row, c + 1] = items[c];

}

worksheet.Cells[row, c + 3] = items.Length;

row++;

}

worksheet.Cells.Columns.AutoFit();

excel.DisplayAlerts = false;

excel.AlertBeforeOverwriting = false;

workbook.SaveAs(path2);

workbook.Close(false);

excel.Quit();

Marshal.ReleaseComObject(excel);

}

}

Please note the following:

There are some inconsistencies between the ProblemFile and the RequiredSolution files. For example (not necessarily exhaustive):

(a) The Excel file has 'XXXI hours' but the word file has 'a hour'.

(b) The Excel file suppresses all opening single quotes but not closing single quotes.

(c) The Excel file doesn't include the blank lines and then the formatting instruction at the end of the Word file.

(d) The Excel file treats: anything –' as a single word even though, in the Word file, there's a space between anything and the dash.

My program sticks rigidly to the rules in all these cases.

Notice also that the program attempts to parse every word to what would be regarded as a valid Roman numeral in modern times. This means that the only symbols recognized are I,V,X,L,C,D and M and four of these symbols together such as IIII is regarded as invalid (IV should be used instead).

There are a few cases where valid Roman numerals are also valid English words such as 'mix'.
In such cases the program assumes it's a Roman numeral rather than a word and so returns a count of 4 (i.e. 1009) not 3.

I don't know of any way to read individual lines from a Word file as these will depend on the width of the container and the font size.

However, you can get individual sentences or paragraphs.

I've used sentences in the following code (requires C# 4.0 or later). You need to add references to both the Word and Excel object libraries to your project:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text.RegularExpressions;

using System.Runtime.InteropServices;

using Word = Microsoft.Office.Interop.Word;

using Excel = Microsoft.Office.Interop.Excel;

namespace WordToExcel

{

class Program

{

static void Main(string[] args)

{

// open word document, get sentences and break them down into words

Word._Application word = new Word.Application();

string path = @"C:\users\alan\documents\hello.docx";

Word._Document doc = word.Documents.Open(FileName : path, ReadOnly : true);

var sentences = new List();

for (int i = 1; i <= doc.Sentences.Count; i++)

{

sentences.Add(doc.Sentences[i].Text);

}

doc.Close();

word.Quit();

Marshal.ReleaseComObject(word);

// open a new excel file and write the words in each sentence to a separate row

Excel._Application excel = new Excel.Application();

excel.Visible = true;

string path2 = @"C:\users\alan\documents\hello2.xlsx";

Excel._Workbook workbook = excel.Workbooks.Add();

Excel._Worksheet worksheet = workbook.ActiveSheet;

int row = 1;

foreach (string sentence in sentences)

{

MatchCollection mc = Regex.Matches(sentence, @"\b\w+\b");

string[] items = (from Match m in mc select String.Format("{0} {1}", m.Value, m.Length)).ToArray();

for (int i = 0; i < items.Length; i++) worksheet.Cells[row, i + 1] = items[i];

row++;

}

worksheet.Cells.Columns.AutoFit();

excel.DisplayAlerts = false;

excel.AlertBeforeOverwriting = false;

workbook.SaveAs(path2);

workbook.Close(false);

excel.Quit();

Marshal.ReleaseComObject(excel);

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
First of all, I've found a way to get lines rather than sentences from a Word file. Basically, you select each line, read it and save it:

http://www.excelforum.com/excel-programming-vba-macros/514832-how-to-read-a-line-from-ms-word.html

So I've translated the VB Script to C# and included it in the program.

I've also dealt with the other aspects you described in your latest post:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Runtime.InteropServices;
using Word = Microsoft.Office.Interop.Word;
using Excel = Microsoft.Office.Interop.Excel;

namespace WordToExcel
{
static class Roman
{
static string[] roman1 = { "MMM", "MM", "M" };
static string[] roman2 = { "CM", "DCCC", "DCC", "DC", "D", "CD", "CCC", "CC", "C" };
static string[] roman3 = { "XC", "LXXX", "LXX", "LX", "L", "XL", "XXX", "XX", "X" };
static string[] roman4 = { "IX", "VIII", "VII", "VI", "V", "IV", "III", "II", "I" };

public static bool TryParse(string text, out int value)
{
value = 0;
if (String.IsNullOrEmpty(text)) return false;
text = text.ToUpper();
int len = 0;

for (int i = 0; i < 3; i++)
{
if (text.StartsWith(roman1[i]))
{
value += 1000 * (3 - i);
len = roman1[i].Length;
break;
}
}

if (len > 0)
{
text = text.Substring(len);
len = 0;
}

for (int i = 0; i < 9; i++)
{
if (text.StartsWith(roman2[i]))
{
value += 100 * (9 - i);
len = roman2[i].Length;
break;
}
}

if (len > 0)
{
text = text.Substring(len);
len = 0;
}

for (int i = 0; i < 9; i++)
{
if (text.StartsWith(roman3[i]))
{
value += 10 * (9 - i);
len = roman3[i].Length;
break;
}
}

if (len > 0)
{
text = text.Substring(len);
len = 0;
}

for (int i = 0; i < 9; i++)
{
if (text.StartsWith(roman4[i]))
{
value += 9 - i;
len = roman4[i].Length;
break;
}
}

if (text.Length > len)
{
value = 0;
return false;
}

return true;
}
}

class Program
{
static void Main(string[] args)
{
// open word document, get lines and break them down into words
Word._Application word = new Word.Application();
string path = @"C:\users\alan\documents\ProblemFile.docx";
Word._Document doc = word.Documents.Open(FileName: path, ReadOnly: true);

var lines = new List();
bool bolEOF = false;
doc.Characters[1].Select();

do
{
word.Selection.MoveEnd(Unit: Word.WdUnits.wdLine, Count: 1);
lines.Add(word.Selection.Text);
word.Selection.Collapse(Direction: Word.WdCollapseDirection.wdCollapseEnd);
if (word.Selection.Bookmarks.Exists(@"\EndOfDoc")) bolEOF = true;
}
while (!bolEOF);

doc.Close();
word.Quit();
Marshal.ReleaseComObject(word);

// open a new excel file and write the words in each line to a separate row
Excel._Application excel = new Excel.Application();
excel.Visible = true;
string path2 = @"C:\users\alan\documents\ProblemFile2.xlsx";
Excel._Workbook workbook = excel.Workbooks.Add();
Excel._Worksheet worksheet = workbook.ActiveSheet;
int row = 1;
string temp;
char[] space = { ' ' };
StringSplitOptions ree = StringSplitOptions.RemoveEmptyEntries;

foreach (string line in lines)
{
// make sure (, ) and ? will be treated as separate words
temp = line.Replace("(", " ( ").Replace(")", " ) ").Replace("?", " ? ");
string[] items = temp.Trim().Split(space, ree);

for (int i = 0; i < items.Length; i++)
{
int count = 0, first = -1, last = -1;

if (items[i] == "(" || items[i] == ")" || items[i] == "?")
{
count = 1;
}
else
{
int len = items[i].Length;
string temp2 = items[i];

for (int j = 0; j < len; j++)
{
if (Char.IsLetterOrDigit(items[i][j]))
{
count++;
if (first == -1) first = j;
last = j;
}
}

if (first > -1 && !(first == 0 && last == len - 1))
{
temp2 = temp2.Substring(first, last - first + 1);
}

int value = 0;

if (Roman.TryParse(temp2, out value)) count = value.ToString().Length;
}

items[i] = String.Format("{0} {1}", items[i], count);
}

int c;
for (c = 0; c < items.Length; c++)
{
worksheet.Cells[row, c + 1] = items[c];
}

worksheet.Cells[row, c + 3] = items.Length;
row++;
}

worksheet.Cells.Columns.AutoFit();
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
workbook.SaveAs(path2);
workbook.Close(false);
excel.Quit();
Marshal.ReleaseComObject(excel);
}
}
}

Please note the following:

There are some inconsistencies between the ProblemFile and the RequiredSolution files. For example (not necessarily exhaustive):

(a) The Excel file has 'XXXI hours' but the word file has 'a hour'.

(b) The Excel file suppresses all opening single quotes but not closing single quotes.

(c) The Excel file doesn't include the blank lines and then the formatting instruction at the end of the Word file.

(d) The Excel file treats: anything –' as a single word even though, in the Word file, there's a space between anything and the dash.

My program sticks rigidly to the rules in all these cases.

Notice also that the program attempts to parse every word to what would be regarded as a valid Roman numeral in modern times. This means that the only symbols recognized are I,V,X,L,C,D and M and four of these symbols together such as IIII is regarded as invalid (IV should be used instead).

There are a few cases where valid Roman numerals are also valid English words such as 'mix'.
In such cases the program assumes it's a Roman numeral rather than a word and so returns a count of 4 (i.e. 1009) not 3.
Vulpes12y ago
I don't know of any way to read individual lines from a Word file as these will depend on the width of the container and the font size.

However, you can get individual sentences or paragraphs.

I've used sentences in the following code (requires C# 4.0 or later). You need to add references to both the Word and Excel object libraries to your project:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Runtime.InteropServices;
using Word = Microsoft.Office.Interop.Word;
using Excel = Microsoft.Office.Interop.Excel;

namespace WordToExcel
{
class Program
{
static void Main(string[] args)
{
// open word document, get sentences and break them down into words
Word._Application word = new Word.Application();
string path = @"C:\users\alan\documents\hello.docx";
Word._Document doc = word.Documents.Open(FileName : path, ReadOnly : true);
var sentences = new List();

for (int i = 1; i <= doc.Sentences.Count; i++)
{
sentences.Add(doc.Sentences[i].Text);
}

doc.Close();
word.Quit();
Marshal.ReleaseComObject(word);

// open a new excel file and write the words in each sentence to a separate row
Excel._Application excel = new Excel.Application();
excel.Visible = true;
string path2 = @"C:\users\alan\documents\hello2.xlsx";
Excel._Workbook workbook = excel.Workbooks.Add();
Excel._Worksheet worksheet = workbook.ActiveSheet;
int row = 1;

foreach (string sentence in sentences)
{
MatchCollection mc = Regex.Matches(sentence, @"\b\w+\b");
string[] items = (from Match m in mc select String.Format("{0} {1}", m.Value, m.Length)).ToArray();
for (int i = 0; i < items.Length; i++) worksheet.Cells[row, i + 1] = items[i];
row++;
}

worksheet.Cells.Columns.AutoFit();
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
workbook.SaveAs(path2);
workbook.Close(false);
excel.Quit();
Marshal.ReleaseComObject(excel);
}
}
}