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