Export Data to Excel File not in HTML Table Format using C#.NET

If you would like to see how to export data to Excel file not in HTML table using VB.NET, click here.

There are a few ways to export data to Excel file, either in HTML Table or in Excel’s original table. Previously, I use the method that export data to Excel file in HTML Table (with extension .xls). However, I faced with troubles when I want to re-import the Excel File using System.Data.OleDb method (OleDbConnection, OleDbDataAdapter, etc).

Firstly, I set the connection string like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ + strFilePath + “; Extended Properties=Excel 8.0;”
With this totally cannot import Excel file in HTML Table.

Secondly, I tried to change the Extended Properties=HTML Import, the connection string is like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ + strFilePath + “; Extended Properties=HTML Import;”
With this, the Excel file in HTML Table can be imported. However, when I add in new row record manually to that file, save it and re-import it; the new row record I manually added is not be selected. This is because this method only will select the records in HTML Table but the new record I added is not in HTML Table (is in Excel’s original table).

Therefore, what I can do to solve this problem is to change the method I export data to Excel file. I need to find another way to export data to Excel file that not in HTML Table. I had searched around internet and finally I had found the way to export data into the actual Excel table format that using Exel.ApplicationClass.

Firstly, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references. The codes of the function to export data to Excel file is like below:-

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}

Export Data to Excel File not in HTML Table Format using VB.NET

If you would like to see how to export data to Excel file not in HTML table using C#, click here.

There are a few ways to export data to Excel file, either in HTML Table or in Excel’s original table. Previously, I use the method that export data to Excel file in HTML Table (with extension .xls). However, I faced with troubles when I want to re-import the Excel File using System.Data.OleDb method (OleDbConnection, OleDbDataAdapter, etc).

Firstly, I set the connection string like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ & strFilePath & “; Extended Properties=Excel 8.0;”
With this totally cannot import Excel file in HTML Table.

Secondly, I tried to change the Extended Properties=HTML Import, the connection string is like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ & strFilePath & “; Extended Properties=HTML Import;”
With this, the Excel file in HTML Table can be imported. However, when I add in new row record manually to that file, save it and re-import it; the new row record I manually added is not be selected. This is because this method only will select the records in HTML Table but the new record I added is not in HTML Table (is in Excel’s original table).

Therefore, what I can do to solve this problem is to change the method I export data to Excel file. I need to find another way to export data to Excel file that not in HTML Table. I had searched around internet and finally I had found the way to export data into the actual Excel table format that using Exel.Application.

Firstly, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references. The codes of the function to export data to Excel file is like below:-

Private Funtion ExportToExcelFile(ByVal dt As System.Data.DataTable)
   Dim xlsApp As New Excel.Application
   Dim xlsWorkbook As Excel.Workbook
   Dim xlsWorksheet As Excel.Worksheet
   Dim strhdr As String
   Dim row As Integer
   Dim dr As DataRow
   Dim strFile As String = "file1.xls"
   Dim filename As String = Server.MapPath(strFile)

   If dt.Rows.Count > 0 Then
      'Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add

      'Get the first worksheet
      xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

      'Activate current worksheet
      xlsWorksheet.Activate()

      'Set header row to row 1
      row = 1

      'Add table headers to worksheet
      xlsWorksheet.Cells(row, 1).Value = "Name"
      xlsWorksheet.Cells(row, 2).Value = "Gender"
      xlsWorksheet.Cells(row, 3).Value = "Age"

      'Format header row (bold, extra row height, autofit width)
      With xlsWorksheet.Range("A" & row, "C" & row)
         .Font.Bold = True
         .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
         .EntireRow.AutoFit()
      End With

      'Freeze the column headers
      With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
         xlsApp.ActiveWindow.FreezePanes = True
      End With

      'Write data to Excel worksheet
      For Each dr In dt.Rows
         row += 1
         If Not IsDBNull(dr.Item("Name")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("Name")
         If Not IsDBNull(dr.Item("Gender ")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("Gender")
         If Not IsDBNull(dr.Item("Age")) Then xlsWorksheet.Cells(row, 3).Value = dr.Item("Age")
      Next

      'Format data rows (align to center and left, autofit width and height)
      With xlsWorksheet.Range("A2", "C" & row)
         .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
         .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
         .EntireColumn.AutoFit()
         .EntireRow.AutoFit()
      End With

      'Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = False
      xlsWorkbook.Close(True, filename)

      'Export data to client machine
      strhdr = "attachment;filename=" & strFile
      With Response
         .Clear()
         .ContentType = "application/vnd.ms-excel"
         .ContentEncoding = System.Text.Encoding.Default
         .AppendHeader("Content-Disposition", strhdr)
         .WriteFile(filename)
         .Flush()
         .Clear()
         .Close()
      End With
   End If
End Function

.NET Web Service error - "The test form is only available for requests from the local machine"

I faced this error before and I already solved it.

I had created a window service application that will call web service functions that located at my local PC. The application is working as what I did and required. However, after I has migrated the web service application to Server (or different PC) and I changed my window service application to call the web service functions at Server, the application cannot work.

Then, I tried to browse to the web service at Server using Internet Browser, when I click the function, it showed the message – “The test form is only available for requests from the local machine”.

After that, I found the solution to solve it. I just added in some code into the Web.config for the Web Service application. The code is like below:

<configuration>
   <system.web>
      <webServices>
         <protocols>
            <add name="HttpGet"/>
            <add name="HttpPost"/>
         </protocols>
      </webServices>
   </system.web>
</configuration>

Cheer! Happy Programming!

Window Service timer problem - VB.NET

I had written a Windows Service application in VB.NET before and I faced a problem. I used timer to do the schedule task in this application. I dropped a Timer Control on the designer then add the Tick event to do the schedule task. I can built, install, stop and restart the service. However, the problem is it the Tick event never fires at all.

After that, I try to use code to create the timer. I changed to use System.Timers.Timer instead of System.Windows.Forms.Timer dropped on the designer from Toolbox. Then it is work already. The code is like below:

Dim timerSchedule As System.Timers.Timer

Protected Overrides Sub OnStart(ByVal args() As String)
   timerSchedule = New System.Timers.Timer(1000)
   AddHandler timerSchedule.Elapsed, AddressOf timerSchedule_Elapsed
   timerSchedule.Start()
End Sub

Private Sub timerSchedule_Elapsed(ByVal pSender As Object, ByVal pArgs As System.Timers.ElapsedEventArgs)
   Try
      timerSchedule.Stop()
      'call my a function to do the scheduled task
      DoScheduledTask()
   Catch ex As Exception
   Finally
      timerSchedule.Start()
   End Try
End Sub

Try to know more about System.Timers Namespace, it will help you more.

Server.MapPath Method

Server.MapPath is a function that takes one argument, a virtual path on the Web server, and returns the corresponding physical path.

This function can be used in a number of ways.
First, you can use it to obtain the physical path of a particular web page. For example:
C#
string strFilePath;
strFilePath = Server.MapPath("/WebApp/myWebPage.aspx");

Visual Basic
Dim strFilePath As String
strFilePath = Server.MapPath("/WebApp/myWebPage.aspx")

The output would depend on the web site's physical root directory, but it might be something like: "C:\Inetpub\wwwroot\WebApp\myWebpage.aspx".

You also can use this method to obtain the physical path of a particular directory. For example:
C#
//current directory
string strCurrDir = Server.MapPath("");
//parent directory
string strParentDir = Server.MapPath("..");
//root directory
string strRootDir = Server.MapPath("/");

Visual Basic
'current directory
Dim strCurrDir As String = Server.MapPath("")
'parent directory
Dim strParentDir As String = Server.MapPath("..")
'root directory
Dim strRootDir As String = Server.MapPath("/")

Whether you use backslahed (\) or forward slashes (/), it is same in this function. If you do not put a forward or backward slash at the beginning of the string passed into Server.MapPath, the current directory that the ASP page is being executed is used as the base for the physical path. Else, the root physical path is used as the base for the physical path. For example:
C#
string strFilePath1 = Server.MapPath("someXmlFile.xml");
string strFilePath2 = Server.MapPath("/someXmlFile.xml");

Visual Basic
Dim strFilePath1 As String = Server.MapPath("someXmlFile.xml")
Dim strFilePath2 As String = Server.MapPath("/someXmlFile.xml")

The two lines code above is executed in a ASP page running in the /Dir directory, where the page had physical path C:\Inetpub\wwwroot\Dir. So, the value of strFilePath1 and strFilePath2 will not be the same, where the value of strFilePath1 is C:\Inetpub\wwwroot\Dir\someXmlFile.xml and the value of strFilePath2 is C:\Inetpub\wwwroot\someXmlFile.xml.

Note:
To use MapPath function in a code-behind module, use HttpContext.Current.Server.MapPath.

SQL Server function for datetime - DATEADD

DATEADD is a date function will return a datetime value based on the number interval add to the particular date part of the specified date. The syntax DATEADD is like:
DATEADD(date_part, number_interval, specified_date)

date_part is the parameter that specifies on which part of the date to be manipulated with the number interval. You can add month, year, day and etc. You can use
MONTH, MM or M for month
YEAR, YYYY, YY for year
DAY, DD, D for day
HH for hour
SS, S for Second

For example :
SELECT DATEADD(D, -1, GETDATE())AS [Yesterday]
SELECT DATEADD(MM, 3, GETDATE())AS [ThreeMonthsFromNow]
SELECT DATEADD(YEAR, -2, GETDATE())AS [TwoYearsAgo]