Importing and Exporting DataTable To & From Excel File

• Download source code - 982.6 KB

Introduction

It's important for people involved in database projects to be able to extract and input their tables in a systematic way. In this tip, we would like to make this task easy for all people using C#.

The main question is what is the best file used in extracting or inputting the tables? As it is mentioned in the title, and in our opinion, the Excel files are the best files to be used, because it's used by most people and its exploration is so easy.

Using the Code

At first, we will use a sample datatable and then learn how to import data from an Excel file.

To succeed, we have to:

• Read the Excel file using C#
• Read each Row start from specified HeaderRow and Column Start of the Excel file

public DataTable ReadExcelToDatatble
(string worksheetName, string saveAsLocation, string ReporType,int HeaderLine,int ColumnStart)
{
    System.Data.DataTable dataTable= new DataTable();
    Microsoft.Office.Interop.Excel.Application excel;
    Microsoft.Office.Interop.Excel.Workbook excelworkBook;
    Microsoft.Office.Interop.Excel.Worksheet excelSheet;
    Microsoft.Office.Interop.Excel.Range range;
    try
    {
        // Get Application object.
        excel = new Microsoft.Office.Interop.Excel.Application();
        excel.Visible = false;
        excel.DisplayAlerts = false;
        // Creation a new Workbook
        excelworkBook = excel.Workbooks.Open(saveAsLocation);
        // Workk sheet
        excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)
                              excelworkBook.Worksheets.Item[worksheetName];
        range = excelSheet.UsedRange;
        int cl = range.Columns.Count;
        // loop through each row and add values to our sheet
        int rowcount =  range.Rows.Count; ;
        //create the header of table
            for (int j = ColumnStart; j <= cl; j++)
            {
              dataTable.Columns.Add(Convert.ToString
                                   (range.Cells[HeaderLine, j].Value2), typeof(string));
            }  
       //filling the table from  excel file                
          for (int i = HeaderLine+1; i <= rowcount; i++)
            {
            DataRow dr = dataTable.NewRow();
            for (int j = ColumnStart; j <= cl; j++)
            {                       
            
                dr[j - ColumnStart] = Convert.ToString(range.Cells[i, j].Value2);
            }                   
            
            dataTable.Rows.InsertAt(dr, dataTable.Rows.Count+1);
            }
            
        //now close the workbook and make the function return the data table
        
        excelworkBook.Close();
        excel.Quit();
        return dataTable;
 }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return null;
    }
    finally
    {
        excelSheet = null;
        range = null;
        excelworkBook = null;
    }
}

The second task is how to extract data table to Excel file:

1. Creating Excel file using C#
2. Writing data to cells

public bool WriteDataTableToExcel
(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
    Microsoft.Office.Interop.Excel.Application excel;
    Microsoft.Office.Interop.Excel.Workbook excelworkBook;
    Microsoft.Office.Interop.Excel.Worksheet excelSheet;
    Microsoft.Office.Interop.Excel.Range excelCellrange;
    
    try
    {
        //  get Application object.
        excel = new Microsoft.Office.Interop.Excel.Application();               
        excel.Visible = false;
        excel.DisplayAlerts = false;
        
        // Creation a new Workbook
        excelworkBook = excel.Workbooks.Add(Type.Missing);
        
        // Workk sheet
        excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
        excelSheet.Name = worksheetName;           
      
        // loop through each row and add values to our sheet
        int rowcount = 1;
        
        foreach (DataRow datarow in dataTable.Rows)
        {
            rowcount += 1;
            for (int i = 1; i <= dataTable.Columns.Count; i++)
            {
                // on the first iteration we add the column headers
                if (rowcount == 3)
                {
                    excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
                }
               // Filling the excel file 
                excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();                       
            }
        }               
        
        //now save the workbook and exit Excel
        excelworkBook.SaveAs(saveAsLocation);;
        excelworkBook.Close();
        excel.Quit();
        return true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return false;
    }
    finally
    {
        excelSheet = null;
        excelCellrange = null;
        excelworkBook = null;
    }
}

Points of Interest

Through this tip, we hope that we helped some developers by reducing the task of the exploitation of the tables in databases, and made the data extraction easier than before.

References

The idea is inspired from the work of Devesh Omar, in that he makes only the extraction of the datatable to Excel file, so we make some changes on his code source and we add the inputting of data from Excel file.