Read specific region from Text file and write in existing Excel using C# and VB.Net

Last Reply 23 days ago By pandeyism

Posted 24 days ago

Hi All,

I am trying to read only certain region of texts from a file(Notepad) and store it to excel.Here's how the file looks:

Control1--------------------

Control was tested in 2018 march Tests performed: Design Test results: None

Control1 Ends------------


Control2--------------------

Control was tested in 2017 march Tests performed: None Test results: No Exceptions

Control2 Ends------------

*********************

I know how to Read all lines but not sure about this.

Regards,

Vikash

You are viewing reply posted by: pandeyism 23 days ago.
Posted 23 days ago

Hey Vikash21,

Please refer below sample.

Namespaces

C#

using System.IO;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

VB.Net

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Reflection

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    string text = File.ReadAllText(Server.MapPath("~/Test.txt"));
    int start = text.IndexOf("Control1");
    int end = text.IndexOf("Control1 End", start);
    string result = text.Substring(start, end - start).Replace("Control1", "").Replace("--", "").Trim();
    ReadExistingExcel(result);
}

public static void ReadExistingExcel(string result)
{
    string path = @"C:\Users\anand\Desktop\excelfolder\Book1.xls";
    Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
    application.Visible = true;
    application.DisplayAlerts = false;
    Excel.Workbook workbook = application.Workbooks.Open(path);
    Excel.Sheets sheets = workbook.Worksheets;
    Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet1");
    Excel.Range range = workSheet.UsedRange;
    // Inserted in the cell position.
    workSheet.Cells[2, 2] = result;
    workbook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive,
    Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value);
    workbook.Close(Missing.Value, Missing.Value, Missing.Value);
    workSheet = null;
    workbook = null;
    application.Quit();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim text As String = File.ReadAllText(Server.MapPath("~/Test.txt"))
    Dim start As Integer = text.IndexOf("Control1")
    Dim [end] As Integer = text.IndexOf("Control1 End", start)
    Dim result As String = text.Substring(start, [end] - start).Replace("Control1", "").Replace("--", "").Trim()
    ReadExistingExcel(result)
End Sub
Public Shared Sub ReadExistingExcel(ByVal result As String)
    Dim path As String = "C:\Users\anand\Desktop\excelfolder\Book1.xls"
    Dim application As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    application.Visible = True
    application.DisplayAlerts = False
    Dim workbook As Excel.Workbook = application.Workbooks.Open(path)
    Dim sheets As Excel.Sheets = workbook.Worksheets
    Dim workSheet As Excel.Worksheet = CType(sheets.Item("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
    Dim range As Excel.Range = workSheet.UsedRange
    workSheet.Cells(2, 2) = result
    workbook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
    workbook.Close(Missing.Value, Missing.Value, Missing.Value)
    workSheet = Nothing
    workbook = Nothing
    application.Quit()
End Sub

Screenshots

Existing Excel

After Inserting text file data in excel