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
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Open(saveAsLocation);
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)
excelworkBook.Worksheets.Item[worksheetName];
range = excelSheet.UsedRange;
int cl = range.Columns.Count;
int rowcount = range.Rows.Count; ;
for (int j = ColumnStart; j <= cl; j++)
{
dataTable.Columns.Add(Convert.ToString
(range.Cells[HeaderLine, j].Value2), typeof(string));
}
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);
}
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:
- Creating Excel file using C#
- 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
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Add(Type.Missing);
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
int rowcount = 1;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
}
}
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.