how to import data using excel

Hi

how to import data from excel sheet using excel buffer…

i have gone through the previous posts…but i could not understand.

can anybody please explain me little more clear.

you can import or export data into excel using Dataport in Navision 5.0

Use Excel bufferlike this:

CLEAR(ExcelBuf);
ExcelBuf.OpenBook(FileName,SheetName);
ExcelBuf.ReadSheet;

Hi

Thanks nuno…however could u please let me know where to write this code…wat is the corresponding form for excel buffer table…

Open Book function - That code will open the specified sheet of the excel.

Read Sheet function - All the data in the excel sheet will be imported into the Excel Buffer table(table no 370).

Excel data will be available in Navision table & u can write code as per the requirement. Refer Report 81 - Import Budget from Excel

You can insert this code where you want to read Excel Buffer. It can be in a report, codeunit, etc.
This sample code is to show the use of Excel buffer nothing more, know you have to adapt to your current need.

Thanks for ur valuable replies…

I am able to import data from excel into excel buffer table…how to transfer those data into the corresponding table.(i.e; into jobjournal table).

Hi,

i wote code in navision 3.7

try it

CREATE(Excel1);
IF Excel AND QC_Report THEN
BEGIN
CLEAR(TempExcelBuffer);
TempExcelBuffer.DELETEALL;
END;

Report - OnPostReport()
Excel1.Visible(TRUE);
IF Excel AND QC_Report THEN
BEGIN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(‘QC’,’’,COMPANYNAME,’’);
TempExcelBuffer.GiveUserControl;
END;
Sheet3.Range(‘a’+FORMAT(Row)).Value :=BOM_DUM[PROD_NO].“No.” ;
Sheet3.Range(‘b’+FORMAT(Row)).Value :=BOM_DUM[PROD_NO].Description ;
Sheet3.Range(‘c’+FORMAT(Row)).Value :=BOM_DUM[PROD_NO].“Budget Quantity” ;
Sheet3.Range(‘d’+FORMAT(Row)).Value :=FORMAT(“Stock AT Stores”) ;
IF Shortage_ByConsidering_Previou=(BOM_DUM[PROD_NO].“Budget Quantity”-“Stock AT Stores” ) THEN
Sheet3.Range(‘e’+FORMAT(Row)).Value :=FORMAT(BOM_DUM[PROD_NO].“Budget Quantity”-“Stock AT Stores”)
ELSE
Sheet3.Range(‘e’+FORMAT(Row)).Value :=FORMAT(BOM_DUM[PROD_NO].“Budget Quantity”);
Sheet3.Range(‘f’+FORMAT(Row)).Value :=FORMAT(Shortage_ByConsidering_Previou);
Sheet3.Range(‘g’+FORMAT(Row)).Value :=FORMAT(BOM_DUM[PROD_NO].“Standard Cost”);
Sheet3.Range(‘h’+FORMAT(Row)).Value :=FORMAT(BOM_DUM[PROD_NO].“Unit Cost”);
END;

, Body (3) - OnPostSection()

Enter Cell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean)
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := CellValue;
TempExcelBuffer.Bold:=Bold ;
TempExcelBuffer.INSERT;

Enter Headings(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean)
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := FORMAT(CellValue);
TempExcelBuffer.Bold:=Bold ;
TempExcelBuffer.Formula := ‘’;
TempExcelBuffer.INSERT;

Hi anilkumar

Thanks for ur reply…

please tell me where to write this code to import my excel data into jobjournal…

NAV releases from 5 and up support importing entries form excel in stadard, using data migration.

You can use the following code If you used the .net excel import component Spire.XLS.

private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();

//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];

sheet.InsertDataTable((DataTable)this.dataGrid1.DataSource,true,2,1,-1,-1);

//Sets body style
CellStyle oddStyle = workbook.Styles.Add(“oddStyle”);
oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
oddStyle.KnownColor = ExcelColors.LightGreen1;

CellStyle evenStyle = workbook.Styles.Add(“evenStyle”);
evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
evenStyle.KnownColor = ExcelColors.LightTurquoise;

foreach( CellRange range in sheet.AllocatedRange.Rows)
{
if (range.Row % 2 == 0)
range.CellStyleName = evenStyle.Name;
else
range.CellStyleName = oddStyle.Name;
}

//Sets header style
CellStyle styleHeader = sheet.Rows[0].Style;
styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
styleHeader.VerticalAlignment = VerticalAlignType.Center;
styleHeader.KnownColor = ExcelColors.Green;
styleHeader.Font.KnownColor = ExcelColors.White;
styleHeader.Font.IsBold = true;

sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = “”$"#,##0";
sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = “”$"#,##0";

sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();

sheet.Rows[0].RowHeight = 20;

workbook.SaveToFile(“sample.xls”);
ExcelDocViewer( workbook.FileName );
}

  • removed link