GST Billing Software

header image

Import Data from Excel sheet

Erachana Line

Import data from a spreadsheet

  • Create a new Excel worksheet, name it and insert the data.

• Browse to the folder that contains Excel sheet when you want to import the data.

The following example shows how to import values from excel sheet and store the values into a DataGrid.

Example: Contact Manager Application

Let's see how to import values to country master from excel sheet.

Step1: First include ExcelImport.cs class.

Step 2: create an excel sheet and store required values.

Step 3: Include following code

public void import_expression() {

try

{

OpenFileDialog dlg = new OpenFileDialog();

string strFileName;

MainForm m = new MainForm();

dlg.Filter = "Excel Templatex(*.xls;*.xlsx ) |*.xls;*.xlsx";

if (dlg.ShowDialog() == DialogResult.OK)

{

strFileName = dlg.FileName;

}

else

return;

ExcelImport excelimp = new ExcelImport(ExcelAppType.MicrosoftExcel); //to import excel sheet

excelimp.OpenWorkBook(strFileName);// open excel sheet

string strExcelSheetName = (string) excelimp.GetExcelSheetName();

if (!(strExcelSheetName == "country")) //checking whether proper excel sheet opened or not

{

excelimp.CloseExcel();

MessageBox.Show("Excel file not matching with template", "Template Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Stop);

return; }

string[] StatementCols = { "country" }; //include all the fields of countryMaster table except id

for (int z = 1; z <= 1; z++) //z specifies how many fields; in contact manager only 1 field.

{

try

{

object colVal = excelimp.GetValue(z, 3);/ /In excel sheet 3rd row contains header names

if (TypeConverter.ConvertToString(colVal) != StatementCols[z - 1]) // checking whether header name is same as field name which you included in StatementCols

{

excelimp.CloseExcel();

MessageBox.Show("Excel file not matching with template", "Template Missmatch", MessageBoxButtons.OK, MessageBoxIcon.Stop);

return;

}

}

catch (Exception ex)

{

excelimp.CloseExcel();

excelimp.CloseWorkbook();

MessageBox.Show("Error in Excel file " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

return;

}

}

DataTable dtError = new DataTable();

dtError.Columns.Add("COL", typeof(Int32)); //It is created because if excel sheet contains null values that should bee added to error datatable.

dtError.Columns.Add("ROW", typeof(Int32));

dtError.Columns.Add("ERR", typeof(String));

try

{

DataTable Country = SqlInterpreter.GetData("Select * from Country where id =-1");

for (long lngRow = 4; lngRow < 5000; lngRow++) //actual values are present from 4th row, up to 5000 row values can be added

{

int ColNo = 1;

int nColumns = 1 ;//no of columns; in countrymaster only one

int nEmptyCount = 0;

object oVal; //object which stores excel sheet cell value

bool bValidRow = true;

for (ColNo = 1; ColNo <= nColumns; ColNo++)

{

oVal = excelimp.GetValue(ColNo, lngRow);

if (((oVal != null) && ((oVal.ToString()).Length == 0)) || (oVal == null))

nEmptyCount++;

}

if (nEmptyCount == nColumns)

break;

ColNo = 1;

DataRow newRow = Country.NewRow(); //new row is added to datatable

for (ColNo = 1; ColNo <= nColumns; ColNo++)

{

oVal = excelimp.GetValue(ColNo, lngRow);

switch (ColNo)

{

case 1://country name

if (oVal != null && TypeConverter.ConvertToString(oVal).Length > 0)

{

try

{

newRow["CountryName"] = oVal; //country name value is added to datatable "country name" field

}

catch

{

bValidRow = false;

DataRow dtrerr = dtError.NewRow();

dtrerr[0] = 1;

dtrerr[1] = lngRow;

dtrerr[2] = "Invlaid Transaction Date";

dtError.Rows.Add(dtrerr);

}

}

else

{

bValidRow = false;

DataRow dtrerr = dtError.NewRow();

dtrerr[0] = 1;

dtrerr[1] = lngRow;

dtrerr[2] = "Transaction Date is Empty";

dtError.Rows.Add(dtrerr);

}

break;

//If more column present, then include that column in case 2

}

}

If (bValidRow == true) //add to datatable place

{

Country.Rows.Add(newRow);

}}

if (dtError.Rows.Count > 0)//Errors in Excel

{

excelimp.CloseExcel();

Form frm = ControlAdapter.CreateForm("Import_Errors");

frm.SetPropertyValue("gData", dtError);

frm.SetPropertyValue("gExcelPath", strFileName);

frm.ShowDialog();

}

else

{ //insert values to database

DataTable dtslno = SqlInterpreter.GetData("select COALESCE(max(Id),0)+1 as id from Country");

int id = TypeConverter.ConvertToInt(dtslno.Rows[0][0]);

int i = 0;

DataRow[] dr = Country.Select();

foreach (DataRow dr1 in dr)

{

DataTable dt1 = (DataTable) DataAdapter.Current.LoadData("select * from Country", "Country");

DataSet ds = new DataSet();

DataRow[] drw = dt1.Select("Id=" + id);// selecting next id value from database to store values

if (drw.Count() == 0)

{

DataRow drs = dt1.NewRow();

drs[0] = id;

drs[1] = dr1["CountryName"];

dt1.Rows.Add(drs);

DataRow[] drw1 = dt1.Select("Id=" + id);

DataAdapter.Current.Update("Country", drw1);// table country is updated

}

id = id + 1;

i = i + 1;

}

MessageBox.Show("Item Imported successfully", "Import", MessageBoxButtons.OK, MessageBoxIcon.Information);

dgrData.Update();

}}

catch (Exception ex)

{

excelimp.CloseExcel();

MessageBox.Show("Error in Excel file " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

return;

}

}

catch (Exception exc)

{

MessageBox.Show("Unexpected error occured!" + Environment.NewLine + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

}