Import Data from Microsoft Office Excel

1C:Enterprise platform integration capabilities and techniques

#1
People who like this:0Yes/0No
Active user
Rating: 6
Joined: Sep 16, 2011
Company: TLG Integration

I am going to implement the data import from Microsoft Office Excel Workbook file, but have no idea how to do that. It seems to be very inefficient to parse XLS file.
Any ideas?

 
#2
People who like this:1Yes/0No
Active user
Rating: 2
Joined: Nov 24, 2011
Company:

Code
&OnClient
Procedure LoadGoods(command) //button event
   Items = getItemsTable();
   FromXLSGOODS(Items); //code of this server fucntion i think you can write selfdepended
EndProcedure

&Onclient
function getItemsTable() 
   Separator = "/";
   ExcelObject = new COMObject("Excel.Application");
   ExcelObject.Visible = False;
   WorkBook = ExcelObject.Workbooks.Open(xlsFile);//file name
   TCells = WorkBook.ActiveSheet.Cells;
   VT = New ValueList;
   StringC = 2;
   VT.Clear();
   While Not EmptyString(TCells .Cells(StringC ,3).Value) do
      tablestring= New structure(("ItemCode,ItemDescr");//add another wich you need
      tablestring.ItemCode= TCells .Cells(StringC ,3).Value ;//
      tablestring.ItemDescr=  TCells .Cells(StringC ,4).Value;//
      VT.Add(tablestring);
      StringC = StringC + 1;
   EndDo;
   WorkBook.Close();
   ExcelObject.Quit();
    TablStc = New Structure;
    TablStc.Insert("VvalueTable",VT);
   TAddres= LoadToTemporaryStore(TablStc,ThisForm.UniqueIdentifier);//sorry i don't know English version of this function
   Return TAddres;
EndFunction

 
#3
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Nov 24, 2011
Company:

i didn't debug this function. but russian version is working good :)

 
#4
People who like this:0Yes/0No
Active user
Rating: 6
Joined: Sep 16, 2011
Company: TLG Integration

This produces tons of errors like this:

{DataProcessor.XLSImport.Form.Form.Form(25,50)}: Expecting ')'
tablestring= New structure(("ItemCode,ItemDescr")<<?>>;//add another wich you need  (Verification: Server)

 
#5
People who like this:1Yes/0No
Active user
Rating: 3
Joined: Sep 16, 2011
Company: individual

Thanks for good idea, Cocos!
I've made some corrections:

You will need to add a LoadGoods command for LoadGoods handler and XlsFile attribute of string type for the file name to your form. After reading you will need to load the data from the temporary storage at server.

Code
&AtClient 
Procedure LoadGoods(Command) //button event 
    Address = GetItemsTable(); 
    // handle the data 
EndProcedure 

&AtClient 
Function GetItemsTable() 
    xlCellTypeLastCell = 11; // VBA constant 

    CodeColumnNumber = 3; 
    DescriptionColumnNumber = 4; 
    RowNumber = 2; // first row with data 

    ExcelObject = New COMObject("Excel.Application"); 
    ExcelObject.Visible = False; 
    WorkBook = ExcelObject.Workbooks.Open(XlsFile); //file name 
    TCells = WorkBook.ActiveSheet.Cells; 

    VT = New ValueList; 
    LastRowNumber = WorkBook.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row; 
    VT.Clear(); 
    While RowNumber <= LastRowNumber Do 
        Row = New Structure("ItemCode,ItemDescr"); //add another wich you need 
        Row.ItemCode = TCells.Cells(RowNumber, CodeColumnNumber).Value; 
        Row.ItemDescr = TCells.Cells(RowNumber, DescriptionColumnNumber).Value; 
        VT.Add(Row); 
        RowNumber = RowNumber + 1; 
    EndDo; 
    WorkBook.Close(); 
    ExcelObject.Quit(); 
    TablStc = New Structure; 
    TablStc.Insert("ValueTable", VT); 
    TAddres = PutToTempStorage(TablStc, ThisForm.UUID); 
    Return TAddres; 
EndFunction

 
#6
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Nov 24, 2011
Company:

Silvia Ashley. Look Samuel Harris's code. He corrects all mistakes. I wrote my code after translationg from russian.
Samuel Harris thanks.

 
#7
People who like this:0Yes/0No
Active user
Rating: 6
Joined: Sep 16, 2011
Company: TLG Integration

Thank you very much, Cocos and Samuel!

 
#8
People who like this:0Yes/0No
Active user
Rating: 5
Joined: Jun 4, 2013
Company:

This is  helpful ... Thanks for sharing

 
#9
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Jun 23, 2013
Company: NBMotor

Dear all!   please, help with this.   If anyone able to make new configuration with just this import procedure, offload it to *.dt file, add to it *.xls file that is definitely can be parsed and upload to this forum - it will be REALLY  helpful for all newcomer.

 
#10
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Jan 11, 2013
Company: Brighteye AB

Dear all, have problems with above sample. Opening the Excel file in file version of 1C is OK, but in SQL server version cant open? Any hint of what is wrong.
Regards
Keijo

 
#11
People who like this:0Yes/0No
Timofey Bugaevsky
Guest

Joined:
Company:

Hello, Keijo.

Please check, maybe the Excel file is opened by another application (Excel, for example) when you are trying to import it to 1C application.

 
#12
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Jan 11, 2013
Company: Brighteye AB

Hi, sorry not that easy unfortunately. Have tried everything even restarted computer, and the file (several versions with different names) are openable with other tools, so not locked ..And as said, it works fine in file version.
Regards
Keijo

 
#13
People who like this:0Yes/0No
Active user
Rating: 7
Joined: Sep 26, 2012
Company: individual

Maybe you try open excel at server, but its not install at server? check that script work &AtClient

 
#14
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Jan 11, 2013
Company: Brighteye AB

Many Thanks, that was the problem, had the script in &AtServer block, changed to &AtClient and all works...
Regards
Keijo

 
Subscribe
Be the first to know tips & trick on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.