Import/export Excel files - Funziona!!

[Gfb22]

In my apps I occasionally need to import some data from Excel files (coming from other programs) to then insert in the DBF archives (and viceversa).
Until now, for 'convenience' I used the possibility of converting .xls files into .dbf going from Microsoft Excel but I want to find a more effective and cleaner way to do this.
There is also another problem during the conversion: in the original Excel file there is a text field that contains almost exclusively numeric values. So, during the conversion (automatically), the field is converted into NUM and therefore I lose any information present as text.
How can I fix?
Thank you all!

[wriedmann]

Ciao Gian Ferrucio,
domanda 1: si tratta di file xls oppure xlsx?
domanda 2: la soluzione può costare qualcosa?
domanda 3: sulle macchine è installato Excel?

Spiego meglio: per gli xls ho una libreria COM che mi permette di leggere e scriere. Questa libreria era abbastanza economica ed è possibile integrarla in VO molto facilmente, ma la cosa funziona (ovviamente) anche in X#.
Per xlsx uso una libreria .NET che una volta era gratuita, e che ho integrato in VO tramite una mia libreria COM. In X# si usa direttamente.
Se Excel è installato, puoi usare OLE Automation direttamente da VO oppure X#.

Saluti

Wolfgang

[softdevo@tiscali.it]

Puoi usare NPOI.DLL è una dll che permette di scrivere leggere e manipolare file XLS senza che sia presente Excel sulla macchina. Io la uso da tempo e funziona una meraviglia.

[softdevo@tiscali.it]

Ah è gratuita tra l'altro

[Gfb22]

...Ho trovato la NPOI.DLL su https://www.dllme.com/dll/files/npoi. Ora provo a scaricarla e vedo di capire come funziona: se hai già dei suggerimenti sono certamente graditi!
Il vantaggio, da quello che dici (oltre ad essere gratuita) è che non richiede necessariamente che sulla macchina ci sia Excel installato...
Grazie!

[wriedmann]

Ciao,

per favore butta questa DLL perchè non mi sembra una fonte affidabile.

Quella corretta è https://www.nuget.org/packages/NPOI/

Saluti

Wolfgang

[Gfb22]

Grazie Wolfgang!

[softdevo@tiscali.it]

Ecco un esempio funzionante

// Application : Export2Excel
// Export2Excel.prg , Created : 16/06/2012 16:30
// User : Danilo

#using NPOI.HSSF.UserModel
#using NPOI.HPSF
#using NPOI.POIFS.FileSystem
#using NPOI.SS.UserModel
#using System.IO
#using System.Data

FUNCTION ImportExcel(cFile AS STRING,cTable AS STRING,cModello := "" AS STRING,lRigaDiIntestazione := FALSE AS LOGIC ) AS VOID
LOCAL oFileStream AS FileStream
LOCAL wb AS HSSFWorkBook
LOCAL ws AS ISheet
LOCAL rows AS System.Collections.IEnumerator
LOCAL row AS HSSFRow
LOCAL cVal AS STRING
LOCAL dt AS DataTable //,dt1
LOCAL j AS INT
LOCAL nCol AS INT
LOCAL cell AS ICell
LOCAL oSqlTableCreator AS SqlTableCreator
LOCAL cCommand AS STRING

oFileStream := FileStream{cFile, FileMode.Open, FileAccess.Read}
wb := HSSFWorkbook{oFileStream}
ws := wb:GetSheetAt(0)
rows := ws:GetRowEnumerator()
dt := DataTable{}

eseguisql("drop table IF EXISTS "+cTable)

IF !Empty(cModello) .and. ExistTabella(cModello)
cVal := retqueryresultstring("SHOW CREATE TABLE "+cModello,1)
cVal := StrTran(cVal,"CREATE TABLE `"+cModello+"` (","CREATE TABLE `"+cTable+"` (")

eseguisql(cVal)
eseguisql("truncate "+cTable)
dt := RetDataTable("select * from "+cTable,cTable,SqlConn():oConn,TRUE)
/*dt1 := RetDataTable("select * from "+cModello,cTable,SqlConn():oConn,TRUE)
dt := dt1:Clone()
dt1:Dispose()
*/
nCol := dt:Columns:Count-1
ELSE
LOCAL capp := "col" AS STRING
LOCAL i AS INT
rows:MoveNext()
row := (HSSFRow)rows:Current
nCol := row:LastCellNum-1
rows:Reset()
i := 0
FOR j := 0 UPTO nCol
IF lRigaDiIntestazione // c'è la riga di intestazione //
dt:Columns:Add(System.Convert.ToString(row:GetCell(j)))
ELSE
dt:Columns:Add(capp+Chr(97+(DWORD)i))
IF 97+(DWORD)i > 121
capp := "col"+Chr(Integer((j/25)+96))
i := 0
ENDIF
++i
ENDIF
NEXT
oSqlTableCreator := SqlTableCreator{SqlConn():oConn}
oSqlTableCreator:DestinationTableName := cTable
cCommand := oSqlTableCreator:GetCreateFromDataTableSQL(cTable,dt)
eseguisql(cCommand)
ENDIF

IF lRigaDiIntestazione // salta la riga di intestazione //
rows:MoveNext()
ENDIF

DO WHILE rows:MoveNext()
row := (HSSFRow)rows:Current
cCommand := "insert into "+cTable+" set "

FOR j := 0 UPTO nCol
cell := row:GetCell(j)
IF cell == NULL
cCommand += dt:Columns[j]:ColumnName+"=NULL,"
ELSE
cVal := System.Convert.ToString(cell)
IF dt:Columns[j]:DataType:FullName == "System.SByte" .or. dt:Columns[j]:DataType:FullName == "System.Boolean" ;
.or. dt:Columns[j]:DataType:FullName == "System.TinyInt"
cVal := iif(Upper(cVal)=="FALSE","0","1")
ELSEIF dt:Columns[j]:DataType:FullName == "System.DateTime"
cVal := MyDToSFromText(cVal)
ELSEIF dt:Columns[j]:DataType:FullName == "System.UInt16" .or. dt:Columns[j]:DataType:FullName == "System.UInt32" ;
.or. dt:Columns[j]:DataType:FullName == "System.UInt64" .or. dt:Columns[j]:DataType:FullName == "System.Decimal" ;
.or. dt:Columns[j]:DataType:FullName == "System.Int32" ;
.or. dt:Columns[j]:DataType:FullName == "System.Int16" .or. dt:Columns[j]:DataType:FullName == "System.Int64" ;
.or. dt:Columns[j]:DataType:FullName == "System.Single" .or. dt:Columns[j]:DataType:FullName == "System.Double"
IF Empty(DevoCommonNet.Functions.Val(cVal))
cVal := "0"
ENDIF
cVal := StrTran(cVal,",",".")
ELSE
cVal := AddSlash(cVal)
ENDIF
IF j = nCol
cCommand += dt:Columns[j]:ColumnName+"='"+cVal+"'"
ELSE
cCommand += dt:Columns[j]:ColumnName+"='"+cVal+"',"
ENDIF
ENDIF
NEXT
//cCommand:TrimEnd(<char>{','})
IF !eseguisql(cCommand)
IF allerta("Warning","Abort?")
EXIT
ENDIF
ENDIF
ENDDO

/*
DO WHILE rows:MoveNext()
row := (HSSFRow)rows:Current
dr := dt:NewRow()

FOR j := 0 UPTO nCol
cell := row:GetCell(j)
IF cell == NULL
dr[j] := NULL
ELSE
dr[j] := cell:ToString()
ENDIF
NEXT
dt:Rows:Add(dr)
ENDDO
*/

RETURN


FUNCTION ExportDataTable2Excel(oDT AS System.Data.DataTable,cFile := "" AS STRING, lOpenAfterCreate := FALSE AS LOGIC) AS LOGIC
LOCAL wb AS HSSFWorkBook
LOCAL ws AS ISheet
LOCAL row AS IRow
LOCAL rownum, colcount AS INT
LOCAL oRichTextBox AS System.Windows.Forms.RichTextBox
LOCAL cell AS ICell
LOCAL val AS OBJECT
LOCAL cVal AS STRING
LOCAL vType AS Type
LOCAL oSaveFileDialog AS System.Windows.Forms.SaveFileDialog

IF Empty(cFile)
oSaveFileDialog := System.Windows.Forms.SaveFileDialog{}
oSaveFileDialog:Filter := "File .XLS |*.Xls"
IF oSaveFileDialog:ShowDialog() == System.Windows.Forms.DialogResult.OK
cFile := oSaveFileDialog:FileName
ELSEIF Empty(oSaveFileDialog:FileName)
RETURN FALSE
ELSE
RETURN FALSE
ENDIF
ENDIF

wb := HSSFWorkBook{}
ws := wb:CreateSheet("JN2_Sheet1")
rownum := 0
row := ws:CreateRow(rownum++)

FOR LOCAL i := 0 AS INT UPTO (colcount := oDT:Columns:Count) - 1
row:CreateCell(i):SetCellValue(oDT:Columns:ColumnName)
NEXT

FOREACH dtRow AS System.Data.DataRow IN oDT:Rows
row := ws:CreateRow(rownum++)
LOCAL i AS INT
FOR i := 0 UPTO colcount - 1
cell := row:CreateCell(i)
val := dtRow
TRY
vType := val:GetType()
IF vType == TypeOf(STRING)
cVal := (STRING)val
IF Left(cVal,6) == "{rtf1"
oRichTextBox := System.Windows.Forms.RichTextBox{}
oRichTextBox:Rtf := cVal
cell:SetCellValue(oRichTextBox:Text)
oRichTextBox:Dispose()
ELSE
cell:SetCellValue(cVal)
ENDIF
ELSEIF vType == TypeOf(LOGIC)
cell:SetCellValue((LOGIC)val)
ELSEIF vType == TypeOf(DateTime)
cell:SetCellValue(((DateTime)val):ToShortDateString())// :ToString())
ELSE
TRY
cell:SetCellValue(Double.Parse(val:ToString()))
CATCH
cell:SetCellValue(System.Convert.ToString(val))
END TRY
ENDIF
CATCH
cell:SetCellValue("")
END TRY
NEXT
NEXT

FOR LOCAL i := 1 AS INT UPTO colcount
ws:AutoSizeColumn(i-1)
NEXT

System.IO.File.Delete(cFile)
LOCAL file AS FileStream
file := FileStream{cFile,FileMode.Create}
wb:Write(file)
file:Flush()
file:Close()

IF lOpenAfterCreate .and. File(cFile)
EseguiUnaShell(cFile)
ENDIF

RETURN File(cFile)

FUNCTION ExportPiuDataTable2Excel(oLServer AS MySqlSelect,cFile := "" AS STRING,cNomeTab AS STRING) AS STRING
LOCAL wb AS HSSFWorkBook
LOCAL ws AS ISheet
LOCAL row AS IRow
LOCAL rownum, colcount AS INT
LOCAL oRichTextBox AS System.Windows.Forms.RichTextBox
LOCAL cell AS ICell
LOCAL val AS OBJECT
LOCAL cVal AS STRING
LOCAL vType AS Type
LOCAL oSaveFileDialog AS System.Windows.Forms.SaveFileDialog
LOCAL oDT AS System.Data.DataTable

IF Empty(cFile)
oSaveFileDialog := System.Windows.Forms.SaveFileDialog{}
oSaveFileDialog:Filter := "File .XLS |*.Xls"
IF oSaveFileDialog:ShowDialog() == System.Windows.Forms.DialogResult.OK
cFile := oSaveFileDialog:FileName
System.IO.File.Delete(cFile)
wb := HSSFWorkBook{}
ELSEIF Empty(oSaveFileDialog:FileName)
RETURN ""
ELSE
RETURN ""
ENDIF
ELSE
LOCAL fs AS FileStream
IF File(cFile)
fs := FileStream{cFile, FileMode.Open, FileAccess.ReadWrite}
wb := HSSFWorkBook{fs}
ELSE
RETURN ""
ENDIF
ENDIF
oDT := oLServer:DataTable
ws := wb:CreateSheet(cNomeTab)
rownum := 0
LOCAL styleTitle AS HSSFCellStyle
LOCAL font1 AS HSSFFont

font1 := (HSSFFont)wb:CreateFont() // HSSFFont{}
font1:Color := NPOI.HSSF.Util.HSSFColor.BLACK.index
font1:FontName := "Arial"
font1:FontHeightInPoints := 12
font1:Boldweight := (SHORT)NPOI.SS.UserModel.FontBoldWeight.BOLD

styleTitle := (HSSFCellStyle)wb:CreateCellStyle() //HSSFCellStyle{}
styleTitle:FillForegroundColor := NPOI.HSSF.Util.HSSFColor.LightYellow.Index // .LIGHT_YELLOW.index
styleTitle:FillPattern := NPOI.SS.UserModel.FillPattern.SolidForeground // .FillPatternType .SOLID_FOREGROUND // FillPattern.SolidForeground //Type.SOLID_FOREGROUND
styleTitle:BorderRight := BorderStyle.Thin
styleTitle:RightBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
styleTitle:BorderBottom := BorderStyle.Thin
styleTitle:BottomBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
styleTitle:BorderLeft := BorderStyle.Thin
styleTitle:LeftBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
styleTitle:BorderTop := BorderStyle.Thin
styleTitle:TopBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
styleTitle:SetFont(font1)

LOCAL TableNametyle AS HSSFCellStyle
LOCAL font3 AS HSSFFont

font3 := (HSSFFont)wb:CreateFont() // HSSFFont{}
font3:Color := NPOI.HSSF.Util.HSSFColor.BLACK.index
font3:FontName := "Arial"
font3:FontHeightInPoints := 12
font3:Color := NPOI.HSSF.Util.HSSFColor.Brown.Index
font3:Boldweight := (SHORT)NPOI.SS.UserModel.FontBoldWeight.Bold

TableNametyle := (HSSFCellStyle)wb:CreateCellStyle() //HSSFCellStyle{}
TableNametyle:FillForegroundColor := NPOI.HSSF.Util.HSSFColor.LightGreen.Index
TableNametyle:FillPattern := NPOI.SS.UserModel.FillPattern.SolidForeground //FillPattern.SolidForeground
TableNametyle:BorderRight := BorderStyle.Thin
TableNametyle:RightBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
TableNametyle:BorderBottom := BorderStyle.Thin
TableNametyle:BottomBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
TableNametyle:BorderLeft := BorderStyle.Thin
TableNametyle:LeftBorderColor := NPOI.HSSF.Util.HSSFColor.Black.index
TableNametyle:BorderTop := BorderStyle.Thin
TableNametyle:TopBorderColor := NPOI.HSSF.U

[Gfb22]

Grazie Danilo.
Intanto ho scaricato NPOI e nei prossimi giorni provo a fare delle prove per capire come funziona...
Chiedo intanto a Wolfgang se la sua libreria COM funziona anche con VO e quanto costa (mi sembra invece che la soluzione con NPOI possa funzionare solo con .NET ma non con VO...)
Grazie!

NPOI.jpg (14.45 KiB) Viewed 2408 times

[wriedmann]

Ciao Gian Ferruccio,
purtroppo quello che uso io non è a pagamento unico, ma sono passati a un modello subscription, cioè pagamenti regolari.
Dovrei vedere se trovo la versione vecchia che era libera e te la posso dare.
Altrimenti sarebbe da scrivere una DLL COM usando NPOI.
Saluti
Wolfgang