Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Highlighted
Ferraz Outstanding Contributor.
Outstanding Contributor.
617 views

Export data to excel

Hi, 

I need to export the data from a file (number, name and address) to an excel file.

I had this done on NetExpress but now I need to "pass" to Visual Cobol WPF.

Does anyone have a small example that can send me how to create the file in excel, export the data to the cells and then save and close the file?

I already ran the forum here but could not find any answer to what I need.

 

Thanks

Alberto Ferraz

0 Likes
7 Replies
lanter-edv_ch
New Member.

RE: Export data to excel

I have a small C# example in .NET managed code. I can convert it to managed Cobol. Would this help you?
0 Likes
Ferraz Outstanding Contributor.
Outstanding Contributor.

RE: Export data to excel

Hi,
Yes, of course it helps.

I will be waiting.

Thanks
0 Likes
Ferraz Outstanding Contributor.
Outstanding Contributor.

RE: Export data to excel

Hi,
The code i use in NetExpress is the same of an topic of forum.

The link is community.microfocus.com/.../create-an-excel-workbook-and-prompt-user-for-filename-to-save-as-using-cobol-com-support

I hope this information is usuful for you.

Thanks
Alberto Ferraz
0 Likes
WPita_MMSIS Super Contributor.
Super Contributor.

RE: Export data to excel

You can use epplus is simple to use.


Você pode usar o epplus é simples de usar.

exemplo.
* declare pck as type ExcelPackage = new ExcelPackage
declare newFile = new FileInfo("C:\temp\mynewfile.xlsx") *> cria o arquivo xlsx
declare pck = new ExcelPackage(newFile) *> abrindo a planilha (pck)
*> CRIANDO (ADD) uma planilha neste arquivo e obtendo a referência para meu código operá-la.
declare ws = pck::Workbook::Worksheets::Add("Vendas")
*>ESCREVENDO O CABECALHO

*> Uma forma de escrever: informando endereco da celula
set ws::Cells["A1"]::Value to "Sample 1"
set ws::Cells["A1"]::Style::Font::Bold to true

* //Outra forma de escrever: informando linha e coluna da celula
* int linha = 1, coluna = 2;
* planilha.Cells[linha, coluna].Value = "O doce, colorido e irresistível mundo dos livros";

declare shape = ws::Drawings::AddShape("Shape1", type eShapeStyle::Rect)
invoke shape::SetPosition(50, 200)
invoke shape::SetSize(200, 100)
set shape::Text to "Sample 1 saves to the Response.OutputStream"

invoke pck::Save()
invoke pck::Dispose.
0 Likes
lanter-edv_ch
New Member.

RE: Export data to excel

The is the .Net Cobol program:

$set ilusing "Microsoft.Office.Interop.Excel"
program-id. Write2Excel as "NetCobol2Excel.Write2Excel".

data division.
working-storage section.

01 W-Filename string.

01 W-ExcelApp type Application.
01 W-ExcelWorkBook type Workbook.
01 W-ExcelWorkSheet type Worksheet.

01 W-Zeile binary-long.

procedure division.

DISPLAY 1 UPON ARGUMENT-NUMBER.
ACCEPT W-Filename FROM ARGUMENT-VALUE.
if type String::IsNullOrEmpty(W-Filename)
move "C:\temp\Cobol2Excel.xlsx" to W-Filename
end-if.

set W-ExcelApp to new Application
set W-ExcelWorkBook to W-ExcelApp::Workbooks::Add(1)
set W-ExcelWorkSheet to W-ExcelWorkBook::Sheets[1] as type Worksheet.

set W-ExcelWorkSheet::Name to "Test Cobol"
move 1 to W-Zeile
set W-ExcelWorkSheet::Cells[W-Zeile, 1] to type DateTime::Now::ToString()
set W-ExcelWorkSheet::Cells[W-Zeile, 2] to 100
add 1 to W-Zeile
set W-ExcelWorkSheet::Cells[W-Zeile, 1] to "Row " & W-Zeile & ", Column A"
set W-ExcelWorkSheet::Cells[W-Zeile, 2] to 200
add 1 to W-Zeile
set W-ExcelWorkSheet::Cells[W-Zeile, 1] to "Row " & W-Zeile & ", Column A"
set W-ExcelWorkSheet::Cells[W-Zeile, 2] to 300
add 1 to W-Zeile
set W-ExcelWorkSheet::Cells[W-Zeile, 1] to W-Filename
set W-ExcelWorkSheet::Cells[W-Zeile, 2] to "=SUM(B1:B3)"

* invoke W-ExcelWorkBook::SaveAs(W-Filename) *> fails
invoke W-ExcelWorkBook::SaveAs(W-Filename,
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type XlSaveAsAccessMode::xlNoChange
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
type System.Reflection.Missing::Value
)

invoke W-ExcelWorkBook::Close()
invoke W-ExcelApp::Quit()

stop run.

end program Write2Excel.

If you send a mail address I can send you the VS solution.
Freundliche Grüsse
Werner Lanter
0 Likes
Ferraz Outstanding Contributor.
Outstanding Contributor.

RE: Export data to excel

Hi Werner,

Thanks for your help.
I already tested it and it's okay.

Best regards
Alberto Ferraz
0 Likes
WPita_MMSIS Super Contributor.
Super Contributor.

RE: Export data to excel

What Werner sent works but only for those who have Excel installed.

O que o Werner enviou funciona mas somente para quem tem Excel instalado.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.