how to import data using excel

Business Central/NAV Developers Forum
1

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.

2

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

3

Use Excel bufferlike this:

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

4

Hi

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

5

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

6

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.

7

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).

8

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;

9

Hi anilkumar

Thanks for ur reply…

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

10

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

11

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