How to Read MS Word Document Content using Office Interoperability Service Assemblies

Below code will read the content of a Microsoft Word document and return the text in a string variable.

public string GetText()
        {
     
                string Referees = string.Empty;
                string totaltext = "";
                Microsoft.Office.Interop.Word.Application word = new Microsoft.Office.Interop.Word.Application();

                object miss = System.Reflection.Missing.Value;
                object path = Server.MapPath("~/Content/Documents/CV March 2013 Peter Corbitt.docx");
                object readOnly = true;
                Microsoft.Office.Interop.Word.Document docsw = word.Documents.Open(ref path, ref miss, ref readOnly, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss);
                try
                {
                 
                    for (int i = 0; i < docsw.Paragraphs.Count; i++)
                    {
                        totaltext += " \r\n " + docsw.Paragraphs[i + 1].Range.Text.ToString();
                    }

                    docsw.Close();
                    word.Quit();
                }
                catch (Exception ex)
                {
                    docsw.Close();
                    word.Quit();
                    throw ex;
                }

                return totaltext;
        }

Import Excel Data into a Sql Server table.

In my previous post i have shown you how to import Excel data into a DataTable. In this post i'm going to show you how to do a bulk insert from the DataTable into a Sql Server table.

First create a table in your Sql Server exactly with the column names same as the excel sheet column names you are going to import.

Now run the below method. Method accept two parameters first parameter accept the DataTable with Excel data and the second parameter accepts the table name where the will be imported in the Sql Severer.


public void BulkInsert(DataTable DT,string DestinationTableName)
        {
            using (SqlConnection Conn = new SqlConnection(connectionString))
                {
                    SqlTransaction trans;
                    Conn.Open();
                    trans = Conn.BeginTransaction();
                    try
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Conn, SqlBulkCopyOptions.Default, trans))
                        {
                            try
                            {
                                // The table I'm loading the data to
                                bulkCopy.DestinationTableName = DestinationTableName;
                                // How many records to send to the database in one go (all of them)
                                bulkCopy.BatchSize = DT.Rows.Count;

                                // Load the data to the database
                                bulkCopy.WriteToServer(DT);

                                // Close up        
                                bulkCopy.Close();
                                trans.Commit();
                            }
                            catch(Exception ex)
                            {
                                throw ex;
                            }
                        }

                    }
                    catch
                    {
                        trans.Rollback();

                    }
                    finally
                    {
                        trans.Dispose();
                   }
               }
        }

How to Import Data from a Excel Sheet into a DataTable

Below method will import data from a excel sheet into a DataTable. You need to import three name space.

System.Data.SqlClient;
System.Data;
System.Data.OleDb;

We are passing two parameters.

TabName - The Tab name in Excel sheet.
excelConnStr - OLEDB connection string. Following connection works for Office 2007.

string FilePath = "~/Files/MyFile.xlsx";
String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Server.MapPath(FilePath) + ";Extended Properties=Excel 12.0;";

public static DataTable GetExcelData(string TabName, string excelConnStr)
{
   using (OleDbConnection excelConn = new OleDbConnection(excelConnStr))
{
  excelConn.Open();
  string query = string.Empty;
  if (TabName == "Management$")
   {
    query = "select * from [" + TabName + "]";
   }
  else if (TabName == "Business$")
   {
    query = "select * from [" + TabName + "]";
   }
  else if (TabName == "Design$")
  {
    query = "select * from [" + TabName + "]";
  }
  using (OleDbCommand excelCommand = new OleDbCommand(query, excelConn))
  {
    using (OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter())
   {

   DataTable dtPatterns = new DataTable();
   excelDataAdapter.SelectCommand = excelCommand;
   excelDataAdapter.Fill(dtPatterns);
   return dtPatterns;
   }
  }
 }

}

Export DataTable to MS Excel in ASP.NET

.NET framework has a interoperability service with MS office package. It works perfectly fine when i tried to export data to MS Excel sheet from a ASP.NET application but the problem was it's a time consuming. When you have thousands of data the process will take huge time to process.

To over come we can directly write the DataTable content into the Excel cells using Response.Write() by looping through the Excel columns and rows. This method will save huge amount of time for exporting data.


public static void DataTableToExcel(System.Data.DataTable dtExcel)
    {
     
        HttpContext context = HttpContext.Current;
        string attachment = "attachment; filename=pinDoc.xls";
        context.Response.ClearContent();
        context.Response.AddHeader("content-disposition", attachment);
        context.Response.ContentType = "application/vnd.ms-excel";
        string tab = "";
        foreach (DataColumn dc in dtExcel.Columns)
        {
            context.Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        context.Response.Write("\n");
        int i;
        foreach (DataRow dr in dtExcel.Rows)
        {
            tab = "";
            for (i = 0; i < dtExcel.Columns.Count; i++)
            {
                context.Response.Write(tab + dr[i].ToString().Replace("\r\n", "").Replace("\n", ""));
                tab = "\t";
            }
            context.Response.Write("\n");
        }

        context.Response.Flush();
        context.Response.End();
    }