We blog about dot net source code, net programming, vb code programming, visual basic dot net, visual basic, c sharp dot net

Writing data inthe Dataset into an Excel (with formattings)

Posted on August 1, 2008

The code below help to writing the data from the DataSet into a Excel. You can also do some style formatting in the Excel output. You need two important namespace to implement this concept, 'Microsoft.Office.Interop.Excel', 'Microsoft.VisualBasic'. You should also need to reference these interopable dll into you project.

This code need the MS Excel to be installed in the machine. As we are reference the Excel dll into the project.

using Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic; 
 
        private void TOExcel(string strExcelPath, DataSet ds)
        {
            //declaring the application
            Microsoft.Office.Interop.Excel.Application oAppln;
            //declaring work book
            Microsoft.Office.Interop.Excel.Workbook oWorkBook;
            //declaring worksheet
            Microsoft.Office.Interop.Excel.Worksheet oWorkSheet;
            //declaring the range
            Microsoft.Office.Interop.Excel.Range oRange;
            Microsoft.Office.Interop.Excel.Range oCellRange;
            Microsoft.Office.Interop.Excel.Range oHeadRange;
 
            try
            {
                oAppln = new Microsoft.Office.Interop.Excel.Application();
                oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true));
                oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet;
 
                int iRow = 2;
                int totRow = ds.Tables[0].Rows.Count;
                int totCol = ds.Tables[0].Columns.Count;
 
                //check for data
                if (totRow > 0)
                {
                    //headers
                    for (int j = 0; j < totCol; j++)
                    {
                        string colHead = ds.Tables[0].Columns[j].ColumnName;
                        switch (colHead)
                        { 
                            case "employee":
                                oWorkSheet.Cells[1, j + 1] = "EMP ID";
                                break;
                            case "first_name":
                                oWorkSheet.Cells[1, j + 1] = "FIRST NAME";
                                break;
                            case "last_name":
                                oWorkSheet.Cells[1, j + 1] = "LAST NAME";
                                break;
                            case "hrms_att_shift":
                                oWorkSheet.Cells[1, j + 1] = "CURRENT SHIFT";
                                break;
                        }
                    }
                    ///getting the head range and apply header styles
                    oHeadRange = oWorkSheet.get_Range(oWorkSheet.Cells[1, 1], oWorkSheet.Cells[1, totCol]);
                    oHeadRange.Font.Bold = true;
                    oHeadRange.Interior.ColorIndex = 11;
                    oHeadRange.Font.ColorIndex = 2;
 
                    //inserting datas
                    for (int rowNo = 0; rowNo < totRow; rowNo++)
                    {
                        //in each row
                        for (int colNo = 0; colNo < totCol; colNo++)
                        {
                            // in each column
                            oWorkSheet.Cells[iRow, colNo + 1] = ds.Tables[0].Rows[rowNo][colNo].ToString();
                        }
                        if ((rowNo & 1) == 1)
                        {
                            oCellRange = oWorkSheet.get_Range(oWorkSheet.Cells[iRow, 1], oWorkSheet.Cells[iRow, totCol]);
                            oCellRange.Interior.ColorIndex = 15;
                        }
                        //moving to next row
                        iRow++;
                    }
                }
                //range of the excel sheet
                oRange = oWorkSheet.get_Range("A1", oWorkSheet.Cells[totRow + 1, totCol]);
                oRange.EntireColumn.AutoFit();
                oRange.Borders.Value = 1;
                oWorkBook.SaveAs(strExcelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
            }
 
            catch (Exception theException)
            {
                string s = theException.Message;
                Console.WriteLine("Console: Attachment - " + theException.Message.ToString());
            }
            finally
            {
                ///Clearing all loaded memory variables
                oRange = null;
                oCellRange = null;
                oWorkBook = null;
                oWorkSheet = null;
                oAppln = null;
            }
        }

Tagged as: do some style formatting in the Excel output, dot net timer, dot net vb, dotnet net, Microsoft.Office.Interop.Excel, Microsoft.VisualBasic, namespace, need to reference interopable dll to you project, open dot net, source code dot net, vb net code, visual basic dot net code, visual basic net code, write the data from the DataSet into a Excel No Comments

Converting DataTable into CSV format

Posted on August 1, 2008

The below code will help in converting the datatable into CSV foramat, which can be opened in MS Excel. This task is essential when you want to mail tabular data as attachment to your receipent. And especially whcn you don't have MS Excel installed in your server.

 public static void SaveDataTableToCsvFile(string AbsolutePathAndFileName, DataTable TheDataTable, params string[] Options)
        {
            //variables
            string separator;
            if (Options.Length > 0)  { separator = Options[0]; }
	    else { separator = ","; //default            }
            string quote = "\"";
 
            //create CSV file
            StreamWriter sw = new StreamWriter(AbsolutePathAndFileName);
 
            //write header line
            int iColCount = TheDataTable.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                sw.Write(TheDataTable.Columns[i]);
                if (i < iColCount - 1) { sw.Write(separator);  }
            }
            sw.Write(sw.NewLine);
 
            //write rows
            foreach (DataRow dr in TheDataTable.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        string data = dr[i].ToString();
                        data = data.Replace("\"", "\\\"");
                        sw.Write(quote + data + quote);
                    }
                    if (i < iColCount - 1)  { sw.Write(separator);  }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }

Tagged as: convert data table, Converting DataTable into CSV format, csv, DataTable into CSV, DataTable into excel, excel No Comments

How to import MS SQL Server Data into MS Access database

Posted on July 31, 2008

The following simple code will import the MS SQL Server data/table into an Access Database. It uses ADO.

'Set Reference to ADODB
Private Sub Import_MS_SQL_Server_Data_Into_MS_Access()
    Dim cn As New Adodb.Connection
    cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=Your_Access_Database.mdb;DefaultDir=C:\test;Uid=admin;Pwd=admin;"
    cn.Execute "SELECT * INTO [table_data] FROM [ODBC;Driver=SQL Server; SERVER=MySQL_Server;DATABASE=MySQL_DB;UID=MySQL_User;PWD=MySQL_Password;].[table_data];"
    cn = Nothing
End Sub