private void UsingInteropExcel()
{
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(EXCEL_FILE_PATH);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
Excel.Range lastCell = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
int lastColumn = (int)lastCell.Column;
int lastRow = (int)lastCell.Row;
string text = "";
string firstTitle = "";
string title = "";
string subtitle = "";
string index_MDA = "";
for (int rowIndex = 1; rowIndex <= lastRow; rowIndex++)
{
for (int columnIndex = 1; columnIndex <= lastColumn; columnIndex++)
{
text = worksheet.Cells[rowIndex, columnIndex].Text.ToString();
if (String.IsNullOrEmpty(text))
{
continue;
}
if (columnIndex > 1)
{
title = worksheet.Cells[rowIndex, columnIndex - 1].Text.ToString();
int inc = 1;
while (String.IsNullOrEmpty(title))
{
title = worksheet.Cells[rowIndex - inc, columnIndex - 1].Text.ToString();
inc++;
}
}
else
{
title = firstTitle;
}
if (text.Contains("Предметный заголовок"))
{
int splitLength = text.Split('|').Length;
firstTitle = text.Split('|')[splitLength - 1];
index_MDA = text.Split('|')[1];
Console.WriteLine("\n\nдобавлен Предметный заголовок: " + index_MDA + " " + firstTitle);
continue;
}
subtitle = text;
SubtitleObj subtitleObj = new SubtitleObj(index_MDA, title, subtitle);
Console.WriteLine("ИНДЕКС МДА: " + index_MDA + " ЗАГОЛОВОК: " + subtitleObj.Title + " ПОДЗАГОЛОВОК: " + subtitleObj.Subtitle);
subtitlesObjs.Add(subtitleObj);
}
}
workbook.Close(false);
app.Quit();
}
private static bool ConsoleDialog(SubtitleObj subObj)
{
bool exit = false;
StringBuilder builder = new StringBuilder();
bool readCh = true;
do
{
Console.WriteLine("Add Подзаголовок\n{0}\n from Заголовка{1}\n", subObj.Subtitle, subObj.Title);
Console.Write("(");
ColorConsole("y");
Console.Write("es /");
ColorConsole("n");
Console.Write("o / ");
ColorConsole("e");
Console.Write("dit / e");
ColorConsole("x");
Console.Write("it) ?");
char c = Console.ReadKey().KeyChar;
switch (c)
{
case 'e':
Console.Write("\nEdit name:\n");
SendKeys.SendWait(subObj.Subtitle);
subObj.Subtitle = Console.ReadLine();
Console.Clear();
Console.WriteLine("New subtitle name:\n{0}", subObj.Subtitle);
Console.ReadKey();
readCh = false;
break;
case 'y':
readCh = false;
break;
case 'n':
readCh = false;
break;
case 'x':
readCh = false;
exit = true;
break;
default:
Console.Clear();
continue;
}
} while (readCh);
Console.Clear();
return(exit);
}
internal void InsertDataDB(string tableName, SubtitleObj subtitleObj)
{
var builder = new StringBuilder();
builder.AppendFormat(@"INSERT INTO doc_subtitles (index_MDA, title, subtitle)
VALUES ('{0}', '{1}', '{2}')", subtitleObj.Index_MDA, subtitleObj.Title, subtitleObj.Subtitle);
Console.WriteLine(builder.ToString());
try
{
command.CommandText = builder.ToString();
command.ExecuteNonQuery();
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex);
}
}
internal List <SubtitleObj> GetSubtitles()
{
List <SubtitleObj> subtitleObjs = new List <SubtitleObj>();
var numberOfPages = objDoc.ComputeStatistics(WdStatistic.wdStatisticPages, false);
for (int i = 1; i <= numberOfPages; i++)
{
var pageRange = objDoc.Range()
.GoTo(WdGoToItem.wdGoToPage, WdGoToDirection.wdGoToAbsolute, i);
foreach (Table tb in pageRange.Tables)
{
foreach (Row row in tb.Rows)
{
foreach (Cell cell in row.Cells)
{
string pageText = pageRange.Text;
string cellText = cell.Range.Text;
System.Console.WriteLine("Text in cell: {0}\n" +
"Text On Page: {1}\n\n", cellText, pageText);
if (cellText.Contains("подзаголовок"))
{
continue;
}
else
{
SubtitleObj subObj = new SubtitleObj("", "", "");
subObj.Title = pageText;
subObj.Subtitle = cellText;
subtitleObjs.Add(subObj);
}
}
}
}
}
return(subtitleObjs);
}
