• State Suggested Answer
  • Date
  • Date 16 Jan 2019 17:23
  • Replies 7 replies
  • Answers 1 answer
  • Subscribers 964 subscribers
  • Views 374 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

  • I have a small C# example in .NET managed code. I can convert it to managed Cobol. Would this help you?
  • In reply to lanter-edv.ch:

    Hi,
    Yes, of course it helps.

    I will be waiting.

    Thanks
  • In reply to Ferraz:

    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
  • In reply to Ferraz:

    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.
  • In reply to Ferraz:

    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
  • In reply to lanter-edv.ch:

    Hi Werner,

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

    Best regards
    Alberto Ferraz
  • In reply to Ferraz:

    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.